Exemple #1
0
        public void WriteLRAccountSheet(ContractInfoBusiness contractInfoBusiness, string OutPutFolderPath, string dateyyyymm)
        {
            IExcel excelApp = new ExcelCore.ExcelCore();

            try
            {
                ProcessLogProxy.Normal("Start building LRAccount excel");
                var           excelPath     = OutPutFolderPath + @"\TEMP_" + ExcelTemplateName.LRAccount + ".xlsx";
                ExcelTemplate excelTemplate = new ExcelTemplate();
                excelTemplate.CreateTemplate(excelApp, excelPath, ExcelTemplateName.LRAccount);//创建模板
                GetLRAccountData(contractInfoBusiness, dateyyyymm);
                excelApp.OpenExcel(excelPath, false);
                for (int i = 0; i < listLRAccount.Count; i++)
                {
                    var model = listLRAccount[i];
                    excelApp.SetCellValue("Sheet1", i + 2, "A", model.TransactionNo);
                    excelApp.SetCellValue("Sheet1", i + 2, "B", model.CompanyCode);
                    excelApp.SetCellValue("Sheet1", i + 2, "C", model.AccountID);
                    excelApp.SetCellValue("Sheet1", i + 2, "D", model.AccountingPeriodfrom);
                    excelApp.SetCellValue("Sheet1", i + 2, "E", model.AccountingPeriodto);
                    excelApp.SetCellValue("Sheet1", i + 2, "F", model.ReinsurerCode);
                    excelApp.SetCellValue("Sheet1", i + 2, "G", model.ReinsurerName);
                    excelApp.SetCellValue("Sheet1", i + 2, "H", model.ReInsuranceContNo);
                    excelApp.SetCellValue("Sheet1", i + 2, "I", model.ReInsuranceContName);
                    excelApp.SetCellValue("Sheet1", i + 2, "J", model.Currency);
                    excelApp.SetCellValue("Sheet1", i + 2, "K", model.ReinsurancePremium);
                    excelApp.SetCellValue("Sheet1", i + 2, "L", model.ReinsuranceCommssionRate);
                    excelApp.SetCellValue("Sheet1", i + 2, "M", model.ReinsuranceCommssion);
                    excelApp.SetCellValue("Sheet1", i + 2, "N", model.ReturnReinsurancePremium);
                    excelApp.SetCellValue("Sheet1", i + 2, "O", model.ReturnReinsuranceCommssion);
                    excelApp.SetCellValue("Sheet1", i + 2, "P", model.ReturnSurrenderPay);
                    excelApp.SetCellValue("Sheet1", i + 2, "Q", model.ReturnClaimPay);
                    excelApp.SetCellValue("Sheet1", i + 2, "R", model.ReturnMaturity);
                    excelApp.SetCellValue("Sheet1", i + 2, "S", model.ReturnAnnuity);
                    excelApp.SetCellValue("Sheet1", i + 2, "T", model.ReturnLivBene);
                    excelApp.SetCellValue("Sheet1", i + 2, "U", model.AccountStatus);
                    excelApp.SetCellValue("Sheet1", i + 2, "V", model.PairingStatus);
                    excelApp.SetCellValue("Sheet1", i + 2, "W", model.PairingDate);
                    excelApp.SetCellValue("Sheet1", i + 2, "X", model.CurrentRate);
                }
                excelApp.SetSheetAutoFit("Sheet1");
                excelApp.Save();
                excelApp.Close();
                ProcessLogProxy.SuccessMessage("Build Success");
            }
            catch (Exception ex)
            {
                ProcessLogProxy.Error(ex.Message);
                ProcessLogProxy.Error("Build fail");
            }
        }
Exemple #2
0
        public void WriteLRInsureContSheet(ContractInfoBusiness contractInfoBusiness, string OutPutFolderPath, string LastDateOfMonthyyyyMMdd)
        {
            IExcel excelApp = new ExcelCore.ExcelCore();

            try
            {
                ProcessLogProxy.Normal("Start building LRInsureCont excel");
                LastDateOfMonth = LastDateOfMonthyyyyMMdd;
                var           excelPath     = OutPutFolderPath + @"\TEMP_" + ExcelTemplateName.LRInsureCont + ".xlsx";
                ExcelTemplate excelTemplate = new ExcelTemplate();
                excelTemplate.CreateTemplate(excelApp, excelPath, ExcelTemplateName.LRInsureCont);//创建模板
                //GetLRInsureContData(contractInfoBusiness);//得到需写入excel的数据
                excelApp.OpenExcel(excelPath, false);
                for (int i = 0; i < contractInfoBusiness.lstLRInsureContModel.Count; i++)
                {
                    var model = listLRInsureContModel[i];
                    excelApp.SetCellValue("Sheet1", i + 2, "A", CommFuns.GetTransactionNo2(i + 1, LastDateOfMonth));
                    excelApp.SetCellValue("Sheet1", i + 2, "B", model.CompanyCode);
                    excelApp.SetCellValue("Sheet1", i + 2, "C", model.ReInsuranceContNo);
                    excelApp.SetCellValue("Sheet1", i + 2, "D", model.ReInsuranceContName);
                    excelApp.SetCellValue("Sheet1", i + 2, "E", model.ReInsuranceContTitle);
                    excelApp.SetCellValue("Sheet1", i + 2, "F", model.MainReInsuranceContNo);
                    excelApp.SetCellValue("Sheet1", i + 2, "G", model.ContOrAmendmentType);
                    excelApp.SetCellValue("Sheet1", i + 2, "H", model.ContAttribute);
                    excelApp.SetCellValue("Sheet1", i + 2, "I", model.ContStatus);
                    excelApp.SetCellValue("Sheet1", i + 2, "J", model.TreatyOrFacultativeFlag);
                    excelApp.SetCellValue("Sheet1", i + 2, "K", model.ContSigndate);
                    excelApp.SetCellValue("Sheet1", i + 2, "L", model.PeriodFrom);
                    excelApp.SetCellValue("Sheet1", i + 2, "M", model.PeriodTo);
                    excelApp.SetCellValue("Sheet1", i + 2, "N", model.ContType);
                    excelApp.SetCellValue("Sheet1", i + 2, "O", model.ReinsurerCode);
                    excelApp.SetCellValue("Sheet1", i + 2, "P", model.ReinsurerName);
                    excelApp.SetCellValue("Sheet1", i + 2, "Q", model.ChargeType);
                }
                excelApp.SetSheetAutoFit("Sheet1");
                excelApp.Save();
                excelApp.Close();
                ProcessLogProxy.SuccessMessage("Build Success");
            }
            catch (Exception ex)
            {
                ProcessLogProxy.Error(ex.Message);
                ProcessLogProxy.Error("Build fail");
            }
        }
Exemple #3
0
        public void WriteLREdorSheet(ContractInfoBusiness contractInfoBusiness, string OutPutFolderPath, string dateyyyymm)
        {
            IExcel excelApp     = new ExcelCore.ExcelCore();
            int    serialNumber = 0;

            try
            {
                ProcessLogProxy.Normal("Start building LREdor excel");
                var           excelPath     = OutPutFolderPath + @"\TEMP_" + ExcelTemplateName.LREdor + ".xlsx";
                ExcelTemplate excelTemplate = new ExcelTemplate();
                excelTemplate.CreateTemplate(excelApp, excelPath, ExcelTemplateName.LREdor);//创建模板
                GetGroupLREdorData(contractInfoBusiness, dateyyyymm);
                GetLREdorData(contractInfoBusiness, dateyyyymm);
                excelApp.OpenExcel(excelPath, false);
                for (int i = 0; i < lREdorModelList.Count; i++)
                {
                    serialNumber++;
                    var model = lREdorModelList[i];
                    excelApp.SetCellValue(i + 2, "A", CommFuns.GetTransactionNo5(i + 1, dateyyyymm));
                    excelApp.SetCellValue(i + 2, "B", model.CompanyCode);
                    excelApp.SetCellValue(i + 2, "C", model.GrpPolicyNo);
                    excelApp.SetCellValue(i + 2, "D", model.GrpProductNo);
                    excelApp.SetCellValue(i + 2, "E", model.PolicyNo);
                    excelApp.SetCellValue(i + 2, "F", model.ProductNo);
                    excelApp.SetCellValue(i + 2, "G", model.GPFlag);
                    excelApp.SetCellValue(i + 2, "H", model.MainProductNo);
                    excelApp.SetCellValue(i + 2, "I", model.MainProductFlag);
                    excelApp.SetCellValue(i + 2, "J", model.ProductCode);
                    excelApp.SetCellValue(i + 2, "K", model.LiabilityCode);
                    excelApp.SetCellValue(i + 2, "L", model.LiabilityName);
                    excelApp.SetCellValue(i + 2, "M", model.Classification);
                    excelApp.SetCellValue(i + 2, "N", model.TermType);
                    excelApp.SetCellValue(i + 2, "O", model.ManageCom);
                    excelApp.SetCellValue(i + 2, "P", model.SignDate);
                    excelApp.SetCellValue(i + 2, "Q", model.EffDate);
                    excelApp.SetCellValue(i + 2, "R", model.PolYear);
                    excelApp.SetCellValue(i + 2, "S", model.InvalidDate);
                    excelApp.SetCellValue(i + 2, "T", model.UWConclusion);
                    excelApp.SetCellValue(i + 2, "U", model.PolStatus);
                    excelApp.SetCellValue(i + 2, "V", model.Status);
                    excelApp.SetCellValue(i + 2, "W", model.BasicSumInsured);
                    excelApp.SetCellValue(i + 2, "X", model.RiskAmnt);
                    excelApp.SetCellValue(i + 2, "Y", model.Premium);
                    excelApp.SetCellValue(i + 2, "Z", model.AccountValue);
                    excelApp.SetCellValue(i + 2, "AA", model.FacultativeFlag);
                    excelApp.SetCellValue(i + 2, "AB", model.AnonymousFlag);
                    excelApp.SetCellValue(i + 2, "AC", model.WaiverFlag);
                    excelApp.SetCellValue(i + 2, "AD", model.WaiverPrem);
                    excelApp.SetCellValue(i + 2, "AE", model.FinalCashValue);
                    excelApp.SetCellValue(i + 2, "AF", model.FinalLiabilityReserve);
                    excelApp.SetCellValue(i + 2, "AG", model.InsuredNo);
                    excelApp.SetCellValue(i + 2, "AH", model.InsuredName);
                    excelApp.SetCellValue(i + 2, "AI", model.InsuredSex);
                    excelApp.SetCellValue(i + 2, "AJ", model.InsuredCertType);
                    excelApp.SetCellValue(i + 2, "AK", model.InsuredCertNo);
                    excelApp.SetCellValue(i + 2, "AL", model.OccupationType);
                    excelApp.SetCellValue(i + 2, "AM", model.AppntAge);
                    excelApp.SetCellValue(i + 2, "AN", model.PreAge);
                    excelApp.SetCellValue(i + 2, "AO", model.ProfessionalFee);
                    excelApp.SetCellValue(i + 2, "AP", model.SubStandardFee);
                    excelApp.SetCellValue(i + 2, "AQ", model.EMRate);
                    excelApp.SetCellValue(i + 2, "AR", model.ProjectFlag);
                    excelApp.SetCellValue(i + 2, "AS", model.InsurePeoples);
                    excelApp.SetCellValue(i + 2, "AT", model.EndorAcceptNo);
                    excelApp.SetCellValue(i + 2, "AU", model.EndorsementNo);
                    excelApp.SetCellValue(i + 2, "AV", model.EdorType);
                    excelApp.SetCellValue(i + 2, "AW", model.EdorValiDate);
                    excelApp.SetCellValue(i + 2, "AX", model.EdorConfDate);
                    excelApp.SetCellValue(i + 2, "AY", model.EdorMoney);
                    excelApp.SetCellValue(i + 2, "AZ", model.SaparateFlag);
                    excelApp.SetCellValue(i + 2, "BA", model.ReInsuranceContNo);
                    excelApp.SetCellValue(i + 2, "BB", model.ReinsurerCode);
                    excelApp.SetCellValue(i + 2, "BC", model.ReinsurerName);
                    excelApp.SetCellValue(i + 2, "BD", model.ReinsurMode);
                    excelApp.SetCellValue(i + 2, "BE", model.QuotaSharePercentage);
                    excelApp.SetCellValue(i + 2, "BF", model.PreInsuredAge);
                    excelApp.SetCellValue(i + 2, "BG", model.PreBasicSumInsured);
                    excelApp.SetCellValue(i + 2, "BH", model.PreRiskAmnt);
                    excelApp.SetCellValue(i + 2, "BI", model.PreReinsuranceAmnt);
                    excelApp.SetCellValue(i + 2, "BJ", model.PreRetentionAmount);
                    excelApp.SetCellValue(i + 2, "BK", model.PrePremium);
                    excelApp.SetCellValue(i + 2, "BL", model.PreAccountValue);
                    excelApp.SetCellValue(i + 2, "BM", model.PreWaiverPrem);
                    excelApp.SetCellValue(i + 2, "BN", model.ProjectAcreageChange);
                    excelApp.SetCellValue(i + 2, "BO", model.ProjectCostChange);
                    excelApp.SetCellValue(i + 2, "BP", model.ReinsuranceAmntChange);
                    excelApp.SetCellValue(i + 2, "BQ", model.RetentionAmount);
                    excelApp.SetCellValue(i + 2, "BR", model.ReinsurancePremiumChange);
                    excelApp.SetCellValue(i + 2, "BS", model.ReinsuranceCommssionChange);
                    excelApp.SetCellValue(i + 2, "BT", model.Currency);
                    excelApp.SetCellValue(i + 2, "BU", model.ReComputationsDate);
                    excelApp.SetCellValue(i + 2, "BV", model.AccountGetDate);
                }
                excelApp.SetSheetAutoFit("Sheet1");
                excelApp.Save();
                excelApp.Close();
                ProcessLogProxy.SuccessMessage("Build Success");
            }
            catch (Exception ex)
            {
                ProcessLogProxy.Error(ex.Message);
                ProcessLogProxy.Error("Build fail");
            }
        }
Exemple #4
0
        public void WriteLRContSheet(ContractInfoBusiness contractInfoBusiness,
                                     string OutPutFolderPath, string dateyyyymm)
        {
            IExcel excelApp = new ExcelCore.ExcelCore();

            try
            {
                ProcessLogProxy.Normal("Start building LRCont excel");
                var           excelPath     = OutPutFolderPath + @"\TEMP_" + ExcelTemplateName.LRCont + ".xlsx";
                ExcelTemplate excelTemplate = new ExcelTemplate();
                excelTemplate.CreateTemplate(excelApp, excelPath, ExcelTemplateName.LRCont);//创建模板
                int serialNumber = 1;
                SetGroupDataToModel(contractInfoBusiness, dateyyyymm, ref serialNumber);
                SetIndividualDataToModel(contractInfoBusiness, dateyyyymm, ref serialNumber);
                excelApp.OpenExcel(excelPath, false);
                for (int i = 0; i < lRContModelList.Count; i++)
                {
                    var model = lRContModelList[i];

                    excelApp.SetCellValue(i + 2, "A", model.TransactionNo);
                    excelApp.SetCellValue(i + 2, "B", model.CompanyCode);
                    excelApp.SetCellValue(i + 2, "C", model.GrpPolicyNo);

                    if (model.IsGroup && string.IsNullOrWhiteSpace(model.GrpProductNo))
                    {
                        Color errorBackGroudColor = Color.Yellow;
                        excelApp.SetCellBackgroundColor(i + 2, 4, errorBackGroudColor);
                    }
                    else
                    {
                        excelApp.SetCellValue(i + 2, "D", model.GrpProductNo);
                    }

                    excelApp.SetCellValue(i + 2, "E", model.PolicyNo);
                    excelApp.SetCellValue(i + 2, "F", model.ProductNo);
                    excelApp.SetCellValue(i + 2, "G", model.GPFlag);
                    excelApp.SetCellValue(i + 2, "H", model.MainProductNo);
                    excelApp.SetCellValue(i + 2, "I", model.MainProductFlag);
                    excelApp.SetCellValue(i + 2, "J", model.ProductCode);
                    excelApp.SetCellValue(i + 2, "K", model.LiabilityCode);
                    excelApp.SetCellValue(i + 2, "L", model.LiabilityName);
                    excelApp.SetCellValue(i + 2, "M", model.Classification);
                    excelApp.SetCellValue(i + 2, "N", model.EventType);
                    excelApp.SetCellValue(i + 2, "O", model.RenewalTimes);
                    excelApp.SetCellValue(i + 2, "P", model.TermType);
                    excelApp.SetCellValue(i + 2, "Q", model.ManageCom);
                    excelApp.SetCellValue(i + 2, "R", model.SignDate);
                    excelApp.SetCellValue(i + 2, "S", model.EffDate);
                    excelApp.SetCellValue(i + 2, "T", model.PolYear);
                    excelApp.SetCellValue(i + 2, "U", model.InvalidDate);
                    excelApp.SetCellValue(i + 2, "V", model.UWConclusion);
                    excelApp.SetCellValue(i + 2, "W", model.PolStatus);
                    excelApp.SetCellValue(i + 2, "X", model.Status);
                    excelApp.SetCellValue(i + 2, "Y", model.BasicSumInsured);
                    excelApp.SetCellValue(i + 2, "Z", model.RiskAmnt);
                    excelApp.SetCellValue(i + 2, "AA", model.Premium);
                    excelApp.SetCellValue(i + 2, "AB", model.AccountValue);
                    excelApp.SetCellValue(i + 2, "AC", model.FacultativeFlag);
                    excelApp.SetCellValue(i + 2, "AD", model.AnonymousFlag);
                    excelApp.SetCellValue(i + 2, "AE", model.WaiverFlag);
                    excelApp.SetCellValue(i + 2, "AF", model.WaiverPrem);
                    excelApp.SetCellValue(i + 2, "AG", model.FinalCashValue);
                    excelApp.SetCellValue(i + 2, "AH", model.FinalLiabilityReserve);
                    excelApp.SetCellValue(i + 2, "AI", model.InsuredNo);
                    excelApp.SetCellValue(i + 2, "AJ", model.InsuredName);
                    excelApp.SetCellValue(i + 2, "AK", model.InsuredSex);
                    excelApp.SetCellValue(i + 2, "AL", model.InsuredCertType);
                    excelApp.SetCellValue(i + 2, "AM", model.InsuredCertNo);
                    excelApp.SetCellValue(i + 2, "AN", model.OccupationType);
                    excelApp.SetCellValue(i + 2, "AO", model.AppntAge);
                    excelApp.SetCellValue(i + 2, "AP", model.PreAge);
                    excelApp.SetCellValue(i + 2, "AQ", model.ProfessionalFee);
                    excelApp.SetCellValue(i + 2, "AR", model.SubStandardFee);
                    excelApp.SetCellValue(i + 2, "AS", model.EMRate);
                    excelApp.SetCellValue(i + 2, "AT", model.ProjectFlag);
                    excelApp.SetCellValue(i + 2, "AU", model.InsurePeoples);
                    excelApp.SetCellValue(i + 2, "AV", model.SaparateFlag);
                    excelApp.SetCellValue(i + 2, "AW", model.ReInsuranceContNo);
                    excelApp.SetCellValue(i + 2, "AX", model.ReinsurerCode);
                    excelApp.SetCellValue(i + 2, "AY", model.ReinsurerName);
                    excelApp.SetCellValue(i + 2, "AZ", model.ReinsurMode);
                    excelApp.SetCellValue(i + 2, "BA", model.ReinsuranceAmnt);
                    excelApp.SetCellValue(i + 2, "BB", model.RetentionAmount);
                    excelApp.SetCellValue(i + 2, "BC", model.Currency);
                    excelApp.SetCellValue(i + 2, "BD", model.QuotaSharePercentage);
                    excelApp.SetCellValue(i + 2, "BE", model.ReinsurancePremium);
                    excelApp.SetCellValue(i + 2, "BF", model.ReinsuranceCommssion);
                    excelApp.SetCellValue(i + 2, "BG", model.ReComputationsDate);
                    excelApp.SetCellValue(i + 2, "BH", model.AccountGetDate);
                }
                excelApp.SetSheetAutoFit("Sheet1");
                excelApp.Save();
                excelApp.Close();
                ProcessLogProxy.SuccessMessage("Build Success");
            }
            catch (Exception ex)
            {
                ProcessLogProxy.Error(ex.Message);
                ProcessLogProxy.Error("Build fail");
            }
        }