public override string GenerateExcelFile(DataTable dt)
{
BillingUnit summaryUnit1 = summaryUnits[0];
BillingUnit summaryUnit2 = summaryUnits[1];
//Complete code rewrite is needed in here
//The problem stems form Material JE has two summary rows
//The SummaryUnit and SummaryUnit2 are shifted due to Credit account # sorting.
DataView dv = dt.DefaultView;
dv.Sort = "CreditAccount ASC, ItemDescription ASC, ProjectGrant ASC";
string lastCreditAccount = "default";
//Contruct the excel object
string fileName = Utility.GetRequiredAppSetting("SUB_Template");
string templatePath = HttpContext.Current.Server.MapPath($".\\SpreadSheets\\Templates\\{fileName}");
string workPathDir = HttpContext.Current.Server.MapPath(".\\SpreadSheets\\Work");
using (var mgr = ExcelUtility.NewExcelManager())
{
mgr.OpenWorkbook(templatePath);
mgr.SetActiveWorksheet("Sheet1");
int iRow = 1;
int iRowNumber2 = 0; //this keep the last row of the first portion of Store SUB, needed this to formulate correct formula for total sum cell
foreach (DataRowView drv in dv)
{
string creditAccount = drv["CreditAccount"].ToString();
if (creditAccount != lastCreditAccount && lastCreditAccount != "default" && summaryUnit2 != null)
{
mgr.SetCellTextValue(iRow, 0, summaryUnit1.CardType);
mgr.SetCellTextValue(iRow, 1, summaryUnit1.ShortCode);
mgr.SetCellTextValue(iRow, 2, summaryUnit1.Account);
mgr.SetCellTextValue(iRow, 3, summaryUnit1.FundCode);
mgr.SetCellTextValue(iRow, 4, summaryUnit1.DeptID);
mgr.SetCellTextValue(iRow, 5, summaryUnit1.ProgramCode);
mgr.SetCellTextValue(iRow, 6, summaryUnit1.ClassName);
mgr.SetCellTextValue(iRow, 7, summaryUnit1.ProjectGrant);
mgr.SetCellTextValue(iRow, 9, summaryUnit1.InvoiceDate);
mgr.SetCellTextValue(iRow, 11, summaryUnit1.Uniqname);
mgr.SetCellTextValue(iRow, 18, summaryUnit1.ItemDescription);
mgr.SetCellTextValue(iRow, 24, summaryUnit1.QuantityVouchered);
mgr.SetCellFormula(iRow, 27, string.Format("=-SUM(AB2:AB{0})", iRow));
iRow += 1;
iRowNumber2 = iRow + 1;
}
mgr.SetCellTextValue(iRow, 0, drv["CardType"]);
mgr.SetCellTextValue(iRow, 1, drv["ShortCode"]);
mgr.SetCellTextValue(iRow, 2, drv["Account"]);
mgr.SetCellTextValue(iRow, 3, drv["FundCode"]);
mgr.SetCellTextValue(iRow, 4, drv["DeptID"]);
mgr.SetCellTextValue(iRow, 5, drv["ProgramCode"]);
mgr.SetCellTextValue(iRow, 6, drv["Class"]);
mgr.SetCellTextValue(iRow, 7, drv["ProjectGrant"]);
mgr.SetCellTextValue(iRow, 8, drv["VendorID"]);
mgr.SetCellTextValue(iRow, 9, drv["InvoiceDate"]);
mgr.SetCellTextValue(iRow, 10, drv["InvoiceID"]);
string uniqName = drv["Uniqname"].ToString();
if (uniqName.Length > 8)
{
uniqName = uniqName.Substring(0, 8);
}
mgr.SetCellTextValue(iRow, 11, uniqName);
mgr.SetCellTextValue(iRow, 15, drv["DepartmentalReferenceNumber"]);
mgr.SetCellTextValue(iRow, 18, drv["ItemDescription"]);
mgr.SetCellTextValue(iRow, 24, drv["QuantityVouchered"]);
mgr.SetCellTextValue(iRow, 26, Convert.ToDouble(drv["UnitOfMeasure"]));
mgr.SetCellTextValue(iRow, 27, Convert.ToDouble(drv["MerchandiseAmount"]));
iRow += 1;
lastCreditAccount = creditAccount;
}
//Add the last row - which is the summary unit
mgr.SetCellTextValue(iRow, 0, summaryUnit2.CardType);
mgr.SetCellTextValue(iRow, 1, summaryUnit2.ShortCode);
mgr.SetCellTextValue(iRow, 2, summaryUnit2.Account);
mgr.SetCellTextValue(iRow, 3, summaryUnit2.FundCode);
mgr.SetCellTextValue(iRow, 4, summaryUnit2.DeptID);
mgr.SetCellTextValue(iRow, 5, summaryUnit2.ProgramCode);
mgr.SetCellTextValue(iRow, 6, summaryUnit2.ClassName);
mgr.SetCellTextValue(iRow, 7, summaryUnit2.ProjectGrant);
mgr.SetCellTextValue(iRow, 9, summaryUnit2.InvoiceDate);
mgr.SetCellTextValue(iRow, 11, summaryUnit2.Uniqname);
mgr.SetCellTextValue(iRow, 18, summaryUnit2.ItemDescription);
mgr.SetCellTextValue(iRow, 24, summaryUnit2.QuantityVouchered);
mgr.SetCellFormula(iRow, 27, string.Format("=-SUM(AB{0}:AB{1})", iRowNumber2, iRow));
mgr.SetColumnCollapsed("I", true);
mgr.SetColumnCollapsed("J", true);
mgr.SetColumnWidth(10, 1);
string workFilePath = workPathDir + "\\" + "StoreSUB" + "_";
if (EndPeriod == StartPeriod.AddMonths(1))
{
workFilePath += StartPeriod.ToString("yyyy-MM") + Path.GetExtension(fileName);
}
else
{
workFilePath += StartPeriod.ToString("yyyy-MM") + "_" + EndPeriod.ToString("yyyy-MM") + Path.GetExtension(fileName);
}
mgr.SaveAs(workFilePath);
return(workFilePath);
}
}
