在网上看到了一个操作Excel的类,于是保存下来供以后参考。 不过用这种方法操作Excel缺点是当数据量很大时,速度会很慢。
这个类中可能还有很多需要修改的地方,需要用的朋友可以自己修改一下。
using System;
using System.IO;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using cfg = System.Configuration;
namespace ExcelHelperTest
{
/// <summary>
/// 功能说明:套用模板输出Excel,并对数据进行分页
/// </summary>
public class ExcelHelper
{
protected string templetFile = null;
protected string outputFile = null;
protected object missing = Missing.Value;
<summary>
/// 构造函数,需指定模板文件和输出文件完整路径
/// </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数据写入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名称依次为“Sheet-1,Sheet-2”</param>
public void DataTableToExcel(DataTable dt,int rows,int top,int left,string sheetPrefixName)
{
int rowCount = dt.Rows.Count; //源DataTable行数
int colCount = dt.Columns.Count; //源DataTable列数
int sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数
DateTime beforeTime;
DateTime afterTime;
if(sheetPrefixName == null || sheetPrefixName.Trim() == "")
sheetPrefixName = "Sheet";
//创建一个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);
//复制sheetCount-1个WorkSheet对象
for(int i=1;i<sheetCount;i++)
{
((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
}
将源DataTable数据写入Excel#region 将源DataTable数据写入Excel
for(int i=1;i<=sheetCount;i++)
{
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数
if(i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象,并重命名
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
sheet.Name = sheetPrefixName + "-" + i.ToString();
//将dt中的数据写入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();
}
}
//写文本框数据
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数量
/// </summary>
/// <param name="rowCount">记录总行数</param>
/// <param name="rows">每WorkSheet行数</param>
private int GetSheetCount(int rowCount,int rows)
{
int n = rowCount % rows; //余数
if(n == 0)
return rowCount / rows;
else
return Convert.ToInt32(rowCount / rows) + 1;
}
<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名称依次为“Sheet-1,Sheet-2”</param>
public void ArrayToExcel(string[,] arr,int rows,int top,int left,string sheetPrefixName)
{
int rowCount = arr.GetLength(0); //二维数组行数(一维长度)
int colCount = arr.GetLength(1); //二维数据列数(二维长度)
int sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数
DateTime beforeTime;
DateTime afterTime;
if(sheetPrefixName == null || sheetPrefixName.Trim() == "")
sheetPrefixName = "Sheet";
//创建一个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);
//复制sheetCount-1个WorkSheet对象
for(int i=1;i<sheetCount;i++)
{
((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
}
将二维数组数据写入Excel#region 将二维数组数据写入Excel
for(int i=1;i<=sheetCount;i++)
{
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数
if(i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象,并重命名
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
sheet.Name = sheetPrefixName + "-" + i.ToString();
//将二维数组中的数据写入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();
}
}
}
}
}