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