public void WriteLRInsureContSheet()
{
try
{
string path = @"C:\Users\Administrator\Desktop\template\template\yyyymm\output";
ExcelTemplate excelTemplate = new ExcelTemplate();
IExcel excelApp = new ExcelCore.ExcelCore();
excelTemplate.CreateTemplate(excelApp, path + @"\TEMP_" + ExcelTemplateName.LRProduct + ".xlsx", ExcelTemplateName.LRProduct);
excelTemplate.CreateTemplate(excelApp, path + @"\TEMP_" + ExcelTemplateName.LRInsureCont + ".xlsx", ExcelTemplateName.LRInsureCont);
excelTemplate.CreateTemplate(excelApp, path + @"\TEMP_" + ExcelTemplateName.LRAccount + ".xlsx", ExcelTemplateName.LRAccount);
excelTemplate.CreateTemplate(excelApp, path + @"\TEMP_" + ExcelTemplateName.LJInvoiceRelation + ".xlsx", ExcelTemplateName.LJInvoiceRelation);
excelTemplate.CreateTemplate(excelApp, path + @"\TEMP_" + ExcelTemplateName.LJInvoice + ".xlsx", ExcelTemplateName.LJInvoice);
LRInsureCont lRInsureCont = new LRInsureCont();
ContractInfoBusiness contractInfoBusiness = new ContractInfoBusiness();
string TEMP_LMLiabilityInfoExcelPath = @"C:\Users\Administrator\Desktop\template\template\inputPath2";
contractInfoBusiness.GetInformationDataFromExcel(TEMP_LMLiabilityInfoExcelPath, @"C:\Users\Administrator\Desktop\template\template\yyyymm");
lRInsureCont.WriteLRInsureContSheet(contractInfoBusiness, path + @"\TEMP_" + ExcelTemplateName.LRInsureCont + ".xlsx", "20170131");
LRAccount lRAccount = new LRAccount();
lRAccount.WriteLRAccountSheet(contractInfoBusiness, path + @"\TEMP_" + ExcelTemplateName.LRAccount + ".xlsx", "201701");
}
catch (Exception ex)
{
throw;
}
}
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");
}
}
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");
}
}
public void CreateTemplateTest()
{
try
{
string path = @"C:\Users\Administrator\Desktop\aaaa";
ExcelTemplate lrProduct = new ExcelTemplate();
IExcel excelApp = new ExcelCore.ExcelCore();
lrProduct.CreateTemplate(excelApp, path + @"\TEMP_" + ExcelTemplateName.LRProduct + ".xlsx", ExcelTemplateName.LRProduct);
lrProduct.CreateTemplate(excelApp, path + @"\TEMP_" + ExcelTemplateName.LRInsureCont + ".xlsx", ExcelTemplateName.LRInsureCont);
lrProduct.CreateTemplate(excelApp, path + @"\TEMP_" + ExcelTemplateName.LRAccount + ".xlsx", ExcelTemplateName.LRAccount);
lrProduct.CreateTemplate(excelApp, path + @"\TEMP_" + ExcelTemplateName.LJInvoiceRelation + ".xlsx", ExcelTemplateName.LJInvoiceRelation);
lrProduct.CreateTemplate(excelApp, path + @"\TEMP_" + ExcelTemplateName.LJInvoice + ".xlsx", ExcelTemplateName.LJInvoice);
}
catch (Exception ex)
{
throw ex;
}
}
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");
}
}
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");
}
}
