.NET Framework导入Excel表到数据库
一、下载GET包
1、NPOI.dll 2、NPOI.OOXML.dll 3、NPOI.OpenXml4Net.dll
二、引用命名空间
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel;
三、前台上传文件按钮
<a class="auto-Excel" href="http://localhost:27262/Content/Templet/PersonalInformation.xlsx" download="个人信息模板.xlsx">导出Excel模板</a> <asp:Button ID="btnExcel" runat="server" class="btn" Text="导入Excel" OnClick="btnExcel_Click"/> <asp:FileUpload ID="Excelfile" runat="server"/>
四、后台操作导入Excel表格存放本地（导入Excel表格保存到本地中，再从本地读取表格转化为Datatable数据导入数据库中）
#region 导入Excel protected void btnExcel_Click(object sender, EventArgs e) { if (Excelfile.HasFile) { string fileName = Excelfile.FileName; string strLastName = fileName.Substring(fileName.LastIndexOf(".") + 1).ToLower(); if (strLastName != "xlsx" && strLastName != "xls") { MsgBox("文件上传的文件不是常用文件格式！"); } else { fileName = DateTime.Now.ToString("yyyyMMddhhssms") + "_" + fileName; string path = Server.MapPath("~/Upload/NucleInurl"); try { string date = DateTime.Now.ToString("yyyyMMdd"); string excelPath = path + "/" + date + "/" + fileName; if (!Directory.Exists(path + "/" + date)) { Directory.CreateDirectory(path + "/" + date); } Excelfile.SaveAs(path + "/" + date + "/" + fileName); var stream = File.Open(excelPath, FileMode.Open, FileAccess.Read); string sheetName = null; if (sql(ExcelToDatatable(stream, Path.GetExtension(excelPath), sheetName))==true) { BindRepeater(); MsgBox("导入成功！"); } else { MsgBox("导入失败！"); } } catch (Exception ex) { MsgBox("导入Excel出错！"); } } } } #endregion
五、后台操作Excel单表转为Datatable类型数据
#region 将Excel单表转为Datatable public DataTable ExcelToDatatable(Stream stream, string fileType, string sheetName) { DataTable dt = new DataTable(); ISheet sheet = null; IWorkbook workbook = null; try { #region 判断excel版本 // workbook = WorkbookFactory.Create(fileType); //2007以上版本excel if (fileType == ".xlsx") { workbook = new XSSFWorkbook(stream); } //2007以下版本excel else if (fileType == ".xls") { workbook = new HSSFWorkbook(stream); } else { throw new Exception("传入的不是Excel文件！"); } #endregion if (!string.IsNullOrEmpty(sheetName)) { sheet = workbook.GetSheet(sheetName); if (sheet == null) { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; for (int i = firstRow.FirstCellNum; i < cellCount; i++) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue.Trim(); switch (cellValue) { case "姓名": cellValue = "UserName"; break; case "手机号": cellValue = "UserPhone"; break; case "身份证号": cellValue = "CardId"; break; case "身份类型": cellValue = "Type"; break; case "店铺号": cellValue = "ShopId"; break; case "最后一次核酸检测时间": cellValue = "NucleInlastTime"; break; case "": cellValue = "guid"; break; } if (!string.IsNullOrEmpty(cellValue)) { DataColumn dataColumn = new DataColumn(cellValue); dt.Columns.Add(dataColumn); } } } DataColumn dataColumnc = new DataColumn("guid"); dt.Columns.Add(dataColumnc); DataRow dataRow = null; // 遍历行 for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++) { IRow row = sheet.GetRow(j); dataRow = dt.NewRow(); if (row == null || row.FirstCellNum < 0) { continue; } // 遍历列 for (int i = row.FirstCellNum; i < cellCount; i++) { ICell cellData = row.GetCell(i); if (cellData != null) { // 判断是否为数字型，必须加这个判断不然下面的日期判断会异常 if (cellData.CellType == CellType.Numeric) { // 判断是否日期类型 if (DateUtil.IsCellDateFormatted(cellData)) { dataRow[i] = cellData.DateCellValue; } else { dataRow[i] = cellData.ToString().Trim(); } } else { dataRow[i] = cellData.ToString().Trim(); } } } dt.Rows.Add(dataRow); } } else { throw new Exception("没有获取到Excel中的数据表！"); } } catch (Exception ex) { } return dt; } #endregion
六、保存到数据库中（sql server数据库）
#region 导入数据库 public bool sql(DataTable dt) { bool bo = true; var Shop = db.Fy_shops.Where(o => o.isdeleted == 0); var Dictionary = db.SysDictionary.Where(o => o.ParentValue == "ShoperType"); if (dt!=null) { try { for (int i = 0; i < dt.Rows.Count; i++) { Fy_users entity = new Fy_users(); entity.guid = Guid.NewGuid().ToString(); entity.username = dt.Rows[i]["UserName"].ToString(); entity.userphone = dt.Rows[i]["UserPhone"].ToString(); entity.usertype = int.Parse(Dictionary.Where(o => o.FieldName == dt.Rows[i]["Type"].ToString()).FirstOrDefault().FieldValue); entity.shopguid = Shop.Where(o => o.shopid == dt.Rows[i]["ShopId"].ToString()).FirstOrDefault().guid; //entity.vaccinatetime1 = ConvertHelper.ConvertToDateTime(textVaccinateTime1.Text); //entity.vaccinatetime2 = ConvertHelper.ConvertToDateTime(textVaccinateTime2.Text); //entity.vaccinatetime3 = ConvertHelper.ConvertToDateTime(textVaccinateTime3.Text); entity.nucleinlasttime = ConvertHelper.ConvertToDateTime(dt.Rows[i]["NucleInlastTime"]); //entity.nucleinresult = int.Parse(textNucleInresult.Text); entity.cardid = dt.Rows[i]["CardId"].ToString(); //entity.antigenlasttime = ConvertHelper.ConvertToDateTime(textAntigenTime.Text); //entity.antigenresult = int.Parse(textAntigen.Text); //entity.healthstatus = int.Parse(textHealthstatus.Text); //entity.comment = txtComment.InnerText; //entity.nucleinurl = NuclePic.Value; //entity.antigenurl = AntigenPic.Value; entity.isdeleted = 0; entity.createdby = _User.Id.ToString(); entity.createdon = DateTime.Now; db.Fy_users.InsertOnSubmit(entity); WriteOperationLog("新增", entity.guid.ToString()); } db.SubmitChanges(); } catch (Exception) { bo = false; } } return bo; } #endregion
