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); } }