Apache POI autoSizeColumn() not working right

java apache excel apache-poi

42,094

Solution 1

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

Solution 2

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);

42,094

Author by

Billy Stalnaker

Updated on July 30, 2022

Comments

Billy Stalnaker almost 2 years

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 **

Billy Stalnaker about 11 years

The autoSizeColumn() method is after I enter all the data and it works it just autosizes to the width of the last cell wntered
knowbody about 11 years

try to place it after u defined columns and before the cells
Billy Stalnaker about 11 years

Excuse my in-experience with the POI but you don't define columns only sheets rows and cells right?
knowbody about 11 years

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
Billy Stalnaker about 11 years

Oh ok, gotcha but if you look at the code above that's what I did right?
Billy Stalnaker about 11 years

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 about 11 years

So nothing further to add??
theblang almost 11 years

@knowbody I am also having an issue with autosize, and it is definitely placed after data is entered.
coretechie over 7 years

Similar issue is being faced and it is placed after data is entered. Did anyone find any solution to this?