int ir;//树布局类Lines的个数，树value的个数
/// <summary>
/// 读取json文件
/// </summary>
/// <param name="filename">json文件名</param>
/// <returns></returns>
FinProjection ReadFromJson(string filename)
{
FinProjection finsim = null;
using (StreamReader reader = File.OpenText(filename))
{
JsonSerializer serializer = JsonSerializer.Create();
finsim = serializer.Deserialize(reader, typeof(FinProjection)) as FinProjection;
}
return(finsim);
}
static void Main()
{
FileInfo newFile = new FileInfo(@"EAR.xlsx");
if (newFile.Exists)
{
newFile.Delete();
newFile = new FileInfo(@"EAR.xlsx");
}
string dir = Path.GetFullPath("../..");
string filePath = dir + "\\THC NII Report Template.xlsx";
FileInfo templateFile = new FileInfo(filePath);
using (ExcelPackage package = new ExcelPackage(templateFile))
{
//EaR summary
ExcelWorksheet shtES = package.Workbook.Worksheets["EaR summary"];
shtES.Cells[11, 4].Value = "Dn 200BP";
shtES.Cells[11, 5].Value = "Dn 100BP";
shtES.Cells[11, 6].Value = "Base";
shtES.Cells[11, 7].Value = "Up 100BP";
shtES.Cells[11, 8].Value = "Up 200BP";
shtES.Cells[11, 9].Value = "Up 300BP";
shtES.Cells[11, 10].Value = "Up 400BP ";
shtES.Cells[11, 11].Value = "Flattener";
shtES.Cells[11, 12].Value = "Ramp Up";
shtES.Cells[21, 4].Value = "Dn 200BP";
shtES.Cells[21, 5].Value = "Dn 100BP";
shtES.Cells[21, 6].Value = "Base";
shtES.Cells[21, 7].Value = "Up 100BP";
shtES.Cells[21, 8].Value = "Up 200BP";
shtES.Cells[21, 9].Value = "Up 300BP";
shtES.Cells[21, 10].Value = "Up 400BP ";
shtES.Cells[21, 11].Value = "Flattener";
shtES.Cells[21, 12].Value = "Ramp Up";
shtES.Cells[12, 4].Value = 18138;
shtES.Cells[12, 5].Value = 19032;
shtES.Cells[12, 6].Value = 20507;
shtES.Cells[12, 7].Value = 22503;
shtES.Cells[12, 8].Value = 24569;
shtES.Cells[12, 9].Value = 26659;
shtES.Cells[12, 10].Value = 28697;
shtES.Cells[12, 11].Value = 26756;
shtES.Cells[12, 12].Value = 20998;
shtES.Cells[14, 4].Value = -11.55;
shtES.Cells[14, 5].Value = -7.19;
shtES.Cells[14, 6].Value = 0.00;
shtES.Cells[14, 7].Value = 9.73;
shtES.Cells[14, 8].Value = 19.81;
shtES.Cells[14, 9].Value = 30.00;
shtES.Cells[14, 10].Value = 39.94;
shtES.Cells[14, 11].Value = 30.47;
shtES.Cells[14, 12].Value = 2.39;
shtES.Cells[24, 4].Value = -7.31;
shtES.Cells[24, 5].Value = -5.20;
shtES.Cells[24, 6].Value = 0.00;
shtES.Cells[24, 7].Value = 7.62;
shtES.Cells[24, 8].Value = 15.85;
shtES.Cells[24, 9].Value = 24.21;
shtES.Cells[24, 10].Value = 32.35;
shtES.Cells[24, 11].Value = 22.40;
shtES.Cells[24, 12].Value = 4.27;
//ExcelChart excelChart = shtES.Drawings.add
ExcelChart chart = shtES.Drawings.AddChart("chart", eChartType.ColumnClustered);
ExcelChartSerie serie = chart.Series.Add(shtES.Cells[12, 4, 12, 12], shtES.Cells[11, 4, 11, 12]);
var chartType = chart.PlotArea.ChartTypes.Add(eChartType.LineStacked);
chartType.UseSecondaryAxis = true;
ExcelChartSerie chartSerie = chartType.Series.Add(shtES.Cells[14, 2, 14, 12], shtES.Cells[11, 4, 11, 12]);
var charType2 = chart.PlotArea.ChartTypes.Add(eChartType.LineMarkers);
charType2.UseSecondaryAxis = true;
ExcelChartSerie excelChartSerie = charType2.Series.Add(shtES.Cells[24, 2, 24, 12], shtES.Cells[11, 4, 11, 12]);
chart.SetPosition(980, 155);
serie.HeaderAddress = shtES.Cells[11, 4];
chartSerie.HeaderAddress = shtES.Cells[11, 5];
excelChartSerie.HeaderAddress = shtES.Cells[11, 6];
//1st year projection
ExcelWorksheet sht1year = package.Workbook.Worksheets["1st year projection"];
sht1year.Cells.Style.Font.Name = "Calibri";
sht1year.Cells.Style.Border.BorderAround(ExcelBorderStyle.Medium);
Program pro = new Program();
FinProjection finProjection = pro.ReadFromJson("azf201906.json");
sht1year.Cells[7, 2].Value = "Scenario:Base Case";
sht1year.Cells[8, 2].Value = "ASSETS INTEREST INCOME";
sht1year.Cells[9, 2].Value = "LIABILITIES INTEREST COST";
sht1year.Cells[10, 2].Value = "NET INTEREST INCOME";
for (int i = 0; i <= 36; i++)
{
sht1year.Cells[8, i + 4].Value = finProjection.TotalLines.NetIncome.InterestIncome[i];
sht1year.Cells[9, i + 4].Value = finProjection.TotalLines.NetIncome.InterestCost[i];
sht1year.Cells[10, i + 4].Value = finProjection.TotalLines.NetIncome.NetInterestIncome[i];
double dblNIC = finProjection.TotalLines.NetIncome.NonInterestExpense.Value[i];
double dblNII = finProjection.TotalLines.NetIncome.NonInterestIncome.Value[i];
sht1year.Cells[11, i + 4].Value = dblNIC - dblNII;
sht1year.Cells[12, i + 4].Value = finProjection.TotalLines.NetIncome.LoanLossProvision[i];
sht1year.Cells[14, i + 4].Value = finProjection.TotalLines.NetIncome.TaxPayments[i];
sht1year.Cells[15, i + 4].Value = finProjection.TotalLines.NetIncome.NI[i];
sht1year.Cells[16, i + 4].Value = finProjection.TotalLines.NetIncome.DividendPayment[i];
sht1year.Cells[18, i + 4].Value = finProjection.TotalLines.NetIncome.THCNetChangeUnRealizedGain[i];
if (finProjection.TotalLines.Capital != null)
{
sht1year.Cells[19, i + 4].Value = finProjection.TotalLines.Capital.Equity[i];
}
}
for (int i = 11; i < 24; i++)
{
sht1year.Row(i).OutlineLevel = 1;
}
sht1year.Cells[11, 2].Value = "Non Interest Expense(income)";
sht1year.Cells[12, 2].Value = "Provision of losses";
sht1year.Cells[13, 2].Value = "Profit before taxes";
sht1year.Cells[14, 2].Value = "Tax";
sht1year.Cells[15, 2].Value = "Net Income";
sht1year.Cells[16, 2].Value = "Dividend Payment";
sht1year.Cells[17, 2].Value = "Retained Earning chg";
sht1year.Cells[18, 2].Value = "Unrealized G/L";
sht1year.Cells[6, 2].Value = "Date";
using (ExcelRange range = sht1year.Cells[7, 2, 10, 2])
{
range.Style.Font.Bold = true;
range.Style.Font.Name = "微软雅黑";
sht1year.Cells[7, 2].Value = "Scenario:Base Case";
sht1year.Cells[8, 2].Value = "ASSETS INTEREST INCOME";
sht1year.Cells[9, 2].Value = "LIABILITIES INTEREST COST";
sht1year.Cells[10, 2].Value = "NET INTEREST INCOME";
}
using (ExcelRange range = sht1year.Cells["B7"])
{
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));
}
sht1year.Cells[5, 15].Value = "Currency: USD .Amounts in 000s";
using (ExcelRange range = sht1year.Cells[11, 2, 18, 2])
{
range.Style.Font.Italic = true;
range.Style.Font.Name = "Calibri";
sht1year.Cells[11, 2].Value = "Non Interest Expense(income)";
sht1year.Cells[12, 2].Value = "Provision of losses";
sht1year.Cells[13, 2].Value = "Profit before taxes";
sht1year.Cells[14, 2].Value = "Tax";
sht1year.Cells[15, 2].Value = "Net Income";
sht1year.Cells[16, 2].Value = "Dividend Payment";
sht1year.Cells[17, 2].Value = "Retained Earning chg";
sht1year.Cells[18, 2].Value = "Unrealized G/L";
}
sht1year.Cells[19, 2].Value = "Equity";
sht1year.Cells[19, 2].Style.Font.Name = "Calibri";
sht1year.Cells[19, 2].Style.Font.Bold = true;
sht1year.Cells[19, 2].Style.Font.Italic = true;
sht1year.Cells[19, 2].Style.Font.Size = 11;
pro.PrepareSampleEaRLayoutSettings();
pro.WriteCOAData(sht1year, finProjection.COA, pro.settings, 21);//写入树的相关数据
package.SaveAs(newFile);
}
}
