當需要輸出帶大量公式的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();
}
}
}
}
}
}
From:http://tw.wingwit.com/Article/program/net/201311/12695.html
