CODEWITHSUNDEEP
javaspringxls
Belham
Belham

Reputation: 165

Why the java saveSpreadsheetRecords method limits in 65535 rows to write an xls file?

I'm implementing the snipet code bellow to export an xls file in the browser in a spring application :

@RequestMapping(method = RequestMethod.POST, value = ResourcesPath.EXCEL,
        consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE)
@ResponseStatus(HttpStatus.OK)
public void loadExcel(ReportFilter filter, HttpServletResponse response) throws
        IOException {

    List<ReportItemVO> result = // list of ReportItemVO ...
    Date today = new Date();
    response.addHeader("Content-Disposition", "attachment; filename=today.getTime() + ".xls");

    BufferedOutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
    xlsExportService.saveSpreadsheetRecords(ReportItemVO.class, result, outputStream);

    outputStream.flush();
    outputStream.close();
}

public interface XlsExportService extends SpreadsheetService {
}
@Autowired
private XlsExportService xlsExportService;

Here everything works well when exporting data of result.size() < 65535. Otherwise "java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)" is thrown.

I found that saveSpreadsheetRecords uses HSSFSheet which is limited in 65535 rows, and that I need to use a XSSHSheet, but I didn't find out any clue to use this in a way to have (ReportItemVO.class, result, outputStream) as an input.

Any idea please?

Upvotes: 0

Views: 480

Answers (2)

locus2k
locus2k

Reputation: 2935

You should be implementing SpreedsheetService not extending it

@Component
public class XlsExportService implements SpreedsheetService {

}

Then you can fill in the methods to use XSSFSheet instead of HSSFSheets

Upvotes: 1

steenbergh
steenbergh

Reputation: 1771

In the old Excel format of XLS, the maximum number of rows is 65535: see this.

Try saving in XLSX format, that goes up to 1,048,576 rows.

Upvotes: 1

Related Questions