Apache POI 设置单元格边框不起作用

Excel 对独特的单元格格式/单元格样式以及字体都有限制。单元格样式和字体存储在工作簿级别。它们为所有工作表中的所有单元格共享。当前 Excel 版本限制为：

独特的单元格格式/单元格样式：65,490

和

独特的字体类型：1,024 种全局字体可供使用；每个工作簿 512 个

以前版本的 Excel（二进制 *.xls）的限制甚至更小。

因此，如果您为每个单元格创建单独的单元格样式，正如您所做的那样，您将很快达到极限。XSSF可能会在限制较大时起作用。但是以前版本的 Excel（二进制 *.xls）的限制甚至更小。这就是为什么它不使用HSSF.

那么什么做的是，根据需要为工作簿创建尽可能多的单元格样式，一旦在工作簿水平。在单元格填充过程之外执行此操作。在创建单元格本身时，单元格样式只能使用cell.setCellStyle. 但它无法在那时被创建。

让我们用一个完整的例子来说明这一点。它部分使用了您的代码，populateExcelData但我只能希望它符合您的想法，因为您没有提供完整的示例。

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.util.GregorianCalendar;
import java.util.List;
import java.util.ArrayList;

import org.apache.commons.collections4.CollectionUtils;

public class CreateExcel {

 private Workbook workbook;
 private CellStyle textStyle;
 private CellStyle dateStyle;
 private CellStyle numberStyle;
 private CellStyle headerStyle;

 public CreateExcel(String type, String path, List<List<Object>> headerData, List<List<Object>> excelData) {
  try {
   this.workbook = ("HSSF".equals(type)) ? new HSSFWorkbook() : new XSSFWorkbook(); 

   DataFormat dataFormat = workbook.createDataFormat();

   this.textStyle = workbook.createCellStyle();
   setCellStyleAllBorders(textStyle);

   this.dateStyle = workbook.createCellStyle();
   dateStyle.setDataFormat(dataFormat.getFormat("DDDD, MMMM, DD, YYYY"));
   setCellStyleAllBorders(dateStyle);

   this.numberStyle = workbook.createCellStyle();
   numberStyle.setDataFormat(dataFormat.getFormat("#,##0.00 \" Coins\""));
   setCellStyleAllBorders(numberStyle);

   this.headerStyle = workbook.createCellStyle();
   Font headerFont = workbook.createFont();
   headerFont.setBold(true);
   headerFont.setColor(IndexedColors.WHITE.getIndex());
   headerStyle.setFont(headerFont);
   headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
   headerStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex());
   setCellStyleAllBorders(headerStyle);

   Sheet sheet = workbook.createSheet(); 

   int rowNum = 0;
   rowNum = populateExcelData(workbook, sheet, rowNum, headerData, 1, true);

   rowNum = populateExcelData(workbook, sheet, rowNum, excelData, 1, false);
   
   System.out.println(rowNum);

   for (int c = 0; c < 256; c++) {
    sheet.autoSizeColumn(c); // this is very time consuming, would be better one can set column widths using Sheet.setColumnWidth(int columnIndex, int width) directly
   }

   FileOutputStream fileout = new FileOutputStream(path);
   this.workbook.write(fileout);
   fileout.close();
   this.workbook.close();

  } catch (Exception ex) {
   ex.printStackTrace(); 
  }
 }

 private int populateExcelData(Workbook workbook, Sheet sheet, int rowNum, List<List<Object>> excelData, Integer colPadding, Boolean isHeader) {
  if (CollectionUtils.isNotEmpty(excelData)) {
   int cellNum = 0;
   Row row;
   for (List<Object> objects : excelData) {
    cellNum = colPadding;
    row = sheet.createRow(rowNum++);
    Cell cell;
    for (Object object : objects) {
     cell = row.createCell(cellNum++);
     setCellValueAndStyle(cell, object, isHeader);
    }
   }
  }
  return rowNum;
 }

 private void setCellValueAndStyle(Cell cell, Object object, Boolean isHeader) {
  if (object instanceof String) {
   cell.setCellValue((String) object);
   if (!isHeader) cell.setCellStyle(this.textStyle);
  } else if (object instanceof Double) {
   cell.setCellValue((Double) object);
   if (!isHeader) cell.setCellStyle(this.numberStyle);
  } else if (object instanceof GregorianCalendar) {
   cell.setCellValue((GregorianCalendar) object);
   if (!isHeader) cell.setCellStyle(this.dateStyle);
  }
  if (isHeader) cell.setCellStyle(this.headerStyle);
 }

 private void setCellStyleAllBorders(CellStyle cellStyle) {
  cellStyle.setBorderBottom(BorderStyle.THIN);
  cellStyle.setBorderLeft(BorderStyle.THIN);
  cellStyle.setBorderRight(BorderStyle.THIN);
  cellStyle.setBorderTop(BorderStyle.THIN);
 }

 public static void main(String[] args) throws Exception {
  List<List<Object>> headerData = new ArrayList<>();
  List<Object> headerRow = new ArrayList<>();
  headerRow.add("Text"); headerRow.add("Value"); headerRow.add("Date");
  headerData.add(headerRow);
  headerRow = new ArrayList<>();
  headerRow.add("not formatted"); headerRow.add("in Coins"); headerRow.add("as long date");
  headerData.add(headerRow);

  List<List<Object>> excelData = new ArrayList<>();
  for (int r = 1; r < 1000; r++) {
   List<Object> excelRow = new ArrayList<>();
   excelRow.add("Text" + r); excelRow.add(123.45 * r); excelRow.add(new GregorianCalendar(2020, 0, r));
   excelData.add(excelRow);
  }

  CreateExcel test = new CreateExcel("HSSF", "./Excel.xls", headerData, excelData);
  test = new CreateExcel("XSSF", "./Excel.xlsx", headerData, excelData);
 }
}