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

答案：1.操作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

上一个：asp.net 通过UserAgent判断智能设备(Android,IOS)
下一个：排除JQuery通过HttpGet调用WebService返回Json时“parserror”错误