POI操作Excel设置前景色背景色

POI中背景颜色设置方法如下:

HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillBackgroundColor(HSSFColor.TEAL.index); //设置背景色
cellStyle.setFillForegroundColor(HSSFColor.TEAL.index);// 设置前景色    
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

附:HSSFColor颜色定义

执行结果
HSSFColor.ROYAL_BLUE  
HSSFColor.TEAL  
HSSFColor.LIME  
HSSFColor.PALE_BLUE  
HSSFColor.AQUA  
HSSFColor.GREEN  
HSSFColor.TURQUOISE  
HSSFColor.DARK_BLUE  
HSSFColor.CORNFLOWER_BLUE  
HSSFColor.OLIVE_GREEN  
HSSFColor.WHITE  
HSSFColor.LIGHT_TURQUOISE  
HSSFColor.LEMON_CHIFFON  
HSSFColor.LIGHT_GREEN  
HSSFColor.BLUE  
HSSFColor.DARK_RED  
HSSFColor.CORAL  
HSSFColor.RED  
HSSFColor.LIGHT_YELLOW  
HSSFColor.SKY_BLUE  
HSSFColor.BROWN  
HSSFColor.SEA_GREEN  
HSSFColor.INDIGO  
HSSFColor.MAROON  
HSSFColor.GREY_80_PERCENT  
HSSFColor.GREY_25_PERCENT  
HSSFColor.DARK_GREEN  
HSSFColor.YELLOW  
HSSFColor.GOLD  
HSSFColor.GREY_40_PERCENT  
HSSFColor.DARK_TEAL  
HSSFColor.PINK  
HSSFColor.ORCHID  
HSSFColor.LIGHT_BLUE  
HSSFColor.LIGHT_CORNFLOWER_BLUE  
HSSFColor.BLACK  
HSSFColor.DARK_YELLOW  
HSSFColor.VIOLET  
HSSFColor.LAVENDER  
HSSFColor.ROSE  
HSSFColor.BLUE_GREY  
HSSFColor.LIGHT_ORANGE  
HSSFColor.ORANGE  
HSSFColor.GREY_50_PERCENT  

 

POI中背景颜色设置方法如下: HSSFCellStyle cellStyle =
wb.createCellStyle();cellStyle.setFillBackgroundColor(HSSFColor.TEAL.index);…

POI中可能会用到一些需要设置EXCEL单元格格式的操作小结:

  一、先说设置单元格的背景颜色:
  HSSFWorkbook wb = new HSSFWorkbook();
  …
  HSSFCellStyle style = wb.createCellStyle();
  style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  style.setFillForegroundColor(HSSFColor.WHITE.index);
  cell.setCellStyle(style);  //cell 是 HSSFCell 对象
setFillPattern是设置单元格填充样式,SOLID_FOREGROUND纯色使用前景颜色填充,接着设置前景颜色(setFillForegroundColor)就可以给单元格着色了。setFillForegroundColor()方法的参数是一个short类型,POI使用索引来代表颜色,默认已经有一些颜色了,如:
    8: BLACK
    60: BROWN
    59: OLIVE_GREEN
    58: DARK_GREEN
    … 
颜色的索引还必须是 0x08 ~ 0x40 (8 ~ 64) 的数字。

先获取工作薄对象:

        二、接下来,使用自定义颜色
如果不使用POI提供的默认颜色,就需要自定颜色索引:
  HSSFPalette palette = wb.getCustomPalette();  //wb HSSFWorkbook对象
  palette.setColorAtIndex((short) 9, (byte) (color.getRed()), (byte)
(color.getGreen()), (byte) (color.getBlue()));
   
   /*设置颜色的索引只能是 8 ~
64,在此之外的索引无效,也不会报错。以下三种方式都可以设置成功。
   palette.setColorAtIndex((short)9, (byte) (0xff & 251), (byte) (0xff &
161), (byte) (0xff & 161));
   palette.setColorAtIndex((short)10, (byte) (0x66), (byte) (0xcd),
(byte) (0xaa));
   palette.setColorAtIndex((short)11, (byte) (255), (byte) (165), (byte)
(0));
   */
然后使用颜色,如上例,可以用新的颜色索引,替换原有的颜色:
  style.setFillForegroundColor((short) 9);

HSSFWorkbook wb
= new HSSFWorkbook();

        三、setFillPattern(),设置单元格填充的样式,比如:
   style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
   style.setFillForegroundColor(HSSFColor.RED.index);
   style.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index);
这样当前单元格就被红蓝交替的格子填充

HSSFSheet
sheet = wb.createSheet();

 威尼斯人平台 1
上面3行代码,去掉setFillPattern设置填充样式的一行,同时设置前景色和背景色,生成的文件没有填充颜色,此时既不会用前景色填充,也不会用背景色填充。这种情况与
setFillPattern(HSSFCellStyle.NO_FILL); 时一样。
api上setFillBackgroundColor方法说明有如下示例:

HSSFCellStyle
setBorder = wb.createCellStyle();

public void setFillBackgroundColor(short bg)

一、设置背景色:

set the background fill color.

setBorder.setFillForegroundColor((short)
13);// 设置背景色
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

For example:

二、设置边框:

 cs.setFillPattern(HSSFCellStyle.FINE_DOTS );
 cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); 
 //上面代码经测试,是黑色点状的背景(无前景),设置红色背景色无效

setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//下边框
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

optionally a Foreground and background fill can be applied: Note:
Ensure Foreground color is set prior to background

三、设置居中:

 cs.setFillPattern(HSSFCellStyle.FINE_DOTS );
 cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex());
 cs.setFillBackgroundColor(new HSSFColor.RED().getIndex());

setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 居中

or, for the special case of SOLID_FILL:

四、设置字体:

 cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );
 cs.setFillForegroundColor(new HSSFColor.RED().getIndex());

HSSFFont
font = wb.createFont();
font.setFontName(“黑体”);
font.setFontHeightInPoints((short) 16);//设置字体大小

It is necessary to set the fill style in order for the color to be shown
in the cell. 

HSSFFont
font2 = wb.createFont();
font2.setFontName(“仿宋_GB2312”);
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font2.setFontHeightInPoints((short) 12);

setBorder.setFont(font);//选择需要用到的字体格式

五、设置列宽:

sheet.setColumnWidth(0,
3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值

六、设置自动换行:

setBorder.setWrapText(true);//设置自动换行

七、合并单元格:

Region
region1 = new Region(0, (short) 0, 0, (short) 6);

//参数1:行号
参数2:起始列号 参数3:行号 参数4:终止列号
sheet.addMergedRegion(region1);

八、加边框

  HSSFCellStyle cellStyle=
wookBook.createCellStyle();
  cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  cellStyle.setBorderBottom(HSSFCellStyle.BorderBORDER_MEDIUM);
  cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
  cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
  cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
  cellStyle.setTopBorderColor(HSSFColor.BLACK.index);

另附:完整小例子一个

开发环境:IntelliJ
IDEA 10.0.2

 

@ResponseBody

@RequestMapping(value
= “/reportForms/joinStocktaking/exportStorage.api”)

public
AjaxResponse exportStorage(@RequestBody StorageModel model) throws
Exception {

if
(logger.isDebugEnabled())

logger.debug(“tmpdir
is, {}”, System.getProperty(JAVA_IO_TMPDIR));

int row =
1;

 

HSSFWorkbook
workbook = new HSSFWorkbook();

HSSFSheet
hssfSheet = workbook.createSheet();

HSSFCellStyle
style = workbook.createCellStyle();

style.setFillBackgroundColor(HSSFCellStyle.LEAST_DOTS);

style.setFillPattern(HSSFCellStyle.LEAST_DOTS);

 

//设置Excel中的边框(表头的边框)

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);

style.setBottomBorderColor(HSSFColor.BLACK.index);

style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);

style.setLeftBorderColor(HSSFColor.BLACK.index);

style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);

style.setRightBorderColor(HSSFColor.BLACK.index);

style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);

style.setTopBorderColor(HSSFColor.BLACK.index);

 

//设置字体

HSSFFont font =
workbook.createFont();

font.setFontHeightInPoints((short)
14); // 字体高度

font.setFontName(”
黑体 “); // 字体

 

style.setFont(font);

HSSFRow
firstRow = hssfSheet.createRow((short) 0);

HSSFCell
firstCell = firstRow.createCell(0);

firstRow.setHeight((short)
400);

//设置Excel中的背景

style.setFillForegroundColor(HSSFColor.GREEN.index);

style.setFillBackgroundColor(HSSFColor.GREEN.index);

firstCell.setCellValue(new
HSSFRichTextString(“库房”));

firstCell.setCellStyle(style);

 

HSSFCell
secondCell = firstRow.createCell(1);

firstRow.setHeight((short)
400);

style.setFillForegroundColor(HSSFColor.GREEN.index);

style.setFillBackgroundColor(HSSFColor.GREEN.index);

secondCell.setCellValue(new
HSSFRichTextString(“库区”));

secondCell.setCellStyle(style);

 

HSSFCell
threeCell = firstRow.createCell(2);

firstRow.setHeight((short)
400);

style.setFillForegroundColor(HSSFColor.GREEN.index);

style.setFillBackgroundColor(HSSFColor.GREEN.index);

threeCell.setCellValue(new
HSSFRichTextString(“物料编号”));

threeCell.setCellStyle(style);

 

HSSFCell
fourCell = firstRow.createCell(3);

firstRow.setHeight((short)
400);

style.setFillForegroundColor(HSSFColor.GREEN.index);

style.setFillBackgroundColor(HSSFColor.GREEN.index);

fourCell.setCellValue(new
HSSFRichTextString(“物料名称”));

fourCell.setCellStyle(style);

 

HSSFCell
fiveCell = firstRow.createCell(4);

firstRow.setHeight((short)
400);

style.setFillForegroundColor(HSSFColor.GREEN.index);

style.setFillBackgroundColor(HSSFColor.GREEN.index);

威尼斯人平台,fiveCell.setCellValue(new
HSSFRichTextString(“在库数量”));

fiveCell.setCellStyle(style);

 

HSSFCell
sixCell = firstRow.createCell(5);

firstRow.setHeight((short)
400);

style.setFillForegroundColor(HSSFColor.GREEN.index);

style.setFillBackgroundColor(HSSFColor.GREEN.index);

sixCell.setCellValue(new
HSSFRichTextString(“锁定数量”));

sixCell.setCellStyle(style);

 

//设置列宽

hssfSheet.setColumnWidth(0, 7000);

 

hssfSheet.setColumnWidth(1,
8000);

hssfSheet.setColumnWidth(2,
4000);

hssfSheet.setColumnWidth(3,
6000);

hssfSheet.setColumnWidth(4,
4000);

hssfSheet.setColumnWidth(5,
4000);

 

 

List<?> list =
joinStocktackingService.findjoinStorageByTerm(model.getWareHouse(),
model.getStockArea(), model.getMaterialCode(),
model.getMaterialName());

for (Object object : list)
{

Object[] objects =
(Object[]) object;

Storage storage = (Storage)
objects[0];

Warehouse warehouse =
(Warehouse) objects[1];

StockArea stockArea =
(StockArea) objects[2];

Material material = (Material)
objects[3];

 

 

//设置Excel中的边框

HSSFCellStyle cellStyle =
workbook.createCellStyle();

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);

cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);

cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);

cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);

cellStyle.setRightBorderColor(HSSFColor.BLACK.index);

cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);

cellStyle.setTopBorderColor(HSSFColor.BLACK.index);

 

 

HSSFRow hssfRow =
hssfSheet.createRow((short) row);

HSSFCell firstHssfCell =
hssfRow.createCell(0);//库房

firstHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

firstHssfCell.setCellValue(new
HSSFRichTextString(warehouse.getName()));

firstHssfCell.setCellStyle(cellStyle);//设置单元格的样式

 

HSSFCell secondHssfCell =
hssfRow.createCell(1);

secondHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

secondHssfCell.setCellValue(new
HSSFRichTextString(stockArea.getName()));

secondHssfCell.setCellStyle(cellStyle);//设置单元格的样式

 

HSSFCell threeHssfCell =
hssfRow.createCell(2);

threeHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

threeHssfCell.setCellValue(new
HSSFRichTextString(material.getCode()));

threeHssfCell.setCellStyle(cellStyle);//设置单元格的样式

 

HSSFCell fourHssfCell =
hssfRow.createCell(3);

fourHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

fourHssfCell.setCellValue(new
HSSFRichTextString(material.getName()));

fourHssfCell.setCellStyle(cellStyle);//设置单元格的样式

 

HSSFCell fiveHssfCell =
hssfRow.createCell(4);

fiveHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

fiveHssfCell.setCellValue(new
HSSFRichTextString(String.valueOf(storage.getQty())));

fiveHssfCell.setCellStyle(cellStyle);//设置单元格的样式

 

HSSFCell sixHssfCell =
hssfRow.createCell(5);

sixHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

sixHssfCell.setCellValue(new
HSSFRichTextString(String.valueOf(storage.getQtyLocked())));

sixHssfCell.setCellStyle(cellStyle);//设置单元格的样式

 

row++;

}

String newFileName =
String.format(“%s.%s”, “joinStocktaking-” + (new Date()).getTime(),
“xls”);

String uploadPath =
FileUtils.contractPath(System.getProperty(JAVA_IO_TMPDIR),
newFileName);

FileOutputStream fOut = new
FileOutputStream(uploadPath);

workbook.write(fOut);

fOut.flush();

fOut.close();

 

return
AjaxResponse.createSuccess(newFileName);

}

相关文章