页面代码：
<form id="form1" enctype="multipart/form-data"> <div style="float:right"> <button type="button" class="btn btn-primary" onclick="$('#fileUpload').click()" id="reviewFile">浏览</button> <button class="btn btn-primary" type="button" style="margin-left:5px;height:30px;" id="dataExport">批量导入</button> <input type="button" class="btn btn-primary" style="margin-left:5px;height:30px;" id="downLoad" value="下载模板"> </div> <div style="float:right;margin-top:5px"> <input id="fileUpload" name="fileUpload" type="file" style="display:none" /> <input id="fileText" type="text" class="form-control" disabled /> </div> <script> $("#fileUpload").change(function () { $("#fileText").val($(this).val()); }) </script> </form>
js代码：
//导入excel数据 $("#dataExport").click(function () { var formData = new FormData($('form')[0]); $.ajax({ url: '/BaseInfoPage/Upload', type: 'POST', xhr: function () { return $.ajaxSettings.xhr(); }, data: formData, cache: false, contentType: false, processData: false, success: function (data) { if (data == "导入成功!") { layer.msg(data, { icon: 1, time: 5000 }, function () { location.reload(); //刷新父页面 第二个参数设置msg显示的时间长短 }); } else { layer.msg(data, { icon: 0, time: 5000 }, function () { return; }); } }, error: function (e) { layer.msg(e, { icon: 0, time: 5000 }, function () { return; }); } }); })
c#后台代码：
public string Upload(HttpPostedFileBase fileUpload) { if (fileUpload == null) { return "文件为空"; } string fileExtension = Path.GetExtension(fileUpload.FileName);//获取文件名后缀 try { //判断文件类型 if (".xls" == fileExtension || ".xlsx" == fileExtension) { //将硬盘路径转化为服务器路径的文件流 //string fileName = Path.Combine(Request.MapPath("~/ExcelTemplate"), Path.GetFileName(fileUpload.FileName)); string fileName = fileUpload.FileName; string filePath = ""; filePath = CSysCfg.exFilePath; if (!Directory.Exists(filePath)) { Directory.CreateDirectory(filePath); } //保存模板到服务器 fileUpload.SaveAs(filePath + "\" + fileName); //从NPOI读取到的Excel的数据，保存到excelTable里 DataTable excelTable = new DataTable(); excelTable = GetExcelDataTable(filePath + "\" + fileName);//自定义方法 //把表的中文表头转换成数据库表中对应的英文 DataTable dbdata = new DataTable(); dbdata.Columns.Add("ltl_Id"); dbdata.Columns.Add("ltl_PlateId"); dbdata.Columns.Add("ltl_StarteTime"); dbdata.Columns.Add("ltl_EndTime"); for (int i = 0; i < excelTable.Rows.Count; i++) { DataRow dr = excelTable.Rows[i]; DataRow dr_ = dbdata.NewRow(); dr_["ltl_Id"] = dr["申请编号"]; dr_["ltl_PlateId"] = dr["车牌号码"]; dr_["ltl_StarteTime"] = dr["开始日期"]; dr_["ltl_EndTime"] = dr["结束日期"]; dbdata.Rows.Add(dr_); } RemoveEmpty(dbdata);//自定义方法 //获取连接字符串，调用批量插入数据库的方法 需更改web.config添加配置 string constr = System.Configuration.ConfigurationManager.AppSettings["exportData"]; SqlBulkCopyByDatatable(constr, "LargeTransportLicense", dbdata);//自定义方法(连接字符串，表名，数据) return "导入成功!"; } else { return "只可以选择Excel文件!"; } } catch { return "导入失败!"; } } // 从Excel中获取数据到DataTable public static DataTable GetExcelDataTable(string filePath) { IWorkbook Workbook; DataTable table = new DataTable(); try { using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式 string fileExt = Path.GetExtension(filePath).ToLower(); if (fileExt == ".xls") { Workbook = new HSSFWorkbook(fileStream); } else if (fileExt == ".xlsx") { Workbook = new XSSFWorkbook(fileStream); } else { Workbook = null; } } //定位在第一个sheet ISheet sheet = Workbook.GetSheetAt(0); //第一行为标题行 IRow headerRow = sheet.GetRow(1); int cellCount = headerRow.LastCellNum;// 是当前行的总列数 int rowCount = sheet.LastRowNum;////LastRowNum 是当前表的总行数-1（注意） //循环添加标题列 for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } List<string> regionName = new List<string>(); //数据 for (int i = (sheet.FirstRowNum + 2); i <= rowCount; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); if (row != null) { for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = GetCellValue2(row.GetCell(j)); } } } table.Rows.Add(dataRow); } } catch (Exception ex) { throw ex; } return table; } //数据类型判断 方式一 private static string GetCellValue(NPOI.SS.UserModel.ICell cell) { if (cell == null) { return string.Empty; } else { switch (cell.CellType) { case CellType.Blank: return string.Empty; case CellType.Boolean: return cell.BooleanCellValue.ToString(); case CellType.Error: return cell.ErrorCellValue.ToString(); case CellType.Numeric://数值 case CellType.Unknown: default: return cell.ToString(); case CellType.String: return cell.StringCellValue; case CellType.Formula://公式 try { HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); return cell.ToString(); } catch { return cell.NumericCellValue.ToString(); } } } } //数据类型判断，并设置为对应的数据类型 方式二 public static object GetCellValue2(NPOI.SS.UserModel.ICell cell) { object value = null; if (cell == null) { value = 0; } try { if (cell.CellType != CellType.Blank) { switch (cell.CellType) { case CellType.Blank: value = string.Empty; break; case CellType.Numeric: // 日期 if (DateUtil.IsCellDateFormatted(cell)) { value = cell.DateCellValue; } else { // 数值 value = cell.NumericCellValue; } break; case CellType.Boolean: // Boolean type value = cell.BooleanCellValue; break; case CellType.Formula: value = cell.CellFormula; break; default: // String type value = cell.StringCellValue; break; } } else { value = 0; } } catch (Exception) { value = 0; } return value; } /// <summary> /// 大数据插入 /// </summary> /// <param name="connectionString">目标库连接</param> /// <param name="TableName">目标表</param> /// <param name="dtSelect">来源数据</param> public static void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dtSelect) { using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction)) { try { sqlbulkcopy.DestinationTableName = TableName; sqlbulkcopy.BatchSize = 20000; sqlbulkcopy.BulkCopyTimeout = 0;//不限时间 for (int i = 0; i < dtSelect.Columns.Count; i++) { sqlbulkcopy.ColumnMappings.Add(dtSelect.Columns[i].ColumnName, dtSelect.Columns[i].ColumnName); } sqlbulkcopy.WriteToServer(dtSelect); } catch (System.Exception ex) { throw ex; } } } } //在导入Excel数据的时候，有时候会有空行，用RemoveEmpty方法去空 protected void RemoveEmpty(DataTable dt) { List<DataRow> removelist = new List<DataRow>(); for (int i = 0; i < dt.Rows.Count; i++) { bool IsNull = true; for (int j = 0; j < dt.Columns.Count; j++) { if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim())) { IsNull = false; } } if (IsNull) { removelist.Add(dt.Rows[i]); } } for (int i = 0; i < removelist.Count; i++) { dt.Rows.Remove(removelist[i]); } }
注：此方法需在web.config中添加配置
<appSettings>
<add key="exportData" value="server=xxx;database=xx;uid=xxx;pwd=xxx" />
</appSettings>