List of usage examples for org.apache.poi.ss.util CellReference formatAsString
public String formatAsString()
From source file:RefDiviedMain.java
License:Creative Commons License
public static Element getTable(String name) { DataFormatter formatter = new DataFormatter(Locale.US); if (name == null) { DocumentBuilder db = null; try {/*from ww w . ja v a 2s. co m*/ db = dbf.newDocumentBuilder(); } catch (ParserConfigurationException ex) { ta.append("\nerrors happen:\n"); ta.append(ex.getMessage() + "\n"); } doc = db.newDocument(); } if (name == null) { name = "C:\\Users\\DLiu1\\Documents\\NetBeansProjects\\Simon\\dist\\Table 1"; } String fileName = name + ".xls"; File aaa = new File(fileName); if (!aaa.exists()) { RefDiviedMain .error(fileName + " doesn't exist, please copy the " + fileName + " into the same folder!"); return null; } Element tableFrame = null; try { tableFrame = doc.createElement("table"); tableFrame.setAttribute("frame", "hsides"); tableFrame.setAttribute("rules", "groups"); Element thead = doc.createElement("thead"); Element tbody = doc.createElement("tbody"); tableFrame.appendChild(thead); tableFrame.appendChild(tbody); /** Creating Input Stream**/ //InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName ); FileInputStream myInput = new FileInputStream(aaa); /** Create a POIFSFileSystem object**/ POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); /** Create a workbook using the File System**/ HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); /** Get the first sheet from workbook**/ HSSFSheet mySheet = myWorkBook.getSheetAt(0); /** We now need something to iterate through the cells.**/ Iterator rowIter = mySheet.rowIterator(); int theRow = 0; int theadRows = 1; while (rowIter.hasNext()) { theRow++; HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); //Vector cellStoreVector=new Vector(); System.out.println("\nprinting " + theRow); Element tr = doc.createElement("tr"); System.out.println("\nprinting " + theRow); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); CellProperties cp = new CellProperties(myCell); Element td = null; int colspan = cp.getColspan(); int rowspan = cp.getRowspan(); CellReference ref = new CellReference(myCell); System.out.println( "The value of " + ref.formatAsString() + " is " + formatter.formatCellValue(myCell)); // String myCellValue = myCell.toString(); // myCell.setCellType(Cell.CELL_TYPE_STRING); // String myCellValue = myCell.getRichStringCellValue().toString(); String myCellValue = formatter.formatCellValue(myCell); if (myCellValue != null && myCellValue.trim().endsWith(".0")) { System.out.println(myCellValue + " have 0"); myCellValue = myCellValue.replace(".0", ""); } System.out .println(myCellValue + ": colspan:" + cp.getColspan() + " rowspan:" + cp.getRowspan()); if (rowspan > 1) { if (theRow == 1) { theadRows = rowspan; } } if (theRow <= theadRows) { td = doc.createElement("th"); td.setAttribute("align", "left"); } else { td = doc.createElement("td"); td.setAttribute("align", "left"); td.setAttribute("valign", "top"); } if (colspan > 1) { td.setAttribute("colspan", colspan + ""); } if (rowspan > 1) { td.setAttribute("rowspan", rowspan + ""); } if ((colspan > 1 || rowspan > 1) && myCellValue.trim().equals("")) { continue; } Element bold = doc.createElement("bold"); tr.appendChild(td); td.appendChild(doc.createTextNode(myCellValue.trim())); // cellStoreVector.addElement(myCell); } if (theRow <= theadRows) { thead.appendChild(tr); } else { tbody.appendChild(tr); } // cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { ta.append("\nerrors happen:\n"); ta.append(e.getMessage() + "\n"); } return tableFrame; }
From source file:RefSouceOnlyMain.java
License:Creative Commons License
public static Element getTable(String name) { DataFormatter formatter = new DataFormatter(Locale.US); if (name == null) { DocumentBuilder db = null; try {/*from w w w . j a v a 2 s .c o m*/ db = dbf.newDocumentBuilder(); } catch (ParserConfigurationException ex) { ta.append("\nerrors happen:\n"); ta.append(ex.getMessage() + "\n"); } doc = db.newDocument(); } if (name == null) { name = "C:\\Users\\DLiu1\\Documents\\NetBeansProjects\\Simon\\dist\\Table 1"; } String fileName = name + ".xls"; File aaa = new File(fileName); if (!aaa.exists()) { RefSouceOnlyMain .error(fileName + " doesn't exist, please copy the " + fileName + " into the same folder!"); return null; } Element tableFrame = null; try { tableFrame = doc.createElement("table"); tableFrame.setAttribute("frame", "hsides"); tableFrame.setAttribute("rules", "groups"); Element thead = doc.createElement("thead"); Element tbody = doc.createElement("tbody"); tableFrame.appendChild(thead); tableFrame.appendChild(tbody); /** Creating Input Stream**/ //InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName ); FileInputStream myInput = new FileInputStream(aaa); /** Create a POIFSFileSystem object**/ POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); /** Create a workbook using the File System**/ HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); /** Get the first sheet from workbook**/ HSSFSheet mySheet = myWorkBook.getSheetAt(0); /** We now need something to iterate through the cells.**/ Iterator rowIter = mySheet.rowIterator(); int theRow = 0; int theadRows = 1; while (rowIter.hasNext()) { theRow++; HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); //Vector cellStoreVector=new Vector(); System.out.println("\nprinting " + theRow); Element tr = doc.createElement("tr"); System.out.println("\nprinting " + theRow); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); CellProperties cp = new CellProperties(myCell); Element td = null; int colspan = cp.getColspan(); int rowspan = cp.getRowspan(); CellReference ref = new CellReference(myCell); System.out.println( "The value of " + ref.formatAsString() + " is " + formatter.formatCellValue(myCell)); // String myCellValue = myCell.toString(); // myCell.setCellType(Cell.CELL_TYPE_STRING); // String myCellValue = myCell.getRichStringCellValue().toString(); String myCellValue = formatter.formatCellValue(myCell); if (myCellValue != null && myCellValue.trim().endsWith(".0")) { System.out.println(myCellValue + " have 0"); myCellValue = myCellValue.replace(".0", ""); } System.out .println(myCellValue + ": colspan:" + cp.getColspan() + " rowspan:" + cp.getRowspan()); if (rowspan > 1) { if (theRow == 1) { theadRows = rowspan; } } if (theRow <= theadRows) { td = doc.createElement("th"); td.setAttribute("align", "left"); } else { td = doc.createElement("td"); td.setAttribute("align", "left"); td.setAttribute("valign", "top"); } if (colspan > 1) { td.setAttribute("colspan", colspan + ""); } if (rowspan > 1) { td.setAttribute("rowspan", rowspan + ""); } if ((colspan > 1 || rowspan > 1) && myCellValue.trim().equals("")) { continue; } Element bold = doc.createElement("bold"); tr.appendChild(td); td.appendChild(doc.createTextNode(myCellValue.trim())); // cellStoreVector.addElement(myCell); } if (theRow <= theadRows) { thead.appendChild(tr); } else { tbody.appendChild(tr); } // cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { ta.append("\nerrors happen:\n"); ta.append(e.getMessage() + "\n"); } return tableFrame; }
From source file:CellStyleDetails.java
License:Apache License
public static void main(String[] args) throws Exception { if (args.length == 0) { throw new IllegalArgumentException("Filename must be given"); }//from w ww .j a va 2 s .c om Workbook wb = WorkbookFactory.create(new File(args[0])); DataFormatter formatter = new DataFormatter(); for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) { Sheet sheet = wb.getSheetAt(sn); System.out.println("Sheet #" + sn + " : " + sheet.getSheetName()); for (Row row : sheet) { System.out.println(" Row " + row.getRowNum()); for (Cell cell : row) { CellReference ref = new CellReference(cell); System.out.print(" " + ref.formatAsString()); System.out.print(" (" + cell.getColumnIndex() + ") "); CellStyle style = cell.getCellStyle(); System.out.print("Format=" + style.getDataFormatString() + " "); System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " "); System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " "); Font font = wb.getFontAt(style.getFontIndex()); System.out.print("Font=" + font.getFontName() + " "); System.out.print("FontColor="); if (font instanceof HSSFFont) { System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb))); } if (font instanceof XSSFFont) { System.out.print(renderColor(((XSSFFont) font).getXSSFColor())); } System.out.println(); System.out.println(" " + formatter.formatCellValue(cell)); } } System.out.println(); } }
From source file:br.ufal.cideei.util.count.MetricsTable.java
License:Open Source License
private void printFooters() { int columns = columnMapping.size(); Row firstRow = sheet.getRow(1);// w ww. ja va 2 s . c o m Row lastRow = sheet.getRow(rowCount - 1); Row sumFooterRow = sheet.createRow(rowCount++); Cell sumFooterLabelCell = sumFooterRow.createCell(0); sumFooterLabelCell.setCellValue("SUM"); Row averageFooterRow = sheet.createRow(rowCount++); Cell averageFooterLabelCell = averageFooterRow.createCell(0); averageFooterLabelCell.setCellValue("AVERAGE"); for (int index = 0; index <= columns; index++) { Cell cell = firstRow.getCell(index); if (cell == null) { cell = firstRow.createCell(index); } Cell sumFooterCell = sumFooterRow.createCell(index); Cell averageFooterCell = averageFooterRow.createCell(index); CellReference firstCell = new CellReference(firstRow.getCell(index)); Cell lastRowCell = lastRow.getCell(index); if (lastRowCell == null) { lastRowCell = lastRow.createCell(index); } CellReference lastCell = new CellReference(lastRowCell); sumFooterCell .setCellFormula("SUM(" + firstCell.formatAsString() + ":" + lastCell.formatAsString() + ")"); averageFooterCell.setCellFormula( "AVERAGE(" + firstCell.formatAsString() + ":" + lastCell.formatAsString() + ")"); } }
From source file:br.ufal.cideei.util.count.SummaryBuilder.java
License:Open Source License
public static void buildSummary(String splShortName) throws InvalidFormatException, FileNotFoundException, IOException { // final String userHomeFolder = System.getProperty("user.home").substring(3); String userHomeFolder = "C:\\tst"; final String output = userHomeFolder + File.separator + "summ.xls"; File outputFile = new File(output); Workbook outputWorkbook;/*www .ja va 2 s . c o m*/ if (!outputFile.exists()) { outputFile.createNewFile(); outputWorkbook = new HSSFWorkbook(); } else { FileInputStream inputFileStream = new FileInputStream(outputFile); outputWorkbook = WorkbookFactory.create(inputFileStream); } { List<String> referencesForRDA3 = new ArrayList<String>(); List<String> referencesForUVA3 = new ArrayList<String>(); List<String> referencesForRDA2 = new ArrayList<String>(); List<String> referencesForUVA2 = new ArrayList<String>(); String fileName = "fs-" + splShortName + ".xls"; String filePath = userHomeFolder + File.separator; String fullFileName = filePath + File.separator + "fs-" + splShortName + ".xls"; Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(fullFileName))); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); Row headerRow = sheet.getRow(0); for (Cell cell : headerRow) { String stringCellValue = cell.getStringCellValue(); if (stringCellValue.equals("rd")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForRDA2.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("uv")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForUVA2.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("rd (a3)")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForRDA3.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("uv (a3)")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForUVA3.add(cellRefForAnotherSheet); } } } if (outputWorkbook.getSheet(splShortName) != null) { outputWorkbook.removeSheetAt(outputWorkbook.getSheetIndex(splShortName)); } Sheet outputSheet = outputWorkbook.createSheet(splShortName); Row RDA2Row = outputSheet.createRow(0); RDA2Row.createCell(0).setCellValue("RD A2"); for (int i = 0; i < referencesForRDA2.size(); i++) { Cell createdCell = RDA2Row.createCell(i + 1); System.out.println(referencesForRDA2.get(i)); createdCell.setCellType(Cell.CELL_TYPE_FORMULA); createdCell.setCellValue(referencesForRDA2.get(i)); } Row UVA2Row = outputSheet.createRow(1); UVA2Row.createCell(0).setCellValue("UV A2"); for (int i = 0; i < referencesForUVA2.size(); i++) { Cell createdCell = UVA2Row.createCell(i + 1); createdCell.setCellFormula(referencesForUVA2.get(i)); } Row RDA3Row = outputSheet.createRow(2); RDA3Row.createCell(0).setCellValue("RD A3"); for (int i = 0; i < referencesForRDA3.size(); i++) { Cell createdCell = RDA3Row.createCell(i + 1); createdCell.setCellFormula(referencesForRDA3.get(i)); } Row UVA3Row = outputSheet.createRow(3); UVA3Row.createCell(0).setCellValue("UV A3"); for (int i = 0; i < referencesForUVA3.size(); i++) { Cell createdCell = UVA3Row.createCell(i + 1); createdCell.setCellFormula(referencesForUVA3.get(i)); } } FileOutputStream fileOutputStream = new FileOutputStream(outputFile); outputWorkbook.write(fileOutputStream); fileOutputStream.close(); }
From source file:com.adanac.excel.reader.sax.ExcelXSSFSheetXMLHandler.java
License:Apache License
/** * Output an empty-cell comment./* w ww . ja v a2 s . c om*/ * * @throws BingSaxReadStopException */ private void outputEmptyCellComment(CellReference cellRef) throws BingSaxReadStopException { String cellRefString = cellRef.formatAsString(); XSSFComment comment = commentsTable.findCellComment(cellRefString); output.cell(rowNum, cellRefString, null, comment); }
From source file:com.canoo.webtest.plugins.exceltest.ExcelFindRow.java
License:Open Source License
public void doExecute() { final CellReference cellReference = ExcelCellUtils.getCellReference(this, null, getStartRow(), getCol()); final Sheet excelSheet = getExcelSheet(); int row = cellReference.getRow(); while (row <= excelSheet.getLastRowNum()) { final Cell excelCellAt = ExcelCellUtils.getExcelCellAt(this, row, cellReference.getCol()); if (verifyText(ExcelCellUtils.getCellValueAt(excelCellAt))) { setWebtestProperty(getProperty(), String.valueOf(row + 1), getPropertyType()); return; }//from ww w . j a v a 2 s.c o m row++; } throw new StepFailedException( "No cells were found matching '" + getText() + "' starting from " + cellReference.formatAsString(), this); }
From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java
License:Open Source License
/** * Shifts rows between startRow and endRow n number of rows. If you use a * negative number for n, the rows will be shifted upwards. This method * ensures that rows can't wrap around.//from w ww . j a va 2 s . c o m * <p> * If you are adding / deleting rows, you might want to change the number of * visible rows rendered {@link #getRows()} with {@link #setMaxRows(int)}. * <p> * See {@link Sheet#shiftRows(int, int, int, boolean, boolean)}. * * @param startRow * The first row to shift, 0-based * @param endRow * The last row to shift, 0-based * @param n * Number of rows to shift, positive numbers shift down, negative * numbers shift up. * @param copyRowHeight * True to copy the row height during the shift * @param resetOriginalRowHeight * True to set the original row's height to the default */ public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) { Sheet sheet = getActiveSheet(); int lastNonBlankRow = getLastNonBlankRow(sheet); sheet.shiftRows(startRow, endRow, n, copyRowHeight, resetOriginalRowHeight); // need to re-send the cell values to client // remove all cached cell data that is now empty getFormulaEvaluator().clearAllCachedResultValues(); int start = n < 0 ? Math.max(lastNonBlankRow, startRow) : startRow; int end = n < 0 ? endRow : startRow + n - 1; valueManager.updateDeletedRowsInClientCache(start + 1, end + 1); int firstAffectedRow = n < 0 ? startRow + n : startRow; int lastAffectedRow = n < 0 ? endRow : endRow + n; if (copyRowHeight || resetOriginalRowHeight) { // might need to increase the size of the row heights array int oldLength = getState(false).rowH.length; int neededLength = endRow + n + 1; if (n > 0 && oldLength < neededLength) { getState().rowH = Arrays.copyOf(getState().rowH, neededLength); } for (int i = firstAffectedRow; i <= lastAffectedRow; i++) { Row row = sheet.getRow(i); if (row != null) { if (row.getZeroHeight()) { getState().rowH[i] = 0f; } else { getState().rowH[i] = row.getHeightInPoints(); } } else { getState().rowH[i] = sheet.getDefaultRowHeightInPoints(); } } } if (hasSheetOverlays()) { reloadImageSizesFromPOI = true; } // need to shift the cell styles, clear and update // need to go -1 and +1 because of shifted borders.. final ArrayList<Cell> cellsToUpdate = new ArrayList<Cell>(); for (int r = (firstAffectedRow - 1); r <= (lastAffectedRow + 1); r++) { if (r < 0) { r = 0; } Row row = sheet.getRow(r); final Integer rowIndex = new Integer(r + 1); if (row == null) { valueManager.updateDeletedRowsInClientCache(rowIndex, rowIndex); if (getState(false).hiddenRowIndexes.contains(rowIndex)) { getState().hiddenRowIndexes.remove(rowIndex); } for (int c = 0; c < getState().cols; c++) { styler.clearCellStyle(r, c); } } else { if (row.getZeroHeight()) { getState().hiddenRowIndexes.add(rowIndex); } else if (getState(false).hiddenRowIndexes.contains(rowIndex)) { getState().hiddenRowIndexes.remove(rowIndex); } for (int c = 0; c < getState().cols; c++) { Cell cell = row.getCell(c); if (cell == null) { styler.clearCellStyle(r, c); if (r <= lastNonBlankRow + n) { // There might be a pre-shift value for this cell in // client-side and should be overwritten cell = row.createCell(c); cellsToUpdate.add(cell); } } else { cellsToUpdate.add(cell); } } } } rowsMoved(firstAffectedRow, lastAffectedRow, n); for (Cell cell : cellsToUpdate) { styler.cellStyleUpdated(cell, false); markCellAsUpdated(cell, false); } styler.loadCustomBorderStylesToState(); updateMarkedCells(); // deleted and formula cells and style selectors updateRowAndColumnRangeCellData(firstRow, firstColumn, lastRow, lastColumn); // shifted area values updateMergedRegions(); CellReference selectedCellReference = selectionManager.getSelectedCellReference(); if (selectedCellReference != null) { if (selectedCellReference.getRow() >= firstAffectedRow && selectedCellReference.getRow() <= lastAffectedRow) { selectionManager.onSheetAddressChanged(selectedCellReference.formatAsString(), false); } } }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetHistoryManager.java
License:Open Source License
/** * Ensures that the correct sheet is active, as recorded in the given * Command.//from w w w . java 2s . c om * * @param command * Command to fetch the sheet from */ protected void makeSureCorrectSheetActive(Command command) { if (spreadsheet.getActiveSheetIndex() != command.getActiveSheetIndex()) { spreadsheet.setActiveSheetIndex(command.getActiveSheetIndex()); CellRangeAddress paintedCellRange = command.getPaintedCellRange(); CellReference selectedCellReference = command.getSelectedCellReference(); String initialSheetSelection = paintedCellRange != null ? paintedCellRange.formatAsString() : selectedCellReference != null ? selectedCellReference.formatAsString() : "A1"; spreadsheet.initialSheetSelection = initialSheetSelection; } }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetHistoryManager.java
License:Open Source License
/** * Applies the cell selection from the given Command. * //w w w . j a v a 2 s . c o m * @param command * Command to fetch the cell selection from. */ protected void changeSelection(Command command) { // if the sheet has changed, the selected cell can't be set if (!spreadsheet.isRerenderPending()) { CellReference selectedCellReference = command.getSelectedCellReference(); CellRangeAddress paintedCellRange = command.getPaintedCellRange(); if (selectedCellReference != null) { if (paintedCellRange == null) { spreadsheet.getCellSelectionManager() .onSheetAddressChanged(selectedCellReference.formatAsString(), false); } else { spreadsheet.getCellSelectionManager().handleCellRangeSelection(selectedCellReference, paintedCellRange, true); } } else { // the selected cell value might have changed, thus need to // make sure it gets updated to formula field spreadsheet.getCellSelectionManager().reSelectSelectedCell(); } } }