using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.Office.Interop.Excel; using Microsoft.Office.Interop; using Microsoft.Office.Core; using System.IO; using System.Windows.Forms; using System.Data; using System.Reflection; namespace WinFormsApplication { public class ExcelLib { //http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfexcelapplicationobject.asp #region Variables private Microsoft.Office.Interop.Excel.Application excelApplication = null; private Microsoft.Office.Interop.Excel.Workbooks excelWorkBooks = null; private Microsoft.Office.Interop.Excel.Workbook excelWorkBook = null; private Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = null; private Microsoft.Office.Interop.Excel.Range excelRange = null;//Excel Range Object,多¨¤种?用®?途ª? private Microsoft.Office.Interop.Excel.Range excelCopySourceRange = null;//Excel Range Object private int excelActiveWorkSheetIndex; //活?动¡¥工¡è作Á¡Â表À¨ª索¡Â引°y private string excelOpenFileName = ""; //操¨´作Á¡ÂExcel的Ì?路¡¤径? private string excelSaveFileName = ""; //保À¡ê存ä?Excel的Ì?路¡¤径? #endregion #region Properties public int ActiveSheetIndex { get { return excelActiveWorkSheetIndex; } set { excelActiveWorkSheetIndex = value; } } public string OpenFileName { get { return excelOpenFileName; } set { excelOpenFileName = value; } } public string SaveFileName { get { return excelSaveFileName; } set { excelSaveFileName = value; } } #endregion // //-------------------------------------------------------------------------------------------------------- /// <summary> /// 构1造¨¬函¡¥数ºy；ê? /// </summary> public ExcelLib() { excelApplication = null;//Excel Application Object excelWorkBooks = null;//Workbooks excelWorkBook = null;//Excel Workbook Object excelWorkSheet = null;//Excel Worksheet Object ActiveSheetIndex = 1; //默?认¨?值¦Ì活?动¡¥工¡è作Á¡Â簿?为a第̨²一°?个?；ê?设¦¨¨置?活?动¡¥工¡è作Á¡Â簿?请?参?阅?SetActiveWorkSheet() } /// <summary> /// 以°?excelOpenFileName为a模¡ê板ã?新?建¡§Excel文?件t /// </summary> public bool OpenExcelFile() { if (excelApplication != null) CloseExcelApplication(); //检¨¬查¨¦文?件t是º?否¤?存ä?在¨² if (excelOpenFileName == "") { throw new Exception("请?选?择?文?件t！ê?"); } if (!File.Exists(excelOpenFileName)) { throw new Exception(excelOpenFileName + "该?文?件t不?存ä?在¨²！ê?");//该?异°¨¬常¡ê如¨?何?处ä|理¤¨ª，ê?由®¨¦什º2么¡ä处ä|理¤¨ª？ê?？ê?？ê?？ê? } try { excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelWorkBooks = excelApplication.Workbooks; excelWorkBook = ((Microsoft.Office.Interop.Excel.Workbook)excelWorkBooks.Open(excelOpenFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); excelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[excelActiveWorkSheetIndex]; excelApplication.Visible = false; return true; } catch (Exception e) { CloseExcelApplication(); MessageBox.Show("(1)没?有®D安ã2装Á¡ãExcel 2003；ê?(2)或¨°没?有®D安ã2装Á¡ãExcel 2003 .NET 可¨¦编À¨¤程¨¬性?支¡ì持?；ê?/n详¨º细?信?息¡é：êo" + e.Message, "提¬¨¢示º?", MessageBoxButtons.OK, MessageBoxIcon.Information); //throw new Exception(e.Message); return false; } } /// <summary> /// 读¨¢取¨?一°?个?Cell的Ì?值¦Ì /// </summary> /// <param name="CellRowID">要°a读¨¢取¨?的Ì?Cell的Ì?行D索¡Â引°y</param> /// <param name="CellColumnID">要°a读¨¢取¨?的Ì?Cell的Ì?列¢D索¡Â引°y</param> /// <returns>Cell的Ì?值¦Ì</returns> public string getOneCellValue(int CellRowID, int CellColumnID) { if (CellRowID <= 0) { throw new Exception("行D索¡Â引°y超?出?范¤?围¡ì！ê?"); } string sValue = ""; try { sValue = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID]).Text.ToString(); } catch (Exception e) { CloseExcelApplication(); throw new Exception(e.Message); } return (sValue); } /// <summary> /// 读¨¢取¨?一°?个?连¢?续?区?域®¨°的Ì?Cell的Ì?值¦Ì(矩?形?区?域®¨°，ê?包㨹含?一°?行D或¨°一°?列¢D,或¨°多¨¤行D，ê?多¨¤列¢D)，ê?返¤¦Ì回?一°?个?一°?维?字Á?符¤?串ä?数ºy组Á¨¦。¡ê /// </summary> /// <param name="StartCell">StartCell是º?要°a写¡ä入¨?区?域®¨°的Ì?左Á¨®上¦?角?单Ì£¤元a格?</param> /// <param name="EndCell">EndCell是º?要°a写¡ä入¨?区?域®¨°的Ì?右®¨°下?角?单Ì£¤元a格?</param> /// <returns>值¦Ì的Ì?集¡¥合?</returns> public string[] getCellsValue(string StartCell, string EndCell) { string[] sValue = null; //try //{ excelRange = (Microsoft.Office.Interop.Excel.Range)excelWorkSheet.get_Range(StartCell, EndCell); sValue = new string[excelRange.Count]; int rowStartIndex = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.get_Range(StartCell, StartCell)).Row; //起e始º?行D号? int columnStartIndex = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.get_Range(StartCell, StartCell)).Column; //起e始º?列¢D号? int rowNum = excelRange.Rows.Count; //行D数ºy目? int columnNum = excelRange.Columns.Count; //列¢D数ºy目? int index = 0; for (int i = rowStartIndex; i < rowStartIndex + rowNum; i++) { for (int j = columnStartIndex; j < columnNum + columnStartIndex; j++) { //读¨¢到Ì?空?值¦Ìnull和¨ª读¨¢到Ì?空?串ä?""分¤?别Àe处ä|理¤¨ª sValue[index] = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[i, j]).Text.ToString(); index++; } } //} //catch (Exception e) //{ // CloseExcelApplication(); // throw new Exception(e.Message); //} return (sValue); } /// <summary> /// 读¨¢取¨?所¨´有®D单Ì£¤元a格?的Ì?数ºy据Y(矩?形?区?域®¨°)，ê?返¤¦Ì回?一°?个?datatable.假¨´设¦¨¨所¨´有®D单Ì£¤元a格?靠?工¡è作Á¡Â表À¨ª左Á¨®上¦?区?域®¨°。¡ê /// </summary> public System.Data.DataTable getAllCellsValue() { int columnCount = getTotalColumnCount(); int rowCount = getTotalRowCount(); System.Data.DataTable dt = new System.Data.DataTable(); //设¦¨¨置?datatable列¢D的Ì?名?称? for (int columnID = 1; columnID <= columnCount; columnID++) { dt.Columns.Add(((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, columnID]).Text.ToString()); } for (int rowID = 2; rowID <= rowCount; rowID++) { DataRow dr = dt.NewRow(); for (int columnID = 1; columnID <= columnCount; columnID++) { dr[columnID - 1] = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowID, columnID]).Text.ToString(); //读¨¢到Ì?空?值¦Ìnull和¨ª读¨¢到Ì?空?串ä?""分¤?别Àe处ä|理¤¨ª } dt.Rows.Add(dr); } return (dt); } public int getTotalRowCount() {//当Ì¡À前¡ã活?动¡¥工¡è作Á¡Â表À¨ª中D有®D效¡ì行D数ºy(总Á¨¹行D数ºy) int rowsNumber = 0; try { while (true) { if (((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowsNumber + 1, 1]).Text.ToString().Trim() == "" & ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowsNumber + 2, 1]).Text.ToString().Trim() == "" & ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowsNumber + 3, 1]).Text.ToString().Trim() == "") break; rowsNumber++; } } catch { return -1; } return rowsNumber; } /// <summary> /// 当Ì¡À前¡ã活?动¡¥工¡è作Á¡Â表À¨ª中D有®D效¡ì列¢D数ºy(总Á¨¹列¢D数ºy) /// </summary> /// <param></param> public int getTotalColumnCount() { int columnNumber = 0; try { while (true) { if (((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, columnNumber + 1]).Text.ToString().Trim() == "" & ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, columnNumber + 2]).Text.ToString().Trim() == "" & ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, columnNumber + 3]).Text.ToString().Trim() == "") break; columnNumber++; } } catch { return -1; } return columnNumber; } /// <summary> /// 向¨°一°?个?Cell写¡ä入¨?数ºy据Y /// </summary> /// <param name="CellRowID">CellRowID是º?cell的Ì?行D索¡Â引°y</param> /// <param name="CellColumnID">CellColumnID是º?cell的Ì?列¢D索¡Â引°y</param> ///<param name="Value">要°a写¡ä入¨?该?单Ì£¤元a格?的Ì?数ºy据Y值¦Ì</param> public void setOneCellValue(int CellRowID, int CellColumnID, string Value) { try { excelRange = (Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID]; excelRange.Value2 = Value;//Value2? //Gets or sets the value of the NamedRange control. //The only difference between this property and the Value property is that Value2 is not a parameterized property. excelRange = null; } catch (Exception e) { CloseExcelApplication(); throw new Exception(e.Message); } } /// <summary> /// 设¦¨¨置?活?动¡¥工¡è作Á¡Â表À¨ª /// </summary> /// <param name="SheetIndex">要°a设¦¨¨置?为a活?动¡¥工¡è作Á¡Â表À¨ª的Ì?索¡Â引°y值¦Ì</param> public void SetActiveWorkSheet(int SheetIndex) { if (SheetIndex <= 0) { throw new Exception("索¡Â引°y超?出?范¤?围¡ì！ê?"); } try { ActiveSheetIndex = SheetIndex; excelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[ActiveSheetIndex]; } catch (Exception e) { CloseExcelApplication(); throw new Exception(e.Message); } } /// <summary> /// 向¨°连¢?续?区?域®¨°一°?次ä?性?写¡ä入¨?数ºy据Y；ê?只?有®D在¨²区?域®¨°连¢?续?和¨ª写¡ä入¨?的Ì?值¦Ì相¨¤同ª?的Ì?情¨¦况?下?可¨¦以°?使º1用®?方¤?法¤¡§ /// </summary> /// <param name="StartCell">StartCell是º?要°a写¡ä入¨?区?域®¨°的Ì?左Á¨®上¦?角?单Ì£¤元a格?</param> /// <param name="EndCell">EndCell是º?要°a写¡ä入¨?区?域®¨°的Ì?右®¨°下?角?单Ì£¤元a格?</param> /// <param name="Value">要°a写¡ä入¨?指?定¡§区?域®¨°所¨´有®D单Ì£¤元a格?的Ì?数ºy据Y值¦Ì</param> public void setCellsValue(string StartCell, string EndCell, string Value) { try { excelRange = excelWorkSheet.get_Range(StartCell, EndCell); excelRange.Value2 = Value; excelRange = null; } catch (Exception e) { CloseExcelApplication(); throw new Exception(e.Message); } } /// <summary> /// 给?一°?行D写¡ä数ºy据Y /// </summary> public void setOneLineValues(int LineID, int StartCellColumnID, int EndCellColumnID, string[] Values)////已°?经-测a试º? { //用®?1-19号?元a素? //if (Values.Length!=EndCellColumnID-StartCellColumnID) //{ // throw new Exception("单Ì£¤元a格?数ºy目?与®?提¬¨¢供?的Ì?值¦Ì的Ì?数ºy目?不?一°?致?！ê?"); //} for (int i = StartCellColumnID; i <= EndCellColumnID; i++) { setOneCellValue(LineID, i, Values[i]); } } public void setCellsBorder(string startCell, string endCell) { //设¦¨¨置?某3个?范¤?围¡ì内¨²的Ì?单Ì£¤元a格?的Ì?边À?框¨° excelRange = excelWorkSheet.get_Range(startCell, endCell); excelRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excelRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excelRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excelRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excelRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //excelRange.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; } public void setOneCellBorder(int CellRowID, int CellColumnID) { //设¦¨¨置?某3个?单Ì£¤元a格?的Ì?边À?框¨° excelRange = (Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID]; excelRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excelRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excelRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; excelRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //excelRange.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; //excelRange.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; } public void SetColumnWidth(string startCell, string endCell, int size) { //设¦¨¨置?某3个?范¤?围¡ì内¨²的Ì?单Ì£¤元a格?的Ì?列¢D的Ì?宽¨ª度¨¨ excelRange = excelWorkSheet.get_Range(startCell, endCell); excelRange.ColumnWidth = size; } public void SetOneCellFont(int CellRowID, int CellColumnID, string fontName, int fontSize) { excelRange = (Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID]; excelRange.Font.Name = fontName; excelRange.Font.Size = fontSize; } public void SetOneCellColor(int CellRowID, int CellColumnID, ColorIndex color) { excelRange = (Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID]; excelRange.Font.ColorIndex = color; } public void SetOneCellHorizontalAlignment(int CellRowID, int CellColumnID, Microsoft.Office.Interop.Excel.Constants alignment) { excelRange = (Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID]; excelRange.HorizontalAlignment = alignment; } public void SetOneCellColumnWidth(int CellRowID, int CellColumnID, int size) { //设¦¨¨置?某3个?单Ì£¤元a格?的Ì?列¢D的Ì?宽¨ª度¨¨ excelRange = (Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID]; excelRange.ColumnWidth = size; } /// <summary> /// 设¦¨¨置?一°?个?Cell的Ì?数ºy据Y格?式º? /// </summary> /// <param name="CellRowID">CellRowID是º?cell的Ì?行D索¡Â引°y</param> /// <param name="CellColumnID">CellColumnID是º?cell的Ì?列¢D索¡Â引°y</param> ///<param name="Value">数ºy据Y格?式º?</param> public void setOneCellNumberFormat(int CellRowID, int CellColumnID, string numberFormat) { try { excelRange = (Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID]; excelRange.NumberFormatLocal = numberFormat; excelRange = null; } catch (Exception e) { CloseExcelApplication(); throw new Exception(e.Message); } } public void SetRowHeight(string startCell, string endCell, int size) { //设¦¨¨置?某3个?范¤?围¡ì内¨²的Ì?单Ì£¤元a格?的Ì?行D的Ì?高?度¨¨ excelRange = excelWorkSheet.get_Range(startCell, endCell); excelRange.RowHeight = size; } public void SetRowHeight(int CellRowID, int CellColumnID, float size) { //设¦¨¨置?某3个?范¤?围¡ì内¨²的Ì?单Ì£¤元a格?的Ì?行D的Ì?高?度¨¨ excelRange = (Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID]; excelRange.RowHeight = size; } public void SetOneCellRowHeight(int CellRowID, int CellColumnID, int size) { //设¦¨¨置?某3个?单Ì£¤元a格?的Ì?行D的Ì?高?度¨¨ excelRange = (Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID]; excelRange.RowHeight = size; } /// <summary> /// 拷?贝À¡ä区?域®¨°.限T制?：êo在¨²同ª?一°?个?工¡è作Á¡Â表À¨ª中D复¡ä制? /// </summary> /// <param name="SourceStart">源¡ä区?域®¨°的Ì?左Á¨®上¦?角?单Ì£¤元a格?</param> /// <param name="SourceEnd">源¡ä区?域®¨°的Ì?右®¨°下?角?单Ì£¤元a格?</param> /// <param name="DesStart">目?标À¨º区?域®¨°的Ì?左Á¨®上¦?角?单Ì£¤元a格?</param> /// <param name="DesEnd">目?标À¨º区?域®¨°的Ì?右®¨°下?角?单Ì£¤元a格?</param> public void CopyCells(string SourceStart, string SourceEnd, string DesStart, string DesEnd) { try { excelCopySourceRange = excelWorkSheet.get_Range(SourceStart, SourceEnd); excelRange = excelWorkSheet.get_Range(DesStart, DesEnd); excelCopySourceRange.Copy(excelRange); excelCopySourceRange = null; excelRange = null; } catch (Exception e) { CloseExcelApplication(); throw new Exception(e.Message); } } public void CopyWorksheet(int SourceWorksheetIndex, int DesWorksheetIndex) { try { // Sheets("Sheet2").Select //Sheets("Sheet2").Copy After:=Sheets(3) Microsoft.Office.Interop.Excel.Worksheet sheetSource = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[SourceWorksheetIndex]; sheetSource.Select(Missing.Value); Microsoft.Office.Interop.Excel.Worksheet sheetDest = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[DesWorksheetIndex]; sheetSource.Copy(Missing.Value, sheetDest); } catch (Exception e) { CloseExcelApplication(); throw new Exception(e.Message); } } /// <summary> /// 插?入¨?一°?行D /// </summary> /// <param name="CellRowID">要°a插?入¨?所¨´在¨²行D的Ì?索¡Â引°y位?置?，ê?插?入¨?后¨®其?原-有®D行D下?移°?</param> /// <param name="RowNum">要°a插?入¨?行D的Ì?个?数ºy</param> public void InsertRow(int CellRowID, int RowNum)//插?入¨?空?行D { if (CellRowID <= 0) { throw new Exception("行D索¡Â引°y超?出?范¤?围¡ì！ê?"); } if (RowNum <= 0) { throw new Exception("插?入¨?行D数ºy无T效¡ì！ê?"); } try { excelRange = (Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Rows[CellRowID, Missing.Value]; for (int i = 0; i < RowNum; i++) { excelRange.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, Missing.Value); } excelRange = null; } catch (Exception e) { CloseExcelApplication(); throw new Exception(e.Message); } } /// <summary> /// 保À¡ê存ä?Excel文?件t /// </summary> public Microsoft.Office.Interop.Excel.Range FindFirstRange(Microsoft.Office.Interop.Excel.Range xlRange, string FindText)//查¨¦找¨°//没?有®D测a试º? { //查¨¦找¨°第̨²一°?个?满¨²足Á?的Ì?区?域®¨° //Search for the first match Microsoft.Office.Interop.Excel.Range firstFind = null; firstFind = xlRange.Find(FindText, Missing.Value, Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlPart, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, Missing.Value, Missing.Value); return firstFind; //如¨?果?没?找¨°到Ì?，ê?返¤¦Ì回?空? } //http://msdn.microsoft.com/library/en-us/dv_wrcore/html/wrtskHowToSearchForTextInWorksheetRanges.asp?frame=true /// <summary> /// 当Ì¡À前¡ã活?动¡¥工¡è作Á¡Â表À¨ª中D有®D效¡ì行D数ºy(总Á¨¹行D数ºy) /// </summary> /// <param></param> /// <summary> /// 判D断?单Ì£¤元a格?是º?否¤?有®D数ºy据Y /// </summary> public bool CellValueIsNull(int CellLineID, int CellColumnID)////已°?经-测a试º? { //判D断?单Ì£¤元a格?是º?否¤?有®D数ºy据Y if ((((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[CellLineID, CellColumnID]).Text.ToString().Trim() != "")) return false; return true; } public void newWorkbook(string excelTemplate, string fileName) { //以°?excelTemplate为a模¡ê板ã?新?建¡§文?件tfileName //excelApplication. excelWorkBook = excelWorkBooks.Add(excelTemplate); SaveFileName = ""; SaveExcel(); } public void newWorksheet() { excelWorkBook.Worksheets.Add(Missing.Value, Missing.Value, 1, Missing.Value); } public void setWorksheetName(int sheetIndex, string worksheetName) { // Missing.Value Microsoft.Office.Interop.Excel._Worksheet sheet = (Microsoft.Office.Interop.Excel._Worksheet)(excelWorkBook.Worksheets[(object)sheetIndex]); sheet.Name = worksheetName; } public void mergeOneLineCells(string startCell, string endCell) { //合?并¡é一°?行D单Ì£¤元a格? excelRange = excelWorkSheet.get_Range(startCell, endCell); //excelRange.Merge(true); excelRange.MergeCells = true; } public void HorizontalAlignmentCells(string startCell, string endCell, Microsoft.Office.Interop.Excel.Constants alignment) { //水?平?对?齐?一°?行D单Ì£¤元a格? excelRange = excelWorkSheet.get_Range(startCell, endCell); excelRange.HorizontalAlignment = alignment; } public void VerticalAlignmentCells(string startCell, string endCell, Microsoft.Office.Interop.Excel.Constants alignment) { //垂ä1直¡À对?齐?一°?行D单Ì£¤元a格? excelRange = excelWorkSheet.get_Range(startCell, endCell); excelRange.VerticalAlignment = alignment; } //实º¦Ì现?列¢D号?-〉¦Ì字Á?母? (26-〉¦ÌZ,27->AA) private string ConvertColumnIndexToChar(int columnIndex) { if (columnIndex < 1 || columnIndex > 256) { MessageBox.Show("columnIndex=" + columnIndex + ",超?出?了¢?有®D效¡ì范¤?围¡ì（ꡧ1-256）ê?"); return "A"; } if (columnIndex >= 1 && columnIndex <= 26)//1--26 { return "AA"; } if (columnIndex >= 27 && columnIndex <= 256)//27--256 { return "AA"; } return "A"; } //字Á?母?-〉¦Ì列¢D号? Z-〉¦Ì26 public void SaveExcel() { if (excelSaveFileName == "") { throw new Exception("未¡ä指?定¡§要°a保À¡ê存ä?的Ì?文?件t名?"); } try { //excelWorkSheet.(excelSaveFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value); excelWorkSheet.SaveAs(excelSaveFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value); } catch (Exception e) { CloseExcelApplication(); throw new Exception(e.Message); } } //-------------------------------------------------------------------------------------------------------- /// <summary> /// 保À¡ê存ä?Excel文?件t，ê?格?式º?xml. /// </summary> public void SaveExcelAsXML() { if (excelSaveFileName == "") { throw new Exception("未¡ä指?定¡§要°a保À¡ê存ä?的Ì?文?件t名?"); } try { //excelWorkSheet.SaveAs(excelSaveFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value); excelWorkSheet.SaveAs(excelSaveFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value); } catch (Exception e) { CloseExcelApplication(); throw new Exception(e.Message); } } //-------------------------------------------------------------------------------------------------------- /// <summary> /// 关?闭À?Excel文?件t，ê?释º¨ª放¤?对?象¨®；ê?最Á?后¨®一°?定¡§要°a调Ì¡Â用®?此ä?函¡¥数ºy，ê?否¤?则¨°会¨¢引°y起e异°¨¬常¡ê /// </summary> /// <param></param> public void CloseExcelApplication() { try { excelWorkBooks = null; excelWorkBook = null; excelWorkSheet = null; excelRange = null; if (excelApplication != null) { excelApplication.Workbooks.Close(); //Object missing = Type.Missing; excelApplication.Quit(); excelApplication = null; //ReleaseAllRef(excelApplication);//Error } } finally { GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } } private void ReleaseAllRef(Object obj) {//ReleaseComObject()方¤?法¤¡§可¨¦以°?使º1RCW减?少¦¨´一°?个?对?COM组Á¨¦件t的Ì?引°y用®?，ê?并¡é返¤¦Ì回?减?少¦¨´一°?个?引°y用®?后¨®RCW对?COM组Á¨¦件t的Ì?剩º¡ê余®¨¤引°y用®?数ºy量¢?。¡ê //我¨°们?用®?一°?个?循-环¡¤，ê?就¨ª可¨¦以°?让¨?RCW将?所¨´有®D对?COM组Á¨¦件t的Ì?引°y用®?全¨?部?去¨£¤掉Ì?。¡ê try { while (System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) > 1) ; } finally { obj = null; } } } public enum ColorIndex { 无T色¦? = -4142, 自Á?动¡¥ = -4105, 黑¨²色¦? = 1, 褐?色¦? = 53, 橄¦?榄¦- = 52, 深¦?绿¨¬ = 51, 深¦?青¨¤ = 49, 深¦?蓝¤? = 11, 靛Ì?蓝¤? = 55, 灰¨°色¦?80 = 56, 深¦?红¨¬ = 9, 橙¨¨色¦? = 46, 深¦?黄? = 12, 绿¨¬色¦? = 10, 青¨¤色¦? = 14, 蓝¤?色¦? = 5, 蓝¤?灰¨° = 47, 灰¨°色¦?50 = 16, 红¨¬色¦? = 3, 浅3橙¨¨色¦? = 45, 酸¨¢橙¨¨色¦? = 43, 海¡ê绿¨¬ = 50, 水?绿¨¬色¦? = 42, 浅3蓝¤? = 41, 紫Á?罗T兰¤? = 13, 灰¨°色¦?40 = 48, 粉¤?红¨¬ = 7, 金e色¦? = 44, 黄?色¦? = 6, 鲜¨º绿¨¬ = 4, 青¨¤绿¨¬ = 8, 天¬¨¬蓝¤? = 33, 梅¡¤红¨¬ = 54, 灰¨°色¦?25 = 15, 玫¦Ì瑰?红¨¬ = 38, 茶¨¨色¦? = 40, 浅3黄? = 36, 浅3绿¨¬ = 35, 浅3青¨¤绿¨¬ = 34, 淡Ì-蓝¤? = 37, 淡Ì-紫Á? = 39, 白ã¡Á色¦? = 2 } }