因为工作需要生成复杂表头算是用来记录一下这个工具类；对了，目前功能是够用。
怎么算是够用呢？
1、可以生成复杂表头，理论上可以生成多级都可以（应该是，至少我只生成四级表头过）
2、可以添加数据有效性，也就是是excel里面的下拉选项。
3、可以设置宽高
4、默认文字垂直居中、左右居中
5、生成表格时，可以顺便导出数据（导出数据偏多的话我没试过）
未实现：边框效果、文件颜色、文字大小、文字风格、文字颜色、背景颜色
当然，作为一个工具类，这些是可以拓展的，但现在至少我现在没用到；
而且实现这些效果也相对简单，毕竟是更具JSON去生成的；
这个是meven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.75</version>
</dependency>这个是全部代码了
package utils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import java.lang.reflect.Field;
import java.util.*;
public class GenerateExcelTemplateUtils {
/**
* （excel下拉填值）数据有效性 —— 有效范围
*/
public static int VALIDATION_ROW = 1000;
/**
* 单元格宽度比例
*/
public static int WIDTH = 30;
/**
* 单元格高度比例
*/
public static int HEIGHT = 30;
/**
* 工作本名称
*/
public static String SHEET_NAME = "sheet";
/**
* 根据JSON生成复杂表头
*
* @param json
* @return HSSFWorkbook workbook 返回一个工作本
*/
public static XSSFWorkbook complexHeader(String json) {
XSSFWorkbook workbook = new XSSFWorkbook();
List<String> parse = JSON.parseArray(json, String.class);
List<List<TemplateObj>> list = new ArrayList<>();
for (String obj : parse) {
list.add(JSON.parseArray(obj, TemplateObj.class));
}
XSSFSheet sheet = workbook.createSheet(SHEET_NAME);
XSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); //水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
Integer colspan = 0; // 列 y
Integer rowspan = 0; // 行 x
Integer maxColspan = 0; //记录最大的列数
for (int y = 0; y < list.size(); y++) {
XSSFRow excelRow = sheet.createRow(rowspan);
colspan = 0;
for (int x = 0; x < maxColspan; x++) {
if (isMergedRegion(sheet, rowspan, colspan)) {
colspan++;
continue;
}
}
for (int x = 0; x < list.get(y).size(); x++) {
if (isMergedRegion(sheet, rowspan, colspan)) {
colspan++;
}
if (null != list.get(y).get(x).getHeight()) {
excelRow.setHeight((short) (list.get(y).get(x).getHeight() * HEIGHT));
}
XSSFCell cell = excelRow.createCell(colspan);
cell.setCellStyle(style);
cell.setCellValue(list.get(y).get(x).getTitle());
if (list.get(y).get(x).getRowspan() > 1 || list.get(y).get(x).getColspan() > 1) {
if (list.get(y).get(x).getRowspan() > 1) {
Integer lastRow = list.get(y).get(x).getRowspan() + rowspan;
CellRangeAddress region = new CellRangeAddress(rowspan, lastRow - 1, colspan, colspan);
if (null != list.get(y).get(x).getWidth()) {
sheet.setColumnWidth(colspan, list.get(y).get(x).getWidth() * WIDTH);
}
sheet.addMergedRegion(region);
if (list.get(y).get(x).getValidation()) {
setExcelValidation(workbook, rowspan, VALIDATION_ROW, colspan, colspan, list.get(y).get(x).getValidationList());
}
colspan++;
}
if (list.get(y).get(x).getColspan() > 1) {
Integer lastCol = list.get(y).get(x).getColspan() + colspan;
CellRangeAddress region = new CellRangeAddress(rowspan, rowspan, colspan, lastCol - 1);
if (null != list.get(y).get(x).getWidth()) {
sheet.setColumnWidth(colspan, list.get(y).get(x).getWidth() * WIDTH);
}
sheet.addMergedRegion(region);
if (list.get(y).get(x).getValidation()) {
setExcelValidation(workbook, rowspan, VALIDATION_ROW, colspan, colspan, list.get(y).get(x).getValidationList());
}
colspan = lastCol;
}
} else {
if (list.get(y).get(x).getValidation()) {
setExcelValidation(workbook, rowspan, VALIDATION_ROW, colspan, colspan, list.get(y).get(x).getValidationList());
}
if (null != list.get(y).get(x).getWidth()) {
sheet.setColumnWidth(colspan, list.get(y).get(x).getWidth() * WIDTH);
}
colspan++;
}
}
rowspan++;
maxColspan = maxColspan > colspan ? maxColspan : colspan;
}
return workbook;
}
/**
* 导出表格时使用
* @param json
* @return
*/
private static Map<String,Object> complexHeaderCarryData(String json) {
Map<String,Object> map = new HashMap<>();
Map<String,Integer> index = new HashMap<>();
XSSFWorkbook workbook = new XSSFWorkbook();
List<String> parse = JSON.parseArray(json, String.class);
List<List<TemplateObj>> list = new ArrayList<>();
for (String obj : parse) {
list.add(JSON.parseArray(obj, TemplateObj.class));
}
XSSFSheet sheet = workbook.createSheet(SHEET_NAME);
XSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); //水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
Integer colspan = 0; // 列 y
Integer rowspan = 0; // 行 x
Integer maxColspan = 0; //记录最大的列数
for (int y = 0; y < list.size(); y++) {
XSSFRow excelRow = sheet.createRow(rowspan);
colspan = 0;
for (int x = 0; x < maxColspan; x++) {
if (isMergedRegion(sheet, rowspan, colspan)) {
colspan++;
continue;
}
}
for (int x = 0; x < list.get(y).size(); x++) {
if (isMergedRegion(sheet, rowspan, colspan)) {
colspan++;
}
if (null != list.get(y).get(x).getHeight()) {
excelRow.setHeight((short) (list.get(y).get(x).getHeight() * HEIGHT));
}
XSSFCell cell = excelRow.createCell(colspan);
cell.setCellStyle(style);
cell.setCellValue(list.get(y).get(x).getTitle());
if (null != list.get(y).get(x).getField() && !"".equals(list.get(y).get(x).getField())) {
index.put(list.get(y).get(x).getField(), colspan);
}
if (list.get(y).get(x).getRowspan() > 1 || list.get(y).get(x).getColspan() > 1) {
if (list.get(y).get(x).getRowspan() > 1) {
Integer lastRow = list.get(y).get(x).getRowspan() + rowspan;
CellRangeAddress region = new CellRangeAddress(rowspan, lastRow - 1, colspan, colspan);
if (null != list.get(y).get(x).getWidth()) {
sheet.setColumnWidth(colspan, list.get(y).get(x).getWidth() * WIDTH);
}
sheet.addMergedRegion(region);
if (list.get(y).get(x).getValidation()) {
setExcelValidation(workbook, rowspan, VALIDATION_ROW, colspan, colspan, list.get(y).get(x).getValidationList());
}
colspan++;
}
if (list.get(y).get(x).getColspan() > 1) {
Integer lastCol = list.get(y).get(x).getColspan() + colspan;
CellRangeAddress region = new CellRangeAddress(rowspan, rowspan, colspan, lastCol - 1);
if (null != list.get(y).get(x).getWidth()) {
sheet.setColumnWidth(colspan, list.get(y).get(x).getWidth() * WIDTH);
}
sheet.addMergedRegion(region);
if (list.get(y).get(x).getValidation()) {
setExcelValidation(workbook, rowspan, VALIDATION_ROW, colspan, colspan, list.get(y).get(x).getValidationList());
}
colspan = lastCol;
}
} else {
if (list.get(y).get(x).getValidation()) {
setExcelValidation(workbook, rowspan, VALIDATION_ROW, colspan, colspan, list.get(y).get(x).getValidationList());
}
if (null != list.get(y).get(x).getWidth()) {
sheet.setColumnWidth(colspan, list.get(y).get(x).getWidth() * WIDTH);
}
colspan++;
}
}
rowspan++;
maxColspan = maxColspan > colspan ? maxColspan : colspan;
}
map.put("workbook",workbook);
map.put("index",index);
return map;
}
/**
* 判断当前单元格是否为合并单元格
*
* @param sheet
* @param row
* @param column
* @return
*/
private static 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 workbook
* @param firstRow
* @param lastRow
* @param firstCol
* @param lastCol
* @param date
*/
public static void setExcelValidation(XSSFWorkbook workbook, int firstRow, int lastRow, int firstCol, int lastCol, List<String> date) {
XSSFSheet sheet = workbook.getSheet(SHEET_NAME);
String sheetName = getRandomString(8);
XSSFSheet validationSheet = workbook.createSheet(sheetName);
int sheetTotal = workbook.getNumberOfSheets();
int index = 0;
String strFormula = sheetName + "!$A$1:$A$" + date.size();
for (int i = 0; i < date.size(); i++) {
XSSFRow xssfRow = validationSheet.createRow(i);
XSSFCell cell = xssfRow.createCell(index);
cell.setCellValue(date.get(i));
}
XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula);
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidationHelper help = new XSSFDataValidationHelper(sheet);
DataValidation validation = help.createValidation(constraint, regions);
sheet.addValidationData(validation);
workbook.setSheetHidden(sheetTotal - 1, true);
}
public static class TemplateObj {
//映射的字段"
private String field;
//标题")
private String title;
//列宽")
private Integer width;
//行高 为空时 默认单元格行高
private Integer height;
//列数")
private Integer colspan = 1;
//行数")
private Integer rowspan = 1;
//数据有效性 excel的下拉选择 默认不开启"
private boolean validation = false;
//数据有效性 excel的下拉选择 默认不开启"
private List<String> validationList;
public TemplateObj() {
}
/**
* @param field 绑定字段
* @param title 标题
* @param width 列宽
* @param colspan 列数
* @param rowspan 行数
*/
public TemplateObj(String field, String title, Integer width, Integer colspan, Integer rowspan) {
this.field = field;
this.title = title;
this.width = width;
this.colspan = colspan;
this.rowspan = rowspan;
}
/**
* @param field 绑定字段
* @param title 标题
* @param colspan 列数
* @param rowspan 行数
*/
public TemplateObj(String field, String title, Integer colspan, Integer rowspan) {
this.field = field;
this.title = title;
this.colspan = colspan;
this.rowspan = rowspan;
}
/**
* @param field 绑定字段
* @param title 标题
*/
public TemplateObj(String field, String title) {
this.field = field;
this.title = title;
}
/**
* @param field 绑定字段
* @param title 标题
* @param width 列宽
*/
public TemplateObj(String field, String title, Integer width) {
this.field = field;
this.title = title;
this.width = width;
}
/**
* @param title 标题
* @param width 列宽
* @param validationList 数据有效性 集合
*/
public TemplateObj(String title, Integer width, List<String> validationList) {
this.title = title;
this.width = width;
this.validation = true;
this.validationList = validationList;
}
/**
* @param title 标题
* @param validationList 数据有效性 集合
*/
public TemplateObj(String title, List<String> validationList) {
this.title = title;
this.validation = true;
this.validationList = validationList;
}
public boolean getValidation() {
return validation;
}
public String getField() {
return field;
}
public TemplateObj setField(String field) {
this.field = field;
return this;
}
public String getTitle() {
return title;
}
public TemplateObj setTitle(String title) {
this.title = title;
return this;
}
public Integer getWidth() {
return width;
}
public TemplateObj setWidth(Integer width) {
this.width = width;
return this;
}
public Integer getColspan() {
return colspan;
}
public TemplateObj setColspan(Integer colspan) {
this.colspan = colspan;
return this;
}
public Integer getRowspan() {
return rowspan;
}
public TemplateObj setRowspan(Integer rowspan) {
this.rowspan = rowspan;
return this;
}
public boolean isValidation() {
return validation;
}
public TemplateObj setValidation(boolean validation) {
this.validation = validation;
return this;
}
public List<String> getValidationList() {
return validationList;
}
public TemplateObj setValidationList(List<String> validationList) {
this.validationList = validationList;
return this;
}
public Integer getHeight() {
return height;
}
public TemplateObj setHeight(Integer height) {
this.height = height;
return this;
}
}
/**
* 随机生成字符串
*
* @param length
* @return
*/
public static String getRandomString(int length) {
String str = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
Random random = new Random();
StringBuffer sb = new StringBuffer();
for (int i = 0; i < length; i++) {
int number = random.nextInt(62);
sb.append(str.charAt(number));
}
return sb.toString();
}
public static XSSFWorkbook complexHeaderCarryData(String tableJson, String dataJson) {
Map<String, Object> map = complexHeaderCarryData(tableJson);
XSSFWorkbook workbook = (XSSFWorkbook) map.get("workbook");
Map<String, Integer> index = (Map<String, Integer>) map.get("index");
XSSFSheet sheet = workbook.getSheet(SHEET_NAME);
List<Map<String, String>> data = (List<Map<String, String>>) JSONArray.parse(dataJson);
int rowLength = sheet.getLastRowNum() + 1; //行数
if (null != data && data.size() > 0) {
for (int i = 0; i < data.size(); i++) {
XSSFRow row = sheet.createRow(rowLength);
data.get(i).forEach((k, v) -> {
if (index.containsKey(k)) {
XSSFCell cell = row.createCell(index.get(k));
cell.setCellValue(v);
}
});
rowLength++;
}
}
return workbook;
}
public static XSSFWorkbook complexHeaderCarryData(String tableJson,List<?> data) throws Exception {
Map<String, Object> map = complexHeaderCarryData(tableJson);
XSSFWorkbook workbook = (XSSFWorkbook) map.get("workbook");
Map<String, Integer> index = (Map<String, Integer>) map.get("index");
XSSFSheet sheet = workbook.getSheet(SHEET_NAME);
int rowLength = sheet.getLastRowNum() + 1; //行数
for (Object obj : data) {
XSSFRow row = sheet.createRow(rowLength);
Class<?> cls = obj.getClass();
Field[] fields = cls.getDeclaredFields();
for (Field field : fields) {
if (index.containsKey(field.getName())) {
field.setAccessible(true);
XSSFCell cell = row.createCell(index.get(field.getName()));
cell.setCellValue((String) field.get(cls.getDeclaredField(field.getName())));
}
}
rowLength++;
}
return workbook;
}
}这是测试代码
import com.alibaba.fastjson.JSON;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import utils.GenerateExcelTemplateUtils;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
public class TestUtils {
public static void main(String[] args) throws Exception{
List<String> validation = new ArrayList<>();
validation.add("数据1");
validation.add("数据2");
validation.add("数据3");
validation.add("数据4");
List<String> validation2 = new ArrayList<>();
validation2.add("男");
validation2.add("女");
List<List<GenerateExcelTemplateUtils.TemplateObj>> title = new ArrayList<>(); //表头
List<GenerateExcelTemplateUtils.TemplateObj> list1 = new ArrayList<>();//一级表头
list1.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("这是一个一级表头").setColspan(10).setHeight(20));
title.add(list1);
List<GenerateExcelTemplateUtils.TemplateObj> list2 = new ArrayList<>();//二级表头
list2.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("a").setWidth(120).setHeight(20).setColspan(4));
list2.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("b").setWidth(150).setColspan(2));
list2.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("c").setWidth(150).setColspan(4));
title.add(list2);
List<GenerateExcelTemplateUtils.TemplateObj> list3 = new ArrayList<>();//三级表头
list3.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("d").setWidth(120).setHeight(20).setRowspan(2));
list3.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("e").setWidth(150).setRowspan(2));
list3.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("f").setWidth(150));
list3.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("g").setWidth(150));
list3.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("h").setWidth(150));
list3.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("i").setWidth(150));
list3.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("j").setWidth(150));
list3.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("k").setWidth(150));
list3.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("l").setWidth(150));
list3.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("m").setWidth(150));
title.add(list3);
List<GenerateExcelTemplateUtils.TemplateObj> list4 = new ArrayList<>();//四级表头
list4.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("n").setWidth(150).setValidation(true).setValidationList(validation));
list4.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("o").setWidth(180).setValidation(true).setValidationList(validation2));
list4.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("p").setWidth(180));
list4.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("q").setWidth(120));
list4.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("r").setWidth(120));
list4.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("s").setWidth(150));
list4.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("t").setWidth(150));
list4.add(new GenerateExcelTemplateUtils.TemplateObj().setTitle("u").setWidth(180));
title.add(list4);
XSSFWorkbook sheets = GenerateExcelTemplateUtils.complexHeader(JSON.toJSONString(title));
FileOutputStream os = new FileOutputStream("E:\\test.xlsx");
sheets.write(os);
}
}JSON的格式大概是这样子：
[
[{
"colspan": 10,
"height": 20,
"rowspan": 1,
"title": "这是一个一级表头",
"validation": false
}],
[{
"colspan": 4,
"height": 20,
"rowspan": 1,
"title": "a",
"validation": false,
"width": 120
}, {
"colspan": 2,
"rowspan": 1,
"title": "b",
"validation": false,
"width": 150
}, {
"colspan": 4,
"rowspan": 1,
"title": "c",
"validation": false,
"width": 150
}],
[{
"colspan": 1,
"height": 20,
"rowspan": 2,
"title": "d",
"validation": false,
"width": 120
}, {
"colspan": 1,
"rowspan": 2,
"title": "e",
"validation": false,
"width": 150
}, {
"colspan": 1,
"rowspan": 1,
"title": "f",
"validation": false,
"width": 150
}, {
"colspan": 1,
"rowspan": 1,
"title": "g",
"validation": false,
"width": 150
}, {
"colspan": 1,
"rowspan": 1,
"title": "h",
"validation": false,
"width": 150
}, {
"colspan": 1,
"rowspan": 1,
"title": "i",
"validation": false,
"width": 150
}, {
"colspan": 1,
"rowspan": 1,
"title": "j",
"validation": false,
"width": 150
}, {
"colspan": 1,
"rowspan": 1,
"title": "k",
"validation": false,
"width": 150
}, {
"colspan": 1,
"rowspan": 1,
"title": "l",
"validation": false,
"width": 150
}, {
"colspan": 1,
"rowspan": 1,
"title": "m",
"validation": false,
"width": 150
}],
[{
"colspan": 1,
"rowspan": 1,
"title": "n",
"validation": true,
"validationList": ["数据1", "数据2", "数据3", "数据4"],
"width": 150
}, {
"colspan": 1,
"rowspan": 1,
"title": "o",
"validation": true,
"validationList": ["男", "女"],
"width": 180
}, {
"colspan": 1,
"rowspan": 1,
"title": "p",
"validation": false,
"width": 180
}, {
"colspan": 1,
"rowspan": 1,
"title": "q",
"validation": false,
"width": 120
}, {
"colspan": 1,
"rowspan": 1,
"title": "r",
"validation": false,
"width": 120
}, {
"colspan": 1,
"rowspan": 1,
"title": "s",
"validation": false,
"width": 150
}, {
"colspan": 1,
"rowspan": 1,
"title": "t",
"validation": false,
"width": 150
}, {
"colspan": 1,
"rowspan": 1,
"title": "u",
"validation": false,
"width": 180
}]
]这里值得一提的是，JSON转成对象的时候要和内部类的属性名称对应上，不然会用不了。
我就不上代码了，截个图就行。
还有一点的是，要导出有数据的复杂表格的时候 field 要和 类名称绑定
比如：
new GenerateExcelTemplateUtils.TemplateObj().setTitle("姓名").setWidth(150).setField("name");
提一嘴，我导出数据用的是这个方法，是没有问题的
complexHeaderCarryData(String tableJson, String dataJson)至于这个方法，我还没试过，不过理论上应该也没问题，出了bug可能要你自己修复了，不过这个是用反射的，难度不是很大。
complexHeaderCarryData(String tableJson,List<?> data)我还弄了一个导入的工具类，使用环境是再Springboot上面的，大致用法就是一个Controller可以导入多个不同的模板，复杂表头也可以，但我感觉会有一些BUG所以我就没发，感觉有需要的我还能再发一篇文章。
（项目经理的提的神奇需求）
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
