Excel sheet formatting using apache POI

0
votes

I am working on creating an excel report from java code using apache POI library. I have encountered a problem while formatting excel cell. Please find below piece of code.

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("coverageData");
    int rownum = 0,cellnum=0;
    Row row1 = sheet.createRow(rownum++);
    Cell cell10 = row1.createCell(cellnum++);
    cell10.setCellValue("cell data");
    XSSFCellStyle row1style = (XSSFCellStyle)cell10.getCellStyle();
    XSSFColor grayColor = new XSSFColor(Color.DARK_GRAY);
    row1style.setFillBackgroundColor(grayColor);
    cell10.setCellStyle(row1style);
    try         
    {             
        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(new File("P:\\automation\\Spreadsheet.xlsx"));
        workbook.write(out); 
        out.close(); 
        System.out.println("Spreadsheet.xlsx written successfully on disk."); 
    }   
    catch (Exception e)
    {  
        e.printStackTrace();
    }

The problem here is i am setting cell10 style in last line of code, but it is not getting affected in the excel sheet created by the program.

Thanks in advance.

2
javaexcelapache-poi

2 Answers

2
votes

Try it, bellow work fine for me:

import java.io.File;
import java.io.FileOutputStream;
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.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TestClass {
    public static void main(String[] args){
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("coverageData");
        int rownum = 0,cellnum=0;
        Row row1 = sheet.createRow((short)rownum++);
        //Set Color style start
        CellStyle row1style = wb.createCellStyle();
        row1style.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        row1style.setFillPattern(CellStyle.BIG_SPOTS);
        //Set Color style end
        Cell cell10 = row1.createCell((short)cellnum++);
        cell10.setCellStyle(row1style);
        cell10.setCellValue("cell data");
        try{             
            //Write the workbook in file system
            FileOutputStream out = new FileOutputStream(new File("Spreadsheet.xlsx"));
            wb.write(out); 
            out.close(); 
            System.out.println("Spreadsheet.xlsx written successfully on disk."); 
        } catch (Exception e) { e.printStackTrace(); } 
    }
}
0
votes

You need to set the color like this:

final XSSFCellStyle description = (XSSFCellStyle) workbook.createCellStyle();
description.setFillForegroundColor( yourColor );
description.setFillPattern( FillPatternType.SOLID_FOREGROUND );

This is, you need a fill pattern