Java Apache Poi, how to set background color and borders at same time

Question

at start i want to say that i'm totally new in developers world.

I tried to generate an excel sheet that contains Mutiplication Table with borders and set background color but only for 1st column and row.

Here is a correct Example: correct example

I wrote something like that, but in result file colored cells has not have borders :(.

Please explain me how to set background color and borders at the same time.


import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.awt.image.IndexColorModel;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Scanner;

public class Excel {
    public static void main(String[] args) throws IOException {

        Scanner in = new Scanner(System.in);

        System.out.println("enter number of rows: ");
        int x = in.nextInt();
        System.out.println("enter number of columns: ");
        int y = in.nextInt();
        System.out.println("enter name of file: ");
        String fileName = in.next() + ".xls";

        System.out.println("Multiplication table will be created in file: " + fileName);

        createExcelMultiplicationTable(fileName, x, y);

        System.out.println("Process successful executed");
    }

    private static void createExcelMultiplicationTable(String fileName, int x, int y) throws IOException {
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("multiplicationTable");

        CellStyle backgroundStyle = workbook.createCellStyle();

        backgroundStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        backgroundStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        CellStyle borderStyle = workbook.createCellStyle();

        borderStyle.setBorderBottom(CellStyle.BORDER_THIN);
        borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        borderStyle.setBorderLeft(CellStyle.BORDER_THIN);
        borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        borderStyle.setBorderRight(CellStyle.BORDER_THIN);
        borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        borderStyle.setBorderTop(CellStyle.BORDER_THIN);
        borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

        for (int i = 1; i <= x; i++) {
            Row row = sheet.createRow(i - 1);

            for (int j = 1; j <= y; j++) {
                Cell cell = row.createCell(j - 1);
                cell.setCellValue(i * j);
                cell.setCellStyle(borderStyle);

                if (cell.getRowIndex() == 0 || cell.getColumnIndex() == 0) {
                    cell.setCellStyle(backgroundStyle);
                }
            }
        }

        FileOutputStream out = new FileOutputStream(fileName);
        workbook.write(out);
        out.close();
    }
}

Maybe [this](http://stackoverflow.com/a/10854107/3519000) helps. — lrnzcig, Aug 10 '16 at 13:03

Karthik · Accepted Answer · 2016-08-10T13:38:51.640

28

change backgroundStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); to

 backgroundStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());

And you can set border as like below :

        backgroundStyle.setBorderBottom(CellStyle.BORDER_THIN);
        backgroundStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        backgroundStyle.setBorderLeft(CellStyle.BORDER_THIN);
        backgroundStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        backgroundStyle.setBorderRight(CellStyle.BORDER_THIN);
        backgroundStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        backgroundStyle.setBorderTop(CellStyle.BORDER_THIN);
        backgroundStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

This will give you yellow color and border as required

edited Aug 10 '16 at 13:38

answered Aug 10 '16 at 13:18

Karthik

545
6
23

I checked your solution but it same like my version returns only one variant (prints background or prints borders), it is impossible to print colored cells with borders using this program. But i found correct solution. I created 2 different styles. First style prints only borders, second prints borders and sets background. It works properly, but i'm almost sure that here is other shorter solution – Robert Lewandowski Aug 10 '16 at 14:17
see i have changed setFillBackgroundColor to setFillForegroundColor. Back to Fore .This will surely change the color of your backgroud. Did you try that .? If you just copy paste what i have mentioned It will work for sure. – Karthik Aug 10 '16 at 14:40
Yeah, when i did as you advised it works. At all your solution is similar to my. – Robert Lewandowski Aug 10 '16 at 14:56
3

I think you forgot to mention `.setFillPattern(FillPatternType.SOLID_FOREGROUND);`, without it cells' background color didn't change for me. – parsecer Dec 10 '19 at 11:04

score 23 · Answer 2 · edited Aug 13 '19 at 13:43

23

As of POI 3.x, cell fill color is set as follows:

CellStyle cs = wb.createCellStyle();
cs.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);

edited Aug 13 '19 at 13:43

Fourat

2,366
4
38
53

answered Oct 29 '17 at 04:12

Slawomir

3,194
1
30
36

Thanks. however compiler says, FillpatternType cannot be converted to short. so have to use setFillPattern((short)1); – Monster Brain Jan 16 '19 at 08:51
1

cs.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()) cs.setFillPatternType(**CellStyle**.SOLID_FOREGROUND) with poi-3.14 – levolutionniste Sep 24 '20 at 11:00

score 12 · Answer 3 · answered Aug 10 '16 at 18:04

Your real problem is that you have two styles, one named backgroundStyle, and the other named borderStyle. You then apply both styles to the same cell, but a cell can only have one style, so instead of adding the second style, you are overwriting the first style with the second style.

Instead of:

    CellStyle backgroundStyle = workbook.createCellStyle();

    backgroundStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    backgroundStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    CellStyle borderStyle = workbook.createCellStyle();

    borderStyle.setBorderBottom(CellStyle.BORDER_THIN);
    borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    borderStyle.setBorderLeft(CellStyle.BORDER_THIN);
    borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    borderStyle.setBorderRight(CellStyle.BORDER_THIN);
    borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    borderStyle.setBorderTop(CellStyle.BORDER_THIN);
    borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

Just create a single style like this:

    CellStyle backgroundStyle = workbook.createCellStyle();

    backgroundStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    backgroundStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    backgroundStyle.setBorderBottom(CellStyle.BORDER_THIN);
    backgroundStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    backgroundStyle.setBorderLeft(CellStyle.BORDER_THIN);
    backgroundStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    backgroundStyle.setBorderRight(CellStyle.BORDER_THIN);
    backgroundStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    backgroundStyle.setBorderTop(CellStyle.BORDER_THIN);
    backgroundStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

Then apply that to your cell:

    Sheet sheet = workbook.createSheet();
    Row row = sheet.createRow(1);
    Cell cell = row.createCell(1);
    cell.setCellStyle(backgroundStyle);

NOTE: As mentioned in other answers here, Background Color is ignored for FillPattern = SOLID_FOREGROUND, you have to set the Foreground color for that pattern. This can be confusing because you are trying to set the cell background to a solid color. But cell background is not the same as background color. Cell background is the same as Fill Pattern which has two colors a Foreground Color and a Background Color these are displayed based on the specific Fill Pattern selected. The SOLID_FOREGROUND fill uses only the Foreground Color.

score 1 · Answer 4 · answered Aug 10 '16 at 14:34

I found one way how to resolve this issue, but i'm almost sure that there is other shorter way.

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Scanner;

public class Excel {
public static void  main(String[] args) throws IOException {

        Scanner in = new Scanner(System.in);

        System.out.println("enter number of rows: ");
        int x = in.nextInt();
        System.out.println("enter number of columns: ");
        int y = in.nextInt();
        System.out.println("enter name of file: ");
        String fileName = in.next() + ".xls";

        System.out.println("Multiplication table will be created in file: " + fileName);

        createExcelMultiplicationTable(fileName, x, y);

        System.out.println("Process successful executed");
    }

    private static void createExcelMultiplicationTable(String fileName, int x, int y) throws IOException {
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("multiplicationTable");

        for (int i = 1; i <= x; i++) {
            Row row = sheet.createRow(i - 1);

            for (int j = 1; j <= y; j++) {
                Cell cell = row.createCell(j - 1);
                cell.setCellValue(i * j);

                if (cell.getRowIndex() == 0 || cell.getColumnIndex() == 0) {
                    CellStyle Style = workbook.createCellStyle();

                    Style.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
                    Style.setFillPattern(CellStyle.BIG_SPOTS);
                    Style.setBorderBottom(CellStyle.BORDER_THIN);
                    Style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                    Style.setBorderLeft(CellStyle.BORDER_THIN);
                    Style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                    Style.setBorderRight(CellStyle.BORDER_THIN);
                    Style.setRightBorderColor(IndexedColors.BLACK.getIndex());
                    Style.setBorderTop(CellStyle.BORDER_THIN);
                    Style.setTopBorderColor(IndexedColors.BLACK.getIndex());

                    cell.setCellStyle(Style);
                } else {
                    CellStyle Style = workbook.createCellStyle();

                    Style.setBorderBottom(CellStyle.BORDER_THIN);
                    Style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                    Style.setBorderLeft(CellStyle.BORDER_THIN);
                    Style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                    Style.setBorderRight(CellStyle.BORDER_THIN);
                    Style.setRightBorderColor(IndexedColors.BLACK.getIndex());
                    Style.setBorderTop(CellStyle.BORDER_THIN);
                    Style.setTopBorderColor(IndexedColors.BLACK.getIndex());

                    cell.setCellStyle(Style);
             }
            }
        }

        FileOutputStream out = new FileOutputStream(fileName);
        workbook.write(out);
        out.close();
  }
}

This program wont give you yellow color backgroud. Understand the difference between setFillBackgroundColor and setFillForegroundColor — Karthik, Aug 10 '16 at 14:45
I know that, I added picture with yellow background only as example — Robert Lewandowski, Aug 10 '16 at 15:17
As state elsewhere DO NOT create cell style within the for loops. For all who want to copy this code, please move creation of cell style outside the out most loop. Excel can only handle a limited amount of cell styles. If there are too many, Excel file will not be created. Furthermore if you want to read the Excel later with POI you may get a "ZIP bomb"! — Christian Lischnig, Oct 24 '20 at 19:24

Java Apache Poi, how to set background color and borders at same time

4 Answers4

Linked