c#WebApi使用form表单提交excel，实现批量写入数据库

思路：用户点击下载模板按钮，获取到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; }

        }