Robert Lewandowski Robert Lewandowski - 3 months ago 47
Java Question

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

Answer

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

Comments