// @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
{
}
}
