Example usage for org.apache.poi.ss.util CellReference convertColStringToIndex

List of usage examples for org.apache.poi.ss.util CellReference convertColStringToIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellReference
  6. convertColStringToIndex

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellReference convertColStringToIndex.

Prototype

public static int convertColStringToIndex(String ref)

Source Link

Document

takes in a column reference portion of a CellRef and converts it from ALPHA-26 number format to 0-based base 10.

Usage

From source file:com.infovity.iep.loader.util.LoadUtil.java

public int createInsertScripts(String tableName, String fromColumn, String toColumn, File file,
        String sheetName, int startingRow) throws Exception {

    List<String> queries = new ArrayList<>();
    String query = null;//from   w w  w.  j  ava  2  s  .  c o m

    int startColumn = CellReference.convertColStringToIndex(fromColumn);
    int endColumn = CellReference.convertColStringToIndex(toColumn);

    Workbook workbook = null;
    workbook = Workbook.getWorkbook(file);

    Sheet sheet = workbook.getSheet(sheetName);

    Cell headerRow[] = sheet.getRow(startingRow - 2);
    Cell dataRow[] = sheet.getRow(startingRow - 1);
    int currentRowNumber = startingRow - 1;

    HttpSession session = IepUtil.getHttpSession();
    String empId = (String) session.getAttribute("loggedInEmpID");
    String loginTime = (String) session.getAttribute("loggedInTime");

    //        query = "INSERT INTO " + empId + loginTime.substring(4) + tableName + " ( ROW_KEY, ";
    // query = "INSERT INTO " + "01" + "4556" + tableName + " (";
    query = "INSERT INTO " + empId + loginTime + tableName + " ( ROW_KEY, ";
    for (int i = startColumn; i <= endColumn && i < headerRow.length; i++) {
        query = query + getColumnName(headerRow[i].getContents());
        if (i < endColumn) {
            query = query + ",";
        }
    }
    query = query + ") VALUE (?,";

    for (int j = startColumn; j <= endColumn && j < headerRow.length; j++) {
        query = query + "?";
        if (j < endColumn) {
            query = query + ",";
        }
    }
    query = query + ")";

    int rowCount = 0;
    connect.setAutoCommit(false);
    statement = connect.prepareStatement(query);

    while (sheetHasAnyMoreData(dataRow, sheet, currentRowNumber)) {
        currentRowNumber++;
        if (rowHasAnyData(dataRow, startColumn, endColumn)) {
            rowCount++;
            int i = 0;
            statement.setInt(++i, currentRowNumber);
            for (int k = startColumn; k <= endColumn && k < dataRow.length; k++) {
                statement.setString(++i, dataRow[k].getContents());
            }
            statement.addBatch();

            if (rowCount % 100 == 0) {
                statement.executeBatch();
                connect.commit();
                statement = connect.prepareStatement(query);
            }
        }

        try {
            dataRow = sheet.getRow(currentRowNumber);
        } catch (ArrayIndexOutOfBoundsException e) {
            System.out.println("No more rows.. returning");
            break;
        }
    }
    statement.executeBatch();
    connect.commit();
    connect.setAutoCommit(true);
    return rowCount;

}

From source file:com.infovity.iep.loader.util.LoadUtil.java

public String formCreateQuery(String tableName, String fromColumn, String toColumn, List<File> xlFiles,
        String sheetName, int startRow) throws Exception {

    List<String> columns = new ArrayList<>();
    String query = null;/*from  w w  w.j  a  va2s.  c  om*/
    int sheetIndex = -1;

    boolean sheetFound = false;

    for (File inputFile : xlFiles) {
        Workbook workbook = Workbook.getWorkbook(inputFile);

        int noOfSheets = workbook.getNumberOfSheets();
        if (sheetFound) {
            break;
        }
        for (int i = 0; i < noOfSheets; i++) {
            Sheet sheet = workbook.getSheet(0);
            if (sheet.getName().equals(sheetName)) {
                sheetFound = true;
                sheetIndex = i;
                selectedFile = inputFile;
                break;
            }
        }
    }

    if (!sheetFound) {
        System.out.println("Sheet not found in the uploaded files!");
        return null;
    }

    int startColumn = CellReference.convertColStringToIndex(fromColumn);
    int endColumn = CellReference.convertColStringToIndex(toColumn);
    InputStream inputStream = new FileInputStream(selectedFile);

    HttpSession session = IepUtil.getHttpSession();
    String empId = (String) session.getAttribute("loggedInEmpID");
    String loginTime = (String) session.getAttribute("loggedInTime");

    Workbook workbook = null;
    workbook = Workbook.getWorkbook(selectedFile);

    Sheet sheet = workbook.getSheet(sheetName);

    Cell headerRow[] = sheet.getRow(startRow - 2);

    if (rowHasAnyData(headerRow, startColumn, endColumn)) {

        //            query = "CREATE TABLE " + empId + loginTime.substring(4) + tableName + " ( ROW_KEY INT(11), ";
        query = "CREATE TABLE " + empId + loginTime + tableName + " ( ROW_KEY INT(11), ";
        // query = "CREATE TABLE " + "01" + "4556" + tableName + " (";
        for (int i = startColumn; i <= endColumn; i++) {
            query = query + getColumnName(headerRow[i].getContents()) + " VARCHAR(200)";
            if (i < endColumn) {
                query = query + ",";
            }

        }
        query = query + ")";
    }
    return query;
}

From source file:com.jkoolcloud.tnt4j.streams.parsers.ActivityExcelRowParser.java

License:Apache License

/**
 * Gets field raw data value resolved by locator.
 *
 * @param locator//from   w  w  w . j a  va2  s . c o m
 *            activity field locator
 * @param cData
 *            MS Excel document row representing activity object data fields
 * @param formattingNeeded
 *            flag to set if value formatting is not needed
 * @return raw value resolved by locator, or {@code null} if value is not resolved
 *
 * @throws ParseException
 *             if exception occurs while resolving raw data value
 */
@Override
protected Object resolveLocatorValue(ActivityFieldLocator locator, ActivityContext cData,
        AtomicBoolean formattingNeeded) throws ParseException {
    Object val = null;
    String locStr = locator.getLocator();
    Row row = cData.getData();

    if (StringUtils.isNotEmpty(locStr)) {
        int cellIndex = CellReference.convertColStringToIndex(locStr);
        if (cellIndex < 0) {
            throw new ParseException(
                    StreamsResources.getStringFormatted(MsOfficeStreamConstants.RESOURCE_BUNDLE_NAME,
                            "ActivityExcelRowParser.unresolved.cell.reference", locStr),
                    row.getRowNum());
        }
        Cell cell = row.getCell(cellIndex);
        boolean cellFound = false;
        if (cell != null) {
            val = getCellValue(cell);
            cellFound = true;
        }

        logger().log(OpLevel.TRACE,
                StreamsResources.getString(MsOfficeStreamConstants.RESOURCE_BUNDLE_NAME,
                        "ActivityExcelRowParser.resolved.cell.value"),
                locStr, row.getSheet().getWorkbook().getMissingCellPolicy(), toString(val));
    }

    return val;
}

From source file:de.jlo.talendcomp.excel.SpreadsheetFile.java

License:Apache License

/**
 * map the value in the row column to an excel column
 * @param schemaColumnIndex index in the data row (parameter of writeRow method)
 * @param columnName 'A' or 'BC'//ww w. ja  va 2  s .co m
 */
public void setDataColumnPosition(int schemaColumnIndex, String columnName) {
    if (columnName != null) {
        columnIndexes.put(schemaColumnIndex, CellReference.convertColStringToIndex(columnName));
        individualColumnMappingUsed = true;
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetFile.java

License:Apache License

public void setColumnStart(String columnName) {
    this.columnStartIndex = CellReference.convertColStringToIndex(columnName);
}

From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java

License:Apache License

public void freezeAt(String columnName, int rowIndex) {
    freezeAt(CellReference.convertColStringToIndex(columnName), rowIndex);
}

From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java

License:Apache License

public boolean writeReferencedCellValue(Integer rowIndex, Object column, Object value, String comment,
        String styleName) throws Exception {
    if ((rowIndex == null || rowIndex.intValue() < 1) || (column == null)) {
        return false;
    }//from  w ww  . j  av a 2 s .c  o m
    int columnIndex = 0;
    if (column instanceof String) {
        columnIndex = CellReference.convertColStringToIndex((String) column);
    } else if (column instanceof Number) {
        columnIndex = ((Number) column).intValue();
    } else {
        throw new Exception("The value " + column + " in parameter column cannot be used as column index.");
    }
    if (columnIndex < 0) {
        return false;
    }
    if (sheet == null) {
        throw new IOException("Sheet is not initialized!");
    }
    Row row = getRow(rowIndex - 1);
    Cell cell = getCell(row, columnIndex);
    writeCellValue(cell, value, columnIndex, rowIndex - 1);
    if (comment != null && comment.isEmpty() == false) {
        setCellComment(cell, comment);
    }
    if (styleName != null) {
        CellStyle style = namedStyles.get(styleName);
        if (style != null) {
            cell.setCellStyle(style);
        }
    }
    return true;
}

From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java

License:Apache License

public void addColumnGroup(Object fromColumn, Object toColumn) {
    int fromColumnIndex = -1;
    if (fromColumn instanceof Number) {
        fromColumnIndex = ((Number) fromColumn).intValue();
    } else if (fromColumn instanceof String) {
        fromColumnIndex = CellReference.convertColStringToIndex((String) fromColumn);
    }/*w w w .jav  a  2 s.  c o  m*/
    int toColumnIndex = 0;
    if (toColumn instanceof Number) {
        toColumnIndex = ((Number) toColumn).intValue();
    } else if (toColumn instanceof String) {
        toColumnIndex = CellReference.convertColStringToIndex((String) toColumn);
    }
    if (fromColumnIndex >= 0 && fromColumnIndex < toColumnIndex - 1) {
        sheet.groupColumn(fromColumnIndex, toColumnIndex);
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java

License:Apache License

public void groupRowsByColumn(String columnName) {
    if (columnName != null && columnName.trim().isEmpty() == false) {
        groupRowsByColumn = true;/*from  w  w w  .j  a  v  a2s.co  m*/
        int columnIndex = CellReference.convertColStringToIndex(columnName);
        if (groupInfoMap.get(columnIndex) == null) {
            GroupInfo gi = new GroupInfo();
            groupInfoMap.put(columnIndex, gi);
        }
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetReferencedCellInput.java

License:Apache License

private boolean readNextCell(int rowIndex, Object columnRef) throws Exception {
    if (workbook == null) {
        throw new IllegalStateException("Workbook is not initialized!");
    }// w w  w.j av a  2  s.com
    clearCurrentCellValue();
    if (rowIndex < 1) {
        throw new IllegalArgumentException("Row index must >= 1");
    }
    int columnIndex = -1;
    if (columnRef instanceof Number) {
        columnIndex = ((Number) columnRef).intValue();
    } else if (columnRef instanceof String) {
        columnIndex = CellReference.convertColStringToIndex((String) columnRef);
    } else {
        throw new IllegalArgumentException("Cell column refeference must be an none empty String or a number");
    }
    Row row = getSheet().getRow(rowIndex - 1);
    if (row != null) {
        Cell cell = row.getCell(columnIndex);
        if (cell != null) {
            return fetchCurrentCellValue(cell);
        } else {
            return false;
        }
    } else {
        return false;
    }
}