SXSSFWorkbook使用

public class Hello {
    public static void main(String[] args) {
        // 1.导入excel模板
        String targetPath = rs.getSession().getServletContext()
                .getRealPath("/excelModel/" + fileName + ".xlsx");
        // 2.创建一个workbook，对应一个Excel文件
        File fi = new File(targetPath);

        FileInputStream is = new FileInputStream(fi);

        XSSFWorkbook wb = new XSSFWorkbook(is);

        int lastRowNum = wb.getSheetAt(0).getLastRowNum();

        if (fileName.contains("横向明细")) {
            wb.getSheetAt(0).getRow(3).getCell(0).setCellValue("分配期号：" + hxmxFpqh);
        }
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(wb, 1000);

        Sheet sheet = sxssfWorkbook.getSheetAt(0);
        // 开始填写查找出来的数据
        writeExcel(list, sheet, lastRowNum);
        // 下载
        tkDownload2(sheetName, sxssfWorkbook, res);
    }

    public static void writeExcel(List<?> list, Sheet sheet, int lastRowNum)
            throws IOException, IllegalArgumentException,
            IllegalAccessException {

        Row row;
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow((int) i + lastRowNum + 1);
            row.createCell(0).setCellValue(i + 1);
            Object tjb = list.get(i);
            Class class1 = (Class) tjb.getClass();
            Field[] fs = class1.getDeclaredFields();
            for (int j = 1; j < fs.length; j++) {
                Field f = fs[j];
                f.setAccessible(true);
                Object v = f.get(tjb);
                String type = f.getType().toString();
                if (v == null || v.toString() == "") {
                    row.createCell(j).setCellValue("");
                } else {

                    if (type.endsWith("Double") || type.endsWith("double")) {
                        row.createCell(j).setCellValue(
                                Double.parseDouble(v.toString()));
                    } else if (type.endsWith("Integer") || type.endsWith("int")) {
                        row.createCell(j).setCellValue(
                                Double.parseDouble(v.toString()));
                    } else if (type.endsWith("String")) {
                        row.createCell(j).setCellValue(v.toString());
                    }
                }
            }
        }
    }


    public static void tkDownload2(String sheetName, SXSSFWorkbook wb,
                                   HttpServletResponse res) throws IOException {
        String fileName = sheetName;
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);

        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        res.reset();
        res.setContentType("application/vnd.ms-excel;charset=utf-8");

        res.addHeader("Content-Disposition", "attachment;filename="
                + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));// 设置文件名


        try (ServletOutputStream out = res.getOutputStream();
             BufferedInputStream bis = new BufferedInputStream(is);
             BufferedOutputStream bos = new BufferedOutputStream(out)) {

            byte[] buff = new byte[2048];
            int bytesRead;
             // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            //e.printStackTrace();
            logger.error("tkDownload failed |{}", () -> e.toString());
            logger.debug("failed {}", e);
        }
    }

}