using System;
using System.Diagnostics;using System.Collections;using System.Data;using System.Web;using System.Runtime.InteropServices;using System.Reflection;using Excel = Microsoft.Office.Interop.Excel;using System.Collections.Generic;using System.Drawing;namespace VQP.Common{ public class ExcelWrapper { private string _ReportTemplate_Excel_FileName = null; private Excel.Application _Excel = null; private Excel.Workbooks _WorkBooks = null; private Excel.Workbook _WorkBook = null; private Excel.Sheets _WorkSheets = null; private Excel.Worksheet _WorkSheet = null; private string _password = ""; private string _filenamepath = ""; private int _WorkSheetCount = 0; public int WorkSheetCount { get { return _WorkSheetCount; } set { _WorkSheetCount = value; } } private string _excelversion; public string ExcelVersion { get { return _excelversion; } set { _excelversion = value; } } private List<int> _vqpSheetCountList = null; public List<int> VqpSheetCountList { get { return _vqpSheetCountList; } set { _vqpSheetCountList = value; } } private Hashtable _hsSheetName = new Hashtable(); public Hashtable HsSheetName { get { return _hsSheetName; } set { _hsSheetName = value; } } public ExcelWrapper() { } /// <summary> /// open excel /// </summary> /// <param name="strFileName"></param> public ExcelWrapper(string strFileName, string strPassword) { _ReportTemplate_Excel_FileName = strFileName; _Excel = new Excel.Application(); _Excel.Visible = false; _Excel.DisplayAlerts = false; _WorkBooks = _Excel.Workbooks; this._password = strPassword.Trim(); //_WorkBook = _WorkBooks.Open(strFileName, 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); if (this._password.Trim().Length == 0) { _WorkBook = _WorkBooks.Open(strFileName, 0, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } else { _WorkBook = _WorkBooks.Open(strFileName, 0, false, Missing.Value, this._password, this._password, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //_WorkBook.WritePassword = } _WorkSheetCount = _Excel.Sheets.Count; _vqpSheetCountList = ValidExcelSheetName(); this._filenamepath = strFileName; _Excel.Visible = false; this._excelversion = _Excel.Version; } #region Read excel function /// <summary> /// /// </summary> /// <returns></returns> private List<int> ValidExcelSheetName() { List<int> sheetList = new List<int>(); for (int i = 1; i <= _WorkSheetCount; i++) { string strSheetName = ((Excel.Worksheet)_WorkBook.Sheets[i]).Name; if (strSheetName.ToUpper().StartsWith("Q-")) { sheetList.Add(i); if (!_hsSheetName.Contains(i)) { this._hsSheetName.Add(i, strSheetName); } } } return sheetList; } private string SetNullToEmpty(Object mValue) { if (null == mValue || string.Empty.Equals(mValue)) { return string.Empty; } else { return mValue.ToString(); } } /// <summary> /// /// </summary> /// <param name="sheetIndex"></param> /// <param name="rowIndex"></param> /// <param name="columnIndex"></param> /// <returns></returns> public string GetExcelCellValue(int sheetIndex, int rowIndex, int columnIndex) { string strResult = string.Empty; if (_WorkSheetCount >= sheetIndex) { Excel.Worksheet sheet = (Excel.Worksheet)_WorkBook.Sheets[sheetIndex]; //strResult = SetNullToEmpty(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, columnIndex]).Text); strResult = SetNullToEmpty(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, columnIndex]).Value2); } return strResult; } public string GetExcelCellText(int sheetIndex, int rowIndex, int columnIndex) { string strResult = string.Empty; if (_WorkSheetCount >= sheetIndex) { Excel.Worksheet sheet = (Excel.Worksheet)_WorkBook.Sheets[sheetIndex]; strResult = SetNullToEmpty(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, columnIndex]).Text); } return strResult; } #endregion #region Write excel function public void SetWorkSheetName(int sheetIndex, string strWorkSheetName) { _WorkSheets = _WorkBook.Sheets; _WorkSheet = (Excel.Worksheet)_WorkSheets[sheetIndex]; _WorkSheet.Name = strWorkSheetName; Release(_WorkSheet); Release(_WorkSheets); } public void CopyWorkSheet(int sheetIndex, string sheetName) { _WorkSheets = _WorkBook.Sheets; _WorkSheet = (Excel.Worksheet)_WorkSheets[sheetIndex]; _WorkSheet.Copy(Missing.Value, _WorkSheets[_WorkSheets.Count]); Release(_WorkSheet); _WorkSheet = (Excel.Worksheet)_WorkSheets[_WorkSheets.Count]; _WorkSheet.Name = sheetName; Release(_WorkSheet); Release(_WorkSheets); } public void HideSheet(int sheetIndex) { _WorkSheets = _WorkBook.Sheets; _WorkSheet = (Excel.Worksheet)_WorkSheets[sheetIndex]; _WorkSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden; Release(_WorkSheet); Release(_WorkSheets); } public void WriteCell(int sheetIndex, int row, int col, string strCellValue) { try { if (strCellValue == null) { strCellValue = ""; } strCellValue = strCellValue.Trim(); strCellValue = strCellValue.Replace("\n\t", String.Empty); strCellValue = strCellValue.Replace("\n", String.Empty); strCellValue = strCellValue.Replace("'", "\'"); _WorkSheets = _WorkBook.Sheets; _WorkSheet = (Excel.Worksheet)_WorkSheets[sheetIndex]; _WorkSheet.Unprotect(this._password); _WorkSheet.Cells[row, col] = strCellValue.ToString(); //(_WorkSheet.Cells[row, col] as Excel.Range).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow); //(_WorkSheet.Cells[row, col] as Excel.Range).Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb(); Excel.Range excelRange = _WorkSheet.Cells[row, col] as Excel.Range; if (excelRange != null) { excelRange.Borders.LineStyle = 1; excelRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); string commentTxt = "Value has been corrected by VQP system."; if (excelRange.Comment != null) { excelRange.Comment.Delete(); } excelRange.AddComment(commentTxt); excelRange.Comment.Visible = true; } } catch(Exception ex) { Console.Write(ex.ToString()); } //Release(_WorkSheet); //Release(_WorkSheets); } public void WriteComment(string strCellValue, int RowStart, int ColStart, int sheetIndex) { WriteCell(sheetIndex, RowStart, ColStart, strCellValue); } public void DeleteColumn(int col, int sheetIndex) { _WorkSheets = _WorkBook.Sheets; _WorkSheet = (Excel.Worksheet)_WorkSheets[sheetIndex]; Excel.Range oColumn = (Excel.Range)_WorkSheet.Cells[1, col]; oColumn.EntireColumn.Delete(Missing.Value); Release(oColumn); Release(_WorkSheet); Release(_WorkSheets); } public void DeleteRow(int row, int sheetIndex) { _WorkSheets = _WorkBook.Sheets; _WorkSheet = (Excel.Worksheet)_WorkSheets[sheetIndex]; Excel.Range oRow = (Excel.Range)_WorkSheet.Cells[row, 1]; oRow.EntireRow.Delete(Missing.Value); Release(oRow); Release(_WorkSheet); Release(_WorkSheets); } public void InsertRow(int row, int sheetIndex) { _WorkSheets = _WorkBook.Sheets; _WorkSheet = (Excel.Worksheet)_WorkSheets[sheetIndex]; Excel.Range oRow = (Excel.Range)_WorkSheet.Cells[row, 1]; oRow.EntireRow.Copy(oRow.EntireRow); oRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); Release(oRow); Release(_WorkSheet); Release(_WorkSheets); } public void InsertRows(int sheetIndex, int row, int insertingRowCount) { _WorkSheets = _WorkBook.Sheets; _WorkSheet = (Excel.Worksheet)_WorkSheets[sheetIndex]; for (int i = 1; i <= insertingRowCount; i++) { Excel.Range oRow = (Excel.Range)_WorkSheet.Rows[row, 1]; oRow.EntireRow.Copy(oRow.EntireRow); oRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); Release(oRow); } Release(_WorkSheet); Release(_WorkSheets); } public void SaveAs(string strExcelName) { //_WorkBook.SaveAs(strExcelName, Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //if (System.IO.Path.GetExtension(strExcelName).ToLower() == ".xlsx") // exclVersion = Excel.XlFileFormat.xlOpenXMLWorkbook;//Excel 2007版本 Excel.XlFileFormat exclVersion = Excel.XlFileFormat.xlWorkbookNormal;//Excel 2003版本 _WorkBook.SaveAs(strExcelName, exclVersion, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } #endregion #region Excel common function public bool IsExistSheetName(string sheetName) { object sheet = null; try { sheet = _WorkBook.Sheets.get_Item(sheetName); } catch { sheet = null; } return (sheet != null); } public int GetSheetIndex(string sheetName) { if(IsExistSheetName(sheetName)) { foreach (Excel.Worksheet sheet in _WorkBook.Sheets) { if (sheet.Name == sheetName) { return sheet.Index; } } } return -1; } #endregion /// <summary> /// close excel /// </summary> public void Close() { _WorkBook.Close(false, Missing.Value, Missing.Value); _WorkBooks.Close(); _Excel.Quit(); Release(_WorkSheet); Release(_WorkSheets); Release(_WorkBook); Release(_WorkBooks); Release(_Excel); KillUselessExcelProcess(); } /// <summary> /// release excel /// </summary> /// <param name="obj"></param> private void Release(Object obj) { if (obj == null) { return; } try { int nResult = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); if (nResult > 0) { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); } } catch { } finally { obj = null; } } /// <summary> /// kill excel process /// </summary> private void KillUselessExcelProcess() { GC.Collect(); foreach (System.Diagnostics.Process p in System.Diagnostics.Process.GetProcesses()) { try { if (p.ProcessName.ToUpper().StartsWith("EXCEL")) { //if (p.StartTime.AddSeconds(15) < TimeParser.Now) //{ // p.Kill(); //} p.Kill(); } } catch (Exception ex) { } } } }}