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

Question

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

我正在使用apache POI3.17创建一个Excel文件。如果我设置了字体颜色，那么生成的excel文件不是用最新的MS Office excel viewer打开的，而是用Libre Office和Mac OS Numbers应用程序打开的。

但是如果我注释掉'urlFont.setColor((short)color.black.getrgb())'行，那么生成的文件将用Ms Office Excel viewer（以及其他应用程序）打开。

有人面对这个问题吗？

 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

我将字体更改为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();
        }
    }
}