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:com.bizosys.dataservice.dao.ReadXLS.java

License:Apache License

@Override
protected List<String> populate() throws SQLException {

    checkCondition();/*w  ww  .j  av a  2  s .co m*/

    Workbook workbook = getWorkbook();
    Sheet sheet = workbook.createSheet();

    ResultSetMetaData md = rs.getMetaData();
    int totalCol = md.getColumnCount();
    String[] cols = createLabels(md, totalCol);

    try {

        if (null != templateFile) {
            File templateFileObject = new File(templateFile);
            if (templateFileObject.exists()) {
                Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
                Sheet templatesheet = templateWorkbook.getSheetAt(0);
                Iterator<Row> rowIterator = templatesheet.iterator();

                while (rowIterator.hasNext()) {
                    Row templateRow = rowIterator.next();
                    Row row = sheet.createRow(startRowIndex++);

                    Iterator<Cell> cellIterator = templateRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell templateCell = cellIterator.next();
                        Cell cell = row.createCell(templateCell.getColumnIndex());
                        cell.setCellType(templateCell.getCellType());
                        switch (templateCell.getCellType()) {
                        case Cell.CELL_TYPE_BLANK:
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(templateCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            cell.setCellValue(templateCell.getErrorCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(templateCell.getCellFormula());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            cell.setCellValue(templateCell.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(templateCell.getStringCellValue());
                            break;
                        }
                    }
                }
            } else {
                System.err.println("Can not read " + templateFileObject.getAbsolutePath());
            }

        }

        while (this.rs.next()) {
            createRecord(totalCol, cols, sheet);
        }
        workbook.write(out);
    } catch (IOException ex) {
        throw new SQLException(ex);
    }
    return null;
}

From source file:com.bizosys.dataservice.dao.WriteToXls.java

License:Apache License

public void write(List<Object[]> records) throws Exception {
    Workbook workbook = getWorkbook();//  www .  j a  v  a  2  s  .co m
    Sheet sheet = workbook.createSheet();

    if (null != templateFile) {
        File templateFileObject = new File(templateFile);
        if (templateFileObject.exists()) {
            Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
            Sheet templatesheet = templateWorkbook.getSheetAt(0);
            Iterator<Row> rowIterator = templatesheet.iterator();

            while (rowIterator.hasNext()) {
                Row templateRow = rowIterator.next();
                Row row = sheet.createRow(startRowIndex++);

                Iterator<Cell> cellIterator = templateRow.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell templateCell = cellIterator.next();
                    Cell cell = row.createCell(templateCell.getColumnIndex());
                    cell.setCellType(templateCell.getCellType());
                    switch (templateCell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        cell.setCellValue(templateCell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cell.setCellValue(templateCell.getErrorCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cell.setCellValue(templateCell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        cell.setCellValue(templateCell.getNumericCellValue());
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cell.setCellValue(templateCell.getStringCellValue());
                        break;
                    }
                }
            }
        } else {
            System.err.println("Can not read " + templateFileObject.getAbsolutePath());
        }
    }

    for (Object[] cols : records) {
        createRecord(cols, sheet);
    }
    workbook.write(out);

}

From source file:com.consensus.qa.framework.ExcelOperations.java

@SuppressWarnings("unused")
public AccountDetails GetAccountDetails(FileNames fileName, SheetName worksheetName) throws IOException {
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    XSSFSheet workSheet = GetSheetFromWorkBook(workBook, worksheetName.toString());
    AccountDetails accountDetails = new AccountDetails();

    try {/*ww w.ja  v a  2s . c  o m*/
        int mtnIndex = -1;
        int passwordIndex = -1;
        int ssnIndex = -1;
        int statusCol = -1;
        Row row = workSheet.getRow(0);
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (String.valueOf(cell.getStringCellValue()).contains("MTN")) {
                mtnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("Password")) {
                passwordIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("SSN")) {
                ssnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains(Status.STATUS.toString())) {
                statusCol = cell.getColumnIndex();
            }
            if (mtnIndex != -1 && passwordIndex != -1 && ssnIndex != -1 && statusCol != -1) {
                break;
            }
        }
        if (statusCol == -1) {
            statusCol = (workSheet.getRow(0).getPhysicalNumberOfCells());
            Cell cell = workSheet.getRow(0).createCell(statusCol);
            cell.setCellValue(Status.STATUS.toString());
        }
        for (int i = 1; i < workSheet.getPhysicalNumberOfRows(); i++) {
            Cell cell = null;
            if (workSheet.getRow(i).getCell(statusCol) == null
                    || (workSheet.getRow(i).getCell(statusCol).getCellType() == Cell.CELL_TYPE_BLANK)) {
                cell = workSheet.getRow(i).createCell(statusCol);
                cell.setCellValue(Status.UNUSED.toString());
            }
            cell = workSheet.getRow(i).getCell(statusCol);
            if (cell.getStringCellValue().toString().equals(Status.UNUSED.toString())) {
                accountDetails.MTN = String
                        .valueOf(workSheet.getRow(i).getCell(mtnIndex).getNumericCellValue());
                accountDetails.Password = workSheet.getRow(i).getCell(passwordIndex).getStringCellValue();
                accountDetails.SSN = String
                        .valueOf(workSheet.getRow(i).getCell(ssnIndex).getNumericCellValue());

                cell.setCellValue(Status.INUSE.toString());
                break;
            }
        }
    }

    catch (Exception ex) {
        ex.printStackTrace();
    }

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    if (accountDetails == null)
        Log.error("FAILED To get account details; one among MTN/Password/SSN is blank");
    return accountDetails;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

@SuppressWarnings("unused")
public NPANXX GetNumberPortData(FileNames fileName, SheetName workSheetName) throws IOException {
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    XSSFSheet workSheet = GetSheetFromWorkBook(workBook, workSheetName.toString());
    NPANXX npaNXX = new NPANXX();

    try {/*w  ww .  j  a  v  a  2 s . c o m*/
        int mtnIndex = -1;
        int passwordIndex = -1;
        int ssnIndex = -1;
        int statusCol = -1;
        Row row = workSheet.getRow(0);
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (String.valueOf(cell.getStringCellValue()).contains("NGP")) {
                mtnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("Location")) {
                passwordIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("NPANXX")) {
                ssnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains(Status.STATUS.toString())) {
                statusCol = cell.getColumnIndex();
            }
            if (mtnIndex != -1 && passwordIndex != -1 && ssnIndex != -1 && statusCol != -1) {
                break;
            }
        }
        if (statusCol == -1) {
            statusCol = (workSheet.getRow(0).getPhysicalNumberOfCells());
            Cell cell = workSheet.getRow(0).createCell(statusCol);
            cell.setCellValue(Status.STATUS.toString());
        }
        for (int i = 1; i < workSheet.getPhysicalNumberOfRows(); i++) {
            Cell cell = null;
            if (workSheet.getRow(i).getCell(statusCol) == null
                    || (workSheet.getRow(i).getCell(statusCol).getCellType() == Cell.CELL_TYPE_BLANK)) {
                cell = workSheet.getRow(i).createCell(statusCol);
                cell.setCellValue(Status.UNUSED.toString());
            }
            cell = workSheet.getRow(i).getCell(statusCol);
            if (cell.getStringCellValue().toString().equals(Status.UNUSED.toString())) {
                npaNXX.NGP = String.valueOf(workSheet.getRow(i).getCell(mtnIndex).getNumericCellValue());
                npaNXX.Location = workSheet.getRow(i).getCell(passwordIndex).getStringCellValue();
                npaNXX.NPANXX = String.valueOf(workSheet.getRow(i).getCell(ssnIndex).getNumericCellValue());

                cell.setCellValue(Status.INUSE.toString());
                break;
            }
        }
    }

    catch (Exception ex) {
        ex.printStackTrace();
    }

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    if (npaNXX == null)
        Log.error("FAILED To get account details; one among MTN/Password/SSN is blank");
    return npaNXX;
}

From source file:com.cseur.utils.ExcelReader.java

public ArrayList<ArrayList<String>> readToMap() throws IOException {

    FileInputStream file = new FileInputStream(xlsFile);
    //Get the workbook instance for XLS file 
    HSSFWorkbook workbook = new HSSFWorkbook(file);

    //Get first sheet from the workbook
    HSSFSheet sheet = workbook.getSheetAt(0);

    //get pol pod via1 via2 via3 via4 index in column and saved to array
    ArrayList<Integer> portIndexs = new ArrayList(Arrays.asList(null, null, null, null, null, null, null));
    boolean isTableHeader = true;
    //Iterate through each rows one by one
    Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) {
        //read file, find the columnindex for required order, and then insert the value to valueArray.
        Row row = rowIterator.next();//from  w  w w .j  ava  2s. c  om
        //            //escape empty lines
        Iterator<Cell> cellIterator = row.cellIterator();
        if (isTableHeader) {
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                //set absolute columnIndex in the order of POL, POD, VIA1.2.l.3.4
                switch (cell.getStringCellValue().trim().toUpperCase()) {
                case "POL":
                    portIndexs.set(0, cell.getColumnIndex());
                    break;
                case "POD":
                    portIndexs.set(1, cell.getColumnIndex());
                    break;
                case "VIA1":
                    portIndexs.set(2, cell.getColumnIndex());
                    break;
                case "VIA2":
                    portIndexs.set(3, cell.getColumnIndex());
                    break;
                case "VIA3":
                    portIndexs.set(4, cell.getColumnIndex());
                    break;
                case "VIA4":
                    portIndexs.set(5, cell.getColumnIndex());
                    break;
                //                    case "VIA5":
                //                            portIndexs.add(cell.getColumnIndex());
                //                        break;
                //                    case "VIA6":
                //                            portIndexs.add(cell.getColumnIndex());
                //                        break;
                //                    case "VIA7":
                //                            portIndexs.add(cell.getColumnIndex());
                //                        break;
                //                    case "VIA8":
                //                            portIndexs.add(cell.getColumnIndex());
                //                        break;
                //                    case "VIA9":
                //                            portIndexs.add(cell.getColumnIndex());
                //                        break;
                //                    case "VIA0":
                //                            portIndexs.add(cell.getColumnIndex());
                //                        break;
                }
                //                    portIndexs.trimToSize();
            }
        }
        if (!isTableHeader) {
            ArrayList<String> valueArrayList = new ArrayList<>();
            for (int i = 0; portIndexs.get(i) != null; i++) {
                if (row.getCell(portIndexs.get(i)) != null
                        && !row.getCell(portIndexs.get(i)).getStringCellValue().isEmpty()) {
                    valueArrayList
                            .add(row.getCell(portIndexs.get(i)).getStringCellValue().trim().toUpperCase());
                }
            }
            System.out.println(valueArrayList.size());
            valueArrayList_X.add(valueArrayList);
            //reference added to arraylist. clear the valueArrayList remove the values in valueArrayList_X Too!!
            //valueArrayList.clear();
        }
        isTableHeader = false;//add contents to vector.
    }
    file.close();
    return valueArrayList_X;
}

From source file:com.dataart.spreadsheetanalytics.engine.DependencyExtractors.java

License:Apache License

/**
 * Extracts {@link IDataModel} from {@link Workbook} at given {@link ICellAddress}.
 * Useful for formula. If given {@link ICellAddress} contains formula it can be parsed.
 * Based on this parsed information a new {@link IDataModel} might be created.
 *//*from   w  w w .  j  a  v  a  2s . c  om*/
static IDataModel toDataModel(final Workbook book, final ICellAddress address) {
    if (book == null || address == null) {
        return null;
    }
    if (address instanceof A1RangeAddress) {
        throw new CalculationEngineException(
                "A1RangeAddress is not supported, only one cell can be converted to DataModel.");
    }

    Sheet s = book.getSheetAt(0); /* TODO: only one sheet is supported */
    Row r = s.getRow(address.a1Address().row());
    if (r == null) {
        return null;
    }
    Cell c = r.getCell(address.a1Address().column());
    if (c == null || CELL_TYPE_FORMULA != c.getCellType()) {
        return null;
    }

    return createDataModelFromCell(s, create((XSSFWorkbook) book),
            fromRowColumn(c.getRowIndex(), c.getColumnIndex()));
}

From source file:com.dataart.spreadsheetanalytics.engine.DependencyExtractors.java

License:Apache License

/**
 * Extracts {@link IDataModel} from {@link Workbook} for given function name.
 * Useful for formula with particular functions. Does scan IDataModel for exact formula use and create new 
 * {@link IDataModel} for every formula found.
 *//* w ww.j a va  2  s . co  m*/
static List<IDataModel> toDataModels(final Workbook book, final String function) {
    if (book == null || function == null) {
        return emptyList();
    }
    List<IDataModel> list = new LinkedList<>();

    final FormulaParsingWorkbook parsingBook = create((XSSFWorkbook) book);

    Sheet s = book.getSheetAt(0); /* TODO: only one sheet is supported */
    for (Row r : s) {
        for (Cell c : r) {
            if (c == null || CELL_TYPE_FORMULA != c.getCellType()) {
                continue;
            }

            try {
                if (ConverterUtils.isFunctionInFormula(c.getCellFormula(), function)) {
                    list.add(createDataModelFromCell(s, parsingBook,
                            fromRowColumn(c.getRowIndex(), c.getColumnIndex())));
                }
            } catch (FormulaParseException e) {
                log.warn("Warning while parsing excel formula. Probably this is OK.", e);
            }
        }
    }

    return list;
}

From source file:com.dataart.spreadsheetanalytics.engine.DependencyExtractors.java

License:Apache License

/**
 * Does the same logic as {@link #toDataModels(Workbook, String)}, but for each new {@link IDataModel} created
 * also created an instance of given {@link FunctionMeta}.
 *//*from   ww  w .j  av a  2 s . c  o  m*/
static <T extends FunctionMeta> Map<T, IDataModel> toMetaFunctions(Workbook book, Class<T> metaClass) {
    Map<T, IDataModel> map = new HashMap<>();

    book.addToolPack(Functions.getUdfFinder());
    final FormulaParsingWorkbook parsingBook = create((XSSFWorkbook) book);

    Sheet s = book.getSheetAt(0); /* TODO: only one sheet is supported */
    for (Row r : s) {
        for (Cell c : r) {
            if (c == null || CELL_TYPE_FORMULA != c.getCellType()) {
                continue;
            }

            try {
                String formula = c.getCellFormula();
                String keyword = metaClass.getAnnotation(FunctionMeta.MetaFunctionKeyword.class).value();

                if (!formula.startsWith(keyword)) {
                    continue;
                }

                IDataModel dataModel = createDataModelFromCell(s, parsingBook,
                        fromRowColumn(c.getRowIndex(), c.getColumnIndex()));
                T meta = createAttributeFunctionMeta(metaClass, formula, dataModel);

                map.put(meta, dataModel);
            } catch (Exception e) {
                log.debug("Warning while parsing custom excel formula. It is OK.", e);
            }
        }
    }

    return map;
}

From source file:com.dataart.spreadsheetanalytics.engine.graph.PoiDependencyGraphBuilder.java

License:Apache License

public static IExecutionGraph buildDependencyGraph(IDataModel dataModel, IA1Address cell) {
    if (dataModel == null) {
        throw new CalculationEngineException("DataModel and PoiModel are required to build dependency graph");
    }//from  w w  w  . jav a2s .  c om

    PoiDependencyGraphBuilder db = new PoiDependencyGraphBuilder(dataModel);

    Sheet s = db.poiBook.getSheetAt(0); //TODO: works for only one sheet workbooks
    if (s == null) {
        return null;
    }
    Row r = s.getRow(cell.row());
    if (r == null) {
        return null;
    }
    Cell c = r.getCell(cell.column());
    if (c == null) {
        return null;
    }

    ExecutionGraphVertex v = ExecutionGraph
            .createVertex(A1Address.fromRowColumn(c.getRowIndex(), c.getColumnIndex()).address());
    db.state.addVertex(v);

    if (CELL_TYPE_FORMULA == c.getCellType()) {
        db.collect(v, c.getCellFormula());
    }

    return db.state;
}

From source file:com.dataart.spreadsheetanalytics.engine.PoiWorkbookConverters.java

License:Apache License

public static PoiProxyCell makeCell(PoiProxySheet sheet, Cell cell, Ptg[] ptgs) {
    final int row = cell.getRowIndex();
    final int col = cell.getColumnIndex();
    return new PoiProxyCell(sheet, row, col, ConverterUtils.resolveCellValue(cell), ptgs);
}