wxAutoExcel: MS Excel automation with wxWidgets made easier

Do you like to promote your wxWidgets based application or component!? Post it here and let's see what the critics have to say. Also, if you found that ONE wx component the world needs to know about, put it here for future reference.
Post Reply
PB
Part Of The Furniture
Part Of The Furniture
Posts: 2094
Joined: Sun Jan 03, 2010 5:45 pm

wxAutoExcel: MS Excel automation with wxWidgets made easier

Post by PB » Thu Feb 23, 2017 4:54 pm

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");    

Manolo
Can't get richer than this
Can't get richer than this
Posts: 704
Joined: Mon Apr 30, 2012 11:07 pm

Re: wxAutoExcel: MS Excel automation with wxWidgets made easier

Post by Manolo » Fri Feb 24, 2017 12:24 am

:D :D =D> =D> =D> =D>

Post Reply