Asp.net操作Excel更轻松的实现代码

时间:2022-09-17 23:35:33

1.操作Excel的动态链接库 
Asp.net操作Excel更轻松的实现代码
2.建立操作动态链接库的共通类,方便调用。(ExcelHelper) 
具体如下: 

复制代码代码如下:


using System; 
using System.Data; 
using System.Configuration; 
using System.Linq; 
using System.Web; 
using System.Web.Security; 
using System.Web.UI; 
using System.Web.UI.HtmlControls; 
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts; 
using System.Xml.Linq; 
using System.IO; 
using System.Reflection; 
using System.Diagnostics; 
using System.Collections; 
/// <summary> 
///ExcelHelper 的摘要说明 
/// </summary> 
public class ExcelHelper 

private string reportModelPath = null; 
private string outPutFilePath = null; 
private object missing = Missing.Value; 
Excel.Application app; 
Excel.Workbook workBook; 
Excel.Worksheet workSheet; 
Excel.Range range; 
/// <summary> 
/// 获取或设置报表模板路径 
/// </summary> 
public string ReportModelPath 

get { return reportModelPath; } 
set { reportModelPath = value; } 

/// <summary> 
/// 获取或设置输出路径 
/// </summary> 
public string OutPutFilePath 

get { return outPutFilePath; } 
set { outPutFilePath = value; } 

public ExcelHelper() 

// 
//TODO: 在此处添加构造函数逻辑 
// 

/// <summary> 
/// 带参ExcelHelper构造函数 
/// </summary> 
/// <param name="reportModelPath">报表模板路径</param> 
/// <param name="outPutFilePath">输出路径</param> 
public ExcelHelper(string reportModelPath, string outPutFilePath) 

//路径验证 
if (null == reportModelPath || ("").Equals(reportModelPath)) 
throw new Exception("报表模板路径不能为空!"); 
if (null == outPutFilePath || ("").Equals(outPutFilePath)) 
throw new Exception("输出路径不能为空!"); 
if (!File.Exists(reportModelPath)) 
throw new Exception("报表模板路径不存在!"); 
//设置路径值 
this.ReportModelPath = reportModelPath; 
this.OutPutFilePath = outPutFilePath; 
//创建一个应用程序对象 
app = new Excel.ApplicationClass(); 
//打开模板文件,获取WorkBook对象 
workBook = app.Workbooks.Open(reportModelPath, missing, missing, missing, missing, missing, missing, 
missing, missing, missing, missing, missing, missing); 
//得到WorkSheet对象 
workSheet = workBook.Sheets.get_Item(1) as Excel.Worksheet; 

/// <summary> 
/// 给单元格设值 
/// </summary> 
/// <param name="rowIndex">行索引</param> 
/// <param name="colIndex">列索引</param> 
/// <param name="content">填充的内容</param> 
public void SetCells(int rowIndex,int colIndex,object content) 

if (null != content) 

content = content.ToString(); 

else 

content = string.Empty; 

try 

workSheet.Cells[rowIndex, colIndex] = content; 

catch 

GC(); 
throw new Exception("向单元格[" + rowIndex + "," + colIndex + "]写数据出错!"); 


/// <summary> 
/// 保存文件 
/// </summary> 
public void SaveFile() 

try 

workBook.SaveAs(outPutFilePath, missing, missing, missing, missing, missing, 
Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing); 

catch 

throw new Exception("保存至文件失败!"); 

finally 

Dispose(); 


/// <summary> 
/// 垃圾回收处理 
/// </summary> 
protected void GC() 

if (null != app) 

int generation = 0; 
System.Runtime.InteropServices.Marshal.ReleaseComObject(app); 
generation = System.GC.GetGeneration(app); 
System.GC.Collect(generation); 
app = null; 
missing = null; 


/// <summary> 
/// 释放资源 
/// </summary> 
protected void Dispose() 

workBook.Close(null, null, null); 
app.Workbooks.Close(); 
app.Quit(); 
if (null != workSheet) 

System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); 
workSheet = null; 

if (workBook != null) 

System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); 
workBook = null; 

if (app != null) 

int generation = 0; 
System.Runtime.InteropServices.Marshal.ReleaseComObject(app); 
generation = System.GC.GetGeneration(app); 
System.GC.Collect(generation); 
app = null; 
missing = null; 



通过ExcelHelper类提供的SetCells()和SaveFile()方法可以给Excel单元格赋值并保存到临时文件夹内。仅供参考。 
3.调用 
因为这里需要用到导出模板,所以需要先建立模板。具体如下:、 

复制代码代码如下:


/// <summary> 
/// 导出数据 
/// </summary> 
protected void Export_Data() 

int ii = 0; 
//取得报表模板文件路径 
string reportModelPath = HttpContext.Current.Server.MapPath("ReportModel/导出订单模板.csv"); 
//导出报表文件名 
fileName = string.Format("{0}-{1}{2}.csv", "导出订单明细", DateTime.Now.ToString("yyyyMMdd"), GetRndNum(3)); 
//导出文件路径 
string outPutFilePath = HttpContext.Current.Server.MapPath("Temp_Down/" + fileName); 
//创建Excel对象 
ExcelHelper excel = new ExcelHelper(reportModelPath, outPutFilePath); 

SqlDataReader sdr = Get_Data(); 
while (sdr.Read()) 

ii++; 
excel.SetCells(1 + ii, 1, ii); 
excel.SetCells(1 + ii, 2, sdr["C_Name"]); 
excel.SetCells(1 + ii, 3, sdr["C_Mtel"]); 
excel.SetCells(1 + ii, 4, sdr["C_Tel"]); 
excel.SetCells(1 + ii, 5, sdr["C_Province"]); 
excel.SetCells(1 + ii, 6, sdr["C_Address"]); 
excel.SetCells(1 + ii, 7, sdr["C_Postcode"]); 

sdr.Close(); 
excel.SaveFile(); 


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