POI绘制复杂表格导出

MAVEN

<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>

Controller

  @GetMapping(value = "/export/{id}")
  public void export(@PathVariable("id") Long id, HttpServletResponse response) throws Exception {
     //todo overview数据查询明细详情
    ListVo vo = oService.getInfo(id);

    // 新建workbook
    HSSFWorkbook workbook = new HSSFWorkbook();

    // 导出数据封装
    oExportService.export(vo,workbook);

    // 临时文件名
    String fileName = System.currentTimeMillis() + ".xls";

    //文件流返回
    OutputStream out = response.getOutputStream();
    response.setContentType("application/octet-stream");
    response.setHeader("Content-Disposition", "attachment;fileName=" + fileName);
    workbook.write(out);
    workbook.close();
  }

Service

@Override
  public void export(ListVo vo, HSSFWorkbook workbook)
      throws IOException, ServerException, InvalidBucketNameException, InsufficientDataException,
          ErrorResponseException, NoSuchAlgorithmException, InvalidKeyException,
          InvalidResponseException, XmlParserException, InternalException {
    // 创建工作表
    HSSFSheet sheet = workbook.createSheet();

    // 创建行 第0行
    HSSFRow row0 = sheet.createRow(0);
    // 创建单元格
    HSSFCell cell00 = row0.createCell(0);
    cell00.setCellValue("11");
    // 设置单元格格式,每个单元格需要单独设置
    CellStyle cellStyle = ExportCellStyleUtils.setLeftTitle(workbook);
    cell00.setCellStyle(cellStyle);
    // 合并单元格
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));

    // 创建行 第一行
    HSSFRow row1 = sheet.createRow(1);
    // 创建单元格
    HSSFCell cell10 = row1.createCell(0);
    cell10.setCellValue("Name");
    cell10.setCellStyle(ExportCellStyleUtils.setBlueBackWhiteFont(workbook));
    HSSFCell cell11 = row1.createCell(1);
    cell11.setCellValue(vo.getName());
    CellStyle cellStyle1 = ExportCellStyleUtils.setBlueBackBlackFont(workbook);
    cell11.setCellStyle(ExportCellStyleUtils.setBorderLine(cellStyle1, false, false, false, true));

    // 此处导出图片 直接复制 替换对应行列值即可
    // 创建行 第36行
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    HSSFRow row36 = sheet.createRow(36);

    HSSFCell cell360 = row36.createCell(0);
    CellStyle cellStyle36a = workbook.createCellStyle();
    cell360.setCellStyle(
        ExportCellStyleUtils.setBorderLine(cellStyle36a, false, true, false, false));

    HSSFCell cell361 = row36.createCell(1);
    CellStyle cellStyle36b = workbook.createCellStyle();
    cell361.setCellStyle(
        ExportCellStyleUtils.setBorderLine(cellStyle36b, false, true, false, true));

    BufferedImage bufferedImage;
    String[] imgs = vo.getFileNames().split(",");
    // 计算边距
    int mar = 10 + 10 + (imgs.length - 1) * 10;
    // 大致平均值,每个图片宽度(1023为每个单元格总比，）
    int ave = (1023 - mar) / imgs.length;
    // 设置图片位于表格的单元格下标
    int hang = 36;
    int lie = 0;
    // 设置单元个宽高，单元格宽高限制了图片的宽高
    row36.setHeight((short) (100 * 10));
    sheet.setColumnWidth(lie, 4800 * imgs.length);
    for (int i = 0; i < imgs.length; i++) {
      InputStream fileInputStream = minioUtils.getObject(imgs[i]);
      bufferedImage = ImageIO.read(fileInputStream);
      ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
      ImageIO.write(bufferedImage, "jpg", byteArrayOutputStream);
      HSSFClientAnchor hSSFClientAnchor;
      // dx1:图片左上角x坐标, dy1:图片左上角y坐标范, dx2:图片右下角x坐标,
      // dy2:图片右下角y坐标。坐标x轴总比1023，坐标y轴总比255,图片排列样式根据左上角与右下角坐标比例排列
      hSSFClientAnchor =
          new HSSFClientAnchor(
              10 * (i + 1) + ave * i,
              10,
              (10 + ave) * (i + 1),
              245,
              (short) lie,
              hang,
              (short) lie,
              hang);
      patriarch.createPicture(
          hSSFClientAnchor,
          workbook.addPicture(byteArrayOutputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
      fileInputStream.close();
      byteArrayOutputStream.close();
    }
    
    sheet.addMergedRegion(new CellRangeAddress(36, 36, 0, 1));

  }

当前为空白模板导出

设计相关工具类

public class ExportCellStyleUtils {

  /**
   * 设置左侧大标题栏 蓝底白字加粗
   *
   * @param workbook 工作簿
   * @return {@link CellStyle}
   */
  public static CellStyle setLeftTitle(HSSFWorkbook workbook) {
    CellStyle cellStyle = getCellStyle(workbook);

    // 单元格字体填充
    Font font = workbook.createFont();
    // 加粗
    font.setBold(true);
    // 字体
    font.setFontName("宋体");
    // 字体高度
    font.setFontHeightInPoints((short) 14);
    // 字体颜色：白色
    font.setColor(IndexedColors.WHITE.getIndex());
    cellStyle.setFont(font);
    return cellStyle;
  }

  /**
   * 设置左侧2-5行蓝底白字
   *
   * @param workbook 工作簿
   * @return {@link CellStyle}
   */
  public static CellStyle setBlueBackWhiteFont(HSSFWorkbook workbook) {
    CellStyle cellStyle = getCellStyle(workbook);

    // 单元格字体填充
    Font font = workbook.createFont();
    // 字体
    font.setFontName("宋体");
    // 字体高度
    font.setFontHeightInPoints((short) 11);
    // 字体颜色：白色
    font.setColor(IndexedColors.WHITE.getIndex());
    cellStyle.setFont(font);
    return cellStyle;
  }

  /**
   * 设置左侧2-6行蓝底黑字
   *
   * @param workbook 工作簿
   * @return {@link CellStyle}
   */
  public static CellStyle setBlueBackBlackFont(HSSFWorkbook workbook) {
    CellStyle cellStyle = getCellStyle(workbook);

    // 单元格字体填充
    Font font = workbook.createFont();
    // 字体
    font.setFontName("宋体");
    // 字体高度
    font.setFontHeightInPoints((short) 11);
    cellStyle.setFont(font);
    return cellStyle;
  }

  /**
   * 设置左侧小标题蓝底白字
   *
   * @param workbook 工作簿
   * @return {@link CellStyle}
   */
  public static CellStyle setLeftLittleTitle(HSSFWorkbook workbook) {
    CellStyle cellStyle = getCellStyle(workbook);

    // 单元格字体填充
    Font font = workbook.createFont();
    // 加粗
    font.setBold(true);
    // 字体
    font.setFontName("宋体");
    // 字体高度
    font.setFontHeightInPoints((short) 11);
    // 字体颜色：白色
    font.setColor(IndexedColors.WHITE.getIndex());
    cellStyle.setFont(font);
    return cellStyle;
  }

  /**
   * 设置右侧蓝色下划线字体
   *
   * @param workbook 工作簿
   * @return {@link CellStyle}
   */
  public static CellStyle setRightBlueDownLine(HSSFWorkbook workbook) {
    CellStyle cellStyle = workbook.createCellStyle();
    // 单元格背景色填充
    HSSFPalette palette = workbook.getCustomPalette();
    palette.setColorAtIndex((short) 0x8, (byte) 0, (byte) 125, (byte) 170);

    // 单元格字体填充
    Font font = workbook.createFont();
    // 加粗
    font.setBold(true);
    // 加粗
    font.setUnderline(Font.U_SINGLE);
    // 字体
    font.setFontName("宋体");
    // 字体高度
    font.setFontHeightInPoints((short) 11);
    // 字体颜色：白色
    font.setColor((short) 0x8);
    cellStyle.setFont(font);
    return cellStyle;
  }

  /**
   * 设置蓝色边框线
   *
   * @param cellStyle 格式样式
   * @param top 前
   * @param bottom 底
   * @param left 左
   * @param right 正确
   * @return {@link CellStyle}
   */
  public static CellStyle setBorderLine(
      CellStyle cellStyle, Boolean top, Boolean bottom, Boolean left, Boolean right) {
    // 设置单元格格式,每个单元格需要单独设置
    if (top) {
      // 设置上边框线条类型
      cellStyle.setBorderTop(BorderStyle.MEDIUM);
      // 设置上边框线条颜色
      cellStyle.setTopBorderColor((short) 0x8);
    }
    if (bottom) {
      // 设置下边框线条类型
      cellStyle.setBorderBottom(BorderStyle.MEDIUM);
      // 设置下边框线条颜色
      cellStyle.setBottomBorderColor((short) 0x8);
    }
    if (left) {
      // 设置左边框线条类型
      cellStyle.setBorderLeft(BorderStyle.MEDIUM);
      // 设置左边框线条颜色
      cellStyle.setLeftBorderColor((short) 0x8);
    }
    if (right) {
      // 设置右边框线条类型
      cellStyle.setBorderRight(BorderStyle.MEDIUM);
      // 设置右边框线条颜色
      cellStyle.setRightBorderColor((short) 0x8);
    }
    return cellStyle;
  }

  /**
   * 获取单元格蓝色背景
   *
   * @param workbook 工作簿
   * @return {@link CellStyle}
   */
  private static CellStyle getCellStyle(HSSFWorkbook workbook) {
    CellStyle cellStyle = workbook.createCellStyle();
    // 单元格背景色填充
    HSSFPalette palette = workbook.getCustomPalette();
    palette.setColorAtIndex((short) 0x8, (byte) 0, (byte) 125, (byte) 170);
    cellStyle.setFillForegroundColor((short) 0x8);
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    return cellStyle;
  }
}