场景
要求每行数据的每个字段的内容不能以 [2,3,33,22] 形式展示 要求独立成列形式如下
代码
maven 依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.17</version>
</dependency>主代码类
package org.fri.controller;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.fri.entity.ExportExample;
import org.fri.entity.MergeVo;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
@RequestMapping(value = "/excel")
@RestController
@Slf4j
public class ExcelController {
@RequestMapping(value = "/export")
public void exportExcel(HttpServletResponse response) throws NoSuchFieldException, IllegalAccessException, InstantiationException {
ExportExample q = new ExportExample("漩涡鸣人", 20, "男", "火影,吊车尾,后宫之术创始人", "隐分身,仙术,螺旋丸");
ExportExample y = new ExportExample("宇智波 佐助", 27, "男", "宇智波一族", "装B");
ExportExample f = new ExportExample("千手扉间", 100, "男", "火影,禁术编写者", "水遁,黑暗术");
LinkedHashMap<String, String> title = new LinkedHashMap<>();
title.put("name","名称");
title.put("age","年龄");
title.put("sex","性别");
title.put("roles","角色");
title.put("technique","技能");
List<ExportExample> list = Arrays.asList(q, y, f);
exportExcel(list, ExportExample.class,title, response);
}
public <T> void exportExcel(List<T> list, Class<T> T,LinkedHashMap<String, String> title,HttpServletResponse response) throws NoSuchFieldException, IllegalAccessException, InstantiationException {
T t = T.newInstance();
LinkedHashMap<String, MergeVo> merged = new LinkedHashMap<>();
Field[] fields = t.getClass().getDeclaredFields();
//查询出所有列值
for (Field field : fields) {
field.setAccessible(true);
merged.put(field.getName(), new MergeVo(field.getName(), 0, 1));
}
//计算所需要合并列的最大项
for (T s : list) {
for (String key : merged.keySet()) {
Field field = t.getClass().getDeclaredField(key);
field.setAccessible(true);
String value = field.get(s).toString();
if (merged.get(key).getTitleMaxColumNumber() < value.split(",").length) {
merged.get(key).setTitleMaxColumNumber(value.split(",").length);
}
}
}
//计算没列的开始位置
int j = 0;
for (Map.Entry<String, MergeVo> et : merged.entrySet()) {
et.getValue().setStartRowIndex(j);
j = j + et.getValue().getTitleMaxColumNumber();
}
HSSFWorkbook workBook = null;
OutputStream out = null;
try {
workBook = new HSSFWorkbook();
HSSFSheet sheet = workBook.createSheet();
HSSFRow titleRows = sheet.createRow(0);
/*//设置表头样式
CellStyle colorStyle = workBook.createCellStyle();
colorStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
colorStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font font = workBook.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 16);
// 设置字体大小为 16*/
// 构建表头
merged.forEach((key1, value) -> {
merged(merged, sheet, titleRows, key1, title.get(key1));
});
// 构建数据
int rowNumbers = 0;
for (T e : list) {
rowNumbers++;
HSSFRow nextRow = sheet.createRow(rowNumbers);
for (String key1 : merged.keySet()) {
Field field = t.getClass().getDeclaredField(key1);
field.setAccessible(true);
String value = field.get(e).toString();
merged(merged, sheet, nextRow, key1, value);
}
}
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
String dateNow = formatter.format(LocalDateTime.now());
String fileName = dateNow + ".xls";
response.setCharacterEncoding("UTF-8");
response.setContentType("application/x-msdownload");
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
out = response.getOutputStream();
workBook.write(out);
} catch (IOException e) {
log.error(e.getMessage());
} finally {
try {
if (out != null) {
out.close();
}
if (workBook != null) {
workBook.close();
}
} catch (IOException e) {
log.error(e.getMessage());
}
}
}
/**
* 合并规则设定
*
* @param mergeVoMap 表头配置信息
* @param sheet 所属sheet
* @param row 所属行
* @param key 所属key
* @param value 值
*/
private void merged(LinkedHashMap<String, MergeVo> mergeVoMap, HSSFSheet sheet, HSSFRow row, String
key, String value) {
MergeVo mergeVo = mergeVoMap.get(key);
String[] split = value.replace("[", "").replace("]", "").split(",");
if (mergeVo.getTitleMaxColumNumber() > 1) {
if (split.length > 1) {
int length = split.length;
List<int[]> ints = splitIntoSegments(mergeVo.getStartRowIndex(), mergeVo.getStartRowIndex() + mergeVo.getTitleMaxColumNumber(), length);
for (int j = 0; j < ints.size(); j++) {
int[] anInt = ints.get(j);
row.createCell(anInt[0]).setCellValue(split[j]);
if (anInt[1] - anInt[0] > 0) {
sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), anInt[0], anInt[1]));
}
}
} else {
row.createCell(mergeVo.getStartRowIndex()).setCellValue(value);
sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), mergeVo.getStartRowIndex(), mergeVo.getStartRowIndex() + mergeVo.getTitleMaxColumNumber() - 1));
}
} else {
row.createCell(mergeVo.getStartRowIndex()).setCellValue(value);
}
}
/**
* 列合并分割分配
*
* @param s
* @param e
* @param x
* @return
*/
public static List<int[]> splitIntoSegments(int s, int e, int x) {
int i = e - s;
List<int[]> segments = new ArrayList<>();
int baseLength = i / x; // 每段的基本长度
int remainder = i % x; // 剩余的数值
int start = 0; // 起始值
for (int k = 1; k <= x; k++) {
int length = baseLength; // 当前段的长度
if (k <= remainder) {
length += 1; // 分配剩余的数值
}
int end = start + length - 1; // 结束值
segments.add(new int[]{start + s, end + s}); // 添加到结果列表
start = end + 1; // 更新下一段的起始值
}
return segments;
}
}
合并项辅助类
package org.fri.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class MergeVo {
private String key;
private int startRowIndex;
private int titleMaxColumNumber;
}
导出测试类
package org.fri.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @author Lenovo
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExportExample {
private String name;
private Integer age;
private String sex;
private String roles;
private String technique;
}
