public void RPT2(C1XLBook book, C1.Win.C1FlexGrid.C1FlexGrid vs, string title, string filename)
{
//string ID="RPT1";
book = new C1XLBook();
XLSheet sheet = book.Sheets[0];
FormatExcel.Init_Excel(sheet);
sheet.Name = "Sheet1";
XLStyle Title = FormatExcel.Get_Style(book, 16, true, XLAlignVertEnum.Center, XLAlignHorzEnum.Center, true, false, "");
XLStyle Caption = FormatExcel.Get_Style(book, 10, true, XLAlignVertEnum.Center, XLAlignHorzEnum.Center, true, true, "");
XLStyle StringN = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, false, "");
XLStyle String = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "");
XLStyle Number = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "#,##0.00");
sheet.Columns[0].Width = 500;
int r = 0, cols = 0;
ArrayList a = new ArrayList();
for (int i = 0; i < vs.Cols.Count; i++)
{
if (vs.Cols[i].Visible == true)
{
cols++;
a.Add(i);
}
}
sheet.MergedCells.Add(r, 0, 1, cols);
sheet.Rows[r].Height = 800;
FormatExcel.Set_Cell(sheet[r, 0],
T_String.GetDataFromSQL("COM_N1", "FILA01A"), Title);
r++;
sheet.MergedCells.Add(r, 0, 1, cols);
sheet.Rows[r].Height = 800;
FormatExcel.Set_Cell(sheet[r, 0], title, Title);
r++;
FormatExcel.Set_Cell(sheet[r, cols - 6], PublicFunction.L_Get_RPT("RptTa", 1) + ":" + PublicFunction.A_UserID, StringN);
r++;
FormatExcel.Set_Cell(sheet[r, cols - 6], PublicFunction.L_Get_RPT("RptTa", 2) + ":" + T_String.GetDate().ToString("yyyy/MM/dd HH:mm"), StringN);
r++;
for (int j = 0; j < vs.Rows.Count; j++)
{
for (int i = 0; i < a.Count; i++)
{
if (j == 0)
{
FormatExcel.Set_Cell(sheet[r, i], vs.Rows[j][(int)a[i]] + "", Caption);
}
else
{
if (vs.Cols[(int)a[i]].DataType == typeof(DateTime))
{
try
{
FormatExcel.Set_Cell(sheet[r, i], DateTime.Parse(vs.Rows[j][(int)a[i]] + "").ToString(vs.Cols[(int)a[i]].Format), String);
sheet.Columns[i].Width = 1500;
}
catch { FormatExcel.Set_Cell(sheet[r, i], "", String); }
}
else
{
if (vs.Cols[(int)a[i]].DataType == typeof(Boolean))
{
if (vs.GetDataDisplay(j, (int)a[i]) + "" != "True")
{
FormatExcel.Set_Cell(sheet[r, i], "False", String);
}
else
{
FormatExcel.Set_Cell(sheet[r, i], "True", String);
}
}
else
{
if (vs.Cols[(int)a[i]].DataType == typeof(String))
{
FormatExcel.Set_Cell(sheet[r, i], vs.GetDataDisplay(j, (int)a[i]) + "", String);
}
else
{
// FormatExcel.Set_Cell(sheet[r,i],"",String);
// sheet[r,i].Value= T_String.IsNullTo00(vs.GetDataDisplay(j,(int)a[i]));
FormatExcel.Set_Cell(sheet[r, i], vs.GetDataDisplay(j, (int)a[i]) + "", String);
}
//FormatExcel.Set_Cell(sheet[r,i], vs.GetDataDisplay(j,(int)a[i])+"",Number);
}
}
}
}
r++;
}
sheet.Columns[1].Width = 1200;
sheet.Columns[2].Width = 1000;
sheet.Columns[3].Width = 3000;
try
{
string fileName = Application.StartupPath + @"\\Reports\\" + filename + ".xls";
book.Save(fileName);
System.Diagnostics.Process.Start(fileName);
}
catch
{
MessageBox.Show("You must close " + filename + ".xls file first!!!!!");
return;
}
}
static void Main(string[] args)
{
Console.WriteLine("--------------------------");
Console.WriteLine("Accounts Management Portal");
Console.WriteLine("--------------------------");
AMOperations operations = new AMOperations();
string[] accounts = operations.initializeAccounts();
string[] organizations = operations.initializeOrganizations();
string[] balances = operations.initializeBalances();
string input;
string[] commandArgs;
//main program loop
do
{
//accept user input for command
Console.WriteLine();
Console.Write("What would you like to do?: ");
input = Console.ReadLine();
commandArgs = input.Split(" ", 2);
if (commandArgs[0] == "increment")
{
//schema: "incBy --accountName --incBy"
//increment account by value given
try {
string[] temp = commandArgs[1].Split(" ", 3);
if (temp[0] == null || temp[1] == null)
{
operations.argError();
}
int index = operations.getIndex(temp[0], accounts);
balances = operations.incBal(index, temp[1], balances, temp[0]);
operations.listAcc(accounts, organizations, balances);
continue;
}
catch (System.IndexOutOfRangeException)
{
operations.argError();
continue;
}
}
else if (commandArgs[0] == "decrement")
{
//schema: "decBal --accountName --decBy"
//decrement account by value given
try {
string[] temp = commandArgs[1].Split(" ", 2);
int index = operations.getIndex(temp[0], accounts);
balances = operations.decBal(index, temp[1], balances, temp[0]);
operations.listAcc(accounts, organizations, balances);
continue;
}
catch (System.IndexOutOfRangeException)
{
operations.argError();
continue;
}
}
else if (commandArgs[0] == "listAccounts")
{
//schema: "listAccounts"
//list current accounts and associated information
operations.listAcc(accounts, organizations, balances);
continue;
}
else if (commandArgs[0] == "removeAccount")
{
//schema: "removeAccount --accountName"
//remove account from xml
try {
int index = operations.getIndex(commandArgs[1], accounts);
accounts = operations.remAcc(index, accounts, commandArgs[1]);
organizations = operations.remOrg(index, organizations);
balances = operations.remBal(index, balances);
operations.listAcc(accounts, organizations, balances);
continue;
}
catch (System.IndexOutOfRangeException)
{
operations.argError();
continue;
}
}
else if (commandArgs[0] == "addAccount")
{
//schema: "addAccount --accountName --organization --newBalance"
//add account to xml
try
{
string[] temp = commandArgs[1].Split(" ", 3);
if (!operations.duplicateAccount(temp[0], accounts))
{
operations.logNewAccount(temp[0], temp[1], temp[2]);
accounts = operations.addAcc(temp[0], accounts);
organizations = operations.addOrg(temp[1], organizations);
balances = operations.addBal(temp[2], balances);
operations.listAcc(accounts, organizations, balances);
}
continue;
} catch (System.IndexOutOfRangeException)
{
operations.argError();
continue;
}
}
else if (commandArgs[0] == "setBalance")
{
//schema: "setBalance --accountName --newBalance"
//set balance of account to value given
try {
string[] temp = commandArgs[1].Split(" ", 2);
int index = operations.getIndex(temp[0], accounts);
string newBalance = temp[1];
balances = operations.setBal(newBalance, index, balances, temp[0]);
operations.listAcc(accounts, organizations, balances);
continue;
}
catch (System.IndexOutOfRangeException)
{
operations.argError();
continue;
}
}
else if (commandArgs[0] == "setOrg")
{
//schema: "setOrg --accountName --newOrg"
//replace organization association for account with given string
try {
string[] temp = commandArgs[1].Split(" ", 2);
int index = operations.getIndex(temp[0], accounts);
string newOrg = temp[1];
organizations = operations.setOrg(newOrg, index, organizations, temp[0]);
operations.listAcc(accounts, organizations, balances);
}
catch (System.IndexOutOfRangeException)
{
operations.argError();
continue;
}
}
else if (commandArgs[0] == "balanceSum")
{
//schema: "balanceSum"
//print sum of all balances currently in system
operations.AllBalanceSum(balances);
}
else if (commandArgs[0] == "--help")
{
Console.WriteLine("--------------------------------------------------------------------------------------");
Console.WriteLine("First option must be a command specifier, followed by necessary arguments:");
Console.WriteLine("\taddAccount -accountName -organization -newBalance");
Console.WriteLine("\tsetOrg -accountName -newOrg");
Console.WriteLine("\tsetBalance -accountName -newBalance");
Console.WriteLine("\tremoveAccount -accountName");
Console.WriteLine("\tlistAccounts");
Console.WriteLine("\tdecrement -accountName -decBy");
Console.WriteLine("\tincrement -accountName -incBy");
Console.WriteLine("\tbalanceSum");
Console.WriteLine("\texportToExcel -ExcelFileName ");
Console.WriteLine("\texportToExcel --Path -PathWithExcelFileName");
Console.Write("\t\t\t");
Console.Write(@"example usage: exportToExcel --Path C:\Users\bob\Documents\doc");
Console.WriteLine("\n\nTo terminate the program, use the 'exit' command");
Console.WriteLine("Following the termination of the program,");
Console.WriteLine(" an XML file with updated account information will be found in the current directory");
Console.WriteLine("--------------------------------------------------------------------------------------");
}
else if (commandArgs[0] == "exportToExcel")
{
FormatExcel export = new FormatExcel();
try {
string[] temp;
string Xlfile;
//determine whether --Path flag was used, else use current directory
if (commandArgs[1].Contains("--Path"))
{
temp = commandArgs[1].Split(" ", 2);
Xlfile = temp[1] + ".xslx";
Console.WriteLine("Excel Document is being created at path: '" + Xlfile + "'");
}
else
{
Xlfile = commandArgs[1] + ".xlsx";
Console.WriteLine("Excel Document has been created in the User's Document directory. Please specify --Path for another destination.");
}
//update XML savedData to save recent changes
operations.constructXML(accounts, organizations, balances);
//Convert the XML into Dataset
System.Data.DataTable dt = export.convertXmlToEl();
// Create an Excel object
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//Create a workbook or open existing workbook if filename matches
string fileName = commandArgs[1];
Microsoft.Office.Interop.Excel.Workbook workbook = export.CreateWorkbook(fileName, excel);
//Create worksheet object
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
//populate workbook with saved DataTable data
workbook = export.PopulateWorkbook(workbook, worksheet, excel, dt);
//Save the workbook
try
{
workbook.SaveAs(Xlfile);
} catch (System.Runtime.InteropServices.COMException)
{
Console.WriteLine("The filepath input to export to was invalid. Please make sure to include the filename at the end.");
Console.WriteLine("Please try again or save via the window.\n");
continue;
}
//Close the Workbook
workbook.Close(true);
// Finally Quit the Application
((Microsoft.Office.Interop.Excel.Application)excel).Quit();
Console.WriteLine("Excel Document has been exported!\n");
continue;
}
catch (System.IndexOutOfRangeException)
{
operations.argError();
continue;
}
}
else
{
Console.WriteLine("No valid command given. Try Again or type --help");
}
}while (commandArgs[0] != "exit"); //end when user inputs terminal command
Console.WriteLine("--------------------------------------");
Console.WriteLine("Saving data to local XML document");
Console.WriteLine("--------------------------------------");
operations.constructXML(accounts, organizations, balances);
Console.WriteLine();
Console.WriteLine("--------------------------------------");
Console.WriteLine("Writing to local JSON log file");
Console.WriteLine("--------------------------------------");
operations.Finish();
Console.WriteLine();
Console.WriteLine("--------------------------------------");
Console.WriteLine("Terminating Accounts Management Portal");
Console.WriteLine("--------------------------------------");
}
public void RPTta(C1XLBook book, string RPT_ID, string title, string wh)
{
//string ID="RPT1";
book = new C1XLBook();
XLSheet sheet = book.Sheets[0];
FormatExcel.Init_Excel(sheet);
sheet.Name = "Sheet1";
XLStyle Title = FormatExcel.Get_Style(book, 16, true, XLAlignVertEnum.Center, XLAlignHorzEnum.Center, true, false, "");
XLStyle Caption = FormatExcel.Get_Style(book, 10, true, XLAlignVertEnum.Center, XLAlignHorzEnum.Center, true, true, "");
XLStyle StringN = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, false, "");
XLStyle String = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "");
XLStyle INT = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "#,###");
XLStyle GIO = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "#,###");
XLStyle DOU = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "#,###.##");
string sql = "";
sql = "Select * from FILE07B where RPT_ID=N'" + RPT_ID + "' and SHO_BT=1 ORDER BY SEQ_NO";
Func.RecordSet rscon = new Func.RecordSet(sql, PublicFunction.C_con);
sql = GET_SQL(rscon, wh);
Func.RecordSet rs = new RecordSet(sql, PublicFunction.C_con);
try
{
if (rs.rows <= 0)
{
return;
}
}
catch { return; }
int r = 0, cols = 0;
ArrayList a = new ArrayList();
a.Add(0);
sheet.Columns[0].Width = 680;
for (int i = 0; i < rs.cols; i++)
{
cols++;
a.Add(i);
}
// tieu de
sheet.MergedCells.Add(r, 0, 1, cols);
sheet.Rows[r].Height = 800;
FormatExcel.Set_Cell(sheet[r, 0],
T_String.GetDataFromSQL("COM_N1", "FILA01A"), Title);
r++;
sheet.MergedCells.Add(r, 0, 1, cols);
sheet.Rows[r].Height = 800;
FormatExcel.Set_Cell(sheet[r, 0], title, Title);
r++;
FormatExcel.Set_Cell(sheet[r, cols - 2], PublicFunction.L_Get_RPT("RptTa", 1) + ":" + PublicFunction.A_UserID, StringN);
r++;
FormatExcel.Set_Cell(sheet[r, cols - 2], PublicFunction.L_Get_RPT("RptTa", 2) + ":" + T_String.GetDate().ToString("yyyy/MM/dd HH:mm"), StringN);
r++;
FormatExcel.Set_Cell(sheet[r, 0], "STT", Caption);
for (int i = 0; i < rscon.rows; i++)
{
FormatExcel.Set_Cell(sheet[r, i + 1], rscon.record(i, "COL_NM"), Caption);
sheet.Columns[i + 1].Width = T_String.IsNullTo0(rscon.record(i, "WID_VL")) * 17;
}
r++;
//
for (int i = 0; i < rs.rows; i++)
{
FormatExcel.Set_Cell(sheet[r, 0], i + 1 + "", INT);
for (int c = 0; c < rscon.rows; c++)
{
int c1 = c + 1;
switch (rscon.record(c, "TYP_ID") + "")
{
case "1": // DateTime
{
try
{
FormatExcel.Set_Cell(sheet[r, c1], DateTime.Parse(rs.record(i, c) + "").ToString("yyyy/MM/dd"), String);
}
catch { FormatExcel.Set_Cell(sheet[r, c1], "", String); }
break;
}
case "2": // boolean
{
if (rs.record(i, c) + "" != "True")
{
FormatExcel.Set_Cell(sheet[r, c1], "False", String);
}
else
{
FormatExcel.Set_Cell(sheet[r, c1], "True", String);
}
break;
}
case "":
{
FormatExcel.Set_Cell(sheet[r, c1], rs.record(i, c), String);
break;
}
case "3": // 00:00
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(rs.record(i, c)).ToString("##:##"), String);
// if(rscon.record(c,"SUM_BT")+""=="True")
// {
// a[c1]= T_String.CongTG(T_String.IsNullTo00(a[c1]+""),T_String.IsNullTo00(rs.record(i,c)));
// }
break;
}
case "4": // double
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(rs.record(i, c)).ToString("#,###.##"), DOU);
if (rscon.record(c, "SUM_BT") + "" == "True")
{
a[c1] = T_String.IsNullTo00(a[c1] + "") + T_String.IsNullTo00(rs.record(i, c));
}
break;
}
case "5": // int
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(rs.record(i, c)).ToString("#,###.##"), INT);
if (rscon.record(c, "SUM_BT") + "" == "True")
{
a[c1] = T_String.IsNullTo00(a[c1] + "") + T_String.IsNullTo00(rs.record(i, c));
}
break;
}
}
}
r++;
if (i + 1 == rs.rows) //dong cuoi
{
for (int c = 0; c < rscon.rows; c++)
{
int c1 = c + 1;
if (rscon.record(c, "SUM_BT") + "" == "True")
{
switch (rscon.record(c, "TYP_ID") + "")
{
// case "3": // 00:00
// {
// FormatExcel.Set_Cell(sheet[r,c1], T_String.IsNullTo00(a[c1]+"").ToString("##:##"),String);
// break;
// }
case "4": // double
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(a[c1] + "").ToString("#,###.##"), DOU);
break;
}
case "5": // int
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(a[c1] + "").ToString("#,###.##"), INT);
break;
}
}
}
}
}
}
// }
try
{
string fileName = Application.StartupPath + @"\\Reports\\TAPO_" + RPT_ID + ".xls";
book.Save(fileName);
System.Diagnostics.Process.Start(fileName);
}
catch
{
MessageBox.Show("You must close TAPO_" + RPT_ID + ".xls file first!!!!!");
return;
}
}
