一、前言
公司框架中，生成Execl表格的工具类已经存在，但是后面有一个需求，生成的Execl需要有合并行单元格，现有的不通用，所以只能写一个方法到工具类中，以前没有接触过，折腾了一天多，总算弄好，记录下来。
效果图：
引入的jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.11-beta2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.11-beta2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.11-beta2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.11-beta2</version>
</dependency>
这里遇到一个问题：我直接引入的poi包，结果出现的是一个common包？？？莫名其妙。。不解
代码块：
public static XSSFWorkbook exportMargeExecl(List<Execl> execls) {
logger.info("Xiaoshishu >>> 开始导出execl，版本为2007版本，后缀为xlsx。。。");
long startTime = System.currentTimeMillis();
// 定义一个工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建标题样式
XSSFCellStyle cellTitleStyle = workbook.createCellStyle();
// 垂直居中
cellTitleStyle.setAlignment(HorizontalAlignment.CENTER);
cellTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellTitleStyle.setBorderLeft(BorderStyle.THIN);
// 标题字体
XSSFFont fontTitle = workbook.createFont();
fontTitle.setFontName("宋体");
// 粗体显示
fontTitle.setBold(true);
fontTitle.setFontHeightInPoints((short) 20); //字体大小
cellTitleStyle.setFont(fontTitle);
// 创建副标题样式
XSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(new XSSFColor(Color.WHITE));
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 生成字体
XSSFFont font = workbook.createFont();
// font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
font.setFontName("宋体");
// font.setColor(new XSSFColor(Color.BLACK));
font.setFontHeightInPoints((short) 11);
// 把字体应用到当前的样式
style.setFont(font);
// 标题行的样式
XSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(new XSSFColor(java.awt.Color.WHITE));
style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style2.setBorderBottom(BorderStyle.THIN);
style2.setBorderLeft(BorderStyle.THIN);
style2.setBorderRight(BorderStyle.THIN);
style2.setBorderTop(BorderStyle.THIN);
style2.setAlignment(HorizontalAlignment.CENTER);
style2.setVerticalAlignment(VerticalAlignment.CENTER);
// 生成另一个字体
XSSFFont font2 = workbook.createFont();
// font2.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
int size = execls.size();
for (int s = 0; s < size; s++) {
Execl execl = execls.get(s);
String sheetName = execl.getSheetName();
if (StringUtils.isBlank(sheetName)) {
sheetName = "sheet" + (s + 1);
}
// 定义一个表格
XSSFSheet sheet = workbook.createSheet(sheetName);
// 如果这行没有了，整个公式都不会有自动计算的效果的
sheet.setForceFormulaRecalculation(true);
// 设置表格宽度
sheet.setDefaultColumnWidth(20);
int index = 0;
String title = execl.getTitle();
boolean isTitle = StringUtils.isNotBlank(title);
if (isTitle) {
// 产生表格标题行
XSSFRow row = sheet.createRow(index++);
XSSFCell row0cell0 = row.createCell(0, CellType.STRING);
row0cell0.setCellValue(title);
row0cell0.setCellStyle(cellTitleStyle);
boolean isMap = execl.isMapContent();
// 合并表格标题
if (isMap) {
// 合并，参数说明：1：开始行 2：结束行 3：开始列 4：结束列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, execl.getColumnMapTitles().length - 1));
}
}
logger.info("Xiaoshishu >>> 完成标题创建[{}]", isTitle ? title : null);
// 副标题需要合并为几行
Integer[] rowMargeSize = execl.getRowMargeSize();
// 合并的副标题值
String[] subTitles = execl.getSubTitle();
XSSFRow row1 = sheet.createRow(index++);
XSSFCell cellHeader = null;
int rowSizeIndex = 0;
for (int i = 0; i < subTitles.length; i++) {
cellHeader = row1.createCell(rowSizeIndex);
XSSFCellStyle titleStyle = workbook.createCellStyle();
// 垂直居中
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setWrapText(true);
titleStyle.setBorderBottom(BorderStyle.THIN);//下边框
titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
titleStyle.setBorderTop(BorderStyle.THIN);//上边框
titleStyle.setBorderRight(BorderStyle.THIN);//右边框
XSSFFont font1 = workbook.createFont();
font1.setFontName("宋体");
//字体大小
font1.setFontHeightInPoints((short) 13);
//选择需要用到的字体格式
titleStyle.setFont(font1);
cellHeader.setCellStyle(titleStyle);
cellHeader.setCellType(CellType.STRING);
cellHeader.setCellStyle(titleStyle);
cellHeader.setCellValue(new XSSFRichTextString(subTitles[i]));
rowSizeIndex = rowMargeSize[i];
}
// 合并副标题单元格，并且设置边框线
int sss = 0;
List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();
CellRangeAddress titleRow1 = null;
for (int ss = 0; ss < rowMargeSize.length; ss++) {
if (isTitle) {
titleRow1 = new CellRangeAddress(1, 2, sss, rowMargeSize[ss] - 1);
}else {
titleRow1 = new CellRangeAddress(0, 1, sss, rowMargeSize[ss] - 1);
}
sheet.addMergedRegion(titleRow1);
cellRangeAddressList.add(titleRow1);
sss = rowMargeSize[ss];
}
// 合并单元格之后，有些边框线没有设置完整，这里是补全边框线
for (CellRangeAddress cellRangeAddress : cellRangeAddressList) {
RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
}
logger.info("Xiaoshishu >>> 完成副标题的创建并且合并单元格。。。");
// 添加子标题
XSSFRow row2 = sheet.createRow(++index);
String[] childCells = execl.getColumnMapTitles();
XSSFCell childCell;
for (int i = 0; i < childCells.length; i++) {
childCell = row2.createCell(i);
childCell.setCellStyle(style);
childCell.setCellValue(new XSSFRichTextString(childCells[i]));
}
logger.info("Xiaoshishu >>> 完成子标题的创建。。。");
// 填充数据行
List<?> list = execl.getList();
if (list != null && list.size() > 0) {
String[] keys = execl.getColumnMapKeys();
for (int j = 0; j < list.size(); j++) {
Map<String,Object> map = (Map<String, Object>) list.get(j);
// 创建一行
XSSFRow row3 = sheet.createRow(++index);
for (int jj = 0; jj < keys.length; jj++) {
XSSFCell cell3 = row3.createCell(jj, CellType.STRING);
cell3.setCellValue(String.valueOf(map.getOrDefault(keys[jj], "")));
cell3.setCellStyle(style2);
}
}
}
logger.info("Xiaoshishu >>> 完成数据行的填充。。。");
}
long endTime = System.currentTimeMillis();
logger.info("Xiaoshishu >>> 结束导出execl-xlsx，本次耗时{}毫秒", endTime - startTime);
return workbook;
}Main方法块执行
public void static Main(String[] args){
List<Execl> execls = new ArrayList<Execl>();
for (int j = 0; j < 1; j++) {
Execl execl = new Execl();
execl.setSheetName("文档名称");
execl.setTitle("标题");
String[] subTitle = {"合并副标题1","合并副标题2"};
// ：如需要合并两行，第一行是2个单元格，第二行是3个单元格。则参数为{2,5};同样，第三行如果也是3个单元格，则是{2,5,8}
Integer[] rowMargeSize = {2,5};
execl.setRowMargeSize(rowMargeSize);
execl.setSubTitle(subTitle);
execl.setColumnMapTitles("id","名字","年龄","备注","备注1");
execl.setColumnMapKeys("id","name","age","note","note1");
execl.setMapContent(true);
List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
for (int i = 0; i < 10; i++) {
Map<String, Object> execlTest = new HashMap<String, Object>();
execlTest.put("id", "id"+i);
execlTest.put("age", i);
execlTest.put("name", "name"+i);
execlTest.put("note", "备注"+i);
execlTest.put("note1", "备注1"+i);
data.add(execlTest);
}
execl.setList(data);
execls.add(execl);
}
String filePath = "D:/Xiaoshishu.xlsx";
XSSFWorkbook workbook = ExeclUtil.exportMargeExecl(execls);
ExeclUtil.exportToFile(workbook, filePath);
System.out.println("导出成功");
}exportToFile导出为文件
public static void exportToFile(XSSFWorkbook workbook, String filePath){
// workbook
FileOutputStream fileOutputStream = null;
try {
// workbook 2 FileOutputStream
fileOutputStream = new FileOutputStream(filePath);
workbook.write(fileOutputStream);
// flush
fileOutputStream.flush();
} catch (Exception e) {
logger.error("Xiaoshishu >>> 导出Excel错误，msg=[{}]", e.getMessage(), e);
throw new RuntimeException(e);
} finally {
try {
if (workbook != null) {
workbook.close();
}
} catch (IOException e1) {
e1.printStackTrace();
}
try {
if (fileOutputStream!=null) {
fileOutputStream.close();
}
} catch (Exception e) {
}
}
}参考网址：
https://blog.csdn.net/datangxiajun/article/details/78308979（java导出Excel合并单元格）
https://blog.csdn.net/l1028386804/article/details/79659605（Java之——导出Excel通用工具类）
https://blog.csdn.net/qq_24035541/article/details/81541406（java XSSFWorkbook 导出Excel）
总结：
1403
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
