Java code for excel row in Bold text style with Background color

Question

I have googled some code and found some answers but not able to get my excel file output in Bold and set background color. I have tried with following code. can you please tell me where am I going wrong? Please take a look. Thanks.

FYI: I am going to make 1st Row in BOLD with blue or any Light color background. If you know please help with the code.

// Excel file generation code
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Readings");
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontHeightInPoints((short)11);
font.setFontName(HSSFFont.FONT_ARIAL);
font.setBoldweight(HSSFFont.COLOR_NORMAL);
font.setBold(true);
font.setColor(HSSFColor.DARK_BLUE.index);

style.setFont(font);
// Freeze 1st Row               
sheet.createFreezePane(0, 1); 

HSSFRow row = sheet.createRow(1);
HSSFRow rowhead = sheet.createRow((short) 0);
rowhead.setRowStyle(style);

rowhead.createCell(0).setCellValue("RUN");
rowhead.createCell(1).setCellValue("NUMBER");

Auto

Answer 1

You are doing wrong in the following:

1- You are not setting any Background color;

2- When you create the new cells they override the row style, so you need to set the style for each new cell you create;

Below is the working code:

FileOutputStream fileOut = new FileOutputStream("poi-test.xls");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Readings");
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontHeightInPoints((short)11);
font.setFontName(HSSFFont.FONT_ARIAL);
font.setBoldweight(HSSFFont.COLOR_NORMAL);
font.setBold(true);
font.setColor(HSSFColor.DARK_BLUE.index);

style.setFont(font);
//Add these lines     
style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);

sheet.createFreezePane(0, 1); // Freeze 1st Row   sheet.createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow)


 HSSFRow rowhead = sheet.createRow((short) 0);
 rowhead.setRowStyle(style);
 //Set the cell0 Style        
 HSSFCell cell0 = rowhead.createCell(0);
 cell0.setCellStyle(style);
 cell0.setCellValue("ROW");
 //Set the cell1 Style        
 HSSFCell cell1 = rowhead.createCell(1);
 cell1.setCellStyle(style);
 cell1.setCellValue("NUMBER");

 workbook.write(fileOut);

file output:

Sharpeared

Answer 2

First remove font.setBoldweight(HSSFFont.COLOR_NORMAL);. COLOR_NORMAL is not really a Boldweight, but a color. font.setBold(true); should be sufficient to bold your text.

You also need to add some properties to your style to get the background color. This is the confusing part. The approach is to use a Fill. Fills have Foreground and Background colors distinct from the rest of the cell. To get a solid fill, you need to use:

style.setForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);

Notice that this sets the foreground color of the fill to light blue. Since the fill has a solid foreground, that is all you need. If you chose one of the pattern fills, you would need to set the background color for the fill as well. The fill itself is the cell background.

Obvert

Answer 3

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;

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 excelStylingExample {

    public static void main(String[] args) throws FileNotFoundException, IOException {
        String toCreateFullPath="C:\\Users\\Your Path\\testExcel.xlsx";
        Path path=Paths.get(toCreateFullPath);
        if(!Files.exists(path.getParent())){

            try {
                Files.createDirectory(path.getParent());
                System.out.println("Directory created");
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            System.out.println("Error Directory is Not Created");
        }
    }

    XSSFWorkbook workbook =new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("testSheet");
    String[] columns = {"Code", "Name", "Address" };
    CellStyle hStyle=null;
    // Creating a font
    XSSFFont font= workbook.createFont();
    font.setFontHeightInPoints((short)12);
    font.setFontName("Arial");
    font.setColor(IndexedColors.YELLOW.getIndex());
    font.setBold(true);
    font.setItalic(false);

    hStyle=workbook.createCellStyle();
    hStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    hStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    hStyle.setAlignment(CellStyle.ALIGN_CENTER);
    // Setting font to style
    hStyle.setFont(font);


    // Create a Row
    Row headerRow = sheet.createRow(0);
    // Create cells
    for(int i = 0; i < columns.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(columns[i]);
        // Setting cell style
        cell.setCellStyle(hStyle);  
        sheet.autoSizeColumn(i);
    }
    
    // setting values into records
    int rowCount = 0;
    for(int i=0;i<1;i++) { // an example of one row 
        
        CellStyle styleAllCell=null;
        styleAllCell=workbook.createCellStyle();
        styleAllCell.setAlignment(CellStyle.ALIGN_CENTER);
        
        Row row = sheet.createRow(++rowCount);
        Cell cell = row.createCell(0);
        cell.setCellValue("1000");
        sheet.autoSizeColumn(0);
        cell.setCellStyle(styleAllCell);
        
        cell = row.createCell(1);
        cell.setCellValue("Leaonardo");
        sheet.autoSizeColumn(1);
        cell.setCellStyle(styleAllCell);

        
        cell = row.createCell(2);
        cell.setCellValue("Italy");
        sheet.autoSizeColumn(2);
        cell.setCellStyle(styleAllCell);

    }

    try (FileOutputStream outputStream = new FileOutputStream(toCreateFullPath)) {

        workbook.write(outputStream);

    }


}

}

finally the excel would be like:

Viridi