Excel 动态拼接表头实现导出

public class Column {
    //单元格内容
    private String content;
    //字段名称，用户导出表格时反射调用
    private String fieldName;
    //这个单元格的集合
    private List<Column> listTpamscolumn = new ArrayList<Column>();

    int totalRow;
    int totalCol;
    int row;//excel第几行
    int col;//excel第几列
    int rLen; //excel 跨多少行
    int cLen;//excel跨多少列
    private boolean HasChilren;//是否有子节点
    private int tree_step;//树的级别 从0开始
    private String id;
    private String pid;

    public Column() {
    }

    ;

    public Column(String content, String fieldName) {
        this.content = content;
        this.fieldName = fieldName;
    }

    public Column(String fieldName, String content, int tree_step) {
        this.tree_step = tree_step;
        this.fieldName = fieldName;
        this.content = content;
    }

    public int getTotalRow() {
        return totalRow;
    }

    public void setTotalRow(int totalRow) {
        this.totalRow = totalRow;
    }

    public int getTotalCol() {
        return totalCol;
    }

    public void setTotalCol(int totalCol) {
        this.totalCol = totalCol;
    }

    public String getPid() {
        return pid;
    }

    public void setPid(String pid) {
        this.pid = pid;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public boolean isHasChilren() {
        return HasChilren;
    }

    public void setHasChilren(boolean hasChilren) {
        HasChilren = hasChilren;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public String getFieldName() {
        return fieldName;
    }

    public void setFieldName(String fieldName) {
        this.fieldName = fieldName;
    }

    public List<Column> getListTpamscolumn() {
        return listTpamscolumn;
    }

    public void setListTpamscolumn(List<Column> listTpamscolumn) {
        this.listTpamscolumn = listTpamscolumn;
    }

    public int getTree_step() {
        return tree_step;
    }

    public void setTree_step(int tree_step) {
        this.tree_step = tree_step;
    }

    public int getRow() {
        return row;
    }

    public void setRow(int row) {
        this.row = row;
    }

    public int getCol() {
        return col;
    }

    public void setCol(int col) {
        this.col = col;
    }

    public int getrLen() {
        return rLen;
    }

    public void setrLen(int rLen) {
        this.rLen = rLen;
    }

    public int getcLen() {
        return cLen;
    }

    public void setcLen(int cLen) {
        this.cLen = cLen;
    }
}

/**
 * excel处理工具
 * 概念-> 表头数据:报表的表头
 * 行内数据:表头以下的数据
 * 功能:动态生成单级，多级Excel表头
 * 备注：tree型结构数据的root节点的id必须为零（0）
 */
public class ExcelTool<T> {

    private HSSFWorkbook workbook;//excel 对象
    private String title; //表格标题
    private int colWidth = 20; //单元格宽度
    private int rowHeight = 20;//单元格行高度
    private HSSFCellStyle styleHead; //表头样式
    private HSSFCellStyle styleBody; //主体样式
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //日期格式化,默认yyyy-MM-dd HH:mm:ss

    /**
     * 无参数 初始化 对象
     */
    public ExcelTool() {
        this.title = "sheet1";
        this.workbook = new HSSFWorkbook();
        init(0);
    }

    /**
     * 有参数 初始化 对象
     *
     * @param title
     * @param colWidth
     * @param rowHeight
     * @param dateFormat
     */
    public ExcelTool(String title, int colWidth, int rowHeight, String dateFormat) {
        this.colWidth = colWidth;
        this.rowHeight = rowHeight;
        this.title = title;
        this.workbook = new HSSFWorkbook();
        this.sdf = new SimpleDateFormat(dateFormat);
        init(0);
    }

    public ExcelTool(String title, int colWidth, int rowHeight) {
        this.colWidth = colWidth;
        this.rowHeight = rowHeight;
        this.title = title;
        this.workbook = new HSSFWorkbook();
        init(0);
    }

    public ExcelTool(String title, int colWidth, int rowHeight, int flag) {
        this.colWidth = colWidth;
        this.rowHeight = rowHeight;
        this.title = title;
        this.workbook = new HSSFWorkbook();
        init(flag);
    }

    public ExcelTool(String title) {
        this.title = title;
        this.workbook = new HSSFWorkbook();
        init(0);
    }

    /**
     * ExcelTool 属性 get、set 方法 开始
     */
    public int getColWidth() {
        return colWidth;
    }

    public void setColWidth(int colWidth) {
        this.colWidth = colWidth;
    }

    public int getRowHeight() {
        return rowHeight;
    }

    public void setRowHeight(int rowHeight) {
        this.rowHeight = rowHeight;
    }

    public HSSFWorkbook getWorkbook() {
        return this.workbook;
    }

    public void setWorkbook(HSSFWorkbook workbook) {
        this.workbook = workbook;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }


    public HSSFCellStyle getStyleHead() {
        return styleHead;
    }


    public void setStyleHead(HSSFCellStyle styleHead) {
        this.styleHead = styleHead;
    }


    public HSSFCellStyle getStyleBody() {
        return styleBody;
    }


    public void setStyleBody(HSSFCellStyle styleBody) {
        this.styleBody = styleBody;
    }

    /**
     * ExcelTool 属性 get、set 方法 结束
     */
    //内部统一调用的样式初始化
    private void init(int styleFlag) {
        this.styleHead = this.workbook.createCellStyle();
        this.styleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        this.styleHead.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        this.styleHead.setRightBorderColor(HSSFColor.BLACK.index);
        this.styleHead.setBottomBorderColor(HSSFColor.BLACK.index);
        switch (styleFlag) {
            case 1:
                this.styleBody = this.workbook.createCellStyle();
                this.styleBody.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中ALIGN_CENTER
                this.styleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
                this.styleBody.setRightBorderColor(HSSFColor.BLACK.index);
                this.styleBody.setBottomBorderColor(HSSFColor.BLACK.index);
                this.styleBody.setBorderRight((short) 1);// 边框的大小
                this.styleBody.setBorderBottom((short) 1);// 边框的大小
                break;
            default:
                this.styleBody = this.workbook.createCellStyle();
                this.styleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中ALIGN_CENTER
                this.styleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
                this.styleBody.setRightBorderColor(HSSFColor.BLACK.index);
                this.styleBody.setBottomBorderColor(HSSFColor.BLACK.index);
                this.styleBody.setBorderRight((short) 1);// 边框的大小
                this.styleBody.setBorderBottom((short) 1);// 边框的大小
                break;
        }
    }

    /**
     * 导出表格 无返回
     *
     * @param listParamColumn 表头数据
     * @param dataList        行内数据
     * @param FilePath        保存路径
     * @param flag
     * @param rowFlag
     * @throws Exception
     */
    public void exportExcel(List<Column> listParamColumn, List<T> dataList, String FilePath, boolean flag, boolean rowFlag) throws Exception {
        splitDataToSheets(dataList, listParamColumn, flag, rowFlag);
        save(this.workbook, FilePath);
    }

    /**
     * 导出表格 无返回
     *
     * @param listParamColumn 表头数据
     * @param dataList        行内数据
     * @param materialColumn  物料基础信息合并单元格
     * @param fileHelper      上传附件
     * @param vendorColumn    供应商合并单元格
     * @param vendorList      供应商数据
     * @param awardVO         计算定选价格
     * @param paramsCode      上传附件的场景值
     * @throws Exception
     */
    public FileInfoVo awardExportExcel(List<Column> listParamColumn, List<T> dataList, HashMap<String, String> paramsCode, int materialColumn, FileHelper fileHelper, int vendorColumn, List<QuoteAmountVO> vendorList, AwardVO awardVO) throws Exception {
        int dataCount = dataList.size();
        int maxColumn = 65535;
        int pieces = dataCount / maxColumn;
        for (int i = 1; i <= pieces; i++) {
            HSSFSheet sheet = this.workbook.createSheet(this.title + i);
            List<T> subList = dataList.subList((i - 1) * maxColumn, i * maxColumn);
            writeSheet(sheet, subList, listParamColumn, true, true);
        }
        String sheetName = this.title;
        if (pieces != 0) {
            sheetName = this.title + (pieces + 1);
        }
        HSSFSheet sheet = this.workbook.createSheet(sheetName);
        writeSheet(sheet, dataList.subList(pieces * maxColumn, dataCount), listParamColumn, true, true);


        // 构建结尾数据
        List<AwardExportDTO> exportDTOList = Lists.newArrayList();
        exportDTOList.add(new AwardExportDTO().setCode("currencyName").setName("报价币种"));
        exportDTOList.add(new AwardExportDTO().setCode("adjustExtent").setName("首末总报价调整幅度（含税）"));
        exportDTOList.add(new AwardExportDTO().setCode("adjustAmount").setName("首末总报价调整额度（含税）"));
        exportDTOList.add(new AwardExportDTO().setCode("adjustExtentExclTax").setName("首末总报价调整幅度（未税）"));
        exportDTOList.add(new AwardExportDTO().setCode("adjustAmountExclTax").setName("首末总报价调整额度（未税）"));
        exportDTOList.add(new AwardExportDTO().setCode("amount").setName("含税报价总计"));
        exportDTOList.add(new AwardExportDTO().setCode("amountExclTax").setName("未税报价总计"));
        exportDTOList.add(new AwardExportDTO().setCode("awardAmount").setName("含税定选总计"));
        exportDTOList.add(new AwardExportDTO().setCode("awardAmountExclTax").setName("未税定选总计"));


        List<AwardLineVO> awardLineVOList = awardVO.getAwardLineVOList();
        HashMap<Long, BigDecimal> vendorAmountMap = new HashMap<>(16);
        HashMap<Long, BigDecimal> vendorAmountExclMap = new HashMap<>(16);
        awardLineVOList.forEach(item -> {
            List<AwardLineVendorVO> awardLineVendorVOS = item.getAwardLineVendorVOS();
            for (AwardLineVendorVO vendorVO : awardLineVendorVOS) {
                if (Objects.equals(vendorVO.getSelectedStatus(), BooleanEnum.TRUE.getCode())) {
                    Long vendorId = vendorVO.getParticipatingVendorId();
                    BigDecimal amount = vendorVO.getAmount();
                    if (vendorAmountMap.containsKey(vendorId)) {
                        BigDecimal addAmount = vendorAmountMap.get(vendorId);
                        amount = amount.add(addAmount);
                    }
                    vendorAmountMap.put(vendorId, amount);

                    BigDecimal amountExcl = vendorVO.getAmountExclTax();
                    if (vendorAmountExclMap.containsKey(vendorId)) {
                        BigDecimal addAmount = vendorAmountExclMap.get(vendorId);
                        amountExcl = amountExcl.add(addAmount);
                    }
                    vendorAmountExclMap.put(vendorId, amountExcl);

                }

            }

        });

        for (int i = 0; i < exportDTOList.size(); i++) {
            AwardExportDTO awardExportDTO = exportDTOList.get(i);
            // 拼接 后面的合并表格
            int lastRowNum = sheet.getLastRowNum() + 1;
            HSSFRow a = sheet.createRow(lastRowNum);
            HSSFCell ac = a.createCell(1);
            ac.setCellValue(awardExportDTO.getName());
            CellRangeAddress one = new CellRangeAddress(lastRowNum, lastRowNum, 1, materialColumn);
            sheet.addMergedRegion(one);
            for (int j = 0; j < vendorList.size(); j++) {
                QuoteAmountVO amountVO = vendorList.get(j);

                int firstCol = materialColumn + 1 + j * vendorColumn;
                HSSFCell ab = a.createCell(firstCol);
                String column = fillColumn(awardExportDTO.getCode(), amountVO);
                if (Objects.equals(awardExportDTO.getCode(), "amount") || Objects.equals(awardExportDTO.getCode(), "amountExclTax")) {
                    BigDecimal columnDecimal = new BigDecimal(column).setScale(amountVO.getCurrencyAmountPrecision(), RoundingMode.HALF_UP);
                    column = amountVO.getCurrencySymbol() + columnDecimal;
                }
                if (Objects.equals(column, "0E-8") || Objects.equals(column, "null")) {
                    column = "--";
                }
                if (Objects.equals(awardExportDTO.getCode(), "adjustExtent") || Objects.equals(awardExportDTO.getCode(), "adjustExtentExclTax")) {
                    if (Objects.equals(column, "0E-8") || Objects.equals(column, "null") || Objects.equals(column, "--")) {
                        column = "0%";
                    } else {
                        column = column + "%";
                    }
                }

                Long vendorId = amountVO.getParticipatingVendorId();
                if (Objects.equals(awardExportDTO.getCode(), "awardAmount") && vendorAmountMap.containsKey(vendorId)) {
                    BigDecimal columnDecimal = vendorAmountMap.get(vendorId).setScale(amountVO.getCurrencyAmountPrecision(), RoundingMode.HALF_UP);
                    column = amountVO.getCurrencySymbol() + columnDecimal;
                }
                if (Objects.equals(awardExportDTO.getCode(), "awardAmountExclTax") && vendorAmountExclMap.containsKey(vendorId)) {
                    BigDecimal columnDecimal = vendorAmountExclMap.get(vendorId).setScale(amountVO.getCurrencyAmountPrecision(), RoundingMode.HALF_UP);
                    column = amountVO.getCurrencySymbol() + columnDecimal;
                }

                ab.setCellValue(column);
                CellRangeAddress two = new CellRangeAddress(lastRowNum, lastRowNum, firstCol, firstCol + vendorColumn - 1);
                sheet.addMergedRegion(two);
            }
        }

        ByteArrayOutputStream fileOutputStream = new ByteArrayOutputStream();
        workbook.write(fileOutputStream);
        String fileName = sheet.getSheetName() + "-" + DateUtil.format(new Date(), "yyyyMMdd-HHmmss") + ExcelTypeEnum.XLSX.getValue();
        return fileHelper.uploadRemoteFile(awardVO.getTenantId(), paramsCode.get("moduleCode"), paramsCode.get("sceneCode"), null, fileName, fileOutputStream.toByteArray());
//        save(this.workbook, FilePath);
    }

    private String fillColumn(String code, QuoteAmountVO amountVO) throws IllegalAccessException {
        Class<?> targetClass = amountVO.getClass();
        //获取类中所有字段集合
        Field[] fields = targetClass.getDeclaredFields();
        //遍历字段
        for (Field field : fields) {
            //取消安全检查，允许防伪private修饰的
            field.setAccessible(true);
            String name = field.getName();
            //字段名和值
            if (Objects.equals(code, name)) {
                return String.valueOf(field.get(amountVO));
            }
        }
        return "";
    }

    /**
     * 返回workbook
     *
     * @param listTpamscolumn 表头数据
     * @param datas           行内数据
     * @param flag            是否写入行内数据
     * @return
     * @throws Exception
     */
    public HSSFWorkbook exportWorkbook(List<Column> listTpamscolumn, List<T> datas, boolean flag) throws Exception {
        splitDataToSheets(datas, listTpamscolumn, flag, false);
        return this.workbook;
    }

    /**
     * 导出表格 有返回值
     *
     * @param listTpamscolumn 表头数据
     * @param datas           行内数据
     * @param flag            只输出表头数据
     * @param rowFlag
     * @return
     * @throws Exception
     */
    public InputStream exportExcel(List<Column> listTpamscolumn, List<T> datas, boolean flag, boolean rowFlag) throws Exception {
        splitDataToSheets(datas, listTpamscolumn, flag, rowFlag);
        return save(this.workbook);
    }

    /**
     * 导出Excel,适用于web导出excel
     *
     * @param sheet            excel
     * @param data             行内数据
     * @param listParamsColumn 表头数据
     * @param flag             只输出表头数据
     * @param rowFlag          输出展示数据的结构(表头下面行的数据)
     * @throws Exception
     */
    private void writeSheet(HSSFSheet sheet, List<T> data, List<Column> listParamsColumn, boolean flag, boolean rowFlag) throws Exception {
        sheet.setDefaultColumnWidth(colWidth);
        sheet.setDefaultRowHeightInPoints(rowHeight);
        sheet = createHead(sheet, listParamsColumn.get(0).getTotalRow(), listParamsColumn.get(0).getTotalCol());
        createHead(listParamsColumn, sheet, 0);
        //控制是否 bug修复：每次写入行数据时，总是漏第一个条数据 rowIndex 错误
        if (flag) {
            writeSheetContent(listParamsColumn, data, sheet, listParamsColumn.get(0).getTotalRow() + 1, rowFlag);
        }
    }

    /**
     * 拆分sheet，因为每个sheet不能超过65535，否则会报异常
     *
     * @param data             行内数据
     * @param listParamsColumn 表头数据
     * @param flag             只输出表头数据
     * @param rowFlag          输出展示数据的结构(表头下面行的数据)
     * @throws Exception
     */
    private void splitDataToSheets(List<T> data, List<Column> listParamsColumn, boolean flag, boolean rowFlag) throws Exception {
        int dataCount = data.size();
        int maxColumn = 65535;
        int pieces = dataCount / maxColumn;
        for (int i = 1; i <= pieces; i++) {
            HSSFSheet sheet = this.workbook.createSheet(this.title + i);
            List<T> subList = data.subList((i - 1) * maxColumn, i * maxColumn);
            writeSheet(sheet, subList, listParamsColumn, flag, rowFlag);
        }
        HSSFSheet sheet = this.workbook.createSheet(this.title + (pieces));
        writeSheet(sheet, data.subList(pieces * maxColumn, dataCount), listParamsColumn, flag, rowFlag);

    }

    /**
     * 把数据写入到单元格
     *
     * @param listParamsColumn 表头数据
     * @param dataList         行内数据
     * @param sheet            工作表（excel分页）
     * @throws Exception void
     */
    private void writeSheetContent(List<Column> listParamsColumn, List<T> dataList, HSSFSheet sheet, int rowIndex, boolean rowFlag) throws Exception {
        HSSFRow row = null;
        List<Column> listCol = new ArrayList<>();
        rowFlag = false;
        if (rowFlag) {//暂时没有用 后面扩展用
            for (int i = 0, index = rowIndex; i < dataList.size(); i++, index++) {
                row = sheet.createRow(index);//创建行
                for (int j = 0; j < listParamsColumn.size(); j++) {
                    createColl(row, j, listParamsColumn.get(j).getFieldName(), dataList.get(i));
                }
            }
        } else {
            getColumnList(listParamsColumn, listCol);
            for (int i = 0, index = rowIndex; i < dataList.size(); i++, index++) {
                row = sheet.createRow(index);//创建行
                for (int j = 0; j < listCol.size(); j++) {
                    Column c = listCol.get(j);
                    createCol(row, c, dataList.get(i));
                }
            }
        }
    }

    /**
     * 根据list 来创建单元格 暂时没有用
     *
     * @param row
     * @param j
     * @param finame
     * @param t
     */
    private void createColl(HSSFRow row, int j, String finame, T t) {
        HSSFCell cell = row.createCell(j);  //创建单元格
        cell.setCellStyle(this.styleBody); //设置单元格样式
        String text = "";
        if (t instanceof List) {
            List<Map> temp = (List<Map>) t;
            if (j >= temp.size()) return;
            text = String.valueOf(temp.get(j).get(finame) == null ? "" : temp.get(j).get(finame));
        }
        HSSFRichTextString richString = new HSSFRichTextString(text);
        cell.setCellValue(richString);
    }

    /**
     * 把column的columnList整理成一个list<column> 过滤表头的脏数据
     *
     * @param list    表头数据
     * @param listCol 返回新的list
     * @return List<column>
     */
    private void getColumnList(List<Column> list, List<Column> listCol) {
        for (int i = 0; i < list.size(); i++) {
            if (list.get(i).getFieldName() != null) {
                listCol.add(list.get(i));
            }
            List<Column> listChilren = list.get(i).getListTpamscolumn();
            if (listChilren.size() > 0) {
                getColumnList(listChilren, listCol);
            }
        }
    }

    /**
     * 保存Excel到InputStream，此方法适合web导出excel
     *
     * @param workbook
     * @return
     */
    private InputStream save(HSSFWorkbook workbook) {
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            workbook.write(bos);
            InputStream bis = new ByteArrayInputStream(bos.toByteArray());
            return bis;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    /**
     * 保存excel到本机指定的路径
     *
     * @param workbook
     * @param filePath
     * @throws IOException
     */
    private void save(HSSFWorkbook workbook, String filePath) {
        File file = new File(filePath);
        if (!file.getParentFile().exists()) {
            file.getParentFile().mkdirs();
        }
        FileOutputStream fOut = null;
        try {
            fOut = new FileOutputStream(file);
            workbook.write(fOut);
            fOut.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (null != fOut) fOut.close();
        } catch (Exception e1) {
        }
    }

    /**
     * 创建行
     *
     * @param row         Excel对应的行
     * @param tpamscolumn 当前单元格属性
     * @param v
     * @param j
     * @return
     * @throws Exception
     */
    public int createRowVal(HSSFRow row, Column tpamscolumn, T v, int j) throws Exception {
        //遍历标题
        if (tpamscolumn.getListTpamscolumn() != null && tpamscolumn.getListTpamscolumn().size() > 0) {
            for (int i = 0; i < tpamscolumn.getListTpamscolumn().size(); i++) {
                createRowVal(row, tpamscolumn.getListTpamscolumn().get(i), v, j);
            }
        } else {
            createCol(row, tpamscolumn, v);
        }
        return j;

    }

    /**
     * 创建单元格
     *
     * @param row         Excel对应的行
     * @param tpamscolumn 当前单元格对象
     * @param v
     * @throws Exception
     */
    public void createCol(HSSFRow row, Column tpamscolumn, T v) throws Exception {
        HSSFCell cell = row.createCell(tpamscolumn.getCol());  //创建单元格
        cell.setCellStyle(this.styleBody); //设置单元格样式
        final Object[] value = {null};
        if (v instanceof Map) {
            Map m = (Map) v;
            m.forEach((k, val) -> {
                if (k.equals(tpamscolumn.getFieldName()) && !tpamscolumn.isHasChilren()) {
                    value[0] = val;
                }
            });
        } else {
            Class<?> cls = v.getClass();// 拿到该类
            Field[] fields = cls.getDeclaredFields();// 获取实体类的所有属性，返回Field数组
            for (int i = 0; i < fields.length; i++) {
                Field f = fields[i];
                f.setAccessible(true); // 设置些属性是可以访问的
                if (tpamscolumn.getFieldName().equals(f.getName()) && !tpamscolumn.isHasChilren())// && !tpamscolumn.isHasChilren()
                    value[0] = f.get(v);
                if (value[0] instanceof Date)
                    value[0] = parseDate((Date) value[0]);
            }
        }
        if (value[0] != null) {
            HSSFRichTextString richString = new HSSFRichTextString(value[0].toString());
            cell.setCellValue(richString);
        }

    }

    /**
     * 时间转换
     *
     * @param date
     * @return String
     */
    private String parseDate(Date date) {
        String dateStr = "";
        try {
            dateStr = this.sdf.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dateStr;
    }


    /**
     * 根据数据的行数和列数，在excel创建单元格cell
     *
     * @param sheetCo excel分页
     * @param r       excel 行数
     * @param c       excel 列数
     * @return
     */
    public HSSFSheet createHead(HSSFSheet sheetCo, int r, int c) {
        for (int i = 0; i < r; i++) {
            HSSFRow row = sheetCo.createRow(i);
            for (int j = 0; j < c; j++) {
                HSSFCell cell = row.createCell(j);
            }
        }
        return sheetCo;
    }

    /**
     * 使用递归 在excel写入表头数据 支持单级，多级表头的创建
     *
     * @param listTpamscolumn 表头数据
     * @param sheetCo         哪个分页
     * @param rowIndex        当前Excel的第几行
     */
    public void createHead(List<Column> listTpamscolumn, HSSFSheet sheetCo, int rowIndex) {
        HSSFRow row = sheetCo.getRow(rowIndex);
//        if(row == null)row = sheetCo.createRow(rowIndex);
        int len = listTpamscolumn.size();//当前行 有多少列
        for (int i = 0; i < len; i++) {//i是headers的索引，n是Excel的索引 多级表头
            Column tpamscolumn = listTpamscolumn.get(i);
            //创建这一行的第几列单元格
            int r = tpamscolumn.getRow();
            int rLen = tpamscolumn.getrLen();
            int c = tpamscolumn.getCol();
            int cLen = tpamscolumn.getcLen();
            int endR = r + rLen;
            int endC = c + cLen;
            if (endC > c) {
                endC--;
            }
            HSSFCell cell = row.getCell(c);
//            if( null == cell)cell = row.createCell(c);

            HSSFRichTextString text = new HSSFRichTextString(tpamscolumn.getContent());
            cell.setCellStyle(this.styleHead); //设置表头样式
            cell.setCellValue(text);
            // 合并单元格
            CellRangeAddress cra = new CellRangeAddress(r, endR, c, endC);
            sheetCo.addMergedRegion(cra);

            // 使用RegionUtil类为合并后的单元格添加边框
            RegionUtil.setBorderBottom(1, cra, sheetCo, this.workbook); // 下边框
            RegionUtil.setBorderLeft(1, cra, sheetCo, this.workbook); // 左边框
            RegionUtil.setBorderRight(1, cra, sheetCo, this.workbook); // 有边框

            if (tpamscolumn.isHasChilren()) {
                rowIndex = r + 1;
                createHead(tpamscolumn.getListTpamscolumn(), sheetCo, rowIndex);
            }
        }
    }


    /**
     * 转换成column对象
     * 支持List<T>的数据结构:map String ，只能是单级的数据
     *
     * @param list 需要转换的数据
     * @return
     */
    public List<Column> columnTransformer(List<T> list) {
        List<Column> lc = new ArrayList<>();
        if (list.get(0) instanceof Map) {
            final int[] i = {1};
            for (Map<String, String> m : (List<Map<String, String>>) list) {
                m.forEach((k, val) -> {
                    Column tpamscolumn = new Column();
                    tpamscolumn.setId(String.valueOf(i[0]));
                    tpamscolumn.setPid("0");
                    tpamscolumn.setContent(k);
                    tpamscolumn.setFieldName(val);
                    lc.add(tpamscolumn);
                    i[0]++;
                });
            }
        } else {
            int i = 1;
            for (String s : (List<String>) list) {
                Column tpamscolumn = new Column();
                tpamscolumn.setId(String.valueOf(i));
                tpamscolumn.setPid("0");
                tpamscolumn.setContent(s);
                tpamscolumn.setFieldName(null);
                lc.add(tpamscolumn);
                i++;
            }
        }
        setParm(lc, "0");//处理一下
        List<Column> s = TreeTool.buildByRecursive(lc, "0");
        setColNum(lc, s, s);
        return s;
    }

    /**
     * 转换成column对象 返回tree数据结构
     * 支持：List<map>、某个具体对象（entity）数据的转换
     *
     * @param list     需要转换的数据
     * @param id       当前节点id 字段的名称  主键
     * @param pid      父节点id 字段的名称
     * @param content  填写表头单元格内容的 字段名称
     * @param fielName 填写行内数据对的 字段名称
     * @param rootid   rootid的值
     * @return
     * @throws Exception
     */
    public List<Column> columnTransformer(List<T> list, String id, String pid, String content, String fielName, String rootid) throws Exception {
        // 结果
        List<Column> lc = new ArrayList<>();
        if (list.get(0) instanceof Map) {
            for (Map m : (List<Map>) list) {
                Column tpamscolumn = new Column();
                m.forEach((k, val) -> {//java8 以上的遍历方式
                    if (id.equals(k))
                        tpamscolumn.setId(String.valueOf(val));
                    if (pid.equals(k)) tpamscolumn.setPid((String) val);
                    if (content.equals(k)) tpamscolumn.setContent((String) val);
                    if (fielName.equals(k) && fielName != null) tpamscolumn.setFieldName((String) val);
                });
                lc.add(tpamscolumn);
            }
        } else {
            for (T t : list) {//反射
                Column tpamscolumn = new Column();
                Class cls = t.getClass();
                Field[] fs = cls.getDeclaredFields();
                for (int i = 0; i < fs.length; i++) {
                    Field f = fs[i];
                    f.setAccessible(true); // 设置些属性是可以访问的
                    if (id.equals(f.getName()) && f.get(t) != null)
                        tpamscolumn.setId(f.get(t).toString());
                    if (pid.equals(f.getName()) && f.get(t) != null)
                        tpamscolumn.setPid(f.get(t).toString());
//                    if (pid.equals(f.getName()) && ( f.get(t) == null || "".equals(f.get(t)))) tpamscolumn.setPid("0");
                    if (content.equals(f.getName()) && f.get(t) != null)
                        tpamscolumn.setContent(f.get(t).toString());
                    if (f.get(t) != null && fielName != null && fielName.equals(f.getName()))
                        tpamscolumn.setFieldName(f.get(t).toString());
                }
                lc.add(tpamscolumn);
            }
        }
        setParm(lc, rootid);//处理一下
        List<Column> s = TreeTool.buildByRecursive(lc, rootid);
        setColNum(lc, s, s);
        return s;
    }

    /**
     * 设置基础的参数
     *
     * @param list
     */
    public static void setParm(List<Column> list, String rootid) {
        int row = 0;//excel第几行
        int rLen = 0; //excel 跨多少行
        // 总行数
        int totalRow = TreeTool.getMaxStep(list);
        // 总列数
        int totalCol = TreeTool.getDownChilren(list, rootid);
        for (int i = 0; i < list.size(); i++) {
            Column poit = list.get(i);
            // 往上遍历tree
            int tree_step = TreeTool.getTreeStep(list, poit.getPid(), 0);
            poit.setTree_step(tree_step);
            // 设置第几行
            poit.setRow(tree_step);
            //判断是否有节点
            boolean hasCh = TreeTool.hasChild(list, poit);
            poit.setHasChilren(hasCh);
            if (hasCh) {
                poit.setrLen(0);//设置跨多少行
            } else {
                if (tree_step < totalRow) {
                    rLen = totalRow - tree_step;
                }
                poit.setrLen(rLen);
            }
//            boolean flag=false;//控制只有root 节点才有总的行数信息
//            if(rootid == null && rootid == poit.getId() )flag = true;
//            if(rootid != null && rootid.equals(poit.getId()))flag = true;
//            if(flag){
//
//            }
            poit.setTotalRow(totalRow);
            poit.setTotalCol(totalCol);
        }
    }

    /**
     * 设置基础的参数
     *
     * @param list     所有list数据，一条一条
     * @param treeList 转成tree结构的list
     */
    public static void setColNum(List<Column> list, List<Column> treeList, List<Column> flist) {
//        int col = pcIndex;//excel第几列
//        int cLen ;//xcel跨多少列
        List<Column> new_list = new ArrayList<>();//新的遍历list
        for (int i = 0; i < treeList.size(); i++) {
            Column poit = treeList.get(i);
//            String temp_id = TreeTool.getStepFid(list,poit.getId() ,1);
            int col = TreeTool.getFCol(list, poit.getPid()).getCol();
            int brotherCol = TreeTool.getBrotherChilNum(list, poit);
            poit.setCol(col + brotherCol);
            int cLen = TreeTool.getDownChilren(list, poit.getId());
            if (cLen <= 1) cLen = 0;
//            else  cLen--;
            poit.setcLen(cLen);//设置跨多少列
            if (poit.getListTpamscolumn().size() > 0) {
                new_list.addAll(poit.getListTpamscolumn());
            }
        }
        if (new_list.size() > 0) {
            setColNum(list, new_list, flist);
        }
    }
//========上部分是导出excel的使用（生成excel），下部分是解析excel，由于excel导入==================================================================================================================================

    /**
     * 根据HSSFCell类型设置数据
     *
     * @param cell 单元格
     * @return
     */
    public static String getCellFormatValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            switch (cell.getCellType()) { // 判断当前Cell的Type

                case HSSFCell.CELL_TYPE_NUMERIC:  // 如果当前Cell的Type为NUMERIC
                case HSSFCell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        cellvalue = sdf.format(date);
                    } else { // 如果是纯数字
                        cellvalue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case HSSFCell.CELL_TYPE_STRING:  // 如果当前Cell的Type为STRIN
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                default:  // 默认的Cell值
                    cellvalue = "";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     *
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbookType(InputStream inStr, String fileName) throws Exception {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            wb = new HSSFWorkbook(inStr);  //2003-
        } else if (".xlsx".equals(fileType)) {
            wb = new XSSFWorkbook(inStr);  //2007+
        } else {
            throw new Exception("导入格式错误");
        }
        return wb;
    }

    /**
     * 获取单元格数据内容为字符串类型的数据
     *
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    public static String getStringCellValue(Cell cell) {
        String strCell = "";
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                strCell = cell.getStringCellValue().trim();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                strCell = String.valueOf(cell.getNumericCellValue()).trim();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue()).trim();
                break;
            case Cell.CELL_TYPE_BLANK:
                strCell = "";
                break;
            default:
                strCell = "";
                break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        return strCell;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    public boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    public String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getStringCellValue(fCell);
                }
            }
        }
        return "";
    }

    /**
     * 获取excel的值 返回的 List<List<String>>的数据结构
     *
     * @param fileUrl  文件路径
     * @param sheetNum 工作表（第几分页[1,2,3.....]）
     * @return List<List < String>>
     */
    public List<List<String>> getExcelValues(String fileUrl, int sheetNum) throws Exception {
        List<List<String>> values = new ArrayList<List<String>>();
        File file = new File(fileUrl);
        InputStream is = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(is);
        int sheetCount = sheetNum - 1; //workbook.getNumberOfSheets();//sheet 数量,可以只读取手动指定的sheet页
        //int sheetCount1= workbook.getNumberOfSheets();
        Sheet sheet = workbook.getSheetAt(sheetCount); //读取第几个工作表sheet
        int rowNum = sheet.getLastRowNum();//有多少行
        for (int i = 1; i <= rowNum; i++) {
            Row row = sheet.getRow(i);//第i行
            if (row == null) {//过滤空行
                continue;
            }
            List<String> list = new ArrayList<>();
            int colCount = sheet.getRow(0).getLastCellNum();//用表头去算有多少列，不然从下面的行计算列的话，空的就不算了
            for (int j = 0; j < colCount; j++) {//第j列://+1是因为最后一列是空 也算进去
                Cell cell = row.getCell(j);
                String cellValue;
                boolean isMerge = false;
                if (cell != null) {
                    isMerge = isMergedRegion(sheet, i, cell.getColumnIndex());
                }
                //判断是否具有合并单元格
                if (isMerge) {
                    cellValue = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());
                } else {
                    cellValue = getStringCellValue(cell);
                }
                list.add(cellValue);
            }
            values.add(list);
        }
        return values;
    }

    /**
     * 判断整行是否为空
     *
     * @param row    excel得行对象
     * @param maxRow 有效值得最大列数
     */
    private static boolean CheckRowNull(Row row, int maxRow) {
        int num = 0;
        for (int j = 0; j < maxRow; j++) {
            Cell cell = row.getCell(j);
            if (cell == null || cell.equals("") || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                num++;
            }
        }
        if (maxRow == num) return true;
        return false;
    }

    /**
     * 根据sheet数获取excel的值 返回List<List<Map<String,String>>>的数据结构
     *
     * @param fileUrl  文件路径
     * @param sheetNum 工作表（第几分页[1,2,3.....]）
     * @return List<List < Map < String, String>>>
     */
    public List<List<Map<String, String>>> getExcelMapVal(String fileUrl, int sheetNum) throws Exception {
        List<List<Map<String, String>>> values = new ArrayList<List<Map<String, String>>>();
        File file = new File(fileUrl);
        InputStream is = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(is);
        int sheetCount = sheetNum - 1; //workbook.getNumberOfSheets();//sheet 数量,可以只读取手动指定的sheet页
        //int sheetCount1= workbook.getNumberOfSheets();
        Sheet sheet = workbook.getSheetAt(sheetCount); //读取第几个工作表sheet
        int rowNum = sheet.getLastRowNum();//有多少行
        Row rowTitle = sheet.getRow(0);//第i行
        int colCount = sheet.getRow(0).getLastCellNum();//用表头去算有多少列，不然从下面的行计算列的话，空的就不算了
        for (int i = 1; i <= rowNum; i++) {
            Row row = sheet.getRow(i);//第i行
            if (row == null || CheckRowNull(row, colCount)) {//过滤空行
                continue;
            }
            List<Map<String, String>> list = new ArrayList<Map<String, String>>();
            for (int j = 0; j < colCount; j++) {//第j列://+1是因为最后一列是空 也算进去
                Map<String, String> map = new HashMap<>();
                Cell cell = row.getCell(j);
                Cell cellTitle = rowTitle.getCell(j);
                String cellValue;
                String cellKey = getStringCellValue(cellTitle);
                boolean isMerge = false;
                if (cell != null) {
                    isMerge = isMergedRegion(sheet, i, cell.getColumnIndex());
                }
                //判断是否具有合并单元格
                if (isMerge) {
                    cellValue = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());
                } else {
                    cellValue = getStringCellValue(cell);
                }
                map.put(cellKey, cellValue);
                list.add(map);
            }
            values.add(list);
        }
        return values;
    }

    /**
     * 获取当前excel的工作表sheet总数
     *
     * @param fileUrl
     * @return
     * @throws Exception
     */
    public int hasSheetCount(String fileUrl) throws Exception {
        File file = new File(fileUrl);
        InputStream is = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(is);
        int sheetCount = workbook.getNumberOfSheets();
        return sheetCount;
    }
}

/**
 * 表头的实体类： 在具体的项目里，可以是你从数据库里查询出来的数据
 */
@Data
public class TitleEntity {
    public String t_id;
    public String t_pid;
    public String t_content;
    public String t_fileName;

    public TitleEntity() {
    }

    public TitleEntity(String t_id, String t_pid, String t_content, String t_fileName) {
        this.t_id = t_id;
        this.t_pid = t_pid;
        this.t_content = t_content;
        this.t_fileName = t_fileName;
    }
}

import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

/**
 * 处理tree结构的数据 工具类
 */
public class TreeTool {
    /**
     * 传入的id 必须存在list集合里
     * 获取某节点的深度
     *
     * @param list
     * @param id   根节点
     * @param step
     * @return
     */
    public static int getTreeStep(List<Column> list, String id, int step) {
        if ("".equals(id) || null == id) return step;
        for (Column cc : list) {
            if (id.equals(cc.getId())) {
                int temp = step + 1;
                return getTreeStep(list, cc.getPid(), temp);
            }
        }
        return step;
    }

    /**
     * 遍历所有数据 获取树最大的深度
     *
     * @param list
     * @return
     */
    public static int getMaxStep(List<Column> list) {
        List<Integer> nums = new ArrayList<Integer>();
        for (Column cc : list) {
            nums.add(getTreeStep(list, cc.getId(), 0));
        }
        return Collections.max(nums);
    }

    /**
     * 获取最底部子节点的个数 所有叶子节点个数
     *
     * @param list
     * @param did
     * @return
     */
    public static int getDownChilren(List<Column> list, String did) {
        int sum = 0;
        for (Column cc : list) {
            if (did.equals(cc.getPid())) {
                sum++;
                //判断该节点 是否有子节点
                if (hasChild(list, cc)) {
                    sum += getDownChilren(list, cc.getId()) - 1;
                }
            }
        }
        return sum;
    }

    /**
     * 获取父节点
     *
     * @param list 所有的list数据，一条一条
     * @param did  当前节点id
     * @return
     */
    public static Column getFCol(List<Column> list, String did) {
        for (Column cc : list) {
            if (did != null && did.equals(cc.getId())) {
                return cc;
            }
            if (did == null && did == cc.getId()) {
                return cc;
            }
        }
        return new Column() {{
            setCol(0);
            setRow(0);
        }};
    }

    /**
     * 获取兄弟节点个数 这个必须是有排序的
     *
     * @param list   所有的list数据，一条一条
     * @param column 当前节点信息
     * @return
     */
    public static int getBrotherChilNum(List<Column> list, Column column) {
        int sum = 0;
        for (Column cc : list) {
            if (column.getId().equals(cc.getId())) {
                break;
            }
            if (!column.getPid().equals(cc.getPid())) {
                continue;
            }
            int temp = getDownChilren(list, cc.getId());
            if (temp == 0 || temp == 1)
                sum++;
            else
                sum += temp;
        }
        return sum;
    }

    /**
     * 根据某节点的第几层的父节点id
     *
     * @param list 所有的list数据，一条一条
     * @param id   当前节点id
     * @param step 第几层(深度 从零开始)
     * @return
     */
    public static String getStepFid(List<Column> list, String id, int step) {
        String f_id = null;
        for (Column cc : list) {
            if (id.equals(cc.getId())) {
                int cstep = getTreeStep(list, cc.getId(), 0);
                if (step == cstep) {
                    return id;
                }
                int fstep = getTreeStep(list, cc.getPid(), 0);
                if (step == fstep) {
                    f_id = cc.getPid();
                    break;
                } else {
                    getStepFid(list, cc.getPid(), step);
                }
            }
        }
        return f_id;
    }

    /**
     * 判断是否有子节点
     *
     * @param list 遍历的数据
     * @param node 某个节点
     * @return
     */
    public static boolean hasChild(List<Column> list, Column node) {
        return getChildList(list, node).size() > 0 ? true : false;
    }

    /**
     * 得到子节点列表
     *
     * @param list 遍历的数据
     * @param node 某个节点
     * @return
     */
    public static List<Column> getChildList(List<Column> list, Column node) {
        List<Column> nodeList = new ArrayList<Column>();
        Iterator<Column> it = list.iterator();
        while (it.hasNext()) {
            Column n = (Column) it.next();
            if (n.getPid() != null && n.getPid().equals(node.getId())) {
                nodeList.add(n);
            }
        }
        return nodeList;
    }

    /**
     * 使用递归方法建树
     *
     * @param treeNodes
     * @return
     */
    public static List<Column> buildByRecursive(List<Column> treeNodes, String rootID) {
        List<Column> trees = new ArrayList<>();
        boolean flag = false;
        boolean sflag = false;
        for (Column treeNode : treeNodes) {
            if ((rootID == null && rootID == treeNode.getId())) {
                flag = true;
            }
            if (rootID != null && rootID.equals(treeNode.getId())) {
                flag = true;
            }
            if (flag) {
                trees.add(findChildren(treeNode, treeNodes));
                flag = false;
            }
        }
        if (trees.size() <= 0) {
            for (Column treeNode : treeNodes) {
                if ((rootID == null && rootID == treeNode.getPid())) {
                    sflag = true;
                }
                if (rootID != null && rootID.equals(treeNode.getPid())) {
                    sflag = true;
                }
                if (sflag) {
                    trees.add(findChildren(treeNode, treeNodes));
                    sflag = false;
                }
            }
        }
        return trees;
    }

    /**
     * 递归查找子节点
     *
     * @param treeNodes
     * @return
     */
    public static Column findChildren(Column treeNode, List<Column> treeNodes) {
        for (Column it : treeNodes) {
            if (treeNode.getId().equals(it.getPid())) {
                if (treeNode.getListTpamscolumn() == null) {
                    treeNode.setListTpamscolumn(new ArrayList<Column>());
                }
                treeNode.getListTpamscolumn().add(findChildren(it, treeNodes));
            }
        }
        return treeNode;
    }
}

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 测试类
 */
public class TextMain {
    public static void main(String[] args) throws Exception {
//        单级的表头
//        Map<String,String> map=new HashMap<String,String>();
//        map.put("登录名","u_login_id");
//        Map<String,String>  map1=new HashMap<String,String>();
//        map1.put("用户名","u_name");
//        Map<String,String>  map2=new HashMap<String,String>();
//        map2.put("角色","u_role");
//        Map<String,String>  map3=new HashMap<String,String>();
//        map3.put("部门","u_dep");//d_name
//        Map<String,String>  map4=new HashMap<String,String>();
//        map4.put("用户类型","u_type");
//        List<Map<String,String>> titleList=new ArrayList<>();
//        titleList.add(map); titleList.add(map1); titleList.add(map2); titleList.add(map3); titleList.add(map4);
//        //单级的 行内数据
//        List<Map<String,String>> rowList=new ArrayList<>();
//        for(int i=0;i<7;i++){
//            Map m= new HashMap<String,String>();
//            m.put("u_login_id","登录名"+i); m.put("u_name","张三"+i);
//            m.put("u_role","角色"+i); m.put("u_dep","部门"+i);
//            m.put("u_type","用户类型"+i);
//            rowList.add(m);
//        }
//        ExcelTool excelTool = new ExcelTool("单级表头的表格",15,20);
//        List<Column>  titleData=excelTool.columnTransformer(titleList);
//        excelTool.exportExcel(titleData,rowList,"D://outExcel.xls",true,false);

        //List<Map>数据 多级表头,数据如下:
        //        登录名  姓名       aa
        //                      角色    部门
//        List<Map<String,String>> titleList=new ArrayList<>();
//        Map<String,String> titleMap=new HashMap<String,String>();
//        titleMap.put("id","11");titleMap.put("pid","0");titleMap.put("content","登录名");titleMap.put("fielName","u_login_id");
//        Map<String,String> titleMap1=new HashMap<String,String>();
//        titleMap1.put("id","1");titleMap1.put("pid","0");titleMap1.put("content","姓名");titleMap1.put("fielName","u_name");
//        Map<String,String> titleMap2=new HashMap<String,String>();
//        titleMap2.put("id","2");titleMap2.put("pid","0");titleMap2.put("content","角色加部门");titleMap2.put("fielName",null);
//        Map<String,String> titleMap3=new HashMap<String,String>();
//        titleMap3.put("id","3");titleMap3.put("pid","2");titleMap3.put("content","角色");titleMap3.put("fielName","u_role");
//        Map<String,String> titleMap4=new HashMap<String,String>();
//        titleMap4.put("id","4");titleMap4.put("pid","2");titleMap4.put("content","部门");titleMap4.put("fielName","u_dep");
//        Map<String,String> titleMap5=new HashMap<String,String>();
//        titleMap5.put("id","22");titleMap5.put("pid","0");titleMap5.put("content","角色加部门1");titleMap5.put("fielName",null);
//        Map<String,String> titleMap6=new HashMap<String,String>();
//        titleMap6.put("id","22_1");titleMap6.put("pid","22");titleMap6.put("content","角色1");titleMap6.put("fielName","u_role");
//        Map<String,String> titleMap7=new HashMap<String,String>();
//        titleMap7.put("id","22_2");titleMap7.put("pid","22");titleMap7.put("content","部门1");titleMap7.put("fielName","u_dep");
//        titleList.add(titleMap); titleList.add(titleMap1); titleList.add(titleMap2); titleList.add(titleMap3); titleList.add(titleMap4);
//        titleList.add(titleMap5); titleList.add(titleMap6); titleList.add(titleMap7);
//       // 单级的 行内数据
//        List<Map<String,String>> rowList=new ArrayList<>();
//        for(int i=0;i<7;i++){
//            Map m= new HashMap<String,String>();
//            m.put("u_login_id","登录名"+i); m.put("u_name","张三"+i);
//            m.put("u_role","角色"+i); m.put("u_dep","部门"+i);
//            m.put("u_type","用户类型"+i);
//            rowList.add(m);
//        }
//        ExcelTool excelTool = new ExcelTool("List<Map>数据 多级表头表格",20,20);
//        List<Column>  titleData=excelTool.columnTransformer(titleList,"id","pid","content","fielName","0");
//        excelTool.exportExcel(titleData,rowList,"D://outExcel.xls",true,false);

        //实体类（entity）数据 多级表头,数据如下:
        //        登录名  姓名       aa
        //                      角色    部门
        List<TitleEntity> titleList = new ArrayList<>();
        TitleEntity titleEntity1 = new TitleEntity("1", "0", "序号", "num");
        TitleEntity titleEntity2 = new TitleEntity("2", "0", "供应商1", null);
        TitleEntity titleEntity5 = new TitleEntity("3", "0", "供应商2", null);
        TitleEntity titleEntity3 = new TitleEntity("4", "2", "角色", "code");
        TitleEntity titleEntity4 = new TitleEntity("5", "2", "部门", "name");
        TitleEntity titleEntity6 = new TitleEntity("6", "3", "角色", "code");
        TitleEntity titleEntity7 = new TitleEntity("7", "3", "部门", "name");
        TitleEntity titleEntity8 = new TitleEntity("8", "0", "供应商3", null);
        TitleEntity titleEntity9 = new TitleEntity("9", "8", "角色", "code");
        TitleEntity titleEntity10 = new TitleEntity("10", "8", "部门", "name");
        titleList.add(titleEntity1);
        titleList.add(titleEntity2);
        titleList.add(titleEntity3);
        titleList.add(titleEntity4);
        titleList.add(titleEntity5);
        titleList.add(titleEntity6);
        titleList.add(titleEntity7);
        titleList.add(titleEntity8);
        titleList.add(titleEntity9);
        titleList.add(titleEntity10);


        //单级的 行内数据
        List<Map<String, String>> rowList = new ArrayList<>();
        for (int i = 0; i < 7; i++) {
            Map m = new HashMap<String, String>();
            m.put("num", i);
            m.put("code", i);
            m.put("age", i);
            m.put("name", "名称" + i);
            rowList.add(m);
        }
        ExcelTool excelTool = new ExcelTool("多级表头表格", 20, 20);
        List<Column> titleData = excelTool.columnTransformer(titleList, "t_id", "t_pid", "t_content", "t_fileName", "0");
        excelTool.exportExcel(titleData, rowList, "/Users/mayuhan/Downloads/test.xls", true, true);

    }

}