POI导入导出

Controller

public void export() {

        String filePath = "";
        StringBuffer modelPath = new StringBuffer();
        try {
            filePath = getRequest().getServletContext().getResource("filemodel/excel/xxx.xlsx").getPath();
        } catch (MalformedURLException e) {
            e.printStackTrace();
            renderErr("文件路径错误");
        }
        File file=new File(filePath);
        List<Object[]> list = XXX.me.export(); // 查询到数据
        Workbook book = XXX.me.getExcelModel(file, list); // Service方法 将数据与文件合并到 Workbook
        try {
            getResponse().setHeader("Content-Type","application/zip");
            getResponse().setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode("XXX.xlsx", "UTF-8"));
            book.write(getResponse().getOutputStream()); // 用数据流的方式响应给浏览器
        } catch(Exception e) {
            e.printStackTrace();
        }
        renderNull();
    }

Service

使用模板方法

public Workbook getExcelModel(File fileExcel, List<Object[]> list) {

        Workbook book = getExcelWorkbook(fileExcel);
        if(null == book) {
            return null;
        }
        Row row = null;
        Sheet sheet = book.getSheetAt(0);
        for(int i=0; i<list.size(); i++) {
            row = sheet.createRow(i+1);
            for(int j=0; j<list.get(i).length; j++) {
                row.createCell(j).setCellValue(list.get(i)[j] == null ? "" : list.get(i)[j].toString());
            }
        }
        return book;
    }

不使用模板方法

public Workbook getExcelModel(List<Object[]> list) {

        Workbook book = new XSSFWorkbook();
        if(null == book) {
            return null;
        }
        Row row = null;
        Sheet sheet = book.createSheet("指导书");
        sheet.setDefaultColumnWidth(20);
        try{
            row = sheet.createRow(0);
            row.createCell(0).setCellValue("XX");
            row.createCell(1).setCellValue("XXX");
            row.createCell(2).setCellValue("品牌");
            row.createCell(3).setCellValue("检查内容");
            row.createCell(4).setCellValue("检查方法");
            row.createCell(5).setCellValue("推荐值");
            row.createCell(6).setCellValue("判断标准");
            row.createCell(7).setCellValue("整改类别");
            row.createCell(8).setCellValue("整改建议");
            row.createCell(9).setCellValue("问题描述");
            for(int i=0; i<list.size(); i++) {
                row = sheet.createRow(i+1);
                for(int j=0; j<list.get(i).length; j++) {
                    row.createCell(j).setCellValue(list.get(i)[j] == null ? "" : list.get(i)[j].toString());
                }
            }
        } catch(Exception e) {
            e.printStackTrace();
        }

        return book;
    }