下载Excel模板样例。

电玩女神 2023-06-22 03:17 21阅读 0赞

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中，不能删除任何列，不然后少数据。**