下钻表导出为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实例

  Workbook workbook = new XSSFWorkbook();
  

• 创建工作表

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

  // 创建名为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();
  

• 单元格合并

  单元格合并首先要有一个单元格范围的实例对象，传入后才可以合并。

  // 获取单元格范围
  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	馒头
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	馒头
2	李四	2	包子
			包子
	王五	2	馒头

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

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

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

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

OK啦，拜拜！！

转载自：下钻表导出为Excel