C# NPOI 分页导出10W级数据

/// <summary>
        /// 下载PO
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="brand"></param>
        /// <param name="usedType"></param>
        /// <param name="key"></param>
        /// <param name="rule"></param>
        /// <param name="sidx"></param>
        /// <param name="sord"></param>
        /// <returns></returns>
        public JsonResult DownPO(string calculationType, int year, int month, string brand, string usedType, string key, string rule, string sidx, string sord)
        {
            try
            {
                //PO存的位置
                string savePath = "/Areas/DSF20/Content/POFile";

                #region 获取数据
                key = Server.UrlDecode(key);
                rule = Server.UrlDecode(rule);
                R_ContractInfo_Impl impl = new R_ContractInfo_Impl();
                int totalCount = 0;
                int totalPage = 0;
                List<V_BIDealerContract> list = impl.GetPageList(calculationType, year, month, brand, usedType, key, sidx, sord, 1, int.MaxValue, out totalCount, out totalPage);
                if (list.Count == 0)
                {
                    throw new Exception("无BI合同数据");
                }
                var dsfCalculation = new DSFCalculation_Impl();
                int saveCount = dsfCalculation.GetContractSaveCount(calculationType, year, month);
                if (list.Count != saveCount)
                {
                    throw new Exception("当前计算月份还有未存档的合同数据,请先存档再下载PO");
                }
                #endregion

                //数据转存T_DealerServiceFeeInfoByMonly
                var result = dsfCalculation.SaveDBToSFInfoByMonly(calculationType, year, month, this.CurrentUserEntry.GetDKCode());
                if (result == false)
                {
                    throw new Exception("数据拷贝异常");
                }
                //生成POList
                result = dsfCalculation.InsertPOList(year, month, this.CurrentUserEntry.GetDKCode());
                if (result == false)
                {
                    throw new Exception("生成POList异常");
                }
                //获取PO结果-Dealer
                DataTable dt = dsfCalculation.GetPOList(year, month);
                //获取PO详情-Contract
                var contractList = dsfCalculation.GetPODetailList(year, month);

                //声明工作表
                HSSFWorkbook wk = new HSSFWorkbook();

                #region 导出Dealer数据
                {
                    ISheet sheet = wk.CreateSheet("DealerInfo");
                    //设置表头
                    IRow cells = sheet.CreateRow(0);

                    #region 列名

                    cells.CreateCell(0).SetCellValue("Month");
                    cells.CreateCell(1).SetCellValue("DealerCode");
                    cells.CreateCell(2).SetCellValue("DealerNameCN");
                    cells.CreateCell(3).SetCellValue("Brand");
                    cells.CreateCell(4).SetCellValue("Region");
                    cells.CreateCell(5).SetCellValue("ProvinceEN");
                    cells.CreateCell(6).SetCellValue("CityEN");

                    cells.CreateCell(7).SetCellValue("AreaManager");
                    cells.CreateCell(8).SetCellValue("ContractVolume");
                    cells.CreateCell(9).SetCellValue("NewCarNum");
                    cells.CreateCell(10).SetCellValue("UsedCarNum");

                    cells.CreateCell(11).SetCellValue("UnitPrice");
                    cells.CreateCell(12).SetCellValue("Category");

                    cells.CreateCell(13).SetCellValue("NewCarServiceFee");
                    cells.CreateCell(14).SetCellValue("UsedCarServiceFee");

                    cells.CreateCell(15).SetCellValue("TaxPayerType");
                    cells.CreateCell(16).SetCellValue("ServiceFeeAmount");
                    cells.CreateCell(17).SetCellValue("BasicServiceFeeAmount");
                    cells.CreateCell(18).SetCellValue("OtherServiceFeeAmount");
                    cells.CreateCell(19).SetCellValue("SpecialFee");
                    cells.CreateCell(20).SetCellValue("ServiceFeeStatus");
                    cells.CreateCell(21).SetCellValue("ServiceFeeDetail");

                    cells.CreateCell(22).SetCellValue("GeneralTaxRate");
                    cells.CreateCell(23).SetCellValue("ActualTaxRate");
                    cells.CreateCell(24).SetCellValue("ActualUnityPrice");
                    #endregion
                    int i = 0;
                    foreach (DataRow row in dt.Rows)
                    {
                        #region 数据
                        i++;
                        IRow rowtemp = sheet.CreateRow(i); //循环行
                        rowtemp.CreateCell(0).SetCellValue(row["Month"].ToString().Replace("-", ""));
                        rowtemp.CreateCell(1).SetCellValue(row["DealerCode"].ToString());
                        rowtemp.CreateCell(2).SetCellValue(row["DealerNameCN"].ToString());
                        rowtemp.CreateCell(3).SetCellValue(row["Brand"].ToString());
                        rowtemp.CreateCell(4).SetCellValue(row["Region"].ToString());
                        rowtemp.CreateCell(5).SetCellValue(row["ProvinceEN"].ToString());
                        rowtemp.CreateCell(6).SetCellValue(row["CityEN"].ToString());

                        rowtemp.CreateCell(7).SetCellValue(row["AreaManager"].ToString());
                        rowtemp.CreateCell(8).SetCellValue(row["ContractVolume"].ToString());
                        rowtemp.CreateCell(9).SetCellValue(row["NewCarNum"].ToString());

                        rowtemp.CreateCell(10).SetCellValue(row["UsedCarNum"].ToString());
                        rowtemp.CreateCell(11).SetCellValue(row["UnitPrice"].ToString());
                        rowtemp.CreateCell(12).SetCellValue(row["Category"].ToString());
                        rowtemp.CreateCell(13).SetCellValue(row["NewCarServiceFee"].ToString());

                        rowtemp.CreateCell(14).SetCellValue(row["UsedCarServiceFee"].ToString());
                        rowtemp.CreateCell(15).SetCellValue(row["TaxPayerType"].ToString());
                        rowtemp.CreateCell(16).SetCellValue(row["ServiceFeeAmount"].ToString());
                        rowtemp.CreateCell(17).SetCellValue(row["BasicServiceFeeAmount"].ToString());
                        rowtemp.CreateCell(18).SetCellValue(row["OtherServiceFeeAmount"].ToString());

                        rowtemp.CreateCell(19).SetCellValue(row["SpecialFee"].ToString());
                        rowtemp.CreateCell(20).SetCellValue(row["ServiceFeeStatus"].ToString());
                        rowtemp.CreateCell(21).SetCellValue(row["ServiceFeeDetail"].ToString());

                        rowtemp.CreateCell(22).SetCellValue(row["GeneralTaxRate"].ToString());
                        rowtemp.CreateCell(23).SetCellValue(row["ActualTaxRate"].ToString());
                        rowtemp.CreateCell(24).SetCellValue(row["ActualUnityPrice"].ToString());

                        #endregion
                    }
                }
                #endregion

                #region 导出Contract数据
                //处理65536问题
                int rowSize = 65000;//页大小
                //总页数
                int pageSize = (int)Math.Ceiling((contractList.Count * 0.1) / (rowSize * 0.1));

                for (int currentPage = 1; currentPage <= pageSize; currentPage++)
                {
                    var dbList = contractList.Skip((currentPage - 1) * rowSize).Take(rowSize).ToList();

                    ISheet sheet = wk.CreateSheet("ContractInfo_" + currentPage.ToString().PadLeft(2, '0'));
                    //设置表头
                    IRow cells = sheet.CreateRow(0);

                    #region 列名

                    cells.CreateCell(0).SetCellValue("月份");
                    cells.CreateCell(1).SetCellValue("经销商编码");
                    cells.CreateCell(2).SetCellValue("合同号");
                    cells.CreateCell(3).SetCellValue("申请日期");
                    cells.CreateCell(4).SetCellValue("激活日期");
                    cells.CreateCell(5).SetCellValue("经销商主品牌");
                    cells.CreateCell(6).SetCellValue("厂商");

                    cells.CreateCell(7).SetCellValue("新车/二手车");
                    cells.CreateCell(8).SetCellValue("普通利率");
                    cells.CreateCell(9).SetCellValue("INT利率");
                    cells.CreateCell(10).SetCellValue("项目号");

                    cells.CreateCell(11).SetCellValue("是否大众集团品牌");
                    cells.CreateCell(12).SetCellValue("是否增益贷");
                    cells.CreateCell(13).SetCellValue("是否附加贷");
                    cells.CreateCell(14).SetCellValue("是否自主贴息");
                    cells.CreateCell(15).SetCellValue("是否奢牌");

                    cells.CreateCell(16).SetCellValue("总价");
                    cells.CreateCell(17).SetCellValue("首付");
                    cells.CreateCell(18).SetCellValue("贷款");
                    cells.CreateCell(19).SetCellValue("贷期");
                    cells.CreateCell(20).SetCellValue("附加贷");

                    cells.CreateCell(21).SetCellValue("基础服务费");
                    cells.CreateCell(22).SetCellValue("增益贷服务费");
                    cells.CreateCell(23).SetCellValue("附加贷服务费");
                    cells.CreateCell(24).SetCellValue("大额融资服务费");
                    cells.CreateCell(25).SetCellValue("经销商自主贴息");

                    cells.CreateCell(26).SetCellValue("单价");

                    cells.CreateCell(27).SetCellValue("原服务费");
                    cells.CreateCell(28).SetCellValue("总服务费");
                    cells.CreateCell(29).SetCellValue("CarveOut基础服务");
                    cells.CreateCell(30).SetCellValue("CarveOut其他服务费");
                    cells.CreateCell(31).SetCellValue("备注");
                    #endregion

                    int i = 0;
                    foreach (var contract in dbList)
                    {
                        #region 数据
                        i++;
                        IRow rowtemp = sheet.CreateRow(i); //循环行
                        rowtemp.CreateCell(0).SetCellValue(contract.Year + "" + contract.Month.ToString().PadLeft(2, '0'));
                        rowtemp.CreateCell(1).SetCellValue(contract.DealerCode);
                        rowtemp.CreateCell(2).SetCellValue(contract.LoanNo);
                        rowtemp.CreateCell(3).SetCellValue(contract.ApplyDate.HasValue ? contract.ApplyDate.Value.ToString("yyyy-MM-dd") : "");
                        rowtemp.CreateCell(4).SetCellValue(contract.ActiveDate.HasValue ? contract.ActiveDate.Value.ToString("yyyy-MM-dd") : "");
                        rowtemp.CreateCell(5).SetCellValue(contract.Brand);
                        rowtemp.CreateCell(6).SetCellValue(contract.Manuftr);

                        rowtemp.CreateCell(7).SetCellValue(contract.UsedType);
                        rowtemp.CreateCell(8).SetCellValue(contract.NormalRate.HasValue ? contract.NormalRate.Value : 0);
                        rowtemp.CreateCell(9).SetCellValue(contract.INTRate.HasValue ? contract.INTRate.Value : 0);
                        rowtemp.CreateCell(10).SetCellValue(StrUtil.SubString(contract.ProjectCode, 4));

                        rowtemp.CreateCell(11).SetCellValue((contract.IsVWBrand.HasValue && contract.IsVWBrand.Value) ? "是" : "否");
                        rowtemp.CreateCell(12).SetCellValue((contract.IsGainLoan.HasValue && contract.IsGainLoan.Value) ? "是" : "否");
                        rowtemp.CreateCell(13).SetCellValue((contract.IsAddLoan.HasValue && contract.IsAddLoan.Value) ? "是" : "否");
                        rowtemp.CreateCell(14).SetCellValue((contract.IsDealerInDis.HasValue && contract.IsDealerInDis.Value) ? "是" : "否");
                        rowtemp.CreateCell(15).SetCellValue((contract.IsLux.HasValue && contract.IsLux.Value) ? "是" : "否");

                        rowtemp.CreateCell(16).SetCellValue(StrUtil.DoubleFormat(contract.CashPriceTotal));
                        rowtemp.CreateCell(17).SetCellValue(StrUtil.DoubleFormat(contract.DownPayTotal));
                        rowtemp.CreateCell(18).SetCellValue(StrUtil.DoubleFormat(contract.ContractPriceTotal));
                        rowtemp.CreateCell(19).SetCellValue(StrUtil.DoubleFormat(contract.ContractPriceTNR));
                        rowtemp.CreateCell(20).SetCellValue(StrUtil.DoubleFormat(contract.VAPPriceTotal)); ;

                        rowtemp.CreateCell(21).SetCellValue(StrUtil.DoubleFormat(contract.BasicServiceFee));
                        rowtemp.CreateCell(22).SetCellValue(StrUtil.DoubleFormat(contract.GainLoanServiceFee));
                        rowtemp.CreateCell(23).SetCellValue(StrUtil.DoubleFormat(contract.AddLoanServiceFee));
                        rowtemp.CreateCell(24).SetCellValue(StrUtil.DoubleFormat(contract.LargeAmountServiceFee));
                        rowtemp.CreateCell(25).SetCellValue(StrUtil.DoubleFormat(contract.DealerInDisServiceFee));

                        rowtemp.CreateCell(26).SetCellValue(contract.UnitPrice.HasValue ? StrUtil.DoubleFormat(contract.UnitPrice) : "");
                        double sf = contract.BasicServiceFee ?? 0;
                        sf += contract.GainLoanServiceFee ?? 0;
                        sf += contract.AddLoanServiceFee ?? 0;
                        sf += contract.LargeAmountServiceFee ?? 0;
                        sf += contract.DealerInDisServiceFee ?? 0;
                        rowtemp.CreateCell(27).SetCellValue(StrUtil.DoubleFormat(sf));
                        rowtemp.CreateCell(28).SetCellValue(StrUtil.DoubleFormat(contract.TotalSF));
                        rowtemp.CreateCell(29).SetCellValue(StrUtil.DoubleFormat(contract.CoveroutBaseSF));
                        rowtemp.CreateCell(30).SetCellValue(StrUtil.DoubleFormat(contract.CoveroutOtherSF));

                        rowtemp.CreateCell(31).SetCellValue(contract.Remark);
                        #endregion
                    }
                }
                #endregion
                string dir = Server.MapPath("~" + savePath);
                if (Directory.Exists(dir) == false)
                {
                    Directory.CreateDirectory(dir);
                }
                string fileName = year + "" + month.ToString().PadLeft(2, '0') + "POList.xls";
                string filePath = dir + "\\" + fileName;



                FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                wk.Write(fs);
                fs.Dispose();
                
                return Json(new { msg = "生成成功,PO号:" + year + "" + month.ToString().PadLeft(2, '0') + ",详情数据已导出", result = true, data = WebCommon.WebPrefix + savePath + "/" + fileName }, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                return Json(new { msg = "生成失败:" + ex.Message, result = false, data = "" }, JsonRequestBehavior.AllowGet);
            }
        }

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值