excelhelper.cs

一个c#的原码

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
{
	/// <summary>
	/// 说    明：Excel输出打印模块
	///			  暂时不提供操作Excel对象样式方法，样式可以在Excel模板中设置好
	///	作    者：lingyun_k
	///	创建日期：2005－7－12
	/// </summary>
	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 公共属性
		/// <summary>
		/// WorkSheet前缀名，比如：前缀名为“页”，那么WorkSheet名称依次为“页-1，页-2...”
		/// </summary>
		public string SheetPrefixName
		{
			set { this.sheetPrefixName = value; }
		}

		/// <summary>
		/// WorkSheet数量
		/// </summary>
		public int WorkSheetCount
		{
			get { return workBook.Sheets.Count; }
		}

		/// <summary>
		/// Excel模板文件路径
		/// </summary>
		public string TempletFilePath
		{
			set { this.templetFile = value; }
		}

		/// <summary>
		/// 输出Excel文件路径
		/// </summary>
		public string OutputFilePath
		{
			set { this.outputFile = value; }
		}
		#endregion

		#region 公共方法

		#region ExcelHelper
		/// <summary>
		/// 构造函数，将一个已有Excel工作簿作为模板，并指定输出路径
		/// </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;

			//创建一个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);

		}

		/// <summary>
		/// 构造函数，打开一个已有的工作簿
		/// </summary>
		/// <param name="fileName">Excel文件名</param>
		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);

		}

		/// <summary>
		/// 构造函数，新建一个工作簿
		/// </summary>
		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

		/// <summary>
		/// 将DataTable数据写入Excel文件（自动分页）
		/// </summary>
		/// <param name="dt">DataTable</param>
		/// <param name="rows">每个WorkSheet写入多少行数据</param>
		/// <param name="top">表格数据起始行索引</param>
		/// <param name="left">表格数据起始列索引</param>
		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<sheetCount;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<endRow-startRow;j++)
//				{
//					for(int k=0;k<colCount;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<row;j++)
				{
					for(int k=0;k<colCount;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<endRow-startRow;j++)
				{
					for(int k=0;k<colCount;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
						
			}
		}


		/// <summary>
		/// 将DataTable数据写入Excel文件（不分页）
		/// </summary>
		/// <param name="dt">DataTable</param>
		/// <param name="top">表格数据起始行索引</param>
		/// <param name="left">表格数据起始列索引</param>
		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<rowCount;j++)
			{
				for(int k=0;k<colCount;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;
		}


		/// <summary>
		/// 将DataTable数据写入Excel文件（自动分页，并指定要合并的列索引）
		/// </summary>
		/// <param name="dt">DataTable</param>
		/// <param name="rows">每个WorkSheet写入多少行数据</param>
		/// <param name="top">表格数据起始行索引</param>
		/// <param name="left">表格数据起始列索引</param>
		/// <param name="mergeColumnIndex">DataTable中要合并相同行的列索引，从0开始</param>
		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<sheetCount;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<endRow-startRow;j++)
//				{
//					for(int k=0;k<colCount;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<row;j++)
				{
					for(int k=0;k<colCount;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);
						
			}
		}


		/// <summary>
		/// 将二维数组数据写入Excel文件（自动分页）
		/// </summary>
		/// <param name="arr">二维数组</param>
		/// <param name="rows">每个WorkSheet写入多少行数据</param>
		/// <param name="top">行索引</param>
		/// <param name="left">列索引</param>
		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<sheetCount;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<endRow-startRow;j++)
//				{
//					for(int k=0;k<colCount;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<row;j++)
				{
					for(int k=0;k<colCount;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


		/// <summary>
		/// 将二维数组数据写入Excel文件（不分页）
		/// </summary>
		/// <param name="arr">二维数组</param>
		/// <param name="top">行索引</param>
		/// <param name="left">列索引</param>
		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

		/// <summary>
		/// 将二维数组数据写入Excel文件（不分页）
		/// </summary>
		/// <param name="arr">二维数组</param>
		/// <param name="top">行索引</param>
		/// <param name="left">列索引</param>
		/// <param name="isFormula">填充的数据是否需要计算</param>
		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

		/// <summary>
		/// 将二维数组数据写入Excel文件（不分页），合并指定列的相同行
		/// </summary>
		/// <param name="arr">二维数组</param>
		/// <param name="top">行索引</param>
		/// <param name="left">列索引</param>
		/// <param name="isFormula">填充的数据是否需要计算</param>
		/// <param name="mergeColumnIndex">需要合并行的列索引</param>
		public void ArrayToExcel(string[,] arr,int top,int left,bool isFormula,int mergeColumnIndex)
		{
			int rowCount = arr.GetLength(0);		//二维数组行数（一维长度）