POI学习笔记（三）
14. 自定义颜色HSSF：
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：
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. 读取和重写工作薄
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. 单元格内容换行
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. 数据格式化
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. 设置打印区域
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. 设置页脚
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
Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("row sheet"); sheet.setSelected(true);
21. 设置方法倍率
Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); sheet1.setZoom(3,4); // 75 percent magnification Zoom是一个分数，例如如果方法75%，那么分子为3，分母为4。
22. 设置打印时的页宽和页高
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. 移动行
public void shiftRow() throws Exception{Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("row sheet"); for(int i=0;i<20;i++){ Row row=sheet.createRow(i); Cell cell=row.createCell(0); cell.setCellValue(""+i); } // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5) //把第6-11行向上移动5行 sheet.shiftRows(5, 10,-5); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();} POI学习笔记四将会介绍poi更高级的一些知识点。
页:
[1]