Bug 43091

Summary: Applying a font to a HSSFRichTextString can lead to that the file can't be loaded by Excel any more
Product: POI Reporter: Jan Dostert <jan.dostert>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED WORKSFORME    
Severity: major    
Priority: P2    
Version: 3.0-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Testcase

Description Jan Dostert 2007-08-11 06:33:53 UTC
Hi,

applying a font to a HSSFRichTextString with poi-3.0.1-FINAL-20070705.jar can
lead to that the file can't be loaded by Excel any more (I tried Excel 2003 and
Excel 2007).

There are no problems in saving the file. However, when opening the file with
Excel, I get the error message "Excel found unreadable content in foo.xls".

Attached a small test case  and some sample data which reproduce the problem.
The created excel file works fine if the font is not applied to the text in row
2775. But if the font is applied to the text in row 2775, the excel file can't
be loaded any more.
There is nothing special about the particular String in row 2775. When applying
the font to this String only, the excel file can be loaded as well. It's just in
this scenario, when applying the fonts to line 1 - 2774, the excel file can be
loaded. But when applying the fonts to line 1 - 2775, the excel file can't be
loaded any more.  

Actually this problem occurs all the time when I export a big table. As a
workaround, I do not apply fonts at all. 

import java.io.BufferedReader;
import java.io.FileOutputStream;
import java.io.FileReader;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ApplyFont {

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

        HSSFWorkbook workBook = new HSSFWorkbook();
        HSSFSheet sheet = workBook.createSheet();
        HSSFFont font = workBook.createFont();
       
        BufferedReader br = new BufferedReader(new FileReader("testdata.txt"));
        
        String line;
        short currentRow = 0;
        
        while ( (line = br.readLine()) != null) {
            HSSFRow row = sheet.createRow(currentRow++);
            HSSFCell cell = row.createCell((short)0);
                
            HSSFRichTextString text = new HSSFRichTextString(line); 
            
            // if we don't apply the font to this particular line,
            // everything works fine 
            // if (currentRow != 2775) {
                text.applyFont(0, line.length(), font);
            // }
            
            cell.setCellValue(text);
        }
        
        workBook.write(new FileOutputStream("foo.xls"));
    }
}

Regards,
Jan
Comment 1 Jan Dostert 2007-08-11 06:35:26 UTC
Created attachment 20646 [details]
Testcase
Comment 2 Nick Burch 2007-08-15 06:11:34 UTC
Alas cryptic error messages from Excel aren't much help for debugging :/

Probably your best bet is to apply the font change to the file in excel, and to
a copy of the file in POI. Then, compare the two with the poi debugging tools
(hssf.dev and poifs.dev) to try to spot the difference, which might give a clue
to what Excel doesn't like about the POI edited file
Comment 3 David Fisher 2007-08-15 06:17:49 UTC
We had similar trouble when migrating from development to a staging server.

We had to make sure that you really have the font in the proper location in the
java setup on the machine where it is created by POI.
Comment 4 admin 2007-08-25 15:06:43 UTC
Is this bug fixed? What is the resolution?
Comment 5 Dominik Stadler 2015-05-01 21:03:46 UTC
I could not reproduce this in a quick try, likely the Font setup of Java is related here and we cannot do much about it from within POI. Maybe also some changes in newer versions improved this. 

If you still see this problem and have a reproducable test-case please reopen this issue or file a new bug.