List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setColumnWidth
@Override public void setColumnWidth(int columnIndex, int width)
The maximum column width for an individual cell is 255 characters.
From source file:paysheets.PaySheetFormatter.java
/** * Sets the default column width based on the constants defined for the * PaySheetFormatter class. Apache POI uses an odd system of 1/256th of a * character for the width.//w w w.jav a2 s . c o m * * @param sheet the HSSFSheet that the column width is being set for */ public static void setDefaultColumnWidth(HSSFSheet sheet) { sheet.setColumnWidth(PaySheet.DATE_INDEX, DATE_WIDTH); sheet.setColumnWidth(PaySheet.CUST_INDEX, CUSTOMER_WIDTH); sheet.setColumnWidth(PaySheet.PAY_INDEX, PAY_WIDTH); sheet.setColumnWidth(PaySheet.SERIAL_INDEX, SERIAL_WIDTH); sheet.setColumnWidth(PaySheet.NONSERIAL_INDEX, NONSERIAL_WIDTH); sheet.setColumnWidth(PaySheet.SHS_INDEX, SHS_WIDTH); }
From source file:poi.hssf.usermodel.examples.AddDimensionedImage.java
License:Apache License
/** * Determines whether the sheets columns should be re-sized to accomodate * the image, adjusts the columns width if necessary and creates then * returns a ClientAnchorDetail object that facilitates construction of * an HSSFClientAnchor that will fix the image on the sheet and establish * it's size./*from w w w .j ava2s. com*/ * * @param sheet A reference to the sheet that will 'contain' the image. * @param colNumber A primtive int that contains the index number of a * column on the sheet. * @param reqImageWidthMM A primtive double that contains the required * width of the image in millimetres * @param resizeBehaviour A primitve int whose value will indicate how the * width of the column should be adjusted if the * required width of the image is greater than the * width of the column. * @return An instance of the ClientAnchorDetail class that will contain * the index number of the column containing the cell whose top * left hand corner also defines the top left hand corner of the * image, the index number column containing the cell whose top * left hand corner also defines the bottom right hand corner of * the image and an inset that determines how far the right hand * edge of the image can protrude into the next column - expressed * as a specific number of co-ordinate positions. */ private ClientAnchorDetail fitImageToColumns(HSSFSheet sheet, int colNumber, double reqImageWidthMM, int resizeBehaviour) { double colWidthMM = 0.0D; double colCoordinatesPerMM = 0.0D; int pictureWidthCoordinates = 0; ClientAnchorDetail colClientAnchorDetail = null; // Get the colum's width in millimetres colWidthMM = ConvertImageUnits.widthUnits2Millimetres((short) sheet.getColumnWidth(colNumber)); // Check that the column's width will accomodate the image at the // required dimension. If the width of the column is LESS than the // required width of the image, decide how the application should // respond - resize the column or overlay the image across one or more // columns. if (colWidthMM < reqImageWidthMM) { // Should the column's width simply be expanded? if ((resizeBehaviour == AddDimensionedImage.EXPAND_COLUMN) || (resizeBehaviour == AddDimensionedImage.EXPAND_ROW_AND_COLUMN)) { // Set the width of the column by converting the required image // width from millimetres into Excel's column width units. sheet.setColumnWidth(colNumber, ConvertImageUnits.millimetres2WidthUnits(reqImageWidthMM)); // To make the image occupy the full width of the column, convert // the required width of the image into co-ordinates. This value // will become the inset for the ClientAnchorDetail class that // is then instantiated. colWidthMM = reqImageWidthMM; colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM; pictureWidthCoordinates = (int) (reqImageWidthMM * colCoordinatesPerMM); colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates); } // If the user has chosen to overlay both rows and columns or just // to expand ONLY the size of the rows, then calculate how to lay // the image out across one or more columns. else if ((resizeBehaviour == AddDimensionedImage.OVERLAY_ROW_AND_COLUMN) || (resizeBehaviour == AddDimensionedImage.EXPAND_ROW)) { colClientAnchorDetail = this.calculateColumnLocation(sheet, colNumber, reqImageWidthMM); } } // If the column is wider than the image. else { // Mow many co-ordinate positions are there per millimetre? colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM; // Given the width of the image, what should be it's co-ordinate? pictureWidthCoordinates = (int) (reqImageWidthMM * colCoordinatesPerMM); colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates); } return (colClientAnchorDetail); }
From source file:poi.hssf.usermodel.examples.BigExample.java
License:Apache License
public static void main(String[] args) throws IOException { int rownum;// w ww . j a v a2s . com // create a new file FileOutputStream out = new FileOutputStream("workbook.xls"); // create a new workbook HSSFWorkbook wb = new HSSFWorkbook(); // create a new sheet HSSFSheet s = wb.createSheet(); // declare a row object reference HSSFRow r = null; // declare a cell object reference HSSFCell c = null; // create 3 cell styles HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); // create 2 fonts objects HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); //set font 1 to 12 point type f.setFontHeightInPoints((short) 12); //make it red f.setColor(HSSFColor.RED.index); // make it bold //arial is the default font f.setBoldweight(f.BOLDWEIGHT_BOLD); //set font 2 to 10 point type f2.setFontHeightInPoints((short) 10); //make it the color at palette index 0xf (white) f2.setColor(HSSFColor.WHITE.index); //make it bold f2.setBoldweight(f2.BOLDWEIGHT_BOLD); //set cell stlye cs.setFont(f); //set the cell format see HSSFDataFromat for a full list cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); //set a thin border cs2.setBorderBottom(cs2.BORDER_THIN); //fill w fg fill color cs2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set foreground fill to red cs2.setFillForegroundColor(HSSFColor.RED.index); // set the font cs2.setFont(f2); // set the sheet name to HSSF Test wb.setSheetName(0, "HSSF Test"); // create a sheet with 300 rows (0-299) for (rownum = 0; rownum < 300; rownum++) { // create a row r = s.createRow(rownum); // on every other row if ((rownum % 2) == 0) { // make the row height bigger (in twips - 1/20 of a point) r.setHeight((short) 0x249); } //r.setRowNum(( short ) rownum); // create 50 cells (0-49) (the += 2 becomes apparent later for (int cellnum = 0; cellnum < 50; cellnum += 2) { // create a numeric cell c = r.createCell(cellnum); // do some goofy math to demonstrate decimals c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); // on every other row if ((rownum % 2) == 0) { // set this cell to the first cell style we defined c.setCellStyle(cs); } // create a string cell (see why += 2 in the c = r.createCell(cellnum + 1); // set the cell's string value to "TEST" c.setCellValue("TEST"); // make this column a bit wider s.setColumnWidth(cellnum + 1, (int) ((50 * 8) / ((double) 1 / 20))); // on every other row if ((rownum % 2) == 0) { // set this to the white on red cell style // we defined above c.setCellStyle(cs2); } } } //draw a thick black border on the row at the bottom using BLANKS // advance 2 rows rownum++; rownum++; r = s.createRow(rownum); // define the third style to be the default // except with a thick black border at the bottom cs3.setBorderBottom(cs3.BORDER_THICK); //create 50 cells for (int cellnum = 0; cellnum < 50; cellnum++) { //create a blank type cell (no value) c = r.createCell(cellnum); // set it to the thick black border style c.setCellStyle(cs3); } //end draw thick black border // demonstrate adding/naming and deleting a sheet // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); //end deleted sheet // write the workbook to the output stream // close our file (don't blow out our file handles wb.write(out); out.close(); }
From source file:poi.hssf.usermodel.examples.InCellLists.java
License:Apache License
/** * Call each of the list creation methods. * * @param outputFilename A String that encapsulates the name of and path to * the Excel spreadsheet file this code will create. *///from w w w . j a va 2 s .c o m public void demonstrateMethodCalls(String outputFilename) { HSSFWorkbook workbook = null; HSSFSheet sheet = null; HSSFRow row = null; HSSFCell cell = null; File outputFile = null; FileOutputStream fos = null; ArrayList<MultiLevelListItem> multiLevelListItems = null; ArrayList<String> listItems = null; String listItem = null; try { workbook = new HSSFWorkbook(); sheet = workbook.createSheet("In Cell Lists"); row = sheet.createRow(0); // Create a cell at A1 and insert a single, bulleted, item into // that cell. cell = row.createCell(0); this.bulletedItemInCell(workbook, "List Item", cell); // Create a cell at A2 and insert a plain list - that is one // whose items are neither bulleted or numbered - into that cell. row = sheet.createRow(1); cell = row.createCell(0); listItems = new ArrayList<String>(); listItems.add("List Item One."); listItems.add("List Item Two."); listItems.add("List Item Three."); listItems.add("List Item Four."); this.listInCell(workbook, listItems, cell); // The row height and cell width are set here to ensure that the // list may be seen. row.setHeight((short) 1100); sheet.setColumnWidth(0, 9500); // Create a cell at A3 and insert a numbered list into that cell. // Note that a couple of items have been added to the listItems // ArrayList row = sheet.createRow(2); cell = row.createCell(0); listItems.add("List Item Five."); listItems.add("List Item Six."); this.numberedListInCell(workbook, listItems, cell, 1, 2); row.setHeight((short) 1550); // Create a cell at A4 and insert a numbered list into that cell. // Note that a couple of items have been added to the listItems // ArrayList row = sheet.createRow(3); cell = row.createCell(0); listItems.add("List Item Seven."); listItems.add("List Item Eight."); listItems.add("List Item Nine."); listItems.add("List Item Ten."); this.bulletedListInCell(workbook, listItems, cell); row.setHeight((short) 2550); // Insert a plain, multi-level list into cell A5. Note that // the major difference here is that the list items are passed as // an ArrayList of MultiLevelListItems. Note that an ArrayList // of instances of an inner class was used here in preference to // a Hashtable or HashMap as the ArrayList will preserve the // ordering of the items added to it; the first item added will // be the first item recovered and the last item added, the last // item recovered. row = sheet.createRow(4); cell = row.createCell(0); multiLevelListItems = new ArrayList<MultiLevelListItem>(); listItems = new ArrayList<String>(); listItems.add("ML List Item One - Sub Item One."); listItems.add("ML List Item One - Sub Item Two."); listItems.add("ML List Item One - Sub Item Three."); listItems.add("ML List Item One - Sub Item Four."); multiLevelListItems.add(new MultiLevelListItem("List Item One.", listItems)); // Passing either null or an empty ArrayList will signal that // there are no lower level items associated with the top level // item multiLevelListItems.add(new MultiLevelListItem("List Item Two.", null)); multiLevelListItems.add(new MultiLevelListItem("List Item Three.", null)); listItems = new ArrayList<String>(); listItems.add("ML List Item Four - Sub Item One."); listItems.add("ML List Item Four - Sub Item Two."); listItems.add("ML List Item Four - Sub Item Three."); multiLevelListItems.add(new MultiLevelListItem("List Item Four.", listItems)); this.multiLevelListInCell(workbook, multiLevelListItems, cell); row.setHeight((short) 2800); // Insert a numbered multi-level list into cell A6. Note that the // same ArrayList as constructed for the above plain multi-level // list example will be re-used row = sheet.createRow(5); cell = row.createCell(0); this.multiLevelNumberedListInCell(workbook, multiLevelListItems, cell, 1, 1, 1, 2); row.setHeight((short) 2800); // Insert a numbered multi-level list into cell A7. Note that the // same ArrayList as constructed for the plain multi-level list // example will be re-used row = sheet.createRow(6); cell = row.createCell(0); this.multiLevelBulletedListInCell(workbook, multiLevelListItems, cell); row.setHeight((short) 2800); // Save the completed workbook outputFile = new File(outputFilename); fos = new FileOutputStream(outputFile); workbook.write(fos); } catch (FileNotFoundException fnfEx) { System.out.println("Caught a: " + fnfEx.getClass().getName()); System.out.println("Message: " + fnfEx.getMessage()); System.out.println("Stacktrace follows..........."); fnfEx.printStackTrace(System.out); } catch (IOException ioEx) { System.out.println("Caught a: " + ioEx.getClass().getName()); System.out.println("Message: " + ioEx.getMessage()); System.out.println("Stacktrace follows..........."); ioEx.printStackTrace(System.out); } finally { if (fos != null) { try { fos.close(); } catch (IOException ioEx) { } } } }
From source file:poi.hssf.usermodel.examples.NewLinesInCells.java
License:Apache License
public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null;//ww w. ja va 2 s . co m HSSFCell c = null; HSSFCellStyle cs = wb.createCellStyle(); HSSFFont f2 = wb.createFont(); cs = wb.createCellStyle(); cs.setFont(f2); // Word Wrap MUST be turned on cs.setWrapText(true); r = s.createRow(2); r.setHeight((short) 0x349); c = r.createCell(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(2, (int) ((50 * 8) / ((double) 1 / 20))); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); }
From source file:poi.hssf.usermodel.examples.OfficeDrawing.java
License:Apache License
private static void drawSheet1(HSSFSheet sheet1) { // Create a row and size one of the cells reasonably large. HSSFRow row = sheet1.createRow(2);/*www .j a v a 2 s . com*/ row.setHeight((short) 2800); row.createCell(1); sheet1.setColumnWidth(2, 9000); // Create the drawing patriarch. This is the top level container for // all shapes. HSSFPatriarch patriarch = sheet1.createDrawingPatriarch(); // Draw some lines and an oval. drawLinesToCenter(patriarch); drawManyLines(patriarch); drawOval(patriarch); drawPolygon(patriarch); // Draw a rectangle. HSSFSimpleShape rect = patriarch .createSimpleShape(new HSSFClientAnchor(100, 100, 900, 200, (short) 0, 0, (short) 0, 0)); rect.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE); }
From source file:poi.hssf.usermodel.examples.OfficeDrawing.java
License:Apache License
private static void drawSheet2(HSSFSheet sheet2) { // Create a row and size one of the cells reasonably large. HSSFRow row = sheet2.createRow(2);//from w w w . j a va 2 s . com row.createCell(1); row.setHeightInPoints(240); sheet2.setColumnWidth(2, 9000); // 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 = sheet2.createDrawingPatriarch(); // Draw a grid in one of the cells. drawGrid(patriarch); }
From source file:poi.hssf.usermodel.examples.OfficeDrawing.java
License:Apache License
private static void drawSheet3(HSSFSheet sheet3) { // Create a row and size one of the cells reasonably large HSSFRow row = sheet3.createRow(2);/*from ww w. j av a 2 s . com*/ row.setHeightInPoints(140); row.createCell(1); sheet3.setColumnWidth(2, 9000); // 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 = sheet3.createDrawingPatriarch(); // 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); }
From source file:poi.hssf.usermodel.examples.OfficeDrawingWithGraphics.java
License:Apache License
public static void main(String[] args) throws IOException { // Create a workbook with one sheet and size the first three somewhat // larger so we can fit the chemical structure diagram in. HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("my drawing"); sheet.setColumnWidth(1, 256 * 27); HSSFRow row1 = sheet.createRow(0);/*from www. ja v a 2 s . c o m*/ row1.setHeightInPoints(10 * 15); HSSFRow row2 = sheet.createRow(1); row2.setHeightInPoints(5 * 15); HSSFRow row3 = sheet.createRow(2); row3.setHeightInPoints(10 * 15); // Add some cells so we can test that the anchoring works when we // sort them. row1.createCell(0).setCellValue("C"); row2.createCell(0).setCellValue("A"); row3.createCell(0).setCellValue("B"); // Create the top level drawing patriarch. HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFClientAnchor a; HSSFShapeGroup group; EscherGraphics g; EscherGraphics2d g2d; // Anchor entirely within one cell. a = new HSSFClientAnchor(0, 0, 1023, 255, (short) 1, 0, (short) 1, 0); group = patriarch.createGroup(a); group.setCoordinates(0, 0, 320, 276); float verticalPointsPerPixel = a.getAnchorHeightInPoints(sheet) / Math.abs(group.getY2() - group.getY1()); g = new EscherGraphics(group, wb, Color.black, verticalPointsPerPixel); g2d = new EscherGraphics2d(g); drawStar(g2d); a = new HSSFClientAnchor(0, 0, 1023, 255, (short) 1, 1, (short) 1, 1); group = patriarch.createGroup(a); group.setCoordinates(0, 0, 640, 276); verticalPointsPerPixel = a.getAnchorHeightInPoints(sheet) / Math.abs(group.getY2() - group.getY1()); // verticalPixelsPerPoint = (float)Math.abs(group.getY2() - group.getY1()) / a.getAnchorHeightInPoints(sheet); g = new EscherGraphics(group, wb, Color.black, verticalPointsPerPixel); g2d = new EscherGraphics2d(g); drawStar(g2d); FileOutputStream out = new FileOutputStream("workbook.xls"); wb.write(out); out.close(); }
From source file:poi.HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells./*from w w w . j a v a 2 s. com*/ */ private static void testCreateSampleSheet(String outputFilename) throws IOException { int rownum; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet FileOutputStream out = new FileOutputStream(outputFilename); wb.write(out); out.close(); // wb.close(); }