Java dynamically exports Excel multi-level header
//export
public void exportExcel(HttpServletResponse response, List<String> ids) {<!-- -->
ServletOutputStream out = null;
try {<!-- -->
String fileName = "template";
//The first step is to create a workbook corresponding to an Excel file
XSSFWorkbook wb = new XSSFWorkbook();
// a sheet
XSSFSheet sheet = wb.createSheet(fileName);
// initialize style
// this.setStyle(wb);
// focus
this.setHeader(sheet, fileName);
List<?> list = this. salaryMapper. getByIds(ids);
JSONObject salary;
XSSFCell cell;
XSSFRow row;
for (int i = 0; i < list. size(); i ++ ) {<!-- -->
row = sheet. createRow(i + 5);
salary = JSONObject. parseObject(JSON. toJSONString(list. get(i)));
for (Map.Entry<Integer, String> entry : exportFieldMap.entrySet()) {<!-- -->
cell = row.createCell(entry.getKey());
cell.setCellStyle(null);
this.setExcelValue(cell, salary, entry.getValue());
}
index + + ;
}
fileName = URLEncoder.encode(fileName + ".xlsx", StandardCharsets.UTF_8.name());
response.setContentType("application/msword;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
out = response. getOutputStream();
wb.write(out);
out. flush();
} catch (IOException e) {<!-- -->
e.printStackTrace();
} finally {<!-- -->
try {<!-- -->
out. close();
} catch (IOException e) {<!-- -->
e.printStackTrace();
}
}
}
private void setHeader(XSSFSheet sheet, String fileName) {<!-- -->
//Here is the tree structure, you can use ToTreeUtil
List<DynamicList> list = this.dynamicListService.headers("SALARY_TYPE", "SALARY");
// first line title (filename)
this.setCellValue(sheet, fileName, 0, 0);
// second line field name (hidden)
fieldRow = sheet. createRow(1);
exportFieldMap = new HashMap<>();
// The third line header group
int maxLevel = ToTreeUtil. getMaxLevel(list);
int lastCol = this.setCellValue(sheet, list, 2, 0, ++ maxLevel);
this.setCellStyle(sheet, 2, + + maxLevel, lastCol + 1);
//header cell merge
sheet.addMergedRegion(new CellRangeAddress(0, 0, (short) 0, (short) lastCol));
}
private void setExcelValue(XSSFCell cell, JSONObject salary, String fieldName) {<!-- -->
if (fieldName. contains("Date")) {<!-- -->
try {<!-- -->
cell.setCellValue(YYYY_MM.format(salary.getDate(fieldName)));
} catch (Exception e) {<!-- -->
System.out.println(e.getMessage());
}
} else {<!-- -->
String value = salary. get(fieldName) + "";
if (StringUtils.isNotEmpty(value) & amp; & amp; !"null".equals(value)) {<!-- -->
cell.setCellValue(value);
}
}
cell.setCellType(CellType.STRING);
}
private void setCellValue(XSSFSheet sheet, String val, int rowNum, int colNum) {<!-- -->
XSSFCell cell = sheet.createRow(rowNum).createCell(colNum);
cell.setCellStyle(null);
cell.setCellValue(val);
}
private int setCellValue(XSSFSheet sheet, List<DynamicList> list, int rowNum, int colNum, int lastRow) {<!-- -->
if (CollUtil. isEmpty(list)) return 0;
XSSFRow row = sheet. getRow(rowNum);
if (row == null) {<!-- -->
row = sheet. createRow(rowNum);
}
int colIndex = 0;
int columnIndex;
XSSFCell cell = null;
DynamicList dynamicItem;
List<DynamicList> children;
for (int i = colNum; i < list. size() + colNum; i + + ) {<!-- -->
dynamicItem = list. get(i - colNum);
columnIndex = i + colIndex;
this.setFieldValue(dynamicItem, columnIndex);
row.createCell(columnIndex).setCellValue(dynamicItem.getColumnName());
children = dynamicItem. getChildes();
colIndex + = this.setCellValue(sheet, children, rowNum + 1, columnIndex, lastRow);
sheet.setColumnWidth(columnIndex, 12 * 256);
if (CollUtil.isEmpty(childes) & amp; & amp; rowNum < lastRow) {<!-- -->
sheet.addMergedRegion(new CellRangeAddress(rowNum, lastRow, columnIndex, columnIndex));
}
if (columnIndex < i + colIndex) {<!-- -->
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, columnIndex, i + colIndex));
}
}
return list.size() + colIndex - 1;
}
private static XSSFRow fieldRow;
private static Map<Integer, String> exportFieldMap;
//Used to import mapping fields
private void setFieldValue(DynamicList dynamicItem, int columnIndex) {<!-- -->
String fieldName = dynamicItem. getFieldName();
if (StringUtils.isNotEmpty(fieldName)) {<!-- -->
XSSFCell cell = fieldRow.createCell(columnIndex);
cell.setCellStyle(null);
cell.setCellValue(fieldName);
exportFieldMap.putIfAbsent(columnIndex, fieldName);
}
}
//import
public List<SalarySave> importExcel(MultipartFile file) {<!-- -->
XSSFSheet sheet = null;
try {<!-- -->
sheet = new XSSFWorkbook(file. getInputStream()). getSheetAt(0);
} catch (IOException e) {<!-- -->
e.printStackTrace();
}
XSSFRow row;
JSONObject obj;
List<?> list = new LinkedList<>();
Map<Integer, String> fieldMap = this.getFieldMap(sheet.getRow(1));
for (int i = 5; i <= sheet.getLastRowNum(); i ++ ) {<!-- -->
obj = new JSONObject();
row = sheet. getRow(i);
for (Map.Entry<Integer, String> entry : fieldMap.entrySet()) {<!-- -->
try {<!-- -->
this.setObjValue(obj, row, entry.getValue(), entry.getKey());
} catch (Exception e) {<!-- -->
System.out.println(e.getMessage());
}
}
list.add(obj.toJavaObject(Salary.class));
}
this. saveOrUpdateBatch(list);
return null;
}
private Map<Integer, String> getFieldMap(XSSFRow row) {<!-- -->
String cellNum = String. valueOf(row. getLastCellNum());
Map<Integer, String> fieldMap = new HashMap<>();
for (int i = 0; i < Integer. parseInt(cellNum); i ++ ) {<!-- -->
fieldMap.put(i, this.getCellValue(row.getCell(i)));
}
return fieldMap;
}
private void setObjValue(JSONObject obj, XSSFRow row, String fieldName, Integer key) {<!-- -->
String value = this. getCellValue(row. getCell(key));
obj. putIfAbsent(fieldName, value);
}
private String getCellValue(XSSFCell cell) {<!-- -->
try {<!-- -->
cell.setCellType(CellType.STRING);
return cell. getStringCellValue();
} catch (Exception e) {<!-- -->
return null;
}
}
public class ToTreeUtil {<!-- -->
/**
* parseTree(list, "childes", "dictId", "dictParentId", "")
*
* @param list
* @param childes subset container
* @param idField primary key ID field
* @param parentIdField parent ID field
* @param parentId parent ID
* @return
*/
private static JSONArray parseTree(List<?> list, String childrens, String idField, String parentIdField, Object parentId) {<!-- -->
JSONArray obj = new JSONArray();
for (Object object : list) {<!-- -->
JSONObject tempObj = JSONObject. parseObject(JSON. toJSONString(object));
Object uuid = tempObj. get(idField);
Object pid = tempObj. get(parentIdField);
if (parentId.toString().equals(pid.toString())) {<!-- -->
JSONArray trees = parseTree(list, children, idField, parentIdField, uuid);
tempObj. put(childes, trees);
obj. add(tempObj);
}
}
return obj;
}
private static final String CHILDES = "childes";
private static final String ID_FIELD = "id";
private static final String PARENT_ID_FIELD = "parentId";
private static final String DEF_PARENT = "";
public static <T> List<T> parseTree(List<T> list, String childrens, String idField, String parentIdField, Object parentId, Class clazz) {<!-- -->
JSONArray parseTree = parseTree(list, children, idField, parentIdField, parentId);
return JSONObject. parseArray(JSONArray. toJSONString(parseTree), clazz);
}
public static <T> List<T> parseTree(List<T> list, String children, String idField, String parentIdField, Class clazz) {<!-- -->
JSONArray parseTree = parseTree(list, children, idField, parentIdField, DEF_PARENT);
return JSONObject. parseArray(JSONArray. toJSONString(parseTree), clazz);
}
public static <T> List<T> parseTree(List<T> list, String idField, String parentIdField, Class clazz) {<!-- -->
JSONArray parseTree = parseTree(list, CHILDES, idField, parentIdField, DEF_PARENT);
return JSONObject. parseArray(JSONArray. toJSONString(parseTree), clazz);
}
public static <T> List<T> parseTree(List<T> list, String childrens, Object parentId, Class clazz) {<!-- -->
JSONArray parseTree = parseTree(list, children, ID_FIELD, PARENT_ID_FIELD, parentId);
return JSONObject. parseArray(JSONArray. toJSONString(parseTree), clazz);
}
public static <T> List<T> parseTree(List<T> list, Object parentId, Class clazz) {<!-- -->
JSONArray parseTree = parseTree(list, CHILDES, ID_FIELD, PARENT_ID_FIELD, parentId);
return JSONObject. parseArray(JSONArray. toJSONString(parseTree), clazz);
}
public static <T> List<T> parseTree(List<T> list, Class clazz) {<!-- -->
JSONArray parseTree = parseTree(list, CHILDES, ID_FIELD, PARENT_ID_FIELD, DEF_PARENT);
return JSONObject. parseArray(JSONArray. toJSONString(parseTree), clazz);
}
private static int getMaxLevel(List<?> list, String children, int level) {<!-- -->
int max_level = 0;
if (CollUtil.isNotEmpty(list)) {<!-- -->
max_level = level;
for (Object object : list) {<!-- -->
JSONObject tempObj = JSONObject. parseObject(JSON. toJSONString(object));
Object obj = tempObj. get(childes);
max_level = Math.max(getMaxLevel((List<?>) obj, children, level + 1), max_level);
}
}
return max_level;
}
public static int getMaxLevel(List<?> list, String childrens) {<!-- -->
return getMaxLevel(list, children, 1);
}
public static int getMaxLevel(List<?> list) {<!-- -->
return getMaxLevel(list, CHILDES, 1);
}
}
public class DynamicList implements Serializable {<!-- -->
@TableId(value = "id", type = IdType.ASSIGN_UUID)
private String id;
/**
* parent class ID
*/
@TableField(value = "parent_id")
private String parentId;
/**
* type
*/
@TableField(value = "type")
private String type;
/**
* column name
*/
@TableField(value = "column_name")
private String columnName;
/**
* field
*/
@TableField(value = "field_name")
private String fieldName;
@TableField(exist = false)
private List<DynamicList> children;
}