概要
使用POI导出Excel,需合并单元格
预期
一、简单合并单元格
二、tree结构合并单元格
代码
/**
* @ClassName MergeModel
* @Description 用于保存需要合并的单元格
* @Author
* @Create 2023/6/1 14:55
* @Modifier
* @ModifyTime
*/
public class MergeModel {
private String content;//内容
private int rowIndex;//记录相同内容的开始行号
private int cellIndex;//列号
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public int getRowIndex() {
return rowIndex;
}
public void setRowIndex(int rowIndex) {
this.rowIndex = rowIndex;
}
public int getCellIndex() {
return cellIndex;
}
public void setCellIndex(int cellIndex) {
this.cellIndex = cellIndex;
}
}
工具类:
/**
* FileName: ExcelMergeUtil
* Author: Administrator
* Date: 2023/6/1 14:49
* Description: 复杂excel导出工具类
* History:
* <author> <time> <version> <desc>
* 作者姓名 修改时间 版本号 描述
*/
import com.fasterxml.jackson.annotation.JsonFormat;
import com.gwos.common.utils.ExceptionUtil;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.*;
import java.util.regex.Pattern;
public class ExcelMergeUtil {
/**
* 创建excel文件
*
* @param objData 数据
* @param fileName 文件名
* @param sheetName sheet名
* @param columns 表头
* @param mergeIndex 需要合并的列号集合
* @return
*/
public static int exportToExcelForXlsx(List<List<Object>> objData, String fileName, String sheetName, List<String> columns, List<Integer> mergeIndex, boolean isTree, HttpServletRequest request, HttpServletResponse response) {
int flag = 0;
Collections.sort(mergeIndex);//将列号排序
// 创建工作薄
XSSFWorkbook wb = new XSSFWorkbook();
// sheet1
XSSFSheet sheet1 = wb.createSheet(sheetName);
//设置样式
XSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 设置水平对齐方式为居中对齐
style.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中对齐
//标题头
sheet1.createFreezePane(0, 1);//冻结表头
XSSFRow sheet1row1 = sheet1.createRow((short) 0);
sheet1row1.setHeight((short) 480);
XSSFCell title = sheet1row1.createCell(0);
title.setCellValue("设备***数据统计"); // 设置第一个单元格的内容
XSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平对齐方式为居中对齐
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中对齐
XSSFFont cellFont = wb.createFont();
cellFont.setFontHeightInPoints((short)18);
cellFont.setBold(true);
titleStyle.setFont(cellFont);
title.setCellStyle(titleStyle);
// 合并 A1 到 F1 这 6 个单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 6);
sheet1.addMergedRegion(region);
//表头
sheet1.createFreezePane(0, 1);//冻结表头
XSSFRow sheet1row2 = sheet1.createRow((short) 1);
sheet1row1.setHeight((short) 480);
//写入表头
if (columns != null && columns.size() > 0) {
for (int i = 0; i < columns.size(); i++) {
String column = columns.get(i);
//列
XSSFCell cell2 = sheet1row2.createCell(i);
cell2.setCellValue(column);
cell2.setCellStyle(style);
}
}
int dataSatrtIndex = 1;//数据开始行
boolean isMerge = false;
if (mergeIndex != null && mergeIndex.size() != 0) {
isMerge = true;
}
//写入数据
if (objData != null && objData.size() > 0) {
Map<Integer, MergeModel> poiModels = new HashMap<Integer, MergeModel>();
//循环写入表中数据
int i = 0;
for (; i < objData.size(); i++) {
//数据行
XSSFRow row = sheet1.createRow((short) (i + dataSatrtIndex));
//行内循环,既单元格(列)
List<Object> list = objData.get(i);
DecimalFormat decimalFormat = new DecimalFormat("0.00");
int j = 0;
for (Object o : list) {
//数据列
String content = "";
if (o != null) {
if (o.toString().contains(".") && isNumeric(o.toString())) {
content = decimalFormat.format(Float.valueOf(o.toString()));
} else if (o.toString().contains("-") && o.toString().contains(":")) {
content = String.valueOf(o).split("\\.")[0];
} else {
content = String.valueOf(o);
}
}
if (isMerge && mergeIndex.contains(j)) {
//如果该列需要合并
MergeModel poiModel = poiModels.get(j);
if (poiModel == null) {
poiModel = new MergeModel();
poiModel.setContent(content);
poiModel.setRowIndex(i + dataSatrtIndex);
poiModel.setCellIndex(j);
poiModels.put(j, poiModel);
} else {
if (!poiModel.getContent().equals(content)) {
//如果不同了,则将前面的数据合并写入
if (isTree) {
//此列向后的所有列都进行一次写入合并操作,并清空。
//树结构中存在这种情况,a目录和b目录为同级目录,a目录下最后一个子目录和b目录下的第一个子目录名称相同,防止本来不应该合并的单元格被合并
addMergedRegionValue(sheet1, poiModels, mergeIndex, i + dataSatrtIndex, poiModel.getCellIndex());
} else {
XSSFRow lastRow = sheet1.getRow(poiModel.getRowIndex());
XSSFCell lastCell = lastRow.createCell(poiModel.getCellIndex());//创建列
lastCell.setCellValue(poiModel.getContent());
lastCell.setCellStyle(style);
//合并单元格
if (poiModel.getRowIndex() != i + dataSatrtIndex - 1) {
sheet1.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), i + dataSatrtIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex()));
}
}
//将新数据存入
poiModel.setContent(content);
poiModel.setRowIndex(i + dataSatrtIndex);
poiModel.setCellIndex(j);
poiModels.put(j, poiModel);
}
}
row.createCell(j);//创建单元格
} else {//该列不需要合并
//数据列
XSSFCell cell = row.createCell(j);
cell.setCellValue(content);
cell.setCellStyle(style);
}
j++;
}
}
//将最后一份存入
if (poiModels != null && poiModels.size() != 0) {
for (Integer key : poiModels.keySet()) {
MergeModel poiModel = poiModels.get(key);
XSSFRow lastRow = sheet1.getRow(poiModel.getRowIndex());
XSSFCell lastCell = lastRow.getCell(poiModel.getCellIndex());
lastCell.setCellValue(poiModel.getContent());
lastCell.setCellStyle(style);
//合并单元格
if (poiModel.getRowIndex() != i + dataSatrtIndex - 1) {
sheet1.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), i + dataSatrtIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex()));
}
}
}
} else {
flag = -1;
}
//设置固定列宽,poi的列宽设置有点操蛋,大概规律网上有不少版本自行百度
//这里大概是143像素
for (int i = 0; i < columns.size(); i++) {
sheet1.setColumnWidth(i, 4550);
}
OutputStream os = null;
try {
// 创建一个普通输出流
os = response.getOutputStream();
fileName = "file.xls";
// 请求浏览器打开下载窗口
response.reset();
response.setCharacterEncoding("UTF-8");
// Content-disposition 告诉浏览器以下载的形式打开
// String header = request.getHeader("User-Agent").toUpperCase();
// if (header.contains("MSIE") || header.contains("TRIDENT") || header.contains("EDGE")) {
// fileName = URLEncoder.encode(fileName, "utf-8");
// fileName = fileName.replace("+", "%20"); // IE下载文件名空格变+号问题
// } else {
// fileName = new String(fileName.getBytes(), "ISO8859-1");
// }
fileName = new String(fileName.getBytes(), "ISO8859-1");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);// 要保存的文件名
response.setContentType("application/octet-stream");
// 直接用数组缓冲输出流输出
wb.write(os);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
wb.close();
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/* 本地下载方式
FileOutputStream out = null;
try {
out = new FileOutputStream("E:\\" + fileName + ".xlsx");
wb.write(out);
} catch (Exception ex) {
try {
out.flush();
out.close();
}catch (IOException e){
flag = 0;
e.printStackTrace();
}
}*/
return flag;
}
/**
* 判断是不是数字
*
* @param str
* @return
*/
private static boolean isNumeric(String str) {
if (str == null || str.length() == 0) {
return false;
}
Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
return pattern.matcher(str).matches();
}
private static void addMergedRegionValue(XSSFSheet sheet, Map<Integer, MergeModel> poiModels, List<Integer> mergeIndex, int nowRowIndex, int nowCellIndex) {
if (poiModels != null && poiModels.size() != 0 && mergeIndex != null && mergeIndex.size() != 0) {
for (Integer index : mergeIndex) {
if (index >= nowCellIndex) {
MergeModel poiModel = poiModels.remove(index);//删除并获取value
if (poiModel != null) {
XSSFRow lastRow = sheet.getRow(poiModel.getRowIndex());
XSSFCell lastCell = lastRow.createCell(poiModel.getCellIndex());//创建列
lastCell.setCellValue(poiModel.getContent());
//合并单元格
if (poiModel.getRowIndex() != nowRowIndex - 1) {
sheet.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), nowRowIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex()));
}
}
}
}
}
}
}
调用层:
@GetMapping("/export")
public void exportDeviceMeasureValues( HttpServletRequest request, HttpServletResponse response, @ApiParam(value = "项目编号")
@RequestParam String projectNo,
@ApiParam(value = "项目名称")
@RequestParam String projectName,
@ApiParam(value = "日期,格式:yyyy-MM-dd")
@RequestParam String inspectDate,
@ApiParam(value = "设备类别")
@RequestParam String deviceCategory) {
try {
TowerMonitorVO monitorValues = monitorService.getDeviceMeasureValues(projectNo, formatter.parse(inspectDate),deviceCategory);
monitorService.exportDeviceMeasureValues(projectName,monitorValues,deviceCategory,request,response);
} catch (Exception e) {
LOG.error("", e);
}
}
tree结构导出
public static void main(String[] args) {
List<String> columns = new ArrayList<>();//标头
columns.add("目录");
columns.add("目录");
columns.add("目录");
columns.add("内容");
String fileName = "文件名字";//文件名字
String sheetName = "sheet名字";//sheet名字
//内容数据
List<List<Object>> exportData = new ArrayList<>();
//行内的数据
List<Object> rowData = new ArrayList<Object>();
rowData.add("一级目录1");
rowData.add("二级目录1");
rowData.add("三级目录1");
rowData.add("内容1");
exportData.add(rowData);
List<Object> rowData2 = new ArrayList<Object>();
rowData2.add("一级目录1");
rowData2.add("二级目录1");
rowData2.add("三级目录1");
rowData2.add("内容2");
exportData.add(rowData2);
List<Object> rowData3 = new ArrayList<Object>();
rowData3.add("一级目录1");
rowData3.add("二级目录1");
rowData3.add("三级目录2");
rowData3.add("内容3");
exportData.add(rowData3);
List<Object> rowData4 = new ArrayList<Object>();
rowData4.add("一级目录1");
rowData4.add("二级目录1");
rowData4.add("三级目录2");
rowData4.add("内容4");
exportData.add(rowData4);
List<Object> rowData5 = new ArrayList<Object>();
rowData5.add("一级目录1");
rowData5.add("二级目录2");
rowData5.add("三级目录3");
rowData5.add("内容5");
exportData.add(rowData5);
List<Object> rowData6 = new ArrayList<Object>();
rowData6.add("一级目录1");
rowData6.add("二级目录2");
rowData6.add("三级目录3");
rowData6.add("内容6");
exportData.add(rowData6);
//需要合并的列号
List<Integer> mergeIndex = new ArrayList<Integer>();
mergeIndex.add(0);
mergeIndex.add(1);
mergeIndex.add(2);
int flag = ExcelMergeUtil.exportToExcelForXlsx(exportData, fileName, sheetName, columns, mergeIndex, true, null, null);
System.out.println(flag);
}
小结
引用原文:
https://www.cnblogs.com/wdk2020/p/13204162.html