I need to download an excel spreadsheet with two sheets from two byte arrays from spring REST. Thanks

CodePudding user response：

To convert two byte arrays to two Excel sheets in a single Apache POI HSSFWorkbook, you can follow these steps:

First, create an instance of HSSFWorkbook using the following code:

HSSFWorkbook workbook = new HSSFWorkbook();

Next, create two instances of HSSFSheet using the following code:

HSSFSheet sheet1 = workbook.createSheet("Sheet1");
HSSFSheet sheet2 = workbook.createSheet("Sheet2");

Now, you can read the byte arrays into the sheets using the following code:

InputStream is1 = new ByteArrayInputStream(byteArray1);
InputStream is2 = new ByteArrayInputStream(byteArray2);

Then, use the following code to read the data from the input streams into the sheets:

HSSFWorkbook workbook1 = new HSSFWorkbook(is1);
HSSFWorkbook workbook2 = new HSSFWorkbook(is2);

Finally, you can write the workbook to a file using the following code:

FileOutputStream fos = new FileOutputStream("filename.xls");
workbook.write(fos);

This will create an Excel file with two sheets, each containing the data from the corresponding byte array.

CodePudding user response：

To download an Excel spreadsheet with two sheets from two byte arrays using a Spring REST application, you can use the following steps:

Create a new class that extends the AbstractXlsxView class and implement the buildExcelDocument method. This method will be responsible for creating the Excel spreadsheet and adding the sheets to it.

In the buildExcelDocument method, use the Workbook and Sheet classes from the Apache POI library to create a new workbook and add two sheets to it.

For each sheet, create a new ByteArrayOutputStream object and write the data from the corresponding byte array to it.

Use the POIFSFileSystem class to create a new file system and write the data from the ByteArrayOutputStream to it.

Add the file system to the sheet using the setReadOnly method.

Return the workbook as the model object in the ModelAndView object that is returned by the view.

In the Spring REST controller, create a new ModelAndView object and set the view to be the class that you created in step 1. Set the byte arrays as model attributes and add them to the ModelAndView object.

In the controller method, return the ModelAndView object. This will trigger the view to create the Excel spreadsheet and send it to the client as a download.

Here is an example of how this could be implemented in a Spring REST controller:

public ModelAndView downloadExcel(@RequestParam("sheet1Data") byte[] sheet1Data,
                                  @RequestParam("sheet2Data") byte[] sheet2Data) {
    ModelAndView modelAndView = new ModelAndView(new ExcelView());
    modelAndView.addObject("sheet1Data", sheet1Data);
    modelAndView.addObject("sheet2Data", sheet2Data);
    return modelAndView;
}

public class ExcelView extends AbstractXlsxView {

    @Override
    protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,HttpServletResponse response) throws Exception {
Sheet sheet1 = workbook.createSheet("Feuille 1");
Sheet sheet2 = workbook.createSheet("Feuille 2");

    byte[] sheet1Data = (byte[]) model.get("sheet1Data");
    byte[] sheet2Data = (byte[]) model.get("sheet2Data");

    ByteArrayOutputStream sheet1OutputStream = new ByteArrayOutputStream();
    sheet1OutputStream.write(sheet1Data);

    ByteArrayOutputStream sheet2OutputStream = new ByteArrayOutputStream();
    sheet2OutputStream.write(sheet2Data);

    POIFSFileSystem sheet1FileSystem = new POIFSFileSystem(sheet1OutputStream);
    POIFSFileSystem sheet2FileSystem = new POIFSFileSystem(sheet2OutputStream);

    sheet1.setReadOnly(true);
    sheet1.getPackagePart().getOutputStream().write(sheet1FileSystem.getRoot().getEntry("Workbook").getDocument().getBytes());

    sheet2.setReadOnly(true);
    sheet2.getPackagePart().getOutputStream().write(sheet2FileSystem.getRoot().getEntry("Workbook").getDocument().getBytes());
}

Note that you will need to include the Apache POI library in your project in order to use the Workbook, Sheet, and POIFSFileSystem classes. You can find more information about these classes and how to use them in the Apache POI documentation: https://poi.apache.org/apidocs/.