Merging Data with Microsoft Excel Files

There can be a need to use ExtraView data for reports that cannot be created within ExtraView, but can be created within Microsoft Excel. This feature extends ExtraView so that you can harness all of Microsoft Excel's capabilities in conjunction with any subset of ExtraView data. For example, you might want to use ExtraView data within a pivot table, or you may want to generate charts that are outside the scope of ExtraView's own charting capabilities.

This feature works with Column reports, Summary reports and Matrix reports.

The procedure to merge ExtraView data with an Excel spreadsheet/workbook is as follows:

Create the Excel sheet that contains the ExtraView data

Create an individual sheet which will be used to hold the ExtraView data. This will most often be empty of data, but the columns should be formatted as follows to receive data of the correct type. The columns may also contain Excel formulae. The ExtraView data is merged with the sheet and only overwrites values, not the formulae.

  • All ExtraView text type fields do not require any formatting in the sheet
  • ExtraView date and day display types will be placed in columns in the sheet as seconds since the epoch
  • ExtraView currency and numeric display types will be placed in columns in the sheet as plain numbers
  • It is not valid to send buttons, image and document display types to the Excel sheet
  • Save the workbook.  It must be saved with an extension with an extension of .xlsx.  The old style .xls files are not supported

The following screenshot show how it might look, after ExtraView populates the data into this sheet. Again, the sheet should be empty at this stage:

The Excel data sheet

Create the remainder of the Excel workbook

Create an additional sheet, or sheets, within the same Excel spreadsheet, which contain the reports or other Excel objects. These should source the ExtraView data by referencing a data range within the first sheet you created. An example pivot table might look like this:

 

The Excel output report

Save the spreadsheet

Note that the spreadsheet must be saved as type XLS, not type XLSX. This is due to a limitation in third-party utility software used to manage the Excel spreadsheet within the ExtraView server.

Upload the spreadsheet to ExtraView

Now, you upload the saved spreadsheet into ExtraView. Create a new Column report, then select Microsoft Excel (Merge with uploaded file) as the output format. Most of the report creation is identical to creating other column reports.

Creating the report

  • Click the button to upload the Excel workbook / spreadsheet. This takes a copy of the spreadsheet from your local computer and places it in the database on the ExtraView server
  • The titles to all the sheets contained within the Excel workbook are displayed in the select list. Choose the sheet that is to be the destination for the ExtraView data
  • The default is that the ExtraView data will be placed at the top left-hand corner of the sheet, beginning with cell A1. You may alter the beginning cell by placing the data at any other row and column in the sheet
  • Use the checkbox Include Column Titles in Sheet to indicate whether you want the column titles from the ExtraView data to be placed in the sheet. Note that for most purposes you want the column titles to match up with the titles as they will be used in the other sheets inside the Excel workbook. The matching is case sensitive
  • The checkbox Use Template Format gives control of the data formatting within the spreadsheet that is output. If this box is not checked, then ExtraView's field formats are used. If this is checked, then the formats of the data within the spreadsheet are observed and used. This has uses to control items such as the format of dates in the spreadsheet. You might want to use the format mandated by the cell formats in the spreadsheet, or you might want to use the user's date format as specified by their personal options.
  • Select the columns for the report that are to be used to fill in the Excel data sheet
  • Apply any filters required
  • Save the report in the normal way.

Run the report

When you run this report, ExtraView will take the data returned by the query, and populate the Excel sheet. The report is then downloaded for you, in the form of a standard Excel spreadsheet.

Tips

  • Wrapping text within Excel cells - it is recommended that you highlight all rows within your spreadsheet and set the text wrapping option before saving and uploading to ExtraView.  If you do not do this, then you have to double-click within the cell in the downloaded report before the text wrapping works
  • Custom date formats - it is not recommended that you set your personal options within ExtraView to use a custom date format.  Excel does not understand all the possible custom date masks that can be used
  • ExtraView can summarize on a significantly larger number of rows and / or columns (eight) within Matrix and Summary reports than Excel.  Excel cannot produce charts with more than 3 summarization levels.