Return particular sheet from Workbook in byte[]

Question

I have this excel workbook stored in my resources folder. Considering this excel is of 9 pages i want to extract a particular page and return it into from of byte[].

I created this controller

@GetMapping(value = "/getSinglePage/{pageNumber}")
    private ResponseEntity<byte[]> getExcelByPageNumber(@PathVariable Integer pageNumber) throws IOException {
        return new ResponseEntity<>(service.getExcelByPageNumber(pageNumber), HttpStatus.OK);
    }

The service code contains--

public byte[] getExcelByPageNumber(Integer pageNumber) throws IOException {
        
        Workbook workbook = null;
        byte[] byteArray = null;
        // here file.xls is my excel sheet that contains 9 sheet for example

        workbook = WorkbookFactory.create(loader.getResource("classpath:file.xls").getInputStream());
        //gets the sheet from given page number 
        Sheet sheet = workbook.getSheetAt(pageNumber);
    
        // now i want to return this sheet in the form of byte[]
        return byteArray;
    }

How should I return it into form of byte[]?

Answer 1

By doing some POC I get to know a single worksheet could not exist without having its own workbook. And Workbook has restricted libraries to work on. So apparently I could not find any direct solution to copy or append the sheet.

What i did is I removed the sheets which are not needed that is keeping only sheet in the workbook and then writing it using ByteArrayOutputStream.

 public byte[] getExcelByPageNumber(Integer pageNumber) throws IOException {
        // TODO Auto-generated method stub
        Workbook workbook = new XSSFWorkbook();
        workbook = WorkbookFactory.create(loader.getResource("classpath:file.xls").getInputStream());
        Sheet tmpSheet = workbook.getSheetAt(pageNumber - 1);
        for (int i = workbook.getNumberOfSheets() - 1; i >= 0; i--) {

            String sheetName = workbook.getSheetName(i).trim();

            if (!tmpSheet.getSheetName().equals(sheetName)) {
                workbook.removeSheetAt(i);
            }

        }

        ByteArrayOutputStream bos = new ByteArrayOutputStream();

        try {
            workbook.write(bos);
        } finally {
            bos.close();
        }
        byte[] bytes = bos.toByteArray();
        return bytes;

    }