Example usage for org.apache.poi.ss.usermodel Cell getCellType

List of usage examples for org.apache.poi.ss.usermodel Cell getCellType

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getCellType

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell getCellType.

Prototype

CellType getCellType();

Source Link

Document

Return the cell type.

Usage

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 ww  w  .  j a v  a  2s .co 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:ImporteerExcelsheet.java

public void importeerExcelsheet(String filePath) {
    try {//from www  .  j  a va2  s.co  m
        FileInputStream file = new FileInputStream(new File(filePath));

        if (filePath.toLowerCase().indexOf(xlsx.toLowerCase()) != -1) {
            XSSFWorkbook wbXlsx = new XSSFWorkbook(file);
            sheet = wbXlsx.getSheetAt(0);
            Row row = sheet.getRow(3);
            Cell cell = row.getCell(1);

            try {
                // convert String dd-mm-yyyy naar Date yyyy-mm-dd 
                String excelDatum1 = cell.getStringCellValue();
                Date date = new SimpleDateFormat("dd-MM-yyyy").parse(excelDatum1);
                this.excelDatum = this.formatter.format(date);
            } catch (Exception e) {
                double date1 = cell.getNumericCellValue();
                //convert excel double naar datum
                Date date2 = DateUtil.getJavaDate((double) date1);
                this.excelDatum = new SimpleDateFormat("yyyy-MM-dd").format(date2);
            }
            //Iterate through each rows one by one
            rowIterator = sheet.iterator();
        } else {
            HSSFWorkbook wbXls = new HSSFWorkbook(file);
            sheet1 = wbXls.getSheetAt(0);
            Row row = sheet1.getRow(3);
            Cell cell = row.getCell(1);

            // convert String dd-mm-yyyy naar Date yyyy-mm-dd 
            try {
                String excelDatum1 = cell.getStringCellValue();
                Date date = new SimpleDateFormat("dd-MM-yyyy").parse(excelDatum1);
                this.excelDatum = this.formatter.format(date);
            } catch (Exception e) {
                double date1 = cell.getNumericCellValue();
                //convert excel double naar datum
                Date date2 = DateUtil.getJavaDate((double) date1);
                this.excelDatum = new SimpleDateFormat("yyyy-MM-dd").format(date2);
            }
            //Iterate through each rows one by one
            rowIterator = sheet1.iterator();
        }

        //Iterate through each rows one by one
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //skip first 5 rows
            if (row.getRowNum() == 0 || row.getRowNum() == 1 || row.getRowNum() == 2 || row.getRowNum() == 3
                    || row.getRowNum() == 4) {
                continue;
            }

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

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    //                            System.out.print(cell.getNumericCellValue() + "\t");
                    break;
                case Cell.CELL_TYPE_STRING:
                    //                            System.out.print(cell.getStringCellValue() + "\t");
                    break;
                case Cell.CELL_TYPE_BLANK:
                    //                            System.out.print(cell.getStringCellValue() + "\t");
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    //                            System.out.print(cell.getStringCellValue() + "\t");
                    break;
                }
            }
            // Tabel client
            int kaartnummer = (int) row.getCell(0).getNumericCellValue();
            String naam = row.getCell(1).getStringCellValue();
            String naamPartner = row.getCell(2).getStringCellValue();
            String telefoonnummer = row.getCell(3).getStringCellValue();
            String email = row.getCell(4).getStringCellValue();
            String mobiel = row.getCell(5).getStringCellValue();
            int aantalPersonen = (int) row.getCell(6).getNumericCellValue();
            int aantalPersonenInDeNorm = (int) row.getCell(7).getNumericCellValue();
            double gebruikInMaanden = (double) row.getCell(8).getNumericCellValue();
            String idSoort = row.getCell(9).getStringCellValue();
            //convert excel double naar datum
            double datumUitgifteId1 = row.getCell(10).getNumericCellValue();
            if (datumUitgifteId1 == 0.0) {
                this.datumUitgifteId = null;
            } else {
                Date datumUitgifteId2 = DateUtil.getJavaDate((double) datumUitgifteId1);
                this.datumUitgifteId = new SimpleDateFormat("yyyy-MM-dd").format(datumUitgifteId2);
            }
            ////////////////////////////////////////////////////////////////////////////////////
            String idNummer = row.getCell(11).getStringCellValue();
            String plaatsUitgifteId = row.getCell(12).getStringCellValue();
            String adres = row.getCell(13).getStringCellValue();
            String postcode = row.getCell(14).getStringCellValue();
            String plaats = row.getCell(15).getStringCellValue();
            String status = row.getCell(16).getStringCellValue();

            // Tabel intake
            // kaartnummer gaat hier ook in de query
            String intaker = row.getCell(17).getStringCellValue();
            double intakeDatum1 = row.getCell(18).getNumericCellValue();
            //convert excel double naar datum
            Date intakeDatum2 = DateUtil.getJavaDate((double) intakeDatum1);
            String intakeDatum = new SimpleDateFormat("yyyy-MM-dd").format(intakeDatum2);
            /////////////////////////////////////////////////////////////////////////
            double startDatumUitgifte1 = row.getCell(19).getNumericCellValue();
            //convert excel double naar datum
            Date startDatumUitgifte2 = DateUtil.getJavaDate((double) startDatumUitgifte1);
            String startDatumUitgifte = new SimpleDateFormat("yyyy-MM-dd").format(startDatumUitgifte2);
            //////////////////////////////////////////////////////////////////////////////////
            double datumHerintake1 = row.getCell(20).getNumericCellValue();
            //convert excel double naar datum
            Date datumHerintake2 = DateUtil.getJavaDate((double) datumHerintake1);
            String datumHerintake = new SimpleDateFormat("yyyy-MM-dd").format(datumHerintake2);
            /////////////////////////////////////////////////////////////////////////////////
            // Tabel Stopt
            double datumStopzetting1 = row.getCell(21).getNumericCellValue();
            //convert excel numbers naar datum
            Date datumStopzetting2 = DateUtil.getJavaDate((double) datumStopzetting1);
            String datumStopzetting = new SimpleDateFormat("yyyy-MM-dd").format(datumStopzetting2);
            //////////////////////////////////////////////////////////////////////////////////////
            String redenStopzetting = row.getCell(22).getStringCellValue();

            // Tabel verwijzer
            String verwijzerNaam = row.getCell(23).getStringCellValue();
            String verwijzersDoorContactpersoon1 = row.getCell(24).getStringCellValue();
            String verwijzersDoorContactpersoon = verwijzersDoorContactpersoon1.toLowerCase();
            String verwijzersDoorTelefoonnummer = row.getCell(25).getStringCellValue();
            String verwijzersDoorEmail = row.getCell(26).getStringCellValue();
            String verwijzersNaar = row.getCell(27).getStringCellValue();
            String verwijzersNaarContactpersoon = row.getCell(28).getStringCellValue();
            String verwijzersNaarTelefoonnummer = row.getCell(29).getStringCellValue();
            String verwijzersNaarEmail = row.getCell(30).getStringCellValue();

            // Uitgiftepunt gaat in de tabel intake
            String uitgiftepunt = row.getCell(31).getStringCellValue();

            // PakketSoort gaat in de tabel client
            String pakketSoort = row.getCell(32).getStringCellValue();

            // Query's om de excelsheet in de database te krijgen
            SQLExcelSheetInsert excelSQL = new SQLExcelSheetInsert();
            SQLget getSQL = new SQLget();

            // Losse query uit de tabel om bepaalde gegevens te krijgen om te controleren of die al bestaat of niet
            int Verwijzer = getSQL.getVerwijzernr(verwijzerNaam, verwijzersDoorContactpersoon);
            int checkKaartnr = getSQL.getCheckKaartnummer(kaartnummer);
            int checkUitgiftepunt = getSQL.getUitgiftepunt(uitgiftepunt);

            if (Verwijzer == 0) {
                excelSQL.insertExcelVerwijzer(verwijzerNaam, verwijzersDoorContactpersoon,
                        verwijzersDoorTelefoonnummer, verwijzersDoorEmail, verwijzersNaar,
                        verwijzersNaarContactpersoon, verwijzersNaarTelefoonnummer, verwijzersNaarEmail);
            }

            if (checkUitgiftepunt == 0) {
                int maxVolgorde = getSQL.getCheckVolgordeLijst();
                excelSQL.insertUitgiftepunt(uitgiftepunt, maxVolgorde);
            }

            int Verwijzer2 = getSQL.getVerwijzernr(verwijzerNaam, verwijzersDoorContactpersoon);

            /// Wanneer er geen kaartnummer is die bekend gaat die door naar insert
            if (checkKaartnr == 0) {
                // Wanneer kaartnummer alles behalve 0 is insert die
                if (kaartnummer > 0) {
                    excelSQL.insertExcelClient(kaartnummer, naam, naamPartner, telefoonnummer, email, mobiel,
                            aantalPersonen, aantalPersonenInDeNorm, gebruikInMaanden, idSoort,
                            this.datumUitgifteId, idNummer, plaatsUitgifteId, adres, postcode, plaats, status,
                            pakketSoort, Verwijzer2);
                } else {
                    break;
                }
            } else {
                excelSQL.updateExcelClient(kaartnummer, naam, naamPartner, telefoonnummer, email, mobiel,
                        aantalPersonen, aantalPersonenInDeNorm, gebruikInMaanden, idSoort, this.datumUitgifteId,
                        idNummer, plaatsUitgifteId, adres, postcode, plaats, status, pakketSoort, Verwijzer2);
            }

            int checkIntake = getSQL.getCheckIntake(intakeDatum, startDatumUitgifte, datumHerintake,
                    kaartnummer);

            if (checkIntake == 0) {
                excelSQL.insertExcelIntake(intaker, intakeDatum, startDatumUitgifte, datumHerintake,
                        kaartnummer, uitgiftepunt);
                int intakeId = getSQL.getIntakeId(kaartnummer);
                excelSQL.insertExcelStopt(datumStopzetting, redenStopzetting, intakeId);
            }

            // Kan upgedate worden aan een knop voor een query aan status in de table voedselpakket wel of niet opgehaald
            String status1 = null;

            String checkStatus = getSQL.getStatus(kaartnummer);
            this.pakketAantal = getSQL.getPakketAantal(kaartnummer);
            int intakeId = getSQL.getIntakeId(kaartnummer);
            int checkPakket = getSQL.getPakket(this.excelDatum, intakeId);

            if (checkPakket == 0) {
                if (checkStatus != null) {
                    if (checkStatus.equals("Actief")) {
                        int intakeId2 = getSQL.getIntakeId(kaartnummer);
                        excelSQL.insertVoedselpakket(this.excelDatum, this.pakketAantal, status1, intakeId2,
                                uitgiftepunt);
                    }
                }
            }
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

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  w w. j ava2 s.com
    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:xlsxtocsv.java

static void convertToXlsx(File inputFile, File outputFile) {
    // For storing data into CSV files
    StringBuffer cellValue = new StringBuffer();
    try {/*from  w  ww .  j  ava2  s.  co m*/
        FileOutputStream fos = new FileOutputStream(outputFile);

        // Get the workbook instance for XLSX file
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));

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

        Row row;
        Cell cell;

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                cell = cellIterator.next();

                switch (cell.getCellType()) {

                case Cell.CELL_TYPE_BOOLEAN:
                    cellValue.append(cell.getBooleanCellValue() + ",");
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    cellValue.append(cell.getNumericCellValue() + ",");
                    break;

                case Cell.CELL_TYPE_STRING:
                    cellValue.append(cell.getStringCellValue() + ",");
                    break;

                case Cell.CELL_TYPE_BLANK:
                    cellValue.append("" + ",");
                    break;

                default:
                    cellValue.append(cell + ",");

                }
            }

            cellValue.append("\n");
        }

        fos.write(cellValue.toString().getBytes());
        fos.close();

    } catch (Exception e) {
        System.err.println("Exception :" + e.getMessage());
    }
}

From source file:xlsxtocsv.java

static void convertToXls(File inputFile, File outputFile) {
    // For storing data into CSV files
    StringBuffer cellDData = new StringBuffer();
    try {/*from ww  w . j av  a  2  s .co m*/
        FileOutputStream fos = new FileOutputStream(outputFile);

        // Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
        // Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);
        Cell cell;
        Row row;

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                cell = cellIterator.next();

                switch (cell.getCellType()) {

                case Cell.CELL_TYPE_BOOLEAN:
                    cellDData.append(cell.getBooleanCellValue() + ",");
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    cellDData.append(cell.getNumericCellValue() + ",");
                    break;

                case Cell.CELL_TYPE_STRING:
                    cellDData.append(cell.getStringCellValue() + ",");
                    break;

                case Cell.CELL_TYPE_BLANK:
                    cellDData.append("" + ",");
                    break;

                default:
                    cellDData.append(cell + ",");
                }
            }

            cellDData.append("\n");
        }

        fos.write(cellDData.toString().getBytes());
        fos.close();

    } catch (FileNotFoundException e) {
        System.err.println("Exception" + e.getMessage());
    } catch (IOException e) {
        System.err.println("Exception" + e.getMessage());
    }
}

From source file:action.FacultyAction.java

public String callAddFacultyExcel() {
    System.out.println("we are in Faculty Action excel");
    try {/* ww w . ja v a2s .  c  o m*/
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(myExcelSheet));

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

        Row row;
        Cell cell;

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        int i = 0;
        row = rowIterator.next();
        while (rowIterator.hasNext()) {
            row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                cell = cellIterator.next();

                switch (cell.getCellType()) {

                case Cell.CELL_TYPE_STRING: {
                    if (i == 0) {
                        fact.setFactId(cell.getStringCellValue());
                    }
                    if (i == 1) {
                        fact.setFacultyName(cell.getStringCellValue());
                    }
                    if (i == 2) {
                        fact.setFacultyEmail(cell.getStringCellValue());
                    }
                    if (i == 3) {
                        fact.setFacultyAddress(cell.getStringCellValue());
                    }
                    if (i == 4) {
                        fact.setFacultyDept(cell.getStringCellValue());
                    }
                }

                    break;
                }
                i++;
            }
            i = 0;
            System.out.println("Object from excel : " + fact);
            callSaveFaculty();
            System.out.println("Our Obeject");

        }
        System.out.println("");
    } catch (Exception ex) {
        ex.printStackTrace();

    }
    System.out.println("We end");
    return SUCCESS;

}

From source file:action.ResultAction.java

public String callReadExecl() {
    System.out.println("we are here");
    String flag = "success";
    try {/*  ww  w.  j  a va2s . c  o  m*/
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(myExcelSheet));

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

        Row row;
        Cell cell;

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        int i = 0;
        row = rowIterator.next();
        while (rowIterator.hasNext()) {
            row = rowIterator.next();
            System.out.println("iterator Strated");
            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            int pra[] = new int[4];
            int sub[] = new int[5];
            int j = 0, k = 0, total = 0;
            while (cellIterator.hasNext()) {
                cell = cellIterator.next();

                switch (cell.getCellType()) {

                case Cell.CELL_TYPE_NUMERIC:
                    int a = (int) cell.getNumericCellValue();
                    total = total + a;
                    if (j < 5) {
                        sub[j] = a;
                        j++;
                    } else if (k < 4) {
                        pra[k] = a;
                        k++;
                    }
                    if (i == 2) {
                        stdR.setSemester(a);
                    } else if (i == 3) {
                        stdR.setSub1(a);
                    } else if (i == 4) {
                        stdR.setSub2(a);
                    } else if (i == 5) {
                        stdR.setSub3(a);
                    } else if (i == 6) {
                        stdR.setSub4(a);
                    } else if (i == 7) {
                        stdR.setSub5(a);
                    } else if (i == 8) {
                        stdR.setPra1(a);
                    } else if (i == 9) {
                        stdR.setPra2(a);
                    } else if (i == 10) {
                        stdR.setPra3(a);
                    } else if (i == 11) {
                        stdR.setPra4(a);
                    } else if (i == 12) {
                        stdR.setTotal(a);
                    } else if (i == 13) {
                        stdR.setPercentage((long) cell.getNumericCellValue());
                    }
                    break;

                case Cell.CELL_TYPE_STRING: {
                    if (i == 0) {
                        stdR.setStdRoll(cell.getStringCellValue());
                    } else if (i == 1) {
                        stdR.setBranch(cell.getStringCellValue());
                    }
                }

                    break;
                }
                i++;
            }
            i = 0;
            stdR.setTotal(total);
            System.out.println("Total");
            System.out.println("Student marks : " + stdR);
            subId.setSemid(stdR.getSemester());
            subId.setBranch(stdR.getBranch());
            subject = subDao.getAllSubject(subId);
            stdEx.setStdRollEx(stdR.getStdRoll());
            getStudentExSubject(stdEx);
            if (stdR.getPra1() < 13) {
                list.add(subject.getPra1() + "_");
            }
            if (stdR.getPra2() < 13) {
                list.add(subject.getPra2() + "_");
            }
            if (stdR.getPra3() < 13) {
                list.add(subject.getPra3() + "_");
            }
            if (stdR.getPra4() < 13) {
                list.add(subject.getPra4() + "_");
            }
            if (stdR.getSub1() < 25) {
                list.add(subject.getSub1() + "_");
            }
            if (stdR.getSub2() < 25) {
                list.add(subject.getSub2() + "_");
            }
            if (stdR.getSub3() < 25) {
                list.add(subject.getSub3() + "_");
            }
            if (stdR.getSub4() < 25) {
                list.add(subject.getSub4() + "_");
            }
            if (stdR.getSub5() < 25) {
                list.add(subject.getSub5() + "_");
            }
            String subj = "";
            for (int ii = 0; ii < list.size(); ii++) {
                subj += list.get(ii);
            }

            System.out.println("Subject Ka object : " + subject);
            System.err.println("Student Object : " + stdDetails);
            System.err.println("Student Marks Object : " + stdR);
            System.err.println("Student Ex : " + stdEx);
            stdEx.setStudentExDetailscol(subj);
            stdDetails.setStdRollnumber(stdR.getStdRoll());
            stdDetails = stdDao.getSingleSudentData(stdDetails);
            stdDetails.setStdMarks(stdR);
            stdDetails.setStudentexdetails(stdEx);
            stdR.setStudentDetails(stdDetails);
            stdEx.setStudentDetails(stdDetails);
            //flag = stdDao.updateStudent(stdDetails);
            System.out.println("Subject Ka object : " + subject);
            System.err.println("Student Object : " + stdDetails);
            System.err.println("Student Marks Object : " + stdR);
            System.err.println("Student Ex : " + stdEx);
            callsaveMarks();
            System.out.println("iteration ended");

        }
        System.out.println("");
    } catch (Exception ex) {
        ex.printStackTrace();

    }
    System.out.println("We end");
    return flag;
}

From source file:action.StudentAction.java

public String callGetStudentExcel() {
    System.out.println("we are here");
    try {/*  w w w  .  j a v  a 2  s .  c om*/
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(myExcelSheet));

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

        Row row;
        Cell cell;

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        int i = 0;
        row = rowIterator.next();
        while (rowIterator.hasNext()) {
            row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                cell = cellIterator.next();

                switch (cell.getCellType()) {

                case Cell.CELL_TYPE_NUMERIC:
                    if (i == 5) {
                        stdDetails.setStdSemester((int) cell.getNumericCellValue());
                    } else if (i == 7) {
                        stdDetails.setStdContact((long) cell.getNumericCellValue());
                    }
                    break;

                case Cell.CELL_TYPE_STRING: {
                    if (i == 0) {
                        stdDetails.setStdRollnumber(cell.getStringCellValue());
                    } else if (i == 1) {
                        stdDetails.setStdName(cell.getStringCellValue());
                    } else if (i == 2) {
                        stdDetails.setStdEmail(cell.getStringCellValue());
                    } else if (i == 3) {
                        stdDetails.setStdBatch(cell.getStringCellValue());
                    } else if (i == 4) {
                        stdDetails.setStdBranch(cell.getStringCellValue());
                    } else if (i == 6) {
                        stdDetails.setStdAddress(cell.getStringCellValue());
                    }
                }

                    break;
                }
                i++;
            }
            i = 0;
            callSaveStudent();
            System.out.println("Our Obeject");
            System.out.println(stdDetails);
        }
        System.out.println("");
    } catch (Exception ex) {
        ex.printStackTrace();

    }
    System.out.println("We end");
    return SUCCESS;
}

From source file:Actionclasses.ReadExcel.java

public ReadExcel(String filePath) {
    filelocation = filePath;/*from  w w w. ja v a2s . c om*/
    try {
        FileInputStream file = new FileInputStream(new File(filelocation));
        //new workbook is created
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        //new sheet is created
        XSSFSheet sheet = workbook.getSheetAt(0);
        /*rowIterator - Returns an iterator of the physical rows
           iterator - Alias for rowIterator() to allow foreach loops
        So basically they return the same values, but the second was added 
        to support Java's for-each loop. In other words, instead of getting the iterator
        and running while loop, you could directly run for-each loop, which makes code 
        shorter and more readable
                     */
        //Creating random table
        Insert();
        //loop for iterate in each row
        for (Row row : sheet) {
            //List list=new ArrayList();
            //loop for iterate in each cell in a particular row
            for (Cell cell : row) {
                cell.setCellType(CELL_TYPE_STRING);
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.println("boolean===>>>\"+cell.getBooleanCellValue() + \"\\t");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    break;
                case Cell.CELL_TYPE_STRING:
                    //list.add(cell.getStringCellValue());
                    System.out.println(cell.getStringCellValue());
                    break;
                }
            }
            String Name = row.getCell(0).getStringCellValue();
            String Emailid = row.getCell(1).getStringCellValue();
            System.out.println(Name + " " + Emailid);
            Insertindb(Name, Emailid);
        }
        //file.close();
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ReadExcel.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(ReadExcel.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:adams.data.io.input.ExcelSpreadSheetReader.java

License:Open Source License

/**
 * Reads the spreadsheet content from the specified file.
 *
 * @param in      the input stream to read from
 * @return      the spreadsheets or null in case of an error
 *///ww  w  .j ava 2  s. co m
@Override
protected List<SpreadSheet> doReadRange(InputStream in) {
    List<SpreadSheet> result;
    int[] indices;
    Workbook workbook;
    Sheet sheet;
    SpreadSheet spsheet;
    Row exRow;
    Cell exCell;
    adams.data.spreadsheet.Row spRow;
    int i;
    int n;
    int cellType;
    DateFormat dformat;
    boolean numeric;
    int dataRowStart;
    int firstRow;
    int lastRow;
    List<String> header;

    result = new ArrayList<>();

    workbook = null;
    dformat = DateUtils.getTimestampFormatter();
    try {
        workbook = WorkbookFactory.create(in);
        m_SheetRange.setMax(workbook.getNumberOfSheets());
        indices = m_SheetRange.getIntIndices();
        firstRow = m_FirstRow - 1;
        dataRowStart = getNoHeader() ? firstRow : firstRow + 1;
        for (int index : indices) {
            if (m_Stopped)
                break;

            spsheet = m_SpreadSheetType.newInstance();
            spsheet.setDataRowClass(m_DataRowType.getClass());
            result.add(spsheet);

            if (isLoggingEnabled())
                getLogger().info("sheet: " + (index + 1));

            sheet = workbook.getSheetAt(index);
            if (sheet.getLastRowNum() == 0) {
                getLogger().severe("No rows in sheet #" + index);
                return null;
            }
            spsheet.setName(sheet.getSheetName());

            // header
            if (isLoggingEnabled())
                getLogger().info("header row");
            exRow = sheet.getRow(firstRow);
            if (exRow == null) {
                getLogger().warning("No data in sheet #" + (index + 1) + "?");
            } else if (exRow != null) {
                spRow = spsheet.getHeaderRow();
                m_TextColumns.setMax(exRow.getLastCellNum());
                if (getNoHeader()) {
                    header = SpreadSheetUtils.createHeader(exRow.getLastCellNum(), m_CustomColumnHeaders);
                    for (i = 0; i < header.size(); i++)
                        spRow.addCell("" + (i + 1)).setContent(header.get(i));
                } else {
                    if (!m_CustomColumnHeaders.trim().isEmpty()) {
                        header = SpreadSheetUtils.createHeader(exRow.getLastCellNum(), m_CustomColumnHeaders);
                        for (i = 0; i < header.size(); i++)
                            spRow.addCell("" + (i + 1)).setContent(header.get(i));
                    } else {
                        for (i = 0; i < exRow.getLastCellNum(); i++) {
                            if (m_Stopped)
                                break;
                            exCell = exRow.getCell(i);
                            if (exCell == null) {
                                spRow.addCell("" + (i + 1)).setMissing();
                                continue;
                            }
                            numeric = !m_TextColumns.isInRange(i);
                            switch (exCell.getCellType()) {
                            case Cell.CELL_TYPE_BLANK:
                            case Cell.CELL_TYPE_ERROR:
                                spRow.addCell("" + (i + 1)).setContent("column-" + (i + 1));
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if (HSSFDateUtil.isCellDateFormatted(exCell))
                                    spRow.addCell("" + (i + 1)).setContent(new DateTime(
                                            HSSFDateUtil.getJavaDate(exCell.getNumericCellValue())));
                                else if (numeric)
                                    spRow.addCell("" + (i + 1)).setContent(exCell.getNumericCellValue());
                                else
                                    spRow.addCell("" + (i + 1)).setContentAsString(numericToString(exCell));
                                break;
                            default:
                                spRow.addCell("" + (i + 1)).setContentAsString(exCell.getStringCellValue());
                            }
                        }
                    }
                }
            }

            // data
            if (spsheet.getColumnCount() > 0) {
                if (m_NumRows < 1)
                    lastRow = sheet.getLastRowNum();
                else
                    lastRow = Math.min(firstRow + m_NumRows - 1, sheet.getLastRowNum());
                for (i = dataRowStart; i <= lastRow; i++) {
                    if (m_Stopped)
                        break;
                    if (isLoggingEnabled())
                        getLogger().info("data row: " + (i + 1));
                    spRow = spsheet.addRow("" + spsheet.getRowCount());
                    exRow = sheet.getRow(i);
                    if (exRow == null)
                        continue;
                    for (n = 0; n < exRow.getLastCellNum(); n++) {
                        // too few columns in header?
                        if ((n >= spsheet.getHeaderRow().getCellCount()) && m_AutoExtendHeader)
                            spsheet.insertColumn(spsheet.getColumnCount(), "");

                        m_TextColumns.setMax(spsheet.getHeaderRow().getCellCount());
                        exCell = exRow.getCell(n);
                        if (exCell == null) {
                            spRow.addCell(n).setMissing();
                            continue;
                        }
                        cellType = exCell.getCellType();
                        if (cellType == Cell.CELL_TYPE_FORMULA)
                            cellType = exCell.getCachedFormulaResultType();
                        numeric = !m_TextColumns.isInRange(n);
                        switch (cellType) {
                        case Cell.CELL_TYPE_BLANK:
                        case Cell.CELL_TYPE_ERROR:
                            if (m_MissingValue.isEmpty())
                                spRow.addCell(n).setMissing();
                            else
                                spRow.addCell(n).setContent("");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(exCell))
                                spRow.addCell(n).setContent(
                                        dformat.format(HSSFDateUtil.getJavaDate(exCell.getNumericCellValue())));
                            else if (numeric)
                                spRow.addCell(n).setContent(exCell.getNumericCellValue());
                            else
                                spRow.addCell(n).setContentAsString(numericToString(exCell));
                            break;
                        default:
                            if (m_MissingValue.isMatch(exCell.getStringCellValue()))
                                spRow.addCell(n).setMissing();
                            else
                                spRow.addCell(n).setContentAsString(exCell.getStringCellValue());
                        }
                    }
                }
            }
        }
    } catch (Exception ioe) {
        getLogger().log(Level.SEVERE, "Failed to read range '" + m_SheetRange + "':", ioe);
        result = null;
        m_LastError = "Failed to read range '" + m_SheetRange + "' from stream!\n"
                + Utils.throwableToString(ioe);
    }

    return result;
}