项目环境：Webform framework4.0
dll版本：NPOI2.0 dotnet2.0版本
这两天要做个Excel导入的功能，想到以前用过NPOI，感觉很给力，今天写了个DEMO，写的时候还算顺利，毕竟以前用过，还是想记录下来，留着以后直接复制
把excel数据读取并拼接到DataTable中，为了使用SqlBulkCopy一次性拷贝到数据库中
1IWorkbook workbook =null;2stringfileExt =Path.GetExtension(path);3try4{5using(varfile =newFileStream(path, FileMode.Open, Fileaccess.Read))6{7if(fileExt ==".xls")8workbook =newHSSFWorkbook(file);9elseif(fileExt ==".xlsx")10workbook =newXSSFWorkbook(file);11else12{1314}15}16}17catch(Exception ex)18{ }
View Code
1//获取sheet页2varsheet = workbook.GetSheetAt(0);3//获取总条数4intRowCount =sheet.LastRowNum;5//获取sheet页的第一条数据6IRow firstRow = sheet.GetRow(0);7//获取总列数8intCellCount =firstRow.LastCellNum;910DataTable dt =newDataTable();11for(intj =0; j < CellCount; j++)12{13stringvalue =firstRow.GetCell(j).StringCellValue;14DataColumn dc =newDataColumn(value,typeof(String));15dt.Columns.Add(dc);16}1718for(inti =1; i <= RowCount; i++)19{20IRow row =sheet.GetRow(i);21DataRow dr =dt.NewRow();22for(intj =0; j < CellCount; j++)23{24objectobj =row.GetCell(j);25if(obj !=null)26dr[j] =obj.ToString();27else28dr[j] ="";29}30dt.Rows.Add(dr);31}
View Code
最后使用SqlBulkCopy
SqlBulkCopy sqlbulkcopy =newSqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);
sqlbulkcopy.DestinationTableName="Table_1";//数据库中的表名sqlbulkcopy.WriteToServer(dataset.Tables[0]);