C#套用模板輸出Excel，并對數(shù)據(jù)進行分頁(收集)
文章分類:.net編程
using System;
using System.IO;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using cfg = System.Configuration;
using Excel;
namespace ExcelHelperTest
{
/**/ /// <summary>
/// 功能說明：套用模板輸出Excel，并對數(shù)據(jù)進行分頁
/// 作 者：Lingyun_k
/// 創(chuàng)建日期：2005-7-12
/// </summary>
public class ExcelHelper
{
protected string templetFile = null ;
protected string outputFile = null ;
protected object missing = Missing.Value;
/**/ /// <summary>
/// 構(gòu)造函數(shù)，需指定模板文件和輸出文件完整路徑
/// </summary>
/// <param name="templetFilePath"> Excel模板文件路徑 </param>
/// <param name="outputFilePath"> 輸出Excel文件路徑 </param>
public ExcelHelper( string templetFilePath, string outputFilePath)
{
if (templetFilePath == null )
throw new Exception( " Excel模板文件路徑不能為空！ " );
if (outputFilePath == null )
throw new Exception( " 輸出Excel文件路徑不能為空！ " );
if ( ! File.Exists(templetFilePath))
throw new Exception( " 指定路徑的Excel模板文件不存在！ " );
this .templetFile = templetFilePath;
this .outputFile = outputFilePath;
}
/**/ /// <summary>
/// 將DataTable數(shù)據(jù)寫入Excel文件（套用模板并分頁）
/// </summary>
/// <param name="dt"> DataTable </param>
/// <param name="rows"> 每個WorkSheet寫入多少行數(shù)據(jù) </param>
/// <param name="top"> 行索引 </param>
/// <param name="left"> 列索引 </param>
/// <param name="sheetPrefixName"> WorkSheet前綴名，比如：前綴名為“Sheet”，那么WorkSheet名稱依次為“Sheet-1，Sheet-2” </param>
public void DataTableToExcel(DataTable dt, int rows, int top, int left, string sheetPrefixName)
{
int rowCount = dt.Rows.Count; // 源DataTable行數(shù)
int colCount = dt.Columns.Count; // 源DataTable列數(shù)
int sheetCount = this .GetSheetCount(rowCount,rows); // WorkSheet個數(shù)
DateTime beforeTime;
DateTime afterTime;
if (sheetPrefixName == null || sheetPrefixName.Trim() == "" )
sheetPrefixName = " Sheet " ;
// 創(chuàng)建一個Application對象并使其可見
beforeTime = DateTime.Now;
Excel.Application app = new Excel.ApplicationClass();
app.Visible = true ;
afterTime = DateTime.Now;
// 打開模板文件，得到WorkBook對象
Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
missing,missing,missing,missing,missing,missing,missing);
// 得到WorkSheet對象
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item( 1 );
// 復(fù)制sheetCount-1個WorkSheet對象
for ( int i = 1 ;i < sheetCount;i ++ )
{
((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
}
將源DataTable數(shù)據(jù)寫入Excel #region 將源DataTable數(shù)據(jù)寫入Excel
for ( int i = 1 ;i <= sheetCount;i ++ )
{
int startRow = (i - 1 ) * rows; // 記錄起始行索引
int endRow = i * rows; // 記錄結(jié)束行索引
// 若是最后一個WorkSheet，那么記錄結(jié)束行索引為源DataTable行數(shù)
if (i == sheetCount)
endRow = rowCount;
// 獲取要寫入數(shù)據(jù)的WorkSheet對象，并重命名
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
sheet.Name = sheetPrefixName + " - " + i.ToString();
// 將dt中的數(shù)據(jù)寫入WorkSheet
for ( int j = 0 ;j < endRow - startRow;j ++ )
{
for ( int k = 0 ;k < colCount;k ++ )
{
sheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
}
}
// 寫文本框數(shù)據(jù)
Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes( " txtAuthor " );
Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes( " txtDate " );
Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes( " txtVersion " );
txtAuthor.Text = " KLY.NET的Blog " ;
txtDate.Text = DateTime.Now.ToShortDateString();
txtVersion.Text = " 1.0.0.0 " ;
}
#endregion
// 輸出Excel文件并退出
try
{
workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);
workBook.Close( null , null , null );
app.Workbooks.Close();
app.Application.Quit();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
workSheet = null ;
workBook = null ;
app = null ;
GC.Collect();
}
catch (Exception e)
{
throw e;
}
finally
{
Process[] myProcesses;
DateTime startTime;
myProcesses = Process.GetProcessesByName( " Excel " );
// 得不到Excel進程ID，暫時只能判斷進程啟動時間
foreach (Process myProcess in myProcesses)
{
startTime = myProcess.StartTime;
if (startTime > beforeTime && startTime < afterTime)
{
myProcess.Kill();
}
}
}
}
/**/ /// <summary>
/// 獲取WorkSheet數(shù)量
/// </summary>
/// <param name="rowCount"> 記錄總行數(shù) </param>
/// <param name="rows"> 每WorkSheet行數(shù) </param>
private int GetSheetCount( int rowCount, int rows)
{
int n = rowCount % rows; // 余數(shù)
if (n == 0 )
return rowCount / rows;
else
return Convert.ToInt32(rowCount / rows) + 1 ;
}
/**/ /// <summary>
/// 將二維數(shù)組數(shù)據(jù)寫入Excel文件（套用模板并分頁）
/// </summary>
/// <param name="arr"> 二維數(shù)組 </param>
/// <param name="rows"> 每個WorkSheet寫入多少行數(shù)據(jù) </param>
/// <param name="top"> 行索引 </param>
/// <param name="left"> 列索引 </param>
/// <param name="sheetPrefixName"> WorkSheet前綴名，比如：前綴名為“Sheet”，那么WorkSheet名稱依次為“Sheet-1，Sheet-2” </param>
public void ArrayToExcel( string [,] arr, int rows, int top, int left, string sheetPrefixName)
{
int rowCount = arr.GetLength( 0 ); // 二維數(shù)組行數(shù)（一維長度）
int colCount = arr.GetLength( 1 ); // 二維數(shù)據(jù)列數(shù)（二維長度）
int sheetCount = this .GetSheetCount(rowCount,rows); // WorkSheet個數(shù)
DateTime beforeTime;
DateTime afterTime;
if (sheetPrefixName == null || sheetPrefixName.Trim() == "" )
sheetPrefixName = " Sheet " ;
// 創(chuàng)建一個Application對象并使其可見
beforeTime = DateTime.Now;
Excel.Application app = new Excel.ApplicationClass();
app.Visible = true ;
afterTime = DateTime.Now;
// 打開模板文件，得到WorkBook對象
Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
missing,missing,missing,missing,missing,missing,missing);
// 得到WorkSheet對象
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item( 1 );
// 復(fù)制sheetCount-1個WorkSheet對象
for ( int i = 1 ;i < sheetCount;i ++ )
{
((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
}
將二維數(shù)組數(shù)據(jù)寫入Excel #region 將二維數(shù)組數(shù)據(jù)寫入Excel
for ( int i = 1 ;i <= sheetCount;i ++ )
{
int startRow = (i - 1 ) * rows; // 記錄起始行索引
int endRow = i * rows; // 記錄結(jié)束行索引
// 若是最后一個WorkSheet，那么記錄結(jié)束行索引為源DataTable行數(shù)
if (i == sheetCount)
endRow = rowCount;
// 獲取要寫入數(shù)據(jù)的WorkSheet對象，并重命名
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
sheet.Name = sheetPrefixName + " - " + i.ToString();
// 將二維數(shù)組中的數(shù)據(jù)寫入WorkSheet
for ( int j = 0 ;j < endRow - startRow;j ++ )
{
for ( int k = 0 ;k < colCount;k ++ )
{
sheet.Cells[top + j,left + k] = arr[startRow + j,k];
}
}
Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes( " txtAuthor " );
Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes( " txtDate " );
Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes( " txtVersion " );
txtAuthor.Text = " KLY.NET的Blog " ;
txtDate.Text = DateTime.Now.ToShortDateString();
txtVersion.Text = " 1.0.0.0 " ;
}
#endregion
// 輸出Excel文件并退出
try
{
workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);
workBook.Close( null , null , null );
app.Workbooks.Close();
app.Application.Quit();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
workSheet = null ;
workBook = null ;
app = null ;
GC.Collect();
}
catch (Exception e)
{
throw e;
}
finally
{
Process[] myProcesses;
DateTime startTime;
myProcesses = Process.GetProcessesByName( " Excel " );
// 得不到Excel進程ID，暫時只能判斷進程啟動時間
foreach (Process myProcess in myProcesses)
{
startTime = myProcess.StartTime;
if (startTime > beforeTime && startTime < afterTime)
{
myProcess.Kill();
}
}
}
}
}
}
posted on 2011-03-21 13:58
luis 閱讀(837)
評論(0) 編輯 收藏 引用