C# 动态生成Excel，可实现冻结窗口等其他Excel扩展

最新推荐文章于 2020-08-12 09:21:30 发布

转载最新推荐文章于 2020-08-12 09:21:30 发布 · 2k 阅读

C# 同时被 3 个专栏收录

52 篇文章

订阅专栏

asp.net

48 篇文章

订阅专栏

OFFICE

19 篇文章

订阅专栏

/// <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;

}
}