1 /// <summary>
2 /// 出力Excel
3 /// </summary>
4 /// <param name="storeModelForExcel"></param>
5 public void GenerateExcel(List<ExcelModel> excelModelList,
string savePath)
6 {
7 Microsoft.Office.Interop.Excel.Application excel =
new Microsoft.Office.Interop.Excel.Application();
8 if (excel ==
null)
9 {
10 return;
11 }
12 //设置为不可见，操作在后台执行，为 true 的话会打开 Excel
13 excel.Visible =
false;
14 //打开时设置为全屏显式
15 //excel.DisplayFullScreen = true;
16 //初始化工作簿
17 Microsoft.Office.Interop.Excel.Workbooks workbooks =
excel.Workbooks;
18 //新增加一个工作簿，Add（）方法也可以直接传入参数 true
19 Microsoft.Office.Interop.Excel.Workbook workbook =
workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
20 var tt =
workbook.Worksheets.Count;
21 try
22 {
23 for (
int sheetNum =
0; sheetNum < excelModelList.Count; sheetNum++
)
24 {
25 Microsoft.Office.Interop.Excel.Range range;
26 for (
int k =
0; k < tt; k++
)
27 {
28 //设置表的名称
29 Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[k +
1];
30 worksheet.Name =
excelModelList[sheetNum].dt.TableName;
31 range = worksheet.get_Range(
"A1", System.Reflection.Missing.Value);
32 range.NumberFormat =
"@";
33 range = range.get_Resize(excelModelList[sheetNum].dt.Rows.Count +
1, excelModelList[sheetNum].dt.Columns.Count);
34 string[,] saRet =
new string[excelModelList[sheetNum].dt.Rows.Count +
1, excelModelList[sheetNum].dt.Columns.Count];
35 //设置列名
36 for (
int i =
0; i < excelModelList[sheetNum].dt.Columns.Count; i++
)
37 {
38 //设置第一行，即列名
39 saRet[
0, i] =
excelModelList[sheetNum].dt.Columns[i].ColumnName;
40 }
41 //跳过第一行，第一行写入了列名
42 int rowIndex =
1;
43 //写入数据
44 for (
int i =
0; i < excelModelList[sheetNum].dt.Rows.Count; i++
)
45 {
46 for (
int j =
0; j < excelModelList[sheetNum].dt.Columns.Count; j++
)
47 {
48 saRet[rowIndex + i, j] =
excelModelList[sheetNum].dt.Rows[i][j].ToString();
49 }
50 }
51 range.set_Value(System.Reflection.Missing.Value, saRet);
52 //设置所有列宽为自动列宽
53 //worksheet.Columns.AutoFit();
54 //设置所有单元格列宽为自动列宽
55 worksheet.Cells.Columns.AutoFit();
56 }
57 //新增加一个 Excel 表(sheet)
58 if (sheetNum < excelModelList.Count -
1)
59 {
60 workbook.Worksheets.Add();
61 }
62 }
63 //是否提示，如果想删除某个sheet页，首先要将此项设为fasle。
64 excel.DisplayAlerts =
false;
65 //保存写入的数据，这里还没有保存到磁盘
66 workbook.Saved =
true;
67 ////创建文件
68 FileStream file =
new FileStream(savePath, FileMode.CreateNew);
69 //关闭释放流，不然没办法写入数据
70 file.Close();
71 file.Dispose();
72 ////保存到指定的路径
73 workbook.SaveCopyAs(savePath);
74 workbook.Close(Type.Missing, Type.Missing, Type.Missing);
75 excel.Quit();
76 //关闭进程，防止启动多个，导致内存溢出
77 IntPtr t =
new IntPtr(excel.Hwnd);
78 int processId =
0;
79 GetWindowThreadProcessId(t,
out processId);
80 System.Diagnostics.Process p =
System.Diagnostics.Process.GetProcessById(processId);
81 p.Kill();
82 }
83 catch (Exception e)
84 {
85 throw e;
86 }
87 }
88
89 [DllImport(
"User32.dll", CharSet =
CharSet.Auto)]
90 public static extern int GetWindowThreadProcessId(IntPtr hwnd,
out int ID);
注意：数据先写到内存saRet，在一次性写进sheet
range.set_Value(System.Reflection.Missing.Value, saRet);
转载于:https://www.cnblogs.com/dehuachenyunfei/p/exportExcel.html
相关资源：数据结构—成绩单生成器
