Example usage for org.apache.poi.ss.usermodel Cell getColumnIndex

List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getColumnIndex

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell getColumnIndex.

Prototype

int getColumnIndex();

Source Link

Document

Returns column index of this cell

Usage

From source file:de.enerko.reports2.engine.Report.java

License:Apache License

private Cell fill(final Workbook workbook, Cell tmp, final CellDefinition cellDefinition, boolean setType) {
    final String type = cellDefinition.getType();

    if (type.equalsIgnoreCase("string")) {
        if (setType)
            tmp.setCellType(Cell.CELL_TYPE_STRING);
        tmp.setCellValue(cellDefinition.value);
    } else if (type.equalsIgnoreCase("number")) {
        if (setType) {
            tmp.setCellType(Cell.CELL_TYPE_NUMERIC);
            tmp.setCellStyle(getFormat(workbook, tmp, type, cellDefinition.value));
        }//from w  w  w. j  av a2 s.c o  m
        try {
            tmp.setCellValue(Double.parseDouble(cellDefinition.value.split("@@")[0]));
        } catch (NumberFormatException e) {
            throw new RuntimeException(String.format("Could not parse value \"%s\" for numeric cell %dx%d!",
                    cellDefinition.value, tmp.getColumnIndex(), tmp.getRowIndex()));
        }
    } else if (type.equalsIgnoreCase("date") || type.equalsIgnoreCase("datetime")) {
        if (setType) {
            tmp.setCellType(Cell.CELL_TYPE_NUMERIC);
            tmp.setCellStyle(getFormat(workbook, tmp, type, cellDefinition.value));
        }
        try {
            tmp.setCellValue(getDateFormatSql(type).parse(cellDefinition.value));
        } catch (ParseException e) {
            throw new RuntimeException(
                    String.format("Could not parse value \"%s\" for date/datetime cell %dx%d!",
                            cellDefinition.value, tmp.getColumnIndex(), tmp.getRowIndex()));
        }
    } else if (type.equalsIgnoreCase("formula")) {
        if (setType)
            tmp.setCellType(Cell.CELL_TYPE_FORMULA);
        tmp.setCellFormula(cellDefinition.value);
    } else
        throw new RuntimeException("Invalid type " + type);
    return tmp;
}

From source file:de.fhg.fokus.odp.portal.uploaddata.service.Worker.java

/**
 * loop through all Cells and rows. Firstly, add correct keys to strings.
 * Secondly, parse corresponding value into correct json and add this
 * dataset to ckan via middleware./*from  w  w w. j  av a 2s.c  o m*/
 * 
 * @param args
 * @throws Exception
 * 
 * @return a String of dataset indices, which were not uploaded.
 */
public String readXlsx() {
    final StringBuilder errormessage = new StringBuilder("");
    final StringBuilder resourceStringBuilder = new StringBuilder("[{");
    final StringBuilder extrasStringBuilder = new StringBuilder("{");

    HashMap<String, String> map = new HashMap<String, String>();
    ArrayList<String> strings = new ArrayList<String>();
    XSSFWorkbook workBook = null;
    try {
        workBook = new XSSFWorkbook(uploadFolder + "file.xlsx");
    } catch (IOException e1) {
        e1.printStackTrace();
    }
    int counter = 0;
    XSSFSheet sheet = workBook.getSheetAt(0);
    for (Row row : sheet) {
        for (Cell cell : row) {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                String value = cell.getRichStringCellValue().getString();
                // first row, add value to strings
                if (counter == 0) {
                    if (!value.startsWith("resources:") && !value.startsWith("extras:")) {
                        map.put(value, null);
                    }

                    strings.add(value);
                    break;
                }
                // compute columnIndex for later use
                int columnIndex = cell.getColumnIndex();
                // compute parameter for later use in if-statements
                String parameter = strings.get(columnIndex);
                handleString(resourceStringBuilder, extrasStringBuilder, map, value, parameter);
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // is a date;
                    parameter = strings.get(cell.getColumnIndex());
                    handleDate(map, parameter, cell, extrasStringBuilder);
                } else {
                    // is a number;
                    parameter = strings.get(cell.getColumnIndex());
                    handleNumber(map, parameter, cell, extrasStringBuilder);
                }
                break;
            default:
                break;
            }
        }
        // finish extras and resources
        finishParseResource(resourceStringBuilder);
        finishParseExtras(extrasStringBuilder);

        Validator.checkTagAndGroupsForEmptyValues(map);
        Validator.setlicenseAndNameToLowerCase(map);

        // add resources and extras to map
        map.put("resources", resourceStringBuilder.toString());
        map.put("extras", extrasStringBuilder.toString());

        createDataSet(errormessage, gw, map, counter);

        ++counter;
        // reset resourceStringBuilder and extrasStringBuilder
        resetStringBuilder(resourceStringBuilder, extrasStringBuilder);

        // reset map
        map.clear();
    }

    if (errormessage.toString().equalsIgnoreCase("")) {
        // no errors
        return errormessage.toString();
    } else {
        // return list of dataset indices
        return errormessage.substring(0, errormessage.length() - 1);
    }
}

From source file:de.fme.alfresco.repo.web.scripts.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body./*from   w  w  w  .  ja va2s  . c om*/
 */
public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status,
        Map<String, Object> model) throws IOException {
    Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)");

    // Build up the details of the header
    List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req);
    String[] headings = new String[propertyDetails.size()];
    String[] descriptions = new String[propertyDetails.size()];
    boolean[] required = new boolean[propertyDetails.size()];
    for (int i = 0; i < headings.length; i++) {
        Pair<QName, Boolean> property = propertyDetails.get(i);
        if (property == null || property.getFirst() == null) {
            headings[i] = "";
            required[i] = false;
        } else {
            QName column = property.getFirst();
            required[i] = property.getSecond();

            // Ask the dictionary service nicely for the details
            PropertyDefinition pd = dictionaryService.getProperty(column);
            if (pd != null && pd.getTitle() != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle();
                descriptions[i] = pd.getDescription();
            } else {
                // Nothing friendly found, try to munge the raw qname into
                //  something we can show to a user...
                String raw = column.getLocalName();
                raw = raw.substring(0, 1).toUpperCase() + raw.substring(1);

                Matcher m = qnameMunger.matcher(raw);
                if (m.matches()) {
                    headings[i] = m.group(1) + " " + m.group(2);
                } else {
                    headings[i] = raw;
                }
            }
        }
    }

    // Build a list of just the properties
    List<QName> properties = new ArrayList<QName>(propertyDetails.size());
    for (Pair<QName, Boolean> p : propertyDetails) {
        QName qn = null;
        if (p != null) {
            qn = p.getFirst();
        }
        properties.add(qn);
    }

    // Output
    if ("csv".equals(format)) {
        StringWriter sw = new StringWriter();
        CSVPrinter csv = new CSVPrinter(sw, CSVStrategy.EXCEL_STRATEGY);
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else {
        Workbook wb;
        if ("xlsx".equals(format)) {
            wb = new XSSFWorkbook();
            // TODO Properties
        } else {
            wb = new HSSFWorkbook();
            // TODO Properties
        }

        // Add our header row
        Sheet sheet = wb.createSheet("Export");
        Row hr = sheet.createRow(0);
        try {
            sheet.createFreezePane(0, 1);
        } catch (IndexOutOfBoundsException e) {
            //https://issues.apache.org/bugzilla/show_bug.cgi?id=51431 & http://stackoverflow.com/questions/6469693/apache-poi-clearing-freeze-split-panes
        }
        Font fb = wb.createFont();
        fb.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font fi = wb.createFont();
        fi.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fi.setItalic(true);

        CellStyle csReq = wb.createCellStyle();
        csReq.setFont(fb);
        CellStyle csOpt = wb.createCellStyle();
        csOpt.setFont(fi);

        // Populate the header
        Drawing draw = null;
        for (int i = 0; i < headings.length; i++) {
            Cell c = hr.createCell(i);
            c.setCellValue(headings[i]);

            if (required[i]) {
                c.setCellStyle(csReq);
            } else {
                c.setCellStyle(csOpt);
            }

            if (headings[i].length() == 0) {
                sheet.setColumnWidth(i, 3 * 250);
            } else {
                sheet.setColumnWidth(i, 18 * 250);
            }

            if (descriptions[i] != null && descriptions[i].length() > 0) {
                // Add a description for it too
                if (draw == null) {
                    draw = sheet.createDrawingPatriarch();
                }
                ClientAnchor ca = wb.getCreationHelper().createClientAnchor();
                ca.setCol1(c.getColumnIndex());
                ca.setCol2(c.getColumnIndex() + 1);
                ca.setRow1(hr.getRowNum());
                ca.setRow2(hr.getRowNum() + 2);

                Comment cmt = draw.createCellComment(ca);
                cmt.setAuthor("");
                cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i]));
                cmt.setVisible(false);
                c.setCellComment(cmt);
            }
        }

        // Have the contents populated
        populateBody(resource, wb, sheet, properties);

        // Save it for the template
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        model.put(MODEL_EXCEL, baos.toByteArray());
    }
}

From source file:de.ingrid.iplug.excel.service.SheetsService.java

License:EUPL

/**
 * Create sheets.//from   w  w w.  j av  a2 s  . c o  m
 * 
 * @param inputStream
 * @return Created sheets.
 * @throws IOException
 */
public static Sheets createSheets(final InputStream inputStream) throws IOException {
    // sheets
    final Sheets sheets = new Sheets();
    // create workbook
    final Workbook workbook = new HSSFWorkbook(inputStream);
    final FormulaEvaluator eval = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
    for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
        final org.apache.poi.ss.usermodel.Sheet poiSheet = workbook.getSheetAt(sheetNum);
        // ingrid sheet
        final Sheet sheet = new Sheet();
        sheet.setSheetIndex(sheetNum);
        sheets.addSheet(sheet);
        final Values values = new Values();
        sheet.setValues(values);
        for (final org.apache.poi.ss.usermodel.Row poiRow : poiSheet) {
            boolean hasValues = false;
            final Map<Point, Comparable<? extends Object>> valuesInCell = new HashMap<Point, Comparable<? extends Object>>();
            for (final Cell poiCell : poiRow) {

                Comparable<? extends Object> value = null;
                switch (poiCell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    value = new Boolean(poiCell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(poiCell)) {
                        value = getFormattedDateString(poiCell);
                    } else {
                        value = new Double(poiCell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    value = poiCell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    value = calculateFormula(poiCell, eval);
                    break;
                default:
                    value = "";
                    break;
                }
                // trim strings
                if (value instanceof String) {
                    value = ((String) value).trim();
                }
                // only add if at least one value does exist in row
                if (!value.equals("")) {
                    hasValues = true;
                    // ingrid column
                    if (sheet.getColumn(poiCell.getColumnIndex()) == null) {
                        final Column column = new Column(poiCell.getColumnIndex());
                        sheet.addColumn(column);
                    }
                }

                // ingrid point and value
                final Point point = new Point(poiCell.getColumnIndex(), poiCell.getRowIndex());
                valuesInCell.put(point, value);
            }
            // ingrid row
            // ! only add if at least one value does exist
            if (hasValues) {
                final Row row = new Row(poiRow.getRowNum());
                sheet.addRow(row);
                for (final Point point : valuesInCell.keySet()) {
                    //
                    if (sheet.getColumn(point.getX()) != null) {
                        values.addValue(point, valuesInCell.get(point));
                    }
                }
            }
        }
    }

    return sheets;
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.export.template.TypeSheetGenerator.java

License:Open Source License

/**
 * @param featureExpression//from ww w . j  a  v  a 2  s.  c om
 * @param col
 */
private void addDurationColumnHeaders(FeatureExpression<?> featureExpression) {
    int col = sheetContext.getColumnCount();
    Cell runtimeFromCell = addColumnHeaderCells(featureExpression, "rp_from");
    Cell runtimeToCell = addColumnHeaderCells(featureExpression, "rp_to");

    CellRangeAddress merge = new CellRangeAddress(PRE_HEADER_ROW_NO, PRE_HEADER_ROW_NO, col, col + 1);
    sheetContext.getSheet().addMergedRegion(merge);
    runtimeFromCell.setCellValue("Runtime Period");

    String featureString = createFeatureString(featureExpression);
    Cell cell = featureNameRow.createCell(runtimeFromCell.getColumnIndex());
    cell.setCellValue(featureString);
    cell.setCellStyle(wbContext.getStyles().get(IteraExcelStyle.DATA_HIDDEN));

    cell = featureNameRow.createCell(runtimeToCell.getColumnIndex());
    cell.setCellValue(featureString);
    cell.setCellStyle(wbContext.getStyles().get(IteraExcelStyle.DATA_HIDDEN));

    cell = oppositeNameRow.createCell(runtimeFromCell.getColumnIndex());
    cell.setCellStyle(wbContext.getStyles().get(IteraExcelStyle.DATA_HIDDEN));
    cell = oppositeNameRow.createCell(runtimeToCell.getColumnIndex());
    cell.setCellStyle(wbContext.getStyles().get(IteraExcelStyle.DATA_HIDDEN));
}

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   ww w .  j  av  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.legacyExcel.exporter.ExportWorkbook.java

License:Open Source License

/**
 * Adds headers stored in <code>headers</code> to the current sheet. If required, a special width
 * for the corresponding column can be set by providing a value in <code>headersWidth</code> using
 * header as key.<br/>/*from w  w  w.ja v a2  s.c om*/
 * <b>IMPORTANT</b>: Headers are added in the order provided in <code>headers</code>.
 * 
 * @param headers
 *          headers to be added
 */
public void addHeaders(int sheetId, List<ExcelSheet.Header> headers) {
    Sheet sheet = getSheetById(sheetId);
    Drawing drawing = sheet.createDrawingPatriarch();
    CreationHelper factory = sheet.getWorkbook().getCreationHelper();
    Row row = sheet.createRow(this.getCurrentRowOfSheet(sheet, 3));
    int columnIndex = 0;
    for (ExcelSheet.Header header : headers) {
        int currColumnIndex = columnIndex;
        Cell cell = row.createCell(columnIndex);
        if (header.getDescription() != null) {
            ClientAnchor commentAnchor = factory.createClientAnchor();
            //Sizing the comment 1x3 cells
            commentAnchor.setCol1(cell.getColumnIndex());
            commentAnchor.setCol2(cell.getColumnIndex() + 1);
            commentAnchor.setRow1(row.getRowNum());
            commentAnchor.setRow2(row.getRowNum() + 3);

            Comment comment = drawing.createCellComment(commentAnchor);
            RichTextString str = factory.createRichTextString(header.getDescription());
            comment.setString(str);
            comment.setAuthor("");
            cell.setCellComment(comment);
        }

        setCellValue(cell, header.getLabel(), getHeaderTableStyle());
        Integer width = header.getWidth();
        if (width != null) {
            sheet.setColumnWidth(currColumnIndex, width.intValue());
        }
        columnIndex++;
    }

    LOGGER.debug("Added headers.");
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ExcelImportUtilities.java

License:Open Source License

/**
 * Returns a {@link Cell}'s coordinates/*from   w  w  w  . j  a  v  a  2 s .  c  o  m*/
 * @param cell
 *          the given {@link Cell}
 * @return String representing the cell's coordinates
 */
public static String getCellRef(Cell cell) {
    if (cell == null) {
        return "undef";
    }
    CellReference cellref = new CellReference(cell.getRowIndex(), cell.getColumnIndex());
    return cellref.formatAsString();
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ExcelImportUtilities.java

License:Open Source License

/**
 * Returns a {@link Cell}'s row number/*from   w  ww .j a v  a 2  s  .c o  m*/
 * @param cell
 *          the given {@link Cell}
 * @return The cell's row number as String
 */
public static String getCellRow(Cell cell) {
    if (cell == null) {
        return "undef";
    }
    CellReference cellref = new CellReference(cell.getRowIndex(), cell.getColumnIndex());
    return String.valueOf(cellref.getRow() + 1);
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.sheets.model.InformationSystemInterfaceSheetImporter.java

License:Open Source License

@Override
protected boolean isValidRow(Map<String, Cell> buildingBlockRowData) {
    String releaseA = getCellByKey(getConstant(Constants.BB_INTERFACE_INFORMATIONSYSTEMRELEASE_A),
            buildingBlockRowData);/*w  w  w.jav a 2s.  c  om*/
    String releaseB = getCellByKey(getConstant(Constants.BB_INTERFACE_INFORMATIONSYSTEMRELEASE_B),
            buildingBlockRowData);

    Cell releaseANameCell = buildingBlockRowData
            .get(getConstant(Constants.BB_INTERFACE_INFORMATIONSYSTEMRELEASE_A));
    String releaseANameCellCoords = ExcelImportUtilities.getCellRef(releaseANameCell);

    Cell releaseBNameCell = buildingBlockRowData
            .get(getConstant(Constants.BB_INTERFACE_INFORMATIONSYSTEMRELEASE_B));
    String releaseBNameCellCoords = ExcelImportUtilities.getCellRef(releaseBNameCell);

    if (releaseANameCell == null && releaseBNameCell != null) {
        CellReference cellRelA = new CellReference(releaseBNameCell.getRowIndex(),
                releaseBNameCell.getColumnIndex() - 1);
        releaseANameCellCoords = cellRelA.formatAsString();
    }

    if (releaseBNameCell == null && releaseANameCell != null) {
        CellReference cellRelB = new CellReference(releaseANameCell.getRowIndex(),
                releaseANameCell.getColumnIndex() + 1);
        releaseBNameCellCoords = cellRelB.formatAsString();
    }

    if (!isNameSet(releaseA)) {
        getProcessingLog().warn("Release A in cell [{0}] is empty: not importing", releaseANameCellCoords);
        return false;
    }
    if (!isNameSet(releaseB)) {
        getProcessingLog().warn("Release B in cell [{0}] is empty: not importing", releaseBNameCellCoords);
        return false;
    }
    return true;
}