Excel导出超过字段限制65535 解决方案:
亲测有效
现场数据是11万条,点击导出后台报: Invalid row number (65536) outside allowable range (0…65535)
话不多说,直接看代码
解释一下:表头需要提前加好,因为大家的标题都不一样,所以没加。
之前代码是 :
response.reset();
response.setContentType("application/pdf");
response.setHeader("Content-Disposition", "attachment;filename="
+ fileName);
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
OutputStream output = response.getOutputStream();
// 初始化excel信息
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = null;
HSSFCell cell = null;
int index = 0;//记录额外创建的sheet数量
for (int r = 0; r < list.size(); r++) {// excel row
row = sheet.createRow(r);
String[] column = list.get(r);
for (int c = 0; c < column.length; c++) {// excel cell
cell = row.createCell(c);
cell.setCellValue(new HSSFRichTextString(column[c]));
}
}
wb.write(output);
output.flush();
output.close();
修改完之后是 :
response.reset();
response.setContentType("application/pdf");
response.setHeader("Content-Disposition", "attachment;filename="
+ fileName);
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
OutputStream output = response.getOutputStream();
// 初始化excel信息
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = null;
HSSFCell cell = null;
int index = 0;//记录额外创建的sheet数量
for (int r = 0; r < list.size(); r++) {// excel row
if ((r + 1) % 65535 == 0) {
sheet = wb.createSheet();
row = sheet.createRow(0);
String[] column = list.get(0);
for (int c = 0; c < column.length; c++) {
cell = row.createCell(c);
cell.setCellValue(new HSSFRichTextString(column[c]));
}
index++;
}
row = sheet.createRow((r + 1) - (index * 65535));
String[] column = list.get(r);
for (int c = 0; c < column.length; c++) {// excel cell
cell = row.createCell(c);
cell.setCellValue(new HSSFRichTextString(column[c]));
}
}
wb.write(output);
output.flush();
output.close();
解决思想是 : 因为Excel一张(或者说一页)默认行数是65535
如果行数太多 直接新建一张(一页),问题就解决了!!!
导出结果如图: