c# 中wpfexcel_c# excel

using System;

using System.IO;

using System.Text;

using System.Data;

using System.Reflection;

using System.Diagnostics;

using System.Collections;

//using cfg = System.Configuration;

namespace GDBI.Common

{

///

/// 说 明：Excel输出打印模块

/// 暂时不提供操作Excel对象样式方法，样式可以在Excel模板中设置好

/// 作 者：lingyun_k

/// 创建日期：2005－7－12

///

public class ExcelHelper

{

#region 成员变量

private string templetFile = null;

private string outputFile = null;

private object missing = Missing.Value;

private DateTime beforeTime; //Excel启动之前时间

private DateTime afterTime; //Excel启动之后时间

Excel.Application app;

Excel.Workbook workBook;

Excel.Worksheet workSheet;

Excel.Range range;

Excel.Range range1;

Excel.Range range2;

Excel.TextBox textBox;

private int sheetCount = 1; //WorkSheet数量

private string sheetPrefixName = "页";

#endregion

#region 公共属性

///

/// WorkSheet前缀名，比如：前缀名为“页”，那么WorkSheet名称依次为“页-1，页-2...”

///

public string SheetPrefixName

{

set { this.sheetPrefixName = value; }

}

///

/// WorkSheet数量

///

public int WorkSheetCount

{

get { return workBook.Sheets.Count; }

}

///

/// Excel模板文件路径

///

public string TempletFilePath

{

set { this.templetFile = value; }

}

///

/// 输出Excel文件路径

///

public string OutputFilePath

{

set { this.outputFile = value; }

}

#endregion

#region 公共方法

#region ExcelHelper

///

/// 构造函数，将一个已有Excel工作簿作为模板，并指定输出路径

///

/// Excel模板文件路径

/// 输出Excel文件路径

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;

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

beforeTime = DateTime.Now;

app = new Excel.ApplicationClass();

app.Visible = true;

afterTime = DateTime.Now;

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

workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,

missing,missing,missing,missing,missing,missing,missing);

//得到WorkSheet对象

workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

}

///

/// 构造函数，打开一个已有的工作簿

///

/// Excel文件名

public ExcelHelper(string fileName)

{

if(!File.Exists(fileName))

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

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

beforeTime = DateTime.Now;

app = new Excel.ApplicationClass();

app.Visible = true;

afterTime = DateTime.Now;

//打开一个WorkBook

workBook = app.Workbooks.Open(fileName,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing);

//得到WorkSheet对象

workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

}

///

/// 构造函数，新建一个工作簿

///

public ExcelHelper()

{

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

beforeTime = DateTime.Now;

app = new Excel.ApplicationClass();

app.Visible = true;

afterTime = DateTime.Now;

//新建一个WorkBook

workBook = app.Workbooks.Add(Type.Missing);

//得到WorkSheet对象

workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

}

#endregion

#region Data Export Methods

///

/// 将DataTable数据写入Excel文件(自动分页)

///

/// DataTable

/// 每个WorkSheet写入多少行数据

/// 表格数据起始行索引

/// 表格数据起始列索引

public void DataTableToExcel(DataTable dt,int rows,int top,int left)

{

int rowCount = dt.Rows.Count; //DataTable行数

int colCount = dt.Columns.Count; //DataTable列数

sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数

// StringBuilder sb;

//复制sheetCount-1个WorkSheet对象

for(int i=1;i

{

workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);

workSheet.Copy(missing,workBook.Worksheets[i]);

}

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

{

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

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

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

if(i == sheetCount)

endRow = rowCount;

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

workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);

workSheet.Name = sheetPrefixName + "-" + i.ToString();

//将dt中的数据写入WorkSheet

// for(int j=0;j

// {

// for(int k=0;k

// {

// workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();

// }

// }

//利用二维数组批量写入

int row = endRow-startRow;

string[,] ss = new string[row,colCount];

for(int j=0;j

{

for(int k=0;k

{

ss[j,k] = dt.Rows[startRow + j][k].ToString();

}

}

range = (Excel.Range)workSheet.Cells[top,left];

range = range.get_Resize(row,colCount);

range.Value = ss;

#region 利用Windwo粘贴板批量拷贝数据(在Web下面行不通)

/*sb = new StringBuilder();

for(int j=0;j

{

for(int k=0;k

{

sb.Append( dt.Rows[startRow + j][k].ToString() );

sb.Append("\t");

}

sb.Append("\n");

}

System.Windows.Forms.Clipboard.SetDataObject(sb.ToString());

range = (Excel.Range)workSheet.Cells[top,left];

workSheet.Paste(range,false);*/

#endregion

}

}

///

/// 将DataTable数据写入Excel文件(不分页)

///

/// DataTable

/// 表格数据起始行索引

/// 表格数据起始列索引

public void DataTableToExcel(DataTable dt,int top,int left)

{

int rowCount = dt.Rows.Count; //DataTable行数

int colCount = dt.Columns.Count; //DataTable列数

//利用二维数组批量写入

string[,] arr = new string[rowCount,colCount];

for(int j=0;j

{

for(int k=0;k

{

arr[j,k] = dt.Rows[j][k].ToString();

}

}

range = (Excel.Range)workSheet.Cells[top,left];

range = range.get_Resize(rowCount,colCount);

range.Value = arr;

}

///

/// 将DataTable数据写入Excel文件(自动分页，并指定要合并的列索引)

///

/// DataTable

/// 每个WorkSheet写入多少行数据

/// 表格数据起始行索引

/// 表格数据起始列索引

/// DataTable中要合并相同行的列索引，从0开始

public void DataTableToExcel(DataTable dt,int rows,int top,int left,int mergeColumnIndex)

{

int rowCount = dt.Rows.Count; //源DataTable行数

int colCount = dt.Columns.Count; //源DataTable列数

sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数

// StringBuilder sb;

//复制sheetCount-1个WorkSheet对象

for(int i=1;i

{

workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);

workSheet.Copy(missing,workBook.Worksheets[i]);

}

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

{

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

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

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

if(i == sheetCount)

endRow = rowCount;

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

workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);

workSheet.Name = sheetPrefixName + "-" + i.ToString();

//将dt中的数据写入WorkSheet

// for(int j=0;j

// {

// for(int k=0;k

// {

// workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();

// }

// }

//利用二维数组批量写入

int row = endRow-startRow;

string[,] ss = new string[row,colCount];

for(int j=0;j

{

for(int k=0;k

{

ss[j,k] = dt.Rows[startRow + j][k].ToString();

}

}

range = (Excel.Range)workSheet.Cells[top,left];

range = range.get_Resize(row,colCount);

range.Value = ss;

//合并相同行

this.MergeRows(workSheet,left+mergeColumnIndex,top,rows);

}

}

///

/// 将二维数组数据写入Excel文件(自动分页)

///

/// 二维数组

/// 每个WorkSheet写入多少行数据

/// 行索引

/// 列索引

public void ArrayToExcel(string[,] arr,int rows,int top,int left)

{

int rowCount = arr.GetLength(0); //二维数组行数(一维长度)

int colCount = arr.GetLength(1); //二维数据列数(二维长度)

sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数

//复制sheetCount-1个WorkSheet对象

for(int i=1;i

{

workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);

workSheet.Copy(missing,workBook.Worksheets[i]);

}

//将二维数组数据写入Excel

for(int i=sheetCount;i>=1;i--)

{

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

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

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

if(i == sheetCount)

endRow = rowCount;

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

workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);

workSheet.Name = sheetPrefixName + "-" + i.ToString();

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

// for(int j=0;j

// {

// for(int k=0;k

// {

// workSheet.Cells[top + j,left + k] = arr[startRow + j,k];

// }

// }

//利用二维数组批量写入

int row = endRow-startRow;

string[,] ss = new string[row,colCount];

for(int j=0;j

{

for(int k=0;k

{

ss[j,k] = arr[startRow + j,k];

}

}

range = (Excel.Range)workSheet.Cells[top,left];

range = range.get_Resize(row,colCount);

range.Value = ss;

}

}//end ArrayToExcel

///

/// 将二维数组数据写入Excel文件(不分页)

///

/// 二维数组

/// 行索引

/// 列索引

public void ArrayToExcel(string[,] arr,int top,int left)

{

int rowCount = arr.GetLength(0); //二维数组行数(一维长度)

int colCount = arr.GetLength(1); //二维数据列数(二维长度)

range = (Excel.Range)workSheet.Cells[top,left];

range = range.get_Resize(rowCount,colCount);

range.FormulaArray = arr;

}//end ArrayToExcel

///

/// 将二维数组数据写入Excel文件(不分页)

///

/// 二维数组

/// 行索引

/// 列索引

/// 填充的数据是否需要计算

public void ArrayToExcel(string[,] arr,int top,int left,bool isFormula)

{

int rowCount = arr.GetLength(0); //二维数组行数(一维长度)

int colCount = arr.GetLength(1); //二维数据列数(二维长度)

range = (Excel.Range)workSheet.Cells[top,left];

range = range.get_Resize(rowCount,colCount);

//注意：使用range.FormulaArray写合并的单元格会出问题

if(isFormula)

range.FormulaArray = arr;

else

range.Value = arr;

}//end ArrayToExcel

///

/// 将二维数组数据写入Excel文件(不分页)，合并指定列的相同行

///

/// 二维数组

/// 行索引

/// 列索引

/// 填充的数据是否需要计算

/// 需要合并行的列索引

public void ArrayToExcel(string[,] arr,int top,int left,bool isFormula,int mergeColumnIndex)

{

int rowCount = arr.GetLength(0); //二维数组行数(一维长度)

int colCount = arr.GetLength(1); //二维数据列数(二维长度)

range = (Excel.Range)workSheet.Cells[top,left];

range = range.get_Resize(rowCount,colCount);

//注意：使用range.FormulaArray写合并的单元格会出问题

if(isFormula)

range.FormulaArray = arr;

else

range.Value = arr;

this.MergeRows(workSheet,mergeColumnIndex,top,rowCount);

}//end ArrayToExcel

///

/// 将二维数组数据写入Excel文件(不分页)

///

/// 工作表索引

/// 二维数组

/// 行索引

/// 列索引

public void ArrayToExcel(int sheetIndex,string[,] arr,int top,int left)

{

if(sheetIndex > this.WorkSheetCount)

{

this.KillExcelProcess();

throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");

}

// 改变当前工作表

this.workSheet = (Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);

int rowCount = arr.GetLength(0); //二维数组行数(一维长度)

int colCount = arr.GetLength(1); //二维数据列数(二维长度)

range = (Excel.Range)workSheet.Cells[top,left];

range = range.get_Resize(rowCount,colCount);

range.Value2 = arr;

}//end ArrayToExcel

///

/// 将二维数组数据写入Excel文件(自动分页，并指定要合并的列索引)

///

/// 二维数组

/// 每个WorkSheet写入多少行数据

/// 行索引

/// 列索引

/// 数组