SXSSFWorkbook使用——使用excel模板

野性酷女 2023-10-02 13:48 8阅读 0赞

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);
            }
        }
    
    }