CODEWITHSUNDEEP
javaapache-poi
Robert Lewandowski
Robert Lewandowski

Reputation: 213

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

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();
    }
}

Upvotes: 19

Views: 94731

Answers (4)

Slawomir
Slawomir

Reputation: 3343

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);

Upvotes: 23

jmarkmurphy
jmarkmurphy

Reputation: 11473

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.

Upvotes: 12

Robert Lewandowski
Robert Lewandowski

Reputation: 213

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();
  }
}

Upvotes: 1

Karthikeyan Subramanian
Karthikeyan Subramanian

Reputation: 545

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

Upvotes: 28

Related Questions