List of usage examples for org.apache.poi.ss.usermodel Row setHeight
void setHeight(short height);
From source file:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java
License:Open Source License
/** * Adds in a Row to the given Sheet//from w ww . ja v a 2s. co m */ public Row addRow(Workbook wb, SheetToAdd sheetToAdd, RowToAdd rowToAdd, int rowIndex, ReportData reportData, ReportDesign design, Map<String, String> repeatSections) { // Create a new row and copy over style attributes from the row to add Row newRow = sheetToAdd.getSheet().createRow(rowIndex); Row rowToClone = rowToAdd.getRowToClone(); try { CellStyle rowStyle = rowToClone.getRowStyle(); if (rowStyle != null) { newRow.setRowStyle(rowStyle); } } catch (Exception e) { // No idea why this is necessary, but this has thrown IndexOutOfBounds errors getting the rowStyle. Mysteries of POI } newRow.setHeight(rowToClone.getHeight()); // Iterate across all of the cells in the row, and configure all those that need to be added/cloned List<CellToAdd> cellsToAdd = new ArrayList<CellToAdd>(); int totalCells = rowToClone.getPhysicalNumberOfCells(); int cellsFound = 0; for (int cellNum = 0; cellsFound < totalCells; cellNum++) { Cell currentCell = rowToClone.getCell(cellNum); log.debug("Handling cell: " + currentCell); if (currentCell != null) { cellsFound++; } // If we find that the cell that we are on is a repeating cell, then add the appropriate number of cells to clone String repeatingColumnProperty = getRepeatingColumnProperty(sheetToAdd.getOriginalSheetNum(), cellNum, repeatSections); if (repeatingColumnProperty != null) { String[] dataSetSpanSplit = repeatingColumnProperty.split(","); String dataSetName = dataSetSpanSplit[0]; DataSet dataSet = getDataSet(reportData, dataSetName, rowToAdd.getReplacementData()); int numCellsToRepeat = 1; if (dataSetSpanSplit.length == 2) { numCellsToRepeat = Integer.parseInt(dataSetSpanSplit[1]); } log.debug("Repeating this cell with dataset: " + dataSet + " and repeat of " + numCellsToRepeat); int repeatNum = 0; for (DataSetRow dataSetRow : dataSet) { repeatNum++; for (int i = 0; i < numCellsToRepeat; i++) { Cell cell = (i == 0 ? currentCell : rowToClone.getCell(cellNum + i)); if (repeatNum == 1 && cell != null && cell != currentCell) { cellsFound++; } Map<String, Object> newReplacements = getReplacementData(rowToAdd.getReplacementData(), reportData, design, dataSetName, dataSetRow, repeatNum); cellsToAdd.add(new CellToAdd(cell, newReplacements)); log.debug("Adding " + cell + " with dataSetRow: " + dataSetRow); } } cellNum += numCellsToRepeat; } else { cellsToAdd.add(new CellToAdd(currentCell, rowToAdd.getReplacementData())); log.debug("Adding " + currentCell); } } // Now, go through all of the collected cells, and add them back in String prefix = getExpressionPrefix(design); String suffix = getExpressionSuffix(design); List<CellRangeAddress> newMergedRegions = new ArrayList<CellRangeAddress>(); for (int i = 0; i < cellsToAdd.size(); i++) { CellToAdd cellToAdd = cellsToAdd.get(i); Cell newCell = newRow.createCell(i); Cell cellToClone = cellToAdd.getCellToClone(); if (cellToClone != null) { Object contents = ExcelUtil.getCellContents(cellToClone); newCell.setCellStyle(cellToClone.getCellStyle()); int numFormattings = sheetToAdd.getSheet().getSheetConditionalFormatting() .getNumConditionalFormattings(); for (int n = 0; n < numFormattings; n++) { ConditionalFormatting f = sheetToAdd.getSheet().getSheetConditionalFormatting() .getConditionalFormattingAt(n); for (CellRangeAddress add : f.getFormattingRanges()) { if (add.getFirstRow() == rowToAdd.getRowToClone().getRowNum() && add.getLastRow() == rowToClone.getRowNum()) { if (add.getFirstColumn() == cellToClone.getColumnIndex() && add.getLastColumn() == cellToClone.getColumnIndex()) { ConditionalFormattingRule[] rules = new ConditionalFormattingRule[f .getNumberOfRules()]; for (int j = 0; j < f.getNumberOfRules(); j++) { rules[j] = f.getRule(j); } CellRangeAddress[] cellRange = new CellRangeAddress[1]; cellRange[0] = new CellRangeAddress(rowIndex, rowIndex, i, i); sheetToAdd.getSheet().getSheetConditionalFormatting() .addConditionalFormatting(cellRange, rules); } } } } int numMergedRegions = sheetToAdd.getSheet().getNumMergedRegions(); for (int n = 0; n < numMergedRegions; n++) { CellRangeAddress add = sheetToAdd.getSheet().getMergedRegion(n); int rowNum = rowToClone.getRowNum(); if (add.getFirstRow() == rowNum && add.getLastRow() == rowNum) { if (add.getFirstColumn() == cellToClone.getColumnIndex()) { newMergedRegions .add(new CellRangeAddress(rowNum, rowNum, i, i + add.getNumberOfCells() - 1)); } } } if (ObjectUtil.notNull(contents)) { if (contents instanceof String) { contents = EvaluationUtil.evaluateExpression(contents.toString(), cellToAdd.getReplacementData(), prefix, suffix); } ExcelUtil.setCellContents(newCell, contents); } ExcelUtil.copyFormula(cellToClone, newCell); } } for (CellRangeAddress mergedRegion : newMergedRegions) { sheetToAdd.getSheet().addMergedRegion(mergedRegion); } return newRow; }
From source file:org.phenotips.export.internal.SpreadsheetExporter.java
License:Open Source License
protected void write(DataSection section, Sheet sheet) { DataCell[][] cells = section.getMatrix(); Styler styler = new Styler(); Row row; for (Integer y = 0; y <= section.getMaxY(); y++) { row = sheet.createRow(y);/*from w w w . j av a 2 s. c o m*/ Integer maxLines = 0; for (Integer x = 0; x <= section.getMaxX(); x++) { DataCell dataCell = cells[x][y]; if (dataCell == null) { continue; } Cell cell = row.createCell(x); cell.setCellValue(dataCell.getValue()); styler.style(dataCell, cell, this.wBook); if (dataCell.getNumberOfLines() != null) { maxLines = maxLines < dataCell.getNumberOfLines() ? dataCell.getNumberOfLines() : maxLines; } } if (maxLines > 1) { Integer height = maxLines * 400; row.setHeight(height.shortValue()); } } for (int col = 0; section.getMaxX() >= col; col++) { sheet.autoSizeColumn(col); if (sheet.getColumnWidth(col) > (DataToCellConverter.charactersPerLine * 210)) { sheet.setColumnWidth(col, DataToCellConverter.charactersPerLine * 210); } } /** Merging has to be done after autosizing because otherwise autosizing breaks */ for (Integer y = 0; y <= section.getMaxY(); y++) { for (Integer x = 0; x <= section.getMaxX(); x++) { DataCell dataCell = cells[x][y]; if (dataCell != null && dataCell.getMergeX() != null) { sheet.addMergedRegion(new CellRangeAddress(y, y, x, x + dataCell.getMergeX())); } /* * No longer will be merging cells on the Y axis, but keep this code for future reference. if * (dataCell.getYBoundry() != null) { sheet.addMergedRegion(new CellRangeAddress(dataCell.y, * dataCell.getYBoundry(), dataCell.x, dataCell.x)); } */ } } }
From source file:org.riflemansd.businessprofit.excel.ExcelExampleFont.java
License:Open Source License
public static void main(String[] args) { // create a new file FileOutputStream out = null;//from w w w. j av a 2 s . c om try { out = new FileOutputStream("workbook.xls"); } catch (FileNotFoundException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } // create a new workbook Workbook wb = new HSSFWorkbook(); // create a new sheet Sheet s = wb.createSheet(); // declare a row object reference Row r = null; // declare a cell object reference Cell c = null; // create 3 cell styles CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); CellStyle cs3 = wb.createCellStyle(); DataFormat df = wb.createDataFormat(); // create 2 fonts objects Font f = wb.createFont(); Font f2 = wb.createFont(); //set font 1 to 12 point type f.setFontHeightInPoints((short) 12); //make it blue f.setColor((short) 0xc); // make it bold //arial is the default font f.setBoldweight(Font.BOLDWEIGHT_BOLD); //set font 2 to 10 point type f2.setFontHeightInPoints((short) 10); //make it red f2.setColor((short) Font.COLOR_RED); //make it bold f2.setBoldweight(Font.BOLDWEIGHT_BOLD); f2.setStrikeout(true); //set cell stlye cs.setFont(f); //set the cell format cs.setDataFormat(df.getFormat("#,##0.0")); //set a thin border cs2.setBorderBottom(cs2.BORDER_THIN); //fill w fg fill color cs2.setFillPattern((short) CellStyle.SOLID_FOREGROUND); //set the cell format to text see DataFormat for a full list cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); // set the font cs2.setFont(f2); // set the sheet name in Unicode wb.setSheetName(0, "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F " + "\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430"); // in case of plain ascii // wb.setSheetName(0, "HSSF Test"); // create a sheet with 30 rows (0-29) int rownum; for (rownum = (short) 0; rownum < 30; 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 10 cells (0-9) (the += 2 becomes apparent later for (short cellnum = (short) 0; cellnum < 10; 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))); String cellValue; // create a string cell (see why += 2 in the c = r.createCell((short) (cellnum + 1)); // on every other row if ((rownum % 2) == 0) { // set this cell to the first cell style we defined c.setCellStyle(cs); // set the cell's string value to "Test" c.setCellValue("Test"); } else { c.setCellStyle(cs2); // set the cell's string value to "\u0422\u0435\u0441\u0442" c.setCellValue("\u0422\u0435\u0441\u0442"); } // make this column a bit wider s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20))); } } //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 (short cellnum = (short) 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 try { // write the workbook to the output stream // close our file (don't blow out our file handles wb.write(out); } catch (IOException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } try { out.close(); } catch (IOException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:org.seasar.fisshplate.core.element.HorizontalIteratorBlock.java
License:Apache License
private void mergeRow(FPContext context, Row row, int maxCellNum) throws FPMergeException { org.apache.poi.ss.usermodel.Row outRow = context.getCurrentRow(); outRow.setHeight(row.getRowHeight()); Map<String, Object> data = context.getData(); data.put(FPConsts.ROW_NUMBER_NAME, Integer.valueOf(context.getCurrentRowNum() + 1)); int maxCellIndex = startCellIndex + maxCellNum - 1; for (int i = 0; i < row.getCellElementList().size(); i++) { if (i < startCellIndex) { continue; } else if (i > maxCellIndex) { break; }//from w ww. j a v a 2 s . c om adjustColumnWidth(context, (short) i); TemplateElement elem = (TemplateElement) row.getCellElementList().get(i); elem.merge(context); } context.nextRow(); }
From source file:org.seasar.fisshplate.core.element.HorizontalIteratorBlock.java
License:Apache License
private void mergeNoIterationRow(FPContext context, Row row) throws FPMergeException { org.apache.poi.ss.usermodel.Row outRow = context.createCurrentRow(); outRow.setHeight(row.getRowHeight()); Map<String, Object> data = context.getData(); data.put(FPConsts.ROW_NUMBER_NAME, Integer.valueOf(context.getCurrentRowNum() + 1)); for (int i = 0; i < row.getCellElementList().size(); i++) { if (i >= startCellIndex) { break; }// w ww .j av a 2 s .c o m TemplateElement elem = (TemplateElement) row.getCellElementList().get(i); elem.merge(context); } context.nextRow(); }
From source file:org.tiefaces.components.websheet.utility.CellUtility.java
License:MIT License
/** * Copy single row.//from w w w .ja v a2 s.c o m * * @param srcSheet * the src sheet * @param destSheet * the dest sheet * @param sourceRowNum * the source row num * @param destinationRowNum * the destination row num * @param checkLock * the check lock * @param setHiddenColumn * the set hidden column */ private static void copySingleRow(final Sheet srcSheet, final Sheet destSheet, final int sourceRowNum, final int destinationRowNum, final boolean checkLock, final boolean setHiddenColumn) { // Get the source / new row Row newRow = destSheet.getRow(destinationRowNum); Row sourceRow = srcSheet.getRow(sourceRowNum); if (newRow == null) { newRow = destSheet.createRow(destinationRowNum); } newRow.setHeight(sourceRow.getHeight()); // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell copyCell(destSheet, sourceRow, newRow, i, checkLock); } if (setHiddenColumn) { ConfigurationUtility.setOriginalRowNumInHiddenColumn(newRow, sourceRow.getRowNum()); } return; }
From source file:se.mithlond.services.content.impl.ejb.report.ExcelReportServiceBean.java
License:Apache License
/** * {@inheritDoc}/* w w w . j av a2 s . c om*/ */ @Override public Sheet createStandardExcelSheet(@NotNull final Workbook workbook, @NotNull final String sheetName, @NotNull final String sheetTitle, @NotNull final List<String> columnTitles) { // Check sanity Validate.notEmpty(sheetName, "sheetName"); Validate.notEmpty(sheetTitle, "sheetTitle"); Validate.notEmpty(columnTitles, "columnTitles"); Validate.notNull(workbook, "workbook"); // Create a new Workbook if required. final LocalDateTime timestamp = LocalDateTime.now(); final String now = TimeFormat.YEAR_MONTH_DATE_HOURS_MINUTES.print(timestamp).replace(":", " "); // Create the Sheet to return final Sheet toReturn = workbook.createSheet(sheetName + "_" + now); toReturn.setFitToPage(true); toReturn.setHorizontallyCenter(true); // Create a "Title" row containing a single cell (i.e. merged cells) // and where the sheet title is presented and centered. final Row titleRow = toReturn.createRow(0); titleRow.setHeightInPoints(45); final Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(sheetTitle + " " + now); titleCell.setCellStyle(getCellStyle(ExcelElement.TITLE, workbook)); // toReturn.addMergedRegion(CellRangeAddress.valueOf("$A$1:$E$1")); toReturn.addMergedRegion(CellRangeAddress .valueOf("$A$1:$" + CellReference.convertNumToColString(columnTitles.size() - 1) + "1")); // Create a header Row with the column names defined above. final Row headerRow = toReturn.createRow(1); // headerRow.setHeightInPoints(40); // This *could* adjust the header row to fit its internal height. titleRow.setHeight((short) -1); Cell headerCell; for (int i = 0; i < columnTitles.size(); i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(columnTitles.get(i)); headerCell.setCellStyle(getCellStyle(ExcelElement.HEADER, workbook)); toReturn.setDefaultColumnStyle(i, getCellStyle(ExcelElement.NON_WRAPPING, workbook)); toReturn.autoSizeColumn(i); } // All done return toReturn; }
From source file:utilities.XLSResultsManager.java
License:Open Source License
private void closeRecord(ArrayList<CellItem> record, Sheet sheet, Map<String, CellStyle> styles, boolean embedImages) throws IOException { CreationHelper createHelper = wb.getCreationHelper(); Row row = sheet.createRow(rowIndex++); if (embedImages) { row.setHeight((short) 1000); }/*w ww . j a v a 2 s.c o m*/ for (int i = 0; i < record.size(); i++) { CellItem ci = record.get(i); Cell cell = row.createCell(i); if (ci.v != null && (ci.v.startsWith("https://") || ci.v.startsWith("http://"))) { if (embedImages) { if (ci.v.endsWith(".jpg") || ci.v.endsWith(".png")) { int idx = ci.v.indexOf("attachments"); int idxName = ci.v.lastIndexOf('/'); if (idx > 0 && idxName > 0) { String fileName = ci.v.substring(idxName); String stem = basePath + "/" + ci.v.substring(idx, idxName); String imageName = stem + "/thumbs" + fileName + ".jpg"; try { InputStream inputStream = new FileInputStream(imageName); byte[] imageBytes = IOUtils.toByteArray(inputStream); int pictureureIdx = wb.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG); inputStream.close(); ClientAnchor anchor = createHelper.createClientAnchor(); anchor.setCol1(i); anchor.setRow1(rowIndex - 1); anchor.setCol2(i + 1); anchor.setRow2(rowIndex); anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE); //sheet.setColumnWidth(i, 20 * 256); Drawing drawing = sheet.createDrawingPatriarch(); Picture pict = drawing.createPicture(anchor, pictureureIdx); //pict.resize(); } catch (Exception e) { log.info("Error: Missing image file: " + imageName); } } } } cell.setCellStyle(styles.get("link")); if (isXLSX) { XSSFHyperlink url = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL); url.setAddress(ci.v); cell.setHyperlink(url); } else { HSSFHyperlink url = new HSSFHyperlink(HSSFHyperlink.LINK_URL); url.setAddress(ci.v); cell.setHyperlink(url); } cell.setCellValue(ci.v); } else { /* * Write the value as double or string */ boolean cellWritten = false; if (ci.type == CellItem.DECIMAL || ci.type == CellItem.INTEGER && ci.v != null) { try { double vDouble = Double.parseDouble(ci.v); cell.setCellStyle(styles.get("default")); cell.setCellValue(vDouble); cellWritten = true; } catch (Exception e) { // Ignore } } else if (ci.type == CellItem.DATETIME) { DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); try { java.util.Date date = dateFormat.parse(ci.v); cell.setCellStyle(styles.get("datetime")); cell.setCellValue(date); cellWritten = true; } catch (Exception e) { // Ignore } } else if (ci.type == CellItem.DATE) { DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); try { java.util.Date date = dateFormat.parse(ci.v); cell.setCellStyle(styles.get("date")); cell.setCellValue(date); cellWritten = true; } catch (Exception e) { // Ignore } } if (!cellWritten) { cell.setCellStyle(styles.get("default")); cell.setCellValue(ci.v); } } } }