Auto Expand Column/Cell Excel Writing in Java

Auto Expand Column/Cell Excel Writing in Java:

When you are writing an excel sheet through java program you will come to these kind of requirements. By default excel file will be generated without any auto fit to text or auto column width properties. We need to set the sheet and cell styles/properties for this externally.

 

Prerequisite:

• You need all Apache Poi Jars to write the excel sheet through Java (Refer below post for all the jars)
• I used pojo class named “GWCPojo” (which you can take/copy from the below link)

Google Webmaster Console Keyword Position Report Program in Java

 

 

How to Read Excel File xlsx in Java using Apache Poi?

 

 

Without Autofit to text:

 

With Auto fit to text:

 

 

Auto Expand Column/Cell Excel Writing in Java – Source code:

 

[java]

package in.javadomain;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashSet;
import java.util.Set;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteAutoCellExample {

public static void main(String[] args) {
WriteAutoCellExample writeExcelAutoExpandCells = new WriteAutoCellExample();
Set<GWCPojo> myset = new HashSet<GWCPojo>();
GWCPojo gwcPojo = new GWCPojo();

gwcPojo.setQuery(“Difference between concat and append in java”);
gwcPojo.setPosition(“2”);
myset.add(gwcPojo);
gwcPojo = new GWCPojo();

gwcPojo.setQuery(“Hello World Java Program”);
gwcPojo.setPosition(“112”);
myset.add(gwcPojo);
gwcPojo = new GWCPojo();

gwcPojo.setQuery(“Tips to write SCJP Exam”);
gwcPojo.setPosition(“1”);
myset.add(gwcPojo);
gwcPojo = new GWCPojo();

writeExcelAutoExpandCells.writeExcelWithAutoCellExpand(myset);
}

private void writeExcelWithAutoCellExpand(Set<GWCPojo> posGWCPojos) {
System.out.println(“Auto Expand Cell Excel Sheet Creation Started!”);
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(“Auto Expand”);
int rowCount = 0;
Row row = sheet.createRow(rowCount++);

Cell cell = row.createCell(0);
cell.setCellValue(“Search Keyword”);

cell = row.createCell(1);
cell.setCellValue(“Keyword Position in Google (Less than 10 Highlighted below)”);
for (GWCPojo gwcPojo : posGWCPojos) {
row = sheet.createRow(rowCount++);

// to increase the cell width to autosize (fit to text for column 0)
sheet.autoSizeColumn(0);
int columnCount = 0;
cell = row.createCell(columnCount++);
if (gwcPojo.getQuery() instanceof String) {
cell.setCellValue((String) gwcPojo.getQuery());
}

// to increase the cell width to autosize (fit to text for column 1)
sheet.autoSizeColumn(1);
cell = row.createCell(columnCount++);
if (gwcPojo.getPosition() instanceof String) {

if ((Double.parseDouble(gwcPojo.getPosition()) < 10)) {
XSSFFont font = workbook.createFont();
// setting font-color white
font.setColor(IndexedColors.WHITE.getIndex());
CellStyle style = workbook.createCellStyle();

// setting background color black
style.setFillBackgroundColor(IndexedColors.BLACK.getIndex());
style.setFont(font);
style.setFillPattern(CellStyle.BIG_SPOTS);
// row.setRowStyle(style);
cell.setCellStyle(style);
}
cell.setCellValue((String) gwcPojo.getPosition());
}
}

try (FileOutputStream outputStream = new FileOutputStream(“C:\\autoexpandcell.xlsx”)) {
workbook.write(outputStream);
System.out.println(“Auto Expand Cell Excel Sheet Written Successfully!”);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}

}
}

[/java]

 

I am not explaining anything as the explanation are given directly in the above program itself. But still have any queries/issues, feel free to write your comments in the below comments section.