java动态导出Excle模板

我会带着你远行 2023-09-30 23:40 2阅读 0赞

实际开发中很多时候都需要导出Excle模板文件，而且是根据不同的需求需要导出不同的模板，在这里简单记录一下使用java实现普通的动态Excle模板导出功能。

1.添加[maven][]依赖：

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

2.编写导出工具类--ExcelTool

import com.mes.material.domain.MaterialDictData;
    import com.mes.material.excelTool.vo.Column;
    import com.mes.material.excelTool.vo.MergedResult;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.ss.util.CellRangeAddressList;
    import org.apache.poi.ss.util.RegionUtil;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.*;
    import java.lang.reflect.Field;
    import java.text.NumberFormat;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    /**
     * excel处理工具
     * 概念-> 表头数据:报表的表头
     * 行内数据:表头以下的数据
     * 功能:动态生成单级，多级Excel表头
     * 备注：tree型结构数据的root节点的id必须为零（0）
     * Created by wtj on 2018/3/2.
     * 修改:
     * 2019/03/18 修复生成跨列的bug
     * 2019/03/20 修复集合存在root的时候，生成不了动态表头
     *
     * @param <T>
     */
    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(HorizontalAlignment.CENTER);// 左右居中
            this.styleHead.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            this.styleHead.setRightBorderColor(IndexedColors.BLACK.getIndex());
            this.styleHead.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            //设置自动换行
            this.styleHead.setWrapText(true);
            switch (styleFlag) {
                case 1:
                    this.styleBody = this.workbook.createCellStyle();
                    this.styleBody.setAlignment(HorizontalAlignment.CENTER);// 左右居中ALIGN_CENTER
                    this.styleBody.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
                    this.styleBody.setRightBorderColor(IndexedColors.BLACK.getIndex());
                    this.styleBody.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                    this.styleBody.setBorderRight(BorderStyle.THIN);// 边框的大小
                    this.styleBody.setBorderBottom(BorderStyle.THIN);// 边框的大小
                    break;
                default:
                    this.styleBody = this.workbook.createCellStyle();
                    this.styleBody.setAlignment(HorizontalAlignment.CENTER);// 左右居中ALIGN_CENTER
                    this.styleBody.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
                    this.styleBody.setRightBorderColor(IndexedColors.BLACK.getIndex());
                    this.styleBody.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                    this.styleBody.setBorderRight(BorderStyle.THIN);// 边框的大小
                    this.styleBody.setBorderBottom(BorderStyle.THIN);// 边框的大小
                    break;
            }
        }
    
        /**
         * 导出表格 无返回
         *
         * @param listTpamscolumn 表头数据
         * @param datas           行内数据
         * @param FilePath        保存路径
         * @param flag
         * @param rowFlag
         * @throws Exception
         */
        public void exportExcel(List<Column> listTpamscolumn, List<T> datas, String FilePath, boolean flag, boolean rowFlag) throws Exception {
            splitDataToSheets(datas, listTpamscolumn, flag, rowFlag);
            save(this.workbook, FilePath);
        }
    
        /**
         * @return java.io.InputStream
         * @Author: best_liu
         * @Description: 导出excel带sheet说明
         * @Date: 16:30 2022/3/15
         * @Param [listExplain, explainDatas, listTpamscolumn, datas, flag, rowFlag]
         **/
        public void exportExcel(Boolean hidden, List<Column> listExplain, List<T> explainDatas, List<Column> listTpamscolumn, List<T> datas, String FilePath, boolean flag, boolean rowFlag) throws Exception {
            List<MaterialDictData> dataList = new ArrayList<>();
            exportTwoSheets(hidden, listExplain, explainDatas, datas, listTpamscolumn,dataList, flag, rowFlag);
            save(this.workbook, FilePath);
        }
    
        /**
         * 返回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);
        }
    
        /**
         * @return java.io.InputStream
         * @Author: best_liu
         * @Description: 导出excel带sheet说明
         * @Date: 16:30 2022/3/15
         * @Param [listExplain, explainDatas, listTpamscolumn, datas, flag, rowFlag]
         **/
        public InputStream exportExcel(Boolean hidden, List<Column> listExplain, List<T> explainDatas, List<Column> listTpamscolumn, List<T> datas, boolean flag, boolean rowFlag) throws Exception {
            List<MaterialDictData> dataList = new ArrayList<>();
            exportTwoSheets(hidden, listExplain, explainDatas, datas, listTpamscolumn,dataList, flag, rowFlag);
            return save(this.workbook);
        }
    
    
        public InputStream exportExcelSelect(Boolean hidden, List<Column> listExplain, List<T> explainDatas, List<Column> listTpamscolumn, List<T> datas, List<MaterialDictData> dataList, boolean flag, boolean rowFlag) throws Exception {
            exportTwoSheets(hidden, listExplain, explainDatas, datas, listTpamscolumn,dataList, flag, rowFlag);
            return save(this.workbook);
        }
    
        /**
         * 导出Excel,适用于web导出excel
         *
         * @param sheet           excel
         * @param data            行内数据
         * @param listTpamscolumn 表头数据
         * @param flag            只输出表头数据
         * @param rowFlag         输出展示数据的结构(表头下面行的数据)
         * @throws Exception
         */
        private void writeSheet(Boolean isColor, HSSFSheet sheet, List<T> data, List<Column> listTpamscolumn, boolean flag, boolean rowFlag) throws Exception {
            sheet.setDefaultColumnWidth(colWidth);
            sheet.setDefaultRowHeightInPoints(rowHeight);
            sheet = createHead(sheet, listTpamscolumn.get(0).getTotalRow(), listTpamscolumn.get(0).getTotalCol());
            createHead(isColor, listTpamscolumn, sheet, 0);
            if (flag)//控制是否 bug修复：每次写入行数据时，总是漏第一个条数据 rowIndex 错误
            {
                writeSheetContent(listTpamscolumn, data, sheet, listTpamscolumn.get(0).getTotalRow() + 1, rowFlag);
            }
        }
    
        private void writeSheetSelect(Boolean isColor, HSSFSheet sheet, List<T> data, List<Column> listTpamscolumn,List<MaterialDictData> dataList, boolean flag, boolean rowFlag) throws Exception {
            sheet.setDefaultColumnWidth(colWidth);
            sheet.setDefaultRowHeightInPoints(rowHeight);
            sheet = createHead(sheet, listTpamscolumn.get(0).getTotalRow(), listTpamscolumn.get(0).getTotalCol());
            createHead(isColor, listTpamscolumn, sheet,dataList, 0);
            if (flag)//控制是否 bug修复：每次写入行数据时，总是漏第一个条数据 rowIndex 错误
            {
                writeSheetContent(listTpamscolumn, data, sheet, listTpamscolumn.get(0).getTotalRow() + 1, rowFlag);
            }
        }
    
        /**
         * 拆分sheet，因为每个sheet不能超过65535，否则会报异常
         *
         * @param data            行内数据
         * @param listTpamscolumn 表头数据
         * @param flag            只输出表头数据
         * @param rowFlag         输出展示数据的结构(表头下面行的数据)
         * @throws Exception
         */
        private void splitDataToSheets(List<T> data, List<Column> listTpamscolumn, 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(false, sheet, subList, listTpamscolumn, flag, rowFlag);
            }
            HSSFSheet sheet = this.workbook.createSheet(this.title + (pieces + 1));
            writeSheet(false, sheet, data.subList(pieces * maxColumn, dataCount), listTpamscolumn, flag, rowFlag);
        }
    
        /**
         * 多个sheet，因为每个sheet不能超过65535，否则会报异常
         *
         * @param data            行内数据
         * @param listTpamscolumn 表头数据
         * @param flag            只输出表头数据
         * @param rowFlag         输出展示数据的结构(表头下面行的数据)
         * @throws Exception
         */
        private void exportTwoSheets(Boolean hidden, List<Column> listExplain, List<T> explainDatas, List<T> data, List<Column> listTpamscolumn,List<MaterialDictData> dataList, boolean flag, boolean rowFlag) throws Exception {
            HSSFSheet sheet1 = this.workbook.createSheet("说明");
            //合并单元格
            CellRangeAddress region = new CellRangeAddress(0, // first row
                    4, // last row
                    1, // first column
                    8 // last column
            );
            sheet1.addMergedRegion(region);
            // 使用RegionUtil类为合并后的单元格添加边框
            RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet1); // 下边框
            RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet1); // 左边框
            RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet1); // 有边框
    
            sheet1.setColumnHidden(0, true);//第1列将被隐藏
            //isColor用于标识是否是说明页，true表示是说明页，false表示不是说明页
            writeSheet(true, sheet1, explainDatas, listExplain, flag, rowFlag);
            HSSFSheet sheet2 = this.workbook.createSheet(this.title);
            if (hidden) {
                //设置最后一列下拉列表----start
                String[] datas = new String[]{"是", "否"};
                HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet2);
                DataValidationConstraint dvConstraint = (DataValidationConstraint) dvHelper.createExplicitListConstraint(datas);
                CellRangeAddressList addressList = new CellRangeAddressList(3, 2000, listTpamscolumn.size() - 1, listTpamscolumn.size() - 1);
                HSSFDataValidation validation = (HSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
                sheet2.addValidationData(validation);
                //设置最后一列下拉列表----end
                sheet2.setColumnHidden(0, true);//第1列将被隐藏
            }
            //isColor用于标识是否是说明页，true表示是说明页，false表示不是说明页
            writeSheetSelect(false, sheet2, data, listTpamscolumn,dataList, flag, rowFlag);
            cellSetWidth(sheet2);
        }
    
        /**
         * @return void
         * @Author: best_liu
         * @Description: POI Excel导出自适应列宽
         * @Date: 9:26 2022/4/14
         * @Param [sheet]
         **/
        public static void cellSetWidth(Sheet sheet) {
            Row row = sheet.getRow(0);
            // 每列
            short lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                sheet.autoSizeColumn(j);
                sheet.setColumnWidth(j, sheet.getColumnWidth(j) * 17 / 10);
            }
        }
    
        /**
         * 把数据写入到单元格
         *
         * @param listTpamscolumn 表头数据
         * @param datas           行内数据
         * @param sheet           工作表（excel分页）
         * @throws Exception void
         */
        private void writeSheetContent(List<Column> listTpamscolumn, List<T> datas, 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 < datas.size(); i++, index++) {
                    row = sheet.createRow(index);//创建行
                    for (int j = 0; j < listTpamscolumn.size(); j++) {
                        createColl(row, j, listTpamscolumn.get(j).getFieldName(), datas.get(i));
                    }
                }
            } else {
                getColumnList(listTpamscolumn, listCol);
                for (int i = 0, index = rowIndex; i < datas.size(); i++, index++) {
                    row = sheet.createRow(index);//创建行
                    for (int j = 0; j < listCol.size(); j++) {
                        Column c = listCol.get(j);
                        createCol(row, c, datas.get(i));
                    }
                    //隐藏第三行
                    if (index == 2) {
                        row.setZeroHeight(true);
                    }
    
                }
            }
        }
    
        /**
         * 根据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;
        }
    
    
        /**
         * 判断指定的单元格是否是合并单元格----刘兴杰
         *
         * @param sheet
         * @param row    行下标
         * @param column 列下标
         * @return
         */
        public static MergedResult mergedRegionBoolean(Sheet sheet, int row, int column) {
            MergedResult mergedResult = new MergedResult();
            boolean isMerged = false;//判断是否合并单元格
    
            mergedResult.setRowIndex(row);//判断的行
            mergedResult.setColumnIndex(column);//判断的列
            //获取sheet中有多少个合并单元格
            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 //判断行
                        && column >= firstColumn && column <= lastColumn) {//判断列
                    isMerged = true;
                    mergedResult.setFirstRow(firstRow);
                    mergedResult.setLastRow(lastRow);
                    mergedResult.setFirstColumn(firstColumn);
                    mergedResult.setLastColumn(lastColumn);
                    mergedResult.setRowMergeNum(lastRow - firstRow + 1);
                    mergedResult.setColumnMergeNum(lastColumn - firstColumn + 1);
                    break;
                }
            }
            mergedResult.setIsMerged(isMerged);
            return mergedResult;
        }
    
        /**
         * @return org.apache.poi.ss.usermodel.CellStyle
         * @Author: best_liu
         * @Description: 返回表头样式
         * @Date: 11:17 2022/5/10
         * @Param [isColor]
         **/
        public CellStyle getStyle(Boolean isColor) {
            if (isColor) {
                return this.styleHead;
            } else {
                CellStyle style = this.workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);// 左右居中
                style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
                style.setRightBorderColor((short) 8);
                style.setBottomBorderColor((short) 8);
                //设置背景颜色
                style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                return style;
            }
        }
    
        /**
         * 使用递归 在excel写入表头数据 支持单级，多级表头的创建
         *
         * @param listTpamscolumn 表头数据
         * @param sheetCo         哪个分页
         * @param rowIndex        当前Excel的第几行
         */
        public void createHead(Boolean isColor, 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());
                //说明页设置说明文字字体为红色，加粗
                if (isColor && c == 1) {
                    HSSFFont font = workbook.createFont();
                    font.setColor(IndexedColors.RED.getIndex()); //红色
                    font.setBold(true); //是否加粗
                    text.applyFont(font);
                }
                //非说明页设置表头背景颜色
                if (!isColor) {
                    cell.setCellStyle(getStyle(isColor)); //设置表头样式
                } else {
                    cell.setCellStyle(getStyle(isColor)); //设置表头样式
                }
    
                cell.setCellValue(text);
                //判断当前单元格是否被合并
                if (!mergedRegionBoolean(sheetCo, r, c).getIsMerged()) {
                    // 合并单元格
                    CellRangeAddress cra = new CellRangeAddress(r, endR, c, endC);
                    sheetCo.addMergedRegion(cra);
    
                    // 使用RegionUtil类为合并后的单元格添加边框
                    RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheetCo); // 下边框
                    RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheetCo); // 左边框
                    RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheetCo); // 有边框
                }
    
                if (tpamscolumn.isHasChilren()) {
                    rowIndex = r + 1;
                    createHead(isColor, tpamscolumn.getListTpamscolumn(), sheetCo, rowIndex);
                }
            }
        }
    
        public void createHead(Boolean isColor, List<Column> listTpamscolumn, HSSFSheet sheetCo,List<MaterialDictData> dataList, int rowIndex) {
            HSSFRow row = sheetCo.getRow(rowIndex);
    //        if(row == null)row = sheetCo.createRow(rowIndex);
            int len = listTpamscolumn.size();//当前行 有多少列
            int index = 1;
            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());
                //说明页设置说明文字字体为红色，加粗
                if (isColor && c == 1) {
                    HSSFFont font = workbook.createFont();
                    font.setColor(IndexedColors.RED.getIndex()); //红色
                    font.setBold(true); //是否加粗
                    text.applyFont(font);
                }
                //非说明页设置表头背景颜色
                if (!isColor) {
                    cell.setCellStyle(getStyle(isColor)); //设置表头样式
                } else {
                    cell.setCellStyle(getStyle(isColor)); //设置表头样式
                }
    
                cell.setCellValue(text);
                //设置下拉列表----start
                MaterialDictData mdd = dataList.get(i);
                //解决EasyPoi导出Excel带下拉框，值超过255报错问题
                if(mdd.getOptions() != null && mdd.getOptions().size()>20){
                    ++index;
                    List<String> strings = new ArrayList<>();
                    mdd.getOptions().forEach(item -> strings.add(item.getDictLabel()));
                    String[] datas = strings.stream().toArray(String[]::new);
                    selectList("hiddon"+index,index,this.workbook,i,i,datas);
                }else if(mdd.getOptions() != null && mdd.getOptions().size()>0){
                    List<String> strings = new ArrayList<>();
                    mdd.getOptions().forEach(item -> strings.add(item.getDictLabel()));
                    String[] datas = strings.stream().toArray(String[]::new);
                    HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheetCo);
                    DataValidationConstraint dvConstraint = (DataValidationConstraint) dvHelper.createExplicitListConstraint(datas);
                    CellRangeAddressList addressList =  new CellRangeAddressList(3, 2000, i, i);
                    HSSFDataValidation validation  = (HSSFDataValidation) dvHelper.createValidation( dvConstraint, addressList);
                    sheetCo.addValidationData(validation);
                }
                //设置最后一列下拉列表----end
                //判断当前单元格是否被合并
                if (!mergedRegionBoolean(sheetCo, r, c).getIsMerged()) {
                    // 合并单元格
                    CellRangeAddress cra = new CellRangeAddress(r, endR, c, endC);
                    sheetCo.addMergedRegion(cra);
    
                    // 使用RegionUtil类为合并后的单元格添加边框
                    RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheetCo); // 下边框
                    RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheetCo); // 左边框
                    RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheetCo); // 有边框
                }
    
                if (tpamscolumn.isHasChilren()) {
                    rowIndex = r + 1;
                    createHead(isColor, tpamscolumn.getListTpamscolumn(), sheetCo, rowIndex);
                }
            }
        }
    
        /** @Author: best_liu
         * @Description:解决下拉字符串超过255时报错问题
         * @Date: 17:31 2023/3/7
         * @Param [sheetName, index, workbook, firstCol, lastCol, strings]
         * @return void
         **/
        public void selectList(String sheetName, Integer index, Workbook workbook, int firstCol, int lastCol, String[] strings ){
    
            //将下拉框数据放到新的sheet里，然后excle通过新的sheet数据加载下拉框数据
            Sheet hidden = workbook.createSheet(sheetName);
    
            //创建单元格对象
            Cell cell =null;
            //遍历我们上面的数组，将数据取出来放到新sheet的单元格中
            for (int i = 0, length = strings.length; i < length; i++){
                //取出数组中的每个元素
                String name = strings[i];
                //根据i创建相应的行对象（说明我们将会把每个元素单独放一行）
                Row row = hidden.createRow(i);
                //创建每一行中的第一个单元格
                cell = row.createCell(0);
                //然后将数组中的元素赋值给这个单元格
                cell.setCellValue(name);
            }
            // 创建名称，可被其他单元格引用
            Name namedCell = workbook.createName();
            namedCell.setNameName(sheetName);
            // 设置名称引用的公式
            namedCell.setRefersToFormula(sheetName+"!$A$1:$A$" + strings.length);
            //加载数据,将名称为hidden的sheet中的数据转换为List形式
            DVConstraint constraint = DVConstraint.createFormulaListConstraint(sheetName);
    
            // 设置第一列的3-65534行为下拉列表
            // (3, 65534, 2, 2) ====> (起始行,结束行,起始列,结束列)
            CellRangeAddressList regions = new CellRangeAddressList(0, 65535, firstCol, lastCol);
            // 将设置下拉选的位置和数据的对应关系 绑定到一起
            DataValidation dataValidation = new HSSFDataValidation(regions, constraint);
    
            //将第二个sheet设置为隐藏
            workbook.setSheetHidden(index, true);
            //将数据赋给下拉列表
            workbook.getSheetAt(1).addValidationData(dataValidation);
    
        }
    
    
        /**
         * 转换成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);
                int tree_step = TreeTool.getTreeStep(list, poit.getPid(), 0);//往上遍历tree
                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().getCode()) { // 判断当前Cell的Type
    
                    case 0:  // 如果当前Cell的Type为NUMERIC
                    case 2: {
                        // 判断当前的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 1:  // 如果当前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().getCode()) {
                case 1:// 如果当前Cell的Type为STRIN
                    strCell = cell.getStringCellValue().trim();
                    break;
                case 0:// 如果当前Cell的Type为NUMERIC
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {//导入为日期时
                        //格式化日期
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sf = new SimpleDateFormat("yyyy/MM/dd");//时间格式话
                        strCell = sf.format(date);
                    } else {
    //                strCell = String.valueOf(cell.getNumericCellValue()).trim();
                        //在导入Excel时，长数字变成了科学计数法,以下方法防止变成科学计数
                        NumberFormat nf = NumberFormat.getInstance();
                        strCell = nf.format(cell.getNumericCellValue());//该方法获得的数字会变成每3位数字加一个逗号，这里还需要将逗号去掉
                        if (strCell.indexOf(",") >= 0) {
                            strCell = strCell.replace(",", "");
                        }
                    }
                    break;
                case 4:
                    strCell = String.valueOf(cell.getBooleanCellValue()).trim();
                    break;
                case 3:
                    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;
        }
    
        /**
         * @return java.util.List<java.util.List < java.lang.String>>
         * @Author: best_liu
         * @Description: 从流中获取excel
         * @Date: 14:27 2022/3/16
         * @Param [is, sheetNum]
         **/
        public List<List<String>> getExcelValues(InputStream is, int sheetNum) throws Exception {
            List<List<String>> values = new ArrayList<List<String>>();
            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().getCode() == 3) {
                    num++;
                }
            }
            if (maxRow == num) {
                return true;
            }
            return false;
        }
    
        /**
         * @return List<List < Map < String, String>>>
         * @Author: best_liu
         * @Description: 根据sheet数获取excel的值 返回List<List<Map<String,String>>>的数据结构
         * @Date: 15:12 2022/3/18
         * @Param
         **/
        public List<Map<String, Object>> getExcelMapVal(InputStream is, int sheetNum) throws Exception {
            List<Map<String, Object>> values = new ArrayList<Map<String, Object>>();
            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(2);//取底三行数据，第三行隐藏字段
            int colCount = sheet.getRow(0).getLastCellNum();//用表头去算有多少列，不然从下面的行计算列的话，空的就不算了
            for (int i = 3; i <= rowNum; i++) {//从第三行开始
                Row row = sheet.getRow(i);//第i行
                if (row == null || CheckRowNull(row, colCount)) {//过滤空行
                    continue;
                }
                Map<String, Object> map = new HashMap<>();
                for (int j = 0; j < colCount; j++) {//第j列://+1是因为最后一列是空 也算进去
                    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);
                }
                values.add(map);
            }
            return values;
        }
    
        /**
         * 根据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;
        }
    }

3.编写导出工具类--TreeTool

import com.mes.material.excelTool.vo.Column;
    
    import java.util.ArrayList;
    import java.util.Collections;
    import java.util.Iterator;
    import java.util.List;
    
    /**
     * 处理tree结构的数据 工具类
     * Created by wtj on 2018/5/20
     * 修改时间：2019/03/20
     */
    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;
        }
    }

4.编写导出工具类--Colum

import java.util.ArrayList;
    import java.util.List;
    
    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;
        }
    }

5.编写导出工具类--MergedResult

public class MergedResult {
        boolean isMerged;//是否合并单元格
        int rowIndex;//行下标
        int columnIndex;//列下标
        int firstRow;//合并的行 开始下标
        int lastRow;//合并的行 结束下标
        int firstColumn;//合并的列 开始下标
        int lastColumn;//合并的列 结束下标
        int rowMergeNum;//合并的行数
        int columnMergeNum;//合并的列数
    
        public boolean getIsMerged() {
            return isMerged;
        }
    
        public void setIsMerged(boolean merged) {
            isMerged = merged;
        }
    
        public int getRowIndex() {
            return rowIndex;
        }
    
        public void setRowIndex(int rowIndex) {
            this.rowIndex = rowIndex;
        }
    
        public int getColumnIndex() {
            return columnIndex;
        }
    
        public void setColumnIndex(int columnIndex) {
            this.columnIndex = columnIndex;
        }
    
        public int getFirstRow() {
            return firstRow;
        }
    
        public void setFirstRow(int firstRow) {
            this.firstRow = firstRow;
        }
    
        public int getLastRow() {
            return lastRow;
        }
    
        public void setLastRow(int lastRow) {
            this.lastRow = lastRow;
        }
    
        public int getFirstColumn() {
            return firstColumn;
        }
    
        public void setFirstColumn(int firstColumn) {
            this.firstColumn = firstColumn;
        }
    
        public int getLastColumn() {
            return lastColumn;
        }
    
        public void setLastColumn(int lastColumn) {
            this.lastColumn = lastColumn;
        }
    
        public int getRowMergeNum() {
            return rowMergeNum;
        }
    
        public void setRowMergeNum(int rowMergeNum) {
            this.rowMergeNum = rowMergeNum;
        }
    
        public int getColumnMergeNum() {
            return columnMergeNum;
        }
    
        public void setColumnMergeNum(int columnMergeNum) {
            this.columnMergeNum = columnMergeNum;
        }
    }

6.编写导出工具类--TitleEntity

public class TitleEntity {
        public String t_id;
        public String t_pid;
        public String t_content;
        public String t_fielName;
    
        public TitleEntity() {
        }
    
        public TitleEntity(String t_id, String t_pid, String t_content, String t_fielName) {
            this.t_id = t_id;
            this.t_pid = t_pid;
            this.t_content = t_content;
            this.t_fielName = t_fielName;
        }
    
        public String getT_id() {
            return t_id;
        }
    
        public void setT_id(String t_id) {
            this.t_id = t_id;
        }
    
        public String getT_pid() {
            return t_pid;
        }
    
        public void setT_pid(String t_pid) {
            this.t_pid = t_pid;
        }
    
        public String getT_content() {
            return t_content;
        }
    
        public void setT_content(String t_content) {
            this.t_content = t_content;
        }
    
        public String getT_fielName() {
            return t_fielName;
        }
    
        public void setT_fielName(String t_fielName) {
            this.t_fielName = t_fielName;
        }
    }

7.编写导出工具类--ExcelUtils

import com.mes.material.domain.MaterialDictData;
    import com.mes.material.excelTool.ExcelTool;
    import com.mes.material.excelTool.vo.Column;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.util.*;
    
    /**
     * @Author: best_liu
     * @Description:
     * @Date Create in 10:37 2022/4/6
     * @Modified By:
     */
    public class ExcelUtils {
    
    
        /**
         * @return void
         * @Author: best_liu
         * @Description: 导出模板工具类
         * @Date: 10:39 2022/4/6
         * @Param [request, response]
         **/
        public static void export(HttpServletRequest request, HttpServletResponse response, List<MaterialDictData> dataList, String materialType, String materialLable) throws Exception {
            //        单级的表头
            List<Map<String, String>> titleList = new ArrayList<>();
            //单级的 行内数据
            List<Map<String, String>> rowList = new ArrayList<>();
            Map mm = new HashMap<String, String>();
            for (MaterialDictData mDate : dataList) {
                Map<String, String> map = new HashMap<String, String>();
                map.put(mDate.getMaterialLabel(), mDate.getMaterialValue());
                titleList.add(map);
                mm.put(mDate.getMaterialValue(), mDate.getMaterialValue());
            }
            rowList.add(mm);
            //单级的 行内数据
            List<Map<String, String>> eList = new ArrayList<>();
            //说明
            Map<String, String> explain = new HashMap<String, String>();
            explain.put("请勿修改表格结构，只需在对应列下填写数据即可。", "explain");
            List<Map<String, String>> explainList = new ArrayList<>();
            Map<String, String> maodian = new HashMap<String, String>();
            maodian.put(materialType, "id");
            explainList.add(maodian);
            explainList.add(explain);
            String title = materialLable + "模板";
            ExcelTool excelTool = new ExcelTool(title, 15, 20);
            List<Column> explainData = excelTool.columnTransformer(explainList);
    
            List<Column> titleData = excelTool.columnTransformer(titleList);
            //获取要下载的文件输入流
            //hidden用来标识第一列是否被隐藏
            InputStream inputStream = excelTool.exportExcelSelect(false, explainData, eList, titleData, rowList,dataList, true, false);
            //获取要下载的文件名
            try {
    
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                //设置content-disposition响应头控制浏览器以下载的形式打开文件
                response.addHeader("Content-Disposition", "attachment;filename=outExcel.xls");
            } catch (Exception e) {
                e.printStackTrace();
            }
            OutputStream out = null;
            try {
                //获取要下载的文件输入流
                int len = 0;
                //创建数据缓冲区
                byte[] buffer = new byte[1024];
                //通过response对象获取outputStream流
                out = response.getOutputStream();
                //将FileInputStream流写入到buffer缓冲区
                while ((len = inputStream.read(buffer)) > 0) {
                    //使用OutputStream将缓冲区的数据输出到浏览器
                    out.write(buffer, 0, len);
                }
                out.flush();
                inputStream.close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                if (out != null) {
                    try {
                        out.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    
        /**
         * @return void
         * @Author: best_liu
         * @Description: 导出物资信息工具类
         * @Date: 10:39 2022/4/6
         * @Param [request, response]
         **/
        public static void exportMatreial(HttpServletRequest request, HttpServletResponse response, List<MaterialDictData> li, List<Map<String, String>> list, String materialType, String materialName) throws Exception {
            //        单级的表头
            List<Map<String, String>> titleList = new ArrayList<>();
            Map<String, String> ma = new LinkedHashMap<>();
            ma.put("主键", "matId");
            titleList.add(ma);
            //单级的 行内数据
            List<Map<String, String>> rowList = new ArrayList<>();
            Map mm = new HashMap<String, String>();
            mm.put("matId", "matId");
            for (MaterialDictData mDate : li) {
                Map<String, String> map = new LinkedHashMap<String, String>();
                map.put(mDate.getMaterialLabel(), mDate.getMaterialValue());
                titleList.add(map);
                mm.put(mDate.getMaterialValue(), mDate.getMaterialValue());
            }
            Map<String, String> m = new LinkedHashMap<String, String>();
            m.put("存放位置", "storageLocation");
            titleList.add(m);
            Map<String, String> m1 = new LinkedHashMap<String, String>();
            m1.put("是否在仓库中", "isExist");
            titleList.add(m1);
            mm.put("storageLocation", "storageLocation");
            mm.put("isExist", "isExist");
            rowList.add(mm);
            for (Map<String, String> map : list) {
                rowList.add(map);
            }
            //单级的 行内数据
            List<Map<String, String>> eList = new ArrayList<>();
            //说明
            Map<String, String> explain = new HashMap<String, String>();
            explain.put("请勿修改表格结构，只需在对应列下填写数据即可。" + "\r\n" + "盘点人员只需要给表格最后一列赋值即可，如果仓库中和表格一致，填‘是’，不一致填‘否’。" + "\r\n" + "表格中没有的物资在下面新加一条数据。", "explain");
            List<Map<String, String>> explainList = new ArrayList<>();
            Map<String, String> maodian = new HashMap<String, String>();
            maodian.put(materialType, "id");
            explainList.add(maodian);
            explainList.add(explain);
            ExcelTool excelTool = new ExcelTool(materialName + "列表", 15, 20);
            List<Column> explainData = excelTool.columnTransformer(explainList);
    
            List<Column> titleData = excelTool.columnTransformer(titleList);
            //获取要下载的文件输入流
            //hidden用来标识第一列是否被隐藏
            InputStream inputStream = excelTool.exportExcel(true, explainData, eList, titleData, rowList, true, false);
            //获取要下载的文件名
            try {
    
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                //设置content-disposition响应头控制浏览器以下载的形式打开文件
                response.addHeader("Content-Disposition", "attachment;filename=outExcel.xls");
            } catch (Exception e) {
                e.printStackTrace();
            }
            OutputStream out = null;
            try {
                //获取要下载的文件输入流
                int len = 0;
                //创建数据缓冲区
                byte[] buffer = new byte[1024];
                //通过response对象获取outputStream流
                out = response.getOutputStream();
                //将FileInputStream流写入到buffer缓冲区
                while ((len = inputStream.read(buffer)) > 0) {
                    //使用OutputStream将缓冲区的数据输出到浏览器
                    out.write(buffer, 0, len);
                }
                out.flush();
                inputStream.close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                if (out != null) {
                    try {
                        out.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

8.实现下载

public void export(HttpServletRequest request, HttpServletResponse response, @RequestBody(required = false) MaterialDictType materialDictType) throws Exception {
            //查询是导入字段的列表
            MaterialDictData dictData = new MaterialDictData();
            dictData.setMaterialType(materialDictType.getMaterialType());
            dictData.setIsImport(YesOrNoEnum.yes.getCode());
            List<MaterialDictData> list = dictDataService.selectMaterialDataList(dictData);
            ExcelUtils.export(request, response, list, materialDictType.getMaterialType(), materialDictType.getMaterialName());
        }

9.实现上传

@RequestMapping("/upload")
        @ResponseBody
        public String handleFileUpload(@RequestParam("file") MultipartFile file) throws Exception {
    
            //获取文件后缀
            String type = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
            if(!Arrays.asList(MaterialConstants.excelType).contains(type)){
                return "仅允许导入“xls”或“xlsx”格式文件！";
            }
    
            if (file.isEmpty()) {
                return "文件为空";
            }
            byte[] byteArr = new byte[0];
            try {
                byteArr = file.getBytes();
            } catch (IOException e) {
                e.printStackTrace();
            }
            InputStream inputStream = new ByteArrayInputStream(byteArr);
            ExcelTool excelTool = new ExcelTool();
            List<List<String>> sheet1 = excelTool.getExcelValues(inputStream, 1);
            InputStream inputStream2 = new ByteArrayInputStream(byteArr);
            //获取锚点标识
            System.out.println(sheet1.get(0).get(0));
            List<Map<String, Object>> readexeclC = excelTool.getExcelMapVal(inputStream2, 2);
            String result = excelService.handleFileUpload(sheet1.get(0).get(0), readexeclC);
            return result;
        }

10.效果预览

![d69f5fb6e9594132b91453963e2cd732.png][]

![b084d0198c894ac8b9385103f3389007.png][]

[maven]: https://so.csdn.net/so/search?q=maven&spm=1001.2101.3001.7020
[d69f5fb6e9594132b91453963e2cd732.png]: https://img-blog.csdnimg.cn/d69f5fb6e9594132b91453963e2cd732.png
[b084d0198c894ac8b9385103f3389007.png]: https://img-blog.csdnimg.cn/b084d0198c894ac8b9385103f3389007.png