下载Excel模板样例。

1.首先查询出数据库的数据转化成json格式

如：String totalJson = paymentDayRptService.queryPayDayRtpForExport();

service层中获取到totalJson。

String totalList；;
List<PaymentDayRpt> paymentDayRptList = paymentDayRptDao.queryPayDayRtpExport(startDay,
        endDay, paymentPlatform, paymentPlatformSubType, serviceType, DM_SCHEMA);
for (PaymentDayRpt payDayRpt : paymentDayRptList) {
    Object[] arrs = {payDayRpt.getDayId(), 
          swithPlatform(payDayRpt.getPaymentPlatform()),
          swithSubType(payDayRpt.getPaymentPlatformSubType()), 
          swithServiceType(payDayRpt.getServiceType()),
            payDayRpt.getWbCdrs(), payDayRpt.getWbwjFees(), payDayRpt.getNbtzCdrs(),
            payDayRpt.getNbtzFees(), payDayRpt.getBclsCdrs(), payDayRpt.getBclsFees(),
            payDayRpt.getCxlsCdrs(), payDayRpt.getCxlsFees(),
            payDayRpt.getCylsCdrs(), payDayRpt.getCylsFees()};
    totalList.add(arrs);
}
if (totalList != null && totalList.size() > 0) {
    ObjectMapper mapper = new ObjectMapper();
    totalJson = mapper.writeValueAsString(totalList);

}

2.然后将Stringjson转化成List<String>.

List<String[]> totalList = null;
if (totalJson != null) {
    ObjectMapper mapper = new ObjectMapper();
    totalList = mapper.readValue(totalJson,
            new TypeReference<List<String[]>>() {
            });
}

3.找到模板路劲。

String filePath = this.getClass().getClassLoader().getResource(File.separator).getPath()
        + "/template/export/payDayRpt.xlsx";
File file = new File(filePath);

4.响应处理-关于IE浏览器和非IE的处理。

String userAgent = request.getHeader("User-Agent");
// 针对IE或者以IE为内核的浏览器：
if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {

    response.setHeader("Content-Disposition", "attachment;filename="
            + URLEncoder.encode(downFileName, "UTF-8"));
} else {
    // 非IE浏览器的处理：
    response.setHeader("Content-Disposition",
            "attachment;filename=" + new String(downFileName.getBytes("UTF-8"), "iso8859-1"));
}

5.将文件以流返回

InputStream is = new FileInputStream(filePath);

6.ExportUtils中的generateExcelByTemplate方法
参数一为eslx类型：一般都是"07"
参数二为从第几行开始：
参数三位totalList 
参数四为文件流
参数五为响应信息

ExportUtils.generateExcelByTemplate("07", 3, totalList, is, response.getOutputStream());

7.ExportUtils的工具类

    public static void generateExcelByTemplate(String type, int startRowNum,
                                               List<String[]> dataList, InputStream is,
                                               OutputStream os) throws IOException {
        if ("07".equals(type)) {
            SXSSFWorkbook wb = null;

            try {
                XSSFWorkbook workbook = new XSSFWorkbook(OPCPackage.open(is));
                wb = new SXSSFWorkbook(workbook, 100);
            } catch (InvalidFormatException var9) {
                var9.printStackTrace();
            }
            if (wb != null) {
                Font font = wb.createFont();
                font.setFontHeightInPoints((short) 10);
                font.setFontName("宋体");
                Sheet sheet = wb.getSheetAt(0);
                CellStyle style = wb.createCellStyle();
                style.setFont(font);
                writeXSSFExcel(startRowNum, sheet, style, dataList);
                wb.write(os);
                os.close();
                wb.dispose();
            }
        }

        if ("03".equals(type)) {
            generateExcelByTemplate(startRowNum, dataList, is, os);
        }

    }




    private static void writeXSSFExcel(int startRowNum, Sheet sheet,
                                       CellStyle style, List<String[]> dataList) {
        if (dataList != null && dataList.size() > 0) {
            Row row = null;
            Cell cell = null;
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            sheet.setDefaultColumnWidth(20);
            for (String[] temp : dataList) {
                row = sheet.createRow(startRowNum - 1);
                for (int i = 0; i < temp.length; i++) {
                    cell = row.createCell(i);
                    cell.setCellStyle(style);
                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    cell.setCellValue(new XSSFRichTextString(temp[i]));
                }
                startRowNum++;
            }
        }
    }


    public static void generateExcelByTemplate(int startRowNum, List<String[]> dataList,
                                               InputStream is, OutputStream os) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is));
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 11);
        font.setFontName("宋体");
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(font);
        writeExcel(startRowNum, sheet, style, dataList);
        wb.write(os);
        os.flush();
    }

    private static void writeExcel(int startRowNum, Sheet sheet, CellStyle style,
                                   List<String[]> dataList) {
        if (dataList != null && dataList.size() > 0) {
            Row row = null;
            Cell cell = null;
            style.setAlignment((short) 2);
            style.setBorderBottom((short) 1);
            style.setBorderTop((short) 1);
            style.setBorderLeft((short) 1);
            style.setBorderRight((short) 1);
            sheet.setDefaultColumnWidth(20);

            for (Iterator var6 = dataList.iterator(); var6.hasNext(); ++startRowNum) {
                String[] temp = (String[]) var6.next();
                row = sheet.createRow(startRowNum - 1);

                for (int i = 0; i < temp.length; ++i) {
                    cell = row.createCell(i);
                    cell.setCellStyle(style);
                    cell.setCellType(1);
                    cell.setCellValue(new HSSFRichTextString(temp[i]));
                }
            }
        }

    }

以上按步骤应该可以正常导出。

 

bug出现的可能就是，最后注意文件xlsx中，不能删除任何列，不然后少数据。