Import Excel to Oracle for verification

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 {// if the client computer runs an Excel in English and the current user's region is set to a language other than English, then Exce L will try to find the Language Pack for the configured language. If the required language pack is not found, an error is reported. 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) {stri Ng 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 (catch t Ion 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. excelInst Ance. 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 Get ColumnNameByColumnNumber (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. co Unt);} 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 (lon G 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, c OlumnNumber]; 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. mess Age, 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 }}