思路：用户点击下载模板按钮，获取到excel模板，然后向里面填写数据保存。from表单提交的时候选择保存好的excel，实现数据的批量导入过程
先把模板放在服务器的项目目录下面：如
模板我一般放在：Filedownload检测项目价格导入模板.xls
模板内容如：
下载模板的按钮只需指向服务器的文件地址，模板会自动下载。
地址如：var FilePath = "http://*********/File/download/检测项目价格导入模板.xls"，但是地址一般不写死，而是域名是从webConfig文件中获取的。
例如：<add key="FileServiceAddr" value="http://localhost:8066/"/>
接口如：
/// <summary> /// 下载模板 /// </summary> /// <returns></returns> [HttpGet] [AllowAnonymous] public string DownLoadTemple() { try { //var FilePath = "http://testadmin.hysyzs.com/download/检测项目价格导入模板.xls"; var FilePath = System.Configuration.ConfigurationManager.AppSettings["FileServiceAddr"].ToString() +"/download/检测项目价格导入模板.xls"; //var excel = new Aspose.Cells.Workbook(); //打开上传文件 //excel.Open(FilePath); //var fileMemoryStream = FilePath.Write(ms); //HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK); //response.Content = new ByteArrayContent(fileMemoryStream.ToArray()); //response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream"); //response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") //{ // FileName = "会议签到汇总表.xls" //}; return FilePath; } catch (Exception e) { return e.ToString(); } }
然后用户下载后，编辑内容，form表单提交，后台的处理直接上代码
/// <summary> /// 批量导入价格（form表单方式提交数据） /// </summary> /// <returns></returns> [HttpPost] [AllowAnonymous] public ReturnObject<List<string>> UploadImportPrize() { ReturnObject<List<string>> ret = new ReturnObject<List<string>>(); List<string> errors = new List<string>(); List<string> fails = new List<string>(); string completePath = ""; HttpFileCollection filelist = HttpContext.Current.Request.Files; if (filelist != null && filelist.Count > 0) { for (int i = 0; i < filelist.Count; i++) { HttpPostedFile file = filelist[i]; String Tpath = DateTime.Now.ToString("yyyy-MM-dd") + @"/import/"; string filename = file.FileName; //string FilePath = "D:\" + Tpath + filename; //
string FilePath = System.Web.Hosting.HostingEnvironment.MapPath(@"~/") + Tpath + filename；
//这里应该获取当前项目路径地址，再在后面创建文件，如果按上面的注释掉的写法，在服务器上没有找到d盘，则会报错。
string diPath = Path.GetDirectoryName(FilePath); //获取到当前目录的文件夹，没有就创建 if (!Directory.Exists(diPath)) { Directory.CreateDirectory(diPath); }; try { completePath = FilePath; file.SaveAs(completePath); //生成一个文件目录，把上传的文件写入到目录中去， var d = ImportPrize(completePath); //然后获取这个目录的文件，用DataTable进行读取，然后解析excel的每行数据，批量写入到数据库中 ret.datas = d.datas; ret.isOK = true; ret.errorCode = 0; } catch (Exception ex) { ret.msg = "上传文件写入失败：" + ex.Message; ret.isOK = false; ret.errorCode = 3; } } } else { ret.msg = "上传的文件信息不存在！"; ret.isOK = false; ret.errorCode = 3; } return ret; }
将excel的数据加载到DataTable中去
/// <summary> /// /// </summary> /// <param name="path"></param> /// <returns></returns> private DataTable ReadExcelToTable(string path) { DataTable result = new DataTable(); Workbook workbook = new Workbook(); workbook.Open(path); Cells cells = workbook.Worksheets[0].Cells; result = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, false); return result; }
批量写入数据库的过程
/// <summary> /// 写入数据到数据库 /// </summary> /// <param name="completePath"></param> /// <returns></returns> public ReturnObject<List<string>> ImportPrize(string completePath) { ReturnObject<List<string>> ret = new ReturnObject<List<string>>(); List<string> errors = new List<string>(); List<string> fails = new List<string>(); int succescount = 0; int failcount = 0; try { var a = ReadExcelToTable(completePath).Rows; List<InPrize> list = new List<InPrize>(); if (a.Count > 1) { for (var i = 0; i < a.Count; i++) { if (i == 0) { #region 判断列名 var col_one = a[i][0].ToString(); if (col_one != "版本") { throw new Exception("格式错误，导入文件的第一列应为【版本】！"); } //var col_two = a[i][1].ToString(); //if (col_two != "版本状态") //{ // throw new Exception("格式错误，导入文件的第一列应为【版本状态】！"); //} var col_three = a[i][1].ToString(); if (col_three != "检测机构检测项") { throw new Exception("格式错误，导入文件的第一列应为【检测机构检测项】！"); } var col_five = a[i][2].ToString(); if (col_five != "区域市场") { throw new Exception("格式错误，导入文件的第一列应为【区域市场】！"); } var col_six = a[i][3].ToString(); if (col_six != "区域市场价格") { throw new Exception("格式错误，导入文件的第一列应为【区域市场价格】！"); } var col_even = a[i][4].ToString(); if (col_even != "VIP零售价格") { throw new Exception("格式错误，导入文件的第一列应为【VIP零售价格】！"); } #endregion } else { //int intType2; var intTypeStr2 = a[i][1].ToString(); //if (!int.TryParse(intTypeStr2, out intType2)) //{ // throw new Exception("格式错误，【版本状态】【第" + (i + 1) + "行】应为整数类型数据！"); //} int intType1; var intStr1 = a[i][1].ToString(); if (!int.TryParse(intStr1, out intType1)) { throw new Exception("格式错误，【检测机构检测项】【第" + (i + 1) + "行】应为整数类型数据！"); } int intType; var intStr = a[i][2].ToString(); if (!int.TryParse(intStr, out intType)) { throw new Exception("格式错误，【区域市场】【第" + (i + 1) + "行】应为整数类型数据！"); } decimal docmoney_int; var docmoney_str = a[i][3].ToString(); if (!decimal.TryParse(docmoney_str, out docmoney_int)) { throw new Exception("格式错误，【区域市场价格】【第" + (i + 1) + "行】应为数字类型数据！"); } decimal doczmoney_int; var doczmoney_str = a[i][4].ToString(); if (!decimal.TryParse(doczmoney_str, out doczmoney_int)) { throw new Exception("格式错误，【VIP零售价格】【第" + (i + 1) + "行】应为数字类型数据！"); } list.Add(new InPrize() { Version = a[i][0].ToString(), //VersionState = false, DetectionOrgDetectionItemID = intType1, AreaMarketID = intType, Price = docmoney_int, vipPrice = doczmoney_int }); } } #region ListForEach using (YZS_BUSEntities context = new YZS_BUSEntities()) { foreach (var item in list) { var entities = context.Set<区域产品信息>().Where(n => n.检测机构检测项.Value == item.DetectionOrgDetectionItemID && n.区域市场.Value == item.AreaMarketID && n.版本状态.Value == false).ToList(); if (entities.Count() > 0) { fails.Add("检测项:" + item.DetectionOrgDetectionItemID + "区域市场:" + item.AreaMarketID); failcount++; } else { context.区域产品信息.Add(new 区域产品信息() { 版本 = item.Version, 版本状态 = false, 检测机构检测项 = item.DetectionOrgDetectionItemID, 区域市场 = item.AreaMarketID, 区域市场价格 = item.Price, VIP零售价格 = item.vipPrice }); succescount++; } } context.SaveChanges(); string failRemark = ""; if (failcount > 0) { failRemark = ",失败的数据：" + string.Join(",", fails); } errors.Add($"成功导入{succescount}条样本信息！失败{failcount}条{failRemark}"); } ret.isOK = true; ret.errorCode = 0; ret.msg = ""; ret.count = succescount; ret.datas = errors; #endregion } else { throw new Exception("所选文件格式错误，或者未匹配到有效数据！"); } } catch (Exception error) { ret.isOK = false; ret.errorCode = 200; ret.msg = error.Message; ret.count = 0; ret.datas = errors; } return ret; }
实体模型
public class InPrize { /// <summary> ///检测机构检测项ID /// </summary> public int DetectionOrgDetectionItemID { get; set; } /// <summary> ///区域市场ID /// </summary> public int AreaMarketID { get; set; } /// <summary> ///价格 /// </summary> public decimal? Price { get; set; } /// <summary> /// vip零售价格 /// </summary> public decimal? vipPrice { get; set; } /// <summary> /// 版本 /// </summary> public string Version { get; set; } ///// <summary> ///// 版本状态 ///// </summary> //public int? VersionState { get; set; } }
