Hello all,
How to write the outputstream in the workbook.?? How to workout with Vaadin?
public void writeDataIntoSheet(SXSSFWorkbook wb,String fileName){
…
…
}
Thank you.
Hi Siva P
The popup, which asks whether you want to open or save the file, will be created by your operating system (OS). You do not have control over this popup. (I think) Some OS do not use this popup and directly save the file in the pre-specified folder for example.
To download an excel file you will need two things:
- the download button - we will take advantage of the [file-download-wrapper]
(https://vaadin.com/directory/component/file-download-wrapper/overview) add-on - convert your workbook to a byte array
First let’s set up the download button
Button downloadBtn = new Button("Download Excel file", new Icon(VaadinIcon.DOWNLOAD));
FileDownloadWrapper buttonWrapper = new FileDownloadWrapper(
new StreamResource(fileName".xlsx", () -> new ByteArrayInputStream(getWorkBookBytes(wb)))
);
buttonWrapper.wrapComponent(downloadBtn);
layout.add(buttonWrapper);
How to get a byte array from your SXSSFWorkbook: (I only know XSSFWorkbook, but IIRC this same approach should work for both)
private byte[] getWorkbookBytes(SXSSFWorkbook wb) {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
wb.write(bos); // omitted try-catch for brevity
bos.close(); // omitted try-catch for brevity
return bos.toByteArray();
}
Hello Kasper,
Thank you for your reply.In between your reply i have tried this code below but nothing happens,here when clicking the button Sop is called.
public void writeDataIntoSheet(SXSSFWorkbook wb,String fileName){
System.out.println("<<<<<<<<<<<<<<<<writeDataIntoSheet>>>>>>>>>>>>>"); StreamResource resource = new StreamResource(fileName + ".xlsx",
() -> getImageInputStream(wb));
resource.setContentType("application/xlsx");
resource.setCacheTime(0);
}
private InputStream getImageInputStream(SXSSFWorkbook wb) {
return new ByteArrayInputStream(wb.toString().getBytes());
}
Thank you.
wb.toString().getBytes()
watch out, calling toString() on your workbook will not do what you think it does! SXSSFWorkbook does not have a toString implementation so it will use the one from Object. wb.toString() will therefore result in a String that looks like this: org.apache.poi.xssf.streaming.SXSSFWorkbook@18513. Please try with my approach with writing to a ByteArrayOutputStream.
But you would expect a file being downloaded even if its content was corrupt / not the excel file you wanted, right? Well no, this is where the download button comes into play - you have none. Creating a StreamResource is not enough. In my previous post I showed a working example of how to setup a download button for this. please give it a try 
Hello Kaspar,
Thank you for your reply. I have tried your code its working fine(alternative with double click of btn). I have a scenario like below,
- There are some search parameters like date selection from and To Fields and textbox field values.Values are entered in it.
- By clicking the search button → as a result grid will be populated with ~ 100 records with pagination in screen.
- Now the DOWNLOAD icon button should be visible to the users below the grid pagination buttons.
- By clicking the DOWNLOAD icon button that time only I am calling like below,
downloadBtn.addClickListener(e -> {
generateShortIdExcel(textField, dfFromDate, dfToDate, checkboxReportingValue, shortidRepository,
counterNameRepository, counterNameSummaryRepository);
});
- Now in the below method,
private void generateShortIdExcel(TextField textField, DatePicker dfFromDate, DatePicker dfToDate,
boolean checkboxReportingValue2, ShortidRepository shortidRepository2,
CounterNameRepository counterNameRepository2, CounterNameSummaryRepository counterNameSummaryRepository2) {
..........
.........
..................
List<List<CounterNameSummary>> lstSplit = ListUtils.partition(result,SpreadsheetVersion.EXCEL2007.getMaxRows());
writeDataSheetWise(lstSplit,wb,"shortIdReport");
FileDownloadWrapper wrapper = writeDataIntoSheet(wb,"shortIdReport");
add(wrapper);
}
public FileDownloadWrapper writeDataIntoSheet(SXSSFWorkbook wb,String fileName){
System.out.println("<<<<<<<<<<<<<<<<writeDataIntoSheet>>>>>>>>>>>>>");
FileDownloadWrapper buttonWrapper = new FileDownloadWrapper(
new StreamResource(fileName+".xlsx", () -> {
try {
return new ByteArrayInputStream(getWorkbookBytes(wb));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
})
);
buttonWrapper.wrapComponent(downloadBtn);
return buttonWrapper;
}
private byte[] getWorkbookBytes(SXSSFWorkbook wb) throws IOException {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
wb.write(bos); // omitted try-catch for brevity
bos.close(); // omitted try-catch for brevity
return bos.toByteArray();
}
- My question is how DOWNLOAD icon button is visible when add it in run time. In this case there is no possible way to see the DOWNLOAD icon button below the grid.
- As an alternative case, added after actionlistener is called
downloadBtn.setVisible(false);
add(downloadBtn);
- Making it as true when grid list is loaded in the screen.
downloadBtn.setVisible(true); - Here DOWNLOAD icon is visible properly after grid content is loaded and after the pagination button is displayed.
- When clicking the DOWNLOAD icon button first time nothing happend.Secong time when clicking it it opens popup window of the excel content.
- There is nothing like specific layout available in my project i am adding the component directely using add(Components…)
Thank you.
Initialize the FileDownloadWrapper from the beginning and not in an eventlistener. Don’t add a clickListener to your downloadBtn. You can initialize the wrapper with a dummy StreamResource (to avoid creating an Excel Sheet before user actually clicks download)
// at creation of view Layout
Button downloadBtn = new Button("Download Excel File", new Icon(VaadinIcon.DOWNLOAD));
// dummy resource defined here (null doesn't work), the real resource will be created and set later once the user confirmed that they want to download
FileDownloadWrapper buttonWrapper = new FileDownloadWrapper(
new StreamResource("initial_filename.xlsx", () -> new ByteArrayInputStream("abc".getBytes()))
);
buttonWrapper.wrapComponent(downloadBtn);
add(buttonWrapper);
And then later on, change the file that will be downloaded using setResource(StreamResource resource); The hurdle that I cannot solve right now is how to change the resource after the button is clicked but before the file is downloaded, without resorting to a second button. Maybe adding a clickListener to the downloadBtn will help? It would look something like this (edit: this approach does not work)
downloadBtn.addClickListener(click -> {
wb = createWorkbookUsingCurrentParameters(textField, datePicker, ...);
buttonWrapper.setResource("shortIdReport", () -> new ByteArrayInputStream(getWorkBookBytes(wb)));
});
I have in fact just tested this, and as I feared, using a clickListener to change the underlying StreamResource does not work, as the previously defined StreamResource is downloaded before it is changed by the clickListener.
Maybe Olli - the author of the FileDownloadWrapper - knows how to create the file upon clicking the download button, before the actual download?
Until then, you could set up a workaround that involves 2 displayed buttons. The first one is a normal Button, always visible and is for creating the excel file (it would have the clickListener shown in the last codesample; generating a workbook, and resetting the resource for the buttonWrapper). Additionally, it will make the second button visible which will be the actual downloadBtn/buttonWrapper, now it has an updated StreamResource before its clicked.