Hi there, I am experiencing some strange behaviour with the method
XSFFSheet.autoSizeColumn(i)
I have taken the same spreadsheet. It has 8 sheets. Each have no more than 8
columns. I save two copies:
1 as ".xls" form, and the other as ".xlsx" form.
In the HSFFSheet.autoSizeColumn(), the behaviour is as expected - on each
sheet, all columns are stretched to the widest width to fit in the maximum
length of text in a cell in that column.
The problem is with XSSFSheet.autoSizeColumn(). The behavior is this: It
seems to be taking only the first row of each sheet and stretching the
column width according to each cell in row 1. So there are some cells
further down that are too small to have all the text visible.
To illustrate, I attach the code:
---------------------------------------------------------------------------
package Poi;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*
* @author Rob Stewart
*/
public class Main {
static Workbook populatedWorkbook;
public static void main(String[] args) {
resizeXLSColumns("sample.xls");
resizeXLSXColumns("sample.xlsx");
}
public static void resizeXLSColumns(String FILENAME){
// open the Excel Spreadsheet
try{
POIFSFileSystem fs = new POIFSFileSystem(new
FileInputStream(FILENAME));
populatedWorkbook = new HSSFWorkbook(fs);
}
catch(Exception b){System.out.println(b);}
int sheetCount = populatedWorkbook.getNumberOfSheets();
HSSFSheet sheet;
for(int i=0;i<sheetCount;i++){
sheet = (HSSFSheet) populatedWorkbook.getSheetAt(i);
for (int j=0;j<10;j++){
sheet.autoSizeColumn(j, true);
}
}
// Now save changes
try{
FileOutputStream fileOut = new FileOutputStream(FILENAME);
populatedWorkbook.write(fileOut);
fileOut.close();
}
catch(Exception b){System.out.println(b);}
}
public static void resizeXLSXColumns(String FILENAME){
// open the Excel Spreadsheet
try{
populatedWorkbook = (XSSFWorkbook) WorkbookFactory.create(new
FileInputStream(FILENAME));
}
catch(Exception b){System.out.println(b);}
int sheetCount = populatedWorkbook.getNumberOfSheets();
XSSFSheet sheet;
for(int i=0;i<sheetCount;i++){
sheet = (XSSFSheet) populatedWorkbook.getSheetAt(i);
for (int j=0;j<10;j++){
sheet.autoSizeColumn(j, true);
}
}
// Now save changes
try{
FileOutputStream fileOut = new FileOutputStream(FILENAME);
populatedWorkbook.write(fileOut);
fileOut.close();
}
catch(Exception b){System.out.println(b);}
}
}
----------------------------------------------------
So is this a bug?
