protected SRow importRow(Row poiRow, SSheet sheet) { SRow row = sheet.getRow(poiRow.getRowNum()); row.setHeight(UnitUtil.twipToPx(poiRow.getHeight())); row.setCustomHeight(poiRow.isCustomHeight()); row.setHidden(poiRow.getZeroHeight()); CellStyle rowStyle = poiRow.getRowStyle(); if (rowStyle != null) { row.setCellStyle(importCellStyle(rowStyle)); } for (Cell poiCell : poiRow) { importCell(poiCell, poiRow.getRowNum(), sheet); } return row; }
public void testModifyArrayCells_removeRow() { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); // single-cell array formulas behave just like normal cells CellRangeAddress cra = CellRangeAddress.valueOf("B5"); CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", cra); Cell scell = srange.getTopLeftCell(); assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType()); Row srow = scell.getRow(); assertSame(srow, sheet.getRow(cra.getFirstRow())); sheet.removeRow(srow); assertNull(sheet.getRow(cra.getFirstRow())); // re-create the removed row and cell scell = sheet.createRow(cra.getFirstRow()).createCell(cra.getFirstColumn()); assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType()); assertFalse(scell.isPartOfArrayFormulaGroup()); // we cannot remove rows with cells included in a multi-cell array formula CellRange<? extends Cell> mrange = sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3")); for (Cell mcell : mrange) { int columnIndex = mcell.getColumnIndex(); Row mrow = mcell.getRow(); try { sheet.removeRow(mrow); fail("expected exception"); } catch (IllegalStateException e) { String msg = "Row[rownum=" + mrow.getRowNum() + "] contains cell(s) included in a multi-cell array formula. You cannot change part of an array."; assertEquals(msg, e.getMessage()); } // a failed invocation of Row.removeCell leaves the row // in the state that it was in prior to the invocation assertSame(mrow, sheet.getRow(mrow.getRowNum())); assertSame(mcell, mrow.getCell(columnIndex)); assertTrue(mcell.isPartOfArrayFormulaGroup()); assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType()); } }
private static InterviewInstance getInstance(Row row) throws ParseException { // extract the cells needed from this row Cell interview_id_cell = row.getCell(0, Row.CREATE_NULL_AS_BLANK); Cell line_cell = row.getCell(1, Row.CREATE_NULL_AS_BLANK); Cell speaker_cell = row.getCell(2, Row.CREATE_NULL_AS_BLANK); Cell comment_cell = row.getCell(3, Row.CREATE_NULL_AS_BLANK); ArrayList<Cell> category_cells = new ArrayList<>(5); for (int i = 4; i <= 8; ++i) { Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK); if (cell.getCellType() != Cell.CELL_TYPE_BLANK) category_cells.add(cell); } Cell sentiment_cell = row.getCell(9, Row.CREATE_NULL_AS_BLANK); // verify that all the required information is present verifyNumericCell(interview_id_cell, "Interview ID field"); verifyNumericCell(line_cell, "Line field"); verifySpeakerCell(speaker_cell); verifyCellNotEmpty(comment_cell, "Comment cell"); if (category_cells.isEmpty()) throw new ParseException("No category labels given for samples in row " + row.getRowNum(), 0); Iterator<Cell> row_iter = category_cells.iterator(); ArrayList<Category> categories = new ArrayList<>(); while (row_iter.hasNext()) { Cell category_cell = row_iter.next(); verifyCategoryCell(category_cell); categories.add(Category.getCategory(category_cell.getStringCellValue())); } // convert the cell into an interviewInstance InterviewInstance instance = new InterviewInstance( (int) interview_id_cell.getNumericCellValue(), (int) line_cell.getNumericCellValue(), Speaker.getSpeaker(speaker_cell.getStringCellValue()), comment_cell.getStringCellValue(), categories); return instance; }
/** * 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 File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) { File excelFile = new File("export_parts.xls"); // Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); // Create a blank sheet XSSFSheet sheet = workbook.createSheet("Parts Data"); String header = StringUtils.join(queryResult.getQuery().getSelects(), ";"); String[] columns = header.split(";"); Map<Integer, String[]> data = new HashMap<>(); String[] headerFormatted = createXLSHeaderRow(header, columns, locale); data.put(1, headerFormatted); Map<Integer, String[]> commentsData = new HashMap<>(); String[] headerComments = createXLSHeaderRowComments(header, columns); commentsData.put(1, headerComments); List<String> selects = queryResult.getQuery().getSelects(); int i = 1; for (QueryResultRow row : queryResult.getRows()) { i++; data.put(i, createXLSRow(selects, row, baseURL)); commentsData.put(i, createXLSRowComments(selects, row)); } // Iterate over data and write to sheet Set<Integer> keyset = data.keySet(); int rownum = 0; for (Integer key : keyset) { Row row = sheet.createRow(rownum++); String[] objArr = data.get(key); int cellnum = 0; for (String obj : objArr) { Cell cell = row.createCell(cellnum++); cell.setCellValue(obj); } CreationHelper factory = workbook.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); String[] commentsObjArr = commentsData.get(key); cellnum = 0; for (String commentsObj : commentsObjArr) { if (commentsObj.length() > 0) { Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum); // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum() + 1); Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(commentsObj); comment.setString(str); // Assign the comment to the cell cell.setCellComment(comment); } cellnum++; } } // Define header style Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 10); headerFont.setFontName("Courier New"); headerFont.setItalic(true); headerFont.setColor(IndexedColors.WHITE.getIndex()); CellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFont(headerFont); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); // Set header style for (int j = 0; j < columns.length; j++) { Cell cell = sheet.getRow(0).getCell(j); cell.setCellStyle(headerStyle); if (cell.getCellComment() != null) { String comment = cell.getCellComment().getString().toString(); if (comment.equals(QueryField.CTX_PRODUCT_ID) || comment.equals(QueryField.CTX_SERIAL_NUMBER) || comment.equals(QueryField.PART_MASTER_NUMBER)) { for (int k = 0; k < queryResult.getRows().size(); k++) { Cell grayCell = sheet.getRow(k + 1).getCell(j) != null ? sheet.getRow(k + 1).getCell(j) : sheet.getRow(k + 1).createCell(j); grayCell.setCellStyle(headerStyle); } } } } try { // Write the workbook in file system FileOutputStream out = new FileOutputStream(excelFile); workbook.write(out); out.close(); } catch (Exception e) { LOGGER.log(Level.FINEST, null, e); } return excelFile; }