C# 動態產生Excel，可實現凍結視窗等其他Excel擴充

 

  /// <summary>
/// 需先引用Office的Excel組件Microsoft.Office.Interop.Excel
/// using Microsoft.Office.Interop.Excel;
/// 匯出Excel，樣本：ExportDataTable(DT, @"D:\測試Excel");
/// 作者：若非輕寒 Email：ooofcu@hotmail.com
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="filename">要儲存的檔案路徑</param>
/// <param name="sRangeIndex">參數："1"預設；"2"凍結第一行；"3"凍結前兩行，或者更大的數</param>
public static void ExportDataTable(System.Data.DataTable dt, string filename, string sRangeIndex)
{
if (filename != "")
{
if (filename.LastIndexOf(".xls") <= 0)
{
filename = filename + ".xls";
}
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (xlApp == null)
{
//無法建立Excel對象，可能您的機器未安裝Excel。
return;
}

Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];

for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (i == 0)
{
worksheet.Cells[1, j + 1] = dt.Columns[j].ColumnName;
}
worksheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}

//------------實現 Excel凍結視窗 功能---------------
Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet worksheetA = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range rangeTemp = worksheetA.get_Range("A" + sRangeIndex, "B4"); //A2即從第二行以上進行凍結
rangeTemp.Select();
xlApp.ActiveWindow.FreezePanes = true;
//--------------------------------------------------

workbook.Saved = true;
workbook.SaveCopyAs(filename);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;

}
}