最近项目里面有个周报Excel导出的功能，为了解决这个问题，我显示调研Excel内核的方式实现了，但是被告知该方法有诸多弊端（1、服务器需要装相应版本的Excel；2、如果程序中途出错服务器会有很多Excel进程）；最后用得aspose.cells第三方控件的方式完成。
一、Excel内核方式实现
该方法首先需要添加引用Microsoft.Office.Interop.Excel;然后添加同名的using。具体数据插入的逻辑可以忽略，代码如下
View Code
1 /// Excel导出 2 /// </summary> 3 /// <param name="tArys">数据源</param> 4 /// <param name="templetFileName">模板地址</param> 5 /// <param name="reportFileName">导出文件临时地址</param> 6 public static void ExportWeeklyReport(object[] tArys, string templetFileName, string reportFileName) 7 { 8 //模板文件 9 string TempletFileName = templetFileName; 10 //导出文件 11 string ReportFileName = reportFileName; 12 13 string strTempletFile = Path.GetFileName(TempletFileName); 14 //将模板文件复制到输出文件 15 FileInfo mode = new FileInfo(TempletFileName); 16 mode.CopyTo(ReportFileName, true); 17 18 //打开excel 19 object missing = Missing.Value; 20 Application app = null; 21 Workbook wb = null; 22 Worksheet ws = null; 23 Range r = null; 24 // 25 app = new Microsoft.Office.Interop.Excel.Application(); 26 wb = app.Workbooks.Open(ReportFileName, false, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); 27 app.Visible = true; 28 29 //得到WorkSheet对象 30 ws = (Worksheet)wb.Worksheets.get_Item(2);//2代表第二个sheet 31 32 ////添加或修改WorkSheet里的数据 33 34 //定义一些需要计算的变量 35 int stafforganizedTotal = 0; 36 int staffonguardTotal = 0; 37 int staffontripTotal = 0; 38 int stafftemporaryTotal = 0; 39 string requiredtime = string.Empty; 40 41 ///////////////////////把数据写到Excel////////////////////// 42 if (tArys != null) 43 { 44 for (int tis = 0; tis < tArys.Length; tis++) 45 { 46 47 object[,] tAry = (object[,])tArys[tis]; 48 for (int ti = 0; ti < tAry.GetLength(0); ti++) 49 { 50 string name = cls.toString(tAry[ti, 0]); 51 switch (name) 52 { 53 case "stafforganized": 54 stafforganizedTotal += cls.getNum(cls.toString(tAry[ti, 1])); 55 break; 56 case "staffonguard": 57 staffonguardTotal += cls.getNum(cls.toString(tAry[ti, 1])); 58 break; 59 case "staffontrip": 60 staffontripTotal += cls.getNum(cls.toString(tAry[ti, 1])); 61 break; 62 case "stafftemporary": 63 stafftemporaryTotal += cls.getNum(cls.toString(tAry[ti, 1])); 64 break; 65 case "requiredtime": 66 requiredtime = cls.toString(tAry[ti, 1]); 67 break; 68 case "content": 69 ws.Cells[10 + tis, 5] = cls.toString(tAry[ti, 1]); 70 break; 71 case "plan": 72 ws.Cells[25 + tis, 5] = cls.toString(tAry[ti, 1]); 73 break; 74 case "responsibleperson": 75 ws.Cells[25 + tis, 12] = cls.toString(tAry[ti, 1]); 76 break; 77 default: 78 break; 79 } 80 81 } 82 83 } 84 ws.Cells[6, 6] = stafforganizedTotal; 85 ws.Cells[6, 8] = staffonguardTotal; 86 ws.Cells[6, 10] = staffontripTotal; 87 ws.Cells[6, 12] = stafftemporaryTotal; 88 ws.Cells[4, 11] = requiredtime; 89 } 90 91 //////////////////////////////////////////////////////////// 92 93 //输出Excel文件并退出 94 wb.Save(); 95 wb.Close(null, null, null); 96 app.Workbooks.Close(); 97 app.Application.Quit(); 98 app.Quit(); 99 100 System.Runtime.InteropServices.Marshal.ReleaseComObject(ws); 101 System.Runtime.InteropServices.Marshal.ReleaseComObject(wb); 102 System.Runtime.InteropServices.Marshal.ReleaseComObject(app); 103 104 ws = null; 105 wb = null; 106 app = null; 107 108 109 }
二、aspose.cell方式实现
该方法需要第三方控件的支持，可以在csdn上直接搜索下载，也可以购买。官网上有很多例子，我这个只是最最基础的。具体代码如下。（记得加引用后加using）
View Code
1 /// <summary> 2 /// 生成Excel到指定目录 3 /// </summary> 4 /// <param name="tArys"></param> 5 /// <param name="regulardatetext"></param> 6 public static void ExportWeeklyReport(object[] tArys, string templetFileName, string reportFileName) 7 { 8 9 10 /////////////////参考Aspose.Cells官网的例子http://www.aspose.com/demos/.net-components/aspose.cells/csharp/quick-start/data/hello-world.aspx 11 12 //打开excel 13 14 Workbook wb = new Workbook(templetFileName);//打开对应地址的excel模板 15 Worksheet ws = wb.Worksheets[1];//第二个sheet，从0开始 16 Cells cells = ws.Cells;//获取对应的cells 的引用 17 18 ////定义几个需要计算的变量 19 int stafforganizedTotal = 0; 20 int staffonguardTotal = 0; 21 int staffontripTotal = 0; 22 int stafftemporaryTotal = 0; 23 string requiredtime = string.Empty; 24 25 ///////////////////////把数据写到Excel////////////////////// 26 if (tArys != null) 27 { 28 for (int tis = 0; tis < tArys.Length; tis++) 29 { 30 31 object[,] tAry = (object[,])tArys[tis]; 32 for (int ti = 0; ti < tAry.GetLength(0); ti++) 33 { 34 string name = cls.toString(tAry[ti, 0]); 35 switch (name) 36 { 37 case "stafforganized": 38 stafforganizedTotal += cls.getNum(cls.toString(tAry[ti, 1])); 39 break; 40 case "staffonguard": 41 staffonguardTotal += cls.getNum(cls.toString(tAry[ti, 1])); 42 break; 43 case "staffontrip": 44 staffontripTotal += cls.getNum(cls.toString(tAry[ti, 1])); 45 break; 46 case "stafftemporary": 47 stafftemporaryTotal += cls.getNum(cls.toString(tAry[ti, 1])); 48 break; 49 case "requiredtime": 50 requiredtime = cls.toString(tAry[ti, 1]); 51 break; 52 case "content": 53 cells[9 + tis, 4].PutValue(cls.toString(tAry[ti, 1]), true); 54 break; 55 case "plan": 56 cells[24 + tis, 4].PutValue(cls.toString(tAry[ti, 1]), true); 57 break; 58 case "responsibleperson": 59 cells[24 + tis, 11].PutValue(cls.toString(tAry[ti, 1]), true); 60 break; 61 default: 62 break; 63 } 64 65 } 66 67 } 68 cells[5, 5].PutValue(stafforganizedTotal); 69 cells[5, 7].PutValue(staffonguardTotal); 70 cells[5, 9].PutValue(staffontripTotal); 71 cells[5, 11].PutValue(stafftemporaryTotal); 72 cells[3, 10].PutValue(requiredtime); 73 74 } 75 76 //////////////////////////////////////////////////////////// 77 78 79 //保存到相应的路径 80 wb.Save(reportFileName); 81 82 83 84 }
