extern "C" BOOL LoadExcelFile(const TCHAR* filename)
{
// Load the Excel application in the background.
Excel::_ApplicationPtr pApplication;
if ( FAILED( pApplication.CreateInstance( _T("Excel.Application") ) ) )
{
Errorf( _T("Failed to initialize Excel::_Application!") );
return FALSE;
}
_variant_t varOption( (long) DISP_E_PARAMNOTFOUND, VT_ERROR );
_ftprintf(stdout, _T("%s\n"), filename);
if (!pApplication || !pApplication->Workbooks) {
Errorf(_T("Workbooks is empty\n"));
return FALSE;
}
Excel::_WorkbookPtr pBook = pApplication->Workbooks->Open(filename);//, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption );
if ( pBook == NULL )
{
Errorf( _T("Failed to open Excel file!") );
return FALSE;
}
pApplication->PutVisible(0, FALSE);
Excel::_WorksheetPtr pSheet = pBook->Sheets->Item[1];
if ( pSheet == NULL )
{
Errorf( _T("Failed to get first Worksheet!") );
return FALSE;
}
Excel::RangePtr pRange = pSheet->Cells;
for (int i = 24; i < 29; ++i) {
for (int j = 3; j < 27; ++j) {
pRange->Item[i][j] = j / 10 + i / 100.f;
}
}
#if 0
fprintf(stdout, "2\n");
// Load the column headers.
Excel::RangePtr pRange = pSheet->GetRange( _bstr_t( _T("A1") ), _bstr_t( _T("Z1" ) ) );
if ( pRange == NULL )
{
Errorf( _T("Failed to get header cell range( A1:Z1 )!") );
return FALSE;
}
int iColumns = 0;
for ( int iColumn = 1; iColumn < 26; ++iColumn )
{
_variant_t vItem = pRange->Item[ 1 ][ iColumn ];
_bstr_t bstrText( vItem );
if ( bstrText.length( ) == 0 )
break;
//m_list.AddColumn( bstrText, iColumns++ );
//_ftprintf(stdout, _T("%s\n"), bstrText);
Errorf(bstrText);
}
// Load the rows (up to the first blank one).
pRange = pSheet->GetRange( _bstr_t( _T("A2") ), _bstr_t( _T("Z16384" ) ) );
for ( int iRow = 1; ; ++iRow )
{
int iColumn;
for ( iColumn = 1; iColumn <= iColumns; ++iColumn )
{
_variant_t vItem = pRange->Item[ iRow ][ iColumn ];
_bstr_t bstrText( vItem );
if ( bstrText.length( ) == 0 )
break;
if ( iColumn == 1 )
;//m_list.AddItem( iRow - 1, 0, bstrText );
else
;//m_list.SetItemText( iRow - 1, iColumn - 1, bstrText );
}
if ( iColumn == 1 )
break;
}
pRange->Item[ 1 ][ 1 ] = 1234;
#endif
// Make it all look pretty.
//for ( int iColumn = 1; iColumn <= iColumns; ++iColumn )
//m_list.SetColumnWidth( iColumn, LVSCW_AUTOSIZE_USEHEADER );
// Switch off alert prompting to save as
pApplication->PutDisplayAlerts( LOCALE_USER_DEFAULT, VARIANT_FALSE );
// Save the values in book.xml
pSheet->SaveAs(_T("d:\\tesstbook.xls"));
pApplication->PutDisplayAlerts( LOCALE_USER_DEFAULT, VARIANT_TRUE );
// Don't save any inadvertant changes to the .xls file.
pBook->Close( VARIANT_FALSE );
// And switch back on again...
// Need to quit, otherwise Excel remains active and locks the .xls file.
pApplication->Quit( );
return TRUE;
}
void CDlgReportMounth::OnBnClickedOk()
{
CString sStart, sEnd;
if(m_ComboYear.GetCurSel()<0)
{
AfxMessageBox(_T("Ќе выбран год"));
return;
}
int iYear;
iYear = m_ComboYear.GetItemData(m_ComboYear.GetCurSel());
if((iYear < 2000)||(iYear>2019))
{
AfxMessageBox(_T("ќшибка в программе"));
return;
}
if(m_ComboMounth.GetCurSel()<0)
{
AfxMessageBox(_T("Ќе выбранн мес¤ц"));
return;
}
int iMounth;
iMounth = m_ComboMounth.GetItemData(m_ComboMounth.GetCurSel());
if((iMounth < 1)||(iMounth>12))
{
AfxMessageBox(_T("ќшибка в программе"));
return;
}
CRepScanApp *pApp;
pApp = (CRepScanApp*)AfxGetApp();
if(pApp->dBase == NULL)
{
AfxMessageBox(_T("Ќет доступа к Ѕƒ"));
return ;
}
CString sSelectDate;
if(iMounth < 10)
sSelectDate.Format(_T("%d0%d"),iYear,iMounth);
else
sSelectDate.Format(_T("%d%d"),iYear,iMounth);
try
{
CString sSQL;
CRecordset Query(pApp->dBase);
CDBVariant oValue;
/*sSQL.Format(_T("select name, (Right(left(name,8),4)+Right(left(name,4),2)+left(name,2)) as dat from sysobjects where Right(name,9) = '%s_BQ'"),sSelectDate);
Query.Open(CRecordset::forwardOnly,sSQL,CRecordset::readOnly);
sSQL = _T("");
CString NewDate;
while(!Query.IsEOF())
{
Query.GetFieldValue(_T("name"),oValue);
CString sDate;
sDate = GetValue(&oValue);
sDate = sDate.Left(8);
NewDate = sDate.Right(4);
sDate = sDate.Left(4);
NewDate = NewDate + sDate.Right(2)+sDate.Left(2);
if(sSQL =="")
sSQL = _T("select Employee, count(test.con) as ConDay,id_zone, '")+NewDate+_T("' as dat from (select Employee,id_doc,id_zone, sort,task, 1 as con from [")+GetValue(&oValue)+_T("] group by Employee,id_doc,id_zone,sort,task) as test group by Employee ,id_zone");
else
sSQL = sSQL + _T("\n union select Employee, count(test.con) as ConDay,id_zone, '")+NewDate+_T("' as dat from (select Employee,id_doc,id_zone, sort,task, 1 as con from [")+GetValue(&oValue)+_T("] group by Employee,id_doc,id_zone,sort,task) as test group by Employee ,id_zone");
Query.MoveNext();
}
Query.Close();
if(sSQL.GetLength()<1)
return;
sSQL = _T("select Employee, id_zone,ConDay, dat from (")+sSQL+_T(") as testday order by Employee, dat, id_zone");
*/
/*
Registered Whse. Activity Line
*/
/*sSQL = _T("select [Assigned User ID] as Employee,[Zone Code] as id_zone,count(*) as ConDay from ( SELECT [Source Document No_],[Pick No_],[Assigned User ID],[Item No_],[Zone Code],[From Bin Code] FROM [")+sDatabase;
sSQL = sSQL + _T("$Picking Operation Register] join [")+sDatabase;
sSQL = sSQL + _T("$bin] on [code] = [From Bin Code] where Left(CONVERT ( nchar , [Entry Date], 112),6) = '")+sSelectDate;
sSQL = sSQL + _T("' group by [Source Document No_],[Pick No_],[Assigned User ID],[Item No_],[Zone Code],[From Bin Code] ) as TestTab group by [Assigned User ID],[Zone Code]order by Employee,id_zone");
*/
sSQL = _T("select [Assigned User ID] as Employee ,[Zone Code] as id_zone, count(*) as ConDay from (select [Assigned User ID], hd.[No_],[Line No_],ln.[Zone Code] from [")+sDatabase;
sSQL = sSQL +_T("$Warehouse Zone Employee] join[")+sDatabase;
sSQL = sSQL + _T("$Registered Whse_ Activity Hdr_] as hd on [User ID] =[Assigned User ID] join [")+sDatabase;
sSQL = sSQL + _T("$Registered Whse_ Activity Line] as ln on ln.[No_] = hd.[No_] and [Action Type] = 1 where Left(CONVERT ( nchar , [Registering Date], 112),6) = '")+sSelectDate;
sSQL = sSQL + _T("' and [Type] = 2 group by [Assigned User ID],hd.[No_],[Line No_],ln.[Zone Code]) as Test group by [Assigned User ID],[Zone Code] order by [Assigned User ID],[Zone Code]");
Excel::WorkbooksPtr ExcelBooks;
Excel::_WorkbookPtr ExcelBook;
Excel::_WorksheetPtr ExcelSheet;
Excel::RangePtr range;
appExcel.CreateInstance( _T("Excel.Application"));
VARIANT bTRUE;
bTRUE.vt = 11;
bTRUE.boolVal = TRUE;
appExcel->Visible[0] = FALSE;
ExcelBook= appExcel->Workbooks->Add();
ExcelSheet = ExcelBook->Worksheets->Item[1];
Query.Open(CRecordset::forwardOnly,sSQL,CRecordset::readOnly);
int iRow;
iRow = 1;
CString sName;
CString oldMan;
int iCol;
int iValue;
iCol=2;
CString sDat;
sDat = _T("—борщики");
ExcelSheet->Cells->Item[1,1] = sDat.AllocSysString();
sDat = _T("«она1");
ExcelSheet->Cells->Item[1,2] = sDat.AllocSysString();
sDat = _T("«она2");
ExcelSheet->Cells->Item[1,3] = sDat.AllocSysString();
sDat = _T("«она3");
ExcelSheet->Cells->Item[1,4] = sDat.AllocSysString();
sDat = _T("«она4");
ExcelSheet->Cells->Item[1,5] = sDat.AllocSysString();
sDat = _T("«она5");
ExcelSheet->Cells->Item[1,6] = sDat.AllocSysString();
sDat = _T("«она1нс");
ExcelSheet->Cells->Item[1,7] = sDat.AllocSysString();
sDat = _T("«она2нс");
ExcelSheet->Cells->Item[1,8] = sDat.AllocSysString();
sDat = _T("«она3нс");
ExcelSheet->Cells->Item[1,9] = sDat.AllocSysString();
sDat = _T("«она4нс");
ExcelSheet->Cells->Item[1,10] = sDat.AllocSysString();
sDat = _T("«она5нс");
ExcelSheet->Cells->Item[1,11] = sDat.AllocSysString();
sDat = _T("Ќеизвестна¤ зона");
ExcelSheet->Cells->Item[1,12] = sDat.AllocSysString();
sDat = _T("—умма строк");
ExcelSheet->Cells->Item[1,13] = sDat.AllocSysString();
sDat = _T("USD");
ExcelSheet->Cells->Item[1,14] = sDat.AllocSysString();
Excel::BordersPtr borders;
sDat = _T("—тавки");
ExcelSheet->Cells->Item[1,16] = sDat.AllocSysString();
range = ExcelSheet->GetRange(ExcelSheet->Cells->Item[1,16],ExcelSheet->Cells->Item[2,27]);
borders = range->GetBorders();
borders->PutLineStyle(Excel::xlContinuous);
borders->PutWeight(Excel::xlThin);
borders->PutColorIndex(Excel::xlAutomatic);
//—тавки 1 «она 2 «она 3 «она 4 «она 5 «она Ќеизвестна¤ зона
// 0.08775 0.053625 0.053625 0.053625 0
//—тавки «она1нс «она2нс «она3нс «она4нс «она5нс
// 0.08775 0.053625 0.053625 0.053625 0.053625
sDat = _T("1 «она");
ExcelSheet->Cells->Item[1,17] = sDat.AllocSysString();
//sDat = _T("0.08775");
sDat = sReadFromIni(_T("Rate"),_T("Z01"),_T("0"));
ExcelSheet->Cells->Item[2,17] = sDat.AllocSysString();
sDat = _T("2 «она");
ExcelSheet->Cells->Item[1,18] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("Z02"),_T("0"));
ExcelSheet->Cells->Item[2,18] = sDat.AllocSysString();
sDat = _T("3 «она");
ExcelSheet->Cells->Item[1,19] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("Z03"),_T("0"));
ExcelSheet->Cells->Item[2,19] = sDat.AllocSysString();
sDat = _T("4 «она");
ExcelSheet->Cells->Item[1,20] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("Z04"),_T("0"));
ExcelSheet->Cells->Item[2,20] = sDat.AllocSysString();
sDat = _T("5 «она");
ExcelSheet->Cells->Item[1,21] = sDat.AllocSysString();
//sDat = _T("0");
sDat = sReadFromIni(_T("Rate"),_T("PAL"),_T("0"));
ExcelSheet->Cells->Item[2,21] = sDat.AllocSysString();
sDat = _T("1нс «она");
ExcelSheet->Cells->Item[1,22] = sDat.AllocSysString();
//sDat = _T("0.08775");
sDat = sReadFromIni(_T("Rate"),_T("MZ01"),_T("0"));
ExcelSheet->Cells->Item[2,22] = sDat.AllocSysString();
sDat = _T("2нс «она");
ExcelSheet->Cells->Item[1,23] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("MZ02"),_T("0"));
ExcelSheet->Cells->Item[2,23] = sDat.AllocSysString();
sDat = _T("3нс «она");
ExcelSheet->Cells->Item[1,24] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("MZ03"),_T("0"));
ExcelSheet->Cells->Item[2,24] = sDat.AllocSysString();
sDat = _T("4нс «она");
ExcelSheet->Cells->Item[1,25] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("MZ04"),_T("0"));
ExcelSheet->Cells->Item[2,25] = sDat.AllocSysString();
sDat = _T("5нс «она");
ExcelSheet->Cells->Item[1,26] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("MZ05"),_T("0"));
ExcelSheet->Cells->Item[2,26] = sDat.AllocSysString();
sDat = _T("Ќеизвестна¤ зона");
ExcelSheet->Cells->Item[1,27] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("UNKNOWN"),_T("0"));
ExcelSheet->Cells->Item[2,27] = sDat.AllocSysString();
COleDateTime cDate,datEnd;
_variant_t oVal;
while(!Query.IsEOF())
{
Query.GetFieldValue(_T("Employee"),oValue);
if(GetValue(&oValue)!=oldMan)
{
iRow++;
sName = GetValue(&oValue);
ExcelSheet->Cells->Item[iRow,1] = sName.AllocSysString();
oldMan = sName;
}
//id_zone
Query.GetFieldValue(_T("id_zone"),oValue);
sName = GetValue(&oValue);
iCol = -1;
if(sName == _T("Z01"))
{
iCol = 2;
}
if(sName == _T("Z02"))
{
iCol = 3;
}
if(sName == _T("Z03"))
{
iCol = 4;
}
if(sName == _T("Z04"))
{
iCol = 5;
}
if(sName == _T("PAL"))
{
iCol = 6;
}
if(sName == _T("MZ01"))
{
iCol = 7;
}
if(sName == _T("MZ02"))
{
iCol = 8;
}
if(sName == _T("MZ03"))
{
iCol = 9;
}
if(sName == _T("MZ04"))
{
iCol = 10;
}
if(sName == _T("MZ05"))
{
iCol = 11;
}
if(iCol < 0)
{
iCol = 12;
}
Query.GetFieldValue(_T("ConDay"),oValue);
iValue = GetValueID(&oValue);
//ExcelSheet->Cells->Item[iRow,iCol] = sName.AllocSysString();
range = ExcelSheet->Cells->Item[iRow,iCol];
oVal = range->GetValue2();
if(oVal.vt != 0)
{
oVal.ChangeType(VT_I8);
iValue = iValue+oVal.lVal;
}
sName.Format(_T("%d"),iValue);
ExcelSheet->Cells->Item[iRow,iCol] = sName.AllocSysString();
range = ExcelSheet->Cells->Item[iRow, 13];
range->PutFormula(_T("=SUM(RC[-11]:RC[-1])"));
sName.Format(_T("=RC[-12]*R[-%d]C17 + RC[-11]*R[-%d]C18 + RC[-10]*R[-%d]C19 + RC[-9]*R[-%d]C20 + RC[-8]*R[-%d]C21 + RC[-7]*R[-%d]C22 + RC[-6]*R[-%d]C23 + RC[-5]*R[-%d]C24 + RC[-4]*R[-%d]C25 + RC[-3]*R[-%d]C26 + RC[-2]*R[-%d]C27"),iRow-2,iRow-2,iRow-2,iRow-2,iRow-2,iRow-2,iRow-2,iRow-2,iRow-2,iRow-2,iRow-2);
/*
=RC[-6]*R[-2]C12+RC[-5]*R[-2]C13+RC[-4]*R[-2]C14
*/
range = ExcelSheet->Cells->Item[iRow, 14];
range->PutFormula(sName.AllocSysString());
Query.MoveNext();
}
Query.Close();
//=—”ћћ(R[-169]C:R[-1]C)
if(iRow > 1)
{
iRow++;
sName.Format(_T("»того:"));
ExcelSheet->Cells->Item[iRow, 1] = sName.AllocSysString();
sName.Format(_T("=SUM(R[-%d]C:R[-1]C)"),iRow-2);
range = ExcelSheet->Cells->Item[iRow,2];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,3];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,4];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,5];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,6];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,7];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,8];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,9];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,10];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,11];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,12];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,13];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,14];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->GetRange(ExcelSheet->Cells->Item[1, 1], ExcelSheet->Cells->Item[iRow, 14]);
//range->PutFormula(sName.AllocSysString());
borders = range->GetBorders();
borders->PutLineStyle(Excel::xlContinuous);
borders->PutWeight(Excel::xlThin);
borders->PutColorIndex(Excel::xlAutomatic);
range = ExcelSheet->GetRange(ExcelSheet->Cells->Item[2, 14], ExcelSheet->Cells->Item[iRow, 14]);
range->Interior->Color = RGB(200, 160, 35);
}
appExcel->Visible[0] = TRUE;
}
catch(CDBException *exsept)
{
AfxMessageBox(exsept->m_strError);
exsept->Delete();
return ;
}
OnOK();
}