POI设置Excel单元格颜色.doc

先从单元格顔色设定开始介绍。单元格的顔色有前景色和背景色。前景色的设定使用HSSFCellStyle类的setFillForegroundColor方法。setFillForegroundColorpublic void setFillForegroundColor(short bg)set the foreground fill color Parameters: bg - color背景色的设定则使用HSSFCellStyle类的setFillBackgroundColor方法。setFillBackgroundColorpublic void setFillBackgroundColor(short bg)set the background fill color. Parameters: bg - color两个方法都是通过参数来设定具体什么顔色。该参数类型为short型，在HSSFColor类里，准备了各种各样顔色的定义值。HSSFColor类HSSFColor类定义如下： java.lang.Object org.apache.poi.hssf.util.HSSFColor public class HSSFColor extends java.lang.Object而各种顔色又是作为HSSFColor类的子类，定义一览表如下：HSSFColor.AQUAHSSFColor.BLACKHSSFColor.BLUEHSSFColor.BLUE_GREYHSSFColor.BRIGHT_GREENHSSFColor.BROWNHSSFColor.CORALHSSFColor.CORNFLOWER_BLUEHSSFColor.DARK_BLUEHSSFColor.DARK_GREENHSSFColor.DARK_REDHSSFColor.DARK_TEALHSSFColor.DARK_YELLOWHSSFColor.GOLDHSSFColor.GREENHSSFColor.GREY_25_PERCENTHSSFColor.GREY_40_PERCENTHSSFColor.GREY_50_PERCENTHSSFColor.GREY_80_PERCENTHSSFColor.INDIGOHSSFColor.LAVENDERHSSFColor.LEMON_CHIFFONHSSFColor.LIGHT_BLUEHSSFColor.LIGHT_CORNFLOWER_BLUEHSSFColor.LIGHT_GREENHSSFColor.LIGHT_ORANGEHSSFColor.LIGHT_TURQUOISEHSSFColor.LIGHT_YELLOWHSSFColor.LIMEHSSFColor.MAROONHSSFColor.OLIVE_GREENHSSFColor.ORANGEHSSFColor.ORCHIDHSSFColor.PALE_BLUEHSSFColor.PINKHSSFColor.PLUMHSSFColor.REDHSSFColor.ROSEHSSFColor.ROYAL_BLUEHSSFColor.SEA_GREENHSSFColor.SKY_BLUEHSSFColor.TANHSSFColor.TEALHSSFColor.TURQUOISEHSSFColor.VIOLETHSSFColor.WHITEHSSFColor.YELLOW设定顔色时，用这些子类的静态常量index作为参数，使用方法如下：HSSFWorkbook workbook = new HSSFWorkbook();HSSFCellStyle style = workbook.createCellStyle();style.setFillForegroundColor(HSSFColor.LIME.index);style.setFillBackgroundColor(HSSFColor.GREEN.index);如果这些顔色还不够你用的话，那么下一节再介绍怎么设定自己想要的顔色。填充模式指定填充模式的话，使用HSSFCellStyle类的setFillPattern方法。setFillPatternpublic void setFillPattern(short fp)setting to one fills the cell with the foreground color. No idea about other values Parameters: fp - fill pattern (set to 1 to fill w/foreground color)指定的填充模式，在HSSFCellStyle类里也有定义，类型为static short型，如下所示：值说明NO_FILLNo backgroundSOLID_FOREGROUNDSolidly filledFINE_DOTSSmall fine dotsALT_BARSWide dotsSPARSE_DOTSSparse dotsTHICK_HORZ_BANDSThick horizontal bandsTHICK_VERT_BANDSThick vertical bandsTHICK_BACKWARD_DIAGThick backward facing diagonalsTHICK_FORWARD_DIAGThick forward facing diagonalsBIG_SPOTSLarge spotsBRICKSBrick-like layoutTHIN_HORZ_BANDSThin horizontal bandsTHIN_VERT_BANDSThin vertical bandsTHIN_BACKWARD_DIAGThin backward diagonalTHIN_FORWARD_DIAGThin forward diagonalSQUARESSquaresDIAMONDSDiamonds实际的使用方法如下：HSSFWorkbook workbook = new HSSFWorkbook();HSSFCellStyle style = workbook.createCellStyle();style.setFillForegroundColor(HSSFColor.LIME.index);style.setFillBackgroundColor(HSSFColor.GREEN.index);style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);示例程序这就实际动手做做看吧，首先看一下定义的顔色到底是什么顔色，全部拉上来看看。import java.io.*;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.util.HSSFColor;public class POISample static HSSFWorkbook workbook; public static void main(String args) workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFRow row = new HSSFRow12; for (int i = 0; i 12 ; i+) rowi = sheet.createRow(i); HSSFCell cell = new HSSFCell124; for (int i = 0; i 12; i+) for (int j = 0; j 4 ; j+) cellij = rowi.createCell(short)j); setStyle(cell00, AQUA, HSSFColor.AQUA.index); setStyle(cell01, BLACK, HSSFColor.BLACK.index); setStyle(cell02, BLUE, HSSFColor.BLUE.index); setStyle(cell03, BLUE_GREY, HSSFColor.BLUE_GREY.index); setStyle(cell10, BRIGHT_GREEN, HSSFColor.BRIGHT_GREEN.index); setStyle(cell11, BROWN, HSSFColor.BROWN.index); setStyle(cell12, CORAL, HSSFColor.CORAL.index); setStyle(cell13, CORNFLOWER_BLUE, HSSFColor.CORNFLOWER_BLUE.index); setStyle(cell20, DARK_BLUE, HSSFColor.DARK_BLUE.index); setStyle(cell21, DARK_GREEN, HSSFColor.DARK_GREEN.index); setStyle(cell22, DARK_RED, HSSFColor.DARK_RED.index); setStyle(cell23, DARK_TEAL, HSSFColor.DARK_TEAL.index); setStyle(cell30, DARK_YELLOW, HSSFColor.DARK_YELLOW.index); setStyle(cell31, GOLD, HSSFColor.GOLD.index); setStyle(cell32, GREEN, HSSFColor.GREEN.index); setStyle(cell33, GREY_25_PERCENT, HSSFColor.GREY_25_PERCENT.index); setStyle(cell40, GREY_40_PERCENT, HSSFColor.GREY_40_PERCENT.index); setStyle(cell41, GREY_50_PERCENT, HSSFColor.GREY_50_PERCENT.index); setStyle(cell42, GREY_80_PERCENT, HSSFColor.GREY_80_PERCENT.index); setStyle(cell43, INDIGO, HSSFColor.INDIGO.index); setStyle(cell50, LAVENDER, HSSFColor.LAVENDER.index); setStyle(cell51, LEMON_CHIFFON, HSSFColor.LEMON_CHIFFON.index); setStyle(cell52, LIGHT_BLUE, HSSFColor.LIGHT_BLUE.index); setStyle(cell53, LIGHT_CORNFLOWER_BLUE, HSSFColor.LIGHT_CORNFLOWER_BLUE.index); setStyle(cell60, LIGHT_GREEN, HSSFColor.LIGHT_GREEN.index); setStyle(cell61, LIGHT_ORANGE, HSSFColor.LIGHT_ORANGE.index); setStyle(cell62, LIGHT_TURQUOISE, HSSFColor.LIGHT_TURQUOISE.index); setStyle(cell63, LIGHT_YELLOW, HSSFColor.LIGHT_YELLOW.index); setStyle(cell70, LIME, HSSFColor.LIME.index); setStyle(cell71, MAROON, HSSFColor.MAROON.index); setStyle(cell72, OLIVE_GREEN, HSSFColor.OLIVE_GREEN.index); setStyle(cell73, ORANGE, HSSFColor.ORANGE.index); setStyle(cell80, ORCHID, HSSFColor.ORCHID.index); setStyle(cell81, PALE_BLUE, HSSFColor.PALE_BLUE.index); setStyle(cell82, PINK, HSSFColor.PINK.index); setStyle(cell83, PLUM, HSSFColor.PLUM.index); setStyle(cell90, RED, HSSFColor.RED.index); setStyle(cell91, ROSE, HSSFColor.ROSE.index); setStyle(cell92, ROYAL_BLUE, HSSFColor.ROYAL_BLUE.index); setStyle(cell93, SEA_GREEN, HSSFColor.SEA_GREEN.index); setStyle(cell100, SKY_BLUE, HSSFColor.SKY_BLUE.index); setStyle(cell101, TAN, HSSFColor.TAN.index); setStyle(cell102, TEAL, HSSFColor.TEAL.index); setStyle(cell103, TURQUOISE, HSSFColor.TURQUOISE.index); setStyle(cell110, VIOLET, HSSFColor.VIOLET.index); setStyle(cell111, WHITE, HSSFColor.WHITE.index); setStyle(cell112, YELLOW, HSSFColor.YELLOW.index); FileOutputStream out = null; try out = new FileOutputStream(sample.xls); workbook.write(out); catch(IOException e) System.out.println(e.toString(); finally try out.close(); catch(IOException e) System.out.println(e.toString(); public static void setStyle(HSSFCell cell, String col, short fg) HSSFCellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(fg); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); cell.setCellValue(col); 上面程序只指定了ForegroundColor，填充模式是SOLID_FOREGROUND，因此顔色应该是全部充满整个单元格的。运行结果如下：下面再对填充模式进行各种修改来看看。import java.io.*;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.util.HSSFColor;public class POISample static HSSFWorkbook workbook; public static void main(String args) workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFRow row = new HSSFRow5; for (int i = 0; i 5 ; i+) rowi = sheet.createRow(i); HSSFCell cell = new HSSFCell54; for (int i = 0; i 5; i+) for (int j = 0; j 4 ; j+) cellij = rowi.createCell(short)j); setStyle(cell00, NO_FILL, HSSFCellStyle.NO_FILL); setStyle(cell01, SOLID_FOREGROUND, HSSFCellStyle.SOLID_FOREGROUND); setStyle(cell02, FINE_DOTS, HSSFCellStyle.FINE_DOTS); setStyle(cell03, ALT_BARS, HSSFCellStyle.ALT_BARS); setStyle(cell10, SPARSE_DOTS, HSSFCellStyle.SPARSE_DOTS); setStyle(cell11, THICK_HORZ_BANDS, HSSFCellStyle.THICK_HORZ_BANDS); setStyle(cell12, THICK_VERT_BANDS, HSSFCellStyle.THICK_VERT_BANDS); setStyle(cell13, THICK_BACKWARD_DIAG, HSSFCellStyle.THICK_BACKWARD_DIAG); setStyle(cell20, THICK_FORWARD_DIAG, HSSFCellStyle.THICK_FORWARD_DIAG); setStyle(cell21, BIG_SPOTS, HSSFCellStyle.BIG_SPOTS); setStyle(cell22, BRICKS, HSSFCellStyle.BRICKS); setStyle(cell23, THIN_HORZ_BANDS, HSSFCellStyle.THIN_HORZ_BANDS); setSt