List of usage examples for org.apache.poi.hssf.usermodel HSSFRow setHeight
@Override public void setHeight(short height)
From source file:org.sharegov.cirm.utils.ExcelExportUtil.java
License:Apache License
public void setHeaderColumnStyle(HSSFRow row, HSSFCell cell, HSSFCellStyle boldStyle, String columnValue, int columnNo) { setColumnWidth(columnValue, columnNo); // Calculate what the column width should be. // Increase if the current width is smaller than the calculated width. int width = columnValue.length() * 325; String[] splitHead = Pattern.compile(" ").split(columnValue); int wordCnt = splitHead.length; for (int q = 0; q < splitHead.length; q++) { if (splitHead[q].length() * 325 > width) width = splitHead[q].length() * 325; sheet.setColumnWidth(columnNo, width); }//from ww w. j a va 2 s . co m // Determine the height of the column head int height = wordCnt * 275; if (row.getHeight() < height) row.setHeight((short) height); // Set Cell to boldStyle cell.setCellStyle(boldStyle); }
From source file:paysheets.PaySheetFormatter.java
public static void addTitleRow(HSSFWorkbook workbook) { workbook.createSheet("Sheet 1"); // Each pay sheet only uses the first sheet HSSFSheet sheet = workbook.getSheetAt(0); setDefaultColumnWidth(sheet);/*from ww w . j ava2 s .c o m*/ HSSFRow row; HSSFCell cell; // Create a font and set its attributes Font font = workbook.createFont(); font.setFontHeightInPoints((short) 11); // Set the color to black (constant COLOR_NORMAL) font.setColor(Font.COLOR_NORMAL); font.setBold(true); // Create a cell style and set its properties CellStyle cs = workbook.createCellStyle(); // Set the data format to the built in text format cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); // Set the cell style to use the font created previously cs.setFont(font); // Create the first title row row = sheet.createRow(0); // Use the default row height (-1) is sheet default row.setHeight((short) -1); // Add the first title row's 6 cells for (int cellNum = 0; cellNum < 6; cellNum++) { cell = row.createCell(cellNum); cell.setCellStyle(cs); } // Populate first row's values cell = row.getCell(PaySheet.DATE_INDEX); cell.setCellValue("DATE"); cell = row.getCell(PaySheet.CUST_INDEX); cell.setCellValue("CUSTOMER"); cell = row.getCell(PaySheet.PAY_INDEX); cell.setCellValue("PAY"); cell = row.getCell(PaySheet.NONSERIAL_INDEX); cell.setCellValue("EQUIPMENT"); cell = row.getCell(PaySheet.SERIAL_INDEX); cell.setCellValue("SERIALIZED"); cell = row.getCell(PaySheet.SHS_INDEX); cell.setCellValue("SHS"); // Create second title row row = sheet.createRow(1); row.setHeight((short) -1); // Add the cells to the row for (int cellNum = 0; cellNum < 3; cellNum++) { cell = row.createCell(cellNum); cell.setCellStyle(cs); } // Populate the second title row's values cell = row.getCell(PaySheet.WO_INDEX); cell.setCellValue("WORK ORDER"); cell = row.getCell(PaySheet.TYPE_INDEX); cell.setCellValue("TYPE"); cell = row.getCell(PaySheet.LEP_INDEX); cell.setCellValue("LEP"); // Add thick border around title row addJobBorder(workbook, 0); }
From source file:poi.hssf.usermodel.examples.BigExample.java
License:Apache License
public static void main(String[] args) throws IOException { int rownum;/* ww w .j a va 2 s.c om*/ // 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. *//*w ww . ja v a 2 s. co 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();/*from w w w .jav a 2 s . c o m*/ HSSFRow r = null; 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); row.setHeight((short) 2800); row.createCell(1);//from w w w . j a v a 2 s. co m 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.HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells.//from w w w . java 2 s . co m */ 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(); }
From source file:ReadExcel.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 2s . co m */ 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.setBold(true); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBold(true); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(BorderStyle.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(BorderStyle.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); try { wb.write(out); } finally { out.close(); } wb.close(); }
From source file:ro.nextreports.engine.exporter.util.XlsUtil.java
License:Apache License
/** * Copy a row from a sheet to another sheet * //from w w w . j a v a 2 s. co m * * @param srcSheet the sheet to copy * @param destSheet the sheet to copy into * @param parentSheetRow the row inside destSheet where we start to copy * @param parentSheetColumn the column inside destSheet where we start to copy * @param srcRow the row to copy * @param destRow the row to create * @param styleMap style map * */ public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, int parentSheetRow, int parentSheetColumn, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { // manage a list of merged zone in order to not insert two times a // merged zone Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>(); destRow.setHeight(srcRow.getHeight()); // pour chaque row for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { HSSFCell oldCell = srcRow.getCell(j); // ancienne cell if (oldCell != null) { HSSFCell newCell = destRow.createCell(parentSheetColumn + j); copyCell(oldCell, newCell, styleMap); CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex()); if (mergedRegion != null) { CellRangeAddress newMergedRegion = new CellRangeAddress( parentSheetRow + mergedRegion.getFirstRow(), parentSheetRow + mergedRegion.getLastRow(), parentSheetColumn + mergedRegion.getFirstColumn(), parentSheetColumn + mergedRegion.getLastColumn()); CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion); if (isNewMergedRegion(wrapper, mergedRegions)) { mergedRegions.add(wrapper); destSheet.addMergedRegion(wrapper.range); } } } } }