excelhelper.cs

一个c#的原码

			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

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

		/// <summary>
		/// 将二维数组数据写入Excel文件（自动分页，并指定要合并的列索引）
		/// </summary>
		/// <param name="arr">二维数组</param>
		/// <param name="rows">每个WorkSheet写入多少行数据</param>
		/// <param name="top">行索引</param>
		/// <param name="left">列索引</param>
		/// <param name="mergeColumnIndex">数组的二维索引，相当于DataTable的列索引，索引从0开始</param>
		public void ArrayToExcel(string[,] arr,int rows,int top,int left,int mergeColumnIndex)
		{
			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;

				//合并相同行
				this.MergeRows(workSheet,left+mergeColumnIndex,top,rows);
			}
			
		}//end ArrayToExcel
		#endregion

		#region WorkSheet Methods

		/// <summary>
		/// 改变当前工作表
		/// </summary>
		/// <param name="sheetIndex">工作表索引</param>
		public void ChangeCurrentWorkSheet(int sheetIndex)
		{
			//若指定工作表索引超出范围，则不改变当前工作表
			if(sheetIndex < 1)
				return;

			if(sheetIndex > this.WorkSheetCount)
				return;

			this.workSheet = (Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);
		}
		/// <summary>
		/// 隐藏指定名称的工作表
		/// </summary>
		/// <param name="sheetName">工作表名称</param>
		public void HiddenWorkSheet(string sheetName)
		{
			try
			{
				Excel.Worksheet sheet = null;

				for(int i=1;i<=this.WorkSheetCount;i++)
				{
					workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
					
					if(workSheet.Name == sheetName)
						sheet = workSheet;
				}

				if(sheet != null)
					sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
				else
				{
					this.KillExcelProcess();
					throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
				}
			}
			catch(Exception e)
			{
				this.KillExcelProcess();
				throw e;
			}
		}

		/// <summary>
		/// 隐藏指定索引的工作表
		/// </summary>
		/// <param name="sheetIndex"></param>
		public void HiddenWorkSheet(int sheetIndex)
		{
			if(sheetIndex > this.WorkSheetCount)
			{
				this.KillExcelProcess();
				throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
			}

			try
			{
				Excel.Worksheet sheet = null;
				sheet = (Excel.Worksheet)workBook.Sheets.get_Item(sheetIndex);

				sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
			}
			catch(Exception e)
			{
				this.KillExcelProcess();
				throw e;
			}
		}


		/// <summary>
		/// 在指定名称的工作表后面拷贝指定个数的该工作表的副本，并重命名
		/// </summary>
		/// <param name="sheetName">工作表名称</param>
		/// <param name="sheetCount">工作表个数</param>
		public void CopyWorkSheets(string sheetName,int sheetCount)
		{
			try
			{
				Excel.Worksheet sheet = null;
				int sheetIndex = 0;

				for(int i=1;i<=this.WorkSheetCount;i++)
				{
					workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
					
					if(workSheet.Name == sheetName)
					{
						sheet = workSheet;
						sheetIndex = workSheet.Index;
					}
				}

				if(sheet != null)
				{
					for(int i=sheetCount;i>=1;i--)
					{
						sheet.Copy(this.missing,sheet);
					}

					//重命名
					for(int i=sheetIndex;i<=sheetIndex+sheetCount;i++)
					{
						workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
						workSheet.Name = sheetName + "-" + Convert.ToString(i - sheetIndex + 1);
					}
				}
				else
				{
					this.KillExcelProcess();
					throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
				}
			}
			catch(Exception e)
			{
				this.KillExcelProcess();
				throw e;
			}
		}

		/// <summary>
		/// 将一个工作表拷贝到另一个工作表后面，并重命名
		/// </summary>
		/// <param name="srcSheetIndex">拷贝源工作表索引</param>
		/// <param name="aimSheetIndex">参照位置工作表索引，新工作表拷贝在该工作表后面</param>
		/// <param name="newSheetName"></param>
		public void CopyWorkSheet(int srcSheetIndex,int aimSheetIndex,string newSheetName)
		{
			if(srcSheetIndex > this.WorkSheetCount || aimSheetIndex > this.WorkSheetCount)
			{
				this.KillExcelProcess();
				throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
			}

			try
			{
				Excel.Worksheet srcSheet = (Excel.Worksheet)workBook.Sheets.get_Item(srcSheetIndex);
				Excel.Worksheet aimSheet = (Excel.Worksheet)workBook.Sheets.get_Item(aimSheetIndex);
				
				srcSheet.Copy(this.missing,aimSheet);
					
				//重命名
				workSheet = (Excel.Worksheet)aimSheet.Next;		//获取新拷贝的工作表
				workSheet.Name = newSheetName;
			}
			catch(Exception e)
			{
				this.KillExcelProcess();
				throw e;
			}
		}


		/// <summary>
		/// 根据名称删除工作表
		/// </summary>
		/// <param name="sheetName"></param>
		public void DeleteWorkSheet(string sheetName)
		{
			try
			{
				Excel.Worksheet sheet = null;

				//找到名称位sheetName的工作表
				for(int i=1;i<=this.WorkSheetCount;i++)
				{
					workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
					
					if(workSheet.Name == sheetName)
					{
						sheet = workSheet;
					}
				}

				if(sheet != null)
				{
					sheet.Delete();
				}
				else
				{
					this.KillExcelProcess();
					throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
				}
			}
			catch(Exception e)
			{
				this.KillExcelProcess();
				throw e;
			}
		}

		/// <summary>
		/// 根据索引删除工作表
		/// </summary>
		/// <param name="sheetIndex"></param>
		public void DeleteWorkSheet(int sheetIndex)
		{
			if(sheetIndex > this.WorkSheetCount)
			{
				this.KillExcelProcess();
				throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
			}

			try
			{
				Excel.Worksheet sheet = null;
				sheet = (Excel.Worksheet)workBook.Sheets.get_Item(sheetIndex);

				sheet.Delete();
			}
			catch(Exception e)
			{
				this.KillExcelProcess();
				throw e;
			}
		}

		#endregion

		#region TextBox Methods
		/// <summary>
		/// 向指定文本框写入数据，对每个WorkSheet操作
		/// </summary>
		/// <param name="textboxName">文本框名称</param>
		/// <param name="text">要写入的文本</param>
		public void SetTextBox(string textboxName,string text)
		{
			for(int i=1;i<=this.WorkSheetCount;i++)
			{
				workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);


				try
				{
					textBox = (Excel.TextBox)workSheet.TextBoxes(textboxName);
					textBox.Text = text;
				}
				catch
				{
					this.KillExcelProcess();
					throw new Exception("不存在ID为\"" + textboxName + "\"的文本框！");
				}
			}
		}

		/// <summary>
		/// 向指定文本框写入数据，对指定WorkSheet操作
		/// </summary>
		/// <param name="sheetIndex">工作表索引</param>
		/// <param name="textboxName">文本框名称</param>
		/// <param name="text">要写入的文本</param>
		public void SetTextBox(int sheetIndex,string textboxName,string text)
		{
			workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);

			try
			{
				textBox = (Excel.TextBox)workSheet.TextBoxes(textboxName);
				textBox.Text = text;
			}
			catch
			{
				this.KillExcelProcess();
				throw new Exception("不存在ID为\"" + textboxName + "\"的文本框！");
			}
		}

		/// <summary>
		/// 向文本框写入数据，对每个WorkSheet操作
		/// </summary>
		/// <param name="ht">Hashtable的键值对保存文本框的ID和数据</param>
		public void SetTextBoxes(Hashtable ht)
		{
			if(ht.Count == 0) return;

			for(int i=1;i<=this.WorkSheetCount;i++)
			{
				workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);

				foreach(DictionaryEntry dic in ht)
				{
					try
					{
						textBox = (Excel.TextBox)workSheet.TextBoxes(dic.Key);
						textBox.Text = dic.Value.ToString();
					}
					catch
					{
						this.KillExcelProcess();
						throw new Exception("不存在ID为\"" + dic.Key.ToString() + "\"的文本框！");
					}
				}
			}
		}

		/// <summary>
		/// 向文本框写入数据，对指定WorkSheet操作
		/// </summary>
		/// <param name="ht">Hashtable的键值对保存文本框的ID和数据</param>
		public void SetTextBoxes(int sheetIndex,Hashtable ht)
		{
			if(ht.Count == 0) return;

			if(sheetIndex > this.WorkSheetCount)
			{
				this.KillExcelProcess();
				throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
			}

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

			foreach(DictionaryEntry dic in ht)
			{
				try
				{
					textBox = (Excel.TextBox)workSheet.TextBoxes(dic.Key);
					textBox.Text = dic.Value.ToString();
				}
				catch
				{
					this.KillExcelProcess();
					throw new Exception("不存在ID为\"" + dic.Key.ToString() + "\"的文本框！");
				}
			}
		}
		#endregion

		#region Cell Methods
		/// <summary>
		/// 向单元格写入数据，对当前WorkSheet操作
		/// </summary>