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.runwaysdk.dataaccess.io.excel.ContextBuilder.java

License:Open Source License

@Override
public void configure(ImportContext context, Row typeRow, Row nameRow, Row labelRow) {
    // Copy the type, name, and label rows to the error sheet
    context.addErrorRow(typeRow);/*from  ww  w.  j  av a  2s .com*/
    context.addErrorRow(nameRow);
    context.addErrorRow(labelRow);

    // To start, assume that everything is an extra column. We'll move expected
    // ones to the correct list soon
    Iterator<Cell> nameIterator = nameRow.cellIterator();
    Iterator<Cell> labelIterator = labelRow.cellIterator();
    while (nameIterator.hasNext()) {
        Cell name = nameIterator.next();
        Cell label = labelIterator.next();
        context.addExtraColumn(
                new ExcelColumn(ExcelUtil.getString(name), ExcelUtil.getString(label), name.getColumnIndex()));
    }

    // Build columns for all of the expected attributes
    List<? extends MdAttributeDAOIF> attributes = this.getAttributes(context);

    for (MdAttributeDAOIF mdAttribute : attributes) {
        this.buildAttributeColumn(context, mdAttribute);
    }

    // Map the index for the expected types
    Iterator<AttributeColumn> expectedIterator = context.getExpectedColumns().iterator();
    while (expectedIterator.hasNext()) {
        ExcelColumn expected = expectedIterator.next();
        boolean match = false;

        Iterator<ExcelColumn> extraIterator = context.getExtraColumns().iterator();
        while (extraIterator.hasNext()) {
            ExcelColumn extra = extraIterator.next();
            if (extra.equals(expected)) {
                extraIterator.remove();
                expected.setIndex(extra.getIndex());
                match = true;
                break;
            }
        }

        // No matches found for the expected column. We need to remove it.
        if (!match) {
            expectedIterator.remove();
        }
    }
    // At this point every column is either in the expected list or the extra
    // list.
}

From source file:com.runwaysdk.dataaccess.io.excel.ErrorSheet.java

License:Open Source License

public void addRow(Row _row) {
    Row row = this.errorSheet.createRow(count++);
    row.setZeroHeight(_row.getZeroHeight());
    row.setHeight(_row.getHeight());//  ww  w.j a va  2  s.c om

    CellStyle style = _row.getRowStyle();

    if (style != null) {
        Workbook workbook = row.getSheet().getWorkbook();

        CellStyle clone = workbook.createCellStyle();
        clone.cloneStyleFrom(style);

        row.setRowStyle(clone);
    }

    Iterator<Cell> cellIterator = _row.cellIterator();
    while (cellIterator.hasNext()) {
        Cell oldCell = cellIterator.next();
        Cell newCell = row.createCell(oldCell.getColumnIndex());

        int cellType = oldCell.getCellType();

        if (cellType == Cell.CELL_TYPE_FORMULA) {
            cellType = oldCell.getCachedFormulaResultType();
        }

        switch (cellType) {
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }
    }
}

From source file:com.salahatwa.randomme.ReadXLS.java

/**
 * @param filePath/* www  . j a v a2  s.  co  m*/
 * @return  list of Readed cells from xlsx
 */
public List<ReadedBean> readXLSFromFile(String filePath) {
    List<ReadedBean> data = new ArrayList();
    FileInputStream fis = null;
    try {
        fis = new FileInputStream(filePath);
        // Using XSSF for xlsx format, for xls use HSSF

        Workbook workbook = new XSSFWorkbook(fis);

        int numberOfSheets = workbook.getNumberOfSheets();

        //looping over each workbook sheet
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Iterator rowIterator = sheet.iterator();

            //iterating over each row
            while (rowIterator.hasNext()) {

                ReadedBean readedBean = new ReadedBean();
                Row row = (Row) rowIterator.next();
                Iterator cellIterator = row.cellIterator();
                //Iterating over each cell (column wise)  in a particular row.
                while (cellIterator.hasNext()) {
                    Cell cell = (Cell) cellIterator.next();

                    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                        if (cell.getColumnIndex() == 0) {
                            readedBean.setCell(cell.getStringCellValue());
                        }

                    } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {

                        if (cell.getColumnIndex() == 0) {
                            readedBean.setCell(String.valueOf((int) cell.getNumericCellValue()));
                        }
                        //                           
                    }
                }
                System.out.println(readedBean.getCell());
                data.add(readedBean);
            }
        }
        fis.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return data;
}

From source file:com.sdfl.compiler.util.inputfile.impl.ImportInputFileLoaderFileSystemImpl.java

License:Open Source License

private ImportInputFileRow loadRow(Row lCurRow) {
    ImportInputFileRow lCurInputFileRow = new ImportInputFileRow();

    for (Cell lCurCell : lCurRow) {
        lCurInputFileRow.setColumn(lCurCell.getColumnIndex(), lCurCell.getStringCellValue());
    }//  ww  w.  ja  v  a  2 s.  com

    return lCurInputFileRow;
}

From source file:com.sonicle.webtop.core.io.input.MemoryExcelFileReader.java

License:Open Source License

public HashMap<String, String> listColumnNames(InputStream is)
        throws IOException, UnsupportedOperationException {
    HashMap<String, String> hm = new LinkedHashMap<>();

    Workbook wb = createWorkbook(is);/*www . j a  va  2 s  . co m*/
    if (wb.getNumberOfSheets() == 0)
        throw new UnsupportedOperationException("At least one sheet is required");
    Sheet sh = getSheet(wb);
    if (sh == null)
        throw new UnsupportedOperationException("Unable to find desired sheet");

    String name = null;
    Row hrow = sh.getRow(headersRow - 1);
    for (Cell cell : hrow) {
        if (headersRow == firstDataRow) {
            name = "col_" + CellReference.convertNumToColString(cell.getColumnIndex());
        } else {
            name = fmt.formatCellValue(cell);
            if (StringUtils.isBlank(name))
                name = "col_" + CellReference.convertNumToColString(cell.getColumnIndex());
        }
        hm.put(name.toLowerCase(), name);
    }

    return hm;
}

From source file:com.sonicle.webtop.core.io.input.MemoryExcelFileReader.java

License:Open Source License

public HashMap<String, Integer> listColumnIndexes(InputStream is)
        throws IOException, UnsupportedOperationException {
    HashMap<String, Integer> hm = new LinkedHashMap<>();

    Workbook wb = createWorkbook(is);// w ww .j av  a  2  s  . c om
    if (wb.getNumberOfSheets() == 0)
        throw new UnsupportedOperationException("At least one sheet is required");
    Sheet sh = getSheet(wb);
    if (sh == null)
        throw new UnsupportedOperationException("Unable to find desired sheet");

    String name = null;
    Row hrow = sh.getRow(headersRow - 1);
    for (Cell cell : hrow) {
        if (headersRow == firstDataRow) {
            name = "col_" + CellReference.convertNumToColString(cell.getColumnIndex());
        } else {
            name = fmt.formatCellValue(cell);
            if (StringUtils.isBlank(name))
                name = "col_" + CellReference.convertNumToColString(cell.getColumnIndex());
        }
        hm.put(name.toLowerCase(), cell.getColumnIndex());
    }

    return hm;
}

From source file:com.stam.excellatin.ExcelLatin.java

public static void main(String[] args) {
    List<String> options = new ArrayList<>();
    int startIndex = 0;
    for (String arg : args) {
        if (validOptions.contains(arg)) {
            options.add(arg);//from w ww.j  a va  2s  .  c  o  m
            startIndex++;
        }
    }

    if (args[0].equals("-h") || args.length < 3) {
        System.out.println("usage: ExcelLatin [options] filenameIn filenameOut columnNames...");
        System.out.println("options:");
        System.out.println("\t-L\tto Latin (default)");
        System.out.println("\t-G\tto Greek");
        System.out.println("\t-d\tdon't deaccent");
        System.out.println("\t-h\thelp");
    } else {
        boolean greekToLatin = false;
        boolean latinToGreek = false;
        Transliterator transliterator = null;
        if ((!options.contains("-L") && !options.contains("-G")) || options.contains("-L")) {
            transliterator = Transliterator.getInstance("Greek-Latin/UNGEGN");
            System.out.println("\nTransliterating Greek to Latin");
            greekToLatin = true;
        } else if (options.contains("-G")) {
            transliterator = Transliterator.getInstance("Latin-Greek/UNGEGN");
            System.out.println("\nTransliterating Latin to Greek");
            latinToGreek = true;
        }

        if (transliterator == null) {
            System.out.println("Not a valid option for the transliteration language");
            return;
        }

        boolean deAccent = true;
        if (options.contains("-d")) {
            deAccent = false;
            System.out.println("Will not deaccent");
        }

        String fileNameIn = args[startIndex];
        String fileNameOut = args[startIndex + 1];
        List<String> columnNames = new ArrayList<>();
        System.out.println("\nColumns to transliterate\n---------------------------");
        for (int i = startIndex + 2; i < args.length; i++) {
            columnNames.add(args[i]);
            System.out.println(args[i]);
        }
        System.out.println("\n");

        try {
            File file = new File(fileNameIn);
            if (!file.exists()) {
                System.out.println("The file " + fileNameIn + " was not found");
                return;
            }

            Map<String, String> mapTransformations = new HashMap<>();
            Scanner sc = new Scanner(new FileReader("map.txt"));
            while (sc.hasNextLine()) {
                String greekEntry = sc.next();
                String latinEntry = sc.next();

                if (greekToLatin) {
                    mapTransformations.put(greekEntry, latinEntry);
                } else if (latinToGreek) {
                    mapTransformations.put(latinEntry, greekEntry);
                }
            }

            DataFormatter formatter = new DataFormatter();
            Workbook wb = WorkbookFactory.create(file);

            Workbook newWb = null;
            if (wb instanceof HSSFWorkbook) {
                newWb = new HSSFWorkbook();
            } else if (wb instanceof XSSFWorkbook) {
                newWb = new XSSFWorkbook();
            }
            FileOutputStream fileOut = new FileOutputStream(fileNameOut);
            if (newWb != null) {
                Sheet sheetOut = newWb.createSheet();

                Sheet sheet = wb.getSheetAt(0);

                List<Integer> idxs = new ArrayList<>();

                Row row = sheet.getRow(0);
                for (Cell cell : row) {
                    String cellVal = formatter.formatCellValue(cell);
                    if (cellVal == null || cellVal.trim().equals("")) {
                        break;
                    }

                    if (columnNames.contains(cell.getStringCellValue())) {
                        idxs.add(cell.getColumnIndex());
                    }
                }

                for (Row rowIn : sheet) {
                    Row rowOut = sheetOut.createRow(rowIn.getRowNum());
                    if (rowIn.getRowNum() == 0) {
                        for (Cell cell : rowIn) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            Cell cellOut = rowOut.createCell(cell.getColumnIndex());
                            cellOut.setCellValue(cell.getStringCellValue());
                        }
                    } else {
                        for (Cell cell : rowIn) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            String cellVal = formatter.formatCellValue(cell);
                            String cellNewVal = cellVal;
                            if (idxs.contains(cell.getColumnIndex()) && cellVal != null) {
                                if (mapTransformations.containsKey(cellVal)) {
                                    cellNewVal = mapTransformations.get(cellVal);
                                } else {
                                    if (deAccent) {
                                        cellNewVal = deAccent(transliterator.transform(cellVal));
                                    } else {
                                        cellNewVal = transliterator.transform(cellVal);
                                    }
                                }
                            }
                            Cell cellOut = rowOut.createCell(cell.getColumnIndex());
                            cellOut.setCellValue(cellNewVal);
                        }
                    }
                }

                System.out.println("Finished!");

                newWb.write(fileOut);
                fileOut.close();
            }
        } catch (IOException | InvalidFormatException ex) {
            Logger.getLogger(ExcelLatin.class.toString()).log(Level.SEVERE, null, ex);
        }
    }

}

From source file:com.tm.hiber.service.TMDataOperationServiceImpl.java

@Override
public List<TaskMasterExcelTemplate> prepareTaskMasterFromExcel(File excelFile) {
    mLogger.log(Level.INFO, "prepareTaskMasterFromExcel--Starts");
    if (excelFile != null && excelFile.exists()) {
        InputStream fis = null;/*from  w ww  .  ja  v  a  2 s.c o m*/
        try {
            mTaskMasterList.clear();
            fis = new FileInputStream(excelFile);
            HSSFWorkbook objWorkBook = new HSSFWorkbook(fis);
            HSSFSheet objSheet = objWorkBook.getSheetAt(0);
            Iterator<Row> rowItr = objSheet.iterator();
            TaskMasterExcelTemplate objTaskMaster;
            while (rowItr.hasNext()) {
                Row row = rowItr.next();
                objTaskMaster = new TaskMasterExcelTemplate();
                Iterator<Cell> cellItr = row.cellIterator();
                while (cellItr.hasNext()) {

                    Cell objCell = cellItr.next();
                    String cellValue = getCellData(objCell);
                    switch (objCell.getColumnIndex()) {

                    case 0:
                        objTaskMaster.setTaskReference(cellValue);
                        break;
                    case 2:
                        Date createDate = parseDate(cellValue);
                        if (createDate != null) {
                            objTaskMaster.setCreateDate(createDate);
                        }
                        break;
                    case 6:
                        objTaskMaster.setTitle(cellValue);
                        break;
                    case 7:
                        objTaskMaster.setDescription(cellValue);
                        break;
                    case 8:
                        objTaskMaster.setPriority(cellValue);
                        break;
                    case 18:
                        objTaskMaster.setProjectName(cellValue);
                        break;
                    }

                }

                objTaskMaster.setAuditLastupdateon(new Date());
                objTaskMaster.setAuditLastupdateby(UtilService.self().getSystemUser());

                mTaskMasterList.add(objTaskMaster);
            }

        } catch (Exception ex) {
            mLogger.log(Level.FATAL, ex.getMessage());
        } finally {
            try {
                if (fis != null) {
                    fis.close();
                }
            } catch (IOException ex) {
                mLogger.log(Level.FATAL, ex.getMessage());
            }
        }

    }
    mLogger.log(Level.INFO, "prepareTaskMasterFromExcel--Ends");
    return mTaskMasterList;
}

From source file:com.tutorial.excelreadwrite.excelFunctions.java

    public void markHorizontal(int spacesApart){
        //Set-up rowIterator and get Row
        Iterator<Row> rowIterator = sheet.iterator();
        while(rowIterator.hasNext()){
            Row row = rowIterator.next();
            //  w  w  w . ja v a  2s .  c  o  m
            //Set-up cellIterator and get Cell
            Iterator<Cell> cellIterator = row.cellIterator();
            while(cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                
                //Obtains the Cell Style
                XSSFCellStyle cellStyle = (XSSFCellStyle)cell.getCellStyle();
                //Checks to see if the Cell Style is null; if null, go to next cell
                if(cellStyle != null){
                    //Checks to see what color is the cell's color
                    XSSFColor cellColor = cellStyle.getFillForegroundXSSFColor();
                    //Checks to see if color is null; if not compare to accept only editted or userDefined cells
                    if(cellColor != null){
                        //Checks if current cell is userDefined or editted
                        //If it is not, then go to the next cell
                        if(cellColor.equals(mark.getFillForegroundXSSFColor()) || cellColor.equals(userDefinedColor)){

                            //Set boolean isCellMarked to false before proceeding
                            isCellMarked = false;

                            //Define Cell to be (spacesApart+1) away
                            //So if x = current cell then the cell that is 5 spacesApart =
                            // [x][][][][][][x]
                            Cell cellMark = sheet.getRow(cell.getRowIndex()).getCell(cell.getColumnIndex() + spacesApart + 1);

                            //Checks to see if cell is null; if present, get its Cell Style
                            if(cellMark != null){
                                XSSFCellStyle cellMarkStyle = (XSSFCellStyle)cellMark.getCellStyle();

                                //Checks to see if the style is null; if present, get its color
                                if(cellMarkStyle != null){
                                    XSSFColor cellMarkColor = cellMarkStyle.getFillForegroundXSSFColor();

                                    //Checks to see if the color is null; if present, compare colors
                                    if(cellMarkColor != null){
                                        if(cellMarkColor.equals(userDefinedColor)){
                                            isCellMarked = true;
                                        }
                                    }
                                }
                            }

                            /*
                            ** CHECK#1: 'isCellMarked'
                            ** If isCellMarked is marked true, start iterating through the
                            ** cells in between and check if null or not userDefinedStyle
                            */
                            if(isCellMarked == true){
                                for(int i = 1; i <= spacesApart; ++i){
                                    Cell isNull = sheet.getRow(cell.getRowIndex()).getCell(cell.getColumnIndex()+i);

                                    //Checks to see if the cell is null; if color is present, set isCellMarked to false
                                    if(isNull != null){
                                        XSSFCellStyle cellCheckIfNullCellStyle = (XSSFCellStyle)isNull.getCellStyle();
                                        if(cellCheckIfNullCellStyle != null){
                                            XSSFColor cellCheckIfNullColor = cellCheckIfNullCellStyle.getFillForegroundXSSFColor();
                                            if(cellCheckIfNullColor != null){
                                                if(cellCheckIfNullColor.equals(userDefinedColor)){
                                                    isCellMarked = false;
                                                    break;
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                            /*
                            ** CHECK#2: 'isCellMarked2'
                            ** If isCellMarked remains as true, set the two cell's style
                            */
                            if(isCellMarked == true){
                                cell.setCellStyle(mark);
                                cellMark.setCellStyle(mark);
                            }
                        }
                }
            }
        }
    }
}

From source file:com.vaadin.addon.spreadsheet.action.InsertDeleteCellCommentAction.java

License:Open Source License

private void createCellComment(Spreadsheet spreadsheet, Sheet sheet, Cell cell, CellReference cellRef) {
    CreationHelper factory = sheet.getWorkbook().getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();

    ClientAnchor anchor = factory.createClientAnchor();
    anchor.setCol1(cell.getColumnIndex());
    anchor.setCol2(cell.getColumnIndex() + 1);
    anchor.setRow1(cell.getRowIndex());//w w w  .ja  v a  2s  .co m
    anchor.setRow2(cell.getRowIndex() + 3);

    // Create the comment and set the text+author
    Comment comment = drawing.createCellComment(anchor);
    RichTextString str = factory.createRichTextString("");
    comment.setString(str);

    // Fetch author from provider or fall back to default
    String author = null;
    if (spreadsheet.getCommentAuthorProvider() != null) {
        author = spreadsheet.getCommentAuthorProvider().getAuthorForComment(cellRef);
    }
    if (author == null || author.trim().isEmpty()) {
        author = "Spreadsheet User";
    }
    comment.setAuthor(author);

    // Assign the comment to the cell
    cell.setCellComment(comment);
}