// @note This is the main function of the working // thread. public Process(string filePath, string plant, int year, int period) { ExcoExRate.GetExchangeRatesList(); try { int plantID = 0; string plantName = string.Empty; if (plant == "Markham") { plantID = 1; plantName = "Markham"; } else if (plant == "Michigan") { plantID = 3; plantName = "Michigan"; } else if (plant == "Texas") { plantID = 5; plantName = "Texas"; } else if (plant == "Colombia") { plantID = 4; plantName = "Colombia"; } ExcoCalendar calendar = new ExcoCalendar(year, period, true, plantID); ExcoCalendar tempCalendar = new ExcoCalendar(year, period, false, 1); ExcoODBC database = ExcoODBC.Instance; database.Open(Database.CMSDAT); Dictionary <int, Account> accountMap = new Dictionary <int, Account>(); string query = string.Empty; OdbcDataReader reader; if (plantID == 1) { query = "select distinct a.aj4gl#1 as no1, a.aj4gl#2 as no2, b.aztitl as title, b.azatyp as account_type, (a.aj4tt" + calendar.GetFiscalMonth().ToString("D2") + "+aj4ob" + calendar.GetFiscalMonth().ToString("D2") + ") as balance from cmsdat.glmt as a, cmsdat.mast as b where a.aj4comp=1 and a.aj4ccyy=" + (calendar.GetFiscalYear()).ToString("D2") + " and a.aj4gl#1=b.azgl#1 and a.aj4gl#2=b.azgl#2 and b.azcomp=1 and a.aj4gl#1!=200"; reader = database.RunQuery(query); while (reader.Read()) { Account account = new Account(); account.glNum1 = Convert.ToInt32(reader["no1"]) % 100; account.glNum2 = Convert.ToInt32(reader["no2"]); account.title = reader["title"].ToString().Trim(); account.balanceAmount01 = Convert.ToDouble(reader["balance"]); int key = account.glNum1 * 1000000 + account.glNum2; if (99999999 == key) { continue; } if (accountMap.ContainsKey(key)) { accountMap[key].balanceAmount01 = account.balanceAmount01; } else { accountMap.Add(key, account); } } reader.Close(); } else if (plantID == 3) { query = "select a.aj4gl#1 as no1, a.aj4gl#2 as no2, b.aztitl as title, b.azatyp as account_type, (a.aj4tt" + calendar.GetFiscalMonth().ToString("D2") + "+aj4ob" + calendar.GetFiscalMonth().ToString("D2") + ") as balance from cmsdat.glmt as a, cmsdat.mast as b where a.aj4comp=3 and a.aj4ccyy=" + (calendar.GetFiscalYear()).ToString("D2") + " and a.aj4gl#1=b.azgl#1 and a.aj4gl#2=b.azgl#2 and b.azcomp=3"; reader = database.RunQuery(query); while (reader.Read()) { Account account = new Account(); account.glNum1 = Convert.ToInt32(reader["no1"]) % 100; account.glNum2 = Convert.ToInt32(reader["no2"]); account.title = reader["title"].ToString().Trim(); account.balanceAmount03 = Convert.ToDouble(reader["balance"]); int key = account.glNum1 * 1000000 + account.glNum2; if (99999999 == key) { continue; } if (accountMap.ContainsKey(key)) { accountMap[key].balanceAmount03 = account.balanceAmount03; } else { accountMap.Add(key, account); } } reader.Close(); } else if (plantID == 5) { query = "select a.aj4gl#1 as no1, a.aj4gl#2 as no2, b.aztitl as title, b.azatyp as account_type, (a.aj4tt" + calendar.GetFiscalMonth().ToString("D2") + "+aj4ob" + calendar.GetFiscalMonth().ToString("D2") + ") as balance from cmsdat.glmt as a, cmsdat.mast as b where a.aj4comp=5 and a.aj4ccyy=" + (calendar.GetFiscalYear()).ToString("D2") + " and a.aj4gl#1=b.azgl#1 and a.aj4gl#2=b.azgl#2 and b.azcomp=5"; reader = database.RunQuery(query); while (reader.Read()) { Account account = new Account(); account.glNum1 = Convert.ToInt32(reader["no1"]) % 100; account.glNum2 = Convert.ToInt32(reader["no2"]); account.title = reader["title"].ToString().Trim(); account.balanceAmount05 = Convert.ToDouble(reader["balance"]); int key = account.glNum1 * 1000000 + account.glNum2; if (99999999 == key) { continue; } if (accountMap.ContainsKey(key)) { accountMap[key].balanceAmount05 = account.balanceAmount05; } else { accountMap.Add(key, account); } } reader.Close(); } else if (plantID == 4) { // plant 04 query = "select a.aj4gl#1 as no1, a.aj4gl#2 as no2, b.aztitl as title, b.azatyp as account_type, (a.aj4tt" + calendar.GetFiscalMonth().ToString("D2") + "+aj4ob" + calendar.GetFiscalMonth().ToString("D2") + ") as balance from cmsdat.glmt as a, cmsdat.mast as b where a.aj4comp=4 and a.aj4ccyy=" + (calendar.GetFiscalYear()).ToString("D2") + " and a.aj4gl#1=b.azgl#1 and a.aj4gl#2=b.azgl#2 and b.azcomp=4"; reader = database.RunQuery(query); while (reader.Read()) { Account account = new Account(); account.glNum1 = Convert.ToInt32(reader["no1"]) % 100; account.glNum2 = Convert.ToInt32(reader["no2"]); account.title = reader["title"].ToString().Trim(); account.balanceAmount04 = Convert.ToDouble(reader["balance"]); int key = account.glNum1 * 1000000 + account.glNum2; if (99999999 == key) { continue; } if (accountMap.ContainsKey(key)) { accountMap[key].balanceAmount04 = account.balanceAmount04; } else { accountMap.Add(key, account); } } reader.Close(); // plant 48 query = "select a.aj4gl#1 as no1, a.aj4gl#2 as no2, b.aztitl as title, b.azatyp as account_type, (a.aj4tt" + calendar.GetFiscalMonth().ToString("D2") + "+aj4ob" + calendar.GetFiscalMonth().ToString("D2") + ") as balance from cmsdat.glmt as a, cmsdat.mast as b where a.aj4comp=48 and a.aj4ccyy=" + (calendar.GetFiscalYear() + 2000).ToString("D2") + " and a.aj4gl#1=b.azgl#1 and a.aj4gl#2=b.azgl#2 and b.azcomp=48"; reader = database.RunQuery(query); while (reader.Read()) { Account account = new Account(); account.glNum1 = Convert.ToInt32(reader["no1"]) % 100; account.glNum2 = Convert.ToInt32(reader["no2"]); account.title = reader["title"].ToString().Trim(); account.balanceAmount48 = Convert.ToDouble(reader["balance"]); int key = account.glNum1 * 1000000 + account.glNum2; if (99999999 == key) { continue; } if (accountMap.ContainsKey(key)) { accountMap[key].balanceAmount48 = account.balanceAmount48; } else { accountMap.Add(key, account); } } reader.Close(); // plant 41 query = "select a.aj4gl#1 as no1, a.aj4gl#2 as no2, b.aztitl as title, b.azatyp as account_type, (a.aj4tt" + tempCalendar.GetFiscalMonth().ToString("D2") + "+aj4ob" + tempCalendar.GetFiscalMonth().ToString("D2") + ") as balance from cmsdat.glmt as a, cmsdat.mast as b where a.aj4comp=41 and a.aj4ccyy=" + (tempCalendar.GetFiscalYear() + 2000).ToString("D2") + " and a.aj4gl#1=b.azgl#1 and a.aj4gl#2=b.azgl#2 and b.azcomp=41"; reader = database.RunQuery(query); while (reader.Read()) { Account account = new Account(); account.glNum1 = Convert.ToInt32(reader["no1"]) % 100; account.glNum2 = Convert.ToInt32(reader["no2"]); account.title = reader["title"].ToString().Trim(); account.balanceAmount41 = Convert.ToDouble(reader["balance"]); int key = account.glNum1 * 1000000 + account.glNum2; if (99999999 == key) { continue; } if (accountMap.ContainsKey(key)) { accountMap[key].balanceAmount41 = account.balanceAmount41; } else { accountMap.Add(key, account); } } reader.Close(); // plant 49 query = "select a.aj4gl#1 as no1, a.aj4gl#2 as no2, b.aztitl as title, b.azatyp as account_type, (a.aj4tt" + tempCalendar.GetFiscalMonth().ToString("D2") + "+aj4ob" + tempCalendar.GetFiscalMonth().ToString("D2") + ") as balance from cmsdat.glmt as a, cmsdat.mast as b where a.aj4comp=49 and a.aj4ccyy=" + (tempCalendar.GetFiscalYear() + 2000).ToString("D2") + " and a.aj4gl#1=b.azgl#1 and a.aj4gl#2=b.azgl#2 and b.azcomp=49"; reader = database.RunQuery(query); while (reader.Read()) { Account account = new Account(); account.glNum1 = Convert.ToInt32(reader["no1"]) % 100; account.glNum2 = Convert.ToInt32(reader["no2"]); account.title = reader["title"].ToString().Trim(); account.balanceAmount49 = Convert.ToDouble(reader["balance"]); int key = account.glNum1 * 1000000 + account.glNum2; if (99999999 == key) { continue; } if (accountMap.ContainsKey(key)) { accountMap[key].balanceAmount49 = account.balanceAmount49; } else { accountMap.Add(key, account); } } reader.Close(); } else { throw new Exception("None of plants has been selected!"); } // write to excel Excel2.Application excel = new Excel2.Application(); object misValue = System.Reflection.Missing.Value; Excel2.Workbook book = excel.Workbooks.Add(misValue); //Excel.Worksheet sheet = book.Worksheets[1]; Excel2.Worksheet sheet = book.Worksheets.Add();//[1]; //sheet.Delete(); //sheet = book.Worksheets[1]; //sheet.Delete(); //sheet = book.Worksheets[1]; sheet.Name = "Trial Balance for " + plantName; // write title sheet.Cells[1, 1] = plantName; Excel2.Range range = sheet.get_Range("A1"); range.Font.Color = Color.Red; range.Font.Size = 14; sheet.Cells[2, 1] = "Balance Consolidations"; range = sheet.get_Range("A2"); range.Font.Size = 10; range.Font.Bold = true; sheet.Cells[3, 1] = "Generated at " + DateTime.Today.ToString("MMMM-dd-yyyy"); range = sheet.get_Range("A3"); range.Font.Size = 10; range.Font.Bold = true; sheet.Cells[6, 1] = "Ending Balance"; range = sheet.get_Range("A6"); range.Font.Size = 11; range.Font.Color = Color.Red; range.Font.Bold = true; sheet.Cells[7, 1] = "Account"; range = sheet.get_Range("A7"); range.Font.Size = 11; range.Cells.Borders[Excel2.XlBordersIndex.xlEdgeBottom].LineStyle = Excel2.XlLineStyle.xlContinuous; sheet.Cells[5, 2] = "Company Number"; range = sheet.get_Range("B5"); range.Font.Size = 11; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; sheet.Cells[6, 2] = "Period"; range = sheet.get_Range("B6"); range.Font.Size = 11; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; sheet.Cells[7, 2] = "Account Name / Company"; range = sheet.get_Range("B7"); range.Font.Size = 11; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; if (4 != plantID) { sheet.Cells[5, 3] = plantID.ToString("D2"); range = sheet.get_Range("C5"); range.Font.Size = 11; range.Font.Color = Color.Red; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; sheet.Cells[6, 3] = (calendar.GetFiscalYear() + 2000).ToString() + "-" + calendar.GetFiscalMonth().ToString("D2"); range = sheet.get_Range("C6"); range.Font.Size = 11; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; sheet.Cells[7, 3] = plantName; range = sheet.get_Range("C7"); range.Font.Size = 11; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; } else { // 04 sheet.Cells[5, 3] = "04"; range = sheet.get_Range("C5"); range.Font.Size = 11; range.Font.Color = Color.Red; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; sheet.Cells[6, 3] = (calendar.GetFiscalYear() + 2000).ToString() + "-" + calendar.GetFiscalMonth().ToString("D2"); range = sheet.get_Range("C6"); range.Font.Size = 11; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; sheet.Cells[7, 3] = "Exco GAAP"; range = sheet.get_Range("C7"); range.Font.Size = 11; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; // 41 sheet.Cells[5, 4] = "41"; range = sheet.get_Range("D5"); range.Font.Size = 11; range.Font.Color = Color.Red; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; sheet.Cells[6, 4] = (tempCalendar.GetFiscalYear() + 2000).ToString() + "-" + tempCalendar.GetFiscalMonth().ToString("D2"); range = sheet.get_Range("D6"); range.Font.Size = 11; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; sheet.Cells[7, 4] = "Exco IFRS"; range = sheet.get_Range("D7"); range.Font.Size = 11; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; // 48 sheet.Cells[5, 5] = "48"; range = sheet.get_Range("E5"); range.Font.Size = 11; range.Font.Color = Color.Red; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; sheet.Cells[6, 5] = (calendar.GetFiscalYear() + 2000).ToString() + "-" + calendar.GetFiscalMonth().ToString("D2"); range = sheet.get_Range("E6"); range.Font.Size = 11; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; sheet.Cells[7, 5] = "Coltooling GAAP"; range = sheet.get_Range("E7"); range.Font.Size = 11; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; // 49 sheet.Cells[5, 6] = "49"; range = sheet.get_Range("F5"); range.Font.Size = 11; range.Font.Color = Color.Red; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; sheet.Cells[6, 6] = (tempCalendar.GetFiscalYear() + 2000).ToString() + "-" + tempCalendar.GetFiscalMonth().ToString("D2"); range = sheet.get_Range("F6"); range.Font.Size = 11; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; sheet.Cells[7, 6] = "Coltooling IFRS"; range = sheet.get_Range("F7"); range.Font.Size = 11; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; // cosolidated sheet.Cells[7, 7] = "Consolidated"; range = sheet.get_Range("G7"); range.Font.Size = 11; range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous; range.Cells.Interior.Color = Color.PeachPuff; } // write content int row = 8; var accountList = from account in accountMap.Values orderby account.glNum1 * 1000000 + account.glNum2 select account; foreach (Account account in accountList) { sheet.Cells[row, 1] = account.glNum1.ToString("D2") + "-" + (account.glNum2 / 100).ToString("D4") + "-" + (account.glNum2 % 100).ToString("D2"); sheet.Cells[row, 2] = account.title; if (1 == plantID) { sheet.Cells[row++, 3] = account.balanceAmount01.ToString("C2"); } else if (3 == plantID) { sheet.Cells[row++, 3] = account.balanceAmount03.ToString("C2"); } else if (5 == plantID) { sheet.Cells[row++, 3] = account.balanceAmount05.ToString("C2"); } else if (4 == plantID) { sheet.Cells[row, 3] = account.balanceAmount04.ToString("C2"); sheet.Cells[row, 4] = account.balanceAmount41.ToString("C2"); sheet.Cells[row, 5] = account.balanceAmount48.ToString("C2"); sheet.Cells[row, 6] = account.balanceAmount49.ToString("C2"); range = sheet.get_Range("G" + row.ToString()); range.Formula = "=sum(C" + row.ToString() + ":F" + row.ToString() + ")"; row++; } } // write to file sheet.Cells.Columns.AutoFit(); sheet.Cells.HorizontalAlignment = Excel2.XlHAlign.xlHAlignCenter; File.Delete(filePath); book.SaveAs(filePath, Excel2.XlFileFormat.xlOpenXMLWorkbook); excel.Quit(); } catch (Exception e) { } }
public ActionResult ExchangeRates(string paramOne) { FinancialControls.CurrencyYearList = ExcoExRate.GetExchangeRatesList(); return(View()); }
public void Generate(string path, string plant, string fiscalYear, string fiscalMonth) { ExcoExRate.GetExchangeRatesList(); string plant_name = plant; this.plant = plant; int workbook_number = 10; if (plant == "Colombia") { workbook_number = 10; plant_name = "Colombia"; currency_string = "PESO"; } else if (plant == "Texas") { workbook_number = 8; plant_name = "Texas"; currency_string = "USD"; } else if (plant == "Michigan") { workbook_number = 6; plant_name = "Michigan"; currency_string = "USD"; } else if (plant == "Markham") { workbook_number = 4; plant_name = "Markham"; currency_string = "CAD"; } if (native_currency) { workbook_number--; } else { currency_string = "CAD"; } for (int i = 0; i < 200; i++) { for (int j = 0; j < 20; j++) { WORKSHEET_ARRAY[i, j] = "0"; } } for (int i = 0; i < 12; i++) { for (int j = 0; j < 400; j++) { ANNUAL_DATA[i, j] = ""; ANNUAL_PERCENTAGE[i, j] = ""; } } try { //while (true) //{ int month = Convert.ToInt32(fiscalMonth); int year = Convert.ToInt32(fiscalYear) - 2000; double current_period_exch_rate = 0; for (int i = 1; i < 13; i++) { Log.Append(" Processing period: " + month.ToString() + "/" + year .ToString()); // + "C:\\Sales Report\\Income Statement Report at " + i.ToString() + "-15.xlsx"); int Worksheet_NO = 1; string fileName = @"\\10.0.0.8\EmailAPI\Financials\YTD-IS-Reports\Repository\Income Statement Report at " + month.ToString() + "-" + year.ToString() + ".xlsx"; try { //foreach (var worksheet in Workbook.Worksheets(@"C:\Sales Report\Income Statement Report at " + i.ToString() + "-" + fiscalYear.ToString() + ".xlsx")) foreach (var worksheet in Workbook.Worksheets(fileName)) { int row_count = 0; int column_count = 0; if (worksheet.Rows.Length > 0 && worksheet.Rows[0].Cells[0].Text.Contains(plant_name) && ( native_currency && worksheet.Rows[0].Cells[0].Text.Contains(currency_string) || !native_currency && worksheet.Rows[0].Cells[0].Text.Contains("CAD") )) { foreach (var row in worksheet.Rows) //for (int iz = 0; iz < 200; iz++) { row_count++; column_count = 0; foreach (var cell in row.Cells) { column_count++; if (column_count < 4 || (i == 1 && (column_count == 6 || column_count == 7)) || ((column_count == 6 || column_count == 10 || column_count == 11))) //name/ actual/ percentage //if (column_count < 4 || (i == 1 && (column_count == 6 || column_count == 7)) || (i == 1 && (column_count == 10 || column_count == 11))) //name/ actual/ percentage { if (column_count < 4) { if (total_found_on_row == 0 && cell.Text.Contains("TOTAL") && !cell.Text.Contains("SALES") && !cell.Text.Contains("SURCHARGE")) { total_found_on_row = row_count; } double a; double b; if (double.TryParse(WORKSHEET_ARRAY[row_count, column_count], out a) && double.TryParse(cell.Text, out b)) { /* * a = Convert.ToDouble( * WORKSHEET_ARRAY[row_count, column_count]); * b = Convert.ToDouble(cell.Text);*/ if (row_count == 2) // Get exchange rate { ANNUAL_DATA[i - 1, row_count] = cell.Value.Length > 4 ? cell.Value : cell.Value; if (plant == "Colombia" && native_currency) { current_period_exch_rate = Convert.ToDouble(cell.Value); } else if (native_currency) { current_period_exch_rate = 1; } //Log.Append(" Current Exchange rate: " + current_period_exch_rate); } else if (column_count == 2) { ANNUAL_DATA[i - 1, row_count] = cell.Text; if (month <= Convert.ToInt32(fiscal_month)) { WORKSHEET_ARRAY[row_count, column_count] = (a + b).ToString(); } } else { ANNUAL_PERCENTAGE[i - 1, row_count] = (Convert.ToDouble(cell.Value)).ToString("P2"); } } else { WORKSHEET_ARRAY[row_count, column_count] = cell.Text; } } double c; // Store budget information for current period if (i == 1 && column_count == 6 && double.TryParse(cell.Value.ToString(), out c)) { BUDGET_CURRENT_PERIOD[0, row_count] = c.ToString(); } if (i == 1 && column_count == 7 && double.TryParse(cell.Value.ToString(), out c)) { BUDGET_CURRENT_PERIOD[1, row_count] = c.ToString("P2"); } // Store Y-T-D budget information for current period if (column_count == 6 && row_count > 4 && double.TryParse(cell.Value.ToString(), out c)) //if (column_count == 10 && row_count > 4) //if (i == 1 && column_count == 10 && row_count > 4) { //YTD_BUDGET_CURRENT_PERIOD[0, row_count] = cell.Text;//add //if (Convert.ToDouble(cell.Text) > 1) if (month <= Convert.ToInt32(fiscal_month)) { //YTD_BUDGET_CURRENT_PERIOD[0, row_count] = (Convert.ToDouble(YTD_BUDGET_CURRENT_PERIOD[0, row_count]) + (Convert.ToDouble(cell.Text) / current_period_exch_rate)).ToString(); // ADD YTD_BUDGET_CURRENT_PERIOD[0, row_count] = (Convert.ToDouble(YTD_BUDGET_CURRENT_PERIOD[0, row_count]) + c).ToString(); // ADD } //if (row_count < 26) //Log.Append(" Ongoing total: " + YTD_BUDGET_CURRENT_PERIOD[0, row_count]); } if (i == 1 && column_count == 11 && row_count > 4 && double.TryParse(cell.Value.ToString(), out c)) //get percentage { YTD_BUDGET_CURRENT_PERIOD[1, row_count] = c.ToString("P2"); } } } } } Worksheet_NO++; } } catch (Exception ex) { Log.Append("Error: " + ex.ToString()); } // Adjust to last year PERIOD_VALUES[i - 1] = month.ToString(); month--; if (month == 0) { month = 12; year--; } } //} Log.Append(" Summarizing information..."); Log.Append(" Generating Excel file..."); // EXCEL WRITE workBook = excel.Workbooks.Add(misValue); // create work sheets //consolidatedIS = workBook.Worksheets[3]; consolidatedIS = workBook.Worksheets.Add(); consolidatedIS.Name = "12-month " + plant_name + " (" + currency_string + ")"; consolidatedIS.Columns[2].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; consolidatedIS.Columns[4].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; consolidatedIS.Columns[6].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; consolidatedIS.Columns[8].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; consolidatedIS.Columns[10].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; consolidatedIS.Columns[12].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; consolidatedIS.Columns[14].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; consolidatedIS.Columns[16].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; consolidatedIS.Columns[18].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; consolidatedIS.Columns[20].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; consolidatedIS.Columns[22].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; consolidatedIS.Columns[24].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; consolidatedIS.Columns[26].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; consolidatedIS.Columns[28].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; consolidatedIS.Columns[29].NumberFormat = "##0.00%;-##0.00%"; consolidatedIS.Columns[30].NumberFormat = "$#,##0.00;([Red]$#,##0.00)"; if (workbook_number == 8) // If colombia { consolidatedIS.Rows[3].NumberFormat = "0.000###"; } else { consolidatedIS.Rows[3].NumberFormat = "0.###"; } int excel_row = 1; // title excel_row = WriteSheetHeader( "Annual Summarized Income Statement for " + plant_name + " in " + currency_string + " (" + fiscal_year + ")", consolidatedIS); // header excel_row++; excel_row = WriteHeader(consolidatedIS, excel_row); excel_row++; excel_row = WriteLines(consolidatedIS, excel_row); excel_row++; AdjustSheetStyle(consolidatedIS, excel_row); // write to file File.Delete(path); workBook.SaveAs(path, ExcelWriter.XlFileFormat.xlOpenXMLWorkbook); //workBook.Close(); //excel.Quit(); _MARSHAL_KILL_EXCEL(path); } catch { } }