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