I am creating an excel file with apache poi the excel is generated but i can not adjust the column with according to the cell values i am posting the code what i have done so far
This is how i have created the headers in excel
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
sheet.protectSheet("password");
sheet.autoSizeColumn(15);
HSSFFont hSSFFont = wb.createFont();
hSSFFont.setFontName(HSSFFont.FONT_ARIAL);
hSSFFont.setFontHeightInPoints((short) 8);
CellStyle style = wb.createCellStyle();
/* cell style for locking */
CellStyle lockedCellStyle = wb.createCellStyle();
lockedCellStyle.setLocked(true);
HSSFRow row = null;
HSSFCell cell = null;
row = sheet.createRow(0);
int headercolumnNo = 0;
//1st Column Header for Indicator
cell = row.createCell(headercolumnNo);
cell.setCellValue(new HSSFRichTextString(listOfActiveCarrierUserHeader.get(0)));
style.setWrapText(true);
style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
style.setFont(hSSFFont);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell.setCellStyle(style);
headercolumnNo = 1;
cell = row.createCell(headercolumnNo); //2nd Column Header for Firstname
cell.setCellValue(new HSSFRichTextString(listOfActiveCarrierUserHeader.get(1)));
style.setWrapText(true);
style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
style.setFont(hSSFFont);
cell.setCellStyle(style);
headercolumnNo = headercolumnNo + 1;
cell = row.createCell(headercolumnNo); //2nd Column Header for Firstname
cell.setCellValue(new HSSFRichTextString(listOfActiveCarrierUserHeader.get(2)));
style.setWrapText(true);
style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
style.setFont(hSSFFont);
cell.setCellStyle(style);
headercolumnNo = headercolumnNo + 1;
and this is how i have populated the values in that excel file
for(CarrierActiveUser carrierActiveUser : listOfCarrierUser){
int columnNo = 0;
row = sheet.createRow(j + 1);
cell = row.createCell(columnNo);
if(null != carrierActiveUser.getFistName()){
cell.setCellValue(new HSSFRichTextString(carrierActiveUser.getFistName()));
lockedCellStyle.setFont(hSSFFont);
cell.setCellStyle(lockedCellStyle);
}else{
cell.setCellValue(new HSSFRichTextString(" "));
cell.setCellStyle(lockedCellStyle);
}
columnNo = columnNo + 1;
cell = row.createCell(columnNo);
if(null != carrierActiveUser.getLastName()){
cell.setCellValue(new HSSFRichTextString(carrierActiveUser.getLastName()));
lockedCellStyle.setFont(hSSFFont);
cell.setCellStyle(lockedCellStyle);
}else{
cell.setCellValue(new HSSFRichTextString(" "));
cell.setCellStyle(lockedCellStyle);
}
columnNo = columnNo + 1;
cell = row.createCell(columnNo);
if(null != carrierActiveUser.getLastName()){
cell.setCellValue(new HSSFRichTextString(carrierActiveUser.getEmailId()));
lockedCellStyle.setFont(hSSFFont);
cell.setCellStyle(lockedCellStyle);
}else{
cell.setCellValue(new HSSFRichTextString(" "));
cell.setCellStyle(lockedCellStyle);
}
Please someone help me to adjust the columns , i am new to apache poi
You can use HSSFSheet.autoSizeColumn(columnNumber)
method to align the columns perfectly.
This method adjusts the column width to fit the contents, read the doc.
After setting all cell values for all columns you can use this method, in your current code call this method after for loop.
Sample code
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(2);
Note - You have to do this separately for all columns which you want to be aligned and the call to sheet.autoSizeColumn(columnNumber)
should be made after populating the data into the excel. Calling before populating data will not have any effect.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With