asp.net(C#)套用模板操作Excel

当需要输出带大量公式的Excel文档的时候在代码里写公式就太累了

用设计好的Excel模板复制一下往里面添加数据比较省事

模板

导出文件

大气象

using System;

using SystemData;

using SystemConfiguration;

using SystemWeb;

using SystemWebSecurity;

using SystemWebUI;

using SystemWebUIWebControls;

using SystemWebUIWebControlsWebParts;

using SystemWebUIHtmlControls;

using SystemIO;

using SystemReflection;

using MicrosoftOfficeInteropExcel;

public partial class _Default : SystemWebUIPage

{

protected void Page_Load(object sender EventArgs e)

{

if (!IsPostBack)

Bind();

}

private void Bind()

{

//模板文件

string TempletFileName = ServerMapPath(template/) + templatexlsx;

//导出文件

string ReportFileName = ServerMapPath(xls/) + outxlsx;

string strTempletFile = PathGetFileName(TempletFileName);

//将模板文件复制到输出文件

FileInfo mode = new FileInfo(TempletFileName);

modeCopyTo(ReportFileName true);

//打开excel

object missing = MissingValue;

Application app = null;

Workbook wb = null;

Worksheet ws = null;

Range r = null;

//

app = new MicrosoftOfficeInteropExcelApplication();

wb = appWorkbooksOpen(ReportFileName false missing missing missing missing missing missing missing missing missing missing missing missing missing);

appVisible = true;

//得到WorkSheet对象

ws = (Worksheet)wbWorksheetsget_Item();

//添加或修改WorkSheet里的数据

wsCells[ ] = ;

wsCells[ ] = ;

wsCells[ ] = ;

//代码里写个公式

r = (Range)wsCells[ ];

rFormula = =A*B;

//输出Excel文件并退出

wbSave();

wbClose(null null null);

appWorkbooksClose();

appApplicationQuit();

appQuit();

SystemRuntimeInteropServicesMarshalReleaseComObject(ws);

SystemRuntimeInteropServicesMarshalReleaseComObject(wb);

SystemRuntimeInteropServicesMarshalReleaseComObject(app);

ws = null;

wb = null;

app = null;

}

}

using System;

using SystemIO;

using SystemData;

using SystemReflection;

using SystemDiagnostics;

using cfg = SystemConfiguration;

//using Excel;

namespace ExcelHelperTest

{

/**//// <summary>

/// 功能说明套用模板输出Excel并对数据进行分页

/// 作 者Lingyun_k

/// 创建日期

/// </summary>

public class ExcelHelper

{

protected string templetFile = null;

protected string outputFile = null;

protected object missing = MissingValue;

/**//// <summary>

/// 构造函数需指定模板文件和输出文件完整路径

/// </summary>

/// <param name=templetFilePath>Excel模板文件路径</param>

/// <param name=outputFilePath>输出Excel文件路径</param>

public ExcelHelper(string templetFilePathstring outputFilePath)

{

if(templetFilePath == null)

throw new Exception(Excel模板文件路径不能为空！);

if(outputFilePath == null)

throw new Exception(输出Excel文件路径不能为空！);

if(!FileExists(templetFilePath))

throw new Exception(指定路径的Excel模板文件不存在！);

thistempletFile = templetFilePath;

thisoutputFile = outputFilePath;

}

/**//// <summary>

/// 将DataTable数据写入Excel文件（套用模板并分页）

/// </summary>

/// <param name=dt>DataTable</param>

/// <param name=rows>每个WorkSheet写入多少行数据</param>

/// <param name=top>行索引</param>

/// <param name=left>列索引</param>

/// <param name=sheetPrefixName>WorkSheet前缀名比如前缀名为Sheet那么WorkSheet名称依次为SheetSheet</param>

public void DataTableToExcel(DataTable dtint rowsint topint leftstring sheetPrefixName)

{

int rowCount = dtRowsCount; //源DataTable行数

int colCount = dtColumnsCount; //源DataTable列数

int sheetCount = thisGetSheetCount(rowCountrows); //WorkSheet个数

DateTime beforeTime;

DateTime afterTime;

if(sheetPrefixName == null || sheetPrefixNameTrim() == )

sheetPrefixName = Sheet;

//创建一个Application对象并使其可见

beforeTime = DateTimeNow;

ExcelApplication app = new ExcelApplicationClass();

appVisible = true;

afterTime = DateTimeNow;

//打开模板文件得到WorkBook对象

ExcelWorkbook workBook = appWorkbooksOpen(templetFilemissingmissingmissingmissingmissing

missingmissingmissingmissingmissingmissingmissing);

//得到WorkSheet对象

ExcelWorksheet workSheet = (ExcelWorksheet)workBookSheetsget_Item();

//复制sheetCount个WorkSheet对象

for(int i=;i<sheetCount;i++)

{

((ExcelWorksheet)workBookWorksheetsget_Item(i))Copy(missingworkBookWorksheets[i]);

}

将源DataTable数据写入Excel#region 将源DataTable数据写入Excel

for(int i=;i<=sheetCount;i++)

{

int startRow = (i ) * rows; //记录起始行索引

int endRow = i * rows; //记录结束行索引

//若是最后一个WorkSheet那么记录结束行索引为源DataTable行数

if(i == sheetCount)

endRow = rowCount;

//获取要写入数据的WorkSheet对象并重命名

ExcelWorksheet sheet = (ExcelWorksheet)workBookWorksheetsget_Item(i);

sheetName = sheetPrefixName + + iToString();

//将dt中的数据写入WorkSheet

for(int j=;j<endRowstartRow;j++)

{

for(int k=;k<colCount;k++)

{

sheetCells[top + jleft + k] = dtRows[startRow + j][k]ToString();

}

}

//写文本框数据

ExcelTextBox txtAuthor = (ExcelTextBox)sheetTextBoxes(txtAuthor);

ExcelTextBox txtDate = (ExcelTextBox)sheetTextBoxes(txtDate);

ExcelTextBox txtVersion = (ExcelTextBox)sheetTextBoxes(txtVersion);

txtAuthorText = KLYNET的Blog;

txtDateText = DateTimeNowToShortDateString();

txtVersionText = ;

}

#endregion

//输出Excel文件并退出

try

{

workBookSaveAs(outputFilemissingmissingmissingmissingmissingExcelXlSaveAsAccessModexlExclusivemissingmissingmissingmissing);

workBookClose(nullnullnull);

appWorkbooksClose();

appApplicationQuit();

appQuit();

SystemRuntimeInteropServicesMarshalReleaseComObject(workSheet);

SystemRuntimeInteropServicesMarshalReleaseComObject(workBook);

SystemRuntimeInteropServicesMarshalReleaseComObject(app);

workSheet=null;

workBook=null;

app=null;

GCCollect();

}

catch(Exception e)

{

throw e;

}

finally

{

Process[] myProcesses;

DateTime startTime;

myProcesses = ProcessGetProcessesByName(Excel);

//得不到Excel进程ID暂时只能判断进程启动时间

foreach(Process myProcess in myProcesses)

{

startTime = myProcessStartTime;

if(startTime > beforeTime && startTime < afterTime)

{

myProcessKill();

}

}

}

}

/**//// <summary>

/// 获取WorkSheet数量

/// </summary>

/// <param name=rowCount>记录总行数</param>

/// <param name=rows>每WorkSheet行数</param>

private int GetSheetCount(int rowCountint rows)

{

int n = rowCount % rows; //余数

if(n == )

return rowCount / rows;

else

return ConvertToInt(rowCount / rows) + ;

}

/**//// <summary>

/// 将二维数组数据写入Excel文件（套用模板并分页）

/// </summary>

/// <param name=arr>二维数组</param>

/// <param name=rows>每个WorkSheet写入多少行数据</param>

/// <param name=top>行索引</param>

/// <param name=left>列索引</param>

/// <param name=sheetPrefixName>WorkSheet前缀名比如前缀名为Sheet那么WorkSheet名称依次为SheetSheet</param>

public void ArrayToExcel(string[] arrint rowsint topint leftstring sheetPrefixName)

{

int rowCount = arrGetLength(); //二维数组行数（一维长度）

int colCount = arrGetLength(); //二维数据列数（二维长度）

int sheetCount = thisGetSheetCount(rowCountrows); //WorkSheet个数

DateTime beforeTime;

DateTime afterTime;

if(sheetPrefixName == null || sheetPrefixNameTrim() == )

sheetPrefixName = Sheet;

//创建一个Application对象并使其可见

beforeTime = DateTimeNow;

ExcelApplication app = new ExcelApplicationClass();

appVisible = true;

afterTime = DateTimeNow;

//打开模板文件得到WorkBook对象

ExcelWorkbook workBook = appWorkbooksOpen(templetFilemissingmissingmissingmissingmissing

missingmissingmissingmissingmissingmissingmissing);

//得到WorkSheet对象

ExcelWorksheet workSheet = (ExcelWorksheet)workBookSheetsget_Item();

//复制sheetCount个WorkSheet对象

for(int i=;i<sheetCount;i++)

{

((ExcelWorksheet)workBookWorksheetsget_Item(i))Copy(missingworkBookWorksheets[i]);

}

将二维数组数据写入Excel#region 将二维数组数据写入Excel

for(int i=;i<=sheetCount;i++)

{

int startRow = (i ) * rows; //记录起始行索引

int endRow = i * rows; //记录结束行索引

//若是最后一个WorkSheet那么记录结束行索引为源DataTable行数

if(i == sheetCount)

endRow = rowCount;

//获取要写入数据的WorkSheet对象并重命名

ExcelWorksheet sheet = (ExcelWorksheet)workBookWorksheetsget_Item(i);

sheetName = sheetPrefixName + + iToString();

//将二维数组中的数据写入WorkSheet

for(int j=;j<endRowstartRow;j++)

{

for(int k=;k<colCount;k++)

{

sheetCells[top + jleft + k] = arr[startRow + jk];

}

}

ExcelTextBox txtAuthor = (ExcelTextBox)sheetTextBoxes(txtAuthor);

ExcelTextBox txtDate = (ExcelTextBox)sheetTextBoxes(txtDate);

ExcelTextBox txtVersion = (ExcelTextBox)sheetTextBoxes(txtVersion);

txtAuthorText = KLYNET的Blog;

txtDateText = DateTimeNowToShortDateString();

txtVersionText = ;

}

#endregion

//输出Excel文件并退出

try

{

workBookSaveAs(outputFilemissingmissingmissingmissingmissingExcelXlSaveAsAccessModexlExclusivemissingmissingmissingmissing);

workBookClose(nullnullnull);

appWorkbooksClose();

appApplicationQuit();

appQuit();

SystemRuntimeInteropServicesMarshalReleaseComObject(workSheet);

SystemRuntimeInteropServicesMarshalReleaseComObject(workBook);

SystemRuntimeInteropServicesMarshalReleaseComObject(app);

workSheet=null;

workBook=null;

app=null;

GCCollect();

}

catch(Exception e)

{

throw e;

}

finally

{

Process[] myProcesses;

DateTime startTime;

myProcesses = ProcessGetProcessesByName(Excel);

//得不到Excel进程ID暂时只能判断进程启动时间

foreach(Process myProcess in myProcesses)

{

startTime = myProcessStartTime;

if(startTime > beforeTime && startTime < afterTime)

{

myProcessKill();

}

}

}

}

}

}