Apache POI autoSizeColumn Resizes Incorrectly

Question

I'm using Apache POI in java to create an excel file. I fill in the data then try to autosize each column, however the sizes are always wrong (and I think consistent). The first two rows are always(?) completely collapsed. When I autosize the columns in excel, it works perfectly.

No blank cells are being written (I believe) and the resizing is the last thing I do.

Here's the relevant code: This is a boiled down version without error handling, etc.

public static synchronized String storeResults(ArrayList<String> resultList, String file) {
    if (resultList == null || resultList.size() == 0) {
        return file;
    }
    FileOutputStream stream = new FileOutputStream(file);

    //Create workbook and result sheet
    XSSFWorkbook book = new XSSFWorkbook();
    Sheet results = book.createSheet("Results");

    //Write results to workbook
    for (int x = 0; x < resultList.size(); x++) {
        String[] items = resultList.get(x).split(PRIM_DELIM);

        Row row = results.createRow(x);
        for (int i = 0; i < items.length; i++) {
            row.createCell(i).setCellValue(items[i]);
        }
    }

    //Auto size all the columns
    for (x = 0; x < results.getRow(0).getPhysicalNumberOfCells(); x++) {
        results.autoSizeColumn(x);
    }

    //Write the book and close the stream
    book.write(stream);
    stream.flush();
    stream.close();

    return file;
}

I know there are a few questions out there similar, but most of them are simply a case of sizing before filling in the data. And the few that aren't are more complicated/unanswered.

EDIT: I tried using a couple different fonts and it didn't work. Which isn't too surprising, as no matter what the font either all the columns should be completely collapsed or none should be.

Also, because the font issue came up, I'm running the program on Windows 7.

SOLVED: It was a font issue. The only font that I found that worked was Serif.

Have you checked these two Q&A? [1](http://stackoverflow.com/questions/11573993/setting-column-width-in-apache-poi/11574647), [2](http://stackoverflow.com/questions/7412457/crash-when-calling-autosizecolumn-on-worksheet-when-run-under-linux-servicemix/12517025#12517025) ... it has probably something todo with the used font — kiwiwings, Jun 05 '13 at 15:29
@kiwiwings I use the default font (Calibri) which is in Windows just fine. I'll try changing the font to something else and post the results, but I didn't think it would be font. — Jaws212, Jun 05 '13 at 15:42
Can you try using the `Serif` font (or verify if you already have) and see if that works? I have a feeling this is related to the fonts that Java is aware of. — Shaded, Jun 05 '13 at 18:57
Serif worked. I never tried something that generic, but I guess I should have. Thanks — Jaws212, Jun 05 '13 at 20:14
Have you checked that Java can see all the fonts your system has? — Gagravarr, Jun 05 '13 at 21:51

score 15 · Accepted Answer · answered Jun 14 '13 at 14:52

15

Just to make an answer out of my comment. The rows couldn't size properly because Java was unaware of the font you were trying to use this link should help if you want to install new fonts into Java so you could use something fancier. It also has the list of default fonts that Java knows.

Glad this helped and you got your issue solved!

answered Jun 14 '13 at 14:52

Shaded

17,276
8
37
62

1

I am really curious what changed to make this happen and why I am only seeing it on our Windows 7 developments boxes. See my comment on Aldo's answer. – theblang Jul 09 '13 at 18:56
1

@mattblang See Sebastien's answer for an explanation. We had this issue also only on our development boxes (Windows), but not on integration machines (Linux). – t0r0X Jul 01 '15 at 15:40
Link is broken :( – luca.vercelli Mar 08 '23 at 09:07

score 7 · Answer 2 · edited May 12 '19 at 10:13

7

This is probably related to this POI Bug which is related to Java Bug JDK-8013716: Renderer for Calibri and Cambria Fonts fails since update 45.

In this case changing the Font or using JRE above 6u45 / 7u21 should fix the issue.

You can also mtigitate the issue and avoid the columns from being totally collapsed by using a code like this:

    sheet.autoSizeColumn(x);

    if (sheet.getColumnWidth(x) == 0) {
      // autosize failed use MIN_WIDTH
      sheet.setColumnWidth(x, MIN_WIDTH);
    }

edited May 12 '19 at 10:13

Mike

706
7
16

answered Sep 14 '14 at 19:40

Sebastien

5,506
4
27
37

That's the solving answer, thanks!!! Upgrading, downgrading or patching the JRE do solve my issue, since Calibri is the default Font in the current Excel-Version. – t0r0X Jul 01 '15 at 15:34
In my case the column remains collapsed even after setColumnWidth... what would be a typical MIN_WIDTH? – golimar Oct 19 '16 at 10:49

score 7 · Answer 3 · answered Feb 16 '16 at 15:36

I was also running into this issue and this was my solution.

Steps:

Create workbook
Create spreadsheet
Create row
Create/Set font to "Arial"
Create/Set style with font
Create/Set cell with value and style
autoSizeColumn
Create File

Code:

// initialize objects
XSSFWorkbook workbook = new XSSFWorkbook(); 
XSSFSheet spreadsheet = workbook.createSheet(sheetName);
XSSFRow row = spreadsheet.createRow(0);
XSSFCell cell;

// font/style
XSSFFont font = workbook.createFont();
font.setFontName("Arial");
XSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);

// create/set cell & style
cell = row.createCell(0);
cell.setCellValue("New Cell");
cell.setCellStyle(style);

// auto size
spreadsheet.autoSizeColumn(0);

// create file
File aFile = new File("Your Filename");
FileOutputStream out = new FileOutputStream(aFile);
workbook.write(out);

Resources:

http://www.tutorialspoint.com/apache_poi/index.htm

Actually, adding a font before/after writing the content, solves the issue — sancho21, Apr 11 '16 at 11:37

score 4 · Answer 4 · edited Apr 24 '17 at 08:34

4

I had a similar issue on Windows 7.

I was using the Calibri font (that is supported in my JVM). With that font the getBounds().getWidth() of the java.awt.font.TextLayout used by the autoSizeColumn() POI method returns 0.

Changing the font to Calibri-Regular solved the issue in my case.

edited Apr 24 '17 at 08:34

Tshilidzi Mudau

7,373
6
36
49

answered Jun 18 '13 at 21:34

Aldo

41
1

I was also using Calibri, which worked when I first wrote the code. The only thing that changed, that I can think of, is our Java version from 6 to 7. The weird thing is that our Linux boxes still work fine, only our development Windows 7 environments were affected. – theblang Jul 09 '13 at 18:43
Weird, `Verdana` doesn't work quite right on the production Linux boxes. I am going to try just using `Calibri-Regular` like you mentioned. – theblang Jul 09 '13 at 20:16
This did apparently solve the issue, but in fact didn't, because "Calibri-Regular" being not installed, it was replaced with "Arial", which looks different. Sebastien's answer provided the solution in my case. – t0r0X Jul 01 '15 at 15:37

score 2 · Answer 5 · answered Jan 20 '16 at 10:21

Here are my 2 cents -

I was using the default font (Arial in my case) using it to make certain fields bold in the xls. Worked like a charm in Windows along with autoSizeColumn() function.

Linux wasn't that forgiving. The auto-sizing was improper at places. After going through this thread and other I came up with the following solution.

I copied the Arial font's .tff files into the JAVA/jre/lib/fonts directory and re-ran the application. Worked just fine.

score 2 · Answer 6 · answered Jun 10 '16 at 19:17

2

I've used Helvetica font trying to replace Arial font (in fact, Helvetica is similar to Arial).

XSSFFont font = wb.createFont();
font.setFontName("Helvetica");

answered Jun 10 '16 at 19:17

Sergio A Guzmán

41
2
4

score 1 · Answer 7 · answered Apr 02 '16 at 13:32

I found that auto-sizing didn't make the column wide enough when the widest string began with spaces, e.g.

cell.setCellValue("   New Cell");

This can be fixed by using indentations instead, e.g.

// font/style
XSSFFont font = workbook.createFont();
font.setFontName("Arial");
XSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
style.setIndention((short)2);

// create/set cell & style
cell = row.createCell(0);
cell.setCellValue("New Cell");
cell.setCellStyle(style);

// auto size
spreadsheet.autoSizeColumn(0);

Vikki · Answer 8 · 2018-05-10T06:38:27.190

The following works for me.

I set the font and use autoSizeColumn() after enter all the data.

    public static XSSFWorkbook createExcel(List<ChannelVodFileInfoList> resList) {
    XSSFWorkbook hwb = new XSSFWorkbook();
    String[] title = { "1", "2", "3", "4"};

    XSSFSheet sheet = hwb.createSheet("dataStats");

    XSSFRow firstrow = sheet.createRow(0);
    for (int i = 0; i < title.length; i++) {
        XSSFCell xh = firstrow.createCell(i);
        xh.setCellValue(title[i]);
    }

    if (resList == null || resList.size() == 0) {
        return hwb;
    }

    for (int i = 0; i < resList.size(); i++) {
        ChannelVodFileInfoList doTemp = resList.get(i);
        XSSFRow row = sheet.createRow(i + 1);
        for (int j = 0; j < title.length; j++) {
            XSSFCell cell = row.createCell(j);
            Font font111 = hwb.createFont();
            font111.setBoldweight(Font.BOLDWEIGHT_NORMAL); 
            XSSFCellStyle cellStyle111 = hwb.createCellStyle();
            cellStyle111.setFont(font111);
            cell.setCellStyle(cellStyle111);

            if (j == 0) { 
                cell.setCellValue(dateStr);
            } else if (j == 1) {  
                cell.setCellValue(doTemp.getChannelName()); 
            }else if (j == 2) {  
                cell.setCellValue(doTemp.getBitrate()); 
            }else if (j == 3) {  
                cell.setCellValue(doTemp.getWh()); 
            }
         }

        for (int j = 0; j < title.length; j++) {
           sheet.autoSizeColumn(j);
        }

    return hwb;
}

score 0 · Answer 9 · answered Jul 27 '21 at 08:36

A Hacky Way

My case was after auto-sizing, those cells were slightly smaller than the width of the actual content.

What I did is just added extra 200 widths after the autoSizeColumn method is being called.

sheet.autoSizeColumn(columnIndex);
sheet.setColumnWidth(columnIndex, sheet.getColumnWidth(columnIndex) + 200);

This worked fine for me :)

score 0 · Answer 10 · answered Feb 18 '22 at 21:34

0

In the event users are running into the issue where they have a sheet with both cells and merged cells, Gagravarr's answer on this post really helped me:

https://stackoverflow.com/a/31425163/9407809

Posting here for future users looking for this.

answered Feb 18 '22 at 21:34

Kevin Crum

195
2
16

Apache POI autoSizeColumn Resizes Incorrectly

10 Answers10

A Hacky Way

Linked

Related