標籤：
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.IO; using System.Web; using Microsoft.Office.Interop.Excel; using System.Reflection;
/* * 開發人員：Hisen * 時間：2008年11月24日 * 功能：將資料匯出Excel * */ namespace XT.LiTree.Logic { public class ExcelExport { private ExcelExport() { } private static ExcelExport _instance = null;
public static ExcelExport Instance { get { if (_instance == null) _instance = new ExcelExport(); return _instance; } }
/// <summary> /// DataTable直接匯出Excel,此方法會把DataTable的資料用Excel開啟,再自己手動去儲存到確切的位置 /// </summary> /// <param name="dt">要匯出Excel的DataTable</param> /// <returns></returns> public bool DoExport(DataTable dt) { Application app = new ApplicationClass(); if (app == null) { throw new Exception("Excel無法啟動"); } app.Visible = true; Workbooks wbs = app.Workbooks; Workbook wb = wbs.Add(Missing.Value); Worksheet ws = (Worksheet)wb.Worksheets[1];
int cnt = dt.Rows.Count; int columncnt = dt.Columns.Count;
// *****************擷取資料******************** object[,] objData = new Object[cnt + 1, columncnt]; // 建立快取資料 // 擷取欄位標題 for (int i = 0; i < columncnt; i++) { objData[0, i] = dt.Columns[i].ColumnName; } // 擷取具體資料 for (int i = 0; i < cnt; i++) { System.Data.DataRow dr = dt.Rows[i]; for (int j = 0; j < columncnt; j++) { objData[i + 1, j] = dr[j]; } }
//********************* 寫入Excel****************** Range r = ws.get_Range(app.Cells[1, 1], app.Cells[cnt + 1, columncnt]); r.NumberFormat = "@"; //r = r.get_Resize(cnt+1, columncnt); r.Value2 = objData; r.EntireColumn.AutoFit();
app = null; return true; }
/// <summary> /// DataTable通過流匯出Excel /// </summary> /// <param name="ds">資料來源DataSet</param> /// <param name="columns">DataTable中列對應的列名(可以是中文),若為null則取DataTable中的欄位名</param> /// <param name="fileName">儲存檔案名稱(例如：a.xls)</param> /// <returns></returns> public bool StreamExport(DataTable dt, string[] columns, string fileName, System.Web.UI.Page pages) { if (dt.Rows.Count > 65535) //總行數大於Excel的行數 { throw new Exception("預匯出的資料總行數大於excel的行數"); } if (string.IsNullOrEmpty(fileName)) return false; StringBuilder content = new StringBuilder(); StringBuilder strtitle = new StringBuilder(); content.Append("<html xmlns:o=‘urn:schemas-microsoft-com:office:office‘ xmlns:x=‘urn:schemas-microsoft-com:office:excel‘ xmlns=‘http://www.w3.org/TR/REC-html40‘>"); content.Append("<head><title></title><meta http-equiv=‘Content-Type‘ content=\"text/html; charset=gb2312\">"); //注意：[if gte mso 9]到[endif]之間的代碼，用於顯示Excel的網格線，若不想顯示Excel的網格線，可以去掉此代碼 content.Append("<!--[if gte mso 9]>"); content.Append("<xml>"); content.Append(" <x:ExcelWorkbook>"); content.Append(" <x:ExcelWorksheets>"); content.Append(" <x:ExcelWorksheet>"); content.Append(" <x:Name>Sheet1</x:Name>"); content.Append(" <x:WorksheetOptions>"); content.Append(" <x:Print>"); content.Append(" <x:ValidPrinterInfo />"); content.Append(" </x:Print>"); content.Append(" </x:WorksheetOptions>"); content.Append(" </x:ExcelWorksheet>"); content.Append(" </x:ExcelWorksheets>"); content.Append("</x:ExcelWorkbook>"); content.Append("</xml>"); content.Append("<![endif]-->"); content.Append("</head><body><table style=‘border-collapse:collapse;table-layout:fixed;‘><tr>");
if (columns != null) { for (int i = 0; i < columns.Length; i++) { if (columns[i] != null && columns[i] != "") { content.Append("<td><b>" + columns[i] + "</b></td>"); } else { content.Append("<td><b>" + dt.Columns[i].ColumnName + "</b></td>"); } } } else { for (int j = 0; j < dt.Columns.Count; j++) { content.Append("<td><b>" + dt.Columns[j].ColumnName + "</b></td>"); } } content.Append("</tr>\n");
for (int j = 0; j < dt.Rows.Count; j++) { content.Append("<tr>"); for (int k = 0; k < dt.Columns.Count; k++) { object obj = dt.Rows[j][k]; Type type = obj.GetType(); if (type.Name == "Int32" || type.Name == "Single" || type.Name == "Double" || type.Name == "Decimal") { double d = obj == DBNull.Value ? 0.0d : Convert.ToDouble(obj); if (type.Name == "Int32" || (d - Math.Truncate(d) == 0)) content.AppendFormat("<td style=‘vnd.ms-excel.numberformat:#,##0‘>{0}</td>", obj); else content.AppendFormat("<td style=‘vnd.ms-excel.numberformat:#,##0.00‘>{0}</td>", obj); } else content.AppendFormat("<td style=‘vnd.ms-excel.numberformat:@‘>{0}</td>", obj); } content.Append("</tr>\n"); } content.Append("</table></body></html>"); content.Replace(" ", ""); pages.Response.Clear(); pages.Response.Buffer = true; pages.Response.ContentType = "application/ms-excel"; //"application/ms-excel"; pages.Response.Charset = "UTF-8"; pages.Response.ContentEncoding = System.Text.Encoding.UTF7; fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8); pages.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); pages.Response.Write(content.ToString()); //pages.Response.End(); //注意，若使用此代碼結束響應可能會出現“由於代碼已經過最佳化或者本機架構位於呼叫堆疊之上,無法計算運算式的值。”的異常。 HttpContext.Current.ApplicationInstance.CompleteRequest(); //用此行代碼代替上一行代碼，則不會出現上面所說的異常。 return true; }
/// <summary> /// 直接由GridView匯出Excel /// </summary> /// <param name="ctl">控制項(一般是GridView)</param> /// <param name="FileName">匯出的檔案名稱</param> /// <param name="removeIndexs">要移除的列的索引數組(因為有時我們並不希望把GridView中的所有列全部匯出)</param> /// <param name="pages"></param> public void ControlToExcel(System.Web.UI.WebControls.GridView ctl, string FileName, string[] removeIndexs, System.Web.UI.Page pages) { if (removeIndexs != null) { foreach (string index in removeIndexs) { ctl.Columns[int.Parse(index)].Visible = false; } } pages.Response.Charset = "UTF-8"; pages.Response.ContentEncoding = System.Text.Encoding.UTF7; pages.Response.ContentType = "application/ms-excel"; FileName = System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8); pages.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName); ctl.Page.EnableViewState = false; System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); ctl.RenderControl(hw); pages.Response.Write(tw.ToString()); HttpContext.Current.ApplicationInstance.CompleteRequest(); } } }
C#匯出EXCEL(DataTable匯出EXCEL)
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
