POI操作EXCEL技巧(转)

1.创建工作簿 (WORKBOOK)

HSSFWorkbook wb = new HSSFWorkbook();

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

2.创建工作表（SHEET）

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet1 = wb.createSheet("new sheet");

HSSFSheet sheet2 = wb.createSheet("second sheet");

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

3.创建单元格(CELL)

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.

HSSFRow row = sheet.createRow((short)0);

// Create a cell and put a value in it.

HSSFCell cell = row.createCell((short)0);

cell.setCellValue(1);

// Or do it on one line.

row.createCell((short)1).setCellValue(1.2);

row.createCell((short)2).setCellValue("This is a string");

row.createCell((short)3).setCellValue(true);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

4.创建指定单元格式的单元格

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.

HSSFRow row = sheet.createRow((short)0);

// Create a cell and put a date value in it. The first cell is not styled

// as a date.

HSSFCell cell = row.createCell((short)0);

cell.setCellValue(new Date());

// we style the second cell as a date (and time). It is important to

// create a new cell style from the workbook otherwise you can end up

// modifying the built in style and effecting not only this cell but other cells.

HSSFCellStyle cellStyle = wb.createCellStyle();

cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

cell = row.createCell((short)1);

cell.setCellValue(new Date());

cell.setCellStyle(cellStyle);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

5. 单元格的不同格式

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short)2);

row.createCell((short) 0).setCellValue(1.1);

row.createCell((short) 1).setCellValue(new Date());

row.createCell((short) 2).setCellValue("a string");

row.createCell((short) 3).setCellValue(true);

row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

6.单元格的不通对齐方式

public static void main(String[] args)

throws IOException

{

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short) 2);

createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER);

createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_CENTER_SELECTION);

createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_FILL);

createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_GENERAL);

createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_JUSTIFY);

createCell(wb, row, (short) 5, HSSFCellStyle.ALIGN_LEFT);

createCell(wb, row, (short) 6, HSSFCellStyle.ALIGN_RIGHT);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

}

/**

* Creates a cell and aligns it a certain way.

*

* @param wb the workbook

* @param row the row to create the cell in

* @param column the column number to create the cell in

* @param align the alignment for the cell.

*/

private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align)

{

HSSFCell cell = row.createCell(column);

cell.setCellValue("Align It");

HSSFCellStyle cellStyle = wb.createCellStyle();

cellStyle.setAlignment(align);

cell.setCellStyle(cellStyle);

}

7.单元格的边框设置

Working with borders

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.

HSSFRow row = sheet.createRow((short) 1);

// Create a cell and put a value in it.

HSSFCell cell = row.createCell((short) 1);

cell.setCellValue(4);

// Style the cell with borders all around.

HSSFCellStyle style = wb.createCellStyle();

style.setBorderBottom(HSSFCellStyle.BORDER_THIN);

style.setBottomBorderColor(HSSFColor.BLACK.index);

style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

style.setLeftBorderColor(HSSFColor.GREEN.index);

style.setBorderRight(HSSFCellStyle.BORDER_THIN);

style.setRightBorderColor(HSSFColor.BLUE.index);

style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);

style.setTopBorderColor(HSSFColor.BLACK.index);

cell.setCellStyle(style);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

8.填充和颜色设置

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.

HSSFRow row = sheet.createRow((short) 1);

// Aqua background

HSSFCellStyle style = wb.createCellStyle();

style.setFillBackgroundColor(HSSFColor.AQUA.index);

style.setFillPattern(HSSFCellStyle.BIG_SPOTS);

HSSFCell cell = row.createCell((short) 1);

cell.setCellValue("X");

cell.setCellStyle(style);

// Orange "foreground", foreground being the fill foreground not the font color.

style = wb.createCellStyle();

style.setFillForegroundColor(HSSFColor.ORANGE.index);

style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

cell = row.createCell((short) 2);

cell.setCellValue("X");

cell.setCellStyle(style);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

9.合并单元格操作

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short) 1);

HSSFCell cell = row.createCell((short) 1);

cell.setCellValue("This is a test of merging");

sheet.addMergedRegion(new Region(1,(short)1,1,(short)2));

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

10.字体设置

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.

HSSFRow row = sheet.createRow((short) 1);

// Create a new font and alter it.

HSSFFont font = wb.createFont();

font.setFontHeightInPoints((short)24);

font.setFontName("Courier New");

font.setItalic(true);

font.setStrikeout(true);

// Fonts are set into a style so create a new one to use.

HSSFCellStyle style = wb.createCellStyle();

style.setFont(font);

// Create a cell and put a value in it.

HSSFCell cell = row.createCell((short) 1);

cell.setCellValue("This is a test of fonts");

cell.setCellStyle(style);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

11.自定义颜色

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet();

HSSFRow row = sheet.createRow((short) 0);

HSSFCell cell = row.createCell((short) 0);

cell.setCellValue("Default Palette");

//apply some colors from the standard palette,

// as in the previous examples.

//we'll use red text on a lime background

HSSFCellStyle style = wb.createCellStyle();

style.setFillForegroundColor(HSSFColor.LIME.index);

style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

HSSFFont font = wb.createFont();

font.setColor(HSSFColor.RED.index);

style.setFont(font);

cell.setCellStyle(style);

//save with the default palette

FileOutputStream out = new FileOutputStream("default_palette.xls");

wb.write(out);

out.close();

//now, let's replace RED and LIME in the palette

// with a more attractive combination

// (lovingly borrowed from freebsd.org)

cell.setCellValue("Modified Palette");

//creating a custom palette for the workbook

HSSFPalette palette = wb.getCustomPalette();

//replacing the standard red with freebsd.org red

palette.setColorAtIndex(HSSFColor.RED.index,

(byte) 153, //RGB red (0-255)

(byte) 0, //RGB green

(byte) 0 //RGB blue

);

//replacing lime with freebsd.org gold

palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102);

//save with the modified palette

// note that wherever we have previously used RED or LIME, the

// new colors magically appear

out = new FileOutputStream("modified_palette.xls");

wb.write(out);

out.close();

12.读和重写EXCEL文件

POIFSFileSystem fs =

new POIFSFileSystem(new FileInputStream("workbook.xls"));

HSSFWorkbook wb = new HSSFWorkbook(fs);

HSSFSheet sheet = wb.getSheetAt(0);

HSSFRow row = sheet.getRow(2);

HSSFCell cell = row.getCell((short)3);

if (cell == null)

cell = row.createCell((short)3);

cell.setCellType(HSSFCell.CELL_TYPE_STRING);

cell.setCellValue("a test");

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

13.在EXCEL单元格中使用自动换行

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet s = wb.createSheet();

HSSFRow r = null;

HSSFCell c = null;

HSSFCellStyle cs = wb.createCellStyle();

HSSFFont f = wb.createFont();

HSSFFont f2 = wb.createFont();

cs = wb.createCellStyle();

cs.setFont( f2 );

//Word Wrap MUST be turned on

cs.setWrapText( true );

r = s.createRow( (short) 2 );

r.setHeight( (short) 0x349 );

c = r.createCell( (short) 2 );

c.setCellType( HSSFCell.CELL_TYPE_STRING );

c.setCellValue( "Use \n with word wrap on to create a new line" );

c.setCellStyle( cs );

s.setColumnWidth( (short) 2, (short) ( ( 50 * 8 ) / ( (double) 1 / 20 ) ) );

FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );

wb.write( fileOut );

fileOut.close();

14.数字格式自定义

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("format sheet");

HSSFCellStyle style;

HSSFDataFormat format = wb.createDataFormat();

HSSFRow row;

HSSFCell cell;

short rowNum = 0;

short colNum = 0;

row = sheet.createRow(rowNum++);

cell = row.createCell(colNum);

cell.setCellValue(11111.25);

style = wb.createCellStyle();

style.setDataFormat(format.getFormat("0.0"));

cell.setCellStyle(style);

row = sheet.createRow(rowNum++);

cell = row.createCell(colNum);

cell.setCellValue(11111.25);

style = wb.createCellStyle();

style.setDataFormat(format.getFormat("#,##0.0000"));

cell.setCellStyle(style);

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

15.调整工作单位置

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("format sheet");

HSSFPrintSetup ps = sheet.getPrintSetup();

sheet.setAutobreaks(true);

ps.setFitHeight((short)1);

ps.setFitWidth((short)1);



// Create various cells and rows for spreadsheet.

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

16.设置打印区域

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("Sheet1");

wb.setPrintArea(0, "$A$1:$C$2");

//sets the print area for the first sheet

//Alternatively:

//wb.setPrintArea(0, 0, 1, 0, 0) is equivalent to using the name reference (See the JavaDocs for more details)

// Create various cells and rows for spreadsheet.

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

17.标注脚注

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("format sheet");

HSSFFooter footer = sheet.getFooter()

footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );



// Create various cells and rows for spreadsheet.

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

18.使用方便的内部提供的函数

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet1 = wb.createSheet( "new sheet" );

// Create a merged region

HSSFRow row = sheet1.createRow( (short) 1 );

HSSFRow row2 = sheet1.createRow( (short) 2 );

HSSFCell cell = row.createCell( (short) 1 );

cell.setCellValue( "This is a test of merging" );

Region region = new Region( 1, (short) 1, 4, (short) 4 );

sheet1.addMergedRegion( region );

// Set the border and border colors.

final short borderMediumDashed = HSSFCellStyle.BORDER_MEDIUM_DASHED;

HSSFRegionUtil.setBorderBottom( borderMediumDashed,

region, sheet1, wb );

HSSFRegionUtil.setBorderTop( borderMediumDashed,

region, sheet1, wb );

HSSFRegionUtil.setBorderLeft( borderMediumDashed,

region, sheet1, wb );

HSSFRegionUtil.setBorderRight( borderMediumDashed,

region, sheet1, wb );

HSSFRegionUtil.setBottomBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);

HSSFRegionUtil.setTopBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);

HSSFRegionUtil.setLeftBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);

HSSFRegionUtil.setRightBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);

// Shows some usages of HSSFCellUtil

HSSFCellStyle style = wb.createCellStyle();

style.setIndention((short)4);

HSSFCellUtil.createCell(row, 8, "This is the value of the cell", style);

HSSFCell cell2 = HSSFCellUtil.createCell( row2, 8, "This is the value of the cell");

HSSFCellUtil.setAlignment(cell2, wb, HSSFCellStyle.ALIGN_CENTER);

// Write out the workbook

FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );

wb.write( fileOut );

fileOut.close();

19.在工作单中移动行，调整行的上下位置

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("row sheet");

// Create various cells and rows for spreadsheet.

// Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)

sheet.shiftRows(5, 10, -5);

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

20.选种指定的工作单

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("row sheet");

sheet.setSelected(true);

// Create various cells and rows for spreadsheet.

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

21.工作单的放大缩小

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet1 = wb.createSheet("new sheet");

sheet1.setZoom(3,4); // 75 percent magnification

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

22.头注和脚注

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

HSSFHeader header = sheet.getHeader();

header.setCenter("Center Header");

header.setLeft("Left Header");

header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +

HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

//-------------------------------以上实例代码均来自官方网站

//-------------------------------POI中使用的颜色是用颜色索引来实现，如下：

/*

* 颜色对照表 数字代表颜色索引

8： BLACK

60： BROWN

59： OLIVE_GREEN

58： DARK_GREEN

56： DARK_TEAL

18： DARK_BLUE

32： DARK_BLUE

62： INDIGO

63： GREY_80_PERCENT

53： ORANGE

19： DARK_YELLOW

17： GREEN

21： TEAL

38： TEAL

12： BLUE

39： BLUE

54： BLUE_GREY

23： GREY_50_PERCENT

10： RED

52： LIGHT_ORANGE

50： LIME

57： SEA_GREEN

49： AQUA

48： LIGHT_BLUE

20： VIOLET

36： VIOLET

55： GREY_40_PERCENT

14： PINK

33： PINK

51： GOLD

13： YELLOW

34： YELLOW

11： BRIGHT_GREEN

35： BRIGHT_GREEN

15： TURQUOISE

35： TURQUOISE

16： DARK_RED

37： DARK_RED

40： SKY_BLUE

61： PLUM

25： PLUM

22： GREY_25_PERCENT

45： ROSE

43： LIGHT_YELLOW

42： LIGHT_GREEN

41： LIGHT_TURQUOISE

27：LIGHT_TURQUOISE

44： PALE_BLUE

46： LAVENDER

9： WHITE

24： CORNFLOWER_BLUE

26： LEMON_CHIFFON

25： MAROON

28： ORCHID

29： CORAL

30： ROYAL_BLUE

31： LIGHT_CORNFLOWER_BLUE

*/

//----------------------------------------------------你可以按上面的方法来自定义颜色

/*

* 自定义颜色，去掉注释，贴加，其他则查看颜色对照表

HSSFPalette palette = this.getCustomPalette();

palette.setColorAtIndex(idx,

i, //RGB red (0-255)

j, //RGB green

k //RGB blue

);

*/

1.创建工作簿 (WORKBOOK)

HSSFWorkbook wb = new HSSFWorkbook();

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

2.创建工作表（SHEET）

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet1 = wb.createSheet("new sheet");

HSSFSheet sheet2 = wb.createSheet("second sheet");

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

3.创建单元格(CELL)

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.

HSSFRow row = sheet.createRow((short)0);

// Create a cell and put a value in it.

HSSFCell cell = row.createCell((short)0);

cell.setCellValue(1);

// Or do it on one line.

row.createCell((short)1).setCellValue(1.2);

row.createCell((short)2).setCellValue("This is a string");

row.createCell((short)3).setCellValue(true);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

4.创建指定单元格式的单元格

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.

HSSFRow row = sheet.createRow((short)0);

// Create a cell and put a date value in it. The first cell is not styled

// as a date.

HSSFCell cell = row.createCell((short)0);

cell.setCellValue(new Date());

// we style the second cell as a date (and time). It is important to

// create a new cell style from the workbook otherwise you can end up

// modifying the built in style and effecting not only this cell but other cells.

HSSFCellStyle cellStyle = wb.createCellStyle();

cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

cell = row.createCell((short)1);

cell.setCellValue(new Date());

cell.setCellStyle(cellStyle);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

5. 单元格的不同格式

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short)2);

row.createCell((short) 0).setCellValue(1.1);

row.createCell((short) 1).setCellValue(new Date());

row.createCell((short) 2).setCellValue("a string");

row.createCell((short) 3).setCellValue(true);

row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

6.单元格的不通对齐方式

public static void main(String[] args)

throws IOException

{

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short) 2);

createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER);

createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_CENTER_SELECTION);

createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_FILL);

createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_GENERAL);

createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_JUSTIFY);

createCell(wb, row, (short) 5, HSSFCellStyle.ALIGN_LEFT);

createCell(wb, row, (short) 6, HSSFCellStyle.ALIGN_RIGHT);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

}

/**

* Creates a cell and aligns it a certain way.

*

* @param wb the workbook

* @param row the row to create the cell in

* @param column the column number to create the cell in

* @param align the alignment for the cell.

*/

private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align)

{

HSSFCell cell = row.createCell(column);

cell.setCellValue("Align It");

HSSFCellStyle cellStyle = wb.createCellStyle();

cellStyle.setAlignment(align);

cell.setCellStyle(cellStyle);

}

7.单元格的边框设置

Working with borders

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.

HSSFRow row = sheet.createRow((short) 1);

// Create a cell and put a value in it.

HSSFCell cell = row.createCell((short) 1);

cell.setCellValue(4);

// Style the cell with borders all around.

HSSFCellStyle style = wb.createCellStyle();

style.setBorderBottom(HSSFCellStyle.BORDER_THIN);

style.setBottomBorderColor(HSSFColor.BLACK.index);

style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

style.setLeftBorderColor(HSSFColor.GREEN.index);

style.setBorderRight(HSSFCellStyle.BORDER_THIN);

style.setRightBorderColor(HSSFColor.BLUE.index);

style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);

style.setTopBorderColor(HSSFColor.BLACK.index);

cell.setCellStyle(style);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

8.填充和颜色设置

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.

HSSFRow row = sheet.createRow((short) 1);

// Aqua background

HSSFCellStyle style = wb.createCellStyle();

style.setFillBackgroundColor(HSSFColor.AQUA.index);

style.setFillPattern(HSSFCellStyle.BIG_SPOTS);

HSSFCell cell = row.createCell((short) 1);

cell.setCellValue("X");

cell.setCellStyle(style);

// Orange "foreground", foreground being the fill foreground not the font color.

style = wb.createCellStyle();

style.setFillForegroundColor(HSSFColor.ORANGE.index);

style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

cell = row.createCell((short) 2);

cell.setCellValue("X");

cell.setCellStyle(style);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

9.合并单元格操作

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short) 1);

HSSFCell cell = row.createCell((short) 1);

cell.setCellValue("This is a test of merging");

sheet.addMergedRegion(new Region(1,(short)1,1,(short)2));

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

10.字体设置

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.

HSSFRow row = sheet.createRow((short) 1);

// Create a new font and alter it.

HSSFFont font = wb.createFont();

font.setFontHeightInPoints((short)24);

font.setFontName("Courier New");

font.setItalic(true);

font.setStrikeout(true);

// Fonts are set into a style so create a new one to use.

HSSFCellStyle style = wb.createCellStyle();

style.setFont(font);

// Create a cell and put a value in it.

HSSFCell cell = row.createCell((short) 1);

cell.setCellValue("This is a test of fonts");

cell.setCellStyle(style);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

11.自定义颜色

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet();

HSSFRow row = sheet.createRow((short) 0);

HSSFCell cell = row.createCell((short) 0);

cell.setCellValue("Default Palette");

//apply some colors from the standard palette,

// as in the previous examples.

//we'll use red text on a lime background

HSSFCellStyle style = wb.createCellStyle();

style.setFillForegroundColor(HSSFColor.LIME.index);

style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

HSSFFont font = wb.createFont();

font.setColor(HSSFColor.RED.index);

style.setFont(font);

cell.setCellStyle(style);

//save with the default palette

FileOutputStream out = new FileOutputStream("default_palette.xls");

wb.write(out);

out.close();

//now, let's replace RED and LIME in the palette

// with a more attractive combination

// (lovingly borrowed from freebsd.org)

cell.setCellValue("Modified Palette");

//creating a custom palette for the workbook

HSSFPalette palette = wb.getCustomPalette();

//replacing the standard red with freebsd.org red

palette.setColorAtIndex(HSSFColor.RED.index,

(byte) 153, //RGB red (0-255)

(byte) 0, //RGB green

(byte) 0 //RGB blue

);

//replacing lime with freebsd.org gold

palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102);

//save with the modified palette

// note that wherever we have previously used RED or LIME, the

// new colors magically appear

out = new FileOutputStream("modified_palette.xls");

wb.write(out);

out.close();

12.读和重写EXCEL文件

POIFSFileSystem fs =

new POIFSFileSystem(new FileInputStream("workbook.xls"));

HSSFWorkbook wb = new HSSFWorkbook(fs);

HSSFSheet sheet = wb.getSheetAt(0);

HSSFRow row = sheet.getRow(2);

HSSFCell cell = row.getCell((short)3);

if (cell == null)

cell = row.createCell((short)3);

cell.setCellType(HSSFCell.CELL_TYPE_STRING);

cell.setCellValue("a test");

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

13.在EXCEL单元格中使用自动换行

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet s = wb.createSheet();

HSSFRow r = null;

HSSFCell c = null;

HSSFCellStyle cs = wb.createCellStyle();

HSSFFont f = wb.createFont();

HSSFFont f2 = wb.createFont();

cs = wb.createCellStyle();

cs.setFont( f2 );

//Word Wrap MUST be turned on

cs.setWrapText( true );

r = s.createRow( (short) 2 );

r.setHeight( (short) 0x349 );

c = r.createCell( (short) 2 );

c.setCellType( HSSFCell.CELL_TYPE_STRING );

c.setCellValue( "Use \n with word wrap on to create a new line" );

c.setCellStyle( cs );

s.setColumnWidth( (short) 2, (short) ( ( 50 * 8 ) / ( (double) 1 / 20 ) ) );

FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );

wb.write( fileOut );

fileOut.close();

14.数字格式自定义

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("format sheet");

HSSFCellStyle style;

HSSFDataFormat format = wb.createDataFormat();

HSSFRow row;

HSSFCell cell;

short rowNum = 0;

short colNum = 0;

row = sheet.createRow(rowNum++);

cell = row.createCell(colNum);

cell.setCellValue(11111.25);

style = wb.createCellStyle();

style.setDataFormat(format.getFormat("0.0"));

cell.setCellStyle(style);

row = sheet.createRow(rowNum++);

cell = row.createCell(colNum);

cell.setCellValue(11111.25);

style = wb.createCellStyle();

style.setDataFormat(format.getFormat("#,##0.0000"));

cell.setCellStyle(style);

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

15.调整工作单位置

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("format sheet");

HSSFPrintSetup ps = sheet.getPrintSetup();

sheet.setAutobreaks(true);

ps.setFitHeight((short)1);

ps.setFitWidth((short)1);


// Create various cells and rows for spreadsheet.

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

16.设置打印区域

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("Sheet1");

wb.setPrintArea(0, "$A$1:$C$2");

//sets the print area for the first sheet

//Alternatively:

//wb.setPrintArea(0, 0, 1, 0, 0) is equivalent to using the name reference (See the JavaDocs for more details)

// Create various cells and rows for spreadsheet.

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

17.标注脚注

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("format sheet");

HSSFFooter footer = sheet.getFooter()

footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );



// Create various cells and rows for spreadsheet.

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

18.使用方便的内部提供的函数

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet1 = wb.createSheet( "new sheet" );

// Create a merged region

HSSFRow row = sheet1.createRow( (short) 1 );

HSSFRow row2 = sheet1.createRow( (short) 2 );

HSSFCell cell = row.createCell( (short) 1 );

cell.setCellValue( "This is a test of merging" );

Region region = new Region( 1, (short) 1, 4, (short) 4 );

sheet1.addMergedRegion( region );

// Set the border and border colors.

final short borderMediumDashed = HSSFCellStyle.BORDER_MEDIUM_DASHED;

HSSFRegionUtil.setBorderBottom( borderMediumDashed,

region, sheet1, wb );

HSSFRegionUtil.setBorderTop( borderMediumDashed,

region, sheet1, wb );

HSSFRegionUtil.setBorderLeft( borderMediumDashed,

region, sheet1, wb );

HSSFRegionUtil.setBorderRight( borderMediumDashed,

region, sheet1, wb );

HSSFRegionUtil.setBottomBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);

HSSFRegionUtil.setTopBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);

HSSFRegionUtil.setLeftBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);

HSSFRegionUtil.setRightBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);

// Shows some usages of HSSFCellUtil

HSSFCellStyle style = wb.createCellStyle();

style.setIndention((short)4);

HSSFCellUtil.createCell(row, 8, "This is the value of the cell", style);

HSSFCell cell2 = HSSFCellUtil.createCell( row2, 8, "This is the value of the cell");

HSSFCellUtil.setAlignment(cell2, wb, HSSFCellStyle.ALIGN_CENTER);

// Write out the workbook

FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );

wb.write( fileOut );

fileOut.close();

19.在工作单中移动行，调整行的上下位置

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("row sheet");

// Create various cells and rows for spreadsheet.

// Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)

sheet.shiftRows(5, 10, -5);

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

20.选种指定的工作单

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("row sheet");

sheet.setSelected(true);

// Create various cells and rows for spreadsheet.

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

21.工作单的放大缩小

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet1 = wb.createSheet("new sheet");

sheet1.setZoom(3,4); // 75 percent magnification

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

22.头注和脚注

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");

HSSFHeader header = sheet.getHeader();

header.setCenter("Center Header");

header.setLeft("Left Header");

header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +

HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

//-------------------------------以上实例代码均来自官方网站

//-------------------------------POI中使用的颜色是用颜色索引来实现，如下：

/*

* 颜色对照表 数字代表颜色索引

8： BLACK

60： BROWN

59： OLIVE_GREEN

58： DARK_GREEN

56： DARK_TEAL

18： DARK_BLUE

32： DARK_BLUE

62： INDIGO

63： GREY_80_PERCENT

53： ORANGE

19： DARK_YELLOW

17： GREEN

21： TEAL

38： TEAL

12： BLUE

39： BLUE

54： BLUE_GREY

23： GREY_50_PERCENT

10： RED

52： LIGHT_ORANGE

50： LIME

57： SEA_GREEN

49： AQUA

48： LIGHT_BLUE

20： VIOLET

36： VIOLET

55： GREY_40_PERCENT

14： PINK

33： PINK

51： GOLD

13： YELLOW

34： YELLOW

11： BRIGHT_GREEN

35： BRIGHT_GREEN

15： TURQUOISE

35： TURQUOISE

16： DARK_RED

37： DARK_RED

40： SKY_BLUE

61： PLUM

25： PLUM

22： GREY_25_PERCENT

45： ROSE

43： LIGHT_YELLOW

42： LIGHT_GREEN

41： LIGHT_TURQUOISE

27：LIGHT_TURQUOISE

44： PALE_BLUE

46： LAVENDER

9： WHITE

24： CORNFLOWER_BLUE

26： LEMON_CHIFFON

25： MAROON

28： ORCHID

29： CORAL

30： ROYAL_BLUE

31： LIGHT_CORNFLOWER_BLUE

*/

//----------------------------------------------------你可以按上面的方法来自定义颜色

/*

* 自定义颜色，去掉注释，贴加，其他则查看颜色对照表

HSSFPalette palette = this.getCustomPalette();

palette.setColorAtIndex(idx,

i, //RGB red (0-255)

j, //RGB green

k //RGB blue

);

*/