public void InitClientData(Client clsClient) { DateTime StartPeriod; DateTime EndPeriod; string[] DateStuff = ServiceDate.Split('/'); ClientStuff.NoCommodity = clsClient.clsHH.NoCommodities; ClientStuff.SupplementalOnly = clsClient.clsHH.SupplOnly; ClientStuff.HaveCSFP = (clsClient.clsHH.NbrCSFP > 0); ClientStuff.FamilySize = clsClient.clsHH.TotalFamily; ClientStuff.Homeless = clsClient.clsHH.Homeless; ClientStuff.Transient = (clsClient.clsHH.ClientType == CCFBPrefs.TransientId); TrxLog trxLogWork = new TrxLog(CCFBGlobal.connectionString); //Nbr Service This Month StartPeriod = Convert.ToDateTime(DateStuff[0].ToString() + "/01/" + DateStuff[2].ToString()); EndPeriod = StartPeriod.AddMonths(1).AddDays(-1); trxLogWork.openUsingDateRange(clsClient.clsHH.ID, StartPeriod, EndPeriod); ClientStuff.NbrServicesThisMonth = trxLogWork.RowCount; //Nbr Services This Fiscal Year StartPeriod = CCFBGlobal.CalcFiscalStartDate(Convert.ToDateTime(ServiceDate)); EndPeriod = CCFBGlobal.CalcFiscalEndDate(Convert.ToDateTime(ServiceDate)); trxLogWork.openUsingDateRange(clsClient.clsHH.ID, StartPeriod, EndPeriod); ClientStuff.NbrServicesThisFiscalYr = trxLogWork.RowCount; //Nbr Services This Calendar Year if (CCFBPrefs.FiscalYearStartMonth != 1) { StartPeriod = Convert.ToDateTime("01/01/" + DateStuff[2].ToString()); EndPeriod = StartPeriod.AddYears(1).AddDays(-1); trxLogWork.openUsingDateRange(clsClient.clsHH.ID, StartPeriod, EndPeriod); } ClientStuff.NbrServicesThisCalYr = trxLogWork.RowCount; ClearSelected(); }
private void LoadGridJU(JournalUnitReportItem[] items, CreditEntry creditEntry) { gvJU.Columns[1].Visible = false; gvJU.Columns[2].Visible = false; gvJU.Columns[3].Visible = EndPeriod != StartPeriod.AddMonths(1); gvJU.DataSource = items; gvJU.DataBind(); SetTotalText(creditEntry.MerchandiseAmount); }
private void ProcessHtmlAllJU(JournalUnitTypes juType) { double total; List <JournalUnitReportItem> allItems; var factory = GetReportFactory(); switch (juType) { case JournalUnitTypes.A: case JournalUnitTypes.B: case JournalUnitTypes.C: RoomJU roomJU = factory.GetReportRoomJU(StartPeriod, EndPeriod, juType, GetClientID()); ToolJU toolJU = factory.GetReportToolJU(StartPeriod, EndPeriod, juType, GetClientID()); allItems = ReportFactory.GetAllJU(roomJU, toolJU, out total); break; case JournalUnitTypes.All: RoomJU roomJUA = factory.GetReportRoomJU(StartPeriod, EndPeriod, JournalUnitTypes.A, GetClientID()); ToolJU toolJUA = factory.GetReportToolJU(StartPeriod, EndPeriod, JournalUnitTypes.A, GetClientID()); RoomJU roomJUB = factory.GetReportRoomJU(StartPeriod, EndPeriod, JournalUnitTypes.B, GetClientID()); ToolJU toolJUB = factory.GetReportToolJU(StartPeriod, EndPeriod, JournalUnitTypes.B, GetClientID()); RoomJU roomJUC = factory.GetReportRoomJU(StartPeriod, EndPeriod, JournalUnitTypes.C, GetClientID()); ToolJU toolJUC = factory.GetReportToolJU(StartPeriod, EndPeriod, JournalUnitTypes.C, GetClientID()); double temp; total = 0; allItems = new List <JournalUnitReportItem>(); allItems.AddRange(ReportFactory.GetAllJU(roomJUA, toolJUA, out temp)); total += temp; allItems.AddRange(ReportFactory.GetAllJU(roomJUB, toolJUB, out temp)); total += temp; allItems.AddRange(ReportFactory.GetAllJU(roomJUC, toolJUC, out temp)); total += temp; break; default: throw new ArgumentException(string.Format("Invalid JournalUnitTypes value: {0}", juType)); } gvJU.Columns[1].Visible = true; gvJU.Columns[2].Visible = juType == JournalUnitTypes.All; gvJU.Columns[3].Visible = EndPeriod != StartPeriod.AddMonths(1); gvJU.DataSource = allItems; gvJU.DataBind(); SetTotalText(total); SetLinkText("ju-" + Enum.GetName(typeof(JournalUnitTypes), juType).ToLower(), "all"); }
private void ProcessHtmlAllSUB() { var factory = GetReportFactory(); RoomSUB roomSUB = factory.GetReportRoomSUB(StartPeriod, EndPeriod, GetClientID()); ToolSUB toolSUB = factory.GetReportToolSUB(StartPeriod, EndPeriod, GetClientID()); StoreSUB storeSUB = factory.GetReportStoreSUB(StartPeriod, EndPeriod, false, GetClientID()); IEnumerable <ServiceUnitBillingReportItem> allItems = ReportFactory.GetAllSUB(roomSUB, toolSUB, storeSUB, out double total); gvSUB.Columns[1].Visible = true; gvSUB.Columns[2].Visible = EndPeriod != StartPeriod.AddMonths(1); gvSUB.DataSource = allItems; gvSUB.DataBind(); SetTotalText(total); SetLinkText("sub", "all"); }
private void ProcessAllToolJUHTML() { var factory = GetReportFactory(); var juA = factory.GetReportToolJU(StartPeriod, EndPeriod, JournalUnitTypes.A, GetClientID()); var juB = factory.GetReportToolJU(StartPeriod, EndPeriod, JournalUnitTypes.B, GetClientID()); var juC = factory.GetReportToolJU(StartPeriod, EndPeriod, JournalUnitTypes.C, GetClientID()); List <JournalUnitReportItem> allItems = ReportFactory.GetAllToolJU(juA, juB, juC, out double total); gvJU.Columns[1].Visible = false; gvJU.Columns[2].Visible = true; gvJU.Columns[3].Visible = EndPeriod != StartPeriod.AddMonths(1); gvJU.DataSource = allItems; gvJU.DataBind(); SetTotalText(total); SetLinkText("ju-all", "tool"); }
private void LoadGridSUB(ServiceUnitBillingReportItem[][] items, BillingUnit[] summaries) { List <ServiceUnitBillingReportItem> allItems = new List <ServiceUnitBillingReportItem>(); foreach (var group in items) { allItems.AddRange(group); } double totalMerchAmount = 0; foreach (var bu in summaries) { totalMerchAmount += bu.MerchandiseAmount; } gvSUB.Columns[1].Visible = false; gvSUB.Columns[2].Visible = EndPeriod != StartPeriod.AddMonths(1); gvSUB.DataSource = allItems; gvSUB.DataBind(); SetTotalText(totalMerchAmount); }
//This class will generate a real excel file and return the file path of this newly generated excel file protected string GenerateExcel(DataTable dt, string JEType) { BillingUnit summaryUnit = summaryUnits.First(); //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"); DirectoryInfo di = new DirectoryInfo(workPathDir); try { //Determine whether the directory exists. if (!di.Exists) { di.Create(); } } catch (Exception ex) { return("Error - " + ex.Message); } using (var mgr = ExcelUtility.NewExcelManager()) { mgr.OpenWorkbook(templatePath); mgr.SetActiveWorksheet("Sheet1"); //We start at first row, because for ExcelLite control, the header row is not included int iRow = 1; DataView dv = dt.DefaultView; dv.Sort = "CreditAccount ASC, ItemDescription ASC, ProjectGrant ASC"; foreach (DataRowView drv in dv) { 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); //2009-01-20 Only 8 character is allowed mgr.SetCellTextValue(iRow, 15, drv["DepartmentalReferenceNumber"]); mgr.SetCellTextValue(iRow, 17, drv["ItemDescription"]); mgr.SetCellNumberValue(iRow, 23, drv["QuantityVouchered"]); mgr.SetCellNumberValue(iRow, 25, Convert.ToDouble(drv["UnitOfMeasure"])); mgr.SetCellNumberValue(iRow, 26, Convert.ToDouble(drv["MerchandiseAmount"])); iRow += 1; } //Add the last row - which is the summary unit mgr.SetCellTextValue(iRow, 0, summaryUnit.CardType); mgr.SetCellTextValue(iRow, 1, summaryUnit.ShortCode); mgr.SetCellTextValue(iRow, 2, summaryUnit.Account); mgr.SetCellTextValue(iRow, 3, summaryUnit.FundCode); mgr.SetCellTextValue(iRow, 4, summaryUnit.DeptID); mgr.SetCellTextValue(iRow, 5, summaryUnit.ProgramCode); mgr.SetCellTextValue(iRow, 6, summaryUnit.ClassName); mgr.SetCellTextValue(iRow, 7, summaryUnit.ProjectGrant); mgr.SetCellTextValue(iRow, 9, summaryUnit.InvoiceDate); mgr.SetCellTextValue(iRow, 11, summaryUnit.Uniqname); mgr.SetCellTextValue(iRow, 17, summaryUnit.ItemDescription); mgr.SetCellNumberValue(iRow, 23, "1.0000"); mgr.SetCellNumberValue(iRow, 25, summaryUnit.MerchandiseAmount.ToString() + "000"); mgr.SetCellFormula(iRow, 26, string.Format("=-SUM(AB2:AB{0})", iRow)); string workFilePath = workPathDir + "\\" + JEType; 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); } }
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); } }
//This got moved from SUBBase.vb: I can't seem to import the Billing.Reports namespace in vb App_Code files becuase it's in a cs file, but I can here. private string GenerateExcelSUB(ServiceUnitBillingReportItem[][] items, BillingUnit[] SummaryUnit, string ChargeType) { //Contruct the excel object string fileName = Utility.GetRequiredAppSetting("SUB_Template"); string templatePath = ExcelUtility.GetTemplatePath(fileName); string workPathDir = ExcelUtility.GetWorkPath(CurrentUser.ClientID); using (var mgr = ExcelUtility.NewExcelManager()) { mgr.OpenWorkbook(templatePath); mgr.SetActiveWorksheet("Sheet1"); int iRow = 1; //zero based, iRow = 0 is Row #1 on spreadsheet (the header) //dv.Sort = "ItemDescription ASC, ProjectGrant ASC"; foreach (ServiceUnitBillingReportItem item in items[0]) { if (!string.IsNullOrEmpty(item.CardType)) { mgr.SetCellTextValue(iRow, 0, item.CardType); mgr.SetCellTextValue(iRow, 1, item.ShortCode); mgr.SetCellTextValue(iRow, 2, item.Account); mgr.SetCellTextValue(iRow, 3, item.FundCode); mgr.SetCellTextValue(iRow, 4, item.DeptID); mgr.SetCellTextValue(iRow, 5, item.ProgramCode); mgr.SetCellTextValue(iRow, 6, item.Class); mgr.SetCellTextValue(iRow, 7, item.ProjectGrant); mgr.SetCellTextValue(iRow, 8, item.VendorID); mgr.SetCellTextValue(iRow, 9, item.InvoiceDate); mgr.SetCellTextValue(iRow, 10, item.InvoiceID); string uniqName = item.Uniqname.ToString(); if (uniqName.Length > 8) { uniqName = uniqName.Substring(0, 8); } mgr.SetCellTextValue(iRow, 11, uniqName); mgr.SetCellTextValue(iRow, 15, item.DepartmentalReferenceNumber); //R = 17 //[2014-09-08 jg] All column S and higher are now shifted one to the left so 18 -> 17, 19 -> 18, etc //ws.Cell[iRow, 18].Value = item.ItemDescription; //ws.Cell[iRow, 24].Value = item.QuantityVouchered; //ws.Cell[iRow, 26].Value = string.IsNullOrEmpty(item.UnitOfMeasure) ? 0 : Convert.ToDouble(item.UnitOfMeasure); //ws.Cell[iRow, 27].Value = string.IsNullOrEmpty(item.MerchandiseAmount) ? 0 : Convert.ToDouble(item.MerchandiseAmount); mgr.SetCellTextValue(iRow, 17, item.ItemDescription); mgr.SetCellNumberValue(iRow, 23, item.QuantityVouchered); mgr.SetCellNumberValue(iRow, 25, string.IsNullOrEmpty(item.UnitOfMeasure) ? 0 : Convert.ToDouble(item.UnitOfMeasure)); mgr.SetCellNumberValue(iRow, 26, string.IsNullOrEmpty(item.MerchandiseAmount) ? 0 : Convert.ToDouble(item.MerchandiseAmount)); iRow += 1; } } mgr.SetCellTextValue(iRow, 0, SummaryUnit[0].CardType); mgr.SetCellTextValue(iRow, 1, SummaryUnit[0].ShortCode); mgr.SetCellTextValue(iRow, 2, SummaryUnit[0].Account); mgr.SetCellTextValue(iRow, 3, SummaryUnit[0].FundCode); mgr.SetCellTextValue(iRow, 4, SummaryUnit[0].DeptID); mgr.SetCellTextValue(iRow, 5, SummaryUnit[0].ProgramCode); mgr.SetCellTextValue(iRow, 6, SummaryUnit[0].ClassName); mgr.SetCellTextValue(iRow, 7, SummaryUnit[0].ProjectGrant); mgr.SetCellTextValue(iRow, 9, SummaryUnit[0].InvoiceDate); mgr.SetCellTextValue(iRow, 11, SummaryUnit[0].Uniqname); //ws.Cells[iRow, 18].Value = SummaryUnit[0].ItemDescription; //ws.Cells[iRow, 24].Value = SummaryUnit[0].QuantityVouchered; //ws.Cells[iRow, 26].Value = Math.Round(SummaryUnit[0].MerchandiseAmount, 5); //ws.Cells[iRow, 27].Formula = string.Format("=-SUM(AB2:AB{0})", iRow); mgr.SetCellTextValue(iRow, 17, SummaryUnit[0].ItemDescription); mgr.SetCellNumberValue(iRow, 23, SummaryUnit[0].QuantityVouchered); mgr.SetCellNumberValue(iRow, 25, Math.Round(SummaryUnit[0].MerchandiseAmount, 5)); mgr.SetCellFormula(iRow, 26, string.Format("=-SUM(AB2:AB{0})", iRow)); if (items.Length > 1 && SummaryUnit.Length > 1) { iRow += 1; int startingRowNumber = iRow + 1; //we use excel formula to calculate the sum, so we have to keep this to get the starting range //dv.Sort = "ItemDescription ASC, ProjectGrant ASC"; foreach (ServiceUnitBillingReportItem item in items[1]) { if (!string.IsNullOrEmpty(item.CardType)) { mgr.SetCellTextValue(iRow, 0, item.CardType); mgr.SetCellTextValue(iRow, 1, item.ShortCode); mgr.SetCellTextValue(iRow, 2, item.Account); mgr.SetCellTextValue(iRow, 3, item.FundCode); mgr.SetCellTextValue(iRow, 4, item.DeptID); mgr.SetCellTextValue(iRow, 5, item.ProgramCode); mgr.SetCellTextValue(iRow, 6, item.Class); mgr.SetCellTextValue(iRow, 7, item.ProjectGrant); mgr.SetCellTextValue(iRow, 8, item.VendorID); mgr.SetCellTextValue(iRow, 9, item.InvoiceDate); mgr.SetCellTextValue(iRow, 10, item.InvoiceID); string uniqName = item.Uniqname; if (uniqName.Length > 8) { uniqName = uniqName.Substring(0, 8); } mgr.SetCellTextValue(iRow, 11, uniqName); mgr.SetCellTextValue(iRow, 15, item.DepartmentalReferenceNumber); //ws.Cells[iRow, 18].Value = item.ItemDescription; //ws.Cells[iRow, 24].Value = item.QuantityVouchered; //ws.Cells[iRow, 26].Value = string.IsNullOrEmpty(item.UnitOfMeasure) ? 0 : Convert.ToDouble(item.UnitOfMeasure); //ws.Cells[iRow, 27].Value = string.IsNullOrEmpty(item.MerchandiseAmount) ? 0 : Convert.ToDouble(item.MerchandiseAmount); mgr.SetCellTextValue(iRow, 17, item.ItemDescription); mgr.SetCellNumberValue(iRow, 23, item.QuantityVouchered); mgr.SetCellNumberValue(iRow, 25, string.IsNullOrEmpty(item.UnitOfMeasure) ? 0 : Convert.ToDouble(item.UnitOfMeasure)); mgr.SetCellNumberValue(iRow, 26, string.IsNullOrEmpty(item.MerchandiseAmount) ? 0 : Convert.ToDouble(item.MerchandiseAmount)); iRow += 1; } } mgr.SetCellTextValue(iRow, 0, SummaryUnit[1].CardType); mgr.SetCellTextValue(iRow, 1, SummaryUnit[1].ShortCode); mgr.SetCellTextValue(iRow, 2, SummaryUnit[1].Account); mgr.SetCellTextValue(iRow, 3, SummaryUnit[1].FundCode); mgr.SetCellTextValue(iRow, 4, SummaryUnit[1].DeptID); mgr.SetCellTextValue(iRow, 5, SummaryUnit[1].ProgramCode); mgr.SetCellTextValue(iRow, 6, SummaryUnit[1].ClassName); mgr.SetCellTextValue(iRow, 7, SummaryUnit[1].ProjectGrant); mgr.SetCellTextValue(iRow, 9, SummaryUnit[1].InvoiceDate); mgr.SetCellTextValue(iRow, 11, SummaryUnit[1].Uniqname); //ws.Cells[iRow, 18].Value = SummaryUnit[1].ItemDescription; //ws.Cells[iRow, 24].Value = SummaryUnit[1].QuantityVouchered; //ws.Cells[iRow, 26].Value = Math.Round(SummaryUnit[0].MerchandiseAmount, 5); //ws.Cells[iRow, 27].Formula = string.Format("=-SUM(AB{0}:AB{1})", startingRowNumber, iRow); mgr.SetCellTextValue(iRow, 17, SummaryUnit[1].ItemDescription); mgr.SetCellNumberValue(iRow, 23, SummaryUnit[1].QuantityVouchered); mgr.SetCellNumberValue(iRow, 25, Math.Round(SummaryUnit[0].MerchandiseAmount, 5)); mgr.SetCellFormula(iRow, 26, string.Format("=-SUM(AB{0}:AB{1})", startingRowNumber, iRow)); mgr.SetColumnCollapsed("I", true); mgr.SetColumnCollapsed("J", true); mgr.SetColumnWidth(10, 1); } if (!Directory.Exists(workPathDir)) { Directory.CreateDirectory(workPathDir); } // the sub number string workFilePath = Path.Combine(workPathDir, ChargeType + "SUB_" + StartPeriod.ToString("yyyy-MM")); if (EndPeriod == StartPeriod.AddMonths(1)) { workFilePath += Path.GetExtension(fileName); } else { workFilePath += "_" + EndPeriod.AddMonths(-1).ToString("yyyy-MM") + Path.GetExtension(fileName); } mgr.SaveAs(workFilePath); return(workFilePath); } }