private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo) throws Exception { Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK); Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType()); nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME)); nCell.setCellStyle(tCell.getCellStyle()); tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK); nCell = newSheet.createRow(1).createCell(0, tCell.getCellType()); nCell.setCellValue( tCell .getStringCellValue() .replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD") .replaceAll( PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom)) .replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo))); nCell.setCellStyle(tCell.getCellStyle()); Row tempRow = tempSheet.getRow(4); Row newRow = newSheet.createRow(4); for (int c = 0; c < tempRow.getLastCellNum(); c++) { tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK); nCell = newRow.createCell(c, tCell.getCellType()); nCell.setCellValue(tCell.getStringCellValue()); nCell.setCellStyle(tCell.getCellStyle()); } for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i); newSheet.addMergedRegion(mergedRegion); } }
private void loadData() { int rowNum = sheet.getLastRowNum(); for (int i = 0; i <= rowNum; i++) { if (i % 2 == 0) { Row rowID = sheet.getRow(i); Row rowName = sheet.getRow(i + 1); for (int j = 0; j < 15; j++) { Cell cellID = rowID.getCell(j); Cell cellName = rowName.getCell(j); String name = WorkBookUtil.getCellValue(cellName); String cellValue = WorkBookUtil.getCellValue(cellID); if (name == null || name.equalsIgnoreCase("")) { continue; } int parseInt = Integer.parseInt(cellValue); if (catalogUsers.containsValue(name)) { repeatUsers.put(parseInt, name); } if (cellName.getCellStyle().getFillPattern() == CellStyle.SOLID_FOREGROUND) { colorUsers.put(parseInt, name); } catalogUsers.put(parseInt, name); } } } }
public static void copyCell(Cell oldCell, Cell newCell, boolean copyStyle) { if (copyStyle) { newCell.setCellStyle(oldCell.getCellStyle()); } switch (oldCell.getCellType()) { case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_BLANK: newCell.setCellType(Cell.CELL_TYPE_BLANK); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
/** * 取对应单元格类型的值 * * @param c 列数 * @return 单元格的值 */ private String getCellValue(Cell c) { String o = null; switch (c.getCellType()) { case Cell.CELL_TYPE_BLANK: o = ""; break; case Cell.CELL_TYPE_BOOLEAN: o = String.valueOf(c.getBooleanCellValue()); break; case CELL_TYPE_FORMULA: o = String.valueOf(c.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(c)) { // 处理日期格式、时间格式 SimpleDateFormat sdf; if (c.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) { sdf = new SimpleDateFormat("HH:mm"); } else { // 日期 sdf = new SimpleDateFormat("yyyy-MM-dd"); } Date date = c.getDateCellValue(); o = sdf.format(date).equals("1899-12-31") ? "" : sdf.format(date); } else if (c.getCellStyle().getDataFormat() == 58) { // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58) SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); double value = c.getNumericCellValue(); Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value); o = sdf.format(date); } break; case Cell.CELL_TYPE_STRING: o = c.getStringCellValue(); break; default: o = null; break; } return o; }
/* * Cannot keep existing formats when using CellBlock. * Take a file with existing formats and write it back. */ public static void issue22() throws InvalidFormatException, IOException { System.out.println("Testing issue22 ================================="); FileInputStream in = new FileInputStream(new File("../resources/issue22.xlsx")); Workbook wb = WorkbookFactory.create(in); Sheet sheet0 = wb.getSheetAt(0); Sheet sheet1 = wb.getSheetAt(1); Sheet sheet2 = wb.getSheetAt(2); Cell c00_before = sheet1.getRow(0).getCell(0); CellStyle cs_before = c00_before.getCellStyle(); System.out.println( "Background color before creation is: " + cs_before.getFillBackgroundColor()); System.out.println("Cell style is: " + cs_before.toString()); double[] data = {1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0}; System.out.println("On Sheet 1 ...."); // need to create=true, because sheet is empty! RCellBlock cb0 = new RCellBlock(sheet0, 0, 0, 3, 3, true); cb0.setMatrixData(0, 2, 0, 2, data, true, null); System.out.println("On Sheet 2 ...."); RCellBlock cb1 = new RCellBlock(sheet1, 0, 0, 3, 3, false); Cell c00 = cb1.getCell(0, 0); CellStyle cs = c00.getCellStyle(); System.out.println("Background color after creation is: " + cs.getFillBackgroundColor()); System.out.println("Cell style is: " + cs.toString()); // good news, that the BackgroundColor in the CellBlock is maintained! cb1.setMatrixData(0, 2, 0, 2, data, true, null); System.out.println("On Sheet 3 ...."); RCellBlock cb2 = new RCellBlock(sheet2, 0, 0, 3, 3, false); cb2.setMatrixData(0, 2, 0, 2, data, true, null); FileOutputStream out = new FileOutputStream("/tmp/issue22_out.xlsx"); wb.write(out); out.close(); System.out.println("Wrote /tmp/issue22_out.xlsx"); }
@Test public void testReadCell() throws Exception { when(mockCell.getCellType()).thenReturn(Cell.CELL_TYPE_NUMERIC); final HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); final HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle(); final Date currentDate = new Date(); cellStyle.setDataFormat( hssfWorkbook.getCreationHelper().createDataFormat().getFormat("m/d/yyyy h:mm")); when(mockCell.getCellStyle()).thenReturn(cellStyle); when(mockCell.getDateCellValue()).thenReturn(currentDate); final Calendar calendar = calendarConverter.readCell(mockCell); assertNotNull(calendar); assertEquals(currentDate.getTime(), calendar.getTimeInMillis()); }
public void readUnchangableCell(Row row, int cellIdx, FormDisplayCell tableCell, DataDto dto) { Cell cell = row.getCell(cellIdx); if (null == cell) { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " current value: " + dto.getValue() + " cell is null."); } if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC && cell.getCellType() != Cell.CELL_TYPE_FORMULA) { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " current value: " + dto.getValue() + " cell is is not numeric."); } double editedValue = cell.getNumericCellValue(); if ("0.00%".equals(cell.getCellStyle().getDataFormatString())) { // Percent editedValue = editedValue * 100; } String editedValueString = formatter.formatValueToAtLeastDP(dto, editedValue); if (!editedValueString.equals(dto.getValue()) && !editedValueString.isEmpty()) { // value has changed so log a warning metaData.addWarning( row.getRowNum(), cellIdx, dto.getValue(), editedValueString, "This is a " + tableCell.getCellType().name() + " cell. It cannot be changed."); } }
public void printStyles(Workbook wb) { if (DEFAULTS_CLASS_CSS == null) { DEFAULTS_CLASS_CSS = getDefaultsClassCss(); } out.format(DEFAULTS_CLASS_CSS); Set<CellStyle> seen = new HashSet<CellStyle>(); sheet = wb.getSheetAt(sheetNum); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); for (Cell cell : row) { CellStyle style = cell.getCellStyle(); if (!seen.contains(style)) { printStyle(style); seen.add(style); } } } }
private void contentProcess( Sheet tempSheet, Sheet newSheet, int contentRow, ConfirmationRecord data) throws Exception { int tempRowNum = -1; if (data.getAction() == null) { tempRowNum = 7; } else if (data.getAction().getParamKey().equals("CONFIRMATION_LOG_ACTION_ACCEPT")) { tempRowNum = 5; } else { tempRowNum = 6; } Row tempRow = tempSheet.getRow(tempRowNum); Row newRow = newSheet.createRow(contentRow); for (int c = 0; c < tempRow.getLastCellNum(); c++) { Cell tCell = tempRow.getCell(c); Cell cell = newRow.createCell(c, tCell.getCellType()); setValue(cell, data); cell.setCellStyle(tCell.getCellStyle()); cell.getRow().getSheet().setColumnWidth(c, tempRow.getSheet().getColumnWidth(c)); } }
@Override public void renderCell(Element element, int rowIndex, int columnIndex) { Cell cell = sheet.getRow(rowIndex).createCell(columnIndex); Double numericValue; if (isDateCell(element)) { DateFormat df = new SimpleDateFormat(getDateCellFormat(element)); try { cell.setCellValue(df.parse(getElementText(element))); } catch (ParseException pex) { System.out.println("Invalid Usage"); } } else if ((numericValue = getNumericValue(element)) != null) { cell.setCellValue(numericValue); } else { cell = sheet.getRow(rowIndex).createCell(columnIndex, Cell.CELL_TYPE_STRING); cell.setCellValue(getElementText(element)); } Style style = styleMapper.getStyleForElement(element); cell.setCellStyle(styleGenerator.getStyle(cell, style)); if (isDateCell(element)) { CreationHelper createHelper = sheet.getWorkbook().getCreationHelper(); cell.getCellStyle() .setDataFormat(createHelper.createDataFormat().getFormat(getDateCellFormat(element))); } String commentText; if ((commentText = getCellCommentText(element)) != null) { ExcelCellCommentGenerator.addCellComment(cell, commentText, getCellCommentDimension(element)); } if (definesFreezePane(element)) { sheet.createFreezePane(columnIndex, rowIndex); } }
private java.util.Date getDateValue(Cell cell) { java.util.Date dtValue = null; try { dtValue = cell.getDateCellValue(); } catch (Exception ex) { // ignore } String fmt = cell.getCellStyle().getDataFormatString(); double dv = cell.getNumericCellValue(); if (dtValue == null) { dtValue = getJavaDate(dv); } if (dtValue != null) { if (isTimestampFormat(fmt)) { return new java.sql.Timestamp(dtValue.getTime()); } else { return new java.sql.Date(dtValue.getTime()); } } return null; }
public static void exportExcelForMap( Workbook workBook, List<Map<String, Object>> dataList, OutputStream outputStream) { try { Sheet sheet = workBook.getSheetAt(0); int lastRowNum = sheet.getLastRowNum(); Row row = sheet.getRow(lastRowNum); int rowNum = row.getLastCellNum(); List<String> rowNames = new ArrayList<String>(); List<CellStyle> styles = new ArrayList<CellStyle>(); for (int i = 0; i < rowNum; i++) { Cell cell = row.getCell(i); rowNames.add(cell.getStringCellValue()); styles.add(cell.getCellStyle()); } if (dataList != null && dataList.size() > 0) { for (int i = 0; i < dataList.size(); i++) { Map<String, Object> rowData = dataList.get(i); rowData.put("NUM", lastRowNum + i); createRow(sheet, rowData, rowNames, styles, lastRowNum + i); rowData = null; } } else { sheet.removeRow(row); } workBook.write(outputStream); outputStream.flush(); } catch (IOException e) { throw new RuntimeException("导出文件发生异常"); } finally { if (outputStream != null) { try { outputStream.close(); } catch (IOException e) { throw new RuntimeException("关闭导出文件流发生异常"); } } } }
public void generate( List<String> sheetNames, List<String[]> headersList, List<List<String[]>> contents) throws IOException { for (int i = 0; i < sheetNames.size(); i++) { // Create a blank sheet XSSFSheet spreadsheet = workbook.createSheet(sheetNames.get(i)); // Create row object XSSFRow row; // This data needs to be written (Object[]) Map<Integer, String[]> contentMap = new TreeMap<>(); contentMap.put(1, headersList.get(i)); for (int j = 0; j < contents.get(i).size(); j++) { contentMap.put(j + 2, contents.get(i).get(j)); } // Iterate over data and write to sheet Set<Integer> keyid = contentMap.keySet(); int rowid = 0; for (Integer key : keyid) { row = spreadsheet.createRow(rowid++); String[] objectArr = contentMap.get(key); int cellid = 0; for (String obj : objectArr) { Cell cell = row.createCell(cellid++); CellStyle style = cell.getCellStyle(); style.setWrapText(true); cell.setCellStyle(style); cell.setCellValue(obj); } } // for (int c = 0; c < headersList.get(i).length; c++) { // spreadsheet.autoSizeColumn(c); // } // Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(fileName)); workbook.write(out); out.close(); } }
/** * 导出时间适配器 * * @param fieldValue * @param fieldName * @return * @throws AdapterException */ public void outputDateAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell) throws AdapterException { log.debug( "in DefaultOutputAdapter:outputDateAdapter fieldName:{} fieldValue:{}", fieldName, fieldValue); Date date = null; if (fieldValue == null) { log.debug("fieldValue is null return"); cell.setCellValue(""); return; } else if (fieldValue instanceof Date) { log.debug("fieldValue instanceof Date "); date = (Date) fieldValue; } else if (fieldValue instanceof String) { log.debug("fieldValue instanceof String "); InputDateConfig config = dataBean.getInputConfig(fieldName); try { date = DateUtil.formatToDate((String) fieldValue, config.format()); } catch (ParseException e) { throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell); } } else if (fieldValue instanceof Long) { log.debug("fieldValue instanceof Long "); date = new Date((Long) fieldValue); } else { throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell); } Workbook workbook = cell.getSheet().getWorkbook(); OutputDateConfig outputConfig = dataBean.getOutputConfig(fieldName); CellStyle cellStyle = cell.getCellStyle(); if (cellStyle == null) cellStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(outputConfig.format())); cell.setCellStyle(cellStyle); cell.setCellValue(date); }
static int getColorVall(Cell cellVall) throws SQLException { if (cellVall != null) { short color = cellVall.getCellStyle().getFillForegroundColor(); switch (color) { case 64: return 1; case 10: return 3; case 17: return 5; case 56: return 7; case 12: return 7; case 13: return 7; case 57: return 8; case 30: return 8; case 51: return 9; case 52: return 9; case 53: return 9; case 36: return 10; default: return 1; } } return 0; }
private CellStyle cloneStyle(Cell cell) { CellStyle newStyle = spreadsheet.getWorkbook().createCellStyle(); newStyle.cloneStyleFrom(cell.getCellStyle()); return newStyle; }
/** * Compute width of a single cell * * @param cell the cell whose width is to be calculated * @param defaultCharWidth the width of a single character * @param formatter formatter used to prepare the text to be measured * @param useMergedCells whether to use merged cells * @return the width in pixels */ public static double getCellWidth( Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells) { Sheet sheet = cell.getSheet(); Workbook wb = sheet.getWorkbook(); Row row = cell.getRow(); int column = cell.getColumnIndex(); int colspan = 1; for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress region = sheet.getMergedRegion(i); if (containsCell(region, row.getRowNum(), column)) { if (!useMergedCells) { // If we're not using merged cells, skip this one and move on to the next. return -1; } cell = row.getCell(region.getFirstColumn()); colspan = 1 + region.getLastColumn() - region.getFirstColumn(); } } CellStyle style = cell.getCellStyle(); int cellType = cell.getCellType(); // for formula cells we compute the cell width for the cached formula result if (cellType == Cell.CELL_TYPE_FORMULA) cellType = cell.getCachedFormulaResultType(); Font font = wb.getFontAt(style.getFontIndex()); AttributedString str; TextLayout layout; double width = -1; if (cellType == Cell.CELL_TYPE_STRING) { RichTextString rt = cell.getRichStringCellValue(); String[] lines = rt.getString().split("\\n"); for (int i = 0; i < lines.length; i++) { String txt = lines[i] + defaultChar; str = new AttributedString(txt); copyAttributes(font, str, 0, txt.length()); if (rt.numFormattingRuns() > 0) { // TODO: support rich text fragments } layout = new TextLayout(str.getIterator(), fontRenderContext); if (style.getRotation() != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but * is added by the standard Excel autosize. */ AffineTransform trans = new AffineTransform(); trans.concatenate( AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0)); trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple)); width = Math.max( width, ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention()); } else { width = Math.max( width, ((layout.getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention()); } } } else { String sval = null; if (cellType == Cell.CELL_TYPE_NUMERIC) { // Try to get it formatted to look the same as excel try { sval = formatter.formatCellValue(cell, dummyEvaluator); } catch (Exception e) { sval = String.valueOf(cell.getNumericCellValue()); } } else if (cellType == Cell.CELL_TYPE_BOOLEAN) { sval = String.valueOf(cell.getBooleanCellValue()).toUpperCase(); } if (sval != null) { String txt = sval + defaultChar; str = new AttributedString(txt); copyAttributes(font, str, 0, txt.length()); layout = new TextLayout(str.getIterator(), fontRenderContext); if (style.getRotation() != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but * is added by the standard Excel autosize. */ AffineTransform trans = new AffineTransform(); trans.concatenate( AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0)); trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple)); width = Math.max( width, ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention()); } else { width = Math.max( width, ((layout.getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention()); } } } return width; }
public String readDataCell(Row row, int cellIdx, DataDto dto, CellType cellType) { Cell cell = row.getCell(cellIdx); if (null == cell) { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " current value: " + dto.getValue() + " cell is null."); return null; } String editedValueString = null; if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || cell.getCellType() == Cell.CELL_TYPE_FORMULA) { try { double editedValue = cell.getNumericCellValue(); if ("0.00%".equals(cell.getCellStyle().getDataFormatString())) { // Percent editedValue = editedValue * 100; } editedValueString = formatter.formatValueToAtLeastDP(dto, editedValue); } catch (IllegalStateException e) { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " current value: " + dto.getValue() + " cell is not numeric. CELL TYPE is " + cell.getCellType() + ". Cannot read cell."); metaData.addWarning( row.getRowNum(), cellIdx, " current value: " + dto.getValue() + " cell is not numeric. CELL TYPE is " + cell.getCellType() + ". Cannot read cell."); } } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { RichTextString editedValue = cell.getRichStringCellValue(); if (editedValue.getString().isEmpty()) { editedValueString = ""; } else { if (isNumberInString(editedValue.getString())) { editedValueString = editedValue.getString(); } else { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " current value: " + dto.getValue() + " data '" + editedValue.getString() + "' is not numeric. CELL TYPE is " + cell.getCellType()); } } } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { editedValueString = ""; } else { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " current value: " + dto.getValue() + " cell is not numeric. CELL TYPE is " + cell.getCellType()); } return editedValueString; }
/** Adds in a Row to the given Sheet */ 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 ExcelStyleHelper styleHelper = new ExcelStyleHelper(wb); String prefix = getExpressionPrefix(design); String suffix = getExpressionSuffix(design); 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) { String contents = ExcelUtil.getCellContentsAsString(cellToClone); newCell.setCellStyle(cellToClone.getCellStyle()); try { newCell.setCellFormula(cellToClone.getCellFormula()); } catch (Exception e) { // Do nothing here. I don't know why POI throw exceptions here when the cell is not a // formula, but this suppresses them... } 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); } } } } if (ObjectUtil.notNull(contents)) { Object newContents = EvaluationUtil.evaluateExpression( contents, cellToAdd.getReplacementData(), prefix, suffix); ExcelUtil.setCellContents(styleHelper, newCell, newContents); } } } return newRow; }