Asp.net操作Excel更轻松

标签： asp net excel | 发表时间：2011-10-14 11:18 | 作者：WILLPAN Bloger

出处：http://www.cnblogs.com/

今天先介绍一个关于导出数据的例子，以Excel为模板。直接进入正题了。

1.操作Excel的动态链接库(暂时没有下载地址，稍后提供)

2.建立操作动态链接库的共通类，方便调用。（ExcelHelper）

具体如下：

View Code

  1 using System;
  2 using System.Data;
  3 using System.Configuration;
  4 using System.Linq;
  5 using System.Web;
  6 using System.Web.Security;
  7 using System.Web.UI;
  8 using System.Web.UI.HtmlControls;
  9 using System.Web.UI.WebControls;
 10 using System.Web.UI.WebControls.WebParts;
 11 using System.Xml.Linq;
 12 using System.IO;
 13 using System.Reflection;
 14 using System.Diagnostics;
 15 using System.Collections;
 16 
 17 /// <summary>
 18 ///ExcelHelper 的摘要说明
 19 /// </summary>
 20 public class ExcelHelper
 21 {
 22     private string reportModelPath = null;
 23     private string outPutFilePath = null;
 24     private object missing = Missing.Value;
 25     Excel.Application app;
 26     Excel.Workbook workBook;
 27     Excel.Worksheet workSheet;
 28     Excel.Range range;
 29 
 30     /// <summary>
 31     /// 获取或设置报表模板路径
 32     /// </summary>
 33     public string ReportModelPath
 34     {
 35         get { return reportModelPath; }
 36         set { reportModelPath = value; }
 37     }
 38 
 39     /// <summary>
 40     /// 获取或设置输出路径
 41     /// </summary>
 42     public string OutPutFilePath
 43     {
 44         get { return outPutFilePath; }
 45         set { outPutFilePath = value; }
 46     }
 47 
 48 
 49     public ExcelHelper()
 50     {
 51         //
 52         //TODO: 在此处添加构造函数逻辑
 53         //
 54     }
 55 
 56     /// <summary>
 57     /// 带参ExcelHelper构造函数
 58     /// </summary>
 59     /// <param name="reportModelPath">报表模板路径</param>
 60     /// <param name="outPutFilePath">输出路径</param>
 61     public ExcelHelper(string reportModelPath, string outPutFilePath)
 62     {
 63         //路径验证
 64         if (null == reportModelPath || ("").Equals(reportModelPath))
 65             throw new Exception("报表模板路径不能为空！");
 66         if (null == outPutFilePath || ("").Equals(outPutFilePath))
 67             throw new Exception("输出路径不能为空！");
 68         if (!File.Exists(reportModelPath))
 69             throw new Exception("报表模板路径不存在！");
 70 
 71         //设置路径值
 72         this.ReportModelPath = reportModelPath;
 73         this.OutPutFilePath = outPutFilePath;
 74 
 75         //创建一个应用程序对象
 76         app = new Excel.ApplicationClass();
 77        
 78         //打开模板文件，获取WorkBook对象
 79         workBook = app.Workbooks.Open(reportModelPath, missing, missing, missing, missing, missing, missing, 
 80                                       missing, missing, missing, missing, missing, missing);
 81 
 82         //得到WorkSheet对象
 83         workSheet = workBook.Sheets.get_Item(1) as Excel.Worksheet;
 84     }
 85 
 86     /// <summary>
 87     /// 给单元格设值
 88     /// </summary>
 89     /// <param name="rowIndex">行索引</param>
 90     /// <param name="colIndex">列索引</param>
 91     /// <param name="content">填充的内容</param>
 92     public void SetCells(int rowIndex,int colIndex,object content)
 93     {
 94         if (null != content)
 95         {
 96             content = content.ToString();
 97         }
 98         else
 99         {
100             content = string.Empty;
101         }
102 
103         try
104         {
105             workSheet.Cells[rowIndex, colIndex] = content;
106         }
107         catch
108         {
109             GC();
110             throw new Exception("向单元格[" + rowIndex + "," + colIndex + "]写数据出错！");
111         }
112     }
113 
114     /// <summary>
115     /// 保存文件
116     /// </summary>
117     public void SaveFile()
118     {
119         try
120         {
121             workBook.SaveAs(outPutFilePath, missing, missing, missing, missing, missing,
122                 Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
123         }
124         catch
125         {
126             throw new Exception("保存至文件失败！");
127         }
128         finally
129         {
130             Dispose();
131         }
132     }
133 
134     /// <summary>
135     /// 垃圾回收处理
136     /// </summary>
137     protected void GC()
138     {
139         if (null != app)
140         {
141             int generation = 0;
142             System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
143 
144             generation = System.GC.GetGeneration(app);
145             System.GC.Collect(generation);
146             app = null;
147             missing = null;
148         }
149     }
150 
151     /// <summary>
152     /// 释放资源
153     /// </summary>
154     protected void Dispose()
155     {
156         workBook.Close(null, null, null);
157         app.Workbooks.Close();
158         app.Quit();
159 
160         if (null != workSheet)
161         {
162             System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
163             workSheet = null;
164         }
165         if (workBook != null)
166         {
167             System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
168             workBook = null;
169         }
170         if (app != null)
171         {
172             int generation = 0;
173             System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
174             generation = System.GC.GetGeneration(app);
175             System.GC.Collect(generation);
176             app = null;
177             missing = null;
178         }
179     }
180 }

通过ExcelHelper类提供的SetCells()和SaveFile()方法可以给Excel单元格赋值并保存到临时文件夹内。仅供参考。

3.调用

因为这里需要用到导出模板，所以需要先建立模板。具体如下：、

View Code

 1     /// <summary>
 2     /// 导出数据
 3     /// </summary>
 4     protected void Export_Data()
 5     {
 6         int ii = 0;
 7         //取得报表模板文件路径
 8         string reportModelPath = HttpContext.Current.Server.MapPath("ReportModel/导出订单模板.csv");
 9         //导出报表文件名
10         fileName = string.Format("{0}-{1}{2}.csv", "导出订单明细", DateTime.Now.ToString("yyyyMMdd"), GetRndNum(3));
11         //导出文件路径
12         string outPutFilePath = HttpContext.Current.Server.MapPath("Temp_Down/" + fileName);
13         //创建Excel对象
14         ExcelHelper excel = new ExcelHelper(reportModelPath, outPutFilePath);
15 
16         SqlDataReader sdr = Get_Data();
17         while (sdr.Read())
18         {
19             ii++;
20             excel.SetCells(1 + ii, 1, ii);
21             excel.SetCells(1 + ii, 2, sdr["C_Name"]);
22             excel.SetCells(1 + ii, 3, sdr["C_Mtel"]);
23             excel.SetCells(1 + ii, 4, sdr["C_Tel"]);
24             excel.SetCells(1 + ii, 5, sdr["C_Province"]);
25             excel.SetCells(1 + ii, 6, sdr["C_Address"]);
26             excel.SetCells(1 + ii, 7, sdr["C_Postcode"]);
27         }
28         sdr.Close();
29         excel.SaveFile();
30     }

关于导出就简单写到这，另外下一节讲介绍如何通过这个类库上传Excel文件。

--------------------------------------------欢迎交流，转载请注明出处---------------------------------------------------------------------

作者: WILLPAN 发表于 2011-10-14 11:18 原文链接

评论: 6　查看评论　发表评论

最新新闻：
· iOS 5 总览(2011-10-14 14:11)
· Siri 的人格测试：与 Siri 的普鲁斯特问答。(2011-10-14 13:45)
· 力拼苹果三星正开发下一代Bada系统(2011-10-14 13:44)
· iOS 5不完美越狱教程（含视频）(2011-10-14 13:41)
· 20个使用JavaScript内容滑动展示效果的Web设计(2011-10-14 13:39)

编辑推荐：最老程序员创业札记：全文检索、数据挖掘、推荐引擎应用46

网站导航：博客园首页我的园子新闻闪存小组博问知识库

PHP导出excel

- syeye - scofield PHP开发-SEO SEM

最近做一个项目，其中涉及到了数据导成excel的功能. 后来使用了开源的 PHPExcel http://phpexcel.codeplex.com/ 目前最新版是1.7.6. PHPExcel 可以生成 .xls 和 .xlsx （office2007）. 比如设置 excel的title，keywords,description.

Excel 数据分析

- - ITeye博客

用Excel做数据分析——直方图. 已有 0 人发表留言，猛击->> 这里<<-参与讨论. —软件人才免语言低担保赴美带薪读研.

Debugging .NET Core on Linux with LLDB | RayDBG

- -

The LLDB debugger is conceptually similar to the native Windows debugging tools in that it is a low level and command live driven debugger. Part of the reason the .NET Core team chose the LLDB debugger was for its extensibility points that allowed them to create the SOS plugin which can be used to debug .NET core applications.

Asp.net操作Excel更轻松

相关 [asp net excel] 推荐：

PHP导出excel

Excel 数据分析

Debugging .NET Core on Linux with LLDB | RayDBG

Excel高级使用技巧

学做专业EXCEL图表

Asp.net操作Excel更轻松

Excel项目管理工具

POI Excel导入导出 - milan's

从Excel到微服务

Microsoft .NET Gadgeteer 简介及其它

相关文章

订阅