Page 1 of 1

wxAutoExcel: MS Excel automation with wxWidgets made easier

Posted: Thu Feb 23, 2017 4:54 pm
by PB
Library name: wxAutoExcel
Purpose: To make automating Microsoft Excel OLE with C++ less tedious.
Platform: Microsoft Windows only
wxWidgets: Version 3.1 or newer, older versions lack necessary features and important bug fixes.
Other requirements: As the purpose of the library is to automate Microsoft Excel, it requires a functional Microsoft Excel installation
Licence: wxWindows licence
Documentation: Doxygen
Samples provided: Yes (7)
Source web page: https://github.com/pbfordev/wxAutoExcel
Documentation web page: http://pbfordev.github.io/wxAutoExcel/

Example of code using wxAutoExcel, from its minimal sample:

Code: Select all

    // first create an MS Excel instance
    wxExcelApplication app = wxExcelApplication::CreateInstance();
    if ( !app ) 
    {
        wxLogError(_("Failed to create an instance of MS Excel application."));
        return;
    }
    app.SetVisible(true); // display MS Excel window

    // add a new workbook
    wxExcelWorkbook workbook = app.GetWorkbooks().Add();    
    if ( !workbook ) 
    {
        wxLogError(_("Failed to create a new workbook."));
        return;
    }

    // Set the workbook automation object locale to US English, so we can use
    // English names for its formulas, styles etc. in the automation calls,
    // regardless of the language Excel may be localized into.
    // The end user will still see the localized ones in Excel.
    workbook.SetAutomationLCID_(wxExcelObject::lcidEnglishUS);

    // get the first worksheet in the newly added workbook
    // remember that indices in MS Office collections start at 1, NOT 0
    wxExcelWorksheet worksheet = workbook.GetWorksheets()[1];
    if ( !worksheet )
    {
        wxLogError(_("Failed to obtain worksheet number 1."));
        return;
    }
    // change worksheet name
    worksheet.SetName("A Very Silly Table");    

    wxVariant variant;
    wxExcelRange range;

    // write sheet headers
    range = worksheet.GetRange("A1:E1");    
    variant.ClearList();
    variant.Append("Code");
    variant.Append("Date");
    variant.Append("Quantity");
    variant.Append("Price");
    variant.Append("Subtotal");    
    // set cell values
    range.SetValue(variant);
    // set headers to bold
    range.GetFont().SetBold(true);
    // center headers
    range.SetHorizontalAlignment(xlCenter);

    // write the first row of values
    // first shift the range one row down
    range = range.GetOffset(1);
    
    variant.ClearList();
    variant.Append("ABC0123");
    variant.Append(wxDateTime::Today());
    variant.Append(3L);
    variant.Append(DoubleToCurrencyVariant(10.5));
    variant.Append("=C2*D2");    
    // set cell values
    // wxExcelRange has operator()(const wxVariant&) overloaded
    // so it behaves as if you called SetValue(variant)
    range = variant;

    // second row of values
    // shift the range one row down again
    range = range.GetOffset(1);
    // write the second row of values
    variant.ClearList();
    variant.Append("XYZ4567");
    variant.Append(wxDateTime::Today());
    variant.Append(5L);
    variant.Append(DoubleToCurrencyVariant(8.25));
    variant.Append("=C3*D3");            
    range = variant; 

    // shift the range one row down again
    range = range.GetOffset(1);

    // GetRange() uses addresses relative to range, 
    // so e.g. GetRange("A4:E4").GetRange("A1") returns 
    // a range with a worksheet absolute address A4
    range.GetRange("A1") = "TOTAL";

    range = range.GetRange("E1"); // again, range-relative address
    range.SetFormula("=SUM(E2:E3)"); // address in the formula is related to the whole worksheet
    // you could also use a relative formula to achieve the same result: 
    // range.SetFormulaR1C1("=SUM(R[-2]C:R[-1]C)");
    
    // demonstrates another way of creating a range
    range = worksheet.GetRange("E2", "E4");    
    // set the format of cells with formulas to currency
    wxExcelStyles styles = workbook.GetStyles();        
    range.SetStyle(styles[wxString("Currency")]);    

    // obtain a rectangular area containing all worksheet cells considered not empty
    range = worksheet.GetUsedRange();    
    // add medium-weight borders on the outside and thin-weight on the inside
    wxExcelBorders borders = range.GetBorders();
    borders[xlEdgeTop].SetWeight(xlMedium);
    borders[xlEdgeLeft].SetWeight(xlMedium);    
    borders[xlEdgeBottom].SetWeight(xlMedium);
    borders[xlEdgeRight].SetWeight(xlMedium);
    borders[xlInsideHorizontal].SetWeight(xlThin);
    borders[xlInsideVertical].SetWeight(xlThin);
    
    // format the totals row    
    range = worksheet.GetRange("A4:E4"); 
    wxExcelFont font = range.GetFont();
    font.SetBold(true);
    font.SetColor(*wxBLUE);
    font.SetSize(font.GetSize() * 1.5); // 150% of default size

    // set the cell background to light grey
    range.GetInterior().SetColor(*wxLIGHT_GREY);

    // merge the first four cells
    range.GetRange("A1:D1").Merge();
    
    // get the range for cell with the total sum
    // using another method of specifying a range - row and column
    // WXAEEP is a helper macro for passing pointers to longs and Excel enums
    range = range.GetCells(NULL, WXAEEP(5L)); 
    // add a thick double-lined blue border around the total sum
    range.BorderAround(WXAEEP(xlDouble), WXAEEP(xlThick), NULL, wxBLUE);

    // finally, fit the columns to the content
    worksheet.GetUsedRange().GetEntireColumn().AutoFit();   

    // show the text of the total sum as displayed in Excel    
    wxMessageBox(worksheet.GetRange("E4").GetText(), "Contents of cell E4");    

Re: wxAutoExcel: MS Excel automation with wxWidgets made easier

Posted: Fri Feb 24, 2017 12:24 am
by Manolo
:D :D =D> =D> =D> =D>