SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
List<TestVO> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
TestVO testVO = new TestVO("张三" + i, "123456", format.format(new Date()), format.format(new Date()));
list.add(testVO);
}
String json = "[[{\"title\":\"姓名\",\"field\":\"name\",\"width\":170,\"height\":30},{\"title\":\"密码\",\"field\":\"pwd\",\"width\":170},{\"title\":\"开始时间\",\"field\":\"startTime\",\"width\":170},{\"title\":\"截止时间\",\"field\":\"endTime\",\"width\":170}]]";
XSSFWorkbook wb = null;
try {
wb = GenerateExcelTemplateUtils.complexHeaderCarryData(json, list);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((DateUtil.getDate() + "活动信息.xlsx").getBytes(), StandardCharsets.ISO_8859_1));
wb.write(response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
}
相关依赖
<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>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.31</version>
</dependency>
工具类
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
/**
* 版本 1.0
* 快速使用： GenerateExcelTemplateUtils.complexHeaderCarryData(String json, List dataList);
* 只生成复杂表头：GenerateExcelTemplateUtils.complexHeader(String Json)
* json格式类似 Layui表格 的格式
*/
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 XSSFWorkbook 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); //垂直居中
style.setBorderBottom(BorderStyle.THIN); //下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
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;
}
for (int y = list.size(); y < 20 - list.size(); y++) {
XSSFRow excelRow = sheet.createRow(y);
for (int x = 0; x < maxColspan; x++) {
XSSFCell cell = excelRow.createCell(x);
cell.setCellStyle(style);
}
}
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); //垂直居中
style.setBorderBottom(BorderStyle.THIN); //下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
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;
private Integer align = 1;
private Integer fontSize = 10;
//数据有效性 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;
}
public Integer getFontSize() {
return fontSize;
}
public TemplateObj setFontSize(Integer fontSize) {
this.fontSize = fontSize;
return this;
}
public Integer getAlign() {
return align;
}
/**
* 默认 1 居中对齐
* 0；左对齐 1 居中对齐 2：右对齐
* @param align
* @return
*/
public TemplateObj setAlign(Integer align) {
this.align = align;
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();
}
/**
* 合并的单元格添加边框
* @param sheet
*/
public static void setStyle(XSSFSheet sheet) {
List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
for (CellRangeAddress cellRangeAddress : mergedRegions) {
RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet); // 下边框
RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet); // 左边框
RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet); // 有边框
RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet); // 上边框
}
}
public static XSSFWorkbook complexHeaderCarryData(String tableJson,List<?> data) throws Exception {
Map<String, Object> map = complexHeaderCarryData(tableJson);
XSSFWorkbook workbook = (XSSFWorkbook) map.get("workbook");
XSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); //水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
style.setBorderBottom(BorderStyle.THIN); //下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
Map<String, Integer> index = (Map<String, Integer>) map.get("index");
XSSFSheet sheet = workbook.getSheet(SHEET_NAME);
int rowLength = sheet.getLastRowNum() + 1; //行数
int styleRowLength = rowLength; //行数
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()));
Object v = field.get(obj);
cell.setCellValue(null != v ? v.toString() : "");
// cell.setCellStyle(style);
}
}
rowLength++;
}
for (int y = 0; y < data.size(); y++) {
XSSFRow excelRow = sheet.getRow(styleRowLength);
if (null == excelRow) {
excelRow = sheet.createRow(styleRowLength);
}
excelRow.setHeight((short) (15 * HEIGHT));
for (int x = 0; x < index.size(); x++) {
XSSFCell cell = excelRow.getCell(x);
if (null == cell) {
cell = excelRow.createCell(x);
}
cell.setCellStyle(style);
}
styleRowLength++;
}
setStyle(sheet);
return workbook;
}
}
