java - Apache POI : How to add Diagonal Border

Question

我想使用 Apache POI 来绘制 Diagonal Border 但我找不到任何函数可以支持该功能。

我试过这个 setBorderDiagonal但它不起作用。

XSSFSheetConditionalFormatting my_cond_format_layer = my_sheet.getSheetConditionalFormatting();

XSSFConditionalFormattingRule my_rule = my_cond_format_layer.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "14", "16");
XSSFConditionalFormattingRule my_rule_2 = my_cond_format_layer.createConditionalFormattingRule(ComparisonOperator.EQUAL, "33");
XSSFPatternFormatting fill_pattern_2 = my_rule_2.createPatternFormatting();
fill_pattern_2.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());

XSSFBorderFormatting borderFormatting = my_rule_2.createBorderFormatting();
borderFormatting.setDiagonalBorderColor(IndexedColors.BLUE.getIndex());
borderFormatting.setBorderDiagonal(BorderFormatting.BORDER_THICK);

ConditionalFormattingRule[] multiple_rules = {my_rule, my_rule_2};
CellRangeAddress[] my_data_range = {CellRangeAddress.valueOf("A1:A4")};
my_cond_format_layer.addConditionalFormatting(my_data_range, multiple_rules);

Answer 1

我不知道为什么直到现在XSSFCellStyle 中没有setBorderDiagonal。但是如果我们看一下其他 setBorder... 方法的代码，那么我们可以制作我们自己的 setBorderDiagonal。

例子:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;

import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.model.ThemesTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorder;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorderPr;

import org.openxmlformats.schemas.spreadsheetml.x2006.main.STBorderStyle;

import java.io.FileOutputStream;
import java.io.IOException;


class CellDiagonalBorders {

 private static CTBorder getCTBorder(StylesTable _stylesSource, CTXf _cellXf ){
  CTBorder ct;
  if(_cellXf.getApplyBorder()) {
   int idx = (int)_cellXf.getBorderId();
   XSSFCellBorder cf = _stylesSource.getBorderAt(idx);
   ct = (CTBorder)cf.getCTBorder().copy();
   } else {
    ct = CTBorder.Factory.newInstance();
   }
  return ct;
 }

 public static void setBorderDiagonal(short border, StylesTable _stylesSource, CTXf _cellXf , ThemesTable _theme) {
  CTBorder ct = getCTBorder(_stylesSource, _cellXf);
  CTBorderPr pr = ct.isSetDiagonal() ? ct.getDiagonal() : ct.addNewDiagonal();
  if(border == BorderFormatting.BORDER_NONE) {
   ct.unsetDiagonal();
  }
  else {
   ct.setDiagonalDown(true);
   ct.setDiagonalUp(true);
   pr.setStyle(STBorderStyle.Enum.forInt(border + 1));
  }
  int idx = _stylesSource.putBorder(new XSSFCellBorder(ct, _theme));    
  _cellXf.setBorderId(idx);
  _cellXf.setApplyBorder(true);   
 }

 public static void main(String[] args) {
  try {

   Workbook wb = new XSSFWorkbook();

   Sheet sheet = wb.createSheet("Sheet1");
   Cell cell = sheet.createRow(2).createCell(2);

   CellStyle style = wb.createCellStyle();

   StylesTable _stylesSource = ((XSSFWorkbook)wb).getStylesSource();
   ThemesTable _theme = _stylesSource.getTheme();
   CTXf _cellXf = ((XSSFCellStyle)style).getCoreXf();

   setBorderDiagonal(BorderFormatting.BORDER_THICK, _stylesSource, _cellXf, _theme);

   cell.setCellStyle(style);

   FileOutputStream fileOut = new FileOutputStream("CellDiagonalBorders.xlsx");
   wb.write(fileOut);

  } catch (IOException ioex) {
  }
 }
}

2018 年 2 月 23 日编辑:上面的代码适用于以前版本的 apache poi。以下代码适用于实际的最新稳定版本 3.17:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.streaming.*;

import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;

import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.model.ThemesTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorder;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorderPr;

import org.openxmlformats.schemas.spreadsheetml.x2006.main.STBorderStyle;

import java.io.FileOutputStream;
import java.io.IOException;


class CellDiagonalBorders {

 private static CTBorder getCTBorder(StylesTable _stylesSource, CTXf _cellXf ) {
  CTBorder ct;
  if(_cellXf.getApplyBorder()) {
   int idx = (int)_cellXf.getBorderId();
   XSSFCellBorder cf = _stylesSource.getBorderAt(idx);
   ct = (CTBorder)cf.getCTBorder().copy();
   } else {
    ct = CTBorder.Factory.newInstance();
   }
  return ct;
 }

 public static void setBorderDiagonal(BorderStyle border, StylesTable _stylesSource, CTXf _cellXf, ThemesTable _theme) {
  CTBorder ct = getCTBorder(_stylesSource, _cellXf);
  CTBorderPr pr = ct.isSetDiagonal() ? ct.getDiagonal() : ct.addNewDiagonal();
  if(border == BorderStyle.NONE) {
   ct.unsetDiagonal();
  }
  else {
   ct.setDiagonalDown(true);
   ct.setDiagonalUp(true);
   pr.setStyle(STBorderStyle.Enum.forInt(border.getCode() + 1));
  }
  int idx = _stylesSource.putBorder(new XSSFCellBorder(ct, _theme, _stylesSource.getIndexedColors()));    
  _cellXf.setBorderId(idx);
  _cellXf.setApplyBorder(true);   
 }

 public static void main(String[] args) throws Exception {
  //Workbook wb = new XSSFWorkbook();
  Workbook wb = new SXSSFWorkbook(SXSSFWorkbook.DEFAULT_WINDOW_SIZE);

  CellStyle style = wb.createCellStyle();
  style.setBorderTop(BorderStyle.THICK);
  style.setBorderBottom(BorderStyle.THICK);

  StylesTable _stylesSource = null;
  if (wb instanceof SXSSFWorkbook) {
   _stylesSource = ((SXSSFWorkbook)wb).getXSSFWorkbook().getStylesSource();
  } else if (wb instanceof XSSFWorkbook) {
   _stylesSource = ((XSSFWorkbook)wb).getStylesSource();
  }
  if (_stylesSource != null) {
   ThemesTable _theme = _stylesSource.getTheme();
   CTXf _cellXf = ((XSSFCellStyle)style).getCoreXf();
   setBorderDiagonal(BorderStyle.DOUBLE, _stylesSource, _cellXf, _theme);
  }

  style.setBorderLeft(BorderStyle.THICK);
  style.setBorderRight(BorderStyle.THICK);

  Sheet sheet = wb.createSheet("Sheet1");

  for (int r = 1; r < 1000; r++) {
   Cell cell = sheet.createRow(r).createCell(2);
   cell.setCellStyle(style);
  }

  FileOutputStream fileOut = new FileOutputStream("CellDiagonalBorders.xlsx");
  wb.write(fileOut);
  wb.close();
  if (wb instanceof SXSSFWorkbook) ((SXSSFWorkbook)wb).dispose();

 }
}