xlsgen > overview > Charts management

xlsgen provides the following mechanisms to manage charts :

• addition
  • embedded or stand-alone charts can be added :
    worksheet.NewChart (embedded chart)
    workbook.AddChart (stand-alone chart, has its own sheet tab)
    
    
• deletion
  • any chart can be deleted :
    charts.Item(i).Delete()
    
    
• introspection
  • the chart details of an existing chart can be read :
    worksheet.Charts returns the charts collection for the given worksheet
    charts.Item(i) returns the i-th chart in that collection
    
    
• picture extraction
  • chart.ExtractToFile() returns a snapshot of the chart (PNG file)
    chart.ExtractToMemory() returns a buffer with a snapshot of the chart (PNG buffer)
    
    
• edit
  • existing charts can be edited, for instance to change a pie to a column bar. Virtually any chart formatting property or data source can be edited. Make sure once you are done making the changes, to call the chart.CommitChanges method which ensures that the changes are indeed written back to the file. Example : change the chart type of an existing chart, example source code is in C/C++ :

    C/C++ code

    xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) ); xlsgen::IXlsWorkbookPtr wbk = engine->Open(L"existingFile.xslx", L"updatedFile.xlsx"); // we want to edit the second chart in the first worksheet xlsgen::IXlsChartPtr chart = wbk->WorksheetByIndex[1]->Charts->Item[2]; // change the chart type chart->Type = xlsgen::charttype_bar3D; // commit the changes chart->CommitChanges(); wbk->Close();

• switch rows and columns
  • switch rows and columns in a chart is a mechanism for analyzing data the opposite way. Rows become columns and vice versa.
    
    
• print/preview/PDF
  • the content of a worksheet can be printed including cell formatting, pictures and charts. For a chart to be printed, the Microsoft OWC components must be installed prior that. For more information on the subject, please read this page. Charts can be printed on a paper, previewed in a page or exported as part of a PDF file.
    worksheet.PrintWith prints the worksheet and its contents
    worksheet.PrintPreview previews the worksheet and its contents
    worksheet.Export.ExportAsPDF exports the worksheet and its contents as a PDF file
    
    
  • reading external data sources for rendering purposes. In case the chart data source is a reference to a separate workbook, xlsgen can still read it provided some additional guidance. One or two steps must be added :

    1) ensure to enable the reading of a separate workbook from the current workbook. This is achieved by doing the following : workbook.AutoOpenExternalWorkbookReference = TRUE;

    2) help xlsgen by passing the filepath where the separate workbook is stored. Note that by filepath is not meant the full file path, only the directory file path. For instance if the separate workbook (where the data is stored) is located in C:\tmp\workdir, make sure to do the following : engine.Workbooks.AddFilepath("C:\\tmp\\workdir");, where engine is the xlsgen instance.

• turn to/from standalone
  • a chart can invidually be made stand-alone, i.e. stands in its own sheet. There is a TurnToStandalone method call available at the chart level. And there is the opposite method available too, TurnToEmbedded, which turns a chart from being stand-alone to being embedded.
    
    
• copy to
  • a chart can be invidually brought over from another sheet, be it from the same workbook or another workbook. This can be achieved using the CopyTo method call. A refined method call, CopyToLocation makes it possible to copy a chart to a given location in the target sheet. These two method calls are suited when the scenario tries to avoid bringing over the entire content of a sheet (including chart) which is what happens with the Duplicate method call exposed at the worksheet level. Note that when a chart is copied from an external workbook, the range of cells of the data source are resolved and replaced by arrays of fix values.