asp.net操作Excel(向excel模板添加数据)

  声明:本随笔转载自http://www.cnblogs.com/peaceli/archive/2008/04/13/1151520.html。由于上周要实现关于asp.net操作Excel模板得效果,在网上搜了一下,感觉这篇文章还不错,帮了很大忙。在此表示感谢,并转载已共享。

 一. 程序操作EXCEL的应用主要还是在统计报表方面,您可能会考虑读EXCEL模板,也可能会考虑没必要读模板,其实读不读模板都能达到一样的效果,看实际情况而用了。
       1. 读模板的话,首先模板存放在某个路径下,根据模板把从数据库里取出的数据写回EXCEL然后生成一个新的EXCEL存放都另一个路径以供下载,模板不变.vs08/05中操作EXCEL直接引用.NET自带的COM组件 ,添加后项目的bin目录下会自动出现
     

Interop.Excel.dll这个DLL页面的命名空间引用 using Excel;
下面是调用模板的一段代码:

#region 使用模板导出Excel表
 2                 case "ReportByTemp":
 3                     {
 4 
 5                         DataView dv = Cache["ReportByTemp"as DataView;
 6                         //建立一个Excel.Application的新进程
 7                         Excel.Application app = new Excel.Application();
 8                         if (app == null)
 9                         {
10                             return;
11                         }
12                         app.Visible = false;
13                         app.UserControl = true;
14                         Workbooks workbooks = app.Workbooks;
15                         _Workbook workbook = workbooks.Add(template_path + "\\EXCEL测试模板.xls");//这里的Add方法里的参数就是模板的路径
16                         Sheets sheets = workbook.Worksheets;
17                         _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);//模板只有一个sheet表
18                         if (worksheet == null)
19                         {
20                             return;
21                         }
22 
23                         int rowNum = 0;
24                         for (int i = 0; i < dv.Count; i++)
25                         {
26                             rowNum = i + 1;
27                             worksheet.Cells[3 + i, 1= rowNum;
28                             worksheet.Cells[3 + i, 2= dv[i].Row[0].ToString();
29                             worksheet.Cells[3 + i, 3= dv[i].Row[1].ToString();
30 
31                             excelOperate.SetBold(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 1]); //黑体
32                             excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]);//居中
33                             worksheet.get_Range(worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
34 
35                         }
36 
37                         tick = DateTime.Now.Ticks.ToString();
38                         save_path = temp_path + "\\" + tick + ".xls";
39                         workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
40                         excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程
41 
42                     }
43                     break;
44                 #endregion

2. 不读模板的话,调用的时候其实会继承一个空白模板,然后写入数据,程序画表头,最终达到一样的效果,

程序如下:

  #region 不使用模板生成Excel表
 2                 case "ReportByNone":
 3                     {
 4 
 5                         DataView dv = Cache["ReportByNone"as DataView;
 6                         //建立一个Excel.Application的新进程
 7                         Excel.Application app = new Excel.Application();
 8                         if (app == null)
 9                         {
10                             return;
11                         }
12                         app.Visible = false;
13                         app.UserControl = true;
14                         Workbooks workbooks = app.Workbooks;
15                         _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);//这里的Add方法里的参数就相当于继承了一个空模板(暂这样理解吧)
16                         Sheets sheets = workbook.Worksheets;
17                         _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
18                         if (worksheet == null)
19                         {
20                             return;
21                         }
22 
23                         worksheet.get_Range(worksheet.Cells[11], worksheet.Cells[13]).Merge(Missing.Value); //横向合并
24                         worksheet.get_Range(worksheet.Cells[11], worksheet.Cells[11]).Value2 = "导出EXCEL测试一";
25                         excelOperate.SetBold(worksheet, worksheet.Cells[11], worksheet.Cells[11]); //黑体
26                         excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[11], worksheet.Cells[11]);//居中
27                         excelOperate.SetBgColor(worksheet, worksheet.Cells[11], worksheet.Cells[11], System.Drawing.Color.Red);//背景色
28                         excelOperate.SetFontSize(worksheet, worksheet.Cells[11], worksheet.Cells[11], 16);//字体大小
29                         excelOperate.SetRowHeight(worksheet, worksheet.Cells[11], worksheet.Cells[11], 32.25);//行高
30                         worksheet.get_Range(worksheet.Cells[11], worksheet.Cells[11]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色连续边框
31 
32                         worksheet.Cells[21= "序号";
33                         worksheet.Cells[22= "公司";
34                         worksheet.Cells[23= "部门";
35                         excelOperate.SetBold(worksheet, worksheet.Cells[21], worksheet.Cells[23]); //黑体
36                         worksheet.get_Range(worksheet.Cells[21], worksheet.Cells[23]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
37                         excelOperate.SetHAlignRight(worksheet, worksheet.Cells[21], worksheet.Cells[23]);
38                         excelOperate.SetBgColor(worksheet, worksheet.Cells[21], worksheet.Cells[23], System.Drawing.Color.Silver);//背景色
39                         int rowNum = 0;
40                         for (int i = 0; i < dv.Count; i++)
41                         {
42                             rowNum = i + 1;
43                             worksheet.Cells[3 + i, 1= rowNum;
44                             worksheet.Cells[3 + i, 2= dv[i].Row[0].ToString();
45                             worksheet.Cells[3 + i, 3= dv[i].Row[1].ToString();
46 
47                             excelOperate.SetBold(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 1]); //黑体
48                             excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]);//居中
49                             worksheet.get_Range(worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//设置边框颜色,不然打印预览,会非常不雅观
50 
51                         }
52                         excelOperate.SetColumnWidth(worksheet, "A"10);
53                         excelOperate.SetColumnWidth(worksheet, "B"20);
54                         excelOperate.SetColumnWidth(worksheet, "C"20);
55                         worksheet.Name = "导出EXCEL测试一";
56 
57                         tick = DateTime.Now.Ticks.ToString();
58                         save_path = temp_path + "\\"+ tick + ".xls";
59                         workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
60                         excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程
61 
62                     }
63                     break;
64 
65                 #endregion
------Candy.zhai

posted @ 2009-03-22 17:33  silverPerson  阅读(2883)  评论(0编辑  收藏  举报