| /// <summary> /// author by :ljun /// Excel工具類,，目前僅支持一個(gè)工作薄的操作 /// </summary> public class ExcelHelper : IDisposable { #region 構(gòu)造函數(shù) /// <summary> /// 構(gòu)造函數(shù),，將一個(gè)已有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; excelApp = new Excel.ApplicationClass(); excelApp.Visible = false; excelApp.DisplayAlerts = false; //是否需要顯示提示 excelApp.AlertBeforeOverwriting = false; //是否彈出提示覆蓋 //打開模板文件,，得到WorkBook對(duì)象 workBook = excelApp.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, Type.Missing, Type.Missing); //得到WorkSheet對(duì)象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); } /// <summary> /// 構(gòu)造函數(shù)，新建一個(gè)工作簿 /// </summary> public ExcelHelper() { excelApp = new Excel.ApplicationClass(); excelApp.Visible = false; //設(shè)置禁止彈出保存和覆蓋的詢問提示框 excelApp.DisplayAlerts = false; excelApp.AlertBeforeOverwriting = false; //新建一個(gè)WorkBook workBook = excelApp.Workbooks.Add(Type.Missing); //得到WorkSheet對(duì)象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); } #endregion #region 私有變量 private string templetFile = null; private string outputFile = null; private object missing = System.Reflection.Missing.Value; private Excel.Application excelApp; private Excel.Workbook workBook; private Excel.Worksheet workSheet; private Excel.Range range; private Excel.Range range1; private Excel.Range range2; #endregion #region 公共屬性 /// <summary> /// WorkSheet數(shù)量 /// </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 批量寫入Excel內(nèi)容 /// <summary> /// 將二維數(shù)組數(shù)據(jù)寫入Excel文件 /// </summary> /// <param name="arr">二維數(shù)組</param> /// <param name="top">行索引</param> /// <param name="left">列索引</param> public void ArrayToExcel(object[,] arr, int top, int left) { int rowCount = arr.GetLength(0); //二維數(shù)組行數(shù)（一維長(zhǎng)度） int colCount = arr.GetLength(1); //二維數(shù)據(jù)列數(shù)（二維長(zhǎng)度） range = (Excel.Range)workSheet.Cells[top, left]; range = range.get_Resize(rowCount, colCount); range.FormulaArray = arr; } #endregion #region 行操作 /// <summary> /// 插行（在指定行上面插入指定數(shù)量行） /// </summary> /// <param name="rowIndex"></param> /// <param name="count"></param> public void InsertRows(int rowIndex, int count) { try { range = (Excel.Range)workSheet.Rows[rowIndex, this.missing]; for (int i = 0; i < count; i++) { range.Insert(Excel.XlDirection.xlDown, missing); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } } /// <summary> /// 復(fù)制行（在指定行下面復(fù)制指定數(shù)量行） /// </summary> /// <param name="rowIndex"></param> /// <param name="count"></param> public void CopyRows(int rowIndex, int count) { try { 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(false); throw e; } } /// <summary> /// 刪除行 /// </summary> /// <param name="sheetIndex"></param> /// <param name="rowIndex"></param> /// <param name="count"></param> public void DeleteRows(int rowIndex, int count) { try { for (int i = 0; i < count; i++) { range = (Excel.Range)workSheet.Rows[rowIndex, this.missing]; range.Delete(Excel.XlDirection.xlDown); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } } #endregion #region 列操作 /// <summary> /// 插列（在指定列右邊插入指定數(shù)量列） /// </summary> /// <param name="columnIndex"></param> /// <param name="count"></param> public void InsertColumns(int columnIndex, int count) { try { range = (Excel.Range)(workSheet.Columns[columnIndex, this.missing]); //注意：這里和VS的智能提示不一樣,，第一個(gè)參數(shù)是columnindex for (int i = 0; i < count; i++) { range.Insert(Excel.XlDirection.xlDown, missing); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } } /// <summary> /// 刪除列 /// </summary> /// <param name="columnIndex"></param> /// <param name="count"></param> public void DeleteColumns(int columnIndex, int count) { try { for (int i = columnIndex + count-1; i >= columnIndex; i--) { ((Excel.Range)workSheet.Cells[1, i]).EntireColumn.Delete(0); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } } #endregion #region 單元格操作 /// <summary> /// 合并單元格,，并賦值，對(duì)指定WorkSheet操作 /// </summary> /// <param name="sheetIndex">WorkSheet索引</param> /// <param name="beginRowIndex">開始行索引</param> /// <param name="beginColumnIndex">開始列索引</param> /// <param name="endRowIndex">結(jié)束行索引</param> /// <param name="endColumnIndex">結(jié)束列索引</param> /// <param name="text">合并后Range的值</param> public void MergeCells(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text) { range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]); range.ClearContents(); //先把Range內(nèi)容清除,，合并才不會(huì)出錯(cuò) range.MergeCells = true; range.Value2 = text; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; } /// <summary> /// 向單元格寫入數(shù)據(jù),，對(duì)當(dāng)前WorkSheet操作 /// </summary> /// <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(false); throw new Exception("向單元格[" + rowIndex + "," + columnIndex + "]寫數(shù)據(jù)出錯(cuò)！"); } } /// <summary> /// 向單元格寫入數(shù)據(jù),，對(duì)當(dāng)前WorkSheet操作 /// </summary> /// <param name="rowIndex">行索引</param> /// <param name="columnIndex">列索引</param> /// <param name="text">要寫入的文本值</param> public void SetCells(int rowIndex, int columnIndex, string text, string comment) { try { workSheet.Cells[rowIndex, columnIndex] = text; SetCellComment(rowIndex, columnIndex, comment); } catch { this.KillExcelProcess(false); throw new Exception("向單元格[" + rowIndex + "," + columnIndex + "]寫數(shù)據(jù)出錯(cuò),！"); } } /// <summary> /// 向單元格寫入數(shù)據(jù),，對(duì)當(dāng)前WorkSheet操作 /// </summary> /// <param name="rowIndex">行索引</param> /// <param name="columnIndex">列索引</param> /// <param name="text">要寫入的文本值</param> public void SetCellComment(int rowIndex, int columnIndex, string comment) { try { Excel.Range range = workSheet.Cells[rowIndex, columnIndex] as Excel.Range; range.AddComment(comment); } catch { this.KillExcelProcess(false); throw new Exception("向單元格[" + rowIndex + "," + columnIndex + "]寫數(shù)據(jù)出錯(cuò)！"); } } /// <summary> /// 單元格背景色及填充方式 /// </summary> /// <param name="startRow">起始行</param> /// <param name="startColumn">起始列</param> /// <param name="endRow">結(jié)束行</param> /// <param name="endColumn">結(jié)束列</param> /// <param name="color">顏色索引</param> public void SetCellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color) { Excel.Range range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]); range.Interior.ColorIndex = color; } #endregion #region 保存文件 /// <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, missing); } catch (Exception e) { throw e; } finally { this.Quit(); } } /// <summary> /// 將Excel文件另存為指定格式 /// </summary> /// <param name="format">HTML,，CSV，TEXT,，EXCEL,，XML</param> public void SaveAsFile(SaveAsFileFormat format) { if (this.outputFile == null) throw new Exception("沒有指定輸出文件路徑！"); try { switch (format) { case SaveAsFileFormat.HTML: { workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); break; } case SaveAsFileFormat.CSV: { workBook.SaveAs(outputFile, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); break; } case SaveAsFileFormat.TEXT: { workBook.SaveAs(outputFile, Excel.XlFileFormat.xlUnicodeText, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); break; } case SaveAsFileFormat.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, missing); break; } } } catch (Exception e) { throw e; } finally { this.Quit(); } } #endregion #region 殺進(jìn)程釋放資源 /// <summary> /// 結(jié)束Excel進(jìn)程 /// </summary> public void KillExcelProcess(bool bAll) { if (bAll) { KillAllExcelProcess(); } else { KillSpecialExcel(); } } [DllImport("user32.dll", SetLastError = true)] static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId); /// <summary> /// 殺特殊進(jìn)程的Excel /// </summary> public void KillSpecialExcel() { try { if (excelApp != null) { int lpdwProcessId; GetWindowThreadProcessId((IntPtr)excelApp.Hwnd, out lpdwProcessId); if (lpdwProcessId > 0) //c-s方式 { System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill(); } else { Quit(); } } } catch { } } /// <summary> /// 釋放資源 /// </summary> public void Quit() { if (workBook != null) workBook.Close(null, null, null); if (excelApp != null) { excelApp.Workbooks.Close(); excelApp.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 (workSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); workSheet = null; } if (workBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); workBook = null; } if (excelApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); excelApp = null; } GC.Collect(); } /// <summary> /// 接口方法 釋放資源 /// </summary> public void Dispose() { Quit(); } #endregion #region 靜態(tài)方法 /// <summary> /// 殺Excel進(jìn)程 /// </summary> public static void KillAllExcelProcess() { Process[] myProcesses; myProcesses = Process.GetProcessesByName("Excel"); //得不到Excel進(jìn)程ID,，暫時(shí)只能判斷進(jìn)程啟動(dòng)時(shí)間 foreach (Process myProcess in myProcesses) { myProcess.Kill(); } } /// <summary> /// 打開相應(yīng)的excel /// </summary> /// <param name="filepath"></param> public static void OpenExcel(string filepath) { Excel.Application xlsApp = new Excel.Application(); xlsApp.Workbooks.Open(filepath); xlsApp.Visible = true; } #endregion } /// <summary> /// 常用顏色定義,對(duì)就Excel中顏色名 /// </summary> public enum ColorIndex { 無色 = -4142, 自動(dòng) = -4105, 黑色 = 1, 褐色 = 53, 橄欖 = 52, 深綠 = 51, 深青 = 49, 深藍(lán) = 11, 靛藍(lán) = 55, 灰色80 = 56, 深紅 = 9, 橙色 = 46, 深黃 = 12, 綠色 = 10, 青色 = 14, 藍(lán)色 = 5, 藍(lán)灰 = 47, 灰色50 = 16, 紅色 = 3, 淺橙色 = 45, 酸橙色 = 43, 海綠 = 50, 水綠色 = 42, 淺藍(lán) = 41, 紫羅蘭 = 13, 灰色40 = 48, 粉紅 = 7, 金色 = 44, 黃色 = 6, 鮮綠 = 4, 青綠 = 8, 天藍(lán) = 33, 梅紅 = 54, 灰色25 = 15, 玫瑰紅 = 38, 茶色 = 40, 淺黃 = 36, 淺綠 = 35, 淺青綠 = 34, 淡藍(lán) = 37, 淡紫 = 39, 白色 = 2 } /// <summary> /// HTML,，CSV，TEXT,，EXCEL,，XML /// </summary> public enum SaveAsFileFormat { HTML, CSV, TEXT, EXCEL, XML } /// <summary> /// author by :ljun /// Excel工具類，目前僅支持一個(gè)工作薄的操作 /// </summary> public class ExcelHelper : IDisposable { #region 構(gòu)造函數(shù) /// <summary> /// 構(gòu)造函數(shù),，將一個(gè)已有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; excelApp = new Excel.ApplicationClass(); excelApp.Visible = false; excelApp.DisplayAlerts = false; //是否需要顯示提示 excelApp.AlertBeforeOverwriting = false; //是否彈出提示覆蓋 //打開模板文件，得到WorkBook對(duì)象 workBook = excelApp.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, Type.Missing, Type.Missing); //得到WorkSheet對(duì)象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); } /// <summary> /// 構(gòu)造函數(shù),，新建一個(gè)工作簿 /// </summary> public ExcelHelper() { excelApp = new Excel.ApplicationClass(); excelApp.Visible = false; //設(shè)置禁止彈出保存和覆蓋的詢問提示框 excelApp.DisplayAlerts = false; excelApp.AlertBeforeOverwriting = false; //新建一個(gè)WorkBook workBook = excelApp.Workbooks.Add(Type.Missing); //得到WorkSheet對(duì)象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); } #endregion #region 私有變量 private string templetFile = null; private string outputFile = null; private object missing = System.Reflection.Missing.Value; private Excel.Application excelApp; private Excel.Workbook workBook; private Excel.Worksheet workSheet; private Excel.Range range; private Excel.Range range1; private Excel.Range range2; #endregion #region 公共屬性 /// <summary> /// WorkSheet數(shù)量 /// </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 批量寫入Excel內(nèi)容 /// <summary> /// 將二維數(shù)組數(shù)據(jù)寫入Excel文件 /// </summary> /// <param name="arr">二維數(shù)組</param> /// <param name="top">行索引</param> /// <param name="left">列索引</param> public void ArrayToExcel(object[,] arr, int top, int left) { int rowCount = arr.GetLength(0); //二維數(shù)組行數(shù)（一維長(zhǎng)度） int colCount = arr.GetLength(1); //二維數(shù)據(jù)列數(shù)（二維長(zhǎng)度） range = (Excel.Range)workSheet.Cells[top, left]; range = range.get_Resize(rowCount, colCount); range.FormulaArray = arr; } #endregion #region 行操作 /// <summary> /// 插行（在指定行上面插入指定數(shù)量行） /// </summary> /// <param name="rowIndex"></param> /// <param name="count"></param> public void InsertRows(int rowIndex, int count) { try { range = (Excel.Range)workSheet.Rows[rowIndex, this.missing]; for (int i = 0; i < count; i++) { range.Insert(Excel.XlDirection.xlDown, missing); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } } /// <summary> /// 復(fù)制行（在指定行下面復(fù)制指定數(shù)量行） /// </summary> /// <param name="rowIndex"></param> /// <param name="count"></param> public void CopyRows(int rowIndex, int count) { try { 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(false); throw e; } } /// <summary> /// 刪除行 /// </summary> /// <param name="sheetIndex"></param> /// <param name="rowIndex"></param> /// <param name="count"></param> public void DeleteRows(int rowIndex, int count) { try { for (int i = 0; i < count; i++) { range = (Excel.Range)workSheet.Rows[rowIndex, this.missing]; range.Delete(Excel.XlDirection.xlDown); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } } #endregion #region 列操作 /// <summary> /// 插列（在指定列右邊插入指定數(shù)量列） /// </summary> /// <param name="columnIndex"></param> /// <param name="count"></param> public void InsertColumns(int columnIndex, int count) { try { range = (Excel.Range)(workSheet.Columns[columnIndex, this.missing]); //注意：這里和VS的智能提示不一樣,，第一個(gè)參數(shù)是columnindex for (int i = 0; i < count; i++) { range.Insert(Excel.XlDirection.xlDown, missing); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } } /// <summary> /// 刪除列 /// </summary> /// <param name="columnIndex"></param> /// <param name="count"></param> public void DeleteColumns(int columnIndex, int count) { try { for (int i = columnIndex + count-1; i >= columnIndex; i--) { ((Excel.Range)workSheet.Cells[1, i]).EntireColumn.Delete(0); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } } #endregion #region 單元格操作 /// <summary> /// 合并單元格，并賦值,，對(duì)指定WorkSheet操作 /// </summary> /// <param name="sheetIndex">WorkSheet索引</param> /// <param name="beginRowIndex">開始行索引</param> /// <param name="beginColumnIndex">開始列索引</param> /// <param name="endRowIndex">結(jié)束行索引</param> /// <param name="endColumnIndex">結(jié)束列索引</param> /// <param name="text">合并后Range的值</param> public void MergeCells(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text) { range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]); range.ClearContents(); //先把Range內(nèi)容清除,，合并才不會(huì)出錯(cuò) range.MergeCells = true; range.Value2 = text; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; } /// <summary> /// 向單元格寫入數(shù)據(jù)，對(duì)當(dāng)前WorkSheet操作 /// </summary> /// <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(false); throw new Exception("向單元格[" + rowIndex + "," + columnIndex + "]寫數(shù)據(jù)出錯(cuò),！"); } } /// <summary> /// 向單元格寫入數(shù)據(jù),，對(duì)當(dāng)前WorkSheet操作 /// </summary> /// <param name="rowIndex">行索引</param> /// <param name="columnIndex">列索引</param> /// <param name="text">要寫入的文本值</param> public void SetCells(int rowIndex, int columnIndex, string text, string comment) { try { workSheet.Cells[rowIndex, columnIndex] = text; SetCellComment(rowIndex, columnIndex, comment); } catch { this.KillExcelProcess(false); throw new Exception("向單元格[" + rowIndex + "," + columnIndex + "]寫數(shù)據(jù)出錯(cuò)！"); } } /// <summary> /// 向單元格寫入數(shù)據(jù),，對(duì)當(dāng)前WorkSheet操作 /// </summary> /// <param name="rowIndex">行索引</param> /// <param name="columnIndex">列索引</param> /// <param name="text">要寫入的文本值</param> public void SetCellComment(int rowIndex, int columnIndex, string comment) { try { Excel.Range range = workSheet.Cells[rowIndex, columnIndex] as Excel.Range; range.AddComment(comment); } catch { this.KillExcelProcess(false); throw new Exception("向單元格[" + rowIndex + "," + columnIndex + "]寫數(shù)據(jù)出錯(cuò),！"); } } /// <summary> /// 單元格背景色及填充方式 /// </summary> /// <param name="startRow">起始行</param> /// <param name="startColumn">起始列</param> /// <param name="endRow">結(jié)束行</param> /// <param name="endColumn">結(jié)束列</param> /// <param name="color">顏色索引</param> public void SetCellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color) { Excel.Range range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]); range.Interior.ColorIndex = color; } #endregion #region 保存文件 /// <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, missing); } catch (Exception e) { throw e; } finally { this.Quit(); } } /// <summary> /// 將Excel文件另存為指定格式 /// </summary> /// <param name="format">HTML,，CSV,，TEXT，EXCEL,，XML</param> public void SaveAsFile(SaveAsFileFormat format) { if (this.outputFile == null) throw new Exception("沒有指定輸出文件路徑,！"); try { switch (format) { case SaveAsFileFormat.HTML: { workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); break; } case SaveAsFileFormat.CSV: { workBook.SaveAs(outputFile, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); break; } case SaveAsFileFormat.TEXT: { workBook.SaveAs(outputFile, Excel.XlFileFormat.xlUnicodeText, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); break; } case SaveAsFileFormat.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, missing); break; } } } catch (Exception e) { throw e; } finally { this.Quit(); } } #endregion #region 殺進(jìn)程釋放資源 /// <summary> /// 結(jié)束Excel進(jìn)程 /// </summary> public void KillExcelProcess(bool bAll) { if (bAll) { KillAllExcelProcess(); } else { KillSpecialExcel(); } } [DllImport("user32.dll", SetLastError = true)] static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId); /// <summary> /// 殺特殊進(jìn)程的Excel /// </summary> public void KillSpecialExcel() { try { if (excelApp != null) { int lpdwProcessId; GetWindowThreadProcessId((IntPtr)excelApp.Hwnd, out lpdwProcessId); if (lpdwProcessId > 0) //c-s方式 { System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill(); } else { Quit(); } } } catch { } } /// <summary> /// 釋放資源 /// </summary> public void Quit() { if (workBook != null) workBook.Close(null, null, null); if (excelApp != null) { excelApp.Workbooks.Close(); excelApp.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 (workSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); workSheet = null; } if (workBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); workBook = null; } if (excelApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); excelApp = null; } GC.Collect(); } /// <summary> /// 接口方法 釋放資源 /// </summary> public void Dispose() { Quit(); } #endregion #region 靜態(tài)方法 /// <summary> /// 殺Excel進(jìn)程 /// </summary> public static void KillAllExcelProcess() { Process[] myProcesses; myProcesses = Process.GetProcessesByName("Excel"); //得不到Excel進(jìn)程ID，暫時(shí)只能判斷進(jìn)程啟動(dòng)時(shí)間 foreach (Process myProcess in myProcesses) { myProcess.Kill(); } } /// <summary> /// 打開相應(yīng)的excel /// </summary> /// <param name="filepath"></param> public static void OpenExcel(string filepath) { Excel.Application xlsApp = new Excel.Application(); xlsApp.Workbooks.Open(filepath); xlsApp.Visible = true; } #endregion } /// <summary> /// 常用顏色定義,對(duì)就Excel中顏色名 /// </summary> public enum ColorIndex { 無色 = -4142, 自動(dòng) = -4105, 黑色 = 1, 褐色 = 53, 橄欖 = 52, 深綠 = 51, 深青 = 49, 深藍(lán) = 11, 靛藍(lán) = 55, 灰色80 = 56, 深紅 = 9, 橙色 = 46, 深黃 = 12, 綠色 = 10, 青色 = 14, 藍(lán)色 = 5, 藍(lán)灰 = 47, 灰色50 = 16, 紅色 = 3, 淺橙色 = 45, 酸橙色 = 43, 海綠 = 50, 水綠色 = 42, 淺藍(lán) = 41, 紫羅蘭 = 13, 灰色40 = 48, 粉紅 = 7, 金色 = 44, 黃色 = 6, 鮮綠 = 4, 青綠 = 8, 天藍(lán) = 33, 梅紅 = 54, 灰色25 = 15, 玫瑰紅 = 38, 茶色 = 40, 淺黃 = 36, 淺綠 = 35, 淺青綠 = 34, 淡藍(lán) = 37, 淡紫 = 39, 白色 = 2 } /// <summary> /// HTML，CSV,，TEXT,，EXCEL，XML /// </summary> public enum SaveAsFileFormat { HTML, CSV, TEXT, EXCEL, XML } |
|
|
