InetSoft Product Info: Exporting Dashboards and Reports to Excel

A spreadsheet is a popular tool for performing simple data analysis. It also has fairly strong formatting support. InetSoft products support exporting a report to an Excel file. The Excel export is based on report layout and the following two assumptions:

• The generated Excel file contains the actual data in the report, so it can be further manipulated in a spreadsheet application.

• The Excel file should match the report layout and formatting as closely as possible, so it can be printed out in a form that reasonably resembles the original report.

Due to these two assumptions certain formatting characteristics of the original report will not be retained after an Excel file is generated. For example, page margin settings created in the Designer are not available in the exported Excel .xls file.

Style Intelligence has some advanced features that can be utilized during Excel exporting. You can export supported formulas to Excel tables. This is available for the two Excel export formats: Excel (no pagination) and Excel (best data editing). Style Intelligence supports GroupFilter and TableSummaryFilter for export in Excel format. The NthMostFrequent and WeightedAverage formulae are not supported.

exporting dashboard to excel  
 
 
BI Demo
Register
view demo icon
View a 2-minute demonstration of InetSoft's easy, agile, and robust BI software.

ExcelGenerator and ExcelSheetGenerator Classes

Exporting a report to an Excel file is performed through the ExcelGenerator and ExcelSheetGenerator classes. 

 ExcelGenerator gen =
    ExcelGenerator.createExcelGenerator(
       new FileOutputStream("report.xls"));
 gen.generate(report);

The default page size is the standard letter size (8.5"x11"). The page size can be changed using setPageSize() in ReportSheet. This call must be made before the generate() method is invoked.

When exporting a large file to Excel, note that an Excel spreadsheet has a maximum of 65535 rows and 255 columns; a report should not exceed these limits. Also, the heap size of the JVM needs to be increased. Use Java -XmxbbbM to indicate the maximum (where maximum JVM size is Xmx and JVM heap size is bbbM). For example: 

java -Xmx192M TestCase

Exporting to Different Excel Versions

The ExcelGenerator allows for export to particular types of Excel Files such as Excel 2000, Excel 95, or an Excel Spreadsheet with no page breaks for easier data manipulation (as opposed to being used in a presentation). To use this feature, instantiate the ExcelGenerator by sending the version parameter as follows:

//Excel 2000
ExcelGenerator gen =
   ExcelGenerator.createExcelGenerator(
      new FileOutputStream("report.xls"),
      ExcelGenerator.EXCEL2000);

//Excel 95
ExcelGenerator gen =
   ExcelGenerator.createExcelGenerator(
      new FileOutputStream("report.xls"),
      ExcelGenerator.EXCEL95);

//Excel with no page breaks
ExcelSheetGenerator gen =
   ExcelSheetGenerator.createExcelGenerator(
      new FileOutputStream("report.xls"),
      ExcelGenerator.EXCEL_SHEET);

Copying or Pasting Content in the Generated Excel File

When copying and pasting the contents of the generated Excel file between two spreadsheets, make sure that both spreadsheets use the same formatting. If the two Excel files (source and target) use different cell formatting, a few problems may arise. In this case, try using 'Paste Special' instead of paste and select only the value or content, but not the format. If Windows XP is used, then open the clipboard by selecting Edit → Office Clipboard. Choose 'select using destination format'.

When you export a CSV file to excel, special characters are encoded in UTF8 format, which might provide an inaccurate representation of the report. You can set the property “text.encoding.utf8” in the sree.properties file to “false”. This will ensure that special characters are not encoded into UTF8 during export. The default value of this property is true. Another property is “text.break.pages” which, when set to “true”, will insert a form feed at every page break.

Previous: Generating PDF Reports with CID Fonts