POI Excel单元格高亮显示”修复的记录：格式从 /xl/style.xml部分（样式）

Question

在执行结束后，当我使用Ms-excel-2007打开处理过的文件（.xlsx文件）时，我一直在处理excel表格中的突出显示单元格。我面临两个弹出窗口。此处显示指定的代码和图像：

  HSSFWorkbook xlsWB = new HSSFWorkbook(file1InputStream);

  XSSFWorkbook xlsxWB = new XSSFWorkbook(file2InputStream);

  CellStyle xlsxStyle = getCellStyle(xlsxWB);

  int numbeoOfSheetsFile1 = xlsWB.getNumberOfSheets();
  int numbeoOfSheetsFile2 = xlsxWB.getNumberOfSheets();

  for (int sheetIndex = 0; sheetIndex < numbeoOfSheetsFile1 && sheetIndex < numbeoOfSheetsFile2; sheetIndex++) {

   HSSFSheet sheetFile1 = xlsWB.getSheetAt(sheetIndex);
   XSSFSheet sheetFile2 = xlsxWB.getSheetAt(sheetIndex);

   int noOfRowsSheetFile1 = sheetFile1.getLastRowNum();
   int noOfRowsSheetFile2 = sheetFile2.getLastRowNum();

   if (noOfRowsSheetFile1 < noOfRowsSheetFile2) {
    for (int vRow = noOfRowsSheetFile1; vRow <= noOfRowsSheetFile2 - 1; vRow++) {
     sheetFile2.createRow(vRow).setRowStyle(xlsxStyle);
     //sheetFile2.getRow(vRow).setRowStyle(xlsxStyle);
    }
   }
   if (noOfRowsSheetFile1 > noOfRowsSheetFile2) {
    for (int vRow = noOfRowsSheetFile2 + 1; vRow <= noOfRowsSheetFile1; vRow++) {
     sheetFile2.createRow(vRow).setRowStyle(xlsxStyle);
     //sheetFile2.getRow(vRow).setRowStyle(xlsxStyle);
    }
   }

   for (int vRow = 0; vRow <= noOfRowsSheetFile1; vRow++) {
    HSSFRow rwFile1 = sheetFile1.getRow(vRow);
    XSSFRow rwFile2 = sheetFile2.getRow(vRow);

    int noOfColSheetFile1 = sheetFile1.getRow(vRow).getLastCellNum();

    int noOfColSheetFile2 = 0;
    try {
     noOfColSheetFile2 = sheetFile2.getRow(vRow).getLastCellNum();
    } catch (NullPointerException e) {
     rwFile2 = sheetFile2.createRow(vRow);
     noOfColSheetFile2 = 0;
    }

    // Coloring of mismatch columns
    if (noOfColSheetFile1 < noOfColSheetFile2) {
     for (int vCol = noOfColSheetFile1 + 1; vCol <= noOfColSheetFile2; vCol++) {
      rwFile2.createCell(vCol);
      //rwFile2.getCell(vCol).setCellStyle(xlsxStyle);
     }
    }


    if (noOfColSheetFile1 > noOfColSheetFile2) {
     for (int vCo2 = noOfColSheetFile2 + 1; vCo2 <= noOfColSheetFile1; vCo2++) {
      rwFile2.createCell(vCo2);
      //rwFile2.getCell(vCo2).setCellStyle(xlsxStyle);
     }
    }
    for (int vCol = 0; vCol < noOfColSheetFile1; vCol++) {
     //System.out.println("vCol>>" + vCol + "--vRow>>" + vRow);
     HSSFCell cellFile1 = rwFile1.getCell(vCol);
     XSSFCell cellFile2 = rwFile2.getCell(vCol);
     String cellFile1Value = null;
     if (null != cellFile1) {
      cellFile1Value = cellFile1.toString();

     }
     String cellFile2Value = null;
     if (null != cellFile2) {
      cellFile2Value = cellFile2.toString();
     }

  if ((null == cellFile1Value && null != cellFile2Value) || (null != cellFile1Value && null == cellFile2Value)          || (null != cellFile1Value && cellFile1Value.compareTo(cellFile2Value) != 0)) {

  if ((null != cellFile1Value && !cellFile1Value.isEmpty()) || (null != cellFile2Value && !          cellFile2Value.isEmpty())) { 
    CellValues cellValues = new CellValues();
    cellValues.setValue1(cellFile1Value);
    cellValues.setValue2(cellFile2Value);
    cellValues.setCellRow(vRow);
    cellValues.setCellColumn(vCol);
    scenarioResultDetails.addDifference(cellValues);
    XSSFCell cellDiff = rwFile2.getCell(vCol);
    if (null == cellDiff) {
     cellDiff = rwFile2.createCell(vCol);
    }
    cellDiff.setCellStyle(xlsxStyle);
   }
  }
    }
   }
  }
  String fileDiff = file2.replace(".xls", "_diff.xls");
  FileOutputStream fileOut = new FileOutputStream(fileDiff);
  xlsxWB.write(fileOut);
  if (null != fileOut) {
   try {
    fileOut.close();
   } catch (Exception e) {
   }
  }
  if (scenarioResultDetails.getDifferencesList().size() > 0) {
   scenarioResultDetails.setResult(false);
   scenarioResultDetails.setResultText("Not Matched");
   scenarioResultDetails.setDiffExcel(fileDiff);
  } else {
   scenarioResultDetails.setResult(true);
   scenarioResultDetails.setResultText("Matched");
  }
  return scenarioResultDetails;
 }

 public static void openFile(String fileName){
  ReportGenerator.getDriver().get("file://"+fileName);
 }
 private static CellStyle getCellStyle(XSSFWorkbook xssfWorkbook){
  CellStyle style = xssfWorkbook.createCellStyle();
  Font font = xssfWorkbook.createFont();
  font.setColor(IndexedColors.BLACK.getIndex());
  style.setFont(font);
  style.setFillForegroundColor(HSSFColor.YELLOW.index);
  style.setFillBackgroundColor(HSSFColor.YELLOW.index);
  style.setFillPattern(CellStyle.ALIGN_RIGHT);
  return style;
 }
}

Answer 1

这种行为仍然（或再次）存在。我使用的是NuGet NPOI包的2.4.1版，它显然有一个FontHeight属性错误修复，但它也引入了这个。

问题是字体大小与xssfWorkbook返回的字体不一致。createFont（）；它太小了。您必须这样明确地设置它：

    IFont font = excel.CreateFont();
    font.FontHeightInPoints = 11;

另外，我在重写以前修复过的文件时也遇到了类似的错误。确保每次更改代码时都写入一个干净/新的文件，以避免以前的损坏。

完整示例：

    /// <summary>
    /// Return style for header cells.
    /// </summary>
    /// <returns></returns>
    private static ICellStyle GetHeaderStyle(this XSSFWorkbook excel)
    {
        IFont font = excel.CreateFont();
        font.IsBold = true;
        //Added this explicitly, initial font size is tiny
        font.FontHeightInPoints = 11;

        ICellStyle style = excel.CreateCellStyle();
        style.FillForegroundColor = IndexedColors.Grey25Percent.Index;
        style.FillPattern = FillPattern.SolidForeground;
        style.FillBackgroundColor = IndexedColors.Grey25Percent.Index;          

        style.BorderBottom = style.BorderLeft = style.BorderRight = style.BorderTop = BorderStyle.Thin;
        style.BottomBorderColor = style.LeftBorderColor = style.RightBorderColor = style.TopBorderColor = IndexedColors.Black.Index;

        style.SetFont(font);
        return style;
    }