GetExcelData. cs

Using System; using System. collections. generic; // Listusing System. data; using System. data. oleDb; using System. diagnostics; // Processusing System. IO; using System. reflection; // Missing. valueusing System. text. regularExpressions; // Regexusing System. web; using System. web. UI; using System. web. UI. webControls; using ExcelCOM = Microsoft. office. interop. excel; // enable the Excel component /*/------------------------------------ ------------------------------------------ // Abstract description of GetExcelData // optional/*/public class GetExcelData: System. web. UI. page {// constructor public GetExcelData () {killExcel (); // end all Excel processes} // end the Process public bool killExcel () {try {Process [] myProcesses; myProcesses = Process. getProcessesByName ("Excel"); // end all Excel Process foreach (Process myPr Ocess in myProcesses) {myProcess. kill () ;}return true ;}catch {return false ;}// convert the index character to the number public static int toIndex (string columnName) {int index = 0; // format check if (! Regex. isMatch (columnName, @ "^ [A-Za-z] + $") {return-1;} char [] chars = columnName. toUpper (). toCharArray (); for (int I = 0; I <chars. length; I ++) {index + = (int) chars [I]-(int) 'A' + 1) * (int) Math. pow (26, chars. length-I-1);} return index-1;} // convert the index number to the public static string toColName (int index) {// format check if (index <0) return null; List
 
  
Chars = new List
  
   
(); Do {if (chars. count> 0) index --; chars. insert (0, (char) (index % 26 + (int) 'A ')). toString (); index = (int) (index-index % 26)/26);} while (index> 0); return String. join (string. empty, chars. toArray ();} // export from Excel to DataSetpublic DataSet getDataSet (string f_FilePath, string f_SheetName) {DataSet ds = new DataSet (); string strConnect = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ f_FilePath + "; Extended Properties = 'excel 8.0; HDR = Yes; IMEX = 1'; "; try {OleDbConnection conn = new OleDbConnection (strConnect ); oleDbDataAdapter oada = new OleDbDataAdapter ("select * from [" + f_SheetName + "$]", strConnect); conn. open (); oada. fill (ds, f_SheetName + "$"); conn. close ();} catch {} return ds;} // export data from Excel to ableablepublic System. data. dataTable getDataTable (string f_FilePath, string f_SheetName) {System. data. dataT Able dt = new System. data. dataTable (); string strConnect = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ f_FilePath +"; Extended Properties = 'excel 8.0; HDR = Yes; IMEX = 1'; "; try {OleDbConnection conn = new OleDbConnection (strConnect ); oleDbDataAdapter oada = new OleDbDataAdapter ("select * from [" + f_SheetName + "$]", strConnect); conn. open (); oada. fill (dt); conn. close ();} catch {} return dt;} // obtain the table name pub with the specified number Lic string getSheetName (string f_FilePath, int f_SheetNum) {System. data. dataTable dt = new System. data. dataTable (); string sheetName = null; string strConnect = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ f_FilePath +"; Extended Properties = 'excel 8.0; HDR = Yes; IMEX = 1'; "; try {OleDbConnection conn = new OleDbConnection (strConnect ); conn. open (); dt = conn. getOleDbSchemaTable (System. data. oleDb. oleDbSchem AGuid. tables, new object [] {null, "TABLE"}); conn. close (); sheetName = dt. rows [f_SheetNum] [2]. toString ();} catch {} return sheetName. substring (0, sheetName. length-1);} // number of statistical tables public int countSheet (string f_FilePath) {System. data. dataTable dt = new System. data. dataTable (); string strConnect = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ f_FilePath +"; Extended Properties = 'excel 8.0; HDR = Ye S; IMEX = 1'; "; try {OleDbConnection conn = new OleDbConnection (strConnect); conn. open (); dt = conn. getOleDbSchemaTable (System. data. oleDb. oleDbSchemaGuid. tables, new object [] {null, "TABLE"}); conn. close ();} catch {} return dt. rows. count;} // open Excelpublic ExcelCOM. worksheet openExcel (ExcelCOM. application ExcelApp, string f_FilePath, string f_SheetName) {ExcelApp. visible = false; // execute ExcelApp in the background. dis PlayAlerts = false; // The prompt box ExcelApp is displayed. alertBeforeOverwriting = false; // The try {object oMissing = Missing is not prompted during overwriting. value; ExcelCOM. workbook myBook = ExcelApp. workbooks. open (f_FilePath, oMissing, oMissing); ExcelCOM. worksheet mySheet = myBook. sheets [f_SheetName] as ExcelCOM. worksheet; return MySheet;} catch {closeExcel (ExcelApp, false); return null ;}/// close Excelpublic bool closeExcel (ExcelCOM. application ExcelApp, bool isSave) {if (isSave) ExcelApp. save (); ExcelApp. quit (); System. GC. collect (System. GC. getGeneration (ExcelApp); ExcelApp = null; if (! KillExcel () {return false;} return true;} // read the cell. The row and column start from 1 public string getCell (ExcelCOM. worksheet f_Worksheet, int row, int col) {if (row <= 0) | (col <= 0) {return null;} ExcelCOM. range rangeCell = f_Worksheet.Cells [row, col] as ExcelCOM. range; return rangeCell. text. toString ();} // write to the cell. The row and column start from 1 public bool setCell (ExcelCOM. worksheet f_Worksheet, int row, int col, string val) {if (row <= 0) | (col <= 0) {return false;} E XcelCOM. Range rangeCell = f_Worksheet.Cells [row, col] as ExcelCOM. Range; // judge the merged cell int rowStart = (bool) rangeCell. MergeCells? RangeCell. MergeArea. Row: row; int colStart = (bool) rangeCell. MergeCells? RangeCell. mergeArea. column: col; rangeCell = f_Worksheet.Cells [rowStart, colStart] as ExcelCOM. range; rangeCell. value = val; return true;} // determines whether to merge cells public bool isMergeCell (ExcelCOM. worksheet f_Worksheet, int row, int col) {if (row <= 0) | (col <= 0) {return false;} ExcelCOM. range rangeCell = (ExcelCOM. range) f_Worksheet.Cells [row, col]; return (bool) rangeCell. mergeCells;} // obtain the number of rows in the merged cell. public int getMergeRow (ExcelCOM. worksheet f_Worksheet, int row, int col) {if (row <= 0) | (col <= 0) {return 0;} ExcelCOM. range rangeCell = (ExcelCOM. range) f_Worksheet.Cells [row, col]; return rangeCell. mergeArea. rows. count;} // obtain the cross-column public int getMergeCol (ExcelCOM. worksheet f_Worksheet, int row, int col) {if (row <= 0) | (col <= 0) {return 0;} ExcelCOM. range rangeCell = (ExcelCOM. range) f_Worksheet.Cells [row, col]; return rangeCell. mergeArea. columns. count ;}}