前言
GitHub地址：freedom-excel
一、JAVA POI 实现EXCEL 工具类
我们目的是要建立一个EXCEL的工具类，它的功能：
1、可以生成动态表头，单级，多级都支持，尤其是树形表头（整体思路按照树形结构数据来遍历）；
2、数据可配置，支持动态填写数据（一个List结构的数据）；
3、读取Excel数据；
设计思路：需要一个含有树形结构的实体类Column，用于转换外部数据；而且该实体类要记录下它自己在EXCEL表中的坐标，以及跨行，跨列；接着用这个实体类来生成EXCEL表中的单元格cell。其中还需一个Tree工具类，它要处理Tree型结构数据；最终达到灵活导入导出Excel。
备注: tree根(最顶的节点root)的id必须为零。
二、工具，知识
工具：Java8 + PIO3.9+ Maven +Idea
知识：PIO操作Excel、递归使用、Map List 的遍历、Java类的反射、File的读写
三、代码
补充：
<!--依赖的jar包--> <dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.9</version></dependency>
（1）Column的实体类
/*** Created by wtj on /5/2.*/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;}}
（2）TreeTool类
import java.util.ArrayList;import java.util.Collections;import java.util.Iterator;import java.util.List;/*** 处理tree结构的数据 工具类* Created by wtj on /5/20* 修改时间：/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++;elsesum += 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;}}
（3）ExcelTool类
import org.apache.poi.hssf.usermodel.*;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;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.SimpleDateFormat;import java.util.*;/*** excel处理工具* 概念-> 表头数据:报表的表头* 行内数据:表头以下的数据* 功能:动态生成单级，多级Excel表头* 备注：tree型结构数据的root节点的id必须为零（0）* Created by wtj on /3/2.* 修改:*/03/18 修复生成跨列的bug*/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(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_CENTERthis.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_CENTERthis.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 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);}/*** 返回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 listTpamscolumn 表头数据* @param flag 只输出表头数据* @param rowFlag 输出展示数据的结构(表头下面行的数据)* @throws Exception*/private void writeSheet(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(listTpamscolumn,sheet,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(sheet,subList, listTpamscolumn,flag,rowFlag);}HSSFSheet sheet = this.workbook.createSheet(this.title+(pieces+1));writeSheet(sheet, data.subList(pieces*maxColumn, dataCount), listTpamscolumn,flag,rowFlag);}/*** 把数据写入到单元格* @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));}}}}/*** 根据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);int tree_step = TreeTool.getTreeStep(list,poit.getPid(),0);//往上遍历treepoit.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<>();//新的遍历listfor(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的Typecase HSSFCell.CELL_TYPE_NUMERIC: // 如果当前Cell的Type为NUMERICcase HSSFCell.CELL_TYPE_FORMULA: {// 判断当前的cell是否为Dateif (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); //-}else if(".xlsx".equals(fileType)){wb = new XSSFWorkbook(inStr); //+}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); //读取第几个工作表sheetint 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); //读取第几个工作表sheetint 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;}}
四、测试类
（1）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;}}
（2）测试类Main
兄弟们，需要运行哪些自己解开注释
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 titleEntity0=new TitleEntity("0",null,"总表",null);TitleEntity titleEntity=new TitleEntity("11","0","登录名2","u_login_id");TitleEntity titleEntity1=new TitleEntity("1","0","姓名","u_name");TitleEntity titleEntity11=new TitleEntity("1_1","1","姓名1","u_name");TitleEntity titleEntity2=new TitleEntity("2","0","角色加部门",null);TitleEntity titleEntity3=new TitleEntity("3","2","角色","u_role");TitleEntity titleEntity4=new TitleEntity("4","2","部门","u_dep");TitleEntity titleEntity5=new TitleEntity("33","0","角色加部门1",null);TitleEntity titleEntity6=new TitleEntity("33_1","33","角色33","u_role");TitleEntity titleEntity7=new TitleEntity("33_2","33_1","部门33","u_dep");TitleEntity titleEntity8=new TitleEntity("44","0","角色加部门2",null);TitleEntity titleEntity9=new TitleEntity("44_1","44","角色44","u_role");TitleEntity titleEntity10=new TitleEntity("44_2","44","部门44","u_dep");TitleEntity titleEntity12=new TitleEntity("44_3","44_2","44_2","u_dep");titleList.add(titleEntity0);titleList.add(titleEntity); 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); titleList.add(titleEntity11); titleList.add(titleEntity12);//单级的 行内数据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("实体类（entity）数据 多级表头表格",20,20);List<Column> titleData = excelTool.columnTransformer(titleList,"t_id","t_pid","t_content","t_fielName","0");excelTool.exportExcel(titleData,rowList,"D://outExcel.xls",true,true);//读取excel//ExcelTool excelTool = new ExcelTool();//List<List<String>> readexecl=excelTool.getExcelValues("D://outExcel.xls",1);//List<List<Map<String,String>>> readexeclC=excelTool.getExcelMapVal("D://outExcel.xls",1);//int count= excelTool.hasSheetCount("D://outExcel.xls");}}
五、结果图片
（1）导出单级excel
（2）导出多级表头,处理数据是List<Map>
（3）导出多级表头,处理数据是entity
(4)导入单级表头的数据
