Excel导入导出，通过datatable转存（篇一）

最新推荐文章于 2020-07-07 13:52:46 发布

weixin_33968104

最新推荐文章于 2020-07-07 13:52:46 发布

阅读量123

点赞数

        //导入数据
        public ActionResult ExpressInfoImport()
        {
            var ptcp = new BaseResponse() { DoFlag = true, DoResult = "Success" };
            var file = Request.Files["files"];
            if (file == null)
            {
                ptcp.DoFlag = false;
                ptcp.DoResult = "请选择上传文件";
                goto ovr;
            }
            try
            {
                var filename = Path.GetFileName(file.FileName);
                if (string.IsNullOrEmpty(filename))
                {
                    ptcp.DoFlag = false;
                    ptcp.DoResult = "请选择上传文件"; goto ovr;

                }
                var filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
                var fileEx = Path.GetExtension(filename);//获取上传文件的扩展名
                var noFileName = Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
                var maxSize = 4000 * 1024;//定义上传文件的最大空间大小为4M
                var fileType = ".xls,.xlsx";//定义上传文件的类型字符串

                var fileName = noFileName + "_" + System.Guid.NewGuid() + fileEx;
                if (!fileType.Contains(fileEx))
                {
                    ptcp.DoFlag = false;
                    ptcp.DoResult = "文件类型不对，只能导入xls和xlsx格式的文件"; goto ovr;
                }
                if (filesize >= maxSize)
                {
                    ptcp.DoFlag = false;
                    ptcp.DoResult = "上传文件超过4M，不能上传"; goto ovr;
                }
                var url = Configurator.JsonServiceUrl("UploadFile");
                if (!Directory.Exists(url))//如果不存在就创建file文件夹
                {
                    Directory.CreateDirectory(url);
                }
                var virtualPath = string.Format("{0}{1}", url, fileName);
                // 文件系统不能使用虚拟路径
                file.SaveAs(virtualPath);

                //读取文件内容，循环处理
                var stream = new FileStream(virtualPath, FileMode.Open);
                var dataTable = ExcelHelper.GetInstance().ReadExcelToDataTable(stream);
                var list = new List<ExpressImportModel>();
                try
                {
                    foreach (DataRow dr in dataTable.Rows)
                    {
                        var sysNo = dr["SysNo"];
                        var orderCode = dr["订单号"];
                        var logiscticNo = dr["物流单号"];
                        var sugges = dr["处理建议"];
                        list.Add(new ExpressImportModel()
                            {
                                SysNo = Convert.ToInt32(sysNo),
                                OrderCode = orderCode.ToString(),
                                LogiscticNo = logiscticNo.ToString(),
                                Suggest = sugges.ToString()
                            });
                    }
                }
                catch (Exception e)
                {
                    ptcp.DoFlag = false;
                    ptcp.DoResult = "导入格式不正确：e" + e.Message; goto ovr;
                }
                if (!list.Any())
                {
                    ptcp.DoFlag = false;
                    ptcp.DoResult = "导入内容为空"; goto ovr;
                }

                var response = ExpressClient.Instance.ImportHandleInfo(list);
                ptcp.DoFlag = response.DoFlag;
                ptcp.DoResult = response.DoResult;
                if (response.ErrDtos.Any())
                {
                    var errStr = new StringBuilder();
                    foreach (var item in response.ErrDtos)
                    {
                        errStr.AppendFormat(@"SysNo_{0}_OrderCode{1}_LogiscticNo{2}导入错误:{3} <br/>", item.SysNo,
                                            item.OrderCode, item.LogiscticNo, item.ErrReason);
                    }
                    ptcp.DoResult = "以下导入出现错误：<br/>" + errStr.ToString();
                }
            }
            catch (Exception ex)
            {
                ptcp.DoFlag = false;
                ptcp.DoResult = ex.ToString();
            }

        ovr:
            ViewBag.Result = ptcp;
            return View("Export/Export");
        }

        /// <summary>
        /// 读取Excel文件转化为DataTable
        /// </summary>
        /// <param name="stream"></param>
        /// <returns></returns>
        public DataTable ReadExcelToDataTable(Stream stream)
        {
            DataTable dt = new DataTable();
            workBook = new HSSFWorkbook(stream);
            workSheet = workBook.GetSheetAt(0);
            IEnumerator rows = workSheet.GetRowEnumerator();
            rows.MoveNext();
            HSSFRow row = (HSSFRow)rows.Current;
            for (int i = 0; i < workSheet.GetRow(0).LastCellNum; i++)
            {
                dt.Columns.Add(row.GetCell(i).StringCellValue);
            }
            while (rows.MoveNext())
            {
                row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    if (cell != null)
                    {
                        dr[i] = cell.ToString();
                    }
                    else
                    {
                        dr[i] = null;
                    }
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

导出：

       [HttpPost]
        [MultipleButton(Name = "action", Argument = "ExpressInfoExport")]
        public ActionResult ExpressInfoExport(ExpressInfoRefer refer)
        {
            refer.PageIndex = 1;
            refer.PageSize = int.MaxValue;
            var result = ExpressClient.Instance.QueryExpressInfoPageList(refer);
            if (result.List == null || !result.List.Any())
            {
                return View("Error");
            }
            var dicProperties = new Dictionary<string, string>();
            dicProperties.Add("SysNo", "SysNo");
            dicProperties.Add("OrderCode", "订单号");
            dicProperties.Add("LogiscticNo", "物流单号");
            dicProperties.Add("LogiscticId", "配送商ID");
            dicProperties.Add("LogiscticCompanyName", "快递公司");
            dicProperties.Add("OrderStatusContent", "订单扭转");
            dicProperties.Add("LogiscticContent", "物流信息");
            dicProperties.Add("LogisticOuterContent", "国际物流");
            dicProperties.Add("CallTypeDesc", "对接方式");
            dicProperties.Add("SubscribeStatusDesc", "订阅状态");
            dicProperties.Add("SubscribeCount", "订阅次数");
            dicProperties.Add("CallCount", "总调用(推送)次数");
            dicProperties.Add("LastStatusDesc", "运单状态");
            dicProperties.Add("PushRequestMessage", "快递100消息");
            dicProperties.Add("RequestFaildReason", "订阅失败原因");
            dicProperties.Add("OrderTypeDesc", "订单类型");
            dicProperties.Add("RowCreateDate", "创建日期");
            dicProperties.Add("Remark", "备注");
            dicProperties.Add("HandleSuggest", "处理建议");

            ExcelHelper.GetInstance().WriteListToExcel(result.List, dicProperties, "物流信息列表");
            return null;
        }

    [AttributeUsage(AttributeTargets.Method, AllowMultiple = false, Inherited = true)]
    public class MultipleButtonAttribute : ActionNameSelectorAttribute
    {
        public string Name { get; set; }
        public string Argument { get; set; }

        public override bool IsValidName(ControllerContext controllerContext, string actionName, MethodInfo methodInfo)
        {
            var isValidName = false;
            var keyValue = string.Format("{0}:{1}", Name, Argument);
            var value = controllerContext.Controller.ValueProvider.GetValue(keyValue);

            if (value != null)
            {
                controllerContext.Controller.ControllerContext.RouteData.Values[Name] = Argument;
                isValidName = true;
            }

            return isValidName;
        }
    }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">数据结果List</param>
        /// <param name="dicProperties">以字段-名称的形式</param>
        /// <param name="fileName">导出文件名</param>
        public void WriteListToExcel<T>(List<T> list, Dictionary<string, string> dicProperties, string fileName) where T : class
        {
            var size = 60000;
            var count = list.Count / size;
            if (list.Count%size != 0)
            {
                count += 1;
            }
            workBook = new HSSFWorkbook();
            ms = new MemoryStream();
            fileName = fileName == "" ? "导出Excel" : fileName;
            //fileName不能包含 : / \ ? * [ ]等特殊字符
            for (int a = 0; a < count; a++)
            {
                #region 创建sheet

                workSheet = workBook.CreateSheet(fileName+(a+1));

                DataTable dt = new DataTable();
                if (dicProperties.Count > 0 && list.Count > 0)
                {
                    Type type = typeof(T);
                    foreach (KeyValuePair<string, string> property in dicProperties)
                    {
                        //建立DataTable表头
                        dt.Columns.Add(new DataColumn(property.Value));
                    }
                    foreach (T t in list.Skip(size*a).Take(size))
                    {
                        DataRow dr = dt.NewRow();
                        int i = 0;
                        foreach (KeyValuePair<string, string> property in dicProperties)
                        {
                            PropertyInfo pi = type.GetProperty(property.Key);
                            object obj = pi.GetValue(t, null);
                            //为DataTable表内容赋值
                            dr[i] = obj == null ? "" : obj.ToString();
                            i++;
                        }
                        dt.Rows.Add(dr);
                    }

                    CreateExcel(dt, dicProperties);

                  
                }
                #endregion
            }
            WriteToExcel(fileName);
          
        }

        private void CreateExcel(DataTable dt, Dictionary<string, string> dicProperties)
        {
            IRow rowExcel;
            ICell cellExcel;
            int rowIndex = 0;

            rowExcel = workSheet.CreateRow(rowIndex);
            int intColumn = 0;

            foreach (KeyValuePair<string, string> dicProperty in dicProperties)
            {
                cellExcel = rowExcel.CreateCell(intColumn);
                cellExcel.SetCellValue(dicProperty.Value);
                ++intColumn;
            }

            foreach (DataRow row in dt.Rows)
            {
                ++rowIndex;
                rowExcel = workSheet.CreateRow(rowIndex);
                int intColumnContent = 0;
                foreach (KeyValuePair<string, string> property in dicProperties)
                {
                    cellExcel = rowExcel.CreateCell(intColumnContent);
                    cellExcel.SetCellValue(row[property.Value].ToString());
                    ++intColumnContent;
                }
            }
        }

        /// <summary>
        /// 导出Excel文件
        /// </summary>
        /// <param name="fileName"></param>
        private void WriteToExcel(string fileName)
        {
            workBook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            workBook = null;
            HttpContext current = HttpContext.Current;
            current.Response.ContentType = "application/ms-excel";
            current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");

            current.Response.BinaryWrite(ms.ToArray());
            current.Response.End();
            ms.Close();
            ms = null;
        }