excelhelper.cs

一个c#的原码

		/// <param name="rowIndex">行索引</param>
		/// <param name="columnIndex">列索引</param>
		/// <param name="text">要写入的文本值</param>
		public void SetCells(int rowIndex,int columnIndex,string text)
		{
			try
			{
				workSheet.Cells[rowIndex,columnIndex] = text;
			}
			catch
			{
				this.KillExcelProcess();
				throw new Exception("向单元格[" + rowIndex + "," + columnIndex + "]写数据出错！");
			}
		}

		/// <summary>
		/// 向单元格写入数据，对指定WorkSheet操作
		/// </summary>
		/// <param name="sheetIndex">工作表索引</param>
		/// <param name="rowIndex">行索引</param>
		/// <param name="columnIndex">列索引</param>
		/// <param name="text">要写入的文本值</param>
		public void SetCells(int sheetIndex,int rowIndex,int columnIndex,string text)
		{
			try
			{
				this.ChangeCurrentWorkSheet(sheetIndex);	//改变当前工作表为指定工作表
				workSheet.Cells[rowIndex,columnIndex] = text;
			}
			catch
			{
				this.KillExcelProcess();
				throw new Exception("向单元格[" + rowIndex + "," + columnIndex + "]写数据出错！");
			}
		}

		/// <summary>
		/// 向单元格写入数据，对每个WorkSheet操作
		/// </summary>
		/// <param name="ht">Hashtable的键值对保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
		public void SetCells(Hashtable ht)
		{
			int rowIndex;
			int columnIndex;
			string position;

			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
					{
						position = dic.Key.ToString();
						rowIndex = Convert.ToInt32(position.Split(',')[0]);
						columnIndex = Convert.ToInt32(position.Split(',')[1]);
						
						workSheet.Cells[rowIndex,columnIndex] = dic.Value;
					}
					catch
					{
						this.KillExcelProcess();
						throw new Exception("向单元格[" + dic.Key + "]写数据出错！");
					}
				}
			}
		}

		/// <summary>
		/// 向单元格写入数据，对指定WorkSheet操作
		/// </summary>
		/// <param name="ht">Hashtable的键值对保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
		public void SetCells(int sheetIndex,Hashtable ht)
		{
			int rowIndex;
			int columnIndex;
			string position;

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

			if(ht.Count == 0) return;

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

			foreach(DictionaryEntry dic in ht)
			{
				try
				{
					position = dic.Key.ToString();
					rowIndex = Convert.ToInt32(position.Split(',')[0]);
					columnIndex = Convert.ToInt32(position.Split(',')[1]);
						
					workSheet.Cells[rowIndex,columnIndex] = dic.Value;
				}
				catch
				{
					this.KillExcelProcess();
					throw new Exception("向单元格[" + dic.Key + "]写数据出错！");
				}
			}
		}

		/// <summary>
		/// 设置单元格为可计算的
		/// </summary>
		/// <remarks>
		/// 如果Excel的单元格格式设置为数字，日期或者其他类型时，需要设置这些单元格的FormulaR1C1属性，
		/// 否则写到这些单元格的数据将不会按照预先设定的格式显示
		/// </remarks>
		/// <param name="arr">保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
		public void SetCells(int sheetIndex,string[] arr)
		{
			int rowIndex;
			int columnIndex;
			string position;

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

			if(arr.Length == 0) return;

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

			for(int i=0;i<arr.Length;i++)
			{
				try
				{
					position = arr[i];
					rowIndex = Convert.ToInt32(position.Split(',')[0]);
					columnIndex = Convert.ToInt32(position.Split(',')[1]);

					Excel.Range cell = (Excel.Range)workSheet.Cells[rowIndex,columnIndex];
					cell.FormulaR1C1 = cell.Text;
				}
				catch
				{
					this.KillExcelProcess();
					throw new Exception(string.Format("计算单元格{0}出错！",arr[i]));
				}
			}
		}

		/// <summary>
		/// 向单元格写入数据，对指定WorkSheet操作
		/// </summary>
		/// <param name="ht">Hashtable的键值对保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
		public void SetCells(string sheetName,Hashtable ht)
		{
			int rowIndex;
			int columnIndex;
			string position;
			Excel.Worksheet sheet = null;
			int sheetIndex = 0;

			if(ht.Count == 0) return;

			try
			{
				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)
				{
					foreach(DictionaryEntry dic in ht)
					{
						try
						{
							position = dic.Key.ToString();
							rowIndex = Convert.ToInt32(position.Split(',')[0]);
							columnIndex = Convert.ToInt32(position.Split(',')[1]);
						
							sheet.Cells[rowIndex,columnIndex] = dic.Value;
						}
						catch
						{
							this.KillExcelProcess();
							throw new Exception("向单元格[" + dic.Key + "]写数据出错！");
						}
					}
				}
				else
				{
					this.KillExcelProcess();
					throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
				}
			}
			catch(Exception e)
			{
				this.KillExcelProcess();
				throw e;
			}
		}


		/// <summary>
		/// 合并单元格，并赋值，对每个WorkSheet操作
		/// </summary>
		/// <param name="beginRowIndex">开始行索引</param>
		/// <param name="beginColumnIndex">开始列索引</param>
		/// <param name="endRowIndex">结束行索引</param>
		/// <param name="endColumnIndex">结束列索引</param>
		/// <param name="text">合并后Range的值</param>
		public void MergeCells(int beginRowIndex,int beginColumnIndex,int endRowIndex,int endColumnIndex,string text)
		{
			for(int i=1;i<=this.WorkSheetCount;i++)
			{
				workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
				range = workSheet.get_Range(workSheet.Cells[beginRowIndex,beginColumnIndex],workSheet.Cells[endRowIndex,endColumnIndex]);
				
				range.ClearContents();		//先把Range内容清除，合并才不会出错
				range.MergeCells = true;
				range.Value = text;
				range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
				range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
			}
		}

		/// <summary>
		/// 合并单元格，并赋值，对指定WorkSheet操作
		/// </summary>
		/// <param name="sheetIndex">WorkSheet索引</param>
		/// <param name="beginRowIndex">开始行索引</param>
		/// <param name="beginColumnIndex">开始列索引</param>
		/// <param name="endRowIndex">结束行索引</param>
		/// <param name="endColumnIndex">结束列索引</param>
		/// <param name="text">合并后Range的值</param>
		public void MergeCells(int sheetIndex,int beginRowIndex,int beginColumnIndex,int endRowIndex,int endColumnIndex,string text)
		{
			if(sheetIndex > this.WorkSheetCount)
			{
				this.KillExcelProcess();
				throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
			}

			workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
			range = workSheet.get_Range(workSheet.Cells[beginRowIndex,beginColumnIndex],workSheet.Cells[endRowIndex,endColumnIndex]);
				
			range.ClearContents();		//先把Range内容清除，合并才不会出错
			range.MergeCells = true;
			range.Value = text;
			range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
			range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
		}
		#endregion

		#region Row Methods
		/// <summary>
		/// 将指定索引列的数据相同的行合并，对每个WorkSheet操作
		/// </summary>
		/// <param name="columnIndex">列索引</param>
		/// <param name="beginRowIndex">开始行索引</param>
		/// <param name="endRowIndex">结束行索引</param>
		public void MergeRows(int columnIndex,int beginRowIndex,int endRowIndex)
		{
			if(endRowIndex - beginRowIndex < 1)
				return;

			for(int i=1;i<=this.WorkSheetCount;i++)
			{
				int beginIndex = beginRowIndex;
				int count = 0;
				string text1;
				string text2;
				workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);

				for(int j=beginRowIndex;j<=endRowIndex;j++)
				{
					range = (Excel.Range)workSheet.Cells[j,columnIndex];
					text1 = range.Text.ToString();

					range = (Excel.Range)workSheet.Cells[j+1,columnIndex];
					text2 = range.Text.ToString();

					if(text1 == text2)
					{
						++count;
					}
					else
					{				
						if(count > 0)
						{
							this.MergeCells(workSheet,beginIndex,columnIndex,beginIndex+count,columnIndex,text1);
						}

						beginIndex = j + 1;		//设置开始合并行索引
						count = 0;		//计数器清0
					}

				}

			}
		}


		/// <summary>
		/// 将指定索引列的数据相同的行合并，对指定WorkSheet操作
		/// </summary>
		/// <param name="sheetIndex">WorkSheet索引</param>
		/// <param name="columnIndex">列索引</param>
		/// <param name="beginRowIndex">开始行索引</param>
		/// <param name="endRowIndex">结束行索引</param>
		public void MergeRows(int sheetIndex,int columnIndex,int beginRowIndex,int endRowIndex)
		{
			if(sheetIndex > this.WorkSheetCount)
			{
				this.KillExcelProcess();
				throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
			}

			if(endRowIndex - beginRowIndex < 1)
				return;

			int beginIndex = beginRowIndex;
			int count = 0;
			string text1;
			string text2;
			workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);

			for(int j=beginRowIndex;j<=endRowIndex;j++)
			{
				range = (Excel.Range)workSheet.Cells[j,columnIndex];
				text1 = range.Text.ToString();

				range = (Excel.Range)workSheet.Cells[j+1,columnIndex];
				text2 = range.Text.ToString();

				if(text1 == text2)
				{
					++count;
				}
				else
				{				
					if(count > 0)
					{
						this.MergeCells(workSheet,beginIndex,columnIndex,beginIndex+count,columnIndex,text1);
					}

					beginIndex = j + 1;		//设置开始合并行索引
					count = 0;		//计数器清0
				}

			}

		}


		/// <summary>
		/// 插行（在指定行上面插入指定数量行）
		/// </summary>
		/// <param name="rowIndex"></param>
		/// <param name="count"></param>
		public void InsertRows(int rowIndex,int count)
		{
			try
			{
				for(int n=1;n<=this.WorkSheetCount;n++)
				{
					workSheet = (Excel.Worksheet)workBook.Worksheets[n];
					range = (Excel.Range)workSheet.Rows[rowIndex,this.missing];

					for(int i=0;i<count;i++)
					{
						range.Insert(Excel.XlDirection.xlDown);
					}
				}
			}
			catch(Exception e)
			{
				this.KillExcelProcess();
				throw e;
			}
		}

		/// <summary>
		/// 插行（在指定WorkSheet指定行上面插入指定数量行）
		/// </summary>
		/// <param name="sheetIndex"></param>
		/// <param name="rowIndex"></param>
		/// <param name="count"></param>
		public void InsertRows(int sheetIndex,int rowIndex,int count)
		{
			if(sheetIndex > this.WorkSheetCount)
			{
				this.KillExcelProcess();
				throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
			}

			try
			{
				workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
				range = (Excel.Range)workSheet.Rows[rowIndex,this.missing];

				for(int i=0;i<count;i++)
				{
					range.Insert(Excel.XlDirection.xlDown);
				}
			}
			catch(Exception e)
			{
				this.KillExcelProcess();
				throw e;
			}
		}

		/// <summary>
		/// 复制行（在指定行下面复制指定数量行）
		/// </summary>
		/// <param name="rowIndex"></param>
		/// <param name="count"></param>
		public void CopyRows(int rowIndex,int count)
		{
			try
			{
				for(int n=1;n<=this.WorkSheetCount;n++)
				{
					workSheet = (Excel.Worksheet)workBook.Worksheets[n];
					range1 = (Excel.Range)workSheet.Rows[rowIndex,this.missing];

					for(int i=1;i<=count;i++)
					{
						range2 = (Excel.Range)workSheet.Rows[rowIndex + i,this.missing];
						range1.Copy(range2);
					}
				}
			}
			catch(Exception e)
			{
				this.KillExcelProcess();
				throw e;