通过POI来进行单元格格式的设定
设定格式使用「HSSFCellStyle」类。它有一个构造方法:
protected HSSFCellStyle(short index, ExtendedFormatRecord rec)
虽然有构造方法,但却是protected的,所以不能直接使用,要通过一个工作簿workbook来生成格式对象。
在POI里,格式好像是以workbook为单位来管理的,所以要先作成一个格式对象,保存在workbook里,然后再对已生成好的单元格进行设定。
在单元格里指定格式
1,要作成一个格式对象,可以使用「HSSFWorkbook」类的「createCellStyle」方法。
public HSSFCellStyle createCellStyle()
2,要取出现有的格式对象的话,使用「HSSFWorkbook」类的「getCellStyleAt」方法,这个方法有参数,是被保存格式的INDEX号。
public HSSFCellStyle getCellStyleAt(short idx)
3,对于某一个单元格,也可以取出它的格式对象。这时要使用「HSSFCell」类的「getCellStyle」方法。
public HSSFCellStyle getCellStyle()
4,这样的话,不管是新创建的或者是从现有的单元格里取出来的格式对象,都可以用来对某一个单元格进行格式的设定。设定方法使用「HSSFCell」类的「setCellStyle」方法。
public void setCellStyle(HSSFCellStyle style)package linkin;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
public class Linkin
{
public static void main(String[] args)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = sheet.createRow(1);//第二行
HSSFCell cell = row.createCell(0);//2,1格
cell.setCellValue("sample");//写入sample
HSSFCellStyle style = workbook.createCellStyle();//创建个workbook的HSSFCellStyle格式对象style
//设定格式
style.setFillBackgroundColor(HSSFColor.WHITE.index);
style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
style.setFillPattern(HSSFCellStyle.THICK_HORZ_BANDS);
cell.setCellStyle(style);//对2,1格写入上面的格式
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());
}
}
}
}
POI单元格颜色设定
先从单元格顔色设定开始介绍。单元格的顔色有前景色和背景色。
1,前景色的设定使用「HSSFCellStyle」类的「setFillForegroundColor」方法。
public void setFillForegroundColor(short bg)
2,背景色的设定则使用「HSSFCellStyle」类的「setFillBackgroundColor」方法。
public void setFillBackgroundColor(short bg)
两个方法都是通过参数来设定具体什么顔色。该参数类型为short型,在「HSSFColor」类里,准备了各种各样顔色的定义值。
HSSFColor类
HSSFColor类定义如下:public class HSSFColor extends java.lang.Object
而各种顔色又是作为HSSFColor类的子类,定义一览表如下:
HSSFColor.AQUA HSSFColor.BLACK
HSSFColor.BLUE HSSFColor.BLUE_GREY
HSSFColor.BRIGHT_GREENHSSFColor.BROWN
HSSFColor.CORALHSSFColor.CORNFLOWER_BLUE
HSSFColor.DARK_BLUEHSSFColor.DARK_GREEN
HSSFColor.DARK_REDHSSFColor.DARK_TEAL
HSSFColor.DARK_YELLOWHSSFColor.GOLD
HSSFColor.GREENHSSFColor.GREY_25_PERCENT
HSSFColor.GREY_40_PERCENTHSSFColor.GREY_50_PERCENT
HSSFColor.GREY_80_PERCENTHSSFColor.INDIGO
HSSFColor.LAVENDERHSSFColor.LEMON_CHIFFON
HSSFColor.LIGHT_BLUEHSSFColor.LIGHT_CORNFLOWER_BLUE
HSSFColor.LIGHT_GREENHSSFColor.LIGHT_ORANGE
HSSFColor.LIGHT_TURQUOISEHSSFColor.LIGHT_YELLOW
HSSFColor.LIMEHSSFColor.MAROON
HSSFColor.OLIVE_GREENHSSFColor.ORANGE
HSSFColor.ORCHIDHSSFColor.PALE_BLUE
HSSFColor.PINKHSSFColor.PLUM
HSSFColor.REDHSSFColor.ROSE
HSSFColor.ROYAL_BLUEHSSFColor.SEA_GREEN
HSSFColor.SKY_BLUEHSSFColor.TAN
HSSFColor.TEALHSSFColor.TURQUOISE
HSSFColor.VIOLETHSSFColor.WHITE
HSSFColor.YELLOW
设定顔色时,用这些子类的静态常量「index」作为参数,使用方法如下:
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.LIME.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
如果这些顔色还不够你用的话,那么下面介绍怎么设定自己想要的顔色。
填充模式
1,指定填充模式的话,使用「HSSFCellStyle」类的「setFillPattern」方法。
public void setFillPattern(short fp)
2,指定的填充模式,在「HSSFCellStyle」类里也有定义,类型为static short型,如下所示:
值说明
NO_FILLNo background
SOLID_FOREGROUNDSolidly filled
FINE_DOTSSmall fine dots
ALT_BARSWide dots
SPARSE_DOTSSparse dots
THICK_HORZ_BANDSThick horizontal bands
THICK_VERT_BANDSThick vertical bands
THICK_BACKWARD_DIAGThick backward facing diagonals
THICK_FORWARD_DIAGThick forward facing diagonals
BIG_SPOTSLarge spots
BRICKSBrick-like layout
THIN_HORZ_BANDSThin horizontal bands
THIN_VERT_BANDSThin vertical bands
THIN_BACKWARD_DIAGThin backward diagonal
THIN_FORWARD_DIAGThin forward diagonal
SQUARESSquares
DIAMONDSDiamonds
实际的使用方法如下:
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.LIME.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);package linkin;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
public class Linkin
{
static HSSFWorkbook workbook;
public static void main(String[] args)
{
workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow row[] = new HSSFRow[12];
for (int i = 0; i < 12; i++)
{
row[i] = sheet.createRow(i);
}
HSSFCell cell[][] = new HSSFCell[12][4];
for (int i = 0; i < 12; i++)
{
for (int j = 0; j < 4; j++)
{
cell[i][j] = row[i].createCell((short) j);
}
}
setStyle(cell[0][0], "AQUA", HSSFColor.AQUA.index);
setStyle(cell[0][1], "BLACK", HSSFColor.BLACK.index);
setStyle(cell[0][2], "BLUE", HSSFColor.BLUE.index);
setStyle(cell[0][3], "BLUE_GREY", HSSFColor.BLUE_GREY.index);
setStyle(cell[1][0], "BRIGHT_GREEN", HSSFColor.BRIGHT_GREEN.index);
setStyle(cell[1][1], "BROWN", HSSFColor.BROWN.index);
setStyle(cell[1][2], "CORAL", HSSFColor.CORAL.index);
setStyle(cell[1][3], "CORNFLOWER_BLUE", HSSFColor.CORNFLOWER_BLUE.index);
setStyle(cell[2][0], "DARK_BLUE", HSSFColor.DARK_BLUE.index);
setStyle(cell[2][1], "DARK_GREEN", HSSFColor.DARK_GREEN.index);
setStyle(cell[2][2], "DARK_RED", HSSFColor.DARK_RED.index);
setStyle(cell[2][3], "DARK_TEAL", HSSFColor.DARK_TEAL.index);
setStyle(cell[3][0], "DARK_YELLOW", HSSFColor.DARK_YELLOW.index);
setStyle(cell[3][1], "GOLD", HSSFColor.GOLD.index);
setStyle(cell[3][2], "GREEN", HSSFColor.GREEN.index);
setStyle(cell[3][3], "GREY_25_PERCENT", HSSFColor.GREY_25_PERCENT.index);
setStyle(cell[4][0], "GREY_40_PERCENT", HSSFColor.GREY_40_PERCENT.index);
setStyle(cell[4][1], "GREY_50_PERCENT", HSSFColor.GREY_50_PERCENT.index);
setStyle(cell[4][2], "GREY_80_PERCENT", HSSFColor.GREY_80_PERCENT.index);
setStyle(cell[4][3], "INDIGO", HSSFColor.INDIGO.index);
setStyle(cell[5][0], "LAVENDER", HSSFColor.LAVENDER.index);
setStyle(cell[5][1], "LEMON_CHIFFON", HSSFColor.LEMON_CHIFFON.index);
setStyle(cell[5][2], "LIGHT_BLUE", HSSFColor.LIGHT_BLUE.index);
setStyle(cell[5][3], "LIGHT_CORNFLOWER_BLUE", HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
setStyle(cell[6][0], "LIGHT_GREEN", HSSFColor.LIGHT_GREEN.index);
setStyle(cell[6][1], "LIGHT_ORANGE", HSSFColor.LIGHT_ORANGE.index);
setStyle(cell[6][2], "LIGHT_TURQUOISE", HSSFColor.LIGHT_TURQUOISE.index);
setStyle(cell[6][3], "LIGHT_YELLOW", HSSFColor.LIGHT_YELLOW.index);
setStyle(cell[7][0], "LIME", HSSFColor.LIME.index);
setStyle(cell[7][1], "MAROON", HSSFColor.MAROON.index);
setStyle(cell[7][2], "OLIVE_GREEN", HSSFColor.OLIVE_GREEN.index);
setStyle(cell[7][3], "ORANGE", HSSFColor.ORANGE.index);
setStyle(cell[8][0], "ORCHID", HSSFColor.ORCHID.index);
setStyle(cell[8][1], "PALE_BLUE", HSSFColor.PALE_BLUE.index);
setStyle(cell[8][2], "PINK", HSSFColor.PINK.index);
setStyle(cell[8][3], "PLUM", HSSFColor.PLUM.index);
setStyle(cell[9][0], "RED", HSSFColor.RED.index);
setStyle(cell[9][1], "ROSE", HSSFColor.ROSE.index);
setStyle(cell[9][2], "ROYAL_BLUE", HSSFColor.ROYAL_BLUE.index);
setStyle(cell[9][3], "SEA_GREEN", HSSFColor.SEA_GREEN.index);
setStyle(cell[10][0], "SKY_BLUE", HSSFColor.SKY_BLUE.index);
setStyle(cell[10][1], "TAN", HSSFColor.TAN.index);
setStyle(cell[10][2], "TEAL", HSSFColor.TEAL.index);
setStyle(cell[10][3], "TURQUOISE", HSSFColor.TURQUOISE.index);
setStyle(cell[11][0], "VIOLET", HSSFColor.VIOLET.index);
setStyle(cell[11][1], "WHITE", HSSFColor.WHITE.index);
setStyle(cell[11][2], "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」,因此顔色应该是全部充满整个单元格的
package linkin;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
public class Linkin
{
static HSSFWorkbook workbook;
public static void main(String[] args)
{
workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow row[] = new HSSFRow[5];
for (int i = 0; i < 5; i++)
{
row[i] = sheet.createRow(i);
}
HSSFCell cell[][] = new HSSFCell[5][4];
for (int i = 0; i < 5; i++)
{
for (int j = 0; j < 4; j++)
{
cell[i][j] = row[i].createCell((short) j);
}
}
setStyle(cell[0][0], "NO_FILL", HSSFCellStyle.NO_FILL);
setStyle(cell[0][1], "SOLID_FOREGROUND", HSSFCellStyle.SOLID_FOREGROUND);
setStyle(cell[0][2], "FINE_DOTS", HSSFCellStyle.FINE_DOTS);
setStyle(cell[0][3], "ALT_BARS", HSSFCellStyle.ALT_BARS);
setStyle(cell[1][0], "SPARSE_DOTS", HSSFCellStyle.SPARSE_DOTS);
setStyle(cell[1][1], "THICK_HORZ_BANDS", HSSFCellStyle.THICK_HORZ_BANDS);
setStyle(cell[1][2], "THICK_VERT_BANDS", HSSFCellStyle.THICK_VERT_BANDS);
setStyle(cell[1][3], "THICK_BACKWARD_DIAG", HSSFCellStyle.THICK_BACKWARD_DIAG);
setStyle(cell[2][0], "THICK_FORWARD_DIAG", HSSFCellStyle.THICK_FORWARD_DIAG);
setStyle(cell[2][1], "BIG_SPOTS", HSSFCellStyle.BIG_SPOTS);
setStyle(cell[2][2], "BRICKS", HSSFCellStyle.BRICKS);
setStyle(cell[2][3], "THIN_HORZ_BANDS", HSSFCellStyle.THIN_HORZ_BANDS);
setStyle(cell[3][0], "THIN_VERT_BANDS", HSSFCellStyle.THIN_VERT_BANDS);
setStyle(cell[3][1], "THIN_BACKWARD_DIAG", HSSFCellStyle.THIN_BACKWARD_DIAG);
setStyle(cell[3][2], "THIN_FORWARD_DIAG", HSSFCellStyle.THIN_FORWARD_DIAG);
setStyle(cell[3][3], "SQUARES", HSSFCellStyle.SQUARES);
setStyle(cell[4][0], "DIAMONDS", HSSFCellStyle.DIAMONDS);
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 fps, short fp)
{
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.WHITE.index);
style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
style.setFillPattern(fp);
cell.setCellStyle(style);
cell.setCellValue(fps);
}
}
上面固定了「ForegroundColor」和「BackgroundColor」,而填充模式则做了各种尝试
POI设置边框
在做一个电子表格时,边框的设置有时是必不可少的。这一节就来介绍边框,设置时,可以指定边框的位置,边框的种类,边框的顔色。 首先是边框的位置和种类。对单元格设置边框时,有上下左右位置之分,所以POI也准备了四个不同的方法。
上部的边框: public void setBorderTop(short border)
下部的边框: public void setBorderBottom(short border)
左侧的边框: public void setBorderLeft(short border)
右侧的边框: public void setBorderRight(short border)
参数通过表示边框种类的short型值来指定。下面是定义在「HSSFCellStyle」类里可以被指定值的一览表。
值说明
BORDER_DASH_DOTdash-dot border
BORDER_DASH_DOT_DOTdash-dot-dot border
BORDER_DASHEDdash border
BORDER_DOTTEDdot borderhair-line border
BORDER_DOUBLEdouble-line border
BORDER_HAIRhair-line border
BORDER_MEDIUMMedium border
BORDER_MEDIUM_DASH_DOTmedium dash-dot border
BORDER_MEDIUM_DASH_DOT_DOTmedium dash-dot-dot border
BORDER_MEDIUM_DASHEDMedium dashed border
BORDER_NONENo border
BORDER_SLANTED_DASH_DOTslanted dash-dot border
BORDER_THICKThick border
BORDER_THINThin border
比如要在单元格下边框设置两重线的边框时,按如下方法:HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
下面再看看指定边框顔色。同样也分为上下左右边框来操作。
上部的边框: public void setTopBorderColor(short color)
下部的边框: public void setBottomBorderColor(short color)
左侧的边框: public void setLeftBorderColor(short color)
右侧的边框: public void setRightBorderColor(short color)
仍然是通过参数来指定顔色,而且使用方法和前面一节也是一样。具体如下:
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = workbook.createCellStyle();
style.setRightBorderColor(HSSFColor.RED.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
package linkin;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
public class Linkin
{
public static void main(String[] args)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = sheet.createRow(1);
HSSFCell cell1 = row.createCell((short) 1);
HSSFCell cell2 = row.createCell((short) 2);
HSSFCellStyle style1 = workbook.createCellStyle();
style1.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
style1.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);
style1.setTopBorderColor(HSSFColor.GOLD.index);
style1.setLeftBorderColor(HSSFColor.PLUM.index);
cell1.setCellStyle(style1);
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
style2.setBorderRight(HSSFCellStyle.BORDER_DOUBLE);
style2.setBottomBorderColor(HSSFColor.ORANGE.index);
style2.setRightBorderColor(HSSFColor.SKY_BLUE.index);
cell2.setCellStyle(style2);
cell1.setCellValue("U & L");
cell2.setCellValue("B & R");
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());
}
}
}
}
上面程序既改了顔色,也设置了上和左的边框各一个,右和下的边框各一个。
package linkin;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
public class Linkin
{
static HSSFWorkbook workbook;
public static void main(String[] args)
{
workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow row[] = new HSSFRow[5];
for (int i = 0; i < 5; i++)
{
row[i] = sheet.createRow(i);
}
HSSFCell cell[][] = new HSSFCell[5][3];
for (int i = 0; i < 5; i++)
{
for (int j = 0; j < 3; j++)
{
cell[i][j] = row[i].createCell((short) j);
}
}
setStyle(cell[0][0], "DASH_DOT", HSSFCellStyle.BORDER_DASH_DOT);
setStyle(cell[0][1], "DASH_DOT_DOT", HSSFCellStyle.BORDER_DASH_DOT_DOT);
setStyle(cell[0][2], "DASHED", HSSFCellStyle.BORDER_DASHED);
setStyle(cell[1][0], "DOTTED", HSSFCellStyle.BORDER_DOTTED);
setStyle(cell[1][1], "DOUBLE", HSSFCellStyle.BORDER_DOUBLE);
setStyle(cell[1][2], "HAIR", HSSFCellStyle.BORDER_HAIR);
setStyle(cell[2][0], "MEDIUM", HSSFCellStyle.BORDER_MEDIUM);
setStyle(cell[2][1], "MEDIUM_DASH_DOT", HSSFCellStyle.BORDER_MEDIUM_DASH_DOT);
setStyle(cell[2][2], "MEDIUM_DASH_DOT_DOT", HSSFCellStyle.BORDER_MEDIUM_DASH_DOT_DOT);
setStyle(cell[3][0], "MEDIUM_DASHED", HSSFCellStyle.BORDER_MEDIUM_DASHED);
setStyle(cell[3][1], "NONE", HSSFCellStyle.BORDER_NONE);
setStyle(cell[3][2], "SLANTED_DASH_DOT", HSSFCellStyle.BORDER_SLANTED_DASH_DOT);
setStyle(cell[4][0], "THICK", HSSFCellStyle.BORDER_THICK);
setStyle(cell[4][1], "THIN", HSSFCellStyle.BORDER_THIN);
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 bn, short border)
{
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(border);
style.setBottomBorderColor(HSSFColor.ORANGE.index);
cell.setCellStyle(style);
cell.setCellValue(bn);
}
}