//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); } }
protected override void FillDataTable(DataTable dt) { BillingUnit summaryUnit1 = summaryUnits[0]; BillingUnit summaryUnit2 = summaryUnits[1]; Compile mCompile = new Compile(); //Get Cleints who order items in store in the Period with Credit and Debit and TotalCost calculated DataTable dtStoreDB = mCompile.CalcCost("StoreJE", string.Empty, string.Empty, 0, EndPeriod.AddMonths(-1), 0, 0, Compile.AggType.CliAcct); //Return dtStoreDB //DataTable dtStore = new DataTable(); //BuildDataTable(dtStore); DataTable dtClient = ClientDA.GetAllClient(StartPeriod, EndPeriod); DataTable dtAccount = AccountDA.GetAllInternalAccount(StartPeriod, EndPeriod); DataTable dtClientAccount = ClientDA.GetAllClientAccountWithManagerName(StartPeriod, EndPeriod); //used to find out manager's name //Get the general lab account ID and lab credit account ID GlobalCost gc = GlobalCostDA.GetGlobalCost(); //For performance issue, we have to calculate something first, since it's used on all rows string depRefNum = string.Empty; double fTotal = 0; string lastCreditAccount = "default"; string creditAccount = string.Empty; string creditAccountShortCode = string.Empty; //we also have to show those credit accounts' shortcodes string lastCreditAccountShortCode = string.Empty; //we need this, just like we need 'LastCreditAccount' to track the changes AccountNumber creditAccountNum; DataView dv = dtStoreDB.DefaultView; dv.Sort = "CreditAccountID"; //This for loop will loop through each transaction record and create SUB record on every transactional record foreach (DataRowView sdr in dv) { //do not show an item if the charge and xcharge accounts are the 'same' - can only happen for 941975 if (!(Convert.ToInt32(sdr["DebitAccountID"]) == gc.LabAccountID && Convert.ToInt32(sdr["CreditAccountID"]) == gc.LabCreditAccountID)) { DataRow ndr = dt.NewRow(); DataRow drAccount = dtAccount.Rows.Find(Convert.ToInt32(sdr["DebitAccountID"])); string debitAccount = string.Empty; string shortCode = string.Empty; if (drAccount == null) { debitAccount = string.Format("unknown_{0}", sdr["DebitAccountID"]); shortCode = string.Format("unknown_{0}", sdr["DebitAccountID"]); } else { debitAccount = drAccount["Number"].ToString(); shortCode = drAccount["ShortCode"].ToString(); } //get manager's name DataRow[] drClientAccount = dtClientAccount.Select(string.Format("AccountID = {0}", sdr["DebitAccountID"])); if (drClientAccount.Length > 0) { depRefNum = drClientAccount[0]["ManagerName"].ToString(); } else { depRefNum = "No Manager Found"; } ndr["CardType"] = 1; ndr["ShortCode"] = shortCode; AccountNumber debitAccountNum = AccountNumber.Parse(debitAccount); ndr["Account"] = debitAccountNum.Account; ndr["FundCode"] = debitAccountNum.FundCode; ndr["DeptID"] = debitAccountNum.DeptID; ndr["ProgramCode"] = debitAccountNum.ProgramCode; ndr["Class"] = debitAccountNum.Class; ndr["ProjectGrant"] = debitAccountNum.ProjectGrant; ndr["InvoiceDate"] = StartPeriod.ToString("yyyy/MM/dd"); ndr["InvoiceID"] = $"{ReportSettings.CompanyName} Store Charge"; ndr["Uniqname"] = dtClient.Rows.Find(Convert.ToInt32(sdr["ClientID"]))["UserName"]; ndr["DepartmentalReferenceNumber"] = depRefNum; ndr["ItemDescription"] = dtClient.Rows.Find(Convert.ToInt32(sdr["ClientID"]))["DisplayName"].ToString().Substring(0, 30); ndr["QuantityVouchered"] = "1.0000"; double chargeAmount = Math.Round(Convert.ToDouble(sdr["TotalCalcCost"]), 5); ndr["UnitOfMeasure"] = chargeAmount; ndr["MerchandiseAmount"] = Math.Round(chargeAmount, 2); creditAccount = dtAccount.Rows.Find(Convert.ToInt32(sdr["CreditAccountID"]))["Number"].ToString(); ndr["CreditAccount"] = creditAccount; //2008-10-09 Depend on credit account, we have different vendor ID creditAccountNum = AccountNumber.Parse(creditAccount); if (creditAccountNum.ProjectGrant == "U023440") { ndr["VendorID"] = "0000456136"; } else { ndr["VendorID"] = "0000456133"; } //Used to calculate the total credit amount fTotal += chargeAmount; //2008-10-08 We have to find out the shortcode for the credit account as well, requested by Sandrine creditAccountShortCode = dtAccount.Rows.Find(Convert.ToInt32(sdr["CreditAccountID"]))["ShortCode"].ToString(); if (creditAccount != lastCreditAccount && lastCreditAccount != "default") { //Summary row fTotal -= chargeAmount; //we have to deduct the charge amount again because its no longer belong to this group AccountNumber lastCreditAccountNum = AccountNumber.Parse(lastCreditAccount); summaryUnit2.CardType = 1; summaryUnit2.ShortCode = lastCreditAccountShortCode; summaryUnit2.Account = lastCreditAccountNum.Account; summaryUnit2.FundCode = lastCreditAccountNum.FundCode; summaryUnit2.DeptID = lastCreditAccountNum.DeptID; summaryUnit2.ProgramCode = lastCreditAccountNum.ProgramCode; summaryUnit2.ClassName = lastCreditAccountNum.Class; summaryUnit2.ProjectGrant = lastCreditAccountNum.ProjectGrant; summaryUnit2.InvoiceDate = EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd"); summaryUnit2.Uniqname = "CreditAccount"; summaryUnit2.DepartmentalReferenceNumber = depRefNum; summaryUnit2.ItemDescription = "CreditAccount"; summaryUnit2.MerchandiseAmount = Math.Round(-fTotal, 2); summaryUnit2.CreditAccount = creditAccount; fTotal = chargeAmount; //add the chargeamount back, because we have new group } lastCreditAccount = creditAccount; lastCreditAccountShortCode = creditAccountShortCode; dt.Rows.Add(ndr); } } //Summary row creditAccountNum = AccountNumber.Parse(creditAccount); summaryUnit1.CardType = 1; summaryUnit1.ShortCode = creditAccountShortCode; summaryUnit1.Account = creditAccountNum.Account; summaryUnit1.FundCode = creditAccountNum.FundCode; summaryUnit1.DeptID = creditAccountNum.DeptID; summaryUnit1.ProgramCode = creditAccountNum.ProgramCode; summaryUnit1.ClassName = creditAccountNum.Class; summaryUnit1.ProjectGrant = creditAccountNum.ProjectGrant; summaryUnit1.InvoiceDate = EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd"); summaryUnit1.Uniqname = "CreditAccount"; summaryUnit1.DepartmentalReferenceNumber = depRefNum; summaryUnit1.ItemDescription = "CreditAccount"; summaryUnit1.MerchandiseAmount = Math.Round(-fTotal, 2); summaryUnit1.CreditAccount = creditAccount; //Clean things up manually might help performance in general dtStoreDB.Clear(); dtClient.Clear(); dtAccount.Clear(); }
protected void BtnDownloadAll_Click(object sender, EventArgs e) { MakeOrgGrid(false); var items = (IEnumerable <ExternalInvoice>)rptInvoice.DataSource; bool del = true; if (items.Count() > 0) { var mgr = GetExternalInvoiceManager(); var invoices = mgr.GetInvoices(); foreach (ExternalInvoice i in items) { int accountId = i.Header.AccountID; // get datarows for Org and Account var inv = invoices.First(x => x.Header.AccountID == accountId); string alert = string.Empty; string fileName = ExcelUtility.GenerateInvoiceExcelReport(i, CurrentUser.ClientID, "Zip", del, ref alert); del = false; } Response.Redirect("~/RepInvoice.aspx?DownloadAll=" + "ExternalInvoices_" + StartPeriod.ToString("yyyy-MM")); } }
private string GetDataURL(string report, string charge, bool twoCreditAccounts) { string result = VirtualPathUtility.ToAbsolute("~/data/"); result += string.Format("?report={0}&charge={1}&sdate={2}&edate={3}", report, charge, StartPeriod.ToString("yyyyMM"), EndPeriod.ToString("yyyyMM")); if (charge == "store") { result += "&accts=" + (twoCreditAccounts ? "1" : "0"); } return(result); }
//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); } }