前置需求
- 伺服器安裝 OWC 10/11/12 (Office 2003/2007/2010 Web Components)。
- 專案加入參考:
Microsoft.Office.Interop.Owc11 (或對應版本)。
- 注意:OWC 已停止更新,微軟建議改用 Open XML SDK、EPPlus、ClosedXML、NPOI 等純 .NET 程式庫,無需安裝 Office。
完整匯出程式碼
protected void btnExport_Click(object sender, EventArgs e)
{
string connStr = ConfigurationManager.ConnectionStrings["DqpiHrConnectionString"].ConnectionString;
string sql = "SELECT 姓名, 曾用名, 出生年月, 參加工作時間, 性別, 民族, 政治面貌, 職稱, 學歷, 學位, 職務, 档案號碼, 姓氏首字母, 現從事專業, 工作單位, 身份證號 FROM EmployeeView";
DataTable dt;
using (SqlConnection conn = new SqlConnection(connStr))
using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
{
dt = new DataTable();
da.Fill(dt);
}
// OWC Spreadsheet
OWC10.SpreadsheetClass ss = new OWC10.SpreadsheetClass();
int rowOffset = 0;
foreach (DataRow dr in dt.Rows)
{
int r = rowOffset + 1; // OWC 以 1 為基底
// 標題列合併
ss.get_Range(ss.Cells[r, 1], ss.Cells[r, 8]).MergeCells = true;
ss.Cells[r, 1] = dr["姓名"] + "自然情況";
ss.get_Range(ss.Cells[r, 1], ss.Cells[r, 14]).Font.Bold = true;
ss.get_Range(ss.Cells[r, 1], ss.Cells[r, 14]).HorizontalAlignment = OWC10.XlHAlign.xlHAlignCenter;
ss.get_Range(ss.Cells[r, 1], ss.Cells[r, 14]).Font.Size = 14;
ss.get_Range(ss.Cells[r, 8], ss.Cells[r, 8]).ColumnWidth = 20;
// 邊框
ss.get_Range(ss.Cells[r, 1], ss.Cells[r + 4, 8]).Borders.LineStyle = OWC10.XlLineStyle.xlContinuous;
// 明細資料
ss.Cells[r + 1, 1] = "姓名"; ss.Cells[r + 1, 2] = dr["姓名"];
ss.Cells[r + 1, 3] = "曾用名"; ss.Cells[r + 1, 4] = dr["曾用名"];
ss.Cells[r + 1, 5] = "出生年月"; ss.Cells[r + 1, 6] = Convert.ToDateTime(dr["出生年月"]).ToString("yyyy-MM");
ss.Cells[r + 1, 7] = "參加工作時間"; ss.Cells[r + 1, 8] = Convert.ToDateTime(dr["參加工作時間"]).ToString("yyyy-MM");
ss.Cells[r + 2, 1] = "性別"; ss.Cells[r + 2, 2] = dr["性別"];
ss.Cells[r + 2, 3] = "民族"; ss.Cells[r + 2, 4] = dr["民族"];
ss.Cells[r + 2, 5] = "政治面貌"; ss.Cells[r + 2, 6] = dr["政治面貌"];
ss.Cells[r + 2, 7] = "職稱"; ss.Cells[r + 2, 8] = dr["職稱"];
ss.Cells[r + 3, 1] = "學歷"; ss.Cells[r + 3, 2] = dr["學歷"];
ss.Cells[r + 3, 3] = "學位"; ss.Cells[r + 3, 4] = dr["學位"];
ss.Cells[r + 3, 5] = "職務"; ss.Cells[r + 3, 6] = dr["職務"];
ss.Cells[r + 3, 7] = "檔案號碼"; ss.Cells[r + 3, 8] = dr["姓氏首字母"] + dr["檔案號碼"]; // 避免前導 0 消失
ss.Cells[r + 4, 1] = "現從事專業:" + dr["現從事專業"];
ss.Cells[r + 4, 4] = "工作單位:" + dr["工作單位"];
ss.Cells[r + 4, 7] = "身份證:" + dr["身份證號"];
rowOffset += 5; // 每人佔 5 列
}
// 輸出至瀏覽器
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=EmployeeData.xls");
Response.BinaryWrite((byte[])ss.GetSpreadsheetXML()); // 或 ss.SaveToFile()
Response.End();
}
現代替代方案強力推薦
| 套件 | NuGet | 特點 |
| EPPlus | EPPlus | 支援 .xlsx、樣式豐富、效能佳、無 Office 相依 |
| ClosedXML | ClosedXML | API 直覺、支援 .xlsx、MIT 授權 |
| NPOI | NPOI | 支援 .xls/.xlsx、Java POI 移植、相容性佳 |
| Open XML SDK | DocumentFormat.OpenXml | 微軟官方、底層控制、學習曲線較陡 |
EPPlus 範例 (推薦)
using (var pkg = new ExcelPackage())
{
var ws = pkg.Workbook.Worksheets.Add("員工資料");
ws.Cells.LoadFromDataTable(dt, true); // 自動建立標題與資料
ws.Cells.AutoFitColumns();
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", "attachment;filename=EmployeeData.xlsx");
Response.BinaryWrite(pkg.GetAsByteArray());
Response.End();
}