List of usage examples for org.apache.poi.ss.util CellReference CellReference
public CellReference(String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol)
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public void writeNamedRegion(DataFrame data, String name, boolean header) { Name cname = getName(name);/*from ww w. j a v a 2 s .co m*/ checkName(cname); // Get sheet where name is defined in Sheet sheet = workbook.getSheet(cname.getSheetName()); AreaReference aref = new AreaReference(cname.getRefersToFormula()); // Get upper left corner CellReference topLeft = aref.getFirstCell(); // Compute bottom right cell coordinates int bottomRightRow = Math.max(topLeft.getRow() + data.rows() - 1, topLeft.getRow()); if (header) ++bottomRightRow; int bottomRightCol = Math.max(topLeft.getCol() + data.columns() - 1, topLeft.getCol()); // Create bottom right cell reference CellReference bottomRight = new CellReference(sheet.getSheetName(), bottomRightRow, bottomRightCol, true, true); // Define named range area aref = new AreaReference(topLeft, bottomRight); // Redefine named range cname.setRefersToFormula(aref.formatAsString()); writeData(data, sheet, topLeft.getRow(), topLeft.getCol(), header); }
From source file:com.vaadin.addon.spreadsheet.PopupButton.java
License:Open Source License
/** * Gets the cell reference for the cell that contains this pop-up button. * /*from w w w . j av a2 s . co m*/ * @return Target cell reference */ public CellReference getCellReference() { return new CellReference(getState(false).sheet, getState(false).row - 1, getState(false).col - 1, false, false); }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetTable.java
License:Open Source License
/** * Initializes the pop-up buttons of this table. *///from w w w .j a v a 2 s. c o m protected void initPopupButtons() { if (sheet.equals(spreadsheet.getActiveSheet())) { for (int c = fullTableRegion.getFirstColumn(); c <= fullTableRegion.getLastColumn(); c++) { CellReference popupButtonCellReference = new CellReference(sheet.getSheetName(), fullTableRegion.getFirstRow(), c, true, true); PopupButton popupButton = new PopupButton(); popupButtons.put(popupButtonCellReference, popupButton); spreadsheet.setPopup(popupButtonCellReference, popupButton); } } }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetUtil.java
License:Open Source License
public static CellReference relativeToAbsolute(Spreadsheet sheet, CellReference cell) { String sheetName = sheet.getActiveSheet().getSheetName(); return new CellReference(sheetName, cell.getRow(), cell.getCol(), true, true); }
From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.util.ExcelUtils.java
License:Open Source License
/** * Returns a CellReference containing the cell's sheet name, as opposed to the standard * {@link CellReference#CellReference(Cell)} constructor. * @param cell/*from w ww . j a v a 2s . c o m*/ * Cell to create a CellReference from * @return The CellReference including the sheet name */ public static CellReference getFullCellReference(Cell cell) { return new CellReference(cell.getSheet().getSheetName(), cell.getRowIndex(), cell.getColumnIndex(), true, true); }
From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.util.ExcelUtils.java
License:Open Source License
/** * Returns a CellReference containing the given sheet's sheet name, * and row and column of the given cell reference * @param sheet//from w w w.j a v a 2 s . co m * @param cellReference * @return The CellReference including the sheet name */ public static CellReference getFullCellReference(Sheet sheet, CellReference cellReference) { return new CellReference(sheet.getSheetName(), cellReference.getRow(), cellReference.getCol(), true, true); }
From source file:org.jreserve.gui.poi.read.xls.XlsTableReader.java
License:Open Source License
private void cellRecord(int row, short column, Record record) throws Exception { try {// w w w .ja va 2 s. co m //Not on the good sheet, before first row/column if (!onReferencedSheet || firstRow > row || firstColumn > column) return; //The cell is empty, do not read it boolean isEmptyCell = isEmpty(record); if (isEmptyCell) { //If first cell is empty, whole table is empty if (firstRow == row && firstColumn == column) lastColumn = (short) (firstColumn - 1); return; } //Calculate the last column, based on the first row if (firstRow == row) { //If we missed a cell, do not read other columns if ((column - lastColumn) < 2) lastColumn = column; } //after the last column if (column > lastColumn) return; if (column == firstColumn) prevColumn = firstColumn; //missed a row, do not read further if ((row - prevRow) > 1 || (column - prevColumn) > 1) return; short sid = record.getSid(); switch (sid) { case NumberRecord.sid: factory.numberFound(row, column, ((NumberRecord) record).getValue()); break; case LabelSSTRecord.sid: if (sstRecord == null) throw new IllegalArgumentException("SSTRecord is null"); int stringId = ((LabelSSTRecord) record).getSSTIndex(); String str = sstRecord.getString(stringId).getString(); factory.stringFound(row, column, str); break; case LabelRecord.sid: factory.stringFound(row, column, ((LabelRecord) record).getValue()); break; case StringRecord.sid: factory.stringFound(row, column, ((StringRecord) record).getString()); break; case FormulaRecord.sid: FormulaRecord fr = (FormulaRecord) record; if (fr.hasCachedResultString()) { outputNextString = true; } else { factory.numberFound(row, column, fr.getValue()); } break; case BoolErrRecord.sid: BoolErrRecord ber = (BoolErrRecord) record; if (ber.isBoolean()) { factory.booleanFound(row, column, ber.getBooleanValue()); } else { factory.errorFound(row, column, getErrorMsg(ber.getErrorValue())); } break; default: break; } prevRow = row; prevColumn = column; } catch (Exception ex) { CellReference ref = new CellReference(sheetName, row, column, false, false); throw new ExcelReadException(ref, ex); } }
From source file:org.jreserve.gui.poi.read.xls.XlsTableReader.java
License:Open Source License
protected final String getCellReference(int row, short column) { return new CellReference(sheetName, row, column, false, false).formatAsString(); }
From source file:org.soulwing.jawb.poi.ApachePoiCellReference.java
License:Apache License
/** * {@inheritDoc}//from w w w .jav a 2s . com */ @Override public BoundCellReference applyBias(int sheetOffset, int rowOffset, int columnOffset, BoundWorkbook workbook) { Workbook wb = ((ApachePoiWorkbook) workbook).getDelegate(); int sheetIndex = delegate.getSheetName() != null ? wb.getSheetIndex(delegate.getSheetName()) : 0; sheetIndex += sheetOffset; String sheetName = wb.getSheetName(sheetIndex); int rowIndex = delegate.getRow(); if (!delegate.isRowAbsolute()) { rowIndex += rowOffset; } short columnIndex = delegate.getCol(); if (!delegate.isColAbsolute()) { columnIndex += columnOffset; } return new ApachePoiCellReference(new CellReference(sheetName, rowIndex, columnIndex, delegate.isRowAbsolute(), delegate.isColAbsolute())); }
From source file:poi.xslf.usermodel.PieChartDemo.java
License:Apache License
public static void main(String[] args) throws Exception { if (args.length < 2) { usage();// ww w . j a v a2 s . c om return; } BufferedReader modelReader = new BufferedReader(new FileReader(args[1])); String chartTitle = modelReader.readLine(); // first line is chart title XMLSlideShow pptx = new XMLSlideShow(new FileInputStream(args[0])); XSLFSlide slide = pptx.getSlides()[0]; // find chart in the slide XSLFChart chart = null; for (POIXMLDocumentPart part : slide.getRelations()) { if (part instanceof XSLFChart) { chart = (XSLFChart) part; break; } } if (chart == null) throw new IllegalStateException("chart not found in the template"); // embedded Excel workbook that holds the chart data POIXMLDocumentPart xlsPart = chart.getRelations().get(0); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); CTChart ctChart = chart.getCTChart(); CTPlotArea plotArea = ctChart.getPlotArea(); CTPieChart pieChart = plotArea.getPieChartArray(0); //Pie Chart Series CTPieSer ser = pieChart.getSerArray(0); // Series Text CTSerTx tx = ser.getTx(); tx.getStrRef().getStrCache().getPtArray(0).setV(chartTitle); sheet.createRow(0).createCell(1).setCellValue(chartTitle); String titleRef = new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString(); tx.getStrRef().setF(titleRef); // Category Axis Data CTAxDataSource cat = ser.getCat(); CTStrData strData = cat.getStrRef().getStrCache(); // Values CTNumDataSource val = ser.getVal(); CTNumData numData = val.getNumRef().getNumCache(); strData.setPtArray(null); // unset old axis text numData.setPtArray(null); // unset old values // set model int idx = 0; int rownum = 1; String ln; while ((ln = modelReader.readLine()) != null) { String[] vals = ln.split("\\s+"); CTNumVal numVal = numData.addNewPt(); numVal.setIdx(idx); numVal.setV(vals[1]); CTStrVal sVal = strData.addNewPt(); sVal.setIdx(idx); sVal.setV(vals[0]); idx++; XSSFRow row = sheet.createRow(rownum++); row.createCell(0).setCellValue(vals[0]); row.createCell(1).setCellValue(Double.valueOf(vals[1])); } numData.getPtCount().setVal(idx); strData.getPtCount().setVal(idx); String numDataRange = new CellRangeAddress(1, rownum - 1, 1, 1).formatAsString(sheet.getSheetName(), true); val.getNumRef().setF(numDataRange); String axisDataRange = new CellRangeAddress(1, rownum - 1, 0, 0).formatAsString(sheet.getSheetName(), true); cat.getStrRef().setF(axisDataRange); // updated the embedded workbook with the data OutputStream xlsOut = xlsPart.getPackagePart().getOutputStream(); wb.write(xlsOut); xlsOut.close(); // save the result FileOutputStream out = new FileOutputStream("pie-chart-demo-output.pptx"); pptx.write(out); out.close(); }