第一个方法是案例
上下左右合并表头传值规则: 相同位置相同值就合并
/**
* 生成并下载EXCEL测试案例
*
* @param response 网络请求
* @return excel下载流
*/
public ApiResult downloadExcelTest(HttpServletResponse response) throws Exception {
// 1.创建 Workbook
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet1 = workbook.createSheet("表1");
//设置样式
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 14);
font.setBold(true);
style.setFont(font);
String[] headsOne = {"姓名", "完成", "分数", "取消", "爽约", "请假", "紧急", "紧急"};
String[] headsTwo = {"姓名", "完成1", "分诊1", "取消", "爽约", "请假", "危77", "888888"};
String[] bodyOne = {"144", "442", "34444", "444", "555", "666", "7777777", "888878"};
List<String[]> tableAll = new ArrayList<>();
tableAll.add(headsOne);
tableAll.add(headsTwo);
tableAll.add(bodyOne);
//制作 sheet
opeOneSheet(sheet1, tableAll, 2, style, style);
//相应下载
String fileName = URLEncoder.encode("导出.xlsx", "UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("content-disposition", "attachment;filename=" + new
String(fileName.getBytes("ISO8859-1")));
response.setHeader("filename", fileName);
workbook.write(response.getOutputStream());
workbook.close();
return ApiResult.success();
}
/**
* 制作一个sheet SXSSFSheet 方式 SXSSFWorkbook wb = new SXSSFWorkbook(100); SXSSF -> 一百行持久化一次,解决了内存溢出的问题
* <p>
* 网络中封装好的工具集有:
* 1.easypoi ExcelBatchExportServer
* 2.hutool BigExcelWriter
* 原理都是强制使用 xssf 版本的Excel。
* <p>
* !!!!!!本方法需要合并表头或者表体的时候才使用 ,其他情况请使用 -> new BigExcelWriter().writer()
* <p>
* 表头或者表主体横向竖向自动合并
*
* @param sheet 一个sheet页
* @param tableAll 整sheet表数据包括展示数据和表头字段
* @param headerLength 头部占用tableAll数组长度 例: 1 那么就会认为 tableAll.get(0) 整体为表头,并且横向发现有两次或三次重复那么将对相同的进行合并,竖向若有相同将对相同的进行合并
* @param headerStyle 头部样式
* @param bodyStyle 身体样式
* @param rangeBoo true 表头和表体全部都需要合并 false 只有表头需要合并
* @return true 成功 ;false失败
* @author 肾齐团队 https://blog.csdn.net/qq_39168427/article/details/110560637?spm=1001.2014.3001.5501
*/
public boolean opeOneSXSSFSheet(SXSSFSheet sheet, List<String[]> tableAll, Integer headerLength, CellStyle headerStyle, CellStyle bodyStyle, boolean rangeBoo) {
System.err.println("处理sheet开始:" + System.currentTimeMillis());
if (headerLength == null || headerLength <= 0) {
if (1 > tableAll.size()) {
return false;
}
headerLength = 1;
}
for (int i = 0; i < tableAll.size(); i++) {
SXSSFRow row = sheet.createRow(i);
String[] headerStrings = tableAll.get(i);
if (rangeBoo || i < headerLength) {
String previousOne = "";
Integer colStart = null;
Integer colEnd = null;
for (int j = 0; j < headerStrings.length; j++) {
String headerColOne = headerStrings[j];
SXSSFCell cell = row.createCell(j);
cell.setCellValue(headerColOne);
cell.setCellStyle(i < headerLength ? headerStyle : bodyStyle);
if (!StringUtils.isEmpty(headerColOne)) {
if (previousOne.equals(headerColOne)) {
if (colStart == null) {
colStart = j - 1;
}
colEnd = j;
if (j == headerStrings.length - 1) {
sheet.addMergedRegion(new CellRangeAddress(i, i, colStart, colEnd));
}
} else if (colStart != null) {
sheet.addMergedRegion(new CellRangeAddress(i, i, colStart, colEnd));
colStart = null;
} else if (i - 1 >= 0 && headerColOne.equals(tableAll.get(i - 1)[j])) {
sheet.addMergedRegion(new CellRangeAddress(i - 1, i, j, j));
}
}
previousOne = headerColOne == null ? "" : headerColOne;
}
} else {
for (int j = 0; j < headerStrings.length; j++) {
SXSSFCell cell = row.createCell(j);
cell.setCellValue(headerStrings[j]);
cell.setCellStyle(bodyStyle);
}
}
}
System.err.println("处理sheet结束:" + System.currentTimeMillis());
return true;
}