Asp.net操作Excel更轻松的实现代码_asp.net教程-查字典教程网
Asp.net操作Excel更轻松的实现代码
Asp.net操作Excel更轻松的实现代码
发布时间:2016-12-29 来源:查字典编辑
摘要:1.操作Excel的动态链接库2.建立操作动态链接库的共通类,方便调用。(ExcelHelper)具体如下:复制代码代码如下:usingSy...

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教程学习
    热门asp.net教程学习
    编程开发子分类