Java Apache Poi，如何同时设置背景颜色和边框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38874115/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Java Apache Poi, how to set background color and borders at same time
提问by Robert Lewandowski
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.
我试图生成一个 Excel 工作表，其中包含带边框的多倍表并设置背景颜色，但仅适用于第一列和第一行。
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();
}
}
采纳答案by Karthik
change backgroundStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());to
更改 backgroundStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());为
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
这将根据需要为您提供黄色和边框
回答by Robert Lewandowski
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();
}
}
回答by jmarkmurphy
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.
您真正的问题是您有两种样式，一种名为 backgroundStyle，另一种名为 borderStyle。然后将两种样式应用到同一个单元格，但一个单元格只能有一种样式，因此您不是添加第二种样式，而是用第二种样式覆盖第一种样式。
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 backgroundis not the same as background color. Cell backgroundis the same as Fill Patternwhich has two colors a Foreground Colorand a Background Colorthese are displayed based on the specific Fill Patternselected. The SOLID_FOREGROUNDfill uses only the Foreground Color.
注意：如此处其他答案中所述，FillPattern = SOLID_FOREGROUND 会忽略背景颜色，您必须为该图案设置前景色。这可能会令人困惑，因为您正在尝试将单元格背景设置为纯色。但是cell background不一样background color。Cell background与Fill Pattern有两种颜色 aForeground Color和 a相同，Background Color这些是根据特定Fill Pattern选择显示的。该SOLID_FOREGROUND填充只使用Foreground Color。
回答by Slawomir
As of POI 3.x, cell fill color is set as follows:
从 POI 3.x 开始，单元格填充颜色设置如下：
CellStyle cs = wb.createCellStyle();
cs.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
