Mon Nov 07, 2016 1:22 pm
I have attached the template file I use, which is version 2007.
i have references to Spire.License and Spire.XLS.Wpf (file version 7.11.1.7240)
these are the using statements in use;
using Spire.Xls;
using Spire.Xls.Core;
here is my code
private void PopulateIDCSheet(string idcFileName, IDCHeaderInfo headerInfo, List<DisbursalRecords> lstDisbursalRecords )
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(idcFileName,ExcelVersion.Version2007);
Worksheet mySheet = workbook.Worksheets[0];
mySheet.Range["A1"].Value = headerInfo.ProjectName;
mySheet.Range["F3"].Value = headerInfo.ContractNumber;
mySheet.Range["F4"].Value2 = headerInfo.LoanApprovalAmount.Value;
mySheet.Range["F5"].Value2 = headerInfo.LoanAmount.Value;
mySheet.Range["F6"].Value2 = headerInfo.LoanServiceFeeRate.Value;
if (headerInfo.LoanServiceFeeRate == null)
{
mySheet.Range["F7"].Value2 = headerInfo.LoanServiceFee.Value;
}
else
{
mySheet.Range["F7"].Formula = "=F5*F6";
}
mySheet.Range["F9"].Value2 = headerInfo.InterestRate.Value;
mySheet.Range["F10"].Value2 = headerInfo.ContractTermYears;
mySheet.Range["F12"].DateTimeValue = headerInfo.IDCCalculationDate.Value;
if(headerInfo.SubstantialCompletion == null)
{
mySheet.Range["D13"].Value = "Open";
}
else
{
mySheet.Range["D13"].DateTimeValue = headerInfo.SubstantialCompletion.Value;
}
mySheet.Range["F12"].DateTimeValue = headerInfo.IDCCalculationDate.Value;
mySheet.Range["H13"].DateTimeValue = headerInfo.ContractExpirationDate.Value;
//txtBoxBorrower
ITextBox myTextBox = mySheet.TextBoxes[0];
StringBuilder sb = new StringBuilder();
sb.AppendLine(headerInfo.BorrowerName);
sb.AppendLine(headerInfo.BorrowerAddressLine1);
if (!string.IsNullOrEmpty(headerInfo.BorrowerAddressLine2))
{
sb.AppendLine(headerInfo.BorrowerAddressLine2);
}
sb.AppendLine(headerInfo.BorrowerCity + ", " + headerInfo.BorrowerState + " " + headerInfo.BorrowerZip);
if (!string.IsNullOrEmpty(headerInfo.Contact))
{
sb.AppendLine("c/o " + headerInfo.Contact);
}
sb.AppendLine();
sb.AppendLine("Ph. " + headerInfo.BorrowerPhone);
sb.AppendLine("Fax " + headerInfo.BorrowerFax);
sb.AppendLine(headerInfo.BorrowerEmail);
myTextBox.Text = sb.ToString();
//first row
mySheet.Range["F15"].Formula = "=F5";
int cRow = 0;
int iRow = 16;
if(lstDisbursalRecords.Count > 0)
{
//add a line for every record
for(int i = 0;i < lstDisbursalRecords.Count; i++)
{
cRow = iRow + i;
mySheet.Range[cRow, 1].Value = (i + 1).ToString();
mySheet.Range[cRow, 2].DateTimeValue = lstDisbursalRecords[i].DisbursedDate;
mySheet.Range[cRow, 3].Value = lstDisbursalRecords[i].DisbursalDescription;
mySheet.Range[cRow, 4].Value2= lstDisbursalRecords[i].DisbursedAmount;
mySheet.Range[cRow, 5].Formula = "=E" + (cRow - 1).ToString() + "+" + "D" + cRow.ToString();//cumulative loan balance
mySheet.Range[cRow, 9].Formula = "=E" + cRow.ToString() + @"/$F$15";//%complete
mySheet.Range[cRow, 10].Formula = "=$F$12-B" + cRow.ToString() ; //days
mySheet.Range[cRow, 6].Formula = "=$F$15-E" + cRow.ToString(); //balance available to loan
mySheet.Range[cRow, 7].Formula = "=D" + cRow.ToString() + "*$F$9*(J" + cRow.ToString() + @"/365)";//interest
mySheet.Range[cRow, 8].Formula = "=H" + (cRow - 1).ToString() + "+" + "G" + cRow.ToString();
mySheet.Range[cRow, 1, cRow, 8].Borders.LineStyle = LineStyleType.Thin;
mySheet.Range[cRow, 1, cRow, 8].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
mySheet.Range[cRow, 1, cRow, 8].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
mySheet.Range[cRow, 1, cRow, 8].Borders.Color = System.Drawing.Color.Black;
}
}
workbook.CalculateAllValue();
workbook.SaveToFile(idcFileName, ExcelVersion.Version2007);
}
- Attachments
-
- IDC Template.zip
- this is zipped version of the xlsx template file i use to create new files
- (12.42 KiB) Downloaded 295 times