Busy Developers' Guide to HSSF and XSSF Features |
您所在的位置：网站首页 › createpicture › Busy Developers' Guide to HSSF and XSSF Features |
|
Busy Developers' Guide to HSSF and XSSF Features
Busy Developers' Guide to Features
Want to use HSSF and XSSF read and write spreadsheets in a hurry? This guide is for you. If you're after more in-depth coverage of the HSSF and XSSF user-APIs, please consult the HOWTO guide as it contains actual descriptions of how to use this stuff. Index of Features How to create a new workbook How to create a sheet How to create cells How to create date cells Working with different types of cells Iterate over rows and cells Getting the cell contents Text Extraction Files vs InputStreams Aligning cells Working with borders Fills and color Merging cells Working with fonts Custom colors Reading and writing Use newlines in cells. Create user defined data formats Fit Sheet to One Page Set print area for a sheet Set page numbers on the footer of a sheet Shift rows Set a sheet as selected Set the zoom magnification for a sheet Create split and freeze panes Repeating rows and columns Headers and Footers XSSF enhancement for Headers and Footers Drawing Shapes Styling Shapes Shapes and Graphics2d Outlining Images Named Ranges and Named Cells How to set cell comments How to adjust column width to fit the contents Hyperlinks Data Validations Embedded Objects Autofilters Conditional Formatting Hiding and Un-Hiding Rows Setting Cell Properties Drawing Borders Create a Pivot Table Cells with multiple styles Features New Workbook Workbook wb = new HSSFWorkbook(); ... try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } Workbook wb = new XSSFWorkbook(); ... try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) { wb.write(fileOut); } New Sheet Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); Sheet sheet2 = wb.createSheet("second sheet"); // Note that sheet name is Excel must not exceed 31 characters // and must not contain any of the any of the following characters: // 0x0000 // 0x0003 // colon (:) // backslash (\) // asterisk (*) // question mark (?) // forward slash (/) // opening square bracket ([) // closing square bracket (]) // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)} // for a safe way to create valid names, this utility replaces invalid characters with a space (' ') String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales " Sheet sheet3 = wb.createSheet(safeName); try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } Creating Cells Workbook wb = new HSSFWorkbook(); //Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow(0); // Create a cell and put a value in it. Cell cell = row.createCell(0); cell.setCellValue(1); // Or do it on one line. row.createCell(1).setCellValue(1.2); row.createCell(2).setCellValue( createHelper.createRichTextString("This is a string")); row.createCell(3).setCellValue(true); // Write the output to a file try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } Creating Date Cells Workbook wb = new HSSFWorkbook(); //Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow(0); // Create a cell and put a date value in it. The first cell is not styled // as a date. Cell cell = row.createCell(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. CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat( createHelper.createDataFormat().getFormat("m/d/yy h:mm")); cell = row.createCell(1); cell.setCellValue(new Date()); cell.setCellStyle(cellStyle); //you can also set date as java.util.Calendar cell = row.createCell(2); cell.setCellValue(Calendar.getInstance()); cell.setCellStyle(cellStyle); // Write the output to a file try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } Working with different types of cells Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); Row row = sheet.createRow(2); row.createCell(0).setCellValue(1.1); row.createCell(1).setCellValue(new Date()); row.createCell(2).setCellValue(Calendar.getInstance()); row.createCell(3).setCellValue("a string"); row.createCell(4).setCellValue(true); row.createCell(5).setCellType(CellType.ERROR); // Write the output to a file try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } Files vs InputStreamsWhen opening a workbook, either a .xls HSSFWorkbook, or a .xlsx XSSFWorkbook, the Workbook can be loaded from either a File or an InputStream. Using a File object allows for lower memory consumption, while an InputStream requires more memory as it has to buffer the whole file. If using WorkbookFactory, it's very easy to use one or the other: // Use a file Workbook wb = WorkbookFactory.create(new File("MyExcel.xls")); // Use an InputStream, needs more memory Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx"));If using HSSFWorkbook or XSSFWorkbook directly, you should generally go through POIFSFileSystem or OPCPackage, to have full control of the lifecycle (including closing the file when done): // HSSFWorkbook, File POIFSFileSystem fs = new POIFSFileSystem(new File("file.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true); .... fs.close(); // HSSFWorkbook, InputStream, needs more memory POIFSFileSystem fs = new POIFSFileSystem(myInputStream); HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true); // XSSFWorkbook, File OPCPackage pkg = OPCPackage.open(new File("file.xlsx")); XSSFWorkbook wb = new XSSFWorkbook(pkg); .... pkg.close(); // XSSFWorkbook, InputStream, needs more memory OPCPackage pkg = OPCPackage.open(myInputStream); XSSFWorkbook wb = new XSSFWorkbook(pkg); .... pkg.close(); Demonstrates various alignment options public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet(); Row row = sheet.createRow(2); row.setHeightInPoints(30); createCell(wb, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM); createCell(wb, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM); createCell(wb, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER); createCell(wb, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER); createCell(wb, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY); createCell(wb, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP); createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP); // Write the output to a file try (OutputStream fileOut = new FileOutputStream("xssf-align.xlsx")) { wb.write(fileOut); } wb.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 halign the horizontal alignment for the cell. * @param valign the vertical alignment for the cell. */ private static void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) { Cell cell = row.createCell(column); cell.setCellValue("Align It"); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(halign); cellStyle.setVerticalAlignment(valign); cell.setCellStyle(cellStyle); } Working with borders Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow(1); // Create a cell and put a value in it. Cell cell = row.createCell(1); cell.setCellValue(4); // Style the cell with borders all around. CellStyle style = wb.createCellStyle(); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(BorderStyle.THIN); style.setLeftBorderColor(IndexedColors.GREEN.getIndex()); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(IndexedColors.BLUE.getIndex()); style.setBorderTop(BorderStyle.MEDIUM_DASHED); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); cell.setCellStyle(style); // Write the output to a file try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close(); Iterate over rows and cellsSometimes, you'd like to just iterate over all the sheets in a workbook, all the rows in a sheet, or all the cells in a row. This is possible with a simple for loop. These iterators are available by calling workbook.sheetIterator(), sheet.rowIterator(), and row.cellIterator(), or implicitly using a for-each loop. Note that a rowIterator and cellIterator iterate over rows or cells that have been created, skipping empty rows and cells. for (Sheet sheet : wb ) { for (Row row : sheet) { for (Cell cell : row) { // Do something here } } } Iterate over cells, with control of missing / blank cellsIn some cases, when iterating, you need full control over how missing or blank rows and cells are treated, and you need to ensure you visit every cell and not just those defined in the file. (The CellIterator will only return the cells defined in the file, which is largely those with values or stylings, but it depends on Excel). In cases such as these, you should fetch the first and last column information for a row, then call getCell(int, MissingCellPolicy) to fetch the cell. Use a MissingCellPolicy to control how blank or null cells are handled. // Decide which rows to process int rowStart = Math.min(15, sheet.getFirstRowNum()); int rowEnd = Math.max(1400, sheet.getLastRowNum()); for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) { Row r = sheet.getRow(rowNum); if (r == null) { // This whole row is empty // Handle it as needed continue; } int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT); for (int cn = 0; cn < lastColumn; cn++) { Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL); if (c == null) { // The spreadsheet is empty in this cell } else { // Do something useful with the cell's contents } } } Getting the cell contentsTo get the contents of a cell, you first need to know what kind of cell it is (asking a string cell for its numeric contents will get you a NumberFormatException for example). So, you will want to switch on the cell's type, and then call the appropriate getter for that cell. In the code below, we loop over every cell in one sheet, print out the cell's reference (eg A3), and then the cell's contents. // import org.apache.poi.ss.usermodel.*; DataFormatter formatter = new DataFormatter(); Sheet sheet1 = wb.getSheetAt(0); for (Row row : sheet1) { for (Cell cell : row) { CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex()); System.out.print(cellRef.formatAsString()); System.out.print(" - "); // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc) String text = formatter.formatCellValue(cell); System.out.println(text); // Alternatively, get the value and format it yourself switch (cell.getCellType()) { case CellType.STRING: System.out.println(cell.getRichStringCellValue().getString()); break; case CellType.NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); } else { System.out.println(cell.getNumericCellValue()); } break; case CellType.BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case CellType.FORMULA: System.out.println(cell.getCellFormula()); break; case CellType.BLANK: System.out.println(); break; default: System.out.println(); } } } Text ExtractionFor most text extraction requirements, the standard ExcelExtractor class should provide all you need. try (InputStream inp = new FileInputStream("workbook.xls")) { HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp)); ExcelExtractor extractor = new ExcelExtractor(wb); extractor.setFormulasNotResults(true); extractor.setIncludeSheetNames(false); String text = extractor.getText(); wb.close(); }For very fancy text extraction, XLS to CSV etc, take a look at /poi-examples/src/main/java/org/apache/poi/examples/hssf/eventusermodel/XLS2CSVmra.java Fills and colors Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow(1); // Aqua background CellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); style.setFillPattern(FillPatternType.BIG_SPOTS); Cell cell = row.createCell(1); cell.setCellValue("X"); cell.setCellStyle(style); // Orange "foreground", foreground being the fill foreground not the font color. style = wb.createCellStyle(); style.setFillForegroundColor(IndexedColors.ORANGE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); cell = row.createCell(2); cell.setCellValue("X"); cell.setCellStyle(style); // Write the output to a file try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close(); Merging cells Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); Row row = sheet.createRow(1); Cell cell = row.createCell(1); cell.setCellValue("This is a test of merging"); sheet.addMergedRegion(new CellRangeAddress( 1, //first row (0-based) 1, //last row (0-based) 1, //first column (0-based) 2 //last column (0-based) )); // Write the output to a file try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close(); Working with fonts Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow(1); // Create a new font and alter it. Font 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. CellStyle style = wb.createCellStyle(); style.setFont(font); // Create a cell and put a value in it. Cell cell = row.createCell(1); cell.setCellValue("This is a test of fonts"); cell.setCellStyle(style); // Write the output to a file try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close();Note, the maximum number of unique fonts in a workbook is limited to 32767. You should re-use fonts in your applications instead of creating a font for each cell. Examples: Wrong: for (int i = 0; i < 10000; i++) { Row row = sheet.createRow(i); Cell cell = row.createCell(0); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); cell.setCellStyle(style); }Correct: CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for (int i = 0; i < 10000; i++) { Row row = sheet.createRow(i); Cell cell = row.createCell(0); cell.setCellStyle(style); } Custom colorsHSSF: HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(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(FillPatternType.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setColor(HSSFColor.RED.index); style.setFont(font); cell.setCellStyle(style); //save with the default palette try (OutputStream out = new FileOutputStream("default_palette.xls")) { wb.write(out); } //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 try (out = new FileOutputStream("modified_palette.xls")) { wb.write(out); }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), new DefaultIndexedColorMap())); style1.setFillPattern(FillPatternType.SOLID_FOREGROUND); Reading and Rewriting Workbooks try (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(CellType.STRING); cell.setCellValue("a test"); // Write the output to a file try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } } Using newlines in cells 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 accommodate two lines of text row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints())); //adjust column width to fit the content sheet.autoSizeColumn(2); try (OutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx")) { wb.write(fileOut); } wb.close(); Data Formats Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); CellStyle style; DataFormat format = wb.createDataFormat(); Row row; Cell cell; int rowNum = 0; int 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); try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close(); Fit Sheet to One Page 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. try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close(); Set Print Area 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 ); try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close(); Set Page Numbers on Footer Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); Footer footer = sheet.getFooter(); footer.setRight( "Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages() ); // Create various cells and rows for spreadsheet. try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close(); Using the Convenience FunctionsThe convenience functions provide utility features such as setting borders around merged regions and changing style attributes without explicitly creating new styles. Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook() Sheet sheet1 = wb.createSheet( "new sheet" ); // Create a merged region Row row = sheet1.createRow( 1 ); Row row2 = sheet1.createRow( 2 ); Cell cell = row.createCell( 1 ); cell.setCellValue( "This is a test of merging" ); CellRangeAddress region = CellRangeAddress.valueOf("B2:E5"); sheet1.addMergedRegion( region ); // Set the border and border colors. RegionUtil.setBorderBottom( BorderStyle.MEDIUM_DASHED, region, sheet1, wb ); RegionUtil.setBorderTop( BorderStyle.MEDIUM_DASHED, region, sheet1, wb ); RegionUtil.setBorderLeft( BorderStyle.MEDIUM_DASHED, region, sheet1, wb ); RegionUtil.setBorderRight( BorderStyle.MEDIUM_DASHED, region, sheet1, wb ); RegionUtil.setBottomBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb); RegionUtil.setTopBorderColor( IndexedColors.AQUA.getIndex(), region, sheet1, wb); RegionUtil.setLeftBorderColor( IndexedColors.AQUA.getIndex(), region, sheet1, wb); RegionUtil.setRightBorderColor( IndexedColors.AQUA.getIndex(), region, sheet1, wb); // Shows some usages of HSSFCellUtil CellStyle style = wb.createCellStyle(); style.setIndention((short)4); CellUtil.createCell(row, 8, "This is the value of the cell", style); Cell cell2 = CellUtil.createCell( row2, 8, "This is the value of the cell"); CellUtil.setAlignment(cell2, HorizontalAlignment.CENTER); // Write out the workbook try (OutputStream fileOut = new FileOutputStream( "workbook.xls" )) { wb.write( fileOut ); } wb.close(); Shift rows up or down on a sheet Workbook wb = new HSSFWorkbook(); Sheet 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); Set a sheet as selected Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("row sheet"); sheet.setSelected(true); Set the zoom magnificationThe zoom is expressed as a fraction. For example to express a zoom of 75% use 3 for the numerator and 4 for the denominator. Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); sheet1.setZoom(75); // 75 percent magnification Splits and freeze panesThere are two types of panes you can create; freeze panes and split panes. A freeze pane is split by columns and rows. You create a freeze pane using the following mechanism: sheet1.createFreezePane( 3, 2, 3, 2 ); The first two parameters are the columns and rows you wish to split by. The second two parameters indicate the cells that are visible in the bottom right quadrant. Split panes appear differently. The split area is divided into four separate work area's. The split occurs at the pixel level and the user is able to adjust the split by dragging it to a new position. Split panes are created with the following call: sheet2.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT ); The first parameter is the x position of the split. This is in 1/20th of a point. A point in this case seems to equate to a pixel. The second parameter is the y position of the split. Again in 1/20th of a point. The last parameter indicates which pane currently has the focus. This will be one of Sheet.PANE_LOWER_LEFT, PANE_LOWER_RIGHT, PANE_UPPER_RIGHT or PANE_UPPER_LEFT. Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); Sheet sheet2 = wb.createSheet("second sheet"); Sheet sheet3 = wb.createSheet("third sheet"); Sheet sheet4 = wb.createSheet("fourth sheet"); // Freeze just one row sheet1.createFreezePane( 0, 1, 0, 1 ); // Freeze just one column sheet2.createFreezePane( 1, 0, 1, 0 ); // Freeze the columns and rows (forget about scrolling position of the lower right quadrant). sheet3.createFreezePane( 2, 2 ); // Create a split with the lower left side being the active quadrant sheet4.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT ); try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } Repeating rows and columnsIt's possible to set up repeating rows and columns in your printouts by using the setRepeatingRows() and setRepeatingColumns() methods in the Sheet class. These methods expect a CellRangeAddress parameter which specifies the range for the rows or columns to repeat. For setRepeatingRows(), it should specify a range of rows to repeat, with the column part spanning all columns. For setRepeatingColumns(), it should specify a range of columns to repeat, with the row part spanning all rows. If the parameter is null, the repeating rows or columns will be removed. Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook(); Sheet sheet1 = wb.createSheet("Sheet1"); Sheet sheet2 = wb.createSheet("Sheet2"); // Set the rows to repeat from row 4 to 5 on the first sheet. sheet1.setRepeatingRows(CellRangeAddress.valueOf("4:5")); // Set the columns to repeat from column A to C on the second sheet sheet2.setRepeatingColumns(CellRangeAddress.valueOf("A:C")); try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } Headers and FootersExample is for headers but applies directly to footers. Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); Header 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"); try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } XSSF Enhancement for Headers and FootersExample is for headers but applies directly to footers. Note, the above example for basic headers and footers applies to XSSF Workbooks as well as HSSF Workbooks. The HSSFHeader stuff does not work for XSSF Workbooks. XSSF has the ability to handle First page headers and footers, as well as Even/Odd headers and footers. All Header/Footer Property flags can be handled in XSSF as well. The odd header and footer is the default header and footer. It is displayed on all pages that do not display either a first page header or an even page header. That is, if the Even header/footer does not exist, then the odd header/footer is displayed on even pages. If the first page header/footer does not exist, then the odd header/footer is displayed on the first page. If the even/odd property is not set, that is the same as the even header/footer not existing. If the first page property does not exist, that is the same as the first page header/footer not existing. Workbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet) wb.createSheet("new sheet"); // Create a first page header Header header = sheet.getFirstHeader(); header.setCenter("Center First Page Header"); header.setLeft("Left First Page Header"); header.setRight("Right First Page Header"); // Create an even page header Header header2 = sheet.getEvenHeader(); der2.setCenter("Center Even Page Header"); header2.setLeft("Left Even Page Header"); header2.setRight("Right Even Page Header"); // Create an odd page header Header header3 = sheet.getOddHeader(); der3.setCenter("Center Odd Page Header"); header3.setLeft("Left Odd Page Header"); header3.setRight("Right Odd Page Header"); // Set/Remove Header properties XSSFHeaderProperties prop = sheet.getHeaderFooterProperties(); prop.setAlignWithMargins(); prop.scaleWithDoc(); prop.removeDifferentFirstPage(); // This does not remove first page headers or footers prop.removeDifferentEvenOdd(); // This does not remove even headers or footers try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) { wb.write(fileOut); } Drawing ShapesPOI supports drawing shapes using the Microsoft Office drawing tools. Shapes on a sheet are organized in a hierarchy of groups and and shapes. The top-most shape is the patriarch. This is not visible on the sheet at all. To start drawing you need to call createPatriarch on the HSSFSheet class. This has the effect erasing any other shape information stored in that sheet. By default POI will leave shape records alone in the sheet unless you make a call to this method. To create a shape you have to go through the following steps: Create the patriarch. Create an anchor to position the shape on the sheet. Ask the patriarch to create the shape. Set the shape type (line, oval, rectangle etc...) Set any other style details concerning the shape. (eg: line thickness, etc...) HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); a = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 1, 0, (short) 1, 0 ); HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1); shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);Text boxes are created using a different call: HSSFTextbox textbox1 = patriarch.createTextbox( new HSSFClientAnchor(0,0,0,0,(short)1,1,(short)2,2)); textbox1.setString(new HSSFRichTextString("This is a test") );It's possible to use different fonts to style parts of the text in the textbox. Here's how: HSSFFont font = wb.createFont(); font.setItalic(true); font.setUnderline(HSSFFont.U_DOUBLE); HSSFRichTextString string = new HSSFRichTextString("Woo!!!"); string.applyFont(2,5,font); textbox.setString(string );Just as can be done manually using Excel, it is possible to group shapes together. This is done by calling createGroup() and then creating the shapes using those groups. It's also possible to create groups within groups. Warning Any group you create should contain at least two other shapes or subgroups.Here's how to create a shape group: // Create a shape group. HSSFShapeGroup group = patriarch.createGroup( new HSSFClientAnchor(0,0,900,200,(short)2,2,(short)2,2)); // Create a couple of lines in the group. HSSFSimpleShape shape1 = group.createShape(new HSSFChildAnchor(3,3,500,500)); shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); ( (HSSFChildAnchor) shape1.getAnchor() ).setAnchor(3,3,500,500); HSSFSimpleShape shape2 = group.createShape(new HSSFChildAnchor(1,200,400,600)); shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);If you're being observant you'll noticed that the shapes that are added to the group use a new type of anchor: the HSSFChildAnchor. What happens is that the created group has its own coordinate space for shapes that are placed into it. POI defaults this to (0,0,1023,255) but you are able to change it as desired. Here's how: myGroup.setCoordinates(10,10,20,20); // top-left, bottom-rightIf you create a group within a group it's also going to have its own coordinate space. Styling ShapesBy default shapes can look a little plain. It's possible to apply different styles to the shapes however. The sorts of things that can currently be done are: Change the fill color. Make a shape with no fill color. Change the thickness of the lines. Change the style of the lines. Eg: dashed, dotted. Change the line color.Here's an examples of how this is done: HSSFSimpleShape s = patriarch.createSimpleShape(a); s.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL); s.setLineStyleColor(10,10,10); s.setFillColor(90,10,200); s.setLineWidth(HSSFShape.LINEWIDTH_ONE_PT * 3); s.setLineStyle(HSSFShape.LINESTYLE_DOTSYS); Shapes and Graphics2dWhile the native POI shape drawing commands are the recommended way to draw shapes in a shape it's sometimes desirable to use a standard API for compatibility with external libraries. With this in mind we created some wrappers for Graphics and Graphics2d. Warning It's important to not however before continuing that Graphics2d is a poor match to the capabilities of the Microsoft Office drawing commands. The older Graphics class offers a closer match but is still a square peg in a round hole.All Graphics commands are issued into an HSSFShapeGroup. Here's how it's done: a = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 1, 0, (short) 1, 0 ); group = patriarch.createGroup( a ); group.setCoordinates( 0, 0, 80 * 4 , 12 * 23 ); float verticalPointsPerPixel = a.getAnchorHeightInPoints(sheet) / (float)Math.abs(group.getY2() - group.getY1()); g = new EscherGraphics( group, wb, Color.black, verticalPointsPerPixel ); g2d = new EscherGraphics2d( g ); drawChemicalStructure( g2d );The first thing we do is create the group and set its coordinates to match what we plan to draw. Next we calculate a reasonable fontSizeMultiplier then create the EscherGraphics object. Since what we really want is a Graphics2d object we create an EscherGraphics2d object and pass in the graphics object we created. Finally we call a routine that draws into the EscherGraphics2d object. The vertical points per pixel deserves some more explanation. One of the difficulties in converting Graphics calls into escher drawing calls is that Excel does not have the concept of absolute pixel positions. It measures its cell widths in 'characters' and the cell heights in points. Unfortunately it's not defined exactly what type of character it's measuring. Presumably this is due to the fact that the Excel will be using different fonts on different platforms or even within the same platform. Because of this constraint we've had to implement the concept of a verticalPointsPerPixel. This the amount the font should be scaled by when you issue commands such as drawString(). To calculate this value use the follow formula: multipler = groupHeightInPoints / heightOfGroupThe height of the group is calculated fairly simply by calculating the difference between the y coordinates of the bounding box of the shape. The height of the group can be calculated by using a convenience called HSSFClientAnchor.getAnchorHeightInPoints(). Many of the functions supported by the graphics classes are not complete. Here's some of the functions that are known to work. fillRect() fillOval() drawString() drawOval() drawLine() clearRect()Functions that are not supported will return and log a message using the POI logging infrastructure (disabled by default). OutliningOutlines are great for grouping sections of information together and can be added easily to columns and rows using the POI API. Here's how: Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); sheet1.groupRow( 5, 14 ); sheet1.groupRow( 7, 14 ); sheet1.groupRow( 16, 19 ); sheet1.groupColumn( 4, 7 ); sheet1.groupColumn( 9, 12 ); sheet1.groupColumn( 10, 11 ); try (OutputStream fileOut = new FileOutputStream(filename)) { wb.write(fileOut); }To collapse (or expand) an outline use the following calls: sheet1.setRowGroupCollapsed( 7, true ); sheet1.setColumnGroupCollapsed( 4, true );The row/column you choose should contain an already created group. It can be anywhere within the group. ImagesImages are part of the drawing support. To add an image just call createPicture() on the drawing patriarch. At the time of writing the following types are supported: PNG JPG DIBIt should be noted that any existing drawings may be erased once you add an image to a sheet. //create a new workbook Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); //add picture data to this workbook. InputStream is = new FileInputStream("image1.jpeg"); byte[] bytes = IOUtils.toByteArray(is); int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG); is.close(); CreationHelper helper = wb.getCreationHelper(); //create sheet Sheet sheet = wb.createSheet(); // Create the drawing patriarch. This is the top level container for all shapes. Drawing drawing = sheet.createDrawingPatriarch(); //add a picture shape ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner of the picture, //subsequent call of Picture#resize() will operate relative to it anchor.setCol1(3); anchor.setRow1(2); Picture pict = drawing.createPicture(anchor, pictureIdx); //auto-size picture relative to its top-left corner pict.resize(); //save workbook String file = "picture.xls"; if(wb instanceof XSSFWorkbook) file += "x"; try (OutputStream fileOut = new FileOutputStream(file)) { wb.write(fileOut); } Warning Picture.resize() works only for JPEG and PNG. Other formats are not yet supported.Reading images from a workbook: ist lst = workbook.getAllPictures(); or (Iterator it = lst.iterator(); it.hasNext(); ) { PictureData pict = (PictureData)it.next(); String ext = pict.suggestFileExtension(); byte[] data = pict.getData(); if (ext.equals("jpeg")){ try (OutputStream out = new FileOutputStream("pict.jpg")) { out.write(data); } } Named Ranges and Named CellsNamed Range is a way to refer to a group of cells by a name. Named Cell is a degenerate case of Named Range in that the 'group of cells' contains exactly one cell. You can create as well as refer to cells in a workbook by their named range. When working with Named Ranges, the classes org.apache.poi.ss.util.CellReference and org.apache.poi.ss.util.AreaReference are used. Note: Using relative values like 'A1:B1' can lead to unexpected moving of the cell that the name points to when working with the workbook in Microsoft Excel, usually using absolute references like '$A$1:$B$1' avoids this, see also this discussion. Creating Named Range / Named Cell // setup code String sname = "TestSheet", cname = "TestName", cvalue = "TestVal"; Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet(sname); sheet.createRow(0).createCell(0).setCellValue(cvalue); // 1. create named range for a single cell using areareference Name namedCell = wb.createName(); namedCell.setNameName(cname + "1"); String reference = sname+"!$A$1:$A$1"; // area reference namedCell.setRefersToFormula(reference); // 2. create named range for a single cell using cellreference Name namedCel2 = wb.createName(); namedCel2.setNameName(cname + "2"); reference = sname+"!$A$1"; // cell reference namedCel2.setRefersToFormula(reference); // 3. create named range for an area using AreaReference Name namedCel3 = wb.createName(); namedCel3.setNameName(cname + "3"); reference = sname+"!$A$1:$C$5"; // area reference namedCel3.setRefersToFormula(reference); // 4. create named formula Name namedCel4 = wb.createName(); namedCel4.setNameName("my_sum"); namedCel4.setRefersToFormula("SUM(" + sname + "!$I$2:$I$6)");Reading from Named Range / Named Cell // setup code String cname = "TestName"; Workbook wb = getMyWorkbook(); // retrieve workbook // retrieve the named range int namedCellIdx = wb.getNameIndex(cellName); Name aNamedCell = wb.getNameAt(namedCellIdx); // retrieve the cell at the named range and test its contents AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula()); CellReference[] crefs = aref.getAllReferencedCells(); for (int i=0; i |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |