C # Summary of various data import methods for Excel files,

C # Summary of various data import methods for Excel files,

Before the import, you must save the uploaded files to the server. Therefore, do not write the code repeatedly. paste the uploaded files and save them to the Code in the specified path on the server.

Protected void btnImport_Click (object sender, EventArgs e) {Random random = new Random (); ImportClass Import = new ImportClass (); // save the file's virtual path string Path = "Import/"; // obtain the selected file name string fileName = FileUpload1.FileName; // obtain the file extension name string fileExt = path. getExtension (fileName); // generate the new file name string newName = DateTime. now. toString ("yyyyMMddHHmmssfff") + random. next (0, 9999 ). toString (); // obtain the physical path strin of the specified VM path G fullPath = HttpContext. current. server. mapPath (path); // string savePath = fullPath + newName + fileExt; // save the file to FileUpload1.SaveAs (savePath) on the server ); try {// get imported data DataSet ds = Import. importExcel (savePath); if (ds! = Null & ds. Tables. Count> 0) {// The method for inserting data into the database can be written here} catch (Exception ex) {throw ;}}

View Code

First: OleDB

Public DataSet ImportExcel (string filePath) {DataSet ds = null; OleDbConnection conn; string strConn = string. empty; string sheetName = string. empty; try {// connection string strConn = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ filePath +"; Extended Properties = 'excel 8.0; HDR = YES; IMEX = 1; '"; conn = new OleDbConnection (strConn); conn. open ();} catch {// connection string strConn = "Provider = Microsoft. ACE. OLEDB.12.0; Data Source = "+ filePath +"; Extended Properties = 'excel 12.0; HDR = Yes; IMEX = 1; '"; conn = new OleDbConnection (strConn); conn. open ();} // obtain all the sheet table DataTable dtSheetName = conn. getOleDbSchemaTable (OleDbSchemaGuid. tables, new object [] {null, "Table"}); ds = new DataSet (); for (int I = 0; I <dtSheetName. rows. count; I ++) {DataTable dt = new DataTable (); dt. tableName = "table" + I. toString (); // obtain the table name sheetName = dtSheetName. rows [I] ["TABLE_NAME"]. toString (); OleDbDataAdapter oleda = new OleDbDataAdapter ("select * from [" + sheetName + "]", conn); oleda. fill (dt); ds. tables. add (dt) ;}return ds ;}

View Code

In addition to the inflexible reading process, the disadvantage of this reading method is that when the Excel data volume is large. Memory usage is very high. If the memory is insufficient, an exception of memory overflow will be thrown, but it is applicable in general.

 

Type 2: Microsoft. Office. Interop. Excel. dll

Public DataSet ImportExcel (string filePath) {DataSet ds = null; DataTable dt = null; Microsoft. office. interop. excel. application excel = new Microsoft. office. interop. excel. application (); Microsoft. office. interop. excel. workbook workbook = null; Microsoft. office. interop. excel. worksheet worksheet = null; Microsoft. office. interop. excel. sheets sheets = null; Microsoft. office. interop. excel. range range = null; object missing = System. reflection. missing. value; try {if (excel = null) {return null;} // open the Excel file workbook = excel. workbooks. open (filePath, missing, missing ); // obtain all sheet tables. sheets = workbook. worksheets; ds = new DataSet (); for (int I = 1; I <= sheets. count; I ++) {// obtain the first table worksheet = (Microsoft. office. interop. excel. worksheet) sheets. get_Item (I); int rowCount = worksheet. usedRange. rows. count; int colCount = worksheet. usedRange. columns. count; int rowIndex = 1; // starting Behavior 1 int colIndex = 1; // starting column 1 DataColumn dc; dt = new DataTable (); dt. tableName = "table" + I. toString (); // read the column name for (int j = 0; j <colCount; j ++) {range = worksheet. cells [rowIndex, colIndex + j]; dc = new DataColumn (); dc. dataType = Type. getType ("System. string "); dc. columnName = range. text. toString (). trim (); // Add the column dt. columns. add (dc);} // read row data for (int k = 1; k <rowCount; k ++) {DataRow dr = dt. newRow (); for (int l = 0; l <colCount; l ++) {range = worksheet. cells [rowIndex + k, colIndex + l]; // use range. value. toString (); or range. value2.ToString (); or range. text. toString (); can obtain the value of the cell dr [l] = range. text. toString ();} dt. rows. add (dr. itemArray);} ds. tables. add (dt) ;}} catch (Exception ex) {throw;} return ds ;}

View Code

In this method, an office Excel file must be installed and a cell is read. Therefore, the performance is poor.

 

Not complete...