コード例 #1
0
ファイル: excel_tool.cpp プロジェクト: no2key/tokit
	bool ole_open_excel(const string &xlsx, CApplication &excel_application, CWorkbooks &books, CWorkbook &book, CWorksheets &sheets)
	{
        if(!init_ole()){
            return false;
        }

		if(false == filetool::exist(xlsx)){
			std::string err = xlsx + "文件不存在!";
			AfxMessageBox(strtool::string2wstring(err).c_str());
			return false;
		}

		//创建Excel 服务器(启动Excel)
		if(!excel_application.CreateDispatch(_T("Excel.Application"),NULL)){
			AfxMessageBox(_T("启动Excel服务器失败!"));
			return false;
		}

		excel_application.put_Visible(FALSE);
        excel_application.put_DisplayAlerts(FALSE);
		excel_application.put_UserControl(FALSE);

		//得到工作簿容器
		books.AttachDispatch(excel_application.get_Workbooks(), TRUE);

        // const std::wstring wstr = 
        const std::wstring w_xlsx = strtool::string2wstring(xlsx); //L"E:\\proj\\github_tokit\\trunk\\example\\excel\\测试用例.xlsx";

		//打开一个工作簿,如不存在,则新增一个工作簿
        LPDISPATCH lpDisp = books.Add(COleVariant(w_xlsx.c_str()));
        if (lpDisp){
            book.AttachDispatch(lpDisp);

            // 得到工作簿中的Sheet的容器
            sheets.AttachDispatch(book.get_Sheets());
        }

		return true;
	}
コード例 #2
0
void CLogAnalyzerView::OnGeneratereportExcel()
{
#ifdef EXCEL_REPORT
    try
    {
        if (m_logger->GetFileName().empty())
            return;

        CApplication app;
        CWorkbook exlBook;  
        CWorkbooks exlBooks;  
        CWorksheet exlSheet;  
        CWorksheets exlSheets;
        CChart chart;
        CRange range;
        CChartObjects chartobjects;
        LPDISPATCH lpDisp;

        COleVariant covTrue((short)TRUE), covFalse((short)FALSE), covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

        // Start Excel and get the Application object.
        if(!app.CreateDispatch("Excel.Application"))
        {
            ERROR_MESSAGEBOX("Couldn't start Excel and get an application 0bject");
            return;
        }

        app.put_Visible(TRUE);

        // Get Workbooks collection.
        lpDisp = app.get_Workbooks();
        ASSERT(lpDisp);
        // Attach the IDispatch pointer
        exlBooks.AttachDispatch(lpDisp);
        exlBook = exlBooks.Add(covOptional);

        // Get sheets.
        lpDisp = exlBook.get_Sheets();
        ASSERT(lpDisp);
        exlSheets.AttachDispatch(lpDisp);

        lpDisp = exlSheets.get_Item( COleVariant((short)(1)) );
        ASSERT(lpDisp);
        // Attach the lpDisp pointer to a Worksheet object.
        exlSheet.AttachDispatch(lpDisp);

        const std::map<tstring, Poco::SharedPtr<CComponent>>& componentMap = m_logger->GetComponents();
        char charPos[32];
        int nSize = 0;
        if (componentMap.size() > 0)
        {   
            std::map<tstring, Poco::SharedPtr<CComponent>>::const_iterator ite = componentMap.begin();
            int nIndex = 3;

            while (ite != componentMap.end())
            {
                if (ite->second->GetTraceLogs().size() > 0)
                {
                    sprintf(charPos, "A%d", nIndex);
                    lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
                    ASSERT(lpDisp);
                    range.AttachDispatch(lpDisp);  // Attach the IDispatch pointer
                    range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(ite->second->GetComponentName().c_str()));
                    range.put_Value2(COleVariant(ite->second->GetComponentName().c_str()));

                    int nTrace = 0, nNotification = 0, nError = 0, nInformation = 0, nDebug = 0, nCritical = 0, nWarning = 0, nFatal = 0;
                    if (nSize == 0)
                    {
                        nTrace = 10;
                        nNotification = 10;
                        nError = 5, nInformation = 50, nDebug = 2, nCritical = 2, nWarning = 9, nFatal = 1;
                    }
                    else if (nSize == 1)
                    {
                        nTrace = 10;
                        nNotification = 25;
                        nError = 4, nInformation = 36, nDebug = 5, nCritical = 0, nWarning = 20, nFatal = 1;
                    }
                    else if (nSize == 2)
                    {
                        nTrace = 0;
                        nNotification = 5;
                        nError = 4, nInformation = 100, nDebug = 5, nCritical = 5, nWarning = 15, nFatal = 0;
                    }
                    else if (nSize == 3)
                    {
                        nTrace = 0;
                        nNotification = 5;
                        nError = 4, nInformation = 82, nDebug = 36, nCritical = 1, nWarning = 50, nFatal = 0;
                    }
                    //ite->second->getLogTypeCount(nTrace, nNotification, nError, nInformation, nDebug, nCritical, nWarning, nFatal);
                    //Trace
                    sprintf(charPos, "B%d", nIndex);
                    lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
                    range.AttachDispatch(lpDisp);
                    sprintf(charPos, "%d", nTrace);
                    range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
                    range.put_Value2(COleVariant(charPos));
                    //Debug
                    sprintf(charPos, "C%d", nIndex);
                    lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
                    range.AttachDispatch(lpDisp);
                    sprintf(charPos, "%d", nDebug);
                    range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
                    range.put_Value2(COleVariant(charPos));
                    //Information
                    sprintf(charPos, "D%d", nIndex);
                    lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
                    range.AttachDispatch(lpDisp);
                    sprintf(charPos, "%d", nInformation);
                    range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
                    range.put_Value2(COleVariant(charPos));
                    //Warning
                    sprintf(charPos, "E%d", nIndex);
                    lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
                    range.AttachDispatch(lpDisp);
                    sprintf(charPos, "%d", nWarning);
                    range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
                    range.put_Value2(COleVariant(charPos));
                    //Error
                    sprintf(charPos, "F%d", nIndex);
                    lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
                    range.AttachDispatch(lpDisp);
                    sprintf(charPos, "%d", nError);
                    range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
                    range.put_Value2(COleVariant(charPos));
                    //Critical
                    sprintf(charPos, "G%d", nIndex);
                    lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
                    range.AttachDispatch(lpDisp);
                    sprintf(charPos, "%d", nCritical);
                    range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
                    range.put_Value2(COleVariant(charPos));
                    //Notification
                    sprintf(charPos, "H%d", nIndex);
                    lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
                    range.AttachDispatch(lpDisp);
                    sprintf(charPos, "%d", nNotification);
                    range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
                    range.put_Value2(COleVariant(charPos));
                    //Fatal
                    sprintf(charPos, "I%d", nIndex);
                    lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
                    range.AttachDispatch(lpDisp);
                    sprintf(charPos, "%d", nFatal);
                    range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
                    range.put_Value2(COleVariant(charPos));
                    ++nIndex;
                    ++nSize;
                }
                ++ite;
            }
        }

        lpDisp = exlSheet.get_Range(COleVariant("B2"), COleVariant("B2"));
        ASSERT(lpDisp);
        range.AttachDispatch(lpDisp);
        range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("TRACE"));
        range.put_Value2(COleVariant("TRACE"));

        lpDisp = exlSheet.get_Range(COleVariant("C2"), COleVariant("C2"));
        ASSERT(lpDisp);
        range.AttachDispatch(lpDisp);
        range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("DEBUG"));
        range.put_Value2(COleVariant("DEBUG"));

        lpDisp = exlSheet.get_Range(COleVariant("D2"), COleVariant("D2"));
        ASSERT(lpDisp);
        range.AttachDispatch(lpDisp);
        range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("INFORMATION"));
        range.put_Value2(COleVariant("INFORMATION"));

        lpDisp = exlSheet.get_Range(COleVariant("E2"), COleVariant("E2"));
        ASSERT(lpDisp);
        range.AttachDispatch(lpDisp);
        range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("WARNING"));
        range.put_Value2(COleVariant("WARNING"));

        lpDisp = exlSheet.get_Range(COleVariant("F2"), COleVariant("F2"));
        ASSERT(lpDisp);
        range.AttachDispatch(lpDisp);
        range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("ERROR"));
        range.put_Value2(COleVariant("ERROR"));

        lpDisp = exlSheet.get_Range(COleVariant("G2"), COleVariant("G2"));
        ASSERT(lpDisp);
        range.AttachDispatch(lpDisp);
        range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("CRITICAL"));
        range.put_Value2(COleVariant("CRITICAL"));

        lpDisp = exlSheet.get_Range(COleVariant("H2"), COleVariant("H2"));
        ASSERT(lpDisp);
        range.AttachDispatch(lpDisp);
        range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("NOTIFICATION"));
        range.put_Value2(COleVariant("NOTIFICATION"));

        lpDisp = exlSheet.get_Range(COleVariant("I2"), COleVariant("I2"));
        ASSERT(lpDisp);
        range.AttachDispatch(lpDisp);
        range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("FATAL"));
        range.put_Value2(COleVariant("FATAL"));

        // The cells are populated. To start the chart,
        // declare some long variables and site the chart.
        long left, top, width, height;
        left = 200;
        top = 200;
        width = 450;
        height = 350;

        lpDisp = exlSheet.ChartObjects(covOptional);

        ASSERT(lpDisp);
        chartobjects.AttachDispatch(lpDisp); // Attach the lpDisp pointer
        // for ChartObjects to the chartobjects
        // object.
        CChartObject chartobject = chartobjects.Add(left, top, width, height); 
        //defines the rectangle, 
        // adds a new chart at that rectangle and 
        // assigns its object reference to a 
        // ChartObject variable named chartobject
        chart.AttachDispatch(chartobject.get_Chart());

        sprintf(charPos, "I%d", nSize + 2);
        lpDisp = exlSheet.get_Range(COleVariant("A2"), COleVariant(charPos));
        // The range containing the data to be charted.
        ASSERT(lpDisp);
        range.AttachDispatch(lpDisp);

        VARIANT var; // ChartWizard needs a Variant for the Source range.
        var.vt = VT_DISPATCH; // .vt is the usable member of the tagVARIANT
        var.pdispVal = lpDisp; // Assign IDispatch pointer

        chart.ChartWizard(var,                    // Source.
            COleVariant((short)52),  // Gallery: stacked column.
            covOptional,             // Format, use default.
            COleVariant((short)1),   // PlotBy: xlRows.
            COleVariant((short)1),   // CategoryLabels.
            COleVariant((short)1),   // SeriesLabels.
            COleVariant((short)TRUE),  // HasLegend.
            COleVariant("Trace Report"),  // Title.
            COleVariant("Components"),    // CategoryTitle.
            COleVariant("Trace Count"),  // ValueTitles.
            covOptional              // ExtraTitle.
            );

        range.ReleaseDispatch();   
        exlSheet.ReleaseDispatch();   
        exlSheets.ReleaseDispatch();   
        exlBook.ReleaseDispatch();   
        exlBooks.ReleaseDispatch();
        chart.ReleaseDispatch();
        chartobject.ReleaseDispatch();
        app.ReleaseDispatch();
    }
    catch(COleException *e)
    {
        char buf[1024];
        sprintf(buf, "COleException. SCODE: %08lx.", (long)e->m_sc);
        ::MessageBox(NULL, buf, "COleException", MB_SETFOREGROUND | MB_OK);
    }

    catch(COleDispatchException *e)
    {
        char buf[1024];
        sprintf(buf,
            "COleDispatchException. SCODE: %08lx, Description: \"%s\".", (long)e->m_wCode,
            (LPSTR)e->m_strDescription.GetBuffer(1024));
        ::MessageBox(NULL, buf, "COleDispatchException", MB_SETFOREGROUND | MB_OK);
    }

    catch(...)
    {
        ::MessageBox(NULL, "General Exception caught.", "Catch-All", MB_SETFOREGROUND | MB_OK);
    }
#endif
}