項目環境：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 { }
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 }
最后使用SqlBulkCopy
SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);sqlbulkcopy.DestinationTableName = "Table_1";//數據庫中的表名sqlbulkcopy.WriteToServer(dataset.Tables[0]);
新聞熱點
疑難解答
