apache POI MS Office Excel无法打开字体颜色集Excel文件

Question

我在apache poi上遇到了一个奇怪的问题。

我正在使用apache poi 3.17创建Excel文件。如果我设置字体颜色，那么生成的excel文件不是用最新的MS Office Excel查看器打开的，而是用Libre Office和Mac OS Numbers应用程序打开的。

但是，如果我注释掉'urlFont.setColor((short)Color.BLACK.getRGB())‘行，那么生成的文件将使用Ms Office Excel查看器(以及其他应用程序)打开。

有人遇到过这个问题吗？

 import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.xssf.usermodel.*;

import java.awt.*;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;


public class WriteExcelBasic {
    public static void main(String[] args) throws IOException {

        String excelFileName = "/Users/home/Test3.xlsx";
        FileOutputStream fos = new FileOutputStream(excelFileName);


        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFCellStyle style = wb.createCellStyle();

        XSSFSheet sheet = wb.createSheet("sheet");
        Font urlFont = wb.createFont();
        urlFont.setFontHeight((short)(9*20));
       // urlFont.setUnderline((byte)10);
        //urlFont.setBold(true);
       urlFont.setFontName("Arial");
        urlFont.setItalic(true);
      urlFont.setColor((short)Color.BLACK.getRGB());// commenting out this line will work



        style.setFont(urlFont);
        for (int r = 0; r < 3; r++) {
            XSSFRow row = sheet.createRow(r);


            for (int c = 0; c < 3; c++) {
                XSSFCell cell = row.createCell(c);


                Hyperlink link = wb.getCreationHelper().createHyperlink(HyperlinkType.URL);
                String ss = "http://news.google.com/news/headlines?ned=us&hl=en";
                //String ss = "swasdqde";
               link.setAddress(ss);
                cell.setHyperlink(link);
                cell.setCellValue(ss);
                if(r == 1) {
                    System.out.println("In yellow");
                    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    style.setFillForegroundColor(new XSSFColor(Color.YELLOW));
                } else {
                    System.out.println("In red");
                    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    style.setFillForegroundColor(new XSSFColor(Color.RED));
                }
                cell.setCellStyle(style);


            }
        }

        try (ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
            wb.write(baos);
            byte[] myByteArray = baos.toByteArray();
            fos.write(myByteArray);
            fos.flush();
        }
        finally {
            wb.close();
            fos.close();
        }
    }
}

Answer 1

我将Font更改为XSFFFont，并在该字体中使用XSSFCOLOR，它正在工作。

import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.xssf.usermodel.*;

import java.awt.*;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;


public class WriteExcelBasic {
    public static void main(String[] args) throws IOException {

        String excelFileName = "/Users/home/Test3.xlsx";
        FileOutputStream fos = new FileOutputStream(excelFileName);


        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFCellStyle style = wb.createCellStyle();

        XSSFSheet sheet = wb.createSheet("sheet");
 XSSFFont urlFont = wb.createFont();// changed lines
 urlFont.setFontHeight((short)(9*20));
       // urlFont.setUnderline((byte)10);
        //urlFont.setBold(true);
       urlFont.setFontName("Arial");
        urlFont.setItalic(true);
      urlFont.setColor(new XSSFColor(Color.BLUE)); // changed lines



        style.setFont(urlFont);
        for (int r = 0; r < 3; r++) {
            XSSFRow row = sheet.createRow(r);


            for (int c = 0; c < 3; c++) {
                XSSFCell cell = row.createCell(c);


                Hyperlink link = wb.getCreationHelper().createHyperlink(HyperlinkType.URL);
                String ss = "http://news.google.com/news/headlines?ned=us&hl=en";
                //String ss = "swasdqde";
               link.setAddress(ss);
                cell.setHyperlink(link);
                cell.setCellValue(ss);
                if(r == 1) {
                    System.out.println("In yellow");
                    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    style.setFillForegroundColor(new XSSFColor(Color.YELLOW));
                } else {
                    System.out.println("In red");
                    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    style.setFillForegroundColor(new XSSFColor(Color.RED));
                }
                cell.setCellStyle(style);


            }
        }

        try (ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
            wb.write(baos);
            byte[] myByteArray = baos.toByteArray();
            fos.write(myByteArray);
            fos.flush();
        }
        finally {
            wb.close();
            fos.close();
        }
    }
}

Answer 2

虽然已经给出了XSSF的正确方法，但也存在一个问题，为什么Font.setColor没有像预期的那样工作。

这个问题的答案是，对于Font.setColor(short color)，short color是，而不是 RGB颜色值，而是IndexedColor调色板中的索引。这是一种来自HSSF和BIFF文件格式的旧方法。调色板中有64个IndexedColors，其中8个(32到39)可自由用于自定义颜色。XSSF也可以使用此调色板，但不能在其中存储自定义颜色。在这一点上，Excel 2016更能容忍。这就是在那里使用RGB值时出现错误的原因，这些值可能都大于220 ;-)。

下面的示例显示了如何在Enum IndexedColors和HSSFPalette中使用Font.setColor(short color)及其等效项来存储自定义颜色。

import java.io.FileOutputStream;

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

public class CreateExcelFontColor {

 public static void main(String[] args) throws Exception {

  //Workbook workbook = new XSSFWorkbook();
  Workbook workbook = new HSSFWorkbook();

  Sheet sheet = workbook.createSheet();

  for (int c = 0; c < 221; c++) { //color index from 0 to 220
                                  //greater than 220 leads to an error while opening in Excel XSSF

   CellStyle style = workbook.createCellStyle();
   Font font = workbook.createFont();

   font.setColor((short)c);

   style.setFont(font);

   Row row = sheet.createRow(c);
   Cell cell = row.createCell(0);
   cell.setCellValue("Font color index " + c);

   cell.setCellStyle(style);

   IndexedColors indexedcolor = null;
   try {
    indexedcolor = IndexedColors.fromInt(c);
   } catch (IllegalArgumentException ex) {}

   cell = row.createCell(1);
   if (indexedcolor != null) {
    cell.setCellValue("IndexedColor." + indexedcolor.toString());
   } else {
    cell.setCellValue("No IndexedColor");
   }

   cell.setCellStyle(style);

   if (workbook instanceof HSSFWorkbook) {
    if (indexedcolor == null) {

     HSSFPalette palette = ((HSSFWorkbook)workbook).getCustomPalette();

     byte[] rgb = new byte[3];
     new java.util.Random().nextBytes(rgb);
     palette.setColorAtIndex((short)c, rgb[0], rgb[1], rgb[2]);

     cell = row.createCell(2);
     cell.setCellValue("New font color index " + c 
      + " RGB=" + String.format("%02X", rgb[0]) + String.format("%02X", rgb[1]) + String.format("%02X", rgb[2]));

     cell.setCellStyle(style);
    }
   }

  }

  if (workbook instanceof HSSFWorkbook) {
   workbook.write(new FileOutputStream("CreateExcelFontColor.xls"));
  } else if (workbook instanceof XSSFWorkbook) {
   workbook.write(new FileOutputStream("CreateExcelFontColor.xlsx"));
  }
  workbook.close();

 }

}