Excel匯入Oracle驗證類

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.IO;using System.Drawing;using System.Runtime.InteropServices;using Excel = Microsoft.Office.Interop.Excel;using System.Globalization;using System.Threading;using Ziye.Common;namespace Ziye.BusinessLogic.Implementation{    public class ExcelOperation    {        // 如果用戶端電腦啟動並執行是英語版本的 Excel,並且目前使用者的地區設定配置為英語之外的某個語言，則 Excel 將嘗試尋找針對所配置語言的語言套件。如果沒有找到所需語言套件，則會報告錯誤。         CultureInfo oldCultureInfo = Thread.CurrentThread.CurrentCulture;        ExcelInstance excelInstance;        public ExcelOperation(ExcelInstance excelInstance)        {            this.excelInstance = excelInstance;            Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");        }        #region Public Excel Operation Methods        public void OpenExcelSheet(string fileDirectory, string fileName, long sheetNumber)        {            string filePath = Path.Combine(fileDirectory, fileName);            if (File.Exists(filePath))            {                try                {                    // Open file and get workbook instance                    this.excelInstance.ExcelWorkbook = this.excelInstance.ExcelApp.Workbooks._Open(filePath,                                                            Type.Missing, Type.Missing, Type.Missing, Type.Missing,                                                            Type.Missing, Type.Missing, Type.Missing, Type.Missing,                                                            Type.Missing, Type.Missing, Type.Missing, Type.Missing);                }                catch (Exception ex)                {                    Ziye.Logging.Log4NetHelper.GetLogger().Error(ex.Message, ex);                    throw new Exception("Can't open " + fileName + " on the server! " + ex.Message);                }            }            else            {                throw new Exception("Open excel error: can't find +" + fileName + " !");            }            if (this.excelInstance.ExcelWorkbook.Worksheets.Count != 0)            {                // choose which sheet to process                this.excelInstance.ExcelWorksheet = this.excelInstance.ExcelWorkbook.Sheets[sheetNumber] as Excel.Worksheet;            }            else            {                throw new Exception(fileName + " does not contain any worksheet!");            }        }        public void InitialColor(Color foreColor, Color backColor)        {            Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.UsedRange;            range.Font.Color = System.Drawing.ColorTranslator.ToOle(foreColor);            range.Interior.Color = System.Drawing.ColorTranslator.ToOle(backColor);        }        public string ReadExcelCell(long rowNumber, long columnNumber)        {            Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];            object cellValue = range.get_Value(Type.Missing);            DateTime? dateTime = cellValue as DateTime?;            if (dateTime.HasValue)            {                  IFormatProvider culture = new CultureInfo("zh-CN", true);                cellValue = dateTime.Value.ToString(culture);            }            return cellValue == null ? string.Empty : cellValue.ToString().WTrim();        }        public string ReadExcelCell(long rowNumber, string columnName)        {            long columnNumber = this.GetColumnNumberByColumnName(columnName);            return ReadExcelCell(rowNumber, columnNumber);        }        public void AddComment(long rowNumber, long columnNumber, string comment)        {            try            {                if (!string.IsNullOrEmpty(comment))                {                    Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];                    range.AddComment(comment);                }            }            catch(Exception ex)            {                Ziye.Logging.Log4NetHelper.GetLogger().Error(ex.Message, ex);                throw new Exception("Error eccured when add Comment" +ex.Message);            }        }        public void DelComment()        {            throw new NotImplementedException();        }        public string GetColumnNameByColumnNumber(long columnNumber)        {            Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[1, columnNumber];            string cellValue = range.Text.ToString();            return cellValue;        }        public long GetColumnNumberByColumnName(string columnName)        {            return this.ReadOneRow(1).IndexOf(columnName)+1;        }        public long UsedRowNumber()        {            return (this.excelInstance.ExcelWorksheet.UsedRange.Rows.Count);        }        public long UsedColumnNumber()        {            return (this.excelInstance.ExcelWorksheet.UsedRange.Columns.Count);        }        public IList<string> ReadOneRow(long rowNumber)        {            IList<string> retList = new List<string>();            for (long columnNumber = 1; columnNumber <= this.UsedColumnNumber(); columnNumber++)            {                retList.Add(this.ReadExcelCell(rowNumber, columnNumber));            }            return retList;        }        public void WriteExcelCell(long rowNumber, long columnNumber, string cellValue)        {            throw new NotImplementedException();        }        public void SetExcelCellFontColor(long rowNumber, long columnNumber, Color color)        {            Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];            range.Font.Color = System.Drawing.ColorTranslator.ToOle(color);        }        public void SetExcelCellBackgroundColor(long rowNumber, long columnNumber, Color color)        {            Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];            range.Interior.Color = System.Drawing.ColorTranslator.ToOle(color);        }        public void SetExcelCellMark(long rowNumber, long columnNumber, Color foreColor, Color backColor, string comment)        {            Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];            range.Font.Color = System.Drawing.ColorTranslator.ToOle(foreColor);            range.Interior.Color = System.Drawing.ColorTranslator.ToOle(backColor);            if (!string.IsNullOrEmpty(comment))            {                range.AddComment(comment);            }        }        public void SetExcelRowBackgroundColor(long rowNumber, long startColumnNumber, long columnCount, Color color)        {            for (long i = startColumnNumber; i <= columnCount; i++)            {                Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, i];                range.Interior.Color = System.Drawing.ColorTranslator.ToOle(color);            }        }        public void SaveExcel(string targetDirectory, string fileName)        {            bool oldDisplayAlertState = this.excelInstance.ExcelApp.DisplayAlerts;            string filePath = Path.Combine(targetDirectory, fileName);            DirectoryInfo directoryInfo = new DirectoryInfo(targetDirectory);            try            {                if (directoryInfo.Exists == false)                {                    directoryInfo.Create();                }            }            catch (Exception ex)            {                Ziye.Logging.Log4NetHelper.GetLogger().Error(ex.Message, ex);                throw new Exception("Can't create the directory which used to save the excel! " + ex.Message);            }            try            {                this.excelInstance.ExcelApp.DisplayAlerts = false;                this.excelInstance.ExcelWorkbook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, true);                this.excelInstance.ExcelApp.DisplayAlerts = oldDisplayAlertState;            }            catch (Exception ex)            {                Ziye.Logging.Log4NetHelper.GetLogger().Error(ex.Message, ex);                throw new Exception("Can't save the excel! " + ex.Message);            }        }        public void CloseExcelInstance()        {            Thread.CurrentThread.CurrentCulture = oldCultureInfo;            if (this.excelInstance.ExcelWorkbook != null)            {                this.excelInstance.ExcelWorkbook.Close(false, Type.Missing, Type.Missing);            }            if (this.excelInstance.ExcelApp != null)            {                this.excelInstance.ExcelApp.DisplayAlerts = false;                this.excelInstance.ExcelApp.Quit();                           }            if (this.excelInstance.ExcelWorksheet != null)            {                if (Marshal.ReleaseComObject(this.excelInstance.ExcelWorksheet) >= 0)                {                    this.excelInstance.ExcelWorksheet = null;                }            }            if (this.excelInstance.ExcelWorkbook != null)            {                if (Marshal.ReleaseComObject(this.excelInstance.ExcelWorkbook) >= 0)                {                    this.excelInstance.ExcelWorkbook = null;                }            }            if (this.excelInstance.ExcelApp != null)            {                              if (Marshal.ReleaseComObject(this.excelInstance.ExcelApp) >= 0)                {                    this.excelInstance.ExcelApp = null;                }            }                     GC.GetTotalMemory(false);            GC.Collect();            GC.WaitForPendingFinalizers();            GC.Collect();            GC.GetTotalMemory(true);        }        #endregion    }}