POI导出Excel工具

POI导出Excel工具

Maven依赖（此处为本人所用的）

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency> 
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>

ExcelExportUtil导出工具（直接复制可用）

/** * Excel表格导出工具类 * @author ypl * */
public class ExcelExportUtil {  

    private static Logger logger = Logger.getLogger(ExcelExportUtil.class);

    /** * 获取封装好数据的 HSSFWorkbook * @param list 内容数据(map中key对应headRow的每一个头,value为对应的值) * @param headRowList 头行标题的集合 * @param tableName 表标题 * @param widthMap 各列列宽(map中 key：指定列（从0开始）,value:宽度(一般3-4个字符：16,时间类型+分秒：25)) * @param response * @throws IOException */
    public static void generateExcel(List<Map<String, String>> list, String tableName, Map<Integer,Integer> widthMap, HttpServletResponse response) throws IOException {
    if (list == null || list.isEmpty()) {
        logger.info("要导出的数据列为空");
        return;
    }
    if (StringUtils.isBlank(tableName)) {
        logger.info("要导出的表名为空");
        return;
    }
     //获取头行标题集合
        List<String> headRowList = new ArrayList<>(14);
        Set<String> keySet = list.get(0).keySet();
         for (String key : keySet) {
             headRowList.add(key);
        }
        if (headRowList == null || headRowList.isEmpty()) {
        logger.info("要导出的数据表头行为空");
        return;
    }
    logger.info("getexcel start");

    HSSFWorkbook book = new HSSFWorkbook();
    OutputStream outputStream = null;
        try{  
            HSSFSheet sheet = book.createSheet(tableName); 

            //设置列宽
            if(widthMap!=null && widthMap.size()>0){
            setByWidthMap(sheet, widthMap);
            }else {
            setDefaultSheetWidth(sheet,headRowList.size());
        }

            //样式一设置
            HSSFCellStyle style = book.createCellStyle();  
            setStyle(style);

            // 生成一个字体 ,设置字体 
            HSSFFont font = book.createFont();  
            setFont(style, font, 22, "宋体");  

            //样式内容设置 
            HSSFCellStyle bodyStyle = book.createCellStyle();  
            setStyle(bodyStyle);
            bodyStyle.setWrapText(true);//自动换行 

            //样式头设置
            HSSFCellStyle headstyle = book.createCellStyle();
            setStyle(headstyle);

            // 生成首列头 字体 ,并设置字体 
            HSSFFont headfont = book.createFont(); 
            setFont(headstyle, headfont, 11, null);

            outputStream = response.getOutputStream();
        // response.setContentType("application/dowload");
        response.reset();
        response.setContentType("application/msexcel");
        response.setHeader("Content-disposition","attachment;filename=\"" + new String(
                (java.net.URLEncoder.encode(tableName + CommonUtils.getNowDateStringOf8() + (int) (Math.random() * 100) + ".xls", "UTF-8")).getBytes("UTF-8"),"GB2312") + "\"");

            //填充表头标题 
            int colSize = list.get(0).entrySet().size();  
            //合并单元格供标题使用(表名) 
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colSize-1));  
            HSSFRow firstRow = sheet.createRow(0);//第几行（从0开始） 
            HSSFCell firstCell = firstRow.createCell(0);  
            firstCell.setCellValue(tableName);  
            firstCell.setCellStyle(style);  

            //填充表头header 
            HSSFRow headRow = sheet.createRow(1);
            headRow.setHeight((short) (20 * 20));  
            for (int i = 0; i < headRowList.size(); i++) {
            HSSFCell cell = headRow.createCell(i);
            cell.setCellValue(headRowList.get(i));  
                cell.setCellStyle(headstyle);
            }             

            //填充表格内容 
            for(int i=0; i<list.size(); i++) {  
                HSSFRow row2 = sheet.createRow(i+2);//index：第几行 
                row2.setHeight((short) (25 * 20));  
                Map<String, String> map = list.get(i);  
                for(int j=0; j<headRowList.size(); j++) {  
                    String value = map.get(headRowList.get(j));  
                    HSSFCell cell = row2.createCell(j);//第几列：从0开始 
                    cell.setCellValue(value);  
                    cell.setCellStyle(bodyStyle);  
                }  
            }  

            // 写进文档
            book.write(outputStream);
        outputStream.flush();
        outputStream.close();
        } catch(Exception e) {  
            logger.error("jftj/genexcel Exception", e);
        } finally {
        if (outputStream != null) {
        outputStream.close();
        }
        logger.info("getexcel end");
    }  
    }

    /** * 字体设置 * @param style * @param font */
    private static void setFont(HSSFCellStyle style, HSSFFont font, int size, String fontName) {
    //font.setColor(HSSFColor.VIOLET.index); 
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
    font.setFontHeightInPoints((short) size); //设置字体大小 
    if(StringUtils.isNotBlank(fontName)){
        font.setFontName(fontName);    
    }
    style.setFont(font);// 把字体应用到当前的样式
    }

    /** * 根据widthMap设置相应的列宽 * @param sheet * @param widthMap */
    private static void setByWidthMap(HSSFSheet sheet, Map<Integer, Integer> widthMap) {
    for (Entry<Integer, Integer> entry : widthMap.entrySet()) {
        sheet.setColumnWidth(entry.getKey(), entry.getValue() * 256); 
    }
    }

    /** * 默认宽度设置 * 3-4个中文字符 16 * 时间类型 ,带时分秒的 25 * @param sheet * @param len */
    private static void setDefaultSheetWidth(HSSFSheet sheet, int len) {
    for (int i = 0; i < len; i++) {
        sheet.setColumnWidth(i, 25 * 256);
    }
    }

    /** * 私密直播单元格宽度设置 * @param sheet */
    private static void setStyle(HSSFCellStyle style) {
    style.setFillForegroundColor(HSSFColor.WHITE.index);  //设置背景颜色
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中 
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
    }

}  

导出Controller

    /** * 导出检验 * * @param request * @param response * @param customer * @throws Exception */
    @RequestMapping(value = "/checkExport", method = RequestMethod.GET)
    public void checkExport(String searchText, String beginTime, String endTime, Integer orderStatus, String objectId, Integer orderSource, Integer productId, Integer payPlatForm,
                        Integer payType,HttpServletRequest request, HttpServletResponse response) throws Exception {
    int count = payServiceRemoting.countXsbOrderMng(searchText, beginTime, endTime, orderStatus, objectId, orderSource, productId, payPlatForm, payType);
    if (count >10000) {
        putFailedResult(response, "导出数据超过10000条");//此处是将结果转换成json字符串，并返回成功或者失败给页面js进行判断 是否进行导出
        return;
    }
    if (count == 0) {
        putFailedResult(response, "导出数据超过为空");
        return;
    }
    putSuccessResult(response, "操作成功");
    return;
    }

    /** * 导出 * @param model * @param applyLiveStatus * @param searchkey * @param pageNo * @param pageSize * @param request * @param response * @return * @throws IOException */
    @RequestMapping(value = "exportExcel",method = RequestMethod.GET)
    public void  exportExcel(String searchText, String beginTime, String endTime, Integer orderStatus, String objectId, Integer orderSource, Integer productId, Integer payPlatForm,
                     Integer payType,HttpServletRequest request, HttpServletResponse response) throws IOException {

        //1.获取数据 
        List<XsbOrderVo> orderVos = payServiceRemoting.listXsbOrderMng(searchText, beginTime, endTime, orderStatus, objectId, orderSource, productId, payPlatForm, payType,null, null);

        //2.对数据内容封装成map key为excel表格的每列标题，value为对应数据
        List<Map<String, String>> data = new ArrayList<>();
        for (XsbOrderVo xsbOrderVo : orderVos) {
            Map<String, String> map = new LinkedHashMap<>();
            map.put("订单编号", xsbOrderVo.getOrderId());
            map.put("服务类型", xsbOrderVo.getOrderSource());
            map.put("项目名称", xsbOrderVo.getObjectName());
            map.put("姓名", xsbOrderVo.getCustomerName());
            map.put("手机", xsbOrderVo.getCustomerPhone());
            map.put("支付方式", xsbOrderVo.getPayType());
            map.put("付款金额(/元)", xsbOrderVo.getOrderAmount());
            map.put("支付状态", xsbOrderVo.getOrderStatus());
            map.put("订单来源", xsbOrderVo.getPayPlatForm());
            map.put("下单时间", xsbOrderVo.getCreateTime());
            data.add(map);
        }

        //3.excel表格每列宽度设置,excelColumnWidthMap:key为哪列(从0开始 0对应第一列),value为宽度(一般3-4个中文字符可填写16,时间类型到分秒的 25左右)
        //可以不设置excelColumnWidthMap,填写null即可 默认为25
        Map<Integer,Integer> widthMap = new HashMap<>();
        widthMap.put(0, 20);
        widthMap.put(1, 25);
        widthMap.put(2, 25);
        widthMap.put(3, 20);
        widthMap.put(4, 20);
        widthMap.put(5, 25);
        widthMap.put(6, 25);
        widthMap.put(7, 20);
        widthMap.put(8, 20);
        widthMap.put(9, 22);

        //编辑excel 
        ExcelExportUtil.generateExcel(data, "xxxx",widthMap response);
    }

页面

            //页面
            <form action="List.do" id="searchForm" style="width:100%;">
                ...
            <form/>
            <button type="button" class="btn btn-success mb5" style="float:right;" id = "exportExcel">导出项目列表</button>

            //导出excel
            $('#exportExcel').on('click',function(){
                var isCheck=false;
                var formData = $('#searchForm').serialize();
                $.ajax({
                    type: "GET",
                    url: "/checkExport",
                    async: false,
                    dataType:"json",
                    data: formData,
                    success: function(data) {
                        if (data.status == '1') {
                            isCheck=true;
                        } else {
                            alert(data.msg);
                        };
                    },
                    error : function() {
                        alert("下载失败");
                    } 
                })
                if(isCheck){
                    parent.location.href="/exportExcel?"+formData;
                }
             })