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:list.java

public List<city> readcityFromExcelFile(String excelFilePath) throws IOException {
    List<city> listcity = new ArrayList<>();
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    Workbook workbook = new XSSFWorkbook(inputStream);
    Sheet firstSheet = workbook.getSheetAt(0);
    Iterator<Row> iterator = firstSheet.iterator();

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();/*from  w ww.j  a v  a2s .co m*/

        Iterator<Cell> cellIterator = nextRow.cellIterator();
        city acity = new city();

        while (cellIterator.hasNext()) {
            Cell nextCell = cellIterator.next();
            int columnIndex = nextCell.getColumnIndex();

            switch (columnIndex) {
            case 0:
                acity.setCompany(nextCell.getStringCellValue());
                break;
            case 1:
                acity.setFrom(nextCell.getStringCellValue());
                break;
            case 2:

                acity.setTo(nextCell.getStringCellValue());
                break;

            case 3:

                acity.setFare(nextCell.getNumericCellValue());

                break;
            case 4:
                acity.setTime(nextCell.getNumericCellValue());
                break;
            }

        }
        listcity.add(acity);

    }

    workbook.close();
    inputStream.close();

    return listcity;
}

From source file:ExcelConverter.java

public List<ScheduleClass> Converter() throws FileNotFoundException, IOException {
    ArrayList<ScheduleClass> scheduleList = new ArrayList<>();

    FileInputStream fis = new FileInputStream(pathFile);

    XSSFWorkbook wb = new XSSFWorkbook(fis);
    XSSFSheet sheet = wb.getSheetAt(0);//from w  ww  .  j  av a  2  s . c  o  m
    Iterator<Row> rowIterator = sheet.iterator();

    CellRangeAddress add;
    int colNoIdx = 0;
    ArrayList<String> dosen = new ArrayList<>();
    ArrayList<Integer> idxDosen = new ArrayList<>();
    ArrayList<Integer> colDosen = new ArrayList<>();
    ArrayList<String> location = new ArrayList<>();
    int idxNumber = 0;
    ArrayList<Integer> locationIdx = new ArrayList<>();
    outerloop: for (int j = 0; j < sheet.getLastRowNum(); j++) {
        row = sheet.getRow(j);
        for (int f = 0; f < row.getLastCellNum(); f++) {
            Cell cell = row.getCell(j);
            if (cell.getStringCellValue().contains("No.")) {
                rowNoIdx = j;
                colNoIdx = cell.getColumnIndex();

                break outerloop;
            }
        }
    }
    outerloop2: for (int i = 0; i < sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        outerloop: for (int j = 0; j < row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j);
            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3
                    && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) {
                i = sheet.getLastRowNum();
                break outerloop2;
            }

            if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 1)) {
                String delims = "[,. ]";
                String[] sumary = cell.getStringCellValue().split(delims);
                for (int l = 0; l < sumary.length; l++) {
                    if (sumary[l].equalsIgnoreCase("Mrt")) {
                        sumary[l] = "3";
                    }
                }

                lc = LocalDate.of(Integer.parseInt(sumary[5]), Integer.parseInt(sumary[3]),
                        Integer.parseInt(sumary[2]));
            }
            if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 2)) {
                if (cell.getStringCellValue().equalsIgnoreCase("LIBUR")) {
                    i = i + 1;
                    break outerloop;
                } else {
                    String delimsJam = "[-]";
                    String[] arrJam = cell.getStringCellValue().split(delimsJam);
                    for (int k = 0; k < arrJam.length; k++) {
                        arrJam[k] = arrJam[k].replace('.', ':');
                    }
                    lt = LocalTime.parse(arrJam[0]);
                }

            }
            if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 5)) {
                subject = cell.getStringCellValue();
            }

            if (cell.getRowIndex() > rowNoIdx && cell.getColumnIndex() >= colNoIdx + 6
                    && cell.getColumnIndex() < row.getLastCellNum()) {
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                }
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (cell.getStringCellValue().contains(":")) {
                        String[] splt = cell.getStringCellValue().split(":");
                        String[] splt2 = splt[1].split(",");
                        for (int l = 0; l < splt2.length; l++) {
                            dosen.add(splt2[l].trim());
                            location.add("Lab");
                        }
                    } else {
                        CellReference cr = new CellReference(1, cell.getColumnIndex());
                        Row row2 = sheet.getRow(cr.getRow());
                        Cell c = row2.getCell(cr.getCol());
                        if (!cell.getStringCellValue().isEmpty()) {
                            dosen.add(cell.getStringCellValue().trim());
                            location.add(String.valueOf((int) c.getNumericCellValue()).trim());
                        }
                    }

                }
                if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) {
                    CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex());
                    Row row2 = sheet.getRow(cr.getRow());
                    Cell c = row2.getCell(cr.getCol());
                    CellReference cr2 = new CellReference(1, cell.getColumnIndex());
                    Row row3 = sheet.getRow(cr2.getRow());
                    Cell c2 = row3.getCell(cr2.getCol());
                    if (c.getStringCellValue().contains(":")) {
                        String[] splt = c.getStringCellValue().split(":");
                        String[] splt2 = splt[1].split(",");
                        for (int l = 0; l < splt2.length; l++) {
                            dosen.add("".trim());
                            location.add("");
                        }
                    } else {
                        if (!c.getStringCellValue().isEmpty()) {
                            dosen.add("");
                            location.add("");
                        }
                    }
                }
            }
        }

        for (int j = 0; j < dosen.size(); j++) {
            scheduleList
                    .add(new ScheduleClass(lc, lt, lt.plusHours(2), subject, dosen.get(j), location.get(j)));
        }
        dosen.clear();
        location.clear();

    }

    return Mergering(scheduleList);
}

From source file:ExampleClass.java

public static void main(String[] args) throws Exception {
    File src = new File(
            "C:\\Users\\Ariq\\Documents\\NetBeansProjects\\Skripsi-Jadwal-Mengawas-Ujian\\Contoh File\\Jadwal_Pengawas_ Ujian_Pak_ Pascal.xlsx");
    //File src = new File("D:\\\\Skripsi\\\\Data Baru\\\\Daftar Dosen.xlsx");
    FileInputStream fis = new FileInputStream(src);
    XSSFWorkbook wb = new XSSFWorkbook(fis);

    XSSFSheet sheet1 = wb.getSheetAt(0);
    //        Iterator< Row> rowIterator = sheet1.iterator();
    int colIndex = 0;
    int ex = 0;/* w ww  . j  ava2 s. c om*/
    int lastCol = sheet1.getLastRowNum();
    int i = 0;
    int idx = 0;
    CellRangeAddress add;

    //        while (rowIterator.hasNext()) {
    //            row = (XSSFRow) rowIterator.next();
    //            Iterator< Cell> cellIterator = row.cellIterator();
    //            //System.out.println("i = "+i+", ex:"+ex);
    //
    //            if (row.getRowNum() > 53) {
    //                break;
    //            }
    ////            if(lastCol-(ex+1) == i) break;
    //            while (cellIterator.hasNext()) {
    //                Cell cell = cellIterator.next();

    //                for (int f = 0; f < sheet1.getNumMergedRegions(); f++) {
    //                    add = sheet1.getMergedRegion(f);
    //                    
    //                    int col = add.getFirstColumn();
    //                    int rowNum = add.getFirstRow();
    //                    if (rowNum != 0 && rowNum == cell.getRowIndex() && colIndex == cell.getColumnIndex()) {
    //                        System.out.println("col:"+col+" "+",row :"+rowNum);
    //                        String b = String.valueOf(sheet1.getRow(rowNum).getCell(col));
    //                        System.out.println(b);     
    //                        
    //                    }
    //                    
    //                }
    //               switch (cell.getCellType()) 
    //               {
    //                  case Cell.CELL_TYPE_FORMULA:
    //                      ex++;
    //                       switch (cell.getCachedFormulaResultType()) 
    //                       {
    //                           case Cell.CELL_TYPE_NUMERIC:
    //                           i = (int)cell.getNumericCellValue();
    //                           System.out.print( 
    //                           (int)cell.getNumericCellValue() + " \t\t " );
    //                             
    //                                 
    //                           break;
    //                       }
    //                   break;
    //                  case Cell.CELL_TYPE_NUMERIC:
    //                    if (cell.getColumnIndex() >= 6)
    //                    {
    //                        System.out.print( 
    //                        (int)cell.getNumericCellValue() + " \t\t " );
    //                    }
    //                    break;
    //                  case Cell.CELL_TYPE_STRING:
    //                   add = sheet1.getMergedRegion(cell.getRowIndex());
    //              
    //                   if (cell.getStringCellValue().contentEquals("No."))
    //                    {
    //                       colIndex = cell.getColumnIndex();
    //                    }
    //                   if (cell.getColumnIndex() == 1)
    //                   {
    //                        System.out.print(
    //                        cell.getStringCellValue() + " \t\t " );
    //                   }              
    //                   break;
    //                  
    //               }
    //            }
    //
    //            System.out.println();
    //        }

    for (int j = 0; j < sheet1.getLastRowNum(); j++) {

        row = sheet1.getRow(j);
        for (int k = 0; k < row.getLastCellNum(); k++) {

            Cell cell = row.getCell(k);
            //                if (cell.getColumnIndex() == 1)
            //                {
            //                    System.out.println(cell.getStringCellValue());
            //                }
            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            if (cell.getColumnIndex() == 0 && j > 3
                    && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) {
                System.exit(k);
            }
            if (cell.getColumnIndex() >= 6 && cell.getColumnIndex() <= 11) {
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    System.out.print((int) cell.getNumericCellValue() + " ");
                }
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (cell.getStringCellValue().contains(":")) {
                        String[] splt = cell.getStringCellValue().split(":");
                        String[] splt2 = splt[1].split(",");
                        for (int l = 0; l < splt2.length; l++) {
                            System.out.println(splt2[l] + "= lab");
                        }
                    }

                    else {
                        CellReference cr = new CellReference(1, cell.getColumnIndex());
                        Row row2 = sheet1.getRow(cr.getRow());
                        Cell c = row2.getCell(cr.getCol());
                        System.out.print(
                                cell.getStringCellValue() + " Ruang =" + (int) c.getNumericCellValue() + " ");
                    }

                }
                if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) {
                    CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex());
                    Row row2 = sheet1.getRow(cr.getRow());
                    Cell c = row2.getCell(cr.getCol());
                    CellReference cr2 = new CellReference(1, cell.getColumnIndex());
                    Row row3 = sheet1.getRow(cr2.getRow());
                    Cell c2 = row3.getCell(cr2.getCol());
                    if (c.getStringCellValue().contains(":")) {
                        String[] splt = c.getStringCellValue().split(":");
                        String[] splt2 = splt[1].split(",");
                        for (int l = 0; l < splt2.length; l++) {
                            System.out.println(splt2[l] + "= lab");
                        }
                    }

                    else {
                        System.out.print(
                                c.getStringCellValue() + " Ruang = " + (int) c2.getNumericCellValue() + " ");
                    }
                }
            }

        }
        System.out.println("");
    }
    System.out.println(colIndex);

    System.out.println(idx);

    fis.close();
}

From source file:CellStyleDetails.java

License:Apache License

public static void main(String[] args) throws Exception {
    if (args.length == 0) {
        throw new IllegalArgumentException("Filename must be given");
    }/*from  w  w  w. ja v a2s  .  c o m*/

    Workbook wb = WorkbookFactory.create(new File(args[0]));
    DataFormatter formatter = new DataFormatter();

    for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) {
        Sheet sheet = wb.getSheetAt(sn);
        System.out.println("Sheet #" + sn + " : " + sheet.getSheetName());

        for (Row row : sheet) {
            System.out.println("  Row " + row.getRowNum());

            for (Cell cell : row) {
                CellReference ref = new CellReference(cell);
                System.out.print("    " + ref.formatAsString());
                System.out.print(" (" + cell.getColumnIndex() + ") ");

                CellStyle style = cell.getCellStyle();
                System.out.print("Format=" + style.getDataFormatString() + " ");
                System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " ");
                System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " ");

                Font font = wb.getFontAt(style.getFontIndex());
                System.out.print("Font=" + font.getFontName() + " ");
                System.out.print("FontColor=");
                if (font instanceof HSSFFont) {
                    System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb)));
                }
                if (font instanceof XSSFFont) {
                    System.out.print(renderColor(((XSSFFont) font).getXSSFColor()));
                }

                System.out.println();
                System.out.println("        " + formatter.formatCellValue(cell));
            }
        }

        System.out.println();
    }
}

From source file:ADP_Streamline.MatrixReader.java

public String check() throws Exception {

    List<String> ClientRoles = new ArrayList<>();
    List<String> PartnerRoles = new ArrayList<>();
    List<String> ADPRoles = new ArrayList<>();
    List<String> Rights = new ArrayList<>();

    String rightscolumn = "";
    String rights = "";
    String roles = "";
    String cellwithx;/*w  ww.j  a v a 2s.  co  m*/

    int righstrow = 0;
    int columncount;
    int rowcount;
    int roleslenght;
    int rightslenght = 0;

    Boolean client = false;
    Boolean partner = false;
    Boolean adp;
    Boolean rightstart = false;

    Iterator<Row> rowIterator;
    Iterator<Cell> cellIterator;

    Row row;
    Cell cell;

    OracleJDBC oracle = new OracleJDBC();

    try {

        FileInputStream file = new FileInputStream(
                new File("C:\\Users\\frodri1\\Documents\\SPM 1.2_RoleMatrix_Demo.xlsx"));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

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

        //Iterate through each rows one by one
        rowIterator = sheet.iterator();

        rowcount = 2;
        columncount = 0;

        while (rowIterator.hasNext()) {

            row = rowIterator.next();
            //if(adp) {break;}
            adp = false;

            //For each row, iterate through all the columns
            cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {

                cell = cellIterator.next();

                if (cell.getStringCellValue().contentEquals("Right")) {
                    rightscolumn = CellReference.convertNumToColString(cell.getColumnIndex());
                    righstrow = cell.getRowIndex();
                    rightstart = true;
                }

                if (cell.getStringCellValue().contentEquals("Client")) {
                    client = true;
                }

                if (cell.getStringCellValue().contentEquals("Partner")) {
                    partner = true;
                    client = false;
                }

                if (cell.getStringCellValue().contentEquals("ADP")) {
                    partner = false;
                    client = false;
                    adp = true;
                }

                if (client) {
                    ClientRoles.add(
                            CellIteration(sheet, CellReference.convertNumToColString(cell.getColumnIndex()),
                                    righstrow, columncount, 0).trim());
                }

                if (partner) {
                    PartnerRoles.add(
                            CellIteration(sheet, CellReference.convertNumToColString(cell.getColumnIndex()),
                                    righstrow, columncount, 0).trim());
                }

                if (adp) {
                    ADPRoles.add(
                            CellIteration(sheet, CellReference.convertNumToColString(cell.getColumnIndex()),
                                    righstrow, columncount, 0).trim());
                }
            }

            if (rightstart) {

                rights = CellIteration(sheet, rightscolumn, righstrow, columncount, rowcount);

                if (!"".equals(rights)) {

                    Rights.add(rights.trim());
                    rightslenght++;
                    rowcount++;
                } else
                    break;
            }
        }

        roleslenght = ClientRoles.size() + PartnerRoles.size() + ADPRoles.size();

        for (int i = 0; i < rightslenght; i++) {

            for (int l = 0; l < roleslenght; l++) {

                cellwithx = CellIteration(sheet, rightscolumn, righstrow, l + 1, i + 2);
                if ("x".equals(cellwithx)) {

                    if (l < ClientRoles.size()) {

                        rights = Rights.get(i);
                        roles = ClientRoles.get(l);

                        oracle.check(rights, roles);
                    }

                    if (l >= ClientRoles.size() && l < (ClientRoles.size() + PartnerRoles.size())) {

                        rights = Rights.get(i);
                        roles = PartnerRoles.get(l - ClientRoles.size());

                    }

                    if (l >= ClientRoles.size() + PartnerRoles.size()) {

                        rights = Rights.get(i);
                        roles = ADPRoles.get(l - (ClientRoles.size() + PartnerRoles.size()));

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

    return "";
}

From source file:ambit2.core.io.IteratingXLSReader.java

License:Open Source License

protected void processHeader(Row row) {

    Iterator cols = row.cellIterator();
    TreeMap columns = new TreeMap();
    while (cols.hasNext()) {
        Cell cell = (Cell) cols.next();
        String value = cell.getStringCellValue();

        if (value.equals(defaultSMILESHeader))
            smilesIndex = cell.getColumnIndex();
        columns.put(new Integer(cell.getColumnIndex()), value);
    }/* w ww. j  a v a  2  s  .  co m*/
    Iterator i = columns.keySet().iterator();
    while (i.hasNext()) {
        Integer key = (Integer) i.next();
        setHeaderColumn(key.intValue(), columns.get(key).toString());
    }
}

From source file:android_connector.ExcelReader.java

/**
 * Gibt eine vernnftige Darstellung einer Zelle als String zurck.
 * @param cell die Zelle/*  w  ww.  ja va2 s. c o  m*/
 * @return z.B. bei Zelle, die eine Gleichung enthlt, deren Ergebnis
 */
private String differCellType(Cell cell) {
    String returnValue = "";
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        returnValue = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        returnValue = String.valueOf(cell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_STRING:
        returnValue = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        FormulaEvaluator evaluator = this.wb.getCreationHelper().createFormulaEvaluator();
        CellValue cellValue = evaluator.evaluate(cell);
        returnValue = cellValue.getStringValue();
        break;
    case Cell.CELL_TYPE_ERROR:
        returnValue = String.valueOf(cell.getErrorCellValue());
        break;
    case Cell.CELL_TYPE_BLANK:
        returnValue = "";
        break;
    default:
        returnValue = "default value at (" + cell.getRowIndex() + ";" + cell.getColumnIndex() + ") !";
        break;
    }
    return returnValue;
}

From source file:at.mukprojects.exclycore.dao.XLSXReader.java

License:Open Source License

/**
 * Tries to read the value of the given cell. If it's possible to parse the
 * value into a string it will return an ExclyString with the parsed value.
 * Otherwise an ExcelStringError is returned. An ExcelStringError has the
 * value of an empty string ("")./*  w  w w .jav  a2  s . com*/
 * 
 * @param cell
 *            The Excel cell.
 * @return Return the parsed value of the cell as an ExclyString.
 */
public ExclyString readStringCellValue(Cell cell) {
    ExclyString output = null;

    if (cell == null) {
        return new ExclyStringError();
    }

    try {
        output = readString(cell, cell.getCellType());
    } catch (Exception e) {
        log.error("The reader was unable to read the data from cell [Row, Column] (" + cell.getRowIndex() + ", "
                + cell.getColumnIndex() + ")", e);
        output = new ExclyStringError();
    }

    return output;
}

From source file:at.mukprojects.exclycore.dao.XLSXReader.java

License:Open Source License

private ExclyString readString(Cell cell, int type) throws Exception {
    ExclyString output = null;/*  w  w w  .j a v  a  2s.c  om*/

    if (type == Cell.CELL_TYPE_STRING) {
        output = new ExclyString(cell.getStringCellValue());
    } else if (type == Cell.CELL_TYPE_ERROR) {
        output = new ExclyStringError();
    } else if (type == Cell.CELL_TYPE_FORMULA) {
        int formulaType = cell.getCachedFormulaResultType();
        output = readString(cell, formulaType);
    } else if (type == Cell.CELL_TYPE_BLANK) {
        output = new ExclyString("");
    } else if (type == Cell.CELL_TYPE_BOOLEAN) {
        Boolean data = cell.getBooleanCellValue();
        if (data) {
            output = new ExclyString("WAHR");
        } else {
            output = new ExclyString("FALSCH");
        }
    } else if (DateUtil.isCellDateFormatted(cell)) {
        Date data = cell.getDateCellValue();
        output = new ExclyString(data.toString());
    } else if (type == Cell.CELL_TYPE_NUMERIC) {
        double cellValue = cell.getNumericCellValue();
        String data = String.valueOf(cellValue);
        if (cellValue % 1 == 0 && data.endsWith(".0")) {
            data = data.substring(0, data.length() - 2);
        }
        output = new ExclyString(data);
    } else {
        log.warn("The reader was unable to find a valid parser for the cell [Row, Column] ("
                + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")");
        output = new ExclyStringError();
    }

    return output;
}

From source file:at.mukprojects.exclycore.dao.XLSXReader.java

License:Open Source License

/**
 * Tries to read the value of the given cell. If it's possible to parse the
 * value into a double it will return an ExclyDouble with the parsed value.
 * Otherwise a ExcelDoubleError or a ExclyDoubleBlank is returned, depending
 * on the cell value wasn't parsable or blank. Both have a value of zero.
 * //from   w  w w.java  2s.  co  m
 * @param cell
 *            The Excel cell.
 * @return Return the parsed value of the cell as an ExclyDouble.
 */
public ExclyDouble readDoubleCellValue(Cell cell) {
    ExclyDouble output = null;

    if (cell == null) {
        return new ExclyDoubleError();
    }

    try {
        output = readDouble(cell, cell.getCellType());
    } catch (Exception e) {
        log.error("The reader was unable to read the data from cell [Row, Column] (" + cell.getRowIndex() + ", "
                + cell.getColumnIndex() + ")", e);
        output = new ExclyDoubleError();
    }

    return output;
}