Beispiel #1
0
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;
}
Beispiel #2
0
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();
}