GetExcelData. cs

Source: Internet
Author: User

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 ;}}
  
 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.