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:org.joeffice.spreadsheet.actions.SetBordersAction.java

License:Apache License

public void setBorder(JTable currentTable, short thickness, short color) {
    SheetTableModel tableModel = (SheetTableModel) currentTable.getModel();
    List<Cell> selectedCells = CellUtils.getSelectedCells(currentTable, true);
    for (Cell cell : selectedCells) {
        Workbook workbook = cell.getSheet().getWorkbook();
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.BORDER_TOP, thickness);
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.TOP_BORDER_COLOR, color);
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.BORDER_LEFT, thickness);
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.LEFT_BORDER_COLOR, color);
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.BORDER_BOTTOM, thickness);
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.BOTTOM_BORDER_COLOR, color);
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.BORDER_RIGHT, thickness);
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.RIGHT_BORDER_COLOR, color);
        tableModel.fireTableCellUpdated(cell.getRowIndex(), cell.getColumnIndex());
    }//from   w w  w.ja v  a 2  s.c  o  m
}

From source file:org.joeffice.spreadsheet.TableStyleable.java

License:Apache License

@Override
public void setFontAttributes(AttributedString attributes) {
    SpreadsheetTopComponent currentTopComponent = OfficeTopComponent
            .getSelectedComponent(SpreadsheetTopComponent.class);
    if (currentTopComponent != null) {
        JTable table = currentTopComponent.getSelectedTable();

        List<Cell> selectedCells = CellUtils.getSelectedCells(table);
        for (Cell cell : selectedCells) {
            AttributedCharacterIterator attributesIterator = attributes.getIterator();
            for (Attribute attribute : attributesIterator.getAllAttributeKeys()) {
                Object value = attributesIterator.getAttribute(attribute);
                addAttribute(attribute, value, cell);
                ((AbstractTableModel) table.getModel()).fireTableCellUpdated(cell.getRowIndex(),
                        cell.getColumnIndex());
            }/*from  w  ww. ja  va2s .  c  o  m*/
        }
    }
}

From source file:org.mifos.dmt.excel.CreateExcel.java

License:Open Source License

private void editConfigSheet(Sheet configSheet) throws DMTException {
    ConfigSheet cfSheet = new ConfigSheet();
    Method[] methods = ConfigSheet.class.getDeclaredMethods();
    for (Method method : methods) {

        if (Modifier.isPublic(method.getModifiers()))
            try {
                method.invoke(cfSheet);//w ww. j  av a2 s .c  o m
            } catch (Exception e) {
                logger.error(e.getMessage());
                throw new DMTException("error while editing config sheet " + e.getMessage());
            }
    }
    Row configHeader = configSheet.getRow(1);
    Iterator<Cell> x = configHeader.cellIterator();
    while (x.hasNext()) {
        Cell cell = x.next();
        String label = cell.getStringCellValue();
        int index = cell.getColumnIndex();
        populateColumn(configSheet, label, index, cfSheet.excelValues);
    }

}

From source file:org.natica.expense.ExpenseUtility.java

public List<Expense> parseExcel(File file) throws IOException, ExpenseExcelFormatException {
    List<Expense> expenses = new ArrayList<Expense>();
    FileInputStream fis;// ww  w .ja va 2s  . c  o  m

    fis = new FileInputStream(file);
    XSSFWorkbook wb;

    wb = new XSSFWorkbook(fis);
    XSSFSheet sh = wb.getSheetAt(0);

    for (Row row : sh) {
        if (row.getRowNum() == 0) {
            if (!checkHeaderRow(sh.getRow(0)))
                throw new ExpenseExcelFormatException("Excel Balk simleri Hataldr.");
            else
                continue;
        }
        Expense e = new Expense();
        for (Cell cell : row) {
            if (cell.getColumnIndex() == 0) {
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    if (!HSSFDateUtil.isCellDateFormatted(cell)) {
                        throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum()
                                + " Stun:" + cell.getColumnIndex() + " Hata Nedeni: Veri tipi tarih deil");
                    }
                } else {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi tarih deil");
                }
                e.setExpenseEntryDate(cell.getDateCellValue());
            } else if (cell.getColumnIndex() == 1) {
                if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil");
                }
                e.setProjectName(cell.getStringCellValue());
            } else if (cell.getColumnIndex() == 2) {
                if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil");
                }
                e.setExpenseName(cell.getStringCellValue());
            } else if (cell.getColumnIndex() == 3) {
                if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil");
                }
                e.setPaymentMethod(cell.getStringCellValue());
            } else if (cell.getColumnIndex() == 4) {
                if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil");
                }
                e.setCurrency(cell.getStringCellValue());
            } else if (cell.getColumnIndex() == 5) {
                if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi nmerik deil");
                }
                e.setNetAmount(BigDecimal.valueOf(cell.getNumericCellValue()));
            } else if (cell.getColumnIndex() == 6) {
                if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil");
                }
                e.setRestaurant(cell.getStringCellValue());
            } else if (cell.getColumnIndex() == 7) {
                if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi nmerik deil");
                }
                e.setDocumentNumber(Integer.valueOf((int) cell.getNumericCellValue()));
            }
        }
        expenses.add(e);
    }

    if (wb != null)
        wb.close();

    if (fis != null)
        fis.close();

    return expenses;
}

From source file:org.netxilia.impexp.impl.ExcelImportService.java

License:Open Source License

@Override
public List<SheetFullName> importSheets(INetxiliaSystem workbookProcessor, WorkbookId workbookName,
        InputStream is, IProcessingConsole console) throws ImportException {
    List<SheetFullName> sheetNames = new ArrayList<SheetFullName>();
    try {//from  ww w  .j av  a  2 s  .  com
        log.info("Starting import:" + workbookName);
        Workbook poiWorkbook = new HSSFWorkbook(is);
        IWorkbook nxWorkbook = workbookProcessor.getWorkbook(workbookName);
        log.info("Read POI");

        NetxiliaStyleResolver styleResolver = new NetxiliaStyleResolver(
                styleService.getStyleDefinitions(workbookName));

        HSSFPalette palette = ((HSSFWorkbook) poiWorkbook).getCustomPalette();

        for (int s = 0; s < poiWorkbook.getNumberOfSheets(); ++s) {
            Sheet poiSheet = poiWorkbook.getSheetAt(s);

            SheetFullName sheetName = new SheetFullName(workbookName,
                    getNextFreeSheetName(nxWorkbook, poiSheet.getSheetName()));
            ISheet nxSheet = null;
            BlockCellCommandBuilder cellCommandBuilder = new BlockCellCommandBuilder();
            try {
                List<CellReference> refreshCells = new ArrayList<CellReference>();

                for (Row poiRow : poiSheet) {
                    if (poiRow.getRowNum() % 100 == 0) {
                        log.info("importing row #" + poiRow.getRowNum());
                    }
                    for (Cell poiCell : poiRow) {
                        if (nxSheet == null) {
                            // lazy creation
                            while (true) {
                                try {
                                    nxSheet = nxWorkbook.addNewSheet(sheetName.getSheetName(),
                                            SheetType.normal);
                                    nxSheet.setRefreshEnabled(false);
                                    break;
                                } catch (AlreadyExistsException e) {
                                    // may happen is simultaneous imports take place
                                    sheetName = new SheetFullName(workbookName,
                                            getNextFreeSheetName(nxWorkbook, poiSheet.getSheetName()));
                                }
                            }
                        }

                        CellReference ref = new CellReference(sheetName.getSheetName(), poiRow.getRowNum(),
                                poiCell.getColumnIndex());
                        try {
                            ICellCommand cmd = copyCell(poiCell, ref, palette, styleResolver);
                            if (cmd != null) {
                                cellCommandBuilder.command(cmd);
                            }
                            if (poiCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                                refreshCells.add(ref);
                            }

                        } catch (Exception e) {
                            if (console != null) {
                                console.println("Could import cell " + ref + ":" + poiCell + ":" + e);
                            }
                            log.error("Could import cell " + ref + ":" + poiCell + ":" + e, e);
                        }
                    }

                    if (poiRow.getRowNum() % 100 == 0 && !cellCommandBuilder.isEmpty()) {
                        nxSheet.sendCommandNoUndo(cellCommandBuilder.build());
                        cellCommandBuilder = new BlockCellCommandBuilder();
                    }
                }

                if (nxSheet == null) {
                    // empty sheet
                    continue;
                }
                if (!cellCommandBuilder.isEmpty()) {
                    nxSheet.sendCommandNoUndo(cellCommandBuilder.build());
                }
                // add the columns after as is not very clear how to get the number of cols in poi
                for (int c = 0; c < nxSheet.getDimensions().getNonBlocking().getColumnCount(); ++c) {
                    int width = 50;
                    try {
                        width = PoiUtils.widthUnits2Pixel(poiSheet.getColumnWidth(c));
                        nxSheet.sendCommand(ColumnCommands.width(Range.range(c), width));
                    } catch (NullPointerException ex) {
                        // ignore it
                        // NPE in at org.apache.poi.hssf.model.Sheet.getColumnWidth(Sheet.java:998)
                        // defaultColumnWidth can be null !?
                    }

                    CellStyle poiStyle = poiSheet.getColumnStyle(c);
                    if (poiStyle == null) {
                        continue;
                    }
                    Styles styles = PoiUtils.poiStyle2Netxilia(poiStyle,
                            poiSheet.getWorkbook().getFontAt(poiStyle.getFontIndex()), palette, styleResolver);
                    if (styles != null) {
                        nxSheet.sendCommand(ColumnCommands.styles(Range.range(c), styles));
                    }
                }

                // merge
                List<AreaReference> spans = new ArrayList<AreaReference>(poiSheet.getNumMergedRegions());
                for (int i = 0; i < poiSheet.getNumMergedRegions(); ++i) {
                    CellRangeAddress poiSpan = poiSheet.getMergedRegion(i);
                    spans.add(new AreaReference(sheetName.getSheetName(), poiSpan.getFirstRow(),
                            poiSpan.getFirstColumn(), poiSpan.getLastRow(), poiSpan.getLastColumn()));
                }
                nxSheet.sendCommand(SheetCommands.spans(spans));

                // refresh all the cells now
                nxSheet.setRefreshEnabled(true);
                nxSheet.sendCommandNoUndo(moreCellCommands.refresh(refreshCells, false));

            } finally {
                if (nxSheet != null) {
                    sheetNames.add(sheetName);
                }
            }
        }
    } catch (IOException e) {
        throw new ImportException(null, "Cannot open workbook:" + e, e);
    } catch (StorageException e) {
        throw new ImportException(null, "Error storing sheet:" + e, e);
    } catch (NotFoundException e) {
        throw new ImportException(null, "Cannot find workbook:" + e, e);
    } catch (NetxiliaResourceException e) {
        throw new ImportException(null, e.getMessage(), e);
    } catch (NetxiliaBusinessException e) {
        throw new ImportException(null, e.getMessage(), e);
    }

    return sheetNames;
}

From source file:org.nuclos.client.nuclet.generator.content.AbstractNucletContentGenerator.java

License:Open Source License

protected void error(Cell cell, Exception ex) {
    error(String.format("[Row %s, Column %s], %s", cell == null ? "null" : cell.getRowIndex() + 1,
            cell == null ? "null" : cell.getColumnIndex() + 1, ex.getMessage()));
}

From source file:org.obiba.onyx.core.etl.participant.impl.ParticipantReader.java

License:Open Source License

@SuppressWarnings("unchecked")
private void initAttributeNameToColumnIndexMap(ExecutionContext context, Row headerRow) {
    if (headerRow == null) {
        AppointmentUpdateLog.addErrorLog(context,
                new AppointmentUpdateLog(new Date(), AppointmentUpdateLog.Level.ERROR,
                        "Abort updating appointments: Reading file error: Null headerRow"));
        throw new IllegalArgumentException("Null headerRow");
    }// www  . j  ava 2  s.  c om

    attributeNameToColumnIndexMap = new CaseInsensitiveMap();

    Iterator<Cell> cellIter = headerRow.cellIterator();

    while (cellIter.hasNext()) {
        Cell cell = cellIter.next();

        if (cell != null) {
            if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                AppointmentUpdateLog.addErrorLog(context, new AppointmentUpdateLog(new Date(),
                        AppointmentUpdateLog.Level.ERROR,
                        "Abort updating appointments: Reading file error: Header row contains unexpected cell type"));
                throw new IllegalArgumentException("Header row contains unexpected cell type");
            }

            String columnName = cell.getRichStringCellValue().getString();

            if (columnName != null) {
                String attributeName = columnNameToAttributeNameMap.get(columnName);

                if (attributeName != null) {
                    if (!attributeNameToColumnIndexMap.containsKey(attributeName)) {
                        attributeNameToColumnIndexMap.put(attributeName, cell.getColumnIndex());
                    } else {
                        AppointmentUpdateLog.addErrorLog(context, new AppointmentUpdateLog(new Date(),
                                AppointmentUpdateLog.Level.ERROR,
                                "Abort updating appointments: Reading file error: Duplicate column for field: "
                                        + attributeName));
                        throw new IllegalArgumentException("Duplicate column for field: " + attributeName);
                    }
                }
            }
        }
    }

    log.info("attributeNameToColumnIndexMap: {}", attributeNameToColumnIndexMap);

    checkColumnsForMandatoryAttributesPresent();
}

From source file:org.opencities.berlin.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 a  v a2s . co  m*/
 * 
 * @param args
 * @throws Exception
 */
@SuppressWarnings("rawtypes")
public String readXlsx() {
    String errormessage = "";

    CKANGateway gw = new CKANGateway(ckan, key);
    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) {
        // TODO Auto-generated catch block
        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;
                }
                if (strings.get(cell.getColumnIndex()).equalsIgnoreCase("tags")
                        || strings.get(cell.getColumnIndex()).equalsIgnoreCase("groups")) {
                    String[] tmp = value.split(",");
                    String out = buildString(tmp);
                    map.put(strings.get(cell.getColumnIndex()), out);
                } else if (strings.get(cell.getColumnIndex()).startsWith("resources:")) {
                    String[] tmp = strings.get(cell.getColumnIndex()).split(":");
                    parseResource(tmp[1], value);
                } else if (strings.get(cell.getColumnIndex()).startsWith("extras:")) {
                    String[] tmp = strings.get(cell.getColumnIndex()).split(":");
                    parseExtras(tmp[1], value);
                } else {
                    map.put(strings.get(cell.getColumnIndex()), "\"" + value + "\"");
                }
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // is a date;
                    map = handleDate(map, strings, cell);
                } else {
                    // is a number;
                    map = handleNumber(map, strings, cell);
                }
                break;
            default:
                break;
            }
        }
        // finish extras and resources
        finishParseResource();
        finishParseExtras();

        map = checkEmptyValues(map);
        map = toLowerCase(map);
        // add resources and extras to map
        map.put("resources", resourceString);
        map.put("extras", extrasString);
        if (counter >= 1) {
            // add dataset to CKAN via middleware
            HashMap<String, HashMap> out = gw.createMetaDataSet(map);
            if (out == null)
                errormessage += String.valueOf(counter) + ",";
        }
        ++counter;
        resourceString = resetResourceString();
        extrasString = resetExtrasString();
    }

    if (errormessage.equalsIgnoreCase(""))
        return errormessage;
    else
        return errormessage.substring(0, errormessage.length() - 1);
}

From source file:org.opencities.berlin.uploaddata.service.Worker.java

private HashMap<String, String> handleNumber(HashMap<String, String> map, ArrayList<String> strings,
        Cell cell) {
    String val;
    val = String.valueOf(cell.getNumericCellValue());

    if (strings.get(cell.getColumnIndex()).startsWith("extras:")) {
        String[] tmp = strings.get(cell.getColumnIndex()).split(":");
        parseExtras(tmp[1], val);
    } else/*from ww  w  .j  a  va 2  s .  com*/
        map.put(strings.get(cell.getColumnIndex()), "\"" + val + "\"");

    return map;
}

From source file:org.opencities.berlin.uploaddata.service.Worker.java

private HashMap<String, String> handleDate(HashMap<String, String> map, ArrayList<String> strings, Cell cell) {
    String val;
    SimpleDateFormat form = new SimpleDateFormat("yyyy-MM-dd");
    val = form.format(cell.getDateCellValue());

    if (strings.get(cell.getColumnIndex()).startsWith("extras:")) {
        String[] tmp = strings.get(cell.getColumnIndex()).split(":");
        parseExtras(tmp[1], val);
    } else/*from  w  w  w .ja v  a  2 s  . c  o m*/
        map.put(strings.get(cell.getColumnIndex()), "\"" + val + "\"");

    return map;
}