下钻表导出为Excel

这两天要搞Excel的生成接口，然后把下钻表导出为Excel。于是我自定义了一个工具类，里面包含了两个方法，其中一个是能够单元格合并的，一个是不合并的。

前言

　　这两天要搞Excel的生成接口，然后把下钻表导出为Excel。

　　为了能够复用，我自定义了一个工具类。所用技术点包括Excel操作，Web编程，Java反射，使用反射也是为了提高代码的复用性。工具类中包含了两个方法，其中一个是能够单元格合并的，一个是不合并的。

　　不合并的是这样的：

Name	Age	Quantity	Product
王五	35	10	手机
王五	35	5	电脑
王五	35	15	平板
周八	31	8	耳机

　　合并的话转化为这种格式：

Name	Age	Quantity	Product
王五	35	10	手机
		5	电脑
		15	平板
周八	31	8	耳机

Java操作Excel

　　首先要知道Excel如何用Java方法进行操作，先介绍一些简单的方法。

导入包

　　Java操作Excel可以用两种方式，一种是Apache POI、JExcelAPI、EasyExcel等。这里我们使用的即为Apache POI，导入相关包，具体版本可以在Maven上查询。

<!-- 导出excel工具包-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

操作方法

创建Excel工作簿

XSSFWorkbook()相当于新建一个.xlsx格式的Excel实例
1
Workbook workbook = new XSSFWorkbook();

创建工作表

一个工作簿实例可以包含多个工作表

1 2	// 创建名为Sheet1的工作表，sheet为实例对象 Sheet sheet = workbook.createSheet("Sheet1");

创建行

// 为0，则在第1行创建。数字为i，则在第i+1行创建
Row row = sheet.createRow(0);
// 获取某行
Row row = sheet.getRow(rowIndex);

单元格操作

// 在row这行创建第i+1列的单元格
Cell cell = row.createCell(i);
// 填充单元格数据
cell.setCellValue(String text);
// 获取某个单元格
Cell cell = row.getCell(colIndex);
// 获取某个单元格数据(字符串类型)
cell.getStringCellValue();

单元格合并

单元格合并首先要有一个单元格范围的实例对象，传入后才可以合并。
1
2
3
4
// 获取单元格范围
CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, endRow, startCol, endCol);
// 合并单元格
sheet.addMergedRegion(cellRangeAddress);
startRow：表示要合并的单元格范围的起始行，包含该行。
endRow：表示要合并的单元格范围的结束行，包含该行。
startCol：表示要合并的单元格范围的起始列，包含该列。
endCol：表示要合并的单元格范围的结束列，包含该列。
注意：都从0开始

设置单元格格式/设置行格式

sheet.autoSizeColumn(i);	//设置某列宽度为自动
sheet.getColumnWidth(i);	// 获取某列宽度

Workbook workbook = new XSSFWorkbook();
CellStyle dataStyle = workbook.createCellStyle(); // 建立一个单元格格式
dataStyle.setAlignment(HorizontalAlignment.CENTER);  // 水平居中
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
// 其他自己查查去
cell.setCellStyle(dataStyle); // 某个单元格设置如上的格式

传输方法

　　利用HTTP响应，向客户端发送数据，相当于是网络编程。使用的是HttpServletResponse response，我们所构建传输的过程，就是构建一个http的数据包，所以相应的响应头是需要设置的。

　　另外，传输数据是用的字节流OutputStream，作为数据流。

　　在此代码中，用到的相关代码如下：

OutputStream outputStream = response.getOutputStream();
 // 设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");	// 传输的数据类型是.xlsx
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment; filename=data.xlsx");	// 传输的文件名为data.xlsx
workbook.write(outputStream);	// 将workbook中的数据

类属性反射

　　为了代码的重用性，在方法中传递了一个Map<String,String>作为表头和类属性的映射。具体k,y是<表头字段,类属性名>。

　　然后取出了属性名后，要用反射来取相关的值，如下所示。

// 获取属性名
String fieldName = mapping.get(headings.get(j));
T item = data.get(i);	
// 根据属性名获取相关的属性，注意这边获取属性要用Declared，否则无法取值
Field field = item.getClass().getDeclaredField(fieldName);
// 设置Field对象可访问
field.setAccessible(true);
// 获取item对象的相关属性
Object value = field.get(item);

　　上面的data.get(i)是从List data中获取index为i的数据。反射语句主要就是Field field = item.getClass().getDeclaredField(fieldName);这一句

完整的程序

需要引入的包

我这边导入apache的POI作为表格操作包

<!-- 导出excel工具包-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

代码

setStyle方法是格式的设置，execute方法是带合并的，executeWithoutMerge是不带合并的。
两个方法中的mapping都是表头对于类属性的映射关系，response是需要，合并中要多传一个limit，就是前多少列是需要合并的。

package com.cockpit.common;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.List;
import java.util.Map;

/**
 * Description: 数据导出为excel的工具
 *
 * @Author: Pan Tianyu 00414384
 * @Date: 2025/1/6 14:44
 */
public class ExportExcelCommon {
    // 创建表头单元格样式
    private static CellStyle headerStyle;
    // 创建数据行单元格样式
    private static CellStyle dataStyle;

    // 设置表格式
    private static void setStyle(Workbook workbook) {
        // 创建表头单元格样式
        headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = workbook.createFont();
        headerFont.setBold(false);
        headerFont.setFontHeightInPoints((short) 10);
        headerStyle.setFont(headerFont);

        // 创建数据行单元格样式
        dataStyle = workbook.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    }

    /**
     * @param response 返回excel的对象
     * @param data 传List类型数据
     * @param headings 传表头
     * @param mapping 传类表头和字段的映射关系 <表头,字段>
     * @param limit 合并几列
     * @param <T> 一行数据的类型
     */
    public static <T> void execute(HttpServletResponse response, List<T> data, List<String> headings, Map<String, String> mapping, int limit) {
        try (Workbook workbook = new XSSFWorkbook();
             OutputStream outputStream = response.getOutputStream()) {
            Sheet sheet = workbook.createSheet("Sheet1");
            setStyle(workbook);

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < headings.size(); i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellStyle(headerStyle);
                cell.setCellValue(headings.get(i));
            }

            // 填充数据
            for (int i = 0; i < data.size(); i++) {
                Row row = sheet.createRow(i + 1);
                T item = data.get(i);
                for (int j = 0; j < headings.size(); j++) {
                    String fieldName = mapping.get(headings.get(j));
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(dataStyle);
                    try {
                        Field field = item.getClass().getDeclaredField(fieldName);
                        field.setAccessible(true);
                        Object value = field.get(item);
                        if (value != null) {
                            cell.setCellValue(value.toString());
                            cell.setCellStyle(dataStyle);
                        }
                    } catch (NoSuchFieldException | IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
            }

            // 合并单元格逻辑
            if (limit > 0) {
                for (int col = 0; col < limit; col++) {
                    int startRow = 1;
                    while (startRow < data.size() + 1) {
                        int endRow = startRow + 1;
                        while (endRow < data.size() + 1 &&
                                isEqual(sheet, startRow, endRow, col, limit)) {
                            endRow++;
                        }
                        if (endRow - startRow > 1) {
                            CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, endRow - 1, col, col);
                            sheet.addMergedRegion(cellRangeAddress);
                        }
                        startRow = endRow;
                    }
                }
            }
            for (int i = 0; i < headings.size(); i++) {
                sheet.autoSizeColumn(i);
                int autoSizedWidth = sheet.getColumnWidth(i);
                sheet.setColumnWidth(i, autoSizedWidth + 600);
            }
            // 设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=data.xlsx");

            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static boolean isEqual(Sheet sheet, int startRow, int endRow, int col, int limit) {
        for (int i = 0; i < limit; i++) {
            String startValue = getCellValue(sheet, startRow, i);
            String endValue = getCellValue(sheet, endRow, i);
            if (!startValue.equals(endValue)) {
                return false;
            }
        }
        return true;
    }

    private static String getCellValue(Sheet sheet, int rowIndex, int colIndex) {
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            Cell cell = row.getCell(colIndex);
            if (cell != null) {
                return cell.getStringCellValue();
            }
        }
        return "";
    }

    /**
     * 执行导出 Excel 操作，不进行单元格合并
     *
     * @param data 传 List 类型数据
     * @param headings 传表头
     * @param mapping 传类表头和字段的映射关系 <表头,字段>
     * @return 是否生成成功
     * @param <T> 一行数据的类型
     */
    public static <T> void executeWithoutMerge(HttpServletResponse response, List<T> data, List<String> headings, Map<String, String> mapping) {
        Workbook workbook = null;
        try {
            workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Data Sheet");
            setStyle(workbook);

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < headings.size(); i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellStyle(headerStyle);
                cell.setCellValue(headings.get(i));
            }

            int rowIndex = 1;
            // 缓存 Field 对象，避免多次反射
            Field[] fields = new Field[headings.size()];
            for (int i = 0; i < headings.size(); i++) {
                String fieldName = mapping.get(headings.get(i));
                fields[i] = data.get(0).getClass().getDeclaredField(fieldName);
                fields[i].setAccessible(true);
            }

            for (T item : data) {
                Row row = sheet.createRow(rowIndex);
                int cellIndex = 0;
                for (Field field : fields) {
                    Cell cell = row.createCell(cellIndex);
                    cell.setCellStyle(dataStyle);
                    Object value = field.get(item);
                    if (value!= null) {
                        cell.setCellValue(value.toString());
                    }
                    cellIndex++;
                }
                rowIndex++;
            }

            for (int i = 0; i < headings.size(); i++) {
                sheet.autoSizeColumn(i);
                int autoSizedWidth = sheet.getColumnWidth(i);
                sheet.setColumnWidth(i, autoSizedWidth + 600);
            }

            // 设置响应头，指定内容类型和文件名称
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=data.xlsx");

            // 获取输出流
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
        } catch (NoSuchFieldException | IllegalAccessException | IOException e) {
            try {
                // 设置响应状态码为 500 并发送错误信息
                response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                response.getWriter().write("Error generating Excel: " + e.getMessage());
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        } finally {
            if (workbook!= null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

实际调用写接口如下

@ApiOperation("Test")
@PostMapping("/selectFourDistribution")
@Log(title = "Test", businessType = BusinessType.EXPORT)
public void selectFourDistribution(HttpServletResponse response,@RequestBody TestParam param){
    List<FourDistributionResult> data = testService.selectTest(param).getTestList(); // 自己的service出的List
    List<String> headings = Arrays.asList("序号","供应商名称","代码");
    LinkedHashMap<String, String> mapping = new LinkedHashMap<>();
    mapping.put("序号","index");
    mapping.put("供应商名称","supplierName");
    mapping.put("代码","code");
    ExportExcelCommon.executeWithoutMerge(response, data, headings, mapping);
}

提升

　　这个合并的方法execute其实还有很大的不足之处。我这边的合并的情况，只考虑了一级合并，没有考虑多级合并。

　　拿二级合并举个例子，如果原来是这样的：

组号	姓名	数量	商品
1	张三	2	包子
1	张三	1	馒头
2	李四	2	包子
2	李四	2	包子
2	王五	2	馒头

　　那么limit为3的二级合并就是这样的：

组号	姓名	数量	商品
1	张三	2	包子
1	张三	1	馒头
2	李四	2	包子
	李四	2	包子
	王五	2	馒头

　　当时为了节省时间并没有做成像这样的。现在做出来了，但是没有转化为springboot里面能够直接传给前端的方法，而是本地生成xlsx的方法，有兴趣可以自己转一下，代码如下。

package org.example;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.List;
import java.util.Map;

public class ExportExcelCommon {
    /**
     * 执行导出 Excel 操作，支持多级合并
     *
     * @param data 传 List 类型数据
     * @param headings 传表头
     * @param mapping 传类表头和字段的映射关系 <表头, 字段>
     * @param path 导出文件的路径
     * @param limit 最多合并到第几列
     * @param <T> 一行数据的类型
     * @return 是否生成成功
     */
    public static <T> boolean executeMultMerge(List<T> data, List<String> headings, Map<String, String> mapping, String path, int limit){
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Data Sheet");

        // 创建表头行
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headings.size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headings.get(i));
        }

        int rowIndex = 1;
        try {
            // 存储每个单元格的合并起始行和结束行，初始化为当前行
            int[][] mergeRanges = new int[headings.size()][2];
            for (int i = 0; i < headings.size(); i++) {
                mergeRanges[i][0] = rowIndex;
                mergeRanges[i][1] = rowIndex;
            }

            T prevItem = null;
            for (T item : data) {
                Row row = sheet.createRow(rowIndex);
                int cellIndex = 0;
                boolean merge = true;
                for (String heading : headings) {
                    Cell cell = row.createCell(cellIndex);
                    String fieldName = mapping.get(heading);
                    Field field = item.getClass().getDeclaredField(fieldName);
                    field.setAccessible(true);
                    Object value = field.get(item);
                    if (value!= null) {
                        cell.setCellValue(value.toString());
                    }

                    // 检查是否需要合并，从第一列开始，直到 limit 列
                    if (cellIndex < limit) {
                        if (prevItem!= null) {
                            Field prevField = prevItem.getClass().getDeclaredField(fieldName);
                            prevField.setAccessible(true);
                            Object prevValue = prevField.get(prevItem);
                            if (prevValue == null ||!prevValue.equals(value)) {
                                merge = false;
                            }
                        }
                        if (merge) {
                            // 更新合并范围
                            mergeRanges[cellIndex][1] = rowIndex;
                        } else {
                            // 合并之前标记的范围
                            if (mergeRanges[cellIndex][1] > mergeRanges[cellIndex][0]) {
                                CellRangeAddress newRange = new CellRangeAddress(mergeRanges[cellIndex][0], mergeRanges[cellIndex][1], cellIndex, cellIndex);
                                sheet.addMergedRegion(newRange);
                            }
                            // 开始新的合并范围
                            mergeRanges[cellIndex][0] = rowIndex;
                            mergeRanges[cellIndex][1] = rowIndex;
                        }
                    }
                    cellIndex++;
                }
                prevItem = item;
                rowIndex++;
            }

            // 处理最后一组的合并
            for (int i = 0; i < limit; i++) {
                if (mergeRanges[i][1] > mergeRanges[i][0]) {
                    CellRangeAddress newRange = new CellRangeAddress(mergeRanges[i][0], mergeRanges[i][1], i, i);
                    sheet.addMergedRegion(newRange);
                }
            }
        } catch (NoSuchFieldException | IllegalAccessException e) {
            e.printStackTrace();
            return false;
        }

        try (FileOutputStream outputStream = new FileOutputStream(path)) {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }
}

　　具体举例调用：

public class App 
{
    public static void main( String[] args )
    {
        List<Order> data = new ArrayList<>();
        data.add(new Order(1, "张三", 2, "包子"));
        data.add(new Order(1, "张三", 1, "馒头"));
        data.add(new Order(2, "李四", 2, "包子"));
        data.add(new Order(2, "李四", 2, "包子"));
        data.add(new Order(2, "王五", 2, "馒头"));

        List<String> headings = new ArrayList<>();
        headings.add("组号");
        headings.add("姓名");
        headings.add("数量");
        headings.add("商品");
        HashMap<String, String> mapping = new LinkedHashMap<>();
        mapping.put("组号","index");
        mapping.put("姓名","name");
        mapping.put("数量","num");
        mapping.put("商品","goodName");
        System.out.println(ExportExcelCommon.executeMultMerge(data,headings,mapping,"outputMultMerge.xlsx",3));
    }
}

　　这种情况就会有如下表格：

组号	姓名	数量	商品
1	张三	2	包子
1	张三	1	馒头
2	李四	2	包子
	李四	2	包子
	王五	2	馒头

　　如果把limit设置为4的话，就会变成下面的情况：

组号	姓名	数量	商品
1	张三	2	包子
1	张三	1	馒头
2	李四	2	包子
	李四	2	包子
	王五	2	馒头

　　当然这边李四是占了两行的，这边使用html展示的，所以不好展示，我在最后多加了一列来凸显李四是占了两行的。

　　有兴趣的话，大家可以自己研究一下。

　　OK啦，拜拜！！