`

POI学习笔记（三）

    博客分类：
  • java
FreeBSD 
阅读更多

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.     }  
分享到：
| POI学习笔记（二）
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics