Apache POI autoSizeColumn() not working right

Question

I am creating a program that writes information to an excel file using the apache poi. After I enter all of my data into the file I call the autoSizeColumn method on every column of the file. But it resizes the columns to the width of the last cell entered which is sometime not as big as the other cells in the column. I know I'm using it right and unfortunately I don't have Internet right now to post any code but I will update when I can.

Ok I hope I am using the code tags right but here it is :

public void writeFile() {
    Workbook wb = new HSSFWorkbook();
    Sheet s = wb.createSheet();
    try {

        FileOutputStream out = new FileOutputStream(
                this.getSelectedFile());
        // create a new workbook

        // create a new sheet

        // declare a row object reference
        Row r = null;
        // declare a cell object reference
        Cell c = null;
        // in case of plain ascii
        wb.setSheetName(0, "Street Light Report");
        // create a sheet with 30 rows (0-29)
        // int rownum;
        // short rownum;
        // PrintWriter printOut = new PrintWriter(
        // this.getSelectedFile());
        String[] colName = { "Light Id", "Flagged",
                "Malfunction", "Comments", "Location", "Date" };
        // printOut.println("Light Id,Flagged,Malfunction,Comments,Location,Date");
        Connections.connect();
        String[][] data = Connections.searchForFlagged();
        for (int i = 0; i < data.length; i++) {
            r = s.createRow(i);
        }
        r.setRowNum(0);
        for (int j = 0; j < 6; j++) {
            c = r.createCell(j);
            switch (j) {
            case 0:
                c.setCellValue(colName[j]);
                break;
            case 1:
                c.setCellValue(colName[j]);
                break;
            case 2:
                c.setCellValue(colName[j]);
                break;
            case 3:
                c.setCellValue(colName[j]);
                break;
            case 4:
                c.setCellValue(colName[j]);
                break;
            case 5:
                c.setCellValue(colName[j]);
                break;

            }
        }

        for (int i = 0; i < data.length; i++) {

            r.setRowNum(i + 1);
            for (int j = 0; j < data[i].length; j++) {

                c = r.createCell(j);

                // System.out.println(data[i][j]);

                switch (j) {
                case 0:
                    c.setCellType(Cell.CELL_TYPE_NUMERIC);
                    c.setCellValue(Integer.parseInt(data[i][j]));
                    break;
                case 1:
                    if (data[i][j].equals("true"))
                        c.setCellValue("Yes");
                    else
                        c.setCellValue("No");
                    break;
                case 2:
                    if (data[i][j].equals("I"))
                        c.setCellValue("Intermittent");
                    else
                        c.setCellValue("Not Functional");
                    break;
                case 3:
                    c.setCellValue(data[i][j]);
                    break;
                case 4:
                    c.setCellValue(data[i][j]);
                    break;
                case 5:
                    c.setCellValue(data[i][j]);
                    break;
                }

            }
        }
                                wb.getSheetAt(0).setPrintGridlines(true);
        for (int j = 0; j < 6; j++) {
            s.autoSizeColumn(j);
        }
        wb.write(out);
        out.close();
    } catch (Exception ex) {

    }
};

I would post an image here but I need rep. point and I just started here.

** Note that the Connections class is the JDBC class so that java can pull the information from th mySQL database it returns a 2D array of strings **

See the answer to [this question](http://stackoverflow.com/questions/16943493/apache-poi-autosizecolumn-resizes-incorrectly). — scaevity, Aug 02 '13 at 18:33
Please take note that, if you are generating huge report `s.autoSizeColumn(j);` will slow down the performance. — stanicmail, Jul 02 '13 at 09:05
This is something important to note, downgrades performance significantly! — NinaNa, Jul 20 '14 at 13:01

score 14 · Accepted Answer · answered Apr 01 '13 at 08:40

14

You just need to move the call to

sheet.autoSizeColumn(columnNumber);

to a point in your code after the data has been added.

Here is a link to the API

That's the only way to do it. I had same problem, I was calling the method autoSizeColumn() before I entered the data. Hop it helped

answered Apr 01 '13 at 08:40

knowbody

8,106
6
45
70

The autoSizeColumn() method is after I enter all the data and it works it just autosizes to the width of the last cell wntered – Billy Stalnaker Apr 01 '13 at 17:06
try to place it after u defined columns and before the cells – knowbody Apr 01 '13 at 17:17
Excuse my in-experience with the POI but you don't define columns only sheets rows and cells right? – Billy Stalnaker Apr 01 '13 at 19:27
After you have added all your data to the sheet, you can call `autoSizeColumn(columnNumber)`. Sorry my bad with the columns. Didn't work with it for a while – knowbody Apr 01 '13 at 20:29
Oh ok, gotcha but if you look at the code above that's what I did right? – Billy Stalnaker Apr 01 '13 at 20:42
2

@knowbody I am also having an issue with autosize, and it is definitely placed after data is entered. – theblang Jun 19 '13 at 15:31
2

Similar issue is being faced and it is placed after data is entered. Did anyone find any solution to this? – coretechie Nov 04 '16 at 08:56

score 4 · Answer 2 · answered Apr 01 '13 at 20:51

4

Move the for loop :

for (int j = 0; j < 6; j++) {
                    s.autoSizeColumn(j);
                }

to be just before you write in the workbook and close your out object.

wb.write(out);

answered Apr 01 '13 at 20:51

javadev

1,639
2
17
35

Ok I moved it but still the same results.. Not sure how to post code in a comment box but ill post it up top. – Billy Stalnaker Apr 01 '13 at 21:36
So nothing further to add?? – Billy Stalnaker Apr 02 '13 at 15:05

Apache POI autoSizeColumn() not working right

2 Answers2

Linked