C# 往excel出力数据

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