excelhelper.cs

一个c#的原码

			}
			
			if(letter.Length == 1)
			{
				char c1 = letter.ToCharArray()[0];

				if(!char.IsLetter(c1))
				{
					throw new Exception("格式不正确，必须是字母！");
				}

				c1 = char.ToUpper(c1);

				n = Convert.ToInt32(c1) - 64;
			}

			if(n > 256)
				throw new Exception("索引超出范围，Excel的列索引不能超过256！");
			
			return n;
		}

		/// <summary>
		/// 将Excel列的整数索引值转换为字符索引值
		/// </summary>
		/// <param name="n"></param>
		/// <returns></returns>
		public string IntToLetter(int n)
		{
			if(n > 256)
				throw new Exception("索引超出范围，Excel的列索引不能超过256！");
				
			int i = Convert.ToInt32(n / 26);
			int j = n % 26;

			char c1 = Convert.ToChar( i + 64 );
			char c2 = Convert.ToChar( j + 64 );

			if(n > 26)
				return c1.ToString() + c2.ToString();
			else if(n == 26)
				return "Z";
			else
				return c2.ToString();
		}

		#endregion

		#region Output File(注意：如果目标文件已存在的话会出错)
		/// <summary>
		/// 输出Excel文件并退出
		/// </summary>
		public void OutputExcelFile()
		{
			if(this.outputFile == null)
				throw new Exception("没有指定输出文件路径！");

			try
			{
				workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				this.Dispose();
			}
		}

		/// <summary>
		/// 输出指定格式的文件（支持格式：HTML，CSV，TEXT，EXCEL）
		/// </summary>
		/// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
		public void OutputFile(string format)
		{
			if(this.outputFile == null)
				throw new Exception("没有指定输出文件路径！");

			try
			{
				switch(format)
				{
					case "HTML":
					{
						workBook.SaveAs(outputFile,Excel.XlFileFormat.xlHtml,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
						break;
					}
					case "CSV":
					{
						workBook.SaveAs(outputFile,Excel.XlFileFormat.xlCSV,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
						break;
					}
					case "TEXT":
					{
						workBook.SaveAs(outputFile,Excel.XlFileFormat.xlHtml,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
						break;
					}
//					case "XML":
//					{
//						workBook.SaveAs(outputFile,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
//							Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
//							Type.Missing, Type.Missing, Type.Missing, Type.Missing,	Type.Missing);
//						break;
//
//					}
					default:
					{
						workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
						break;
					}
				}
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				this.Dispose();
			}
		}

		/// <summary>
		/// 保存文件
		/// </summary>
		public void SaveFile()
		{
			try
			{
				workBook.Save();			
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				this.Dispose();
			}
		}

		/// <summary>
		/// 另存文件
		/// </summary>
		public void SaveAsFile()
		{
			if(this.outputFile == null)
				throw new Exception("没有指定输出文件路径！");

			try
			{
				workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				this.Dispose();
			}
		}

		/// <summary>
		/// 将Excel文件另存为指定格式
		/// </summary>
		/// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
		public void SaveAsFile(string format)
		{
			if(this.outputFile == null)
				throw new Exception("没有指定输出文件路径！");

			try
			{
				switch(format)
				{
					case "HTML":
					{
						workBook.SaveAs(outputFile,Excel.XlFileFormat.xlHtml,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
						break;
					}
					case "CSV":
					{
						workBook.SaveAs(outputFile,Excel.XlFileFormat.xlCSV,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
						break;
					}
					case "TEXT":
					{
						workBook.SaveAs(outputFile,Excel.XlFileFormat.xlHtml,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
						break;
					}
//					case "XML":
//					{
//						workBook.SaveAs(outputFile,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
//							Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
//							Type.Missing, Type.Missing, Type.Missing, Type.Missing,	Type.Missing);
//						break;
//					}
					default:
					{
						workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
						break;
					}
				}
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				this.Dispose();
			}
		}

		/// <summary>
		/// 另存文件
		/// </summary>
		/// <param name="fileName">文件名</param>
		public void SaveFile(string fileName)
		{
			try
			{
				workBook.SaveAs(fileName,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				this.Dispose();
			}
		}

		/// <summary>
		/// 将Excel文件另存为指定格式
		/// </summary>
		/// <param name="fileName">文件名</param>
		/// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
		public void SaveAsFile(string fileName,string format)
		{
			try
			{
				switch(format)
				{
					case "HTML":
					{
						workBook.SaveAs(fileName,Excel.XlFileFormat.xlHtml,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
						break;
					}
					case "CSV":
					{
						workBook.SaveAs(fileName,Excel.XlFileFormat.xlCSV,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
						break;
					}
					case "TEXT":
					{
						workBook.SaveAs(fileName,Excel.XlFileFormat.xlHtml,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
						break;
					}
//					case "XML":
//					{
//						workBook.SaveAs(fileName,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
//							Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
//							Type.Missing, Type.Missing, Type.Missing, Type.Missing,	Type.Missing);
//						break;
//					}
					default:
					{
						workBook.SaveAs(fileName,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);			
						break;
					}
				}
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				this.Dispose();
			}
		}
		#endregion

		#endregion

		#region 私有方法

		/// <summary>
		/// 合并单元格，并赋值，对指定WorkSheet操作
		/// </summary>
		/// <param name="beginRowIndex">开始行索引</param>
		/// <param name="beginColumnIndex">开始列索引</param>
		/// <param name="endRowIndex">结束行索引</param>
		/// <param name="endColumnIndex">结束列索引</param>
		/// <param name="text">合并后Range的值</param>
		private void MergeCells(Excel.Worksheet sheet,int beginRowIndex,int beginColumnIndex,int endRowIndex,int endColumnIndex,string text)
		{
			if(sheet == null)
				return;

			range = sheet.get_Range(sheet.Cells[beginRowIndex,beginColumnIndex],sheet.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="columnIndex">要合并的列索引</param>
		/// <param name="beginRowIndex">合并开始行索引</param>
		/// <param name="rows">要合并的行数</param>
		private void MergeRows(Excel.Worksheet sheet,int columnIndex,int beginRowIndex,int rows)
		{
			int beginIndex = beginRowIndex;
			int count = 0;
			string text1;
			string text2;

			if(sheet == null)
				return;

			for(int j=beginRowIndex;j<beginRowIndex+rows;j++)
			{
				range1 = (Excel.Range)sheet.Cells[j,columnIndex];
				range2 = (Excel.Range)sheet.Cells[j+1,columnIndex];
				text1 = range1.Text.ToString();
				text2 = range2.Text.ToString();

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

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

			}

		}


		/// <summary>
		/// 计算WorkSheet数量
		/// </summary>
		/// <param name="rowCount">记录总行数</param>
		/// <param name="rows">每WorkSheet行数</param>
		public 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>
		public void KillExcelProcess()
		{
			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();
				}
			}
		}


		private void Dispose()
		{
			workBook.Close(null,null,null);
			app.Workbooks.Close();
			app.Quit();

			if(range != null)
			{
				System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
				range = null;
			}
			if(range1 != null)
			{
				System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
				range1 = null;
			}
			if(range2 != null)
			{
				System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
				range2 = null;
			}
			if(textBox != null)
			{
				System.Runtime.InteropServices.Marshal.ReleaseComObject(textBox);
				textBox = null;
			}
			if(workSheet != null)
			{
				System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
				workSheet = null;
			}
			if(workBook != null)
			{
				System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
				workBook = null;
			}
			if(app != null)
			{
				System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
				app = null;
			}

			GC.Collect();

			this.KillExcelProcess();
			
		}//end Dispose
		#endregion

		
	}//end class
}//end namespace