List of usage examples for org.apache.poi.ss.usermodel Cell getCellStyle
CellStyle getCellStyle();
From source file:org.lisapark.octopus.util.xml.XmlConverterUtils.java
License:Open Source License
/** * /*from w w w . ja v a 2s . c om*/ * @param jsonCells * @param row * @param stack * @param increment * @return */ private static String xmlFromRowAsTreeAttributes(String tagAttributes, Row row, List<String> stack, int increment) { String tagString = ""; Cell cell = row.getCell(row.getFirstCellNum()); int indent = cell.getCellStyle().getIndention(); int absIndent = indent / increment; int diff = stack.size() - absIndent; if (diff == 0) { tagString = leftTagWithAttributes(treeNodeNames.get(absIndent), tagAttributes); stack.add(treeNodeNames.get(absIndent)); } else if (diff > 0) { while (diff > 0) { tagString = tagString + rightTag(stack.get(stack.size() - 1)); stack.remove(stack.size() - 1); diff--; } tagString = tagString + leftTagWithAttributes(treeNodeNames.get(absIndent), tagAttributes); stack.add(treeNodeNames.get(absIndent)); } return tagString; }
From source file:org.lisapark.octopus.util.xml.XmlConverterUtils.java
License:Open Source License
/** * // w w w . j a v a 2 s . c o m * @param tagNodes * @param row * @param stack * @param increment * @return */ private static String xmlFromRowAsTreeNodes(String tagNodes, Row row, List<String> stack, int increment) { String tagString = ""; Cell cell = row.getCell(row.getFirstCellNum()); int indent = cell.getCellStyle().getIndention(); int absIndent = indent / increment; int diff = stack.size() - absIndent; if (diff == 0) { tagString = leftTagWithNodes(treeNodeNames.get(absIndent), tagNodes); stack.add(treeNodeNames.get(absIndent)); } else if (diff > 0) { while (diff > 0) { tagString = tagString + rightTag(stack.get(stack.size() - 1)); stack.remove(stack.size() - 1); diff--; } tagString = tagString + leftTagWithNodes(treeNodeNames.get(absIndent), tagNodes); stack.add(treeNodeNames.get(absIndent)); } return tagString; }
From source file:org.lisapark.octopus.util.xml.XmlConverterUtils.java
License:Open Source License
private static String buidNodeAsString(Cell cell, int i, int j) { StringBuilder cellStringBuilder = new StringBuilder(); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String string;//ww w .ja v a 2s .co m if (i == 0 && cell.getCellStyle().getIndention() == 2 && treeNodeNames.get(1).equalsIgnoreCase(SHIFT)) { string = extractDateAndShiftAsNodes(cell.getStringCellValue()); if (string != null) { cellStringBuilder.append(string); } else { cellStringBuilder.append(node(dataFieldNames.get(j), cell.getStringCellValue())); } } else { cellStringBuilder.append(node(dataFieldNames.get(j), cell.getStringCellValue())); } } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { // cells.append(attribute(dataFieldNames.get(i), cell.getStringCellValue())); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { cellStringBuilder.append(node(dataFieldNames.get(j), cell.getNumericCellValue())); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { cellStringBuilder.append(node(dataFieldNames.get(j), cell.getBooleanCellValue())); } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { cellStringBuilder.append(node(dataFieldNames.get(j), cell.getErrorCellValue())); } else { cellStringBuilder.append(node(dataFieldNames.get(j), "N/A")); } return cellStringBuilder.toString(); }
From source file:org.netxilia.impexp.impl.ExcelImportService.java
License:Open Source License
private ICellCommand copyCell(Cell poiCell, CellReference cellReference, HSSFPalette palette, NetxiliaStyleResolver styleResolver) throws FormulaParsingException { CellStyle poiStyle = poiCell.getCellStyle(); Styles styles = PoiUtils.poiStyle2Netxilia(poiStyle, poiCell.getSheet().getWorkbook().getFontAt(poiStyle.getFontIndex()), palette, styleResolver); IGenericValue value = null;//from w ww .jav a 2s .com Formula formula = null; // log.info("CELL TYPE:" + cellReference + " type:" + poiCell.getCellType() + " " // + (poiCell.getCellType() == Cell.CELL_TYPE_FORMULA ? poiCell.getCellFormula() : "no")); switch (poiCell.getCellType()) { // TODO translate errors case Cell.CELL_TYPE_STRING: value = new StringValue(poiCell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(poiCell)) { DateTime dt = new DateTime(poiCell.getDateCellValue()); // TODO decide whether is date or time if (dt.isBefore(EXCEL_START)) { value = new DateValue(dt.toLocalTime()); } else if (dt.getMillisOfDay() == 0) { value = new DateValue(dt.toLocalDate()); } else { value = new DateValue(dt.toLocalDateTime()); } } else { value = new NumberValue(poiCell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: value = new BooleanValue(poiCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: if (poiCell.getCellFormula() != null) { formula = formulaParser.parseFormula(new Formula("=" + poiCell.getCellFormula())); } break; } if ((styles == null || styles.getItems().isEmpty()) && formula == null && (value == null || value.equals(GenericValueUtils.EMTPY_STRING))) { return null; } return CellCommands.cell(new AreaReference(cellReference), value, formula, styles); }
From source file:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java
License:Open Source License
/** * Adds in a Row to the given Sheet/* w w w .jav a 2 s. c o 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.openmrs.module.reporting.common.ExcelUtilTest.java
License:Open Source License
@Test public void shouldAddStyle() throws Exception { Workbook wb = ExcelUtil.loadWorkbookFromResource("org/openmrs/module/reporting/common/ExcelUtilTest.xls"); Sheet sheet = wb.getSheet("Testing"); // Test Fonts Cell cell = getCellToTheRightOf(sheet, "String"); Assert.assertEquals("This is a String", ExcelUtil.getCellContents(cell)); Assert.assertEquals(Font.BOLDWEIGHT_NORMAL, ExcelUtil.getFont(cell).getBoldweight()); cell.setCellStyle(ExcelUtil.createCellStyle(wb, "bold")); Assert.assertEquals(Font.BOLDWEIGHT_BOLD, ExcelUtil.getFont(cell).getBoldweight()); Assert.assertFalse(ExcelUtil.getFont(cell).getItalic()); Assert.assertEquals(Font.U_NONE, ExcelUtil.getFont(cell).getUnderline()); cell.setCellStyle(ExcelUtil.createCellStyle(wb, "italic,underline")); Assert.assertTrue(ExcelUtil.getFont(cell).getItalic()); Assert.assertEquals(Font.U_SINGLE, ExcelUtil.getFont(cell).getUnderline()); int fontSize = ExcelUtil.getFont(cell).getFontHeightInPoints() + 1; cell.setCellStyle(ExcelUtil.createCellStyle(wb, "size=" + fontSize)); Assert.assertEquals((short) fontSize, ExcelUtil.getFont(cell).getFontHeightInPoints()); // Test other styles Assert.assertFalse(cell.getCellStyle().getWrapText()); Assert.assertEquals(CellStyle.ALIGN_GENERAL, cell.getCellStyle().getAlignment()); Assert.assertEquals(CellStyle.BORDER_NONE, cell.getCellStyle().getBorderBottom()); cell.setCellStyle(ExcelUtil.createCellStyle(wb, "wraptext,align=center,border=bottom")); Assert.assertTrue(cell.getCellStyle().getWrapText()); Assert.assertEquals(CellStyle.ALIGN_CENTER, cell.getCellStyle().getAlignment()); Assert.assertEquals(CellStyle.BORDER_THIN, cell.getCellStyle().getBorderBottom()); // Test Date/*from w w w . jav a 2 s. com*/ Date date = DateUtil.getDateTime(2013, 10, 31); cell.setCellValue(date); ExcelUtil.formatAsDate(cell); Assert.assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType()); Assert.assertTrue(ExcelUtil.isCellDateFormatted(cell)); Assert.assertEquals(date, ExcelUtil.getCellContents(cell)); }
From source file:org.openpythia.utilities.SSUtilities.java
License:Apache License
public static Row copyRow(Sheet sheet, Row sourceRow, int destination) { Row newRow = sheet.createRow(destination); // get the last row from the headings int lastCol = sheet.getRow(0).getLastCellNum(); for (int currentCol = 0; currentCol <= lastCol; currentCol++) { Cell newCell = newRow.createCell(currentCol); // if there is a cell in the template, copy its content and style Cell currentCell = sourceRow.getCell(currentCol); if (currentCell != null) { newCell.setCellStyle(currentCell.getCellStyle()); newCell.setCellComment(currentCell.getCellComment()); switch (currentCell.getCellType()) { case Cell.CELL_TYPE_STRING: newCell.setCellValue(currentCell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(currentCell.getNumericCellValue()); break; case Cell.CELL_TYPE_FORMULA: String dummy = currentCell.getCellFormula(); dummy = dummy.replace("Row", String.valueOf(destination + 1)); newCell.setCellFormula(dummy); newCell.setCellFormula(//from ww w.j av a 2 s .c o m currentCell.getCellFormula().replace("Row", String.valueOf(destination + 1))); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(currentCell.getBooleanCellValue()); break; default: } } } // if the row contains merged regions, copy them to the new row int numberMergedRegions = sheet.getNumMergedRegions(); for (int i = 0; i < numberMergedRegions; i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.getFirstRow() == sourceRow.getRowNum() && mergedRegion.getLastRow() == sourceRow.getRowNum()) { // this region is within the row - so copy it sheet.addMergedRegion(new CellRangeAddress(destination, destination, mergedRegion.getFirstColumn(), mergedRegion.getLastColumn())); } } return newRow; }
From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java
License:Apache License
void writeField(Object v, ValueMetaInterface vMeta, ExcelWriterStepField excelField, Row xlsRow, int posX, Object[] row, int fieldNr, boolean isTitle) throws KettleException { try {//from ww w . j ava2s .c o m boolean cellExisted = true; // get the cell Cell cell = xlsRow.getCell(posX); if (cell == null) { cellExisted = false; cell = xlsRow.createCell(posX); } // if cell existed and existing cell's styles should not be changed, don't if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) { // if the style of this field is cached, reuse it if (!isTitle && data.getCachedStyle(fieldNr) != null) { cell.setCellStyle(data.getCachedStyle(fieldNr)); } else { // apply style if requested if (excelField != null) { // determine correct cell for title or data rows String styleRef = null; if (!isTitle && !Utils.isEmpty(excelField.getStyleCell())) { styleRef = excelField.getStyleCell(); } else if (isTitle && !Utils.isEmpty(excelField.getTitleStyleCell())) { styleRef = excelField.getTitleStyleCell(); } if (styleRef != null) { Cell styleCell = getCellFromReference(styleRef); if (styleCell != null && cell != styleCell) { cell.setCellStyle(styleCell.getCellStyle()); } } } // set cell format as specified, specific format overrides cell specification if (!isTitle && excelField != null && !Utils.isEmpty(excelField.getFormat()) && !excelField.getFormat().startsWith("Image")) { setDataFormat(excelField.getFormat(), cell); } // cache it for later runs if (!isTitle) { data.cacheStyle(fieldNr, cell.getCellStyle()); } } } // create link on cell if requested if (!isTitle && excelField != null && data.linkfieldnrs[fieldNr] >= 0) { String link = data.inputRowMeta.getValueMeta(data.linkfieldnrs[fieldNr]) .getString(row[data.linkfieldnrs[fieldNr]]); if (!Utils.isEmpty(link)) { CreationHelper ch = data.wb.getCreationHelper(); // set the link on the cell depending on link type Hyperlink hyperLink = null; if (link.startsWith("http:") || link.startsWith("https:") || link.startsWith("ftp:")) { hyperLink = ch.createHyperlink(HyperlinkType.URL); hyperLink.setLabel("URL Link"); } else if (link.startsWith("mailto:")) { hyperLink = ch.createHyperlink(HyperlinkType.EMAIL); hyperLink.setLabel("Email Link"); } else if (link.startsWith("'")) { hyperLink = ch.createHyperlink(HyperlinkType.DOCUMENT); hyperLink.setLabel("Link within this document"); } else { hyperLink = ch.createHyperlink(HyperlinkType.FILE); hyperLink.setLabel("Link to a file"); } hyperLink.setAddress(link); cell.setHyperlink(hyperLink); // if cell existed and existing cell's styles should not be changed, don't if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) { if (data.getCachedLinkStyle(fieldNr) != null) { cell.setCellStyle(data.getCachedLinkStyle(fieldNr)); } else { // CellStyle style = cell.getCellStyle(); Font origFont = data.wb.getFontAt(cell.getCellStyle().getFontIndex()); Font hlink_font = data.wb.createFont(); // reporduce original font characteristics hlink_font.setBold(origFont.getBold()); hlink_font.setCharSet(origFont.getCharSet()); hlink_font.setFontHeight(origFont.getFontHeight()); hlink_font.setFontName(origFont.getFontName()); hlink_font.setItalic(origFont.getItalic()); hlink_font.setStrikeout(origFont.getStrikeout()); hlink_font.setTypeOffset(origFont.getTypeOffset()); // make it blue and underlined hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); CellStyle style = cell.getCellStyle(); style.setFont(hlink_font); cell.setCellStyle(style); data.cacheLinkStyle(fieldNr, cell.getCellStyle()); } } } } // create comment on cell if requrested if (!isTitle && excelField != null && data.commentfieldnrs[fieldNr] >= 0 && data.wb instanceof XSSFWorkbook) { String comment = data.inputRowMeta.getValueMeta(data.commentfieldnrs[fieldNr]) .getString(row[data.commentfieldnrs[fieldNr]]); if (!Utils.isEmpty(comment)) { String author = data.commentauthorfieldnrs[fieldNr] >= 0 ? data.inputRowMeta.getValueMeta(data.commentauthorfieldnrs[fieldNr]).getString( row[data.commentauthorfieldnrs[fieldNr]]) : "Kettle PDI"; cell.setCellComment(createCellComment(author, comment)); } } // cell is getting a formula value or static content if (!isTitle && excelField != null && excelField.isFormula()) { // formula case cell.setCellFormula(vMeta.getString(v)); } else { // static content case switch (vMeta.getType()) { case ValueMetaInterface.TYPE_DATE: if (v != null && vMeta.getDate(v) != null) { cell.setCellValue(vMeta.getDate(v)); } break; case ValueMetaInterface.TYPE_BOOLEAN: if (v != null) { cell.setCellValue(vMeta.getBoolean(v)); } break; case ValueMetaInterface.TYPE_STRING: case ValueMetaInterface.TYPE_BINARY: if (v != null) { cell.setCellValue(vMeta.getString(v)); } break; case ValueMetaInterface.TYPE_BIGNUMBER: case ValueMetaInterface.TYPE_NUMBER: case ValueMetaInterface.TYPE_INTEGER: if (v != null) { cell.setCellValue(vMeta.getNumber(v)); } break; default: break; } } } catch (Exception e) { logError("Error writing field (" + data.posX + "," + data.posY + ") : " + e.toString()); logError(Const.getStackTracker(e)); throw new KettleException(e); } }
From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java
License:Apache License
/** * Set specified cell format/* w w w . j a v a 2 s . c om*/ * * @param excelFieldFormat the specified format * @param cell the cell to set up format */ private void setDataFormat(String excelFieldFormat, Cell cell) { if (log.isDebug()) { logDebug(BaseMessages.getString(PKG, "ExcelWriterStep.Log.SetDataFormat", excelFieldFormat, CellReference.convertNumToColString(cell.getColumnIndex()), cell.getRowIndex())); } DataFormat format = data.wb.createDataFormat(); short formatIndex = format.getFormat(excelFieldFormat); CellStyle style = data.wb.createCellStyle(); style.cloneStyleFrom(cell.getCellStyle()); style.setDataFormat(formatIndex); cell.setCellStyle(style); }
From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep_StyleFormatTest.java
License:Apache License
/** * Test applying Format and Style from cell (from a template) when writing fields * * @param fileType/*from w ww . j ava2s . c om*/ * @throws Exception */ private void testStyleFormat(String fileType) throws Exception { setupStepMock(fileType); createStepMeta(fileType); createStepData(fileType); step.init(stepMeta, stepData); // We do not run transformation or executing the whole step // instead we just execute ExcelWriterStepData.writeNextLine() to write to Excel workbook object // Values are written in A2:D2 and A3:D3 rows List<Object[]> rows = createRowData(); for (int i = 0; i < rows.size(); i++) { step.writeNextLine(rows.get(i)); } // Custom styles are loaded from G1 cell Row xlsRow = stepData.sheet.getRow(0); Cell baseCell = xlsRow.getCell(6); CellStyle baseCellStyle = baseCell.getCellStyle(); DataFormat format = stepData.wb.createDataFormat(); // Check style of the exported values in A3:D3 xlsRow = stepData.sheet.getRow(2); for (int i = 0; i < stepData.inputRowMeta.size(); i++) { Cell cell = xlsRow.getCell(i); CellStyle cellStyle = cell.getCellStyle(); if (i > 0) { assertEquals(cellStyle.getBorderRight(), baseCellStyle.getBorderRight()); assertEquals(cellStyle.getFillPattern(), baseCellStyle.getFillPattern()); } else { // cell A2/A3 has no custom style assertFalse(cellStyle.getBorderRight() == baseCellStyle.getBorderRight()); assertFalse(cellStyle.getFillPattern() == baseCellStyle.getFillPattern()); } if (i != 1) { assertEquals(format.getFormat(cellStyle.getDataFormat()), "0.00000"); } else { // cell B2/B3 use different format from the custom style assertEquals(format.getFormat(cellStyle.getDataFormat()), "##0,000.0"); } } }