java使用poi生成Excel文件并合并单元格
业务需要根据 分管部门 字段进行合并，现在提供一种思路。
controller层
@Inject(target = "/infoResourcesManageRest/custom/batchDetailExcelExport", type = InjectTypeExt.CUSTOM_URL)
public WSResult<?> batchDetailExcelExport(JSONObject jsonObject) throws FileNotFoundException, UnsupportedEncodingException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {
String savePath = downloadBasePath;
String fileName = UUID.randomUUID() + "人才公寓情况表.xls";
SearchFilter filter = SearchFilter.newSearchFilter(jsonObject);
filter.setSortField("rcgyshjs").setSortDir("asc");
List<IdEntity> list = FormDataManagerUtils.findAllByFilter("personApartmentApply", filter);
String outPath = savePath + fileName;
OutputStream os = null;
File file = new File(savePath);
if (!file.exists()) {
file.mkdirs();
}
os = new FileOutputStream(outPath);
String title = "人才公寓情况表";
int sheetNum = 1;// 工作薄sheet编号
int bodyRowCount = 2;// 正文内容行号
int currentRowCount = 1;// 当前的行号
int perPageNum = 50000;// 每个工作薄显示50000条数据
String filename = new String(("人才公寓情况表.xls").getBytes("utf-8"), "ISO-8859-1");
os = new FileOutputStream(outPath);// 输出流
HSSFWorkbook workbook = new HSSFWorkbook();// 创建excel
HSSFSheet sheet = workbook.createSheet(title + sheetNum);// 创建一个工作薄
ExportExcel exportExcel = new ExportExcel();
ExportExcel.setBatchDetailSheetColumn(sheet);// 设置工作薄列宽
HSSFRow row = null;// 创建一行
HSSFCell cell = null;// 每个单元格
HSSFCellStyle titleCellStyle = ExportExcel.createTitleCellStyle(workbook);
ExportExcel.batchDetail(sheet, titleCellStyle, workbook);// 写入标题
// 第二行开始写入数据
HSSFCellStyle bodyCellStyle = ExportExcel.createBodyCellStyle(workbook);
bodyCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
bodyCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
bodyCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
bodyCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
bodyCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
bodyCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
bodyCellStyle.setWrapText(true);
HSSFCellStyle dateBobyCellStyle = ExportExcel.createDateBodyCellStyle(workbook);
int i = 1;
LinkedList<String> strs = new LinkedList<>();
LinkedHashSet<String> set = new LinkedHashSet<>();
for (IdEntity entity : list) {
String rcgyshjs = BeanUtils.getProperty(entity, "rcgyshjs");//13.分管部门
String name = BeanUtils.getProperty(entity, "name");//姓名
String xb = BeanUtils.getProperty(entity, "xb");//性别
String gzdw = BeanUtils.getProperty(entity, "gzdw");//企业名称
String zgxw = BeanUtils.getProperty(entity, "zgxw");//4.最高学位
String zc = BeanUtils.getProperty(entity, "zc");//5.职称
String rzzw = BeanUtils.getProperty(entity, "rzzw");//5.最高学位、职务等
String lxfs = BeanUtils.getProperty(entity, "lxfs");//6.联系方式
String rccc = BeanUtils.getProperty(entity, "rccc");//7.专家复审意见（人才类别）
String fjlx = BeanUtils.getProperty(entity, "fjlx");//8.入住房间类型
String mj = BeanUtils.getProperty(entity, "mj");//9.面积
String zfzt = BeanUtils.getProperty(entity, "zfzt");//10.是否入住
String fjh = BeanUtils.getProperty(entity, "fjh");//11.房间号
String bz = BeanUtils.getProperty(entity, "bz");//12.备注
row = sheet.createRow(bodyRowCount);
cell = row.createCell(0);// 序号
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(i++);
cell = row.createCell(1);// 分管部门
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(rcgyshjs);
cell = row.createCell(2);// 姓名
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(name);
cell = row.createCell(3);// 性别
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(xb);
cell = row.createCell(4);// 企业名称
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(gzdw);
cell = row.createCell(5);// 最高学位
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(zgxw);
cell = row.createCell(6);// 职称
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(zc);
cell = row.createCell(7);// 最高学位、职务等
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(rzzw);
cell = row.createCell(8);// 联系方式
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(lxfs);
cell = row.createCell(9);// 专家复审意见（人才类别）
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(rccc);
cell = row.createCell(10);// 入住房间类型
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(fjlx);
cell = row.createCell(11);// 面积
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(mj);
cell = row.createCell(12);// 是否入住
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(zfzt);
cell = row.createCell(13);// 房间号
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(fjh);
cell = row.createCell(14);// 备注
cell.setCellStyle(bodyCellStyle);
cell.setCellValue(bz);
if (currentRowCount % perPageNum == 0) {// 每个工作薄显示50000条数据
sheet = null;
sheetNum++;// 工作薄编号递增1
sheet = workbook.createSheet(title + sheetNum);// 创建一个新的工作薄
ExportExcel.setBatchDetailSheetColumn(sheet);// 设置工作薄列宽
bodyRowCount = 3;// 正文内容行号置位为0
ExportExcel.batchDetail(sheet, titleCellStyle, workbook);// 写入标题
}
bodyRowCount++;// 正文内容行号递增1
currentRowCount++;// 当前行号递增1
strs.add(rcgyshjs);
set.add(rcgyshjs);
}
//找到需要合并单元格的规律，
Integer num = 2;
Map<String, List<String>> collect = strs.stream().collect(Collectors.groupingBy(s -> s, Collectors.toList()));
for (String s : set) {
for (Map.Entry<String, List<String>> m : collect.entrySet()) {
if (s.equals(m.getKey())) {
System.out.println("888888888888888888" + m);
//合并第二列，根据list数组中的 rcgyshjs 字段分组合并
sheet.addMergedRegion(new CellRangeAddress(num, num + m.getValue().size() - 1, 1, 1));
num = num + m.getValue().size();
}
}
}
try {
workbook.write(os);
os.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return WSResult.successResult(outPath);
}
Excel工具类
```java
public class ExportExcel {
/**
* 设置标题单元样式
*
* @param workbook
* @return
*/
public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook workbook) {
HSSFCellStyle cellStyle = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 12);
font.setFontName(HSSFFont.FONT_ARIAL);// 设置标题字体
cellStyle.setFont(font);
cellStyle.setWrapText(true);
cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);// 设置列标题样式
cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);// 设置背景色
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居中
return cellStyle;
}
/**
* 设置正文单元样式
*
* @param workbook
* @return
*/
public static HSSFCellStyle createBodyCellStyle(HSSFWorkbook workbook) {
HSSFCellStyle cellStyle = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 14);
// font.setFontName("宋体");// 设置标题字体
font.setFontName(HSSFFont.FONT_ARIAL);// 设置标题字体
cellStyle.setFont(font);
cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居中
return cellStyle;
}
/**
* 设置正文单元时间样式
*
* @param workbook
* @return
*/
public static HSSFCellStyle createDateBodyCellStyle(HSSFWorkbook workbook) {
HSSFCellStyle cellStyle = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 12);
font.setFontName(HSSFFont.FONT_ARIAL);// 设置标题字体
cellStyle.setFont(font);
cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居中
HSSFDataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
return cellStyle;
}
/**
* 2022-07-14 17:41:39
* 设置表格宽度
* **/
public static void setBatchDetailSheetColumn(HSSFSheet sheet) {
sheet.setDefaultRowHeight((short) 500);
sheet.setColumnWidth((short) 0, (short) 3000);// 设置 序号 宽度
sheet.setColumnWidth((short) 1, (short) 5000);// 设置 分管部门 宽度
sheet.setColumnWidth((short) 2, (short) 5000);// 设置 姓名 宽度
sheet.setColumnWidth((short) 3, (short) 5000);// 设置 性别 宽度
sheet.setColumnWidth((short) 4, (short) 5000);// 设置 企业名称 宽度
sheet.setColumnWidth((short) 5, (short) 5000);// 设置 最高学位 宽度
sheet.setColumnWidth((short) 6, (short) 5000);// 设置 职称 宽度
sheet.setColumnWidth((short) 7, (short) 5000);// 设置 最高学位、职务等 宽度
sheet.setColumnWidth((short) 8, (short) 5000);// 设置 联系方式 宽度
sheet.setColumnWidth((short) 9, (short) 5000);// 设置 专家复审意见（人才类别） 宽度
sheet.setColumnWidth((short) 10, (short)5000);// 设置 入住房间类型 宽度
sheet.setColumnWidth((short) 11, (short)5000);// 设置 面积 宽度
sheet.setColumnWidth((short) 12, (short)5000);// 设置 是否入住 宽度
sheet.setColumnWidth((short) 13, (short)5000);// 设置 房间号 宽度
sheet.setColumnWidth((short) 14, (short)5000);// 设置 备注 宽度
}
/**
* 2022-07-14 17:42:03
* 设置表头
* **/
public static void batchDetail(HSSFSheet sheet, HSSFCellStyle cellStyle, HSSFWorkbook workbook) {
HSSFCellStyle bcs = ExportExcel.createTitleCellStyle(workbook);
bcs.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
bcs.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
bcs.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
bcs.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
bcs.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中
bcs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
bcs.setWrapText(true);
HSSFRow row = null;
HSSFCell cell = null;
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cellStyle.setWrapText(true);
/*第一行*/
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14));//2.申请年度单位
/*第二行*/
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 0)); //1.序号
sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 1)); //2.分管部门
sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 2)); //3.姓名
sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 3)); //4.性别
sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 4)); //5.企业名称
sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 5)); //6.最高学位
sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 6)); //7.职称
sheet.addMergedRegion(new CellRangeAddress(1, 1, 7, 7)); //8.最高学位、职务等
sheet.addMergedRegion(new CellRangeAddress(1, 1, 8, 8)); //9.联系方式
sheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 9)); //10.专家复审意见（人才类别）
sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 10)); //11.入住房间类型
sheet.addMergedRegion(new CellRangeAddress(1, 1, 11, 11)); //12.面积
sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 12));//13.是否入住
sheet.addMergedRegion(new CellRangeAddress(1, 1, 13, 13));//14.房间号
sheet.addMergedRegion(new CellRangeAddress(1, 1, 14, 14));//15.备注
/*第一行塞值*/
row = sheet.createRow(0);
cell = row.createCell(0);// ID
cell.setCellStyle(cellStyle);
cell.setCellValue("高新区入住人才公寓人员表情况表");
/*第二行塞值*/
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellValue("序号");
cell = row.createCell(1);
cell.setCellStyle(cellStyle);
cell.setCellValue("分管部门");
cell = row.createCell(2);
cell.setCellStyle(cellStyle);
cell.setCellValue("姓名");
cell = row.createCell(3);
cell.setCellStyle(cellStyle);
cell.setCellValue("性别");
cell = row.createCell(4);
cell.setCellStyle(cellStyle);
cell.setCellValue("企业名称");
cell = row.createCell(5);
cell.setCellStyle(cellStyle);
cell.setCellValue("最高学位");
cell = row.createCell(6);
cell.setCellStyle(cellStyle);
cell.setCellValue("职称");
cell = row.createCell(7);
cell.setCellStyle(cellStyle);
cell.setCellValue("最高学位、职务等");
cell = row.createCell(8);
cell.setCellStyle(cellStyle);
cell.setCellValue("联系方式");
cell = row.createCell(9);
cell.setCellStyle(cellStyle);
cell.setCellValue("专家复审意见（人才类别）");
cell = row.createCell(10);
cell.setCellStyle(cellStyle);
cell.setCellValue("入住房间类型");
cell = row.createCell(11);
cell.setCellStyle(cellStyle);
cell.setCellValue("面积");
cell = row.createCell(12);
cell.setCellStyle(cellStyle);
cell.setCellValue("是否入住");
cell = row.createCell(13);
cell.setCellStyle(cellStyle);
cell.setCellValue("房间号");
cell = row.createCell(14);
cell.setCellStyle(cellStyle);
cell.setCellValue("备注");
}
生成效果如下
其他复杂的效果同理，只需要找到需要合并表格的规律，再算出坐标，就可以进行合并操作。
1033
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
