大庆城市建设投资网站,怎么做国际货运代理外贸网站,上海网站域名备案处,wordpress是否被收录最近项目里面有个周报Excel导出的功能#xff0c;为了解决这个问题#xff0c;我显示调研Excel内核的方式实现了#xff0c;但是被告知该方法有诸多弊端#xff08;1、服务器需要装相应版本的Excel#xff1b;2、如果程序中途出错服务器会有很多Excel进程#xff09;为了解决这个问题我显示调研Excel内核的方式实现了但是被告知该方法有诸多弊端1、服务器需要装相应版本的Excel2、如果程序中途出错服务器会有很多Excel进程最后用得aspose.cells第三方控件的方式完成。 一、Excel内核方式实现 该方法首先需要添加引用Microsoft.Office.Interop.Excel;然后添加同名的using。具体数据插入的逻辑可以忽略代码如下 View Code 1 /// Excel导出2 /// /summary3 /// param nametArys数据源/param4 /// param nametempletFileName模板地址/param5 /// param namereportFileName导出文件临时地址/param6 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 //打开excel19 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代表第二个sheet31 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 /// summary2 /// 生成Excel到指定目录3 /// /summary4 /// param nametArys/param5 /// param nameregulardatetext/param6 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 } 转载于:https://www.cnblogs.com/chuanzhifeng/archive/2012/11/13/2768102.html
