📄 balanceofpayment.cpp
字号:
// ;
}
void BalanceOfPayment::OutputExcel(void)
{
String* sSQL;
DataRowCollection* oDataRC;
System::Object* oMissing;
Excel::ApplicationClass* pExcel;
Excel::Workbook* pWorkBook;
Excel::Worksheet* pActiveSheet;
Excel::Range* pActiveCell;
int iRecordCount1, iRecordCount2, iRecordCount3;
int i, iSheetBuf, iTeamBuf, iLinePos;
String* sBuf = S"";
//Get ExcelData1
OutputExcelSQL1(sSQL);
oDataSet = oDB->dSetSQL_Select(sSQL, S"ExcelData1", 0);
oDataTable = oDataSet->Tables->Item["ExcelData1"];
oDataRC = oDataTable->Rows;
iRecordCount1 = oDataRC->Count;
if(iRecordCount1 <= 0) {
//Error Message
return;
}
try {
oMissing = System::Reflection::Missing::Value;
//Start Excel
pExcel = new Excel::ApplicationClass();
pExcel->Visible = true;
pWorkBook = pExcel->Workbooks->Add(oMissing);
//Init worksheet
static_cast<Excel::Worksheet*>(pExcel->ActiveWorkbook->Sheets->Item[__box(3)])->Delete();
static_cast<Excel::Worksheet*>(pExcel->ActiveWorkbook->Sheets->Item[__box(2)])->Delete();
//Worksheet Rename
static_cast<Excel::Worksheet*>(pExcel->ActiveWorkbook->Sheets->Item[__box(1)])->Select(oMissing);
pActiveSheet = static_cast<Excel::Worksheet*>(pExcel->ActiveSheet);
pActiveSheet->Name = S"Total";
//Loop ExcelSQL1
iSheetBuf = 0;
for(i= 0;i < iRecordCount1; i++){
//Select or add worksheet
if(iSheetBuf != Convert::ToInt32(oDataSet->Tables->Item["ExcelData1"]->Rows->get_Item(i)->get_Item(S"EXCEL_ID")->ToString())){
iSheetBuf = Convert::ToInt32(oDataSet->Tables->Item["ExcelData1"]->Rows->get_Item(i)->get_Item(S"EXCEL_ID")->ToString());
static_cast<Excel::Worksheet*>(pExcel->ActiveWorkbook->Worksheets->Add(oMissing, pActiveSheet, __box(1), oMissing));
static_cast<Excel::Worksheet*>(pExcel->ActiveWorkbook->Sheets->Item[__box(iSheetBuf + 1)])->Select(oMissing);
pActiveSheet = static_cast<Excel::Worksheet*>(pExcel->ActiveSheet);
pActiveSheet->Name = oDataSet->Tables->Item["ExcelData1"]->Rows->get_Item(i)->get_Item(S"PROCESS_NO")->ToString();
//Init Sheet
pActiveCell = static_cast<Excel::Range*>(pActiveSheet->get_Range(S"A1", S"C1"));
pActiveCell->Interior->set_ColorIndex(__box(15));
pActiveSheet->Cells->set_Item(__box(1), __box(1), S"TEAM");
pActiveSheet->Cells->set_Item(__box(1), __box(2), S"TM/D");
pActiveSheet->Cells->set_Item(__box(1), __box(3), S"COST");
}
//Cell Edit
iTeamBuf = Convert::ToInt32(oDataSet->Tables->Item["ExcelData1"]->Rows->get_Item(i)->get_Item(S"TEAM")->ToString());
sBuf = oDataSet->Tables->Item["ExcelData1"]->Rows->get_Item(i)->get_Item(S"TEAM")->ToString();
pActiveSheet->Cells->set_Item(__box(iTeamBuf + 1), __box(1), sBuf);
sBuf = oDataSet->Tables->Item["ExcelData1"]->Rows->get_Item(i)->get_Item(S"TOTAL_NUMBER")->ToString();
pActiveSheet->Cells->set_Item(__box(iTeamBuf + 1), __box(2), sBuf);
sBuf = oDataSet->Tables->Item["ExcelData1"]->Rows->get_Item(i)->get_Item(S"TOTAL_COST")->ToString();
pActiveSheet->Cells->set_Item(__box(iTeamBuf + 1), __box(3), sBuf);
}
//Loop ExcelSQL2
OutputExcelSQL2(sSQL);
oDataSet = oDB->dSetSQL_Select(sSQL, S"ExcelData2", 0);
oDataTable = oDataSet->Tables->Item["ExcelData2"];
oDataRC = oDataTable->Rows;
iRecordCount2 = oDataRC->Count;
iSheetBuf = 0;
for(i= 0;i < iRecordCount2; i++){
//Select worksheet
if(iSheetBuf != Convert::ToInt32(oDataSet->Tables->Item["ExcelData2"]->Rows->get_Item(i)->get_Item(S"EXCEL_ID")->ToString())){
iSheetBuf = Convert::ToInt32(oDataSet->Tables->Item["ExcelData2"]->Rows->get_Item(i)->get_Item(S"EXCEL_ID")->ToString());
static_cast<Excel::Worksheet*>(pExcel->ActiveWorkbook->Sheets->Item[__box(iSheetBuf + 1)])->Select(oMissing);
pActiveSheet = static_cast<Excel::Worksheet*>(pExcel->ActiveSheet);
//Init Sheet
pActiveCell = static_cast<Excel::Range*>(pActiveSheet->get_Range(S"A6", S"F6"));
pActiveCell->Interior->set_ColorIndex(__box(15));
pActiveSheet->Cells->set_Item(__box(6), __box(1), S"Parts code");
pActiveSheet->Cells->set_Item(__box(6), __box(2), S"Parts name");
pActiveSheet->Cells->set_Item(__box(6), __box(3), S"Total price");
pActiveSheet->Cells->set_Item(__box(6), __box(4), S"Total QTY");
pActiveSheet->Cells->set_Item(__box(6), __box(5), S"Total RMB");
pActiveSheet->Cells->set_Item(__box(6), __box(6), S"Total lost");
iLinePos = 7;
}
//Cell Edit
sBuf = oDataSet->Tables->Item["ExcelData2"]->Rows->get_Item(i)->get_Item(S"PRODUCT_CODE")->ToString();
pActiveSheet->Cells->set_Item(__box(iLinePos), __box(1), sBuf);
sBuf = oDataSet->Tables->Item["ExcelData2"]->Rows->get_Item(i)->get_Item(S"PARTS_NAME")->ToString();
pActiveSheet->Cells->set_Item(__box(iLinePos), __box(2), sBuf);
sBuf = oDataSet->Tables->Item["ExcelData2"]->Rows->get_Item(i)->get_Item(S"TOTAL_PRICE")->ToString();
pActiveSheet->Cells->set_Item(__box(iLinePos), __box(3), sBuf);
sBuf = oDataSet->Tables->Item["ExcelData2"]->Rows->get_Item(i)->get_Item(S"TOTAL_QTY")->ToString();
pActiveSheet->Cells->set_Item(__box(iLinePos), __box(4), sBuf);
sBuf = oDataSet->Tables->Item["ExcelData2"]->Rows->get_Item(i)->get_Item(S"TOTAL_RMB")->ToString();
pActiveSheet->Cells->set_Item(__box(iLinePos), __box(5), sBuf);
sBuf = oDataSet->Tables->Item["ExcelData2"]->Rows->get_Item(i)->get_Item(S"TOTAL_LOST")->ToString();
pActiveSheet->Cells->set_Item(__box(iLinePos), __box(6), sBuf);
iLinePos++;
}
//Loop ExcelSQL3
OutputExcelSQL3(sSQL);
oDataSet = oDB->dSetSQL_Select(sSQL, S"ExcelData3", 0);
oDataTable = oDataSet->Tables->Item["ExcelData3"];
oDataRC = oDataTable->Rows;
iRecordCount3 = oDataRC->Count;
static_cast<Excel::Worksheet*>(pExcel->ActiveWorkbook->Sheets->Item[__box(1)])->Select(oMissing);
pActiveSheet = static_cast<Excel::Worksheet*>(pExcel->ActiveSheet);
//Init Sheet
pActiveCell = static_cast<Excel::Range*>(pActiveSheet->get_Range(S"A1", S"F1"));
pActiveCell->Interior->set_ColorIndex(__box(15));
pActiveSheet->Cells->set_Item(__box(1), __box(1), S"Parts code");
pActiveSheet->Cells->set_Item(__box(1), __box(2), S"Parts name");
pActiveSheet->Cells->set_Item(__box(1), __box(3), S"Total price");
pActiveSheet->Cells->set_Item(__box(1), __box(4), S"Total QTY");
pActiveSheet->Cells->set_Item(__box(1), __box(5), S"Total RMB");
pActiveSheet->Cells->set_Item(__box(1), __box(6), S"Total lost");
iLinePos = 2;
for(i= 0;i < iRecordCount3; i++){
//Cell Edit
sBuf = oDataSet->Tables->Item["ExcelData3"]->Rows->get_Item(i)->get_Item(S"PRODUCT_CODE")->ToString();
pActiveSheet->Cells->set_Item(__box(iLinePos), __box(1), sBuf);
sBuf = oDataSet->Tables->Item["ExcelData3"]->Rows->get_Item(i)->get_Item(S"PARTS_NAME")->ToString();
pActiveSheet->Cells->set_Item(__box(iLinePos), __box(2), sBuf);
sBuf = oDataSet->Tables->Item["ExcelData3"]->Rows->get_Item(i)->get_Item(S"TOTAL_PRICE")->ToString();
pActiveSheet->Cells->set_Item(__box(iLinePos), __box(3), sBuf);
sBuf = oDataSet->Tables->Item["ExcelData3"]->Rows->get_Item(i)->get_Item(S"TOTAL_QTY")->ToString();
pActiveSheet->Cells->set_Item(__box(iLinePos), __box(4), sBuf);
sBuf = oDataSet->Tables->Item["ExcelData3"]->Rows->get_Item(i)->get_Item(S"TOTAL_RMB")->ToString();
pActiveSheet->Cells->set_Item(__box(iLinePos), __box(5), sBuf);
sBuf = oDataSet->Tables->Item["ExcelData3"]->Rows->get_Item(i)->get_Item(S"TOTAL_LOST")->ToString();
pActiveSheet->Cells->set_Item(__box(iLinePos), __box(6), sBuf);
iLinePos++;
}
//Save File
sBuf = "";
sBuf = String::Concat(S"C:\\", comboBoxPType->Text, comboBoxPeriod->Text, S".xls");
pExcel->ActiveWorkbook->SaveAs(sBuf, __box(Excel::XlFileFormat::xlExcel9795),
oMissing, oMissing, oMissing, oMissing, Excel::XlSaveAsAccessMode::xlNoChange,
oMissing, oMissing, oMissing, oMissing, oMissing);
//Quit Excel
pWorkBook->Close(false, oMissing, oMissing);
System::Runtime::InteropServices::Marshal::ReleaseComObject(pWorkBook);
pExcel->Quit();
System::Runtime::InteropServices::Marshal::ReleaseComObject(pExcel);
GC::Collect();
}
catch(Exception* e) {
e->get_Message();
}
}
void BalanceOfPayment::OutputExcelSQL1(String* & sSQL)
{
String* sSYear;
String* sEYear;
String* sSMonth;
String* sEMonth;
DateTime dtNow;
int iPT, iPe;
int iaSYe[4][12] = {{-1,-2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{-1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{-1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{-1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}};
int iaEYe[4][12] = {{ 0,-1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}};
int iaSMo[4][12] = {{12,12, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1},
{12, 6, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1},
{12, 3, 6, 9, 1, 1, 1, 1, 1, 1, 1, 1},
{12, 1, 2, 3, 4, 5, 6, 7, 8, 9,10,11}};
int iaEMo[4][12] = {{12,12, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1},
{ 6,12, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1},
{ 3, 6, 9,12, 1, 1, 1, 1, 1, 1, 1, 1},
{ 1, 2, 3, 4, 5, 6, 7, 8, 9,10,11,12}};
dtNow = DateTime::Now;
iPT = comboBoxPType->SelectedIndex;
iPe = comboBoxPeriod->SelectedIndex;
sSYear =String::Format("{0}", __box(dtNow.Year + iaSYe[iPT][iPe]));
sEYear =String::Format("{0}", __box(dtNow.Year + iaEYe[iPT][iPe]));
sSMonth =String::Format("{0}", __box(iaSMo[iPT][iPe]));
sEMonth =String::Format("{0}", __box(iaEMo[iPT][iPe]));
// //***** begin **************************//
// //* add by DongZhaoLiang in 2006/4/28 *//
// //***** begin **************************//
// if(sSMonth->Length==1)
// {
// sTemp=sSMonth;
// sSMonth=S"0";
// sSMonth = String::Concat(sSMonth,sTemp);
// }
//if(sEMonth->Length==1)
// {
// sTemp=sEMonth;
// sEMonth=S"0";
// sEMonth = String::Concat(sEMonth,sTemp);
// }
// //***** end ***************************//
// //* add by DongZhaoLiang in 2006/4/28 *//
// //***** end ***************************//
//Get Data
sSQL = S"";
sSQL = String::Concat(sSQL, S"select" );
sSQL = String::Concat(sSQL, S" EID.EXCEL_ID," );
sSQL = String::Concat(sSQL, S" EID.PROCESS_NO," );
sSQL = String::Concat(sSQL, S" EID.TEAM," );
sSQL = String::Concat(sSQL, S" nvl(TOT.TOTAL_NUMBER, 0) TOTAL_NUMBER," );
sSQL = String::Concat(sSQL, S" nvl(TOT.TOTAL_COST, 0) TOTAL_COST " );
sSQL = String::Concat(sSQL, S"from" );
sSQL = String::Concat(sSQL, S" (" );
sSQL = String::Concat(sSQL, S" select" );
sSQL = String::Concat(sSQL, S" TOT.PROCESS_NO," );
sSQL = String::Concat(sSQL, S" TOT.TEAM," );
sSQL = String::Concat(sSQL, S" SUM(TOT.TOTAL_NUMBER) as TOTAL_NUMBER," );
sSQL = String::Concat(sSQL, S" SUM(TOT.TOTAL_COST) as TOTAL_COST " );
sSQL = String::Concat(sSQL, S" from" );
sSQL = String::Concat(sSQL, S" (" );
sSQL = String::Concat(sSQL, S" select" );
sSQL = String::Concat(sSQL, S" LC.PROCESS_NO," );
sSQL = String::Concat(sSQL, S" to_char(LC.WORK_YMD, 'YYYYMMDD') ||" );
sSQL = String::Concat(sSQL, S" decode(LC.TEAM, WC.DAY_SHIFT, '1200', '0000') YMD," );
sSQL = String::Concat(sSQL, S" LC.TEAM," );
sSQL = String::Concat(sSQL, S" LC.TOTAL_NUMBER," );
sSQL = String::Concat(sSQL, S" (LC.TOTAL_NUMBER * LR.COST) as TOTAL_COST" );
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -