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
