NPOI讀取Excel

2019-11-14 16:39:00

字體：大中小

來源：轉載

供稿：網友

項目環境：Webform framework4.0

dll版本：NPOI2.0 dotnet2.0版本

這兩天要做個Excel導入的功能，想到以前用過NPOI，感覺很給力，今天寫了個DEMO，寫的時候還算順利，畢竟以前用過，還是想記錄下來，留著以后直接復制

把excel數據讀取并拼接到DataTable中，為了使用SqlBulkCopy一次性拷貝到數據庫中

 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 //獲取sheet頁 2             var sheet = workbook.GetSheetAt(0); 3             //獲取總條數 4             int RowCount = sheet.LastRowNum; 5             //獲取sheet頁的第一條數據 6             IRow firstRow = sheet.GetRow(0); 7             //獲取總列數 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

最后使用SqlBulkCopy

SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);sqlbulkcopy.DestinationTableName = "Table_1";//數據庫中的表名sqlbulkcopy.WriteToServer(dataset.Tables[0]);