Notes

using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using NPOI.SS.UserModel;
using System.IO;


///<summary>
///使用NPOI方式將DataTable匯出至Excel
///</summary>
///<param name="dt">DataTable</param>
///<param name="connString">Excel連線字串</param>
///<returns></returns>
public static void DataTableToExcel(DataTable dt, String FileName, String Title)
{
int tColumnCount = dt.Columns.Count;
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(FileName);
//顯示 所有資料列
int rowIndex = 0;
IFont fontHeader = (HSSFFont)workbook.CreateFont();
ICellStyle HeaderStyle = workbook.CreateCellStyle();
HeaderStyle.WrapText = false;
HeaderStyle.Alignment = HorizontalAlignment.CENTER;
//背景顏色
HeaderStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index;
HeaderStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
//以下為設定粗體字
fontHeader.Boldweight = (short)FontBoldWeight.BOLD;
fontHeader.FontHeightInPoints = (short)Convert.ToUInt32((12));
fontHeader.FontName = "細明體";
HeaderStyle.SetFont(fontHeader);
if (Title != "")
{
IRow titleRow = sheet.CreateRow(rowIndex);
titleRow.CreateCell(0);
ICell cell = titleRow.CreateCell(0);
cell.SetCellValue(Title);
rowIndex++;
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeight = 20 * 20;
style.SetFont(font);
cell.CellStyle = style;

sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, tColumnCount-1));
}


//顯示 Table 0 的所有欄位名稱
IRow headerRow = sheet.CreateRow(rowIndex);
rowIndex++;
foreach (DataColumn column in dt.Columns)
{
ICell headecell = headerRow.CreateCell(column.Ordinal);
headecell.SetCellValue(column.ColumnName);
headecell.CellStyle = HeaderStyle;
//自動Width
//sheet.AutoSizeColumn(column.Ordinal);
}

foreach (DataRow row in dt.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
dataRow = null;
rowIndex++;
}

foreach (DataColumn column in dt.Columns)
{
//自動Width
sheet.AutoSizeColumn(column.Ordinal);
}

HttpContext.Current.Response.Clear();

// 產生 Excel 資料流
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
headerRow = null;
sheet = null;
workbook = null;

HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.HeaderEncoding = System.Text.Encoding.Default;

// 設定強制下載標頭
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + FileName + ".xls"));
// 輸出檔案
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
HttpContext.Current.Response.End();
}