On the add-on page, Nebras Salman commented “when exporting to csv more than 65536 rows, it raises an exception. Knowing that csv files normally be more than excel limitation.”
The add-on converts to CSV by converting to XLS format first and then converting to CSV. This is because the add-on initially just converted from Vaadin Table to Excel. CSV was added after that and could leverage the existing code. The add-on class CsvExport uses an Apache POI class called XLS2CSVmra (which they provided as an example so it is in the TableExport jar, not the Apache POI jar).
XLS2CSVmra uses the org.apache.poi.hssf.usermodel.HSSFWorkbook spreadsheet model which I believe corresponds to an Excel 97 .xls file with a 64k row limit. There is another model org.apache.poi.xssf.usermodel.XSSFWorkbook which corresponds to an Excel 2007 .xlsx format that can handle 1 million (2^20) rows.
I wouldn’t expect converting XLS2CSVmra to use the XSSFWorkbook to be terribly difficult but I am unable to spend any time on it right now. If you get it working please submit to the project. Here are some resources:
Yes. You can send each table to a separate sheet in the same Excel file. Here’s an example:
ArrayList<Table> saveTables = new ArrayList<>();
ArrayList<String> sheetNames = new ArrayList<>();
ExcelExport excelExport = new ExcelExport(saveTables.get(0), sheetNames.get(0));
excelExport.setExportFileName(fileName.getValue().toString() + extension.getValue());
// Format the first sheet...
excelExport.setDoubleDataFormat("#,##0");
excelExport.setDisplayTotals(false);
// Write the first sheet, but keep the file open for more sheets
excelExport.convertTable();
for (int i = 1; i < saveTables.size(); i++)
{
// This will create a new sheet and associate the table with it
excelExport.setNextTable(saveTables.get(i), sheetNames.get(i));
// Customization table formatting if necessary...
excelExport.setDisplayTotals(true);
// This actually write the table to the sheet
excelExport.convertTable();
}
// Write the Excel file and close it
excelExport.sendConverted();
We created a CvsExport based on
JavaCsv that extends TableExport. So you get the same API but without the xls limit. It can be found
here . It’s published under gpl v3.
works fine now with latest Vaadin release, thx a lot!
Two things that I noticed with the new version:
The code somehow is not able to retrieve theme provided images like this one:
final ThemeResource export = new ThemeResource(
"../images/table-excel.png");
excelExportButton.setIcon(export);
Just FYI (doesn’t cause any problems), but you’ve set the groupId to “org.vaadin.addon” now instead of “org.vaadin.addons” as you wrote above (and which is the standard naming for addons).
Thank you for your quick reply.
Actually what I would need is having a single sheet in an excel file holding multiple tables which are placed below each other. I guess that’s not possible at the moment, right ?
You can always merge all the tables into a new temporary table that stays invisible, then pass the temporary table to ExcelExport. I do that in one instance when the tables have the same structure (same header, # of columns, etc…),
Just wanted to reiterate a comment I just made in the rankings for the addon. The code is really cool, but there is a bug.
In most of the code you use Container.Hierarchical to iterate through a Tree. But in the code that detects that a container is hierarchical, DefaultTableHolder, you compare the container to HierarchicalContainer. You should check it against Container.Hierarchical.
I was able to override isHierarchical() in a subclass of DefaultTableHolder and all was sort-of good. (I don’t see any titles in the first column of the Excel spreadsheet. But that could easily be my fault).
In my table cell value is displaying like 51,994,314.31 but when I export in into excel it’s showing like 5.199431431E7.
I want the original value to be displayed in exported excel file.
How can I achive this using ExcelExport add-on.
Please help me in this.
I second this question, the grid is a huge upgrade and I’m using it quite a bit, I would love the convenience of this export tool to go with it though.
Are you sure that it’s not an excel default setting for each sell? I’m pretty sure you can change the format of the cell and you would see the original value.
Got a little problem with exporting CSVs, the encoding was not correct (Liferay uses encoding UTF-8 w/o BOM).
If someone will go through this problem, you can solve it by overriding sendConvertd() in CsvExport class:
@Override
public boolean sendConverted() {
File tempXlsFile, tempCsvFile;
try {
tempXlsFile = File.createTempFile("tmp", ".xls");
final FileOutputStream fileOut = new FileOutputStream(tempXlsFile);
workbook.write(fileOut);
final FileInputStream fis = new FileInputStream(tempXlsFile);
final POIFSFileSystem fs = new POIFSFileSystem(fis);
tempCsvFile = File.createTempFile("tmp", ".csv");
// Write BOM
OutputStream fileStream = new FileOutputStream(tempCsvFile, true);
final byte[] bom = new byte[]
{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};
fileStream.write(bom);
// Finalize
final PrintStream p
= new PrintStream(new BufferedOutputStream(
fileStream), true, "UTF-8");
final XLS2CSVmra xls2csv = new XLS2CSVmra(fs, p, -1);
xls2csv.process();
p.close();
if (null == mimeType) {
setMimeType(CSV_MIME_TYPE);
}
return super.sendConvertedFileToUser(getTableHolder().getUI(), tempCsvFile,
exportFileName);
} catch (final IOException e) {
LOGGER.warning("Converting to CSV failed with IOException " + e);
return false;
}
}