NPOI read Excel

Project Environment: Webform framework4.0

Dll version: NPOI2.0 dotnet2.0

I want to create an excel import function in the past two days. I thought I had used NPOI before. I wrote a DEMO today and it was quite smooth. After all, I used it before, I still want to record it and copy it later.

Read the excel Data and splice it into the able. In order to use SqlBulkCopy to copy the data to the database at a time

1 IWorkbook workbook = null; 2 string fileExt = Path. getExtension (path); 3 try 4 {5 using (var file = new FileStream (path, FileMode. open, FileAccess. read) 6 {7 if (fileExt = ". xls ") 8 workbook = new HSSFWorkbook (file); 9 else if (fileExt = ". xlsx ") 10 workbook = new XSSFWorkbook (file); 11 else12 {13 14} 15} 16} 17 catch (Exception ex) 18 {}View Code 1 // get sheet page 2 var sheet = workbook. getSheetAt (0); 3 // get the total number of items 4 int RowCount = sheet. lastRowNum; 5 // obtain the first data of the sheet page. 6 IRow firstRow = sheet. getRow (0); 7 // get the total number of Columns 8 int CellCount = firstRow. lastCellNum; 9 10 DataTable dt = new DataTable (); 11 for (int j = 0; j <CellCount; j ++) 12 {13 string value = firstRow. getCell (j ). stringCellValue; 14 DataColumn dc = new DataColumn (value, typeof (String); 15 Dt. columns. add (dc); 16} 17 18 for (int I = 1; I <= RowCount; I ++) 19 {20 IRow row = sheet. getRow (I); 21 DataRow dr = dt. newRow (); 22 for (int j = 0; j <CellCount; j ++) 23 {24 object obj = row. getCell (j); 25 if (obj! = Null) 26 dr [j] = obj. ToString (); 27 else28 dr [j] = ""; 29} 30 dt. Rows. Add (dr); 31}View Code

Use SqlBulkCopy

SqlBulkCopy sqlbulkcopy = new SqlBulkCopy (connectionString, response. UseInternalTransaction); sqlbulkcopy. DestinationTableName = "Table_1"; // sqlbulkcopy. WriteToServer (dataset. Tables [0]) in the database;