方法一:
public void DataBindTitleExcel(Page pPage, DataTable dt, string ExcelTitle, string strUserMsg) { HttpResponse response = pPage.Response; if (dt.Rows.Count == 0) { response.Write("<script>alert('對不起,沒有可用於匯出的資料!')</script>"); response.End(); } response.ContentEncoding = Encoding.GetEncoding("GB2312"); response.ContentType = "application/ms-excel"; response.AppendHeader("Content-Disposition", "attachment;filename=Export.xls"); int count = dt.Columns.Count; StringBuilder builder = new StringBuilder(); builder.Append("<html><head>\n"); builder.Append("<meta http-equiv=\"Content-Language\" content=\"zh-cn\">\n"); builder.Append("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">\n"); builder.Append("</head>\n"); builder.Append("<table border=1>"); if (ExcelTitle != "") { string str = "<font size=4><b>" + ExcelTitle + "</b></font>"; if (strUserMsg != "") { str = str + "（" + strUserMsg + "）"; } builder.Append(string.Concat(new object[] { "<tr><td colspan=", count, ">", str, "</td></tr>" })); } builder.Append("<tr><td colspan=" + count + " valign=middle height=24>"); builder.Append("查詢時間：" + DateTime.Now.ToString("G") + "</td></tr>"); builder.Append("<tr>\n"); for (int i = 0; i < count; i++) { if (dt.Columns[i].Caption.ToString().ToLower() != "id") { builder.Append("<td bgcolor=#CCFFCC><b>" + dt.Columns[i].Caption.ToString() + "</b></td>\n"); } } foreach (DataRow row in dt.Rows) { builder.Append("<tr>"); for (int j = 0; j < count; j++) { if (dt.Columns[j].Caption.ToString().ToLower() != "id") { builder.Append("<td style='vnd.ms-excel.numberformat:@'>" + row[j].ToString() + "</td>"); } } builder.Append("</tr>\n"); } builder.Append("</table>\n"); response.Write(builder.ToString()); response.End(); }
方法二：
引用Interop.Excel.dll
撰寫ExcelExportProvider.cs
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Reflection;using System.IO;namespace TCGL.Web.util{ /// <summary> /// 標題：將 DataSet, DataTable 匯出到 Excel /// 作者：肖小勇 /// 日期：2009-10-23 /// 描述：對之前做的匯出 Excel 做調整以支援對 DataSet 及 DataTable 的匯出； /// DataSet 匯出時可以指定需要匯出的 DataTable /// DataTable 匯出時可以指定需要匯出的 DataColumn 及自訂匯出後的列名 /// </summary> public class ExcelExportProvider { private static object missing = Type.Missing; #region " ExportToExcel " /// <summary> /// 將 /// </summary> /// <param name="fDataSet"></param> /// <param name="fFileName"></param> public static void ExportToExcel(DataSet fDataSet, String fFileName) { List<DataTableExportOptions> options = new List<DataTableExportOptions>(); foreach (DataTable dataTable in fDataSet.Tables) options.Add(new DataTableExportOptions(dataTable)); ExportToExcel(options, fFileName); } public static void ExportToExcel(DataTable fDataTable, String fFileName) { ExportToExcel(new DataTableExportOptions(fDataTable), fFileName); } public static void ExportToExcel(DataTableExportOptions fOption, String fFileName) { ExportToExcel(new List<DataTableExportOptions>(new DataTableExportOptions[] { fOption }), fFileName); } /// <summary> /// 將 DataTable 匯出到 Excel /// </summary> /// <param name="fOptions"></param> public static void ExportToExcel(List<DataTableExportOptions> fOptions, String fFileName) { if (fOptions == null || fOptions.Count == 0) return; try { if (File.Exists(fFileName)) File.Delete(fFileName); } catch { return; } Excel.Application application = new Excel.Application(); application.Visible = false; application.UserControl = false; Excel.Workbook workBook = (Excel.Workbook)(application.Workbooks.Add(missing)); try { #region " 根據需要匯出的 DataTable 數量，預先增加不足的工作表或多餘的工作表 " // 添除多餘的工作表 while (application.ActiveWorkbook.Sheets.Count > fOptions.Count) ((Excel.Worksheet)application.ActiveWorkbook.Sheets[1]).Delete(); // 添加工作表 while (application.ActiveWorkbook.Sheets.Count < fOptions.Count) application.Worksheets.Add(missing, missing, missing, missing); #endregion int sheetIndex = 1; List<String> sheetNames = new List<string>(); foreach (DataTableExportOptions option in fOptions) { #region " 處理在多個 DataTable 設定為相同的工作表名稱的問題 " if (sheetNames.Contains(option.WorkSheetName)) { int i = 1; while (true) { string newSheetName = option.WorkSheetName + i.ToString(); if (!sheetNames.Contains(newSheetName)) { sheetNames.Add(newSheetName); option.WorkSheetName = newSheetName; break; } i++; } } else { sheetNames.Add(option.WorkSheetName); } #endregion ExportToExcel(application, workBook, (Excel.Worksheet)application.ActiveWorkbook.Sheets[sheetIndex], option); sheetIndex++; } workBook.SaveAs(fFileName, missing, missing, missing, missing, missing , Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); } finally { application.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(application); GC.Collect(); } } /// <summary> /// 將 DataTable 匯出到 Excel /// </summary> /// <param name="fApplication"></param> /// <param name="fWorkBook"></param> /// <param name="fOption"></param> private static void ExportToExcel(Excel.Application fApplication, Excel._Workbook fWorkBook, Excel.Worksheet worksheet, DataTableExportOptions fOption) { Excel.Range range; worksheet.Name = fOption.WorkSheetName; if (fOption.DataTable == null) return; int rowCount = fOption.DataTable.Rows.Count; int colCount = fOption.VisibleColumnOptions.Count; int colIndex = 0; int rowIndex = 0; #region " Set Header Values " object[,] colValues = new object[1, colCount]; foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions) { if (!option.Visible) continue; colValues[0, colIndex] = option.Caption; colIndex++; } range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, 1)); range.Value2 = colValues; #endregion #region " Header Style " range.Font.Bold = true; range.Font.Name = "Georgia"; range.Font.Size = 10; range.RowHeight = 26; range.EntireColumn.AutoFit(); #endregion #region " Set Row Values " object[,] rowValues = new object[rowCount, colCount]; rowIndex = 0; foreach (DataRow dataRow in fOption.DataTable.Rows) { colIndex = 0; foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions) { rowValues[rowIndex, colIndex] = dataRow[option.ColumnName]; colIndex++; } rowIndex++; } range = worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1)); range.Value2 = rowValues; #region " Row Style " range.Font.Name = "Georgia"; range.Font.Size = 9; range.RowHeight = 18; range.EntireColumn.AutoFit(); //range.Borders.ColorIndex = 2; #endregion #endregion #region " Set Borders " range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, rowCount + 1)); range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.Borders.Weight = Excel.XlBorderWeight.xlThin; range.Borders.Color = Color.Black.ToArgb(); #endregion } #endregion #region " GetCellName " private static string GetExcelCellName(int fColIndex, int fRowIndex) { if (fColIndex <= 0 || fColIndex > 256) { throw new Exception("Excel 列索引數值超出範圍(1-256)!"); } else if (fColIndex <= 26) { return GetExcelCellName(fColIndex) + fRowIndex.ToString(); } else { string retLetter = GetExcelCellName(fColIndex / 26); retLetter += GetExcelCellName(fColIndex % 26); retLetter += fRowIndex.ToString(); return retLetter; } } private static string GetExcelCellName(int fColIndex) { int i = 1; foreach (string letter in Enum.GetNames(typeof(ExcelColumnLetters))) { if (i == fColIndex) return letter; i++; } throw new Exception("Excel 列索引數值超出範圍(1-256)!"); } #endregion } #region " ExcelColumnLetters " public enum ExcelColumnLetters { A = 1, B = 2, C = 3, D = 4, E = 5, F = 6, G = 7, H = 8, I = 9, J = 10, K = 11, L = 12, M = 13, N = 14, O = 15, P = 16, Q = 17, R = 18, S = 19, T = 20, U = 21, V = 22, W = 23, X = 24, Y = 25, Z = 26 } #endregion #region " DataColumnExportOptions " public class DataColumnExportOptions { private String fColumnName; private String fCaption; private Boolean fVisible; private DateTime fDatas; public String ColumnName { get { return fColumnName; } set { fColumnName = value; } } public String Caption { get { return fCaption; } set { fCaption = value; } } public Boolean Visible { get { return fVisible; } set { fVisible = value; } } public DateTime Datas { get { return fDatas; } set { fDatas = value; } } public DataColumnExportOptions(String fColumnName) : this(fColumnName, fColumnName) { } public DataColumnExportOptions(String fColumnName, String fCaption) : this(fColumnName, fCaption, true) { } public DataColumnExportOptions(String fColumnName, String fCaption, Boolean fVisible) { this.fColumnName = fColumnName; this.fCaption = fCaption; this.fVisible = fVisible; } public DataColumnExportOptions(String fColumnName, String fCaption, Boolean fVisible, DateTime fDatas) { this.fColumnName = fColumnName; this.fCaption = fCaption; this.fVisible = fVisible; this.fDatas = fDatas; } } #endregion #region " DataTableExportOptions " public class DataTableExportOptions { private DataTable fDataTable; private List<DataColumnExportOptions> fColumnOptions; private List<DataColumnExportOptions> fVisibleColumnOptions; private String fWorkSheetName; public DataTable DataTable { get { return fDataTable; } set { fDataTable = value; } } public List<DataColumnExportOptions> ColumnOptions { get { return fColumnOptions; } set { fColumnOptions = value; } } public String WorkSheetName { get { return fWorkSheetName; } set { fWorkSheetName = value; } } public List<DataColumnExportOptions> VisibleColumnOptions { get { return fVisibleColumnOptions; } } public DataTableExportOptions(DataTable fDataTable) : this(fDataTable, null) { } public DataTableExportOptions(DataTable fDataTable, List<DataColumnExportOptions> fColumnOptions) : this(fDataTable, fColumnOptions, null) { } public DataTableExportOptions(DataTable fDataTable, List<DataColumnExportOptions> fColumnOptions, String fWorkSheetName) { if (fDataTable == null) return; this.fDataTable = fDataTable; if (fColumnOptions == null) { this.fColumnOptions = new List<DataColumnExportOptions>(); foreach (DataColumn dataColumn in fDataTable.Columns) this.fColumnOptions.Add(new DataColumnExportOptions(dataColumn.ColumnName)); } else { this.fColumnOptions = fColumnOptions; } if (String.IsNullOrEmpty(fWorkSheetName)) this.fWorkSheetName = fDataTable.TableName; else this.fWorkSheetName = fWorkSheetName; fVisibleColumnOptions = new List<DataColumnExportOptions>(); foreach (DataColumnExportOptions option in this.fColumnOptions) { if (option.Visible) fVisibleColumnOptions.Add(option); } } } #endregion}
調用方法
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
