关于POI导出Excel多级表头带标题与表尾的封装方法

表头带标题与表尾的封装方法"/>

关于POI导出Excel多级表头带标题与表尾的封装方法

前言：关于该方法呢主要是来源于该博主提供的资料，我主要是稍微修改下并记录。额外添加的内容就是表格标题，理论上支持导出一级、二级、三级等多级表头Excel文档，测试一级、二级是OK的，先上效果图如下：

  这是导出一级表头的Excel文档效果图：

  这是导出二级表头的Excel文档效果图：

一级表头的实现

  首先呢说下一级表头是实现，从简单的开始：

先上工具类封装的统一方法：

package com.ylz.packcommonmon.util;import com.ylz.bizDo.statisticalReports.vo.Record;
import org.apachemons.lang.StringUtils;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;/**** @ClassName: ExcelUtils* @Description:POI实现导出含多级表头和含有表尾部信息的excel* @Auther: lyb* @Date: 2019/12/17 11:21* @version : V1.0*/
public class ExcelUtils {/** * @Author lyb * @Description //TODO 多级表头Excel文件导出* @Date 11:24 2019/12/17 * @Param [sheetName, head, dataList, type, tableEndData，out，titles] sheet名,多级表头,导出数据,导出类型,表尾,输出文件对象,首行标题* @return org.apache.poi.xssf.usermodel.XSSFWorkbook **/ public static XSSFWorkbook exportMultilevelHeader(String sheetName, String[][] head, List<?> dataList, Class type, String[][] tableEndData, OutputStream out,String titles) {/*变量*/String[] properties;Object[] rowValue;List<Object[]> values;Field[] fields;XSSFCell cell;String vo;/*导出Excel*/// 第一步，创建一个workBook，对应一个Excel文件XSSFWorkbook wb = new XSSFWorkbook();// 表头 标题样式XSSFFont titleFont = wb.createFont();titleFont.setFontName("微软雅黑");//字体titleFont.setFontHeightInPoints((short) 15);// 字体大小XSSFCellStyle titleStyle = wb.createCellStyle();titleStyle.setFont(titleFont);titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中titleStyle.setLocked(true);// 第二步，在workBook中添加一个sheet,对应Excel文件中的sheetXSSFSheet sheet = wb.createSheet(sheetName);// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制shortXSSFRow row;// 第四步，创建单元格，并设置值表头 设置表头居中//生成一个StyleXSSFCellStyle style = wb.createCellStyle();style.setWrapText(true);style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中int mergerNum = 0; //合并数//添加表格标题sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,  type.getDeclaredFields().length- 1));row = sheet.createRow(0);//创建一行表格row.setHeight((short) 0x349);//设置高度cell = row.createCell(0);//创建单元格cell.setCellStyle(titleStyle);//设置样式cell.setCellValue(titles);//设置标题//给单元格设置值for (int i = 0; i < head.length; i++) {row = sheet.createRow(i+1);row.setHeight((short) 700);for (int j = 0; j < head[i].length; j++) {cell = row.createCell(j);cell.setCellStyle(style);cell.setCellValue(head[i][j]);}}Map<Integer, List<Integer>> map = new HashMap<Integer, List<Integer>>();   // 合并行时要跳过的行列//合并行for (int i = 0; i < head[head.length - 1].length; i++) {if ("".equals(head[head.length - 1][i])) {for (int j = head.length - 2; j >= 0; j--) {if (!"".equals(head[j][i])) {sheet.addMergedRegion(new CellRangeAddress(j+1, head.length, i, i)); // 合并单元格break;} else {if (map.containsKey(j)) {List<Integer> list = map.get(j);list.add(i);map.put(j, list);} else {List<Integer> list = new ArrayList<Integer>();list.add(i);map.put(j, list);}}}}}//合并列for (int i = 0; i < head.length - 1; i++) {for (int j = 0; j < head[i].length; j++) {List<Integer> list = map.get(i);if (list == null || (list != null && !list.contains(j))) {if ("".equals(head[i][j])) {mergerNum++;if (mergerNum != 0 && j == (head[i].length - 1)) {sheet.addMergedRegion(new CellRangeAddress(i, i, j - mergerNum, j)); // 合并单元格mergerNum = 0;}} else {if (mergerNum != 0) {sheet.addMergedRegion(new CellRangeAddress(i+1, i+1, j - mergerNum - 1, j - 1)); // 合并单元格mergerNum = 0;}}}}}//解析导出类型Class<Record> recordClass = Record.class;if (null == type) {//导出失败return null;} else if (type.equals(recordClass)) {//导出List<Record>//获取Record中包含的properties，用于生成表格头及创建Cellproperties = getRecordProperties(dataList, null);vo = "record";} else {//导出List<Bean>//获取Bean的Fieldfields = type.getDeclaredFields();properties = getRecordProperties(null, fields);vo = "bean";}if (null == head) {int i = 0;if (head.length > 0) {i = head.length - 1;}head[i] = properties;}// 第五步，写入实体数据/*表头行数*/int m = 1;if (head.length > 0) {m = head.length;}values = getRowValue(dataList, properties, vo);for (int i = 0; i < dataList.size(); i++) {row = sheet.createRow(i + m+1); //创建行rowValue = values.get(i);// 第四步，创建单元格，并设置值for (int j = 0; j < properties.length; j++) {cell = row.createCell(j);cell.setCellStyle(style);setCellValue(cell, rowValue[j]);}}//第六步，处理表格尾部的数据if (tableEndData != null && tableEndData.length > 0) {for (int i = 0; i < tableEndData.length; i++) {row = sheet.createRow(dataList.size() + m + i);sheet.addMergedRegion(new CellRangeAddress(dataList.size() + m + i, dataList.size() + m + i, 0,  type.getDeclaredFields().length- 1));for (int j = 0; j < tableEndData[i].length; j++) {cell = row.createCell(j);cell.setCellStyle(style);setCellValue(cell, tableEndData[i][j]);}}}try {wb.write(out);out.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}return wb;}/** * @Author lyb * @Description //TODO 获取Record包含的所有properties * @Date 11:30 2019/12/17 * @Param [list, fields] 列名,属性* @return java.lang.String[] 包含properties**/ private static String[] getRecordProperties(List<?> list, Field[] fields) {if (null != list && null == fields) {Record record = (Record) list.get(0);Set<String> keySet = record.keySet();List<String> keysList = new ArrayList<>(keySet);return keysList.toArray(new String[keysList.size()]);} else if (null != fields && null == list) {String[] properties = new String[fields.length];for (int i = 0; i < fields.length; i++) {properties[i] = fields[i].getName();}return properties;}return new String[0];}/** * @Author lyb * @Description //TODO 转换列表数据* @Date 11:33 2019/12/17 * @Param [list, properties, vo] 数据列表,属性列表,类型* @return java.util.List<java.lang.Object[]> 转换后的数据**/ private static List<Object[]> getRowValue(List<?> list, String[] properties, String vo) {List<Object[]> resultList = new ArrayList<>();Record record;if (StringUtils.isBlank(vo)) {return resultList;}else if ("record".equals(vo)) {for (Object object : list) {record = (Record) object;Object[] values = new Object[properties.length];    //定义在外部数组值会被最后写入的覆盖for (int i = 0; i < properties.length; i++) {values[i] = record.get(properties[i]);}resultList.add(values);}return resultList;}else if ("bean".equals(vo)) {for (Object object : list) {Class cf = object.getClass();Object[] values = new Object[properties.length];    //定义在外部数组值会被最后写入的覆盖for (int i = 0; i < properties.length; i++) {char[] name = properties[i].toCharArray();name[0] -= 32;try {Method method = cf.getMethod("get" + String.valueOf(name));values[i] = method.invoke(object);} catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {e.printStackTrace();}}resultList.add(values);}return resultList;}return resultList;}/** * @Author lyb * @Description //TODO 设置单元格值* @Date 11:34 2019/12/17 * @Param [cell, value] 单元格,值* @return void **/ private static void setCellValue(XSSFCell cell, Object value) {if (value instanceof String) {cell.setCellValue((String) value);cell.setCellType(XSSFCell.CELL_TYPE_STRING);} else if (value instanceof Date) {cell.setCellValue((Date) value);cell.setCellType(XSSFCell.CELL_TYPE_STRING);} else if (value instanceof Boolean) {cell.setCellValue((Boolean) value);cell.setCellType(XSSFCell.CELL_TYPE_BOOLEAN);} else if (value instanceof Double) {cell.setCellValue((Double) value);cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);} else if (value instanceof Calendar) {cell.setCellValue((Calendar) value);cell.setCellType(XSSFCell.CELL_TYPE_STRING);} else if (value instanceof RichTextString) {cell.setCellValue((RichTextString) value);cell.setCellType(XSSFCell.CELL_TYPE_STRING);} else {cell.setCellValue(String.valueOf(value));cell.setCellType(XSSFCell.CELL_TYPE_STRING);}}/*** @Author lyb* @Description //TODO 测试方法* @Date 13:26 2019/12/19* @Param [args]* @return void**/public static void main(String[] args) throws Exception{
//        //标题
//        String titles="小脆皮";
//
//        //表头名
//        String[][] headNames = {{"鲁班","小乔","安琪拉","甑姬","王昭君"}};
//
//        //表尾名
//        String[][] tableEnd = {{"不准看：    "}};
//
//        List<Testvo> list = new ArrayList<>();
//        for (int i=0;i<5;i++) {
//            Testvo vo=new Testvo();
//            vo.setNo("1");
//            vo.setName("鲁班大师");
//            vo.setSex("男");
//            vo.setAge(26);
//            vo.setMoney("13888");
//            list.add(vo);
//        }
//
//        OutputStream out = new FileOutputStream("C:\\Users\\lyb\\Desktop\\测试汇总表.xls");
//
//        //导出
//        exportMultilevelHeader("测试汇总",headNames,list,Testvo.class,tableEnd,out,titles);}
}

  这里有一个类需要说明下，就是Record类进行重写了，作用其实就是中间接收我们需导出数据的实体类属性，该类贴码如下：

package com.ylz.bizDo.statisticalReports.vo;/**** @ClassName: Record* @Description:该类用于POI多级表头Excel文件导出,用于接收导出实体属性* @Auther: lyb* @Date: 2019/12/17 11:30* @version : V1.0*/
import java.math.BigDecimal;
import java.util.Date;
import java.util.LinkedHashMap;public class Record extends LinkedHashMap<String,Object> {public void set(String field,Object value){put(field,value);}public String getString(String field){return (String)get(field);}public Integer getInteger(String field){return (Integer)get(field);}public Long getLong(String field){return (Long)get(field);}public BigDecimal getBigDecimal(String field){return (BigDecimal)get(field);}public Date getDate(String field){return (Date)get(field);}public Boolean getBoolean(String field){return (Boolean) get(field);}
}

  然后呢就是一级表头文档导出的测试方法了：

    public static void main(String[] args) throws Exception{//标题String titles="小脆皮";//表头名String[][] headNames = {{"鲁班","小乔","安琪拉","甑姬","王昭君"}};//表尾名String[][] tableEnd = {{"不准看：    "}};List<Testvo> list = new ArrayList<>();for (int i=0;i<5;i++) {Testvo vo=new Testvo();vo.setNo("1");vo.setName("鲁班大师");vo.setSex("男");vo.setAge(26);vo.setMoney("13888");list.add(vo);}OutputStream out = new FileOutputStream("C:\\Users\\lyb\\Desktop\\测试汇总表.xls");//导出exportMultilevelHeader("测试汇总",headNames,list,Testvo.class,tableEnd,out,titles);}

  至于TestVo类就不需要我贴了吧，只是一个测试类只有get、set方法，属性就循环的那些。

  至于二级表头的测试方法如下，主要内容都在工具类里面。基本上都有写注释相信应该可以看明白。

    /*** @Author lyb* @Description //TODO 家庭签约登记统计Excel导出* @Date 9:43 2019/12/17* @Param []* @return java.lang.String**/public String findRegistrationStatisticalToExcel() {try {//查询条件RegistrationListQvo qvo = (RegistrationListQvo) getJsonLay(RegistrationListQvo.class);if(qvo==null){qvo=new RegistrationListQvo();}//登录人信息CdUser user = this.getSessionUser();//导出数据List<RegistrationStatisticalExportVo> listData = sysDao.getStattisticalReportsDao().registrationListExport(qvo);//Excel导出标题String titles = "登记表";//表头名String[][] headNames = {{"姓名","性别","身份证号","联系电话","签约编码","签约家庭类别", "", "", "", "","","", "重点人群签约服务项目", "","","","","","", "其他人群", "","","","","签约服务包类型","备注"},{"","","","","","计生失独伤残家庭", "计生独生子女", "计生双女", "五保户", "低保户","建档立卡贫困人口","其他", "老年人","高血压患者","2型糖尿病患者","严重精神障碍患者","结核病患者","孕产妇","0-6岁儿童", "残疾人", "脑血管病患者", "冠心病患者","癌症患者","其他","",""}};//表尾名String[][] tableEnd = {{"填报人:          分管院长：                填报时间：    年    月    日"}};getResponse().reset();getResponse().setContentType("application/vnd..ms-excel");getResponse().setHeader("content-Disposition","attachment;filename="+ URLEncoder.encode("汇总表.xls","utf-8"));ExcelUtils.exportMultilevelHeader("汇总表",headNames,listData, RegistrationStatisticalExportVo.class,tableEnd,getResponse().getOutputStream(),titles);}catch (Exception e) {e.printStackTrace();new ActionException(getClass(), getAct(), getJsons(), e);}return null;}

代码什么的都已经贴完了，也没别的东西了。

本次记录到此结束，欢迎订阅、关注、收藏、评论、点赞哦～～(￣▽￣～)～

哇咔咔(∪｡∪)｡｡｡zzz