POI学习笔记（三）

14. 自定义颜色

      HSSF：

 

Java代码

1. HSSFWorkbook wb = new HSSFWorkbook();   
2.     HSSFSheet sheet = wb.createSheet();   
3.     HSSFRow row = sheet.createRow((short) 0);   
4.     HSSFCell cell = row.createCell((short) 0);   
5.     cell.setCellValue("Default Palette");   
6.   
7.     //apply some colors from the standard palette,   
8.     // as in the previous examples.   
9.     //we'll use red text on a lime background   
10.   
11.     HSSFCellStyle style = wb.createCellStyle();   
12.     style.setFillForegroundColor(HSSFColor.LIME.index);   
13.     style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);   
14.   
15.     HSSFFont font = wb.createFont();   
16.     font.setColor(HSSFColor.RED.index);   
17.     style.setFont(font);   
18.   
19.     cell.setCellStyle(style);   
20.   
21.     //save with the default palette   
22.     FileOutputStream out = new FileOutputStream("default_palette.xls");   
23.     wb.write(out);   
24.     out.close();   
25.   
26.     //now, let's replace RED and LIME in the palette   
27.     // with a more attractive combination   
28.     // (lovingly borrowed from freebsd.org)   
29.   
30.     cell.setCellValue("Modified Palette");   
31.   
32.     //creating a custom palette for the workbook   
33.     HSSFPalette palette = wb.getCustomPalette();   
34.   
35.     //replacing the standard red with freebsd.org red   
36.     palette.setColorAtIndex(HSSFColor.RED.index,   
37.             (byte) 153,  //RGB red (0-255)   
38.             (byte) 0,    //RGB green   
39.             (byte) 0     //RGB blue   
40.     );   
41.     //replacing lime with freebsd.org gold   
42.     palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102);   
43.   
44.     //save with the modified palette   
45.     // note that wherever we have previously used RED or LIME, the   
46.     // new colors magically appear   
47.     out = new FileOutputStream("modified_palette.xls");   
48.     wb.write(out);   
49.     out.close();  

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();

 

XSSF：

 

Java代码

1. XSSFWorkbook wb = new XSSFWorkbook();   
2.     XSSFSheet sheet = wb.createSheet();   
3.     XSSFRow row = sheet.createRow(0);   
4.     XSSFCell cell = row.createCell( 0);   
5.     cell.setCellValue("custom XSSF colors");   
6.   
7.     XSSFCellStyle style1 = wb.createCellStyle();   
8.     style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128)));   
9.     style1.setFillPattern(CellStyle.SOLID_FOREGROUND);  

XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    XSSFRow row = sheet.createRow(0);
    XSSFCell cell = row.createCell( 0);
    cell.setCellValue("custom XSSF colors");

    XSSFCellStyle style1 = wb.createCellStyle();
    style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128)));
    style1.setFillPattern(CellStyle.SOLID_FOREGROUND);

 

 

15. 读取和重写工作薄

 

Java代码

1. InputStream inp = new FileInputStream("workbook.xls");   
2.     //InputStream inp = new FileInputStream("workbook.xlsx");   
3.   
4.     Workbook wb = WorkbookFactory.create(inp);   
5.     Sheet sheet = wb.getSheetAt(0);   
6.     Row row = sheet.getRow(2);   
7.     Cell cell = row.getCell(3);   
8.     if (cell == null)   
9.         cell = row.createCell(3);   
10.     cell.setCellType(Cell.CELL_TYPE_STRING);   
11.     cell.setCellValue("a test");   
12.   
13.     // Write the output to a file   
14.     FileOutputStream fileOut = new FileOutputStream("workbook.xls");   
15.     wb.write(fileOut);   
16.     fileOut.close();   
17.                       

InputStream inp = new FileInputStream("workbook.xls");
    //InputStream inp = new FileInputStream("workbook.xlsx");

    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);
    Row row = sheet.getRow(2);
    Cell cell = row.getCell(3);
    if (cell == null)
        cell = row.createCell(3);
    cell.setCellType(Cell.CELL_TYPE_STRING);
    cell.setCellValue("a test");

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    

 

 

16. 单元格内容换行

 

Java代码

1. Workbook wb = new XSSFWorkbook();   //or new HSSFWorkbook();   
2.     Sheet sheet = wb.createSheet();   
3.   
4.     Row row = sheet.createRow(2);   
5.     Cell cell = row.createCell(2);   
6.     cell.setCellValue("Use \n with word wrap on to create a new line");   
7.   
8.     //to enable newlines you need set a cell styles with wrap=true   
9.     CellStyle cs = wb.createCellStyle();   
10.     cs.setWrapText(true);   
11.     cell.setCellStyle(cs);   
12.   
13.     //increase row height to accomodate two lines of text   
14.     row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));   
15.   
16.     //adjust column width to fit the content   
17.     sheet.autoSizeColumn((short)2);   
18.   
19.     FileOutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx");   
20.     wb.write(fileOut);   
21.     fileOut.close();  

Workbook wb = new XSSFWorkbook();   //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet();

    Row row = sheet.createRow(2);
    Cell cell = row.createCell(2);
    cell.setCellValue("Use \n with word wrap on to create a new line");

    //to enable newlines you need set a cell styles with wrap=true
    CellStyle cs = wb.createCellStyle();
    cs.setWrapText(true);
    cell.setCellStyle(cs);

    //increase row height to accomodate two lines of text
    row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));

    //adjust column width to fit the content
    sheet.autoSizeColumn((short)2);

    FileOutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx");
    wb.write(fileOut);
    fileOut.close();

 

 换行的步骤：

• 在需要换行的地方加上\n   cell.setCellValue("Use \n with word wrap on to create a new line");
• 设置行高 row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));
• 设置列只适应宽度 sheet.autoSizeColumn((short)2);

 17. 数据格式化

 

Java代码

1. public void dataFormat() throws Exception{   
2.         Workbook wb = new HSSFWorkbook();   
3.         Sheet sheet = wb.createSheet("format sheet");   
4.         CellStyle style;   
5.         DataFormat format = wb.createDataFormat();   
6.         Row row;   
7.         Cell cell;   
8.         short rowNum = 0;   
9.         short colNum = 0;   
10.   
11.         row = sheet.createRow(rowNum++);   
12.         cell = row.createCell(colNum);   
13.         cell.setCellValue(11111.25);   
14.         style = wb.createCellStyle();   
15.         style.setDataFormat(format.getFormat("0.0"));   
16.         cell.setCellStyle(style);   
17.   
18.         row = sheet.createRow(rowNum++);   
19.         cell = row.createCell(colNum);   
20.         cell.setCellValue(1111.25);   
21.         style = wb.createCellStyle();   
22.         style.setDataFormat(format.getFormat("#,###.0000"));   
23.         cell.setCellStyle(style);   
24.   
25.         FileOutputStream fileOut = new FileOutputStream("workbook.xls");   
26.         wb.write(fileOut);   
27.         fileOut.close();   
28.   
29.     }  

public void dataFormat() throws Exception{
		Workbook wb = new HSSFWorkbook();
	    Sheet sheet = wb.createSheet("format sheet");
	    CellStyle style;
	    DataFormat format = wb.createDataFormat();
	    Row row;
	    Cell 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(1111.25);
	    style = wb.createCellStyle();
	    style.setDataFormat(format.getFormat("#,###.0000"));
	    cell.setCellStyle(style);

	    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
	    wb.write(fileOut);
	    fileOut.close();

	}

 

18. 设置打印区域

 

Java代码

1. Workbook wb = new HSSFWorkbook();   
2.     Sheet sheet = wb.createSheet("Sheet1");   
3.     //sets the print area for the first sheet   
4.     wb.setPrintArea(0, "$A$1:$C$2");   
5.        
6.     //Alternatively:   
7.     wb.setPrintArea(   
8.             0, //sheet index   
9.             0, //start column   
10.             1, //end column   
11.             0, //start row   
12.             0  //end row   
13.     );   
14.   
15.     FileOutputStream fileOut = new FileOutputStream("workbook.xls");   
16.     wb.write(fileOut);   
17.     fileOut.close();  

Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("Sheet1");
    //sets the print area for the first sheet
    wb.setPrintArea(0, "$A$1:$C$2");
    
    //Alternatively:
    wb.setPrintArea(
            0, //sheet index
            0, //start column
            1, //end column
            0, //start row
            0  //end row
    );

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();

 

19. 设置页脚

 

   

Java代码

1. HSSFWorkbook wb = new HSSFWorkbook();   
2.     HSSFSheet sheet = wb.createSheet("format sheet");   
3.     HSSFFooter footer = sheet.getFooter();   
4.   
5.     footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );   
6.   
7.   
8.   
9.     // Create various cells and rows for spreadsheet.   
10.   
11.     FileOutputStream fileOut = new FileOutputStream("workbook.xls");   
12.     wb.write(fileOut);   
13.     fileOut.close();  

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();

 

  备注：只有在打印预览的时候页脚才显示出来。

 20. 选中一个Sheet

Java代码

1. Workbook wb = new HSSFWorkbook();   
2.   Sheet sheet = wb.createSheet("row sheet");   
3.   sheet.setSelected(true);  

  Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("row sheet");
    sheet.setSelected(true);

 

 21. 设置方法倍率

Java代码

1. Workbook wb = new HSSFWorkbook();   
2.    Sheet sheet1 = wb.createSheet("new sheet");   
3.    sheet1.setZoom(3,4);   // 75 percent magnification  

 Workbook wb = new HSSFWorkbook();
    Sheet sheet1 = wb.createSheet("new sheet");
    sheet1.setZoom(3,4);   // 75 percent magnification

 Zoom是一个分数，例如如果方法75%，那么分子为3，分母为4。

 

  22. 设置打印时的页宽和页高

 

Java代码

1. Workbook wb = new HSSFWorkbook();   
2.     Sheet sheet = wb.createSheet("format sheet");   
3.     PrintSetup ps = sheet.getPrintSetup();   
4.   
5.     sheet.setAutobreaks(true);   
6.   
7.     ps.setFitHeight((short)1);   
8.     ps.setFitWidth((short)1);   
9.   
10.   
11.     // Create various cells and rows for spreadsheet.   
12.   
13.     FileOutputStream fileOut = new FileOutputStream("workbook.xls");   
14.     wb.write(fileOut);   
15.     fileOut.close();  

Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("format sheet");
    PrintSetup 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();

 

23. 移动行

 

Java代码

1. public void shiftRow() throws Exception{   
2.         Workbook wb = new HSSFWorkbook();   
3.         Sheet sheet = wb.createSheet("row sheet");   
4.   
5.         for(int i=0;i<20;i++){   
6.             Row row=sheet.createRow(i);   
7.             Cell cell=row.createCell(0);   
8.             cell.setCellValue(""+i);   
9.         }   
10.         // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)   
11.         //把第6-11行向上移动5行   
12.         sheet.shiftRows(5, 10,-5);   
13.         FileOutputStream fileOut = new FileOutputStream("workbook.xls");   
14.         wb.write(fileOut);   
15.         fileOut.close();   
16.     }