复杂电话本Excel导出

原创已于 2023-06-15 17:25:30 修改 · 310 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #java

于 2022-11-22 15:53:34 首次发布

excel 专栏收录该内容

2 篇文章

订阅专栏

本文介绍如何利用Apache POI库实现带有部门分组的Excel导出功能，包括横向布局、样式定制及合并单元格等技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景

最近有一个需求，需要将公司联系方式按照部门分组导出excel，具体效果如下：
电话本
之前都是纵向生成数据，即表头生成后，列表内容向下生成就可以。开始是打算使用easyEcxel开发的，后来发现不满足，实现起来比较复杂。之后通过poi原生来实现的，大体逻辑就是：
1.将标题和列表内容都放在实体对象中，通过某些标识区分开。
2.计算显示行数（待定，目前写死固定）。
3.分别计算每条内容对应的行数，一行一行写入。

代码

1. 引入依赖

 <dependency>
    <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>4.0.1</version>
 </dependency>
 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi-ooxml</artifactId>
     <version>4.0.1</version>
 </dependency>
 <dependency>
     <groupId>org.projectlombok</groupId>
     <artifactId>lombok</artifactId>
 </dependency>

2. 实体类

package com.example.excel.dto;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;

/**
 * @ClassName AicosUserExcelDto
 * @Description excel导出
 * @Author JZC
 * @Date 2022/11/21 15:52
 * @Version 1.0
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class AicosUserExcelDto {

    /**
     * 姓名
     */
    private String name;

    /**
     * 手机号
     */
    private String phone;

    /**
     * 办公电话
     */
    private String telephone;

    /**
     * 部门ID
     */
    private String departmentId;

    /**
     * 部门name
     */
    private String departmentName;

    /**
     * 是否是表头 - 姓名、手机号、固定电话表头
     */
    private Boolean isHeader = Boolean.FALSE;

    /**
     * 是否合并 - 部门表头
     */
    private Boolean isMerge = Boolean.FALSE;

    /**
     * 行下标
     */
    private Integer rowIndex;
}

3. Controller层

package com.example.excel.controller;

import com.example.excel.dto.AicosUserExcelDto;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * @ClassName ExcelCOntroller
 * @Description
 * @Author JZC
 * @Date 2022/11/22 9:55
 * @Version 1.0
 */
@RestController
public class ExcelController {

    //每列多少行
    private Integer rowCount = 25;
	//姓名、手机号、电话表头，是固定的
    private final static String NAME = "姓名";
    private final static String PHONE = "手机号";
    private final static String TEL_PHONE = "座机号";


    @GetMapping("excel")
    public void excelDemoEnd(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();
        //建立sheet对象
        HSSFSheet sheet=wb.createSheet("电话本");

        /**部门表头*/
        CellStyle cellStyleOfDept = getCellStyleOfDept(wb);
        /**姓名、手机号、电话表头*/
        CellStyle cellStyleOfTel = getCellStyleOfTel(wb);
        /**电话信息*/
        CellStyle cellStyleOfInfo = getCellStyleOfInfo(wb);

        //获取导出数据
        List<AicosUserExcelDto> excelDtoList = getData();
        //列下标
        int cellIndex = 0;
        //导出
        for (int i = 0; i < rowCount; i++) {
            int index = i;
            //筛选当前行对应的内容
            List<AicosUserExcelDto> excelDtos = excelDtoList.stream().filter(excel -> index == excel.getRowIndex()).collect(Collectors.toList());
            if(CollectionUtils.isEmpty(excelDtos)) continue;
            //在sheet里创建第i行，参数为行索引
            HSSFRow row = sheet.createRow(i);
            for (AicosUserExcelDto excelDto : excelDtos) {
                //是部门表头
                if(Objects.equals(Boolean.TRUE, excelDto.getIsMerge())){
                    //创建单元格
                    HSSFCell cell = row.createCell(cellIndex);
                    cell.setCellStyle(cellStyleOfDept);
                    //设置单元格内容
                    cell.setCellValue(excelDto.getDepartmentName());
                    //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
                    CellRangeAddress cellAddresses = new CellRangeAddress(i, i, cellIndex, cellIndex + 2);
                    setBorderStyle(sheet, cellAddresses);
                    sheet.addMergedRegion(cellAddresses);
                }else if(Objects.equals(Boolean.TRUE, excelDto.getIsHeader())){//姓名、手机号、固定电话表头
                    HSSFCell cell1 = row.createCell(cellIndex);
                    cell1.setCellStyle(cellStyleOfTel);
                    cell1.setCellValue(NAME);
                    HSSFCell cell2 = row.createCell(cellIndex + 1);
                    //设置行宽
                    sheet.setColumnWidth(cellIndex + 1, 12*256);
                    cell2.setCellStyle(cellStyleOfTel);
                    cell2.setCellValue(PHONE);
                    HSSFCell cell3 = row.createCell(cellIndex + 2);
                    //设置行宽
                    sheet.setColumnWidth(cellIndex + 2, 10*256);
                    cell3.setCellStyle(cellStyleOfTel);
                    cell3.setCellValue(TEL_PHONE);
                }else{//用户电话信息
                    HSSFCell cell1 = row.createCell(cellIndex);
                    cell1.setCellStyle(cellStyleOfInfo);
                    cell1.setCellValue(excelDto.getName());
                    HSSFCell cell2 = row.createCell(cellIndex + 1);
                    cell2.setCellStyle(cellStyleOfInfo);
                    cell2.setCellValue(excelDto.getPhone());
                    HSSFCell cell3 = row.createCell(cellIndex + 2);
                    cell3.setCellStyle(cellStyleOfInfo);
                    cell3.setCellValue(excelDto.getTelephone());
                }
                cellIndex = cellIndex + 3;
            }
            //导出下一行时，列下标还原
            cellIndex = 0;
        }
        //输出Excel文件
        OutputStream output=response.getOutputStream();
        response.reset();
        //设置响应头，
        response.setHeader("Content-disposition", "attachment; filename=tel.xls");
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();

    }

    /**
     * 部门表头样式
     * @param wb
     * @return
     */
    private CellStyle getCellStyleOfDept(HSSFWorkbook wb){
        // 设置边框
        CellStyle cellStyleTitleOfDept = wb.createCellStyle();
        // 设置字体
        Font font = wb.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short)14);
        cellStyleTitleOfDept.setFont(font);
        // 设置对齐方式
        cellStyleTitleOfDept.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        cellStyleTitleOfDept.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        return cellStyleTitleOfDept;

    }

    /**
     * 姓名、手机号、电话表头样式
     * @param wb
     * @return
     */
    private CellStyle getCellStyleOfTel(HSSFWorkbook wb){
        // 设置边框
        CellStyle cellStyleTitleOfTel = wb.createCellStyle();
        cellStyleTitleOfTel.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyleTitleOfTel.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyleTitleOfTel.setBorderTop(BorderStyle.THIN);//上边框
        cellStyleTitleOfTel.setBorderRight(BorderStyle.THIN);//右边框
        // 设置字体
        Font fontTel = wb.createFont();
        fontTel.setBold(true);
        fontTel.setFontName("宋体");
        fontTel.setFontHeightInPoints((short)14);
        cellStyleTitleOfTel.setFont(fontTel);
        // 设置对齐方式
        cellStyleTitleOfTel.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        cellStyleTitleOfTel.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        return cellStyleTitleOfTel;
    }

    /**
     * 内容样式
     * @param wb
     * @return
     */
    private CellStyle getCellStyleOfInfo(HSSFWorkbook wb){
        // 设置边框
        CellStyle cellStyleTitleOfInfo = wb.createCellStyle();
        cellStyleTitleOfInfo.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyleTitleOfInfo.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyleTitleOfInfo.setBorderTop(BorderStyle.THIN);//上边框
        cellStyleTitleOfInfo.setBorderRight(BorderStyle.MEDIUM);//右边框
        // 设置字体
        Font fontInfo = wb.createFont();
        fontInfo.setFontName("宋体");
        fontInfo.setFontHeightInPoints((short)10);
        cellStyleTitleOfInfo.setFont(fontInfo);
        // 设置对齐方式
        cellStyleTitleOfInfo.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        cellStyleTitleOfInfo.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        return cellStyleTitleOfInfo;
    }

    /**
     * 合并单元格边框
     * @param sheet
     * @param region
     */
    private  void setBorderStyle(Sheet sheet, CellRangeAddress region) {
        // 合并单元格左边框样式
        RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
        //RegionUtil.setLeftBorderColor(IndexedColors.BLUE.getIndex(), region, sheet);

        // 合并单元格上边框样式
        RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
        //RegionUtil.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex(), region, sheet);

        // 合并单元格右边框样式
        RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
        //RegionUtil.setRightBorderColor(IndexedColors.RED.getIndex(), region, sheet);

        // 合并单元格下边框样式
        RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
        //RegionUtil.setBottomBorderColor(IndexedColors.GREEN.getIndex(), region, sheet);
    }

    //封装数据
    private List<AicosUserExcelDto> getData() {
        //TODO 获取部门
        List<AicosUserExcelDto> deptDataList = deptData();
        //TODO 获取用户
        List<AicosUserExcelDto> userDataList = new ArrayList<>();
        //封装数据
        List<AicosUserExcelDto> excelDtoList = new ArrayList<>();
        for (int i = 0; i < deptDataList.size(); i++) {
            //添加部门
            excelDtoList.add(deptDataList.get(i));
            //添加表头对象,通过isHeader来判断
            AicosUserExcelDto headerExcel = new AicosUserExcelDto();
            headerExcel.setIsHeader(Boolean.TRUE);
            excelDtoList.add(headerExcel);
            //todo 筛选用户，现为模拟数据
            for (int j = 0; j < 9; j++) {
                AicosUserExcelDto data = new AicosUserExcelDto();
                data.setName("姓名" + j);
                data.setPhone(j + "9874563210");
                data.setTelephone(j + "6543210");
                excelDtoList.add(data);
            }
        }
        //计算excel行数下标
        for (int i = 0; i < excelDtoList.size(); i++) {
            int index = i;
            if(i >= rowCount){
                int num = i%rowCount;

                if(num == 0){
                    index = 0;
                }else{
                    index = num;
                }
            }
            excelDtoList.get(i).setRowIndex(index);
        }
        return excelDtoList;
    }
    //模拟 - 部门数据
    private List<AicosUserExcelDto> deptData() {
        List<AicosUserExcelDto> list = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            AicosUserExcelDto data = new AicosUserExcelDto();
            data.setDepartmentName("部门" + i);
            data.setIsMerge(true);
            list.add(data);
        }
        return list;
    }

}