wen poi导出Excel复杂表头的处理
一直在写poi导出表头的时候，发现没有现成的复杂格式的表头的工具类，这类给大家提供一个，我们传入三个参数 head ，title，data，就能为我们创建表头和excel
效果图
<dependencies>
<dependency>
<groupId>;.org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>;.org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
</dependencies>
添加依赖
public class TestData {
public static List<String> getHead() {
List<String> strings = Arrays.asList("一级标题", "二级标题");
return strings;
}
public static List<Map<String, Object>> getTitles() {
List<Map<String, Object>> titles = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Map<String, Object> t = new HashMap<>();
t.put("key", "key" + i);
t.put("name", "名称" + i);
titles.add(t);
}
// 创建二级标题
List<Map<String, Object>> titles2 = new ArrayList<>();
for (int i = 0; i < 3; i++) {
Map<String, Object> t = new HashMap<>();
t.put("key", "key2." + i);
t.put("name", "名称2." + i);
titles2.add(t);
}
titles.get(2).put("children", titles2);
// 创建三级标题
List<Map<String, Object>> titles3 = new ArrayList<>();
for (int i = 0; i < 4; i++) {
Map<String, Object> t = new HashMap<>();
t.put("key", "key5." + i);
t.put("name", "名称5." + i);
titles3.add(t);
}
List<Map<String, Object>> titles4 = new ArrayList<>();
for (int i = 0; i < 3; i++) {
Map<String, Object> t = new HashMap<>();
t.put("key", "key5.2." + i);
t.put("name", "名称5.2." + i);
titles4.add(t);
}
List<Map<String, Object>> titles5 = new ArrayList<>();
for (int i = 0; i < 2; i++) {
Map<String, Object> t = new HashMap<>();
t.put("key", "key5.2.1." +i);
t.put("name", "名称5.2.1." + i);
titles5.add(t);
}
titles4.get(1).put("children", titles5);
titles3.get(2).put("children", titles4);
titles.get(5).put("children", titles3);
return titles;
}
public static List<Map<String, Object>> getTestData(List<String> index) {
List<Map<String, Object>> data = new ArrayList<>();
for (int i = 0; i < 20; i++) {
HashMap<String, Object> map = new HashMap<>();
for (int j = 0; j < index.size(); j++) {
map.put(index.get(j), "数据data" + j);
}
data.add(map);
}
return data;
}
}
---- 以上是传入的参数格式
下面是具体实现代码
public class Main {
public static void main(String[] args) {
File file = new File("F:\test\data\test.xls");
try {
FileOutputStream outfile = new FileOutputStream(file);
List<String> head = TestData.getHead();
List<Map<String, Object>> titles = TestData.getTitles();
Workbook workBook = getWorkBook(head, titles, null);
workBook.write(outfile);
outfile.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private static Workbook getWorkBook(List<String> head, List<Map<String, Object>> titles, List<Map<String, Object>> data) throws Exception {
//全部的列，并判断数据格式是否正确
List<Map<String, Object>> alltitles = new ArrayList<>();
getProperty(titles, alltitles);
// 存储数据的key对应的excel索引
List<String> columIndex = new ArrayList<>();
getDataIndex(titles, columIndex);
int allwidth = columIndex.size();
Workbook work = new HSSFWorkbook();
CellStyle headStyle = getHeadStyle(work);
CellStyle titleStyle = getTitleStyle(work);
CellStyle dataStyle = getDataStyle(work);
Sheet sheet = work.createSheet();
//设置单元格宽度
sheet.setDefaultColumnWidth(18);
int headHeight = head == null ? 0 : head.size();
int titleHeight = getMaxDeep(titles);
//创建头标题
for (int i = 0; i < headHeight; i++) {
Row row = sheet.createRow(i);
row.setHeightInPoints(30);
Cell cell = row.createCell(0);
CellRangeAddress region = new CellRangeAddress(i, i, 0, allwidth-1);
sheet.addMergedRegion(region);
cell.setCellValue(head.get(i));
}
setStyle(work,sheet,0,2,0,allwidth,headStyle);
//创建表头
for (int i = 0; i < titleHeight; i++) {
int height = i + headHeight ;
Row row = sheet.createRow(height);
for (int j = 0; j < alltitles.size(); j++) {
Map<String, Object> colum = alltitles.get(j);
Integer level = Integer.valueOf(colum.get("level").toString());
if (level == i) {
int left = Integer.valueOf(colum.get("left").toString());
int width = Integer.valueOf(colum.get("width").toString());
Cell cell = row.createCell(left);
//判断横向合
int toMergeCol = left+width-1;
//判断是否需要纵向合并
int toMergeRow = hasChildren(colum) ? height : headHeight+titleHeight-1;
if(left!=toMergeCol || height != toMergeRow){
CellRangeAddress region = new CellRangeAddress(height, toMergeRow, left, toMergeCol);
sheet.addMergedRegion(region);
}
cell.setCellValue(colum.get("name").toString());
}
}
}
// 表头设置样式
setStyle(work,sheet,headHeight,headHeight+titleHeight,0,allwidth,titleStyle);
// 创建数据,后续工具类的data是传过来的
List<Map<String, Object>> testData = TestData.getTestData(columIndex);
for(int i = 0 ; i < testData.size();i++ ){
Row row = sheet.createRow(headHeight + titleHeight + i);
Map<String, Object> res = testData.get(i);
row.setHeightInPoints((short)20);
for(int j = 0 ;j < columIndex.size();j++){
Cell cell = row.createCell(j);
cell.setCellStyle(dataStyle);
cell.setCellValue(res.get(columIndex.get(j)).toString());
}
}
return work;
}
private static void getProperty(List<Map<String, Object>> treetitles,List<Map<String, Object>> listtitles) {
Map<String,Integer> left = new HashMap<>();
left.put("left",0);
addAllColum(treetitles,listtitles,0,left);
}
/**
* 查询这个节点的树的最大宽度
*
* @param o
* @return
*/
private static int getMaxWidth(Object o) {
int res = 0;
if (o == null) {
return res;
}
if (o instanceof Map) {
Map map = (Map) o;
return hasChildren(map) ? getMaxWidth(map.get("children")) : 1;
} else if (o instanceof List) {
List list = (List) o;
for (int i = 0; i < list.size(); i++) {
Map map = (Map) list.get(i);
int width = hasChildren(map) ? getMaxWidth(map.get("children")) : 1;
res = res + width;
}
}
return res;
}
/**
* 获取excel的data的数据位于key的index
*
* @param titles
* @param colum
*/
private static void getDataIndex(List<Map<String, Object>> titles, List<String> colum) {
if (titles == null || titles.size() == 0) {
return;
}
for (int i = 0; i < titles.size(); i++) {
Map<String, Object> map = titles.get(i);
if (hasChildren(map)) {
List<Map<String, Object>> children = (List<Map<String, Object>>) map.get("children");
getDataIndex(children, colum);
} else {
colum.add(map.get("key").toString());
}
}
}
/**
* 将树的所有节点放在一个list中
*
* @param titles 树结构的title
* @param all 存放全部节点的list
* @param deep 当前节点的深度
* @param left 当前节点左边Excel的索引距离，（即当前节点左边没有子节点的节点个数）
*/
private static void addAllColum(List<Map<String, Object>> titles, List<Map<String, Object>> all, Integer deep, Map<String,Integer> left) {
if (titles == null || titles.size() == 0) {
return;
}
for (int i = 0; i < titles.size(); i++) {
Map<String, Object> t = titles.get(i);
if(t.get("key")==null){
throw new RuntimeException("节点的key不能为null");
}
t.put("left", left.get("left"));
t.put("level", deep);
t.put("width",getMaxWidth(t));
if(!hasChildren(t)){
left.put("left",left.get("left")+1);
}
if (hasChildren(t)) {
addAllColum((List<Map<String, Object>>) t.get("children"), all, deep + 1, left);
}
// 查看是否有重复的key，重复的key就抛出参数不合法异常
for(int k =0 ; k < all.size(); k++){
Map<String, Object> before = all.get(k);
if(before.get("key").toString().equals(t.get("key"))){
throw new RuntimeException("节点的key:"+t.get("key")+"不能重复");
}
}
all.add(t);
}
}
/**
* 获取这个节点的最大深度
*
* @param o
* @return
*/
public static int getMaxDeep(Object o) {
if (o == null) {
return 0;
}
HashMap<String, Integer> deep = new HashMap<>();
deep.put("deep", 1);
if (o instanceof List) {
List<Map<String, Object>> list = (List<Map<String, Object>>) o;
toNextDeep(list, 1, deep);
} else if (o instanceof Map) {
Map map = (Map) o;
if (hasChildren(map)) {
List<Map<String, Object>> children = (List<Map<String, Object>>) map.get("children");
toNextDeep(children, 2, deep);
}
}
return deep.get("deep");
}
private static void toNextDeep(List<Map<String, Object>> titles, int level, HashMap<String, Integer> deep) {
if (titles != null && titles.size() == 0) {
return;
}
if (deep.get("deep") < level) {
deep.put("deep", level);
}
for (int i = 0; i < titles.size(); i++) {
Map<String, Object> map = titles.get(i);
if (hasChildren(map)) {
toNextDeep((List<Map<String, Object>>) map.get("children"), level + 1, deep);
}
}
}
private static boolean hasChildren(Object o){
if(o == null){
return false;
}
Map map=(Map)o;
if(map.get("children")!=null && map.get("children") instanceof List){
List list = (List)map.get("children");
return list.size()!=0;
}
return false;
}
private static CellStyle getHeadStyle(Workbook wb) throws Exception {
CellStyle head = wb.createCellStyle();
head.setBorderTop(BorderStyle.THIN);
head.setBorderRight(BorderStyle.THIN);
head.setBorderBottom(BorderStyle.THIN);
head.setBorderLeft(BorderStyle.THIN);
head.setVerticalAlignment(VerticalAlignment.CENTER);
head.setAlignment(HorizontalAlignment.CENTER);
head.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.index);
head.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font font = wb.createFont();
font.setFontName("仿宋_GB2312");
font.setColor(IndexedColors.WHITE.index);
font.setFontHeight((short) 24);
font.setFontHeightInPoints((short) 26);
head.setFont(font);
return head;
}
private static CellStyle getTitleStyle(Workbook wb) throws Exception {
CellStyle head = wb.createCellStyle();
head.setBorderTop(BorderStyle.THIN);
head.setBorderRight(BorderStyle.THIN);
head.setBorderBottom(BorderStyle.THIN);
head.setBorderLeft(BorderStyle.THIN);
head.setVerticalAlignment(VerticalAlignment.CENTER);
head.setAlignment(HorizontalAlignment.CENTER);
head.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
head.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font font = wb.createFont();
font.setFontName("仿宋_GB2312");
font.setFontHeight((short) 18);
font.setFontHeightInPoints((short) 18);
head.setFont(font);
return head;
}
private static CellStyle getDataStyle(Workbook wb) throws Exception {
CellStyle head = wb.createCellStyle();
head.setBorderTop(BorderStyle.THIN);
head.setBorderRight(BorderStyle.THIN);
head.setBorderBottom(BorderStyle.THIN);
head.setBorderLeft(BorderStyle.THIN);
head.setVerticalAlignment(VerticalAlignment.CENTER);
head.setAlignment(HorizontalAlignment.CENTER);
Font font = wb.createFont();
font.setFontName("仿宋_GB2312");
font.setFontHeight((short) 14);
font.setFontHeightInPoints((short) 14);
head.setFont(font);
return head;
}
private static void setStyle(Workbook work,Sheet sheet,int rowfrom ,int rowto,int colfrom,int cellto,CellStyle style){
//添加全局样式
for(int i = rowfrom; i < rowto ;i++){
Row cells = sheet.getRow(i) == null ? sheet.createRow(i) : sheet.getRow(i);
for(int j = colfrom ; j <cellto ;j++){
Cell cell = cells.getCell(j) == null ? cells.createCell(j) : cells.getCell(j);
cell.setCellStyle(style);
}
}
}
}
