Class JRXlsAbstractExporter<RC extends XlsReportConfiguration,C extends XlsExporterConfiguration,E extends JRExporterContext>
- java.lang.Object
-
- net.sf.jasperreports.engine.JRAbstractExporter<RC,C,OutputStreamExporterOutput,E>
-
- net.sf.jasperreports.engine.export.ExcelAbstractExporter<RC,C,E>
-
- net.sf.jasperreports.engine.export.JRXlsAbstractExporter<RC,C,E>
-
- All Implemented Interfaces:
Exporter<ExporterInput,RC,C,OutputStreamExporterOutput>
- Direct Known Subclasses:
JROdsExporter
,JRXlsAbstractMetadataExporter
,JRXlsExporter
,JRXlsxExporter
public abstract class JRXlsAbstractExporter<RC extends XlsReportConfiguration,C extends XlsExporterConfiguration,E extends JRExporterContext> extends ExcelAbstractExporter<RC,C,E>
Superclass for the Excel exporters.Excel Exporters
For generating Excel files, there are currently two different exporter implementations available in JasperReports. The first to appear was theJRXlsExporter
implementation, which uses the Apache POI library to export documents to the Microsoft Excel 2003 file format (XLS). Later on, with the introduction of the Microsoft Excel 2007 file format (XLSX), a new exporter was added to JasperReports to support it. This exporter implementation is theJRXlsxExporter
; it does not rely on any third party library to produce XLSX files. Because in XLS and XLSX all document content is placed inside cells, the Excel exporters are considered typical grid exporters, and having their known limitations.Configuring Sheets
An Excel file is structured in multiple sheets, and both exporters can be configured either to put all pages inside the sourceJasperPrint
document on one sheet (one after the another), or to put each page on a separate sheet in the resulting Excel file. The choice is made by setting theisOnePagePerSheet()
exporter configuration setting, which is set toBoolean.FALSE
by default. WhenisOnePagePerSheet()
is set to true, or when you have to execute a batch export to XLS, multiple sheets are created in the worksheet. You can also combine two exporter settings to customize the maximum number of rows per page, and display each page of the report in a separate sheet. To do this, set the number of rows per sheet fornet.sf.jasperreports.export.xls.max.rows.per.sheet
property and set true fornet.sf.jasperreports.export.xls.one.page.per.sheet
property. The JasperReports Excel exporters provide a simple but efficient sheet-naming mechanism. They use thegetSheetNames()
exporter configuration setting to read custom sheet names from the String array passed as value. This exporter setting can hold an array of strings, which are passed as sheet names in order. If no value is supplied for this setting or if the value contains fewer sheet names than actually needed by the final document, then the sheets are named by defaultPage i
(where i represents the one-based sheet index). Taking into account the sheet name's length limitation in Excel (31 characters), if a sheet name contains more than 31 characters it will be truncated as follows: the name of the sheet will be given by the first 31 characters minus the sheet index length characters of the document's name, followed by the sheet index, so that the entire name has exactly 31 characters. For example, if the second sheet name is TheQuickBrownFoxJumpsOverTheLazyDog (35 chars), it will become TheQuickBrownFoxJumpsOverTheLa2 (the final zyDog gets truncated, and the second sheet index 2 will end the name). The name of the 12345-th sheet will become TheQuickBrownFoxJumpsOverT12345 (the final heLazyDog gets truncated, in order to make the exact room for 12345). Multiple sheet names can be specified in the JRXML file as well, using thenet.sf.jasperreports.export.xls.sheet.names.<arbitrary_name>
property at report level. Add the following to the JRXML file:<property name="net.sf.jasperreports.export.xls.sheet.names.all" value="Sheet A/Sheet B/Sheet C" />
Keep in mind the naming order is important, sheets will be named in the same order the names are provided in this property. Sheets can be also named at element level, using thenet.sf.jasperreports.export.xls.sheet.name
element property. This name will be provided for the sheet the element belongs to.Flow-Oriented Output
The JasperPrint documents are page-oriented. When they are exported to a single sheet Excel document, all the pages are rendered consecutively. Because all exporters try to adhere as closely as possible to the quality and layout of the source document'sGraphics2D
or PDF format, the page breaks are visible in Excel format. Sometimes this is not desirable. One way to make page breaks less obvious and the layout more flow-based is to suppress all the remaining blank space between cells on the vertical axis. When set toBoolean.TRUE
, theisRemoveEmptySpaceBetweenRows()
exporter configuration flag ensures that all empty rows on the resulting sheet are collapsed. By default, the exporter preserves all the white space for a precise page layout. The provided /demo/samples/nopagebreak sample shows you how to use this setting when exporting to XLS to produce a more flow-based document layout. To completely ignore pagination, use the built-in fill-time parameterIS_IGNORE_PAGINATION
. You can modify the API to remove the empty space between rows and columns as well. You need to setnet.sf.jasperreports.export.xls.remove.empty.space.between.rows
property and/ornet.sf.jasperreports.export.xls.remove.empty.space.between.columns
property to true. Keep in mind these settings are limited by your report layout. If it is too far away from a grid layout, these options cannot work. On a good grid layout, once you setnet.sf.jasperreports.export.xls.remove.empty.space.between.rows
property and/ornet.sf.jasperreports.export.xls.remove.empty.space.between.columns
property to true, the empty spaces are removed.Cell Types
Inside the proprietary document format that JasperReports uses (represented by theJasperPrint
object), all text elements are considered alphanumeric values. This means that if a numeric text field of typejava.lang.Double
is placed in the report template at design time, all the text elements inside theJasperPrint
object resulting from it will holdjava.lang.String
values, even though they are actually numbers. Therefore, in a sense, data type information is lost during report filling. This is because the main goal of JasperReports is to create documents for viewing and printing, not necessarily for further data manipulation inside tools like Excel, where formulas could be added to numeric cells. However, these resulting text elements found in the generated documents nowadays hold enough data type information (in addition to the alphanumeric content) for the original value of the text element to be re-created, if needed. Both Excel exporters support theisDetectCellType()
configuration flag, which forces the recreation of the original cell value in accordance with its declared data type, as specified in the report template. Cell type detection is turned off by default. You can have JasperReports automatically detect the cell type by modifying the API. Set thenet.sf.jasperreports.export.xls.detect.cell.type
property to true. When you do this, instead of being prompted by Excel to convert the value manually, the value is automatically converted.Format Pattern Conversions
It is important to keep in mind that standard Java format patterns are not completely supported by Microsoft Excel. There are rather few data patterns that make a perfect match between Java and Excel. In the case that the Java pattern stored in the generated report does not match any of the supported Excel cell patterns, there is still a way to choose an appropriate Excel format pattern. The solution is to use thegetFormatPatternsMap()
export configuration setting and supply ajava.util.Map
as value. This map should contain Java format patterns as keys and corresponding Excel format patterns as values. Another way to adjust the format pattern to Excel-compatible values is to set thenet.sf.jasperreports.export.xls.pattern
property at element level.Font Size Correction
Currently, there is no way to control the line spacing in a spreadsheet cell, which results in the cell text not fitting exactly within the cell boundaries. As a workaround, in order to force the cell text to fit, one can use theisFontSizeFixEnabled()
exporter configuration flag to decrease the font size by one point when generating the cell format. Alternatively, one can use thenet.sf.jasperreports.export.xls.font.size.fix.enabled
property at report level.Background Color
Empty space found on each page in the sourceJasperPrint
document normally results in empty cells on the corresponding sheet inside the Excel file. The background color of these empty cells is specified by the configuration of the Excel viewer itself. This makes the cells appear transparent. To force the document's background to be white, set theisWhitePageBackground()
exporter configuration flag toBoolean.TRUE
.Excel Color Palette
In JasperReports, any color can be used for the background or the foreground of a report element. However, when exporting to Excel format, only a limited set of colors is supported, through what is called a color palette. If the colors used in a report template do not match the colors in the color palette, then the Excel exporter will use a special algorithm to determine the closest matches by comparing the RGB levels. However, the results might not always be as expected. A possibility to optimize the use of supported colors is to create a custom color palette. This can be achieved by setting to true theisCreateCustomPalette()
export configuration flag. If the flag is set, the nearest not yet modified color from the palette is chosen and modified to exactly match the report color. If all the colors from the palette are modified (the palette has a fixed size), the nearest color from the palette is chosen for further report colors. To see other various exporter configuration settings, please consult theXlsReportConfiguration
andXlsExporterConfiguration
classes.- Author:
- Teodor Danciu (teodord@users.sourceforge.net)
- See Also:
XlsExporterConfiguration
,XlsReportConfiguration
-
-
Nested Class Summary
Nested Classes Modifier and Type Class Description static class
JRXlsAbstractExporter.SheetInfo
-
Nested classes/interfaces inherited from class net.sf.jasperreports.engine.export.ExcelAbstractExporter
ExcelAbstractExporter.NameScope, ExcelAbstractExporter.TextAlignHolder
-
Nested classes/interfaces inherited from class net.sf.jasperreports.engine.JRAbstractExporter
JRAbstractExporter.BaseExporterContext, JRAbstractExporter.PageRange
-
-
Field Summary
-
Fields inherited from class net.sf.jasperreports.engine.export.ExcelAbstractExporter
autoFilterEnd, autoFilterStart, collapseRowSpan, CURRENT_ROW_AUTOFIT, CURRENT_ROW_HEIGHT, DEFAULT_DEFINED_NAME_SCOPE, DEFAULT_DEFINED_NAME_SCOPE_SEPARATOR, DEFAULT_SHEET_NAME_PREFIX, defaultCellHidden, defaultCellLocked, defaultFont, defaultIgnoreHyperlink, defaultIgnoreTextFormatting, defaultImageAnchorType, defaultShrinkToFit, defaultUseTimeZone, defaultWrapText, definedNamesMap, detectCellType, EXCEPTION_MESSAGE_KEY_CANNOT_ADD_CELL, EXCEPTION_MESSAGE_KEY_CANNOT_MERGE_CELLS, EXCEPTION_MESSAGE_KEY_CELL_FORMAT_TEMPLATE_ERROR, EXCEPTION_MESSAGE_KEY_COLUMN_INDEX_BEYOND_LIMIT, EXCEPTION_MESSAGE_KEY_LOADED_FONTS_ERROR, EXCEPTION_MESSAGE_KEY_NEGATIVE_COLUMN_INDEX, EXCEPTION_MESSAGE_KEY_REPORT_GENERATION_ERROR, EXCEPTION_MESSAGE_KEY_TEMPLATE_NOT_FOUND, firstPageNotSet, formatPatternsMap, ignoreAnchors, imageBorderFixEnabled, invalidCharReplacement, keepTemplateSheets, loadedFonts, maxColumnIndex, nature, onePagePerSheet, onePagePerSheetMap, pageFormat, pageIndex, PROPERTY_AUTO_FILTER, PROPERTY_AUTO_FIT_COLUMN, PROPERTY_AUTO_FIT_ROW, PROPERTY_AVERAGE_CHAR_WIDTH_FACTOR, PROPERTY_BREAK_AFTER_ROW, PROPERTY_BREAK_BEFORE_ROW, PROPERTY_CELL_FORMULA, PROPERTY_CELL_PATTERN, PROPERTY_COLUMN_NAME, PROPERTY_COLUMN_WIDTH, PROPERTY_COLUMN_WIDTH_METADATA, PROPERTY_DATA, PROPERTY_FREEZE_COLUMN_EDGE, PROPERTY_FREEZE_ROW_EDGE, PROPERTY_IGNORE_ROW_HEIGHT, PROPERTY_REPEAT_VALUE, PROPERTY_ROW_OUTLINE_LEVEL_PREFIX, PROPERTY_SHEET_NAME, renderersCache, reportIndex, sheetIndex, sheetInfo, sheetNames, sheetNamesIndex, sheetNamesMap, sheetsBeforeCurrentReport, sheetsBeforeCurrentReportMap, workbookTemplate, XLS_EXPORTER_PROPERTIES_PREFIX
-
Fields inherited from class net.sf.jasperreports.engine.JRAbstractExporter
allSelector, crtCompositeConfiguration, crtCompositeItemConfiguration, crtItem, dateFormatCache, EXCEPTION_MESSAGE_KEY_EMPTY_INPUT_SOURCE_IN_BATCH_MODE, EXCEPTION_MESSAGE_KEY_END_PAGE_INDEX_OUT_OF_RANGE, EXCEPTION_MESSAGE_KEY_INVALID_IMAGE_NAME, EXCEPTION_MESSAGE_KEY_INVALID_ZOOM_RATIO, EXCEPTION_MESSAGE_KEY_NO_INPUT_SOURCE, EXCEPTION_MESSAGE_KEY_NO_OUTPUT_SPECIFIED, EXCEPTION_MESSAGE_KEY_OUTPUT_WRITER_ERROR, EXCEPTION_MESSAGE_KEY_PAGE_INDEX_OUT_OF_RANGE, EXCEPTION_MESSAGE_KEY_START_PAGE_INDEX_OUT_OF_RANGE, exporterConfiguration, exporterContext, exporterInput, exporterOutput, filter, fontUtil, hyperlinkProducerFactory, itemConfiguration, jasperPrint, jasperReportsContext, noBackcolorSelector, noneSelector, numberFormatCache, propertiesUtil, PROPERTY_DEFAULT_FILTER_FACTORY, PROPERTY_SUFFIX_DEFAULT_FILTER_FACTORY, rendererUtil, styledTextUtil, textValueClasses
-
-
Constructor Summary
Constructors Constructor Description JRXlsAbstractExporter()
JRXlsAbstractExporter(JasperReportsContext jasperReportsContext)
-
Method Summary
All Methods Instance Methods Abstract Methods Concrete Methods Modifier and Type Method Description protected abstract void
addBlankCell(JRExporterGridCell gridCell, int colIndex, int rowIndex)
protected abstract void
addOccupiedCell(OccupiedGridCell occupiedGridCell, int colIndex, int rowIndex)
protected void
configureHeaderFooter(JRXlsAbstractExporter.SheetInfo.SheetPrintSettings printSettings, Cut yCut)
protected void
configurePrintSettings(JRXlsAbstractExporter.SheetInfo.SheetPrintSettings printSettings, Cut yCut)
protected abstract void
createSheet(CutsInfo xCuts, JRXlsAbstractExporter.SheetInfo sheetInfo)
protected void
exportEmptyReport()
protected abstract void
exportFrame(JRPrintFrame frame, JRExporterGridCell cell, int colIndex, int rowIndex)
protected abstract void
exportGenericElement(JRGenericPrintElement element, JRExporterGridCell cell, int colIndex, int rowIndex, int emptyCols, int yCutsRow, JRGridLayout layout)
protected abstract void
exportImage(JRPrintImage image, JRExporterGridCell cell, int colIndex, int rowIndex, int emptyCols, int yCutsRow, JRGridLayout layout)
protected abstract void
exportLine(JRPrintLine line, JRExporterGridCell cell, int colIndex, int rowIndex)
protected int
exportPage(JRPrintPage page, CutsInfo xCuts, int startRow, String defaultSheetName)
protected abstract void
exportRectangle(JRPrintGraphicElement element, JRExporterGridCell cell, int colIndex, int rowIndex)
protected void
exportReportToStream(OutputStream os)
protected void
exportSheet(CutsInfo xCuts, CutsInfo yCuts, int startCutIndex, String defaultSheetName)
protected abstract void
exportText(JRPrintText text, JRExporterGridCell cell, int colIndex, int rowIndex)
protected JRXlsAbstractExporter.SheetInfo
getSheetProps(CutsInfo xCuts, CutsInfo yCuts, int startCutIndex)
protected void
setColumnWidths(CutsInfo xCuts)
protected abstract void
setRowHeight(int rowIndex, int lastRowHeight, Cut yCut, XlsRowLevelInfo levelInfo)
-
Methods inherited from class net.sf.jasperreports.engine.export.ExcelAbstractExporter
addRowBreak, calculateHeightForDinAN, calculateHeightForDinBN, calculateHeightForDinCN, calculateWidthForDinAN, calculateWidthForDinBN, calculateWidthForDinCN, closeSheet, closeWorkbook, configureDefinedNames, configureDefinedNames, configureDefinedNames, ensureInput, exportReport, getColumIndexName, getColumnIndex, getColumnName, getConvertedPattern, getDefaultFont, getFormula, getImageBorderCorrection, getMaxRowsPerSheet, getNature, getSheetName, getSheetName, getStyledText, getTextAlignHolder, hasGlobalSheetNames, initExport, initReport, isCellHidden, isCellLocked, isIgnoreTextFormatting, isShrinkToFit, isUseTimeZone, isValidScale, isWrapText, mergeAndSetRowLevels, openWorkbook, resetAutoFilters, setAutoFilter, setColumnWidth, setFreezePane, setRowLevels, setSheetName, setSheetNames, toExcelName, translateDateValue, updateHeaderFooter, updatePrintSettings
-
Methods inherited from class net.sf.jasperreports.engine.JRAbstractExporter
checkInterrupted, createFilter, defaultParseNumber, ensureJasperReportsContext, ensureOutput, getBooleanCellValue, getConfigurationInterface, getCurrentConfiguration, getCurrentItemConfiguration, getCurrentJasperPrint, getDateCellValue, getDateFormat, getExporterConfiguration, getExporterContext, getExporterInput, getExporterKey, getExporterOutput, getExporterPropertiesPrefix, getHyperlinkProducer, getItemConfigurationInterface, getJasperReportsContext, getLocale, getNumberCellValue, getNumberFormat, getOffsetX, getOffsetY, getPageRange, getPropertiesUtil, getRendererUtil, getReportContext, getReportExportConfiguration, getRepository, getStyledText, getTextFormatFactoryClass, getTextLocale, getTextTimeZone, getTextValue, getTextValueString, insideFrame, reset, resetExportContext, restoreElementOffsets, setConfiguration, setConfiguration, setCurrentExporterInputItem, setExporterInput, setExporterOutput, setFrameElementsOffset, setJasperReportsContext, setOffset, setOffset, setReportContext
-
-
-
-
Constructor Detail
-
JRXlsAbstractExporter
public JRXlsAbstractExporter()
-
JRXlsAbstractExporter
public JRXlsAbstractExporter(JasperReportsContext jasperReportsContext)
-
-
Method Detail
-
exportReportToStream
protected void exportReportToStream(OutputStream os) throws JRException, IOException
- Specified by:
exportReportToStream
in classExcelAbstractExporter<RC extends XlsReportConfiguration,C extends XlsExporterConfiguration,E extends JRExporterContext>
- Throws:
JRException
IOException
-
exportPage
protected int exportPage(JRPrintPage page, CutsInfo xCuts, int startRow, String defaultSheetName) throws JRException
- Returns:
- the number of rows added.
- Throws:
JRException
-
getSheetProps
protected JRXlsAbstractExporter.SheetInfo getSheetProps(CutsInfo xCuts, CutsInfo yCuts, int startCutIndex)
-
configurePrintSettings
protected void configurePrintSettings(JRXlsAbstractExporter.SheetInfo.SheetPrintSettings printSettings, Cut yCut)
-
configureHeaderFooter
protected void configureHeaderFooter(JRXlsAbstractExporter.SheetInfo.SheetPrintSettings printSettings, Cut yCut)
-
exportSheet
protected void exportSheet(CutsInfo xCuts, CutsInfo yCuts, int startCutIndex, String defaultSheetName)
-
setColumnWidths
protected void setColumnWidths(CutsInfo xCuts)
-
exportEmptyReport
protected void exportEmptyReport() throws JRException, IOException
- Specified by:
exportEmptyReport
in classExcelAbstractExporter<RC extends XlsReportConfiguration,C extends XlsExporterConfiguration,E extends JRExporterContext>
- Throws:
JRException
IOException
-
createSheet
protected abstract void createSheet(CutsInfo xCuts, JRXlsAbstractExporter.SheetInfo sheetInfo)
-
setRowHeight
protected abstract void setRowHeight(int rowIndex, int lastRowHeight, Cut yCut, XlsRowLevelInfo levelInfo) throws JRException
- Throws:
JRException
-
addBlankCell
protected abstract void addBlankCell(JRExporterGridCell gridCell, int colIndex, int rowIndex) throws JRException
- Throws:
JRException
-
addOccupiedCell
protected abstract void addOccupiedCell(OccupiedGridCell occupiedGridCell, int colIndex, int rowIndex) throws JRException
- Throws:
JRException
-
exportText
protected abstract void exportText(JRPrintText text, JRExporterGridCell cell, int colIndex, int rowIndex) throws JRException
- Throws:
JRException
-
exportImage
protected abstract void exportImage(JRPrintImage image, JRExporterGridCell cell, int colIndex, int rowIndex, int emptyCols, int yCutsRow, JRGridLayout layout) throws JRException
- Throws:
JRException
-
exportRectangle
protected abstract void exportRectangle(JRPrintGraphicElement element, JRExporterGridCell cell, int colIndex, int rowIndex) throws JRException
- Throws:
JRException
-
exportLine
protected abstract void exportLine(JRPrintLine line, JRExporterGridCell cell, int colIndex, int rowIndex) throws JRException
- Throws:
JRException
-
exportFrame
protected abstract void exportFrame(JRPrintFrame frame, JRExporterGridCell cell, int colIndex, int rowIndex) throws JRException
- Throws:
JRException
-
exportGenericElement
protected abstract void exportGenericElement(JRGenericPrintElement element, JRExporterGridCell cell, int colIndex, int rowIndex, int emptyCols, int yCutsRow, JRGridLayout layout) throws JRException
- Throws:
JRException
-
-