Java 导出 Excel，相同列数据相同的情况下合并单元格【POI的相关依赖自行百度添加】
一、PoiModel 类用来记录 上一行数据
代码语言：javascript复制package com.hypersmart.dashboard.util.excelUtils;
/**
* @author bian lei
* @version 1.0
* @date 2021/12/29 17:19
*/
public class PoiModel {
//内容
private String content;
//上一行同一位置内容
private String oldContent;
//行标
private int rowIndex;
//列标
private int cellIndex;
public String getOldContent() {
return oldContent;
}
public void setOldContent(String oldContent) {
this.oldContent = oldContent;
}
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;
}
}二、Excel的工具类【按需自行修改里面的参数：默认是UTF-8编码，有需要在response.setHeader 附件修改】
代码语言：javascript复制package com.hypersmart.dashboard.util.excelUtils;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.hypersmart.dashboard.util.DateUtil;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.google.common.collect.Lists;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @author bian lei
* @version 1.0
* @date 2021/12/29 17:19
*/
public class ExportExcelByPoiUtil {
private static Logger logger = LoggerFactory.getLogger(ExportExcelByPoiUtil.class);
/**
* @param @param request
* @param @param response
* @param @param title 标题数组
* @param @param titleHead Excel标题
* @param @param widthAttr 单元格宽度
* @param @param maps 数据
* @param @param mergeIndex 要合并的列 数组
* @param @return 设定文件
* @return String 返回类型
* @throws
*/
@SuppressWarnings("rawtypes")
public static void createExcel(HttpServletRequest request, HttpServletResponse response,String[] title,String titleHead ,int[] widthAttr,Map<String/*sheet名*/, List<Map<String/*对应title的值*/, String>>> maps, int[] mergeIndex){
if (title.length==0){
return;
}
/*初始化excel模板*/
Workbook workbook = new XSSFWorkbook();
Sheet sheet = null;
int n = 0;
/*循环sheet页*/
for(Map.Entry<String, List<Map<String/*对应title的值*/, String>>> entry : maps.entrySet()){
/*实例化sheet对象并且设置sheet名称，book对象*/
try {
sheet = workbook.createSheet();
workbook.setSheetName(n, entry.getKey());
workbook.setSelectedTab(0);
}catch (Exception e){
e.printStackTrace();
}
// 设置样式 头 cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
// 水平方向的对齐方式
CellStyle cellStyle_head = style(0, workbook);
// 导出时间
CellStyle cellStyle_export = style(3, workbook);
// 标题
CellStyle cellStyle_title = style(1, workbook);
// 正文
CellStyle cellStyle = style(2, workbook);
// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
CellRangeAddress c1 = new CellRangeAddress(0, 0, 0, title.length-1);
sheet.addMergedRegion(c1);
CellRangeAddress c2 = new CellRangeAddress(1, 1, 0, title.length-1);
sheet.addMergedRegion(c2);
// 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
Row row0 = sheet.createRow(0);
// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
Cell cell1 = row0.createCell(0);
// 设置单元格内容 标题 可以自定义拼接
//列 cell1.setCellValue("" titleHead "");
cell1.setCellValue(titleHead);
cell1.setCellStyle(cellStyle_head);
// 设置合并单元格边框
setRegionStyle(sheet, c1, cellStyle_head);
setRegionStyle(sheet, c2, cellStyle_export);
// 设置列宽
for (int i = 0; i < widthAttr.length; i ) {
sheet.setColumnWidth((short) i, (short) widthAttr[i] * 200);
}
// 在sheet里创建第二行
Row row1 = sheet.createRow(1);
// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
Cell cell2 = row1.createCell(0);
// 设置单元格内容 标题
cell2.setCellValue("导出时间：" DateUtil.getDateString(DateUtil.DATE_TIME_PATTERN) );
cell2.setCellStyle(cellStyle_export);
/*初始化标题，填值标题行（第一行）*/
Row row2 = sheet.createRow(2);
for(int i = 0; i<title.length; i ){
/*创建单元格，指定类型*/
Cell cell_1 = row2.createCell(i, Cell.CELL_TYPE_STRING);
//设置标题的值
cell_1.setCellValue(title[i]);
//设置标题样式
cell_1.setCellStyle(cellStyle_title);
}
/*得到当前sheet下的数据集合*/
List<Map<String/*对应title的值*/, String>> list = entry.getValue();
/*遍历该数据集合*/
List<PoiModel> poiModels = Lists.newArrayList();
if(null!=workbook){
Iterator iterator = list.iterator();
// int index = 1;/*这里1是从excel的第二行开始，第一行已经塞入标题了*/
int index = 3;/*这里3是从excel的第四行开始，前面几行已经塞入标题了*/
while (iterator.hasNext()){
Row row = sheet.createRow(index);
/*取得当前这行的map，该map中以key，value的形式存着这一行值*/
@SuppressWarnings("unchecked")
Map<String, String> map = (Map<String, String>)iterator.next();
/*循环列数，给当前行塞值*/
for(int i = 0; i<title.length; i ){
String old = "";
/*old存的是上一行统一位置的单元的值，第一行是最上一行了，所以从第二行开始记*/
if(index > 3){
old = poiModels.get(i)==null ? "":poiModels.get(i).getContent();
}
/*循环需要合并的列*/
for(int j = 0; j < mergeIndex.length; j ){
/* 因为标题行前还有2行 所以index从3开始 也就是第四行*/
if(index == 3){
/*记录第一行的开始行和开始列*/
PoiModel poiModel = new PoiModel();
poiModel.setOldContent(map.get(title[i]));
poiModel.setContent(map.get(title[i]));
poiModel.setRowIndex(3);
poiModel.setCellIndex(i);
poiModels.add(poiModel);
break;
}else if(i > 0 && mergeIndex[j] == i){
/*这边i>0也是因为第一列已经是最前一列了，只能从第二列开始*/
/*当前同一列的内容与上一行同一列不同时，把那以上的合并, 或者在当前元素一样的情况下，前一列的元素并不一样，这种情况也合并*/
/*如果不需要考虑当前行与上一行内容相同，但是它们的前一列内容不一样则不合并的情况，把下面条件中||poiModels.get(i).getContent().equals(map.get(title[i])) && !poiModels.get(i - 1).getOldContent().equals(map.get(title[i-1]))去掉就行*/
if(!poiModels.get(i).getContent().equals(map.get(title[i])) || poiModels.get(i).getContent().equals(map.get(title[i])) && !poiModels.get(i - 1).getOldContent().equals(map.get(title[i-1]))){
/*当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并*/
CellRangeAddress cra=new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index - 1/*到第几行*/, poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);
//在sheet里增加合并单元格
sheet.addMergedRegion(cra);
/*重新记录该列的内容为当前内容，行标记改为当前行标记，列标记则为当前列*/
poiModels.get(i).setContent(map.get(title[i]));
poiModels.get(i).setRowIndex(index);
poiModels.get(i).setCellIndex(i);
}
}
/*处理第一列的情况*/
if(mergeIndex[j] == i && i == 0 && !poiModels.get(i).getContent().equals(map.get(title[i]))){
/*当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并*/
CellRangeAddress cra=new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index - 1/*到第几行*/, poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);
//在sheet里增加合并单元格
sheet.addMergedRegion(cra);
/*重新记录该列的内容为当前内容，行标记改为当前行标记*/
poiModels.get(i).setContent(map.get(title[i]));
poiModels.get(i).setRowIndex(index);
poiModels.get(i).setCellIndex(i);
}
/*最后一行没有后续的行与之比较，所有当到最后一行时则直接合并对应列的相同内容 加2是因为标题行前面还有2行*/
if(mergeIndex[j] == i && index == list.size() 2){
CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index/*到第几行*/, poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);
//在sheet里增加合并单元格
sheet.addMergedRegion(cra);
}
}
Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING);
cell.setCellValue(map.get(title[i]));
cell.setCellStyle(cellStyle);
/*在每一个单元格处理完成后，把这个单元格内容设置为old内容*/
poiModels.get(i).setOldContent(old);
}
index ;
}
}
n ;
}
OutputStream out = null;
try {
Calendar calendar1 = Calendar.getInstance();
String cal = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(calendar1.getTime());
out = response.getOutputStream();
response.reset();//清空输出流
response.setHeader("Content-disposition", "attachment;filename=" new String(titleHead.getBytes(StandardCharsets.UTF_8), "iso8859-1") cal ".xlsx");// 设定输出文件头
response.setContentType("application/vnd.ms-excel;charset=UTF-8");// 定义输出类型
workbook.write(out);
}catch (IOException e){
logger.error("导出异常",e);
}finally {
try {
out.flush();
out.close();
}catch (IOException e){
logger.error("流的关闭异常",e);
}
}
}
/**
* @param @return 设定文件 index 0:头 1：标题 2：正文
* @return HSSFCellStyle 返回类型
* @throws
*/
public static CellStyle style(int index, Workbook workbook) {
CellStyle cellStyle = null;
if (index == 0) {
// 设置头部样式
cellStyle = workbook.createCellStyle();
// 设置字体大小 位置
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
Font font = workbook.createFont();
//设置字体
font.setFontName("微软雅黑");
//字体颜色
font.setColor(HSSFColor.BLACK.index);// HSSFColor.VIOLET.index
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);//背景白色
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setFont(font);
}
//标题
if (index == 1) {
cellStyle = workbook.createCellStyle();
// 设置字体大小 位置
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
Font font_title = workbook.createFont();
//设置字体
font_title.setFontName("微软雅黑");
font_title.setColor(HSSFColor.BLACK.index);// HSSFColor.VIOLET.index
//字体颜色
font_title.setFontHeightInPoints((short) 10);
font_title.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
cellStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cellStyle.setFont(font_title);
}
//正文
if (index == 2) {
// 设置样式
cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
Font font_title = workbook.createFont();
//设置字体
font_title.setFontName("微软雅黑");
// cellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
cellStyle.setWrapText(true); // 自动换行
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);//背景白色
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
}
//时间
if (index == 3) {
// 设置样式
cellStyle = workbook.createCellStyle();
// 居中
cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// 生成一个字体
Font font_title = workbook.createFont();
//设置字体
font_title.setFontName("微软雅黑");
font_title.setColor(HSSFColor.BLACK.index);// HSSFColor.VIOLET.index
// //字体颜色
font_title.setFontHeightInPoints((short) 10);
font_title.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setFont(font_title);
}
if (index == 4) {
// 设置样式
cellStyle = workbook.createCellStyle();
// 居中
// cellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
}
return cellStyle;
}
/**
* @param @param sheet
* @param @param region
* @param @param cs 设定文件
* @return void 返回类型
* @throws
*/
public static void setRegionStyle(Sheet sheet, CellRangeAddress region, CellStyle cs) {
for (int i = region.getFirstRow(); i <= region.getLastRow(); i ) {
Row row = CellUtil.getRow(i, sheet);
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j ) {
Cell cell = CellUtil.getCell(row, (short) j);
cell.setCellStyle(cs);
}
}
}
}三、代码生产中的用例
代码语言：javascript复制package com.hypersmart.dashboard.controller;
import com.hypersmart.dashboard.bo.MaterialStatisticsBo;
import com.hypersmart.dashboard.service.MaterialStatisticsService;
import com.hypersmart.dashboard.vo.MaterialStatisticsVo;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
/**
* 耗材统计表 前端控制器
*
* @author LeiBian
* @since 2021-12-27
*/
@RestController
@RequestMapping("/api/materialStatistics/v1/")
public class MaterialStatisticsController {
@Resource
MaterialStatisticsService materialStatisticsService;
@PostMapping("/excel")
public void getExcel(@RequestBody MaterialStatisticsBo materialStatisticsBo, HttpServletRequest request, HttpServletResponse response){
materialStatisticsService.getExcel(materialStatisticsBo,request,response);
}
}
/**
* materialStatisticsService 中的方法
*/
@Override
public void getExcel(MaterialStatisticsBo materialStatisticsBo, HttpServletRequest request, HttpServletResponse response) {
materialStatisticsBo.setExcel(true);
List<MaterialStatisticsVo> list = getList(materialStatisticsBo); //此处是获取数据接口，替换成自己的数据源即可
String[] titleAttr = {"组织层级","组织名称","工单类型","条线","工单类别","工单数量","耗材名称","耗材单价","耗材数量","费用总计"};
int[] widthAttr = {30,30,30,30,50,30,30,30,30,30};//设置单元的宽度
String titleHead = "耗材统计报表";
List<Map<String, String>> dataList = new ArrayList<>();
if (CollectionUtils.isNotEmpty(list)){
LinkedHashMap<String, List<MaterialStatisticsVo>> collect = list.stream().collect(Collectors.groupingBy(MaterialStatisticsVo::getOrderTypeName, LinkedHashMap::new, Collectors.toList()));
List<MaterialStatisticsVo> customerList = collect.get("报修工单");
dataVoWorkOrder(dataList, customerList);
List<MaterialStatisticsVo> dispatchList = collect.get("派工单");
dataVoWorkOrder(dataList, dispatchList);
}
Map<String,List<Map<String, String>>> map = Maps.newHashMap();
map.put("耗材报表", dataList);
ExportExcelByPoiUtil.createExcel(request,response,titleAttr,titleHead,widthAttr,map,new int[]{2,3,4,5});
}
private void dataVoWorkOrder(List<Map<String, String>> dataList, List<MaterialStatisticsVo> dispatchList) {
if (CollectionUtils.isNotEmpty(dispatchList)) {
for (MaterialStatisticsVo materialStatisticsVo : dispatchList) {
Map<String, String> temp = new HashMap<>();
temp.put("组织层级", materialStatisticsVo.getLayer());
temp.put("组织名称", materialStatisticsVo.getLayerOrgName());
temp.put("工单类型", materialStatisticsVo.getOrderTypeName());
temp.put("条线", materialStatisticsVo.getLineName());
temp.put("工单类别", materialStatisticsVo.getCate());
temp.put("工单数量", materialStatisticsVo.getCnt());
temp.put("耗材名称", materialStatisticsVo.getMetName());
temp.put("耗材单价", materialStatisticsVo.getPrice());
temp.put("耗材数量", materialStatisticsVo.getQuantity());
temp.put("费用总计", materialStatisticsVo.getTotalPrice());
dataList.add(temp);
}
}
}四、导出的Excel样例
