- 浏览: 143942 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
gadmyth:
beta reduction也介绍错了(λx . e)f →β ...
Lamda演算简介 -
gadmyth:
左结合法则是错的,因为Application binds mo ...
Lamda演算简介 -
hongmeikaile:
...
Struts2与ajax的组合 -
aguai0:
非常详细,学习了
prototype-1.3.1.js 开发笔记 -
左看右看:
...
DAO编程模式(转)
- 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
- 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
- Drawing Shapes
- Styling Shapes
- Shapes and Graphics2d
- Outlining
- Images
- Named Ranges and Named Cells
- How to set cell comments
Features
New Workbook
HSSFWorkbook wb = new HSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
New 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();
Creating Cells
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();
Creating Date Cells
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();
Working with different types of cells
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();
Demonstrates various alignment options
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); }
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();
Fills and colors
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();
Merging cells
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();
Working with fonts
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();
Custom colors
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();
Reading and Rewriting Workbooks
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();
Using newlines in cells
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();
Data Formats
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();
Fit Sheet to One Page
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();
Set Print Area
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();
Set Page Numbers on Footer
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();
Using the Convenience Functions
The convenience functions live in contrib and provide utility features such as setting borders around merged regions and changing style attributes without explicitly creating new styles.
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();
Shift rows up or down on a sheet
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();
Set a sheet as selected
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();
Set the zoom magnification
The zoom is expressed as a fraction. For example to express a zoom of 75% use 3 for the numerator and 4 for the denominator.
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();
Splits and freeze panes
There 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 pains 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, HSSFSheet.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 HSSFSheet.PANE_LOWER_LEFT, PANE_LOWER_RIGHT, PANE_UPPER_RIGHT or PANE_UPPER_LEFT.
HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("new sheet"); HSSFSheet sheet2 = wb.createSheet("second sheet"); HSSFSheet sheet3 = wb.createSheet("third sheet"); HSSFSheet 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, HSSFSheet.PANE_LOWER_LEFT ); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
Repeating rows and columns
It's possible to set up repeating rows and columns in your printouts by using the setRepeatingRowsAndColumns() function in the HSSFWorkbook class.
This function Contains 5 parameters. The first parameter is the index to the sheet (0 = first sheet). The second and third parameters specify the range for the columns to repreat. To stop the columns from repeating pass in -1 as the start and end column. The fourth and fifth parameters specify the range for the rows to repeat. To stop the columns from repeating pass in -1 as the start and end rows.
HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("new sheet"); HSSFSheet sheet2 = wb.createSheet("second sheet"); // Set the columns to repeat from column 0 to 2 on the first sheet wb.setRepeatingRowsAndColumns(0,0,2,-1,-1); // Set the the repeating rows and columns on the second sheet. wb.setRepeatingRowsAndColumns(1,4,5,1,2); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
Headers and Footers
Example is for headers but applies directly to footers.
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();
Drawing Shapes
POI supports drawing shapes using the Microsoft Office drawing tools. Shapes on a sheet are organized in a hiearchy of groups and and shapes. The top-most shape is the patriarch. This is not visisble 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 converning 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.
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((short)3,3,500,500); HSSFSimpleShape shape2 = group.createShape(new HSSFChildAnchor((short)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 it's 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-right
If you create a group within a group it's also going to have it's own coordinate space.
Styling Shapes
By 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 Graphics2d
While 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.
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 it's coordinates to match what we plan to draw. Next we calculate a reasonable fontSizeMultipler 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 it's 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 / heightOfGroup
The 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).
Outlining
Outlines are great for grouping sections of information together and can be added easily to columns and rows using the POI API. Here's how:
HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("new sheet"); sheet1.groupRow( 5, 14 ); sheet1.groupRow( 7, 14 ); sheet1.groupRow( 16, 19 ); sheet1.groupColumn( (short)4, (short)7 ); sheet1.groupColumn( (short)9, (short)12 ); sheet1.groupColumn( (short)10, (short)11 ); FileOutputStream fileOut = new FileOutputStream(filename); wb.write(fileOut); fileOut.close();
To collapse (or expand) an outline use the following calls:
sheet1.setRowGroupCollapsed( 7, true ); sheet1.setColumnGroupCollapsed( (short)4, true );
The row/column you choose should contain an already created group. It can be anywhere within the group.
Images
Images 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
- DIB
It is not currently possible to read existing images and it should be noted that any existing drawings may be erased once you add a image to a sheet.
// Create the drawing patriarch. This is the top level container for // all shapes. This will clear out any existing shapes for that sheet. HSSFPatriarch patriarch = sheet5.createDrawingPatriarch(); HSSFClientAnchor anchor; anchor = new HSSFClientAnchor(0,0,0,255,(short)2,2,(short)4,7); anchor.setAnchorType( 2 ); patriarch.createPicture(anchor, loadPicture( "src/resources/logos/logoKarmokar4.png", wb ));
Named Ranges and Named Cells
Named 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.hssf.util.CellReference and & org.apache.poi.hssf.util.AreaReference are used.
Creating Named Range / Named Cell
// setup code String sname = "TestSheet", cname = "TestName", cvalue = "TestVal"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(sname); sheet.createRow(0).createCell((short) 0).setCellValue(cvalue); // 1. create named range for a single cell using areareference HSSFName namedCell = wb.createName(); namedCell.setNameName(cname); String reference = sname+"!A1:A1"; // area reference namedCell.setReference(reference); // 2. create named range for a single cell using cellreference HSSFName namedCell = wb.createName(); namedCell.setNameName(cname); String reference = sname+"!A1"; // cell reference namedCell.setReference(reference); // 3. create named range for an area using AreaReference HSSFName namedCell = wb.createName(); namedCell.setNameName(cname); String reference = sname+"!A1:C5"; // area reference namedCell.setReference(reference);
Reading from Named Range / Named Cell
// setup code String cname = "TestName"; HSSFWorkbook wb = getMyWorkbook(); // retrieve workbook // retrieve the named range int namedCellIdx = wb.getNameIndex(cellName); HSSFName aNamedCell = wb.getNameAt(namedCellIdx); // retrieve the cell at the named range and test its contents AreaReference aref = new AreaReference(aNamedCell.getReference()); CellReference[] crefs = aref.getCells(); for (int i=0; i<crefs.length; i++) { HSSFSheet s = wb.getSheet(crefs[i].getSheetName()); HSSFRow r = sheet.getRow(crefs[i].getRow()); HSSFCell c = r.getCell(crefs[i].getCol()); // extract the cell contents based on cell type etc. }
Cell Comments
In Excel a comment is a kind of a text shape, so inserting a comment is very similar to placing a text box in a worksheet:
HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Cell comments in POI HSSF"); // Create the drawing patriarch. This is the top level container for all shapes including cell comments. HSSFPatriarch patr = sheet.createDrawingPatriarch(); //create a cell in row 3 HSSFCell cell1 = sheet.createRow(3).createCell((short)1); cell1.setCellValue(new HSSFRichTextString("Hello, World")); //anchor defines size and position of the comment in worksheet HSSFComment comment1 = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short)4, 2, (short) 6, 5)); // set text in the comment comment1.setString(new HSSFRichTextString("We can set comments in POI")); //set comment author. //you can see it in the status bar when moving mouse over the commented cell comment1.setAuthor("Apache Software Foundation"); // The first way to assign comment to a cell is via HSSFCell.setCellComment method cell1.setCellComment(comment1); //create another cell in row 6 HSSFCell cell2 = sheet.createRow(6).createCell((short)1); cell2.setCellValue(36.6); HSSFComment comment2 = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short)4, 8, (short) 6, 11)); //modify background color of the comment comment2.setFillColor(204, 236, 255); HSSFRichTextString string = new HSSFRichTextString("Normal body temperature"); //apply custom font to the text in the comment HSSFFont font = wb.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short)10); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); string.applyFont(font); comment2.setString(string); //by default comments are hidden. This one is always visible. comment2.setVisible(true); comment2.setAuthor("Bill Gates"); /** * The second way to assign comment to a cell is to implicitly specify its row and column. * Note, it is possible to set row and column of a non-existing cell. * It works, the commnet is visible. */ comment2.setRow(6); comment2.setColumn((short)1); FileOutputStream out = new FileOutputStream("poi_comment.xls"); wb.write(out); out.close();
Reading cell comments
HSSFCell cell = sheet.get(3).getColumn((short)1); HSSFComment comment = cell.getCellComment(); if (comment != null) { HSSFRichTextString str = comment.getString(); String author = comment.getAuthor(); }
发表评论
-
始终会用上的Common BeanUtils
2007-12-12 09:27 1170Beanutils用了魔术般的反射技术,实现了很多夸张有用的功 ... -
代理模式之理解
2007-10-17 17:22 2029代理模式解决不同请求和相应的目标对象的中介作用,实现面向接口编 ... -
DAO编程模式(转)
2007-10-12 14:55 1392J2EE开发人员使用数据访 ... -
Learn techniques for building better DAOs
2007-10-12 13:57 1418Software Engineer 7 October 200 ... -
JAVA设计模式之事务处理
2007-10-12 11:08 1155事务处理是企业应用需要解决的最主要的问题之一。J2EE通过JT ... -
JAVA字符集
2007-07-03 16:34 9621. 概述 本文主要包括 ... -
多级反向代理[Squid]下获取客户端真实IP地址
2007-06-18 17:05 5730多级反向代理[Squid]下获取客户端真实IP地址 在很多应 ... -
汉字按拼音排序
2007-04-05 16:11 21231。将要排序字符串设置成GBK编码 2。自定义一个类,实现Co ... -
从黑箱到企业,第 1 部分: 管理,JMX 1.1 样式
2007-01-29 10:02 1440本文是关于 Java 管理扩展(Java Management ...
相关推荐
《UAP标准导入导出功能详解》 在IT领域,数据的导入与导出是日常工作中不可或缺的一部分。尤其是在企业级应用系统中,如NC65这样的企业管理软件,UAP(通用应用平台)提供了标准化的导入导出功能,使得数据管理更为...
在IT行业中,数据导入导出是一项基础且重要的任务,特别是在Java开发中。本文将深入探讨如何使用Java进行数据的导入和导出,包括Excel、XML和TXT等常见格式的处理。 首先,我们要理解数据导入导出的核心目的:将...
### 金蝶BOS开发之导入导出 #### 一、引言 金蝶BOS平台作为一款功能强大的企业级应用开发平台,为企业提供了灵活高效的应用定制与开发能力。其中,数据导入导出功能是企业在日常运营过程中经常需要用到的一个重要...
在日常运维和开发过程中,数据的导入导出是非常常见的任务,这包括数据迁移、备份、恢复等场景。本工具是一款基于Java编写的桌面应用程序,专为Oracle数据库的导入导出提供便利。 1. **Oracle数据库导入导出的重要...
T5导入导出工具是一款好用的数据导入导出工具。这款工具适用于T5无线盘点机,可以帮助用户实现导入导出商品信息配置、导出盘点配置、导出数据库盘点配置等功能。还有详细的使用说明,感兴趣的朋友千万不要错过了。 ...
Oracle 19c 备份恢复-导入导出 Oracle 19c 备份恢复-导入导出是指使用 Oracle 数据库提供的工具 exp 和 imp 实现数据库的备份恢复和数据的导入导出。本文将详细介绍 Oracle 19c 备份恢复-导入导出的基本命令、工作...
在ASP中处理Excel数据导入导出是一项常见的需求,特别是在数据处理和报表生成的场景下。这个"asp将EXCEL导入导出数据库原程序"提供了一个解决方案,能够方便地将Excel中的数据存入MSSQL数据库,同时也能将数据库中的...
Allegro 软件中的光绘层的导入导出设置 Allegro 软件中的光绘层的导入导出设置是 CAD 设计中非常重要的一个方面。由于每个人的设计习惯不同,因此在设计中查看的光绘层也不同。为了解决这个问题,我们可以使用 ...
本示例将详细介绍如何利用易语言进行EXCEL快速导入导出的操作。 首先,我们需要了解易语言中与Excel相关的基础组件和函数。易语言提供了一个名为“通用数据访问组件”的模块,其中包含了读取和写入Excel文件所需的...
本文将详细讲解如何利用“组策略导入导出工具”进行有效管理。 首先,组策略对象(Group Policy Object, GPO)是组策略的核心,它包含了针对AD环境中用户和计算机的一系列设置。GPO可以应用到域、站点或组织单位...
"组态王变量导入导出补丁"是一个针对组态王7.5SP2版本的修复工具,专门解决在创建并导出结构变量后可能导致软件崩溃的问题。在深入讲解这个问题和补丁之前,我们先来了解一下组态王及其相关概念。 组态王是一款广泛...
在Spring Boot中实现Excel的导入导出可以通过POI库来实现,POI是一个开源的Java库,用于处理Microsoft Office格式文件,包括Excel。一个简单的示例,演示如何在Spring Boot项目中使用POI来实现Excel的导入导出功能。...
Redis 数据导入导出和数据迁移是 Redis 管理中的重要环节,对于数据备份、故障恢复、集群扩展等场景有着至关重要的作用。本文将详细解析 Redis 数据导入导出的4种方法。 首先,我们来看第一种方法:使用 AOF...
首先,我们要理解易语言中的“EXCEL数据导入导出”涉及的主要知识点。易语言提供了与Microsoft Excel交互的API接口或库,如COM组件,允许程序员通过调用Excel对象模型的方法来操作Excel文件。在易语言中,我们可以...
以上就是QT高效导入导出Excel并使用tableWidget显示的基本流程和优化策略。实际开发时,根据项目需求和资源限制,可能还需要进行额外的错误处理和功能完善。这个程序虽然不完善,但提供了清晰的开发思路,有助于快速...
PLSQL Developer是一款强大的Oracle数据库开发工具,它提供了方便的数据导入导出功能,使得数据库对象和数据的迁移变得简单。在本文中,我们将详细介绍如何在PLSQL Developer中进行数据的导入导出。 首先,我们来看...
SQL Server 数据库导入导出的方法 SQL Server 数据库导入导出是数据库管理员和开发者常用的操作,目的是将数据库备份到本地或网络存储设备中,以便在需要时恢复数据库或将数据库迁移到其他服务器上。下面将详细介绍...
万能的Excel导入导出工具. 支持从List中导出. 支持从List中导入导出. 支持从List里面还有List<POJO>>中导入导出. 支持导出类似课程表结构类型纵表. 支持国际化. 支持数据字典. 支持单元格中下拉框数据校验. 支持自动...
在IT行业中,Excel导入导出性能优化是一个常见且重要的议题,尤其对于处理大量数据的应用程序。Excel文件格式广泛用于数据存储和分析,但其导入和导出操作可能会对系统性能产生显著影响,尤其是在高并发环境下。以下...