Example usage for org.apache.poi.xssf.usermodel XSSFRow getCell

List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFRow
  6. getCell

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFRow getCell.

Prototype

@Override
public XSSFCell getCell(int cellnum)

Source Link

Document

Returns the cell at the given (0 based) index, with the org.apache.poi.ss.usermodel.Row.MissingCellPolicy from the parent Workbook.

Usage

From source file:in.expertsoftware.colorcheck.Reporting_Qtr_Verification.java

private boolean operation_Standard_Workingsection_Verification(int SORQ_tokenLocation,
        int first_Occurance_Of_Financial_Comparision, XSSFSheet Sheet,
        ArrayList<String> opeartion_standard_workingSectionList, ArrayList<ErrorModel> errorModelList,
        XSSFWorkbook workbook) {//from   w ww .  ja v  a  2  s .co  m
    int reporting_Qtr_OS_Working_SectionCount = 0;
    String reporting_Qtr_Formula_Cell_Formula;
    String reporting_Qtr_Formula_Cell_Value;
    boolean isError = false;
    XSSFRow row;
    XSSFCell cell;
    for (int start = (SORQ_tokenLocation - 1); start < (first_Occurance_Of_Financial_Comparision
            - 1); start++) {
        try {
            row = Sheet.getRow(start);
            cell = row.getCell(1);
            reporting_Qtr_Formula_Cell_Value = cell.getStringCellValue();
            if (reporting_Qtr_Formula_Cell_Value.equalsIgnoreCase("Operational Comparison")) {
                start = start + 1;
            } else if (reporting_Qtr_OS_Working_SectionCount < opeartion_standard_workingSectionList.size()) {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_FORMULA:
                    reporting_Qtr_Formula_Cell_Formula = cell.getCellFormula();
                    if (reporting_Qtr_Formula_Cell_Formula.contains("$")) {
                        reporting_Qtr_Formula_Cell_Formula = reporting_Qtr_Formula_Cell_Formula
                                .replaceAll("\\$", "").replaceAll(" ", "");
                    }
                    String retrive_Formula = opeartion_standard_workingSectionList
                            .get(reporting_Qtr_OS_Working_SectionCount);
                    if (reporting_Qtr_Formula_Cell_Formula.equals(retrive_Formula)) {
                        reporting_Qtr_OS_Working_SectionCount++;
                    } else {
                        ErrorModel errorModel = new ErrorModel();
                        CellReference cellRef = new CellReference(cell);
                        errorModel.setCell_ref(cellRef.formatAsString());
                        errorModel.setSheet_name("Reporting_Qtr");
                        errorModel.setError_desc(
                                "Sequence does not match Actual Sequence should be" + retrive_Formula);
                        errorModel.setError_level("Error");
                        errorModel.setRow((cell.getRowIndex() + 1));
                        errorModelList.add(errorModel);
                        reporting_Qtr_OS_Working_SectionCount++;
                        isError = true;
                    }
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;
                default: {
                    ErrorModel errorModel = new ErrorModel();
                    CellReference cellRef = new CellReference(cell);
                    errorModel.setCell_ref(cellRef.formatAsString());
                    errorModel.setSheet_name("Reporting_Qtr");
                    errorModel.setError_desc("Cell does not contain formula");
                    errorModel.setError_level("Error");
                    errorModel.setRow((cell.getRowIndex() + 1));
                    errorModelList.add(errorModel);
                    reporting_Qtr_OS_Working_SectionCount++;
                    isError = true;
                }
                    break;
                }
            } else {
                reporting_Qtr_OS_Working_SectionCount++;
            }
        } catch (NullPointerException nullexcp) {
            continue;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    if (reporting_Qtr_OS_Working_SectionCount != opeartion_standard_workingSectionList.size()) {
        ErrorModel errorModel = new ErrorModel();
        errorModel.setSheet_name("Reporting_Qtr");
        errorModel.setError_desc("Reporting_Qtr have "
                + Math.abs(reporting_Qtr_OS_Working_SectionCount - opeartion_standard_workingSectionList.size())
                + " extra rows from Operation_Standard");
        errorModel.setError_level("Error");
        errorModelList.add(errorModel);
        isError = true;
    }
    return isError;
}

From source file:in.expertsoftware.colorcheck.Reporting_Qtr_Verification.java

private boolean financial_Standard_Workingsection_Verification(int first_Occurance_Of_Financial_Comparision,
        int EORQ_tokenLocation, XSSFSheet Sheet, ArrayList<String> financial_standard_workingSectionList,
        ArrayList<ErrorModel> errorModelList, XSSFWorkbook workbook) {
    int reporting_Qtr_FS_Working_SectionCount = 0;
    String reporting_Qtr_Formula_Cell_Formula = null;
    String reporting_Qtr_Formula_Cell_Value;
    XSSFRow row;
    XSSFCell cell;/*  w  ww  .j  av  a2s  . c o  m*/
    boolean isError = false;
    for (int start = (first_Occurance_Of_Financial_Comparision - 1); start < EORQ_tokenLocation; start++) {
        try {
            row = Sheet.getRow(start);
            cell = row.getCell(1);
            reporting_Qtr_Formula_Cell_Value = cell.getStringCellValue();
            if (reporting_Qtr_Formula_Cell_Value.equals("Financial Comparison")) {
                start = start + 3;
            } else if (reporting_Qtr_FS_Working_SectionCount < financial_standard_workingSectionList.size()) {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_FORMULA:
                    reporting_Qtr_Formula_Cell_Formula = cell.getCellFormula();
                    if (reporting_Qtr_Formula_Cell_Formula.contains("$")) {
                        reporting_Qtr_Formula_Cell_Formula = reporting_Qtr_Formula_Cell_Formula
                                .replaceAll("\\$", "").replaceAll(" ", "");
                    }
                    String retrive_Formula = financial_standard_workingSectionList
                            .get(reporting_Qtr_FS_Working_SectionCount);
                    if (reporting_Qtr_Formula_Cell_Formula.equals(retrive_Formula)) {
                        reporting_Qtr_FS_Working_SectionCount++;
                    } else {
                        ErrorModel errorModel = new ErrorModel();
                        CellReference cellRef = new CellReference(cell);
                        errorModel.setCell_ref(cellRef.formatAsString());
                        errorModel.setSheet_name("Reporting_Qtr");
                        errorModel.setError_desc(
                                "Sequence does not match Actual Sequence should be" + retrive_Formula);
                        errorModel.setError_level("Error");
                        errorModel.setRow((cell.getRowIndex() + 1));
                        errorModelList.add(errorModel);
                        reporting_Qtr_FS_Working_SectionCount++;
                        isError = true;
                    }
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;
                default: {
                    ErrorModel errorModel = new ErrorModel();
                    CellReference cellRef = new CellReference(cell);
                    errorModel.setCell_ref(cellRef.formatAsString());
                    errorModel.setSheet_name("Reporting_Qtr");
                    errorModel.setError_desc("Cell does not contain formula ");
                    errorModel.setError_level("Error");
                    errorModel.setRow((cell.getRowIndex() + 1));
                    errorModelList.add(errorModel);
                    reporting_Qtr_FS_Working_SectionCount++;
                    isError = true;
                }
                    break;
                }
            } else {
                reporting_Qtr_FS_Working_SectionCount++;
            }
        } catch (NullPointerException nullexcp) {
            continue;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    if (reporting_Qtr_FS_Working_SectionCount != financial_standard_workingSectionList.size()) {
        ErrorModel errorModel = new ErrorModel();
        errorModel.setSheet_name("Reporting_Qtr");
        errorModel.setError_desc("Reporting_Qtr have "
                + Math.abs(reporting_Qtr_FS_Working_SectionCount - financial_standard_workingSectionList.size())
                + " extra rows from Operation_Standard");
        errorModel.setError_level("Error");
        errorModelList.add(errorModel);
        isError = true;
    }
    return isError;
}

From source file:in.expertsoftware.colorcheck.Reporting_Qtr_Verification.java

private void reporting_Qtr_operation_Standard_C_To_I_Column_Verification(int start_Point,
        int first_Occurance_Of_Financial_Comparision, ArrayList<ErrorModel> errorModelList, XSSFSheet Sheet,
        XSSFWorkbook workbook) {/* w  w  w .j  av  a2 s .  c  om*/
    String reporting_Qtr_Formula_Cell_Formula = null;
    String reporting_Qtr_Formula_Cell_Value;
    String reporting_Qtr_B_Column_Formula = null;
    XSSFRow row;
    XSSFCell cell_B, cell_C, cell_E = null, cell_F = null, cell_G = null, cell_H = null, cell_I = null;
    for (int start = (start_Point - 1); start < (first_Occurance_Of_Financial_Comparision - 1); start++) {
        try {
            row = Sheet.getRow(start);
            cell_B = row.getCell(1);
            cell_C = row.getCell(2);
            cell_E = row.getCell(4);
            cell_F = row.getCell(5);
            cell_G = row.getCell(6);
            cell_H = row.getCell(7);
            cell_I = row.getCell(8);
            switch (cell_C.getCellType()) {
            case Cell.CELL_TYPE_FORMULA:
                reporting_Qtr_Formula_Cell_Formula = cell_C.getCellFormula();
                if (reporting_Qtr_Formula_Cell_Formula.contains("$")) {
                    reporting_Qtr_Formula_Cell_Formula = reporting_Qtr_Formula_Cell_Formula
                            .replaceAll("\\$", "").replaceAll(" ", "");
                }

                //verify the formula is correct or not.
                if ((cell_B.getCellType() == Cell.CELL_TYPE_FORMULA)
                        && (reporting_Qtr_Formula_Cell_Formula.charAt(19) == 'D')) {
                    if (!(cell_B.getCellFormula().substring(20, cell_B.getCellFormula().length())
                            .equals(reporting_Qtr_Formula_Cell_Formula.substring(20,
                                    reporting_Qtr_Formula_Cell_Formula.length())))) {
                        genrateError(cell_C, errorModelList, "Operation_Standard");
                    }
                }
                //else throw an error
                else {
                    genrateError(cell_C, errorModelList, "Operation_Standard");
                }
                verify_E(cell_E, cell_B, errorModelList);
                verify_F(cell_F, cell_B, errorModelList);
                verify_G(cell_G, cell_B, errorModelList);
                verify_H(cell_H, cell_B, errorModelList);
                verify_I(cell_I, cell_B, errorModelList);
                break;
            case Cell.CELL_TYPE_STRING:
                reporting_Qtr_Formula_Cell_Value = cell_C.getStringCellValue();
                if (reporting_Qtr_Formula_Cell_Value.contains("USD")) {

                } else if (reporting_Qtr_Formula_Cell_Value.equalsIgnoreCase("Unit")) {
                    start = start + 4;
                } else {
                    genrateError(cell_C, errorModelList, "Operation_Standard");
                }
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            default:
                genrateError(cell_C, errorModelList, "Operation_Standard");
                break;
            }
        } catch (NullPointerException nullexcp) {
            continue;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

From source file:in.expertsoftware.colorcheck.Reporting_Year_Verification.java

private boolean operation_Standard_Workingsection_Verification(int SORY_tokenLocation,
        int first_Occurance_Of_Financial_Comparision, XSSFSheet Sheet,
        ArrayList<String> opeartion_standard_workingSectionList, ArrayList<ErrorModel> errorModelList,
        XSSFWorkbook workbook) {//from   w w  w  .ja  v a  2  s  .co m
    int reporting_Year_OS_Working_SectionCount = 0;
    String reporting_Year_Formula_Cell_Formula;
    String reporting_Year_Formula_Cell_Value;
    boolean isError = false;
    XSSFRow row;
    XSSFCell cell;
    for (int start = (SORY_tokenLocation - 1); start < (first_Occurance_Of_Financial_Comparision
            - 1); start++) {
        try {
            row = Sheet.getRow(start);
            cell = row.getCell(1);
            reporting_Year_Formula_Cell_Value = cell.getStringCellValue();
            if (reporting_Year_Formula_Cell_Value.equalsIgnoreCase("Operational Comparison")) {
                start = start + 1;
            } else if (reporting_Year_OS_Working_SectionCount < opeartion_standard_workingSectionList.size()) {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_FORMULA:
                    reporting_Year_Formula_Cell_Formula = cell.getCellFormula();
                    if (reporting_Year_Formula_Cell_Formula.contains("$")) {
                        reporting_Year_Formula_Cell_Formula = reporting_Year_Formula_Cell_Formula
                                .replaceAll("\\$", "").replaceAll(" ", "");
                    }
                    String retrive_Formula = opeartion_standard_workingSectionList
                            .get(reporting_Year_OS_Working_SectionCount);
                    if (reporting_Year_Formula_Cell_Formula.equals(retrive_Formula)) {
                        reporting_Year_OS_Working_SectionCount++;
                    } else {
                        ErrorModel errorModel = new ErrorModel();
                        CellReference cellRef = new CellReference(cell);
                        errorModel.setCell_ref(cellRef.formatAsString());
                        errorModel.setSheet_name("Reporting_Year");
                        errorModel.setError_desc(
                                "Sequence does not match Actual Sequence should be" + retrive_Formula);
                        errorModel.setError_level("Error");
                        errorModel.setRow((cell.getRowIndex() + 1));
                        errorModelList.add(errorModel);
                        reporting_Year_OS_Working_SectionCount++;
                        isError = true;
                    }
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;
                default: {
                    ErrorModel errorModel = new ErrorModel();
                    CellReference cellRef = new CellReference(cell);
                    errorModel.setCell_ref(cellRef.formatAsString());
                    errorModel.setSheet_name("Reporting_Year");
                    errorModel.setError_desc("Cell does not contain formula");
                    errorModel.setError_level("Error");
                    errorModel.setRow((cell.getRowIndex() + 1));
                    errorModelList.add(errorModel);
                    reporting_Year_OS_Working_SectionCount++;
                    isError = true;
                }
                    break;
                }
            } else {
                reporting_Year_OS_Working_SectionCount++;
            }
        } catch (NullPointerException nullexcp) {
            continue;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    if (reporting_Year_OS_Working_SectionCount != opeartion_standard_workingSectionList.size()) {
        ErrorModel errorModel = new ErrorModel();
        errorModel.setSheet_name("Reporting_Year");
        errorModel.setError_desc("Reporting_Year have "
                + Math.abs(
                        reporting_Year_OS_Working_SectionCount - opeartion_standard_workingSectionList.size())
                + " extra rows from Operation_Standard");
        errorModel.setError_level("Error");
        errorModelList.add(errorModel);
        isError = true;
    }
    return isError;
}

From source file:in.expertsoftware.colorcheck.Reporting_Year_Verification.java

private boolean financial_Standard_Workingsection_Verification(int first_Occurance_Of_Financial_Comparision,
        int EORY_tokenLocation, XSSFSheet Sheet, ArrayList<String> financial_standard_workingSectionList,
        ArrayList<ErrorModel> errorModelList, XSSFWorkbook workbook) {
    int reporting_Year_FS_Working_SectionCount = 0;
    String reporting_Year_Formula_Cell_Formula = null;
    String reporting_Year_Formula_Cell_Value;
    XSSFRow row;
    XSSFCell cell;//from  w  w  w . ja v a  2s  .  com
    boolean isError = false;
    for (int start = (first_Occurance_Of_Financial_Comparision - 1); start < EORY_tokenLocation; start++) {
        try {
            row = Sheet.getRow(start);
            cell = row.getCell(1);
            reporting_Year_Formula_Cell_Value = cell.getStringCellValue();
            if (reporting_Year_Formula_Cell_Value.equalsIgnoreCase("Financial Comparison")) {
                start = start + 3;
            } else if (reporting_Year_FS_Working_SectionCount < financial_standard_workingSectionList.size()) {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_FORMULA:
                    reporting_Year_Formula_Cell_Formula = cell.getCellFormula();
                    if (reporting_Year_Formula_Cell_Formula.contains("$")) {
                        reporting_Year_Formula_Cell_Formula = reporting_Year_Formula_Cell_Formula
                                .replaceAll("\\$", "").replaceAll(" ", "");
                    }
                    String retrive_Formula = financial_standard_workingSectionList
                            .get(reporting_Year_FS_Working_SectionCount);
                    if (reporting_Year_Formula_Cell_Formula.equals(retrive_Formula)) {
                        reporting_Year_FS_Working_SectionCount++;
                    } else {
                        ErrorModel errorModel = new ErrorModel();
                        CellReference cellRef = new CellReference(cell);
                        errorModel.setCell_ref(cellRef.formatAsString());
                        errorModel.setSheet_name("Reporting_Year");
                        errorModel.setError_desc(
                                "Sequence does not match Actual Sequence should be" + retrive_Formula);
                        errorModel.setError_level("Error");
                        errorModel.setRow((cell.getRowIndex() + 1));
                        errorModelList.add(errorModel);
                        reporting_Year_FS_Working_SectionCount++;
                        isError = true;
                    }
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;
                default: {
                    ErrorModel errorModel = new ErrorModel();
                    CellReference cellRef = new CellReference(cell);
                    errorModel.setCell_ref(cellRef.formatAsString());
                    errorModel.setSheet_name("Reporting_Year");
                    errorModel.setError_desc("Cell does not contain formula ");
                    errorModel.setError_level("Error");
                    errorModel.setRow((cell.getRowIndex() + 1));
                    errorModelList.add(errorModel);
                    reporting_Year_FS_Working_SectionCount++;
                    isError = true;
                }
                    break;
                }
            } else {
                reporting_Year_FS_Working_SectionCount++;
            }
        } catch (NullPointerException nullexcp) {
            continue;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    if (reporting_Year_FS_Working_SectionCount != financial_standard_workingSectionList.size()) {
        ErrorModel errorModel = new ErrorModel();
        errorModel.setSheet_name("Reporting_Year");
        errorModel.setError_desc("Reporting_Year have "
                + Math.abs(
                        reporting_Year_FS_Working_SectionCount - financial_standard_workingSectionList.size())
                + " extra rows from Operation_Standard");
        errorModel.setError_level("Error");
        errorModelList.add(errorModel);
        isError = true;
    }
    return isError;
}

From source file:in.expertsoftware.colorcheck.Reporting_Year_Verification.java

private void reporting_Year_operation_Standard_C_To_I_Column_Verification(int start_Point,
        int first_Occurance_Of_Financial_Comparision, ArrayList<ErrorModel> errorModelList, XSSFSheet Sheet,
        XSSFWorkbook workbook) {/*from w  w  w .  ja  va  2 s .c  om*/
    String reporting_Year_Formula_Cell_Formula = null;
    String reporting_Year_Formula_Cell_Value;
    String reporting_Year_B_Column_Formula = null;
    XSSFRow row;
    XSSFCell cell_B, cell_C, cell_E = null, cell_F = null, cell_G = null, cell_H = null, cell_I = null;
    for (int start = (start_Point - 1); start < (first_Occurance_Of_Financial_Comparision - 1); start++) {
        try {
            row = Sheet.getRow(start);
            cell_B = row.getCell(1);
            cell_C = row.getCell(2);
            cell_E = row.getCell(4);
            cell_F = row.getCell(5);
            cell_G = row.getCell(6);
            cell_H = row.getCell(7);
            cell_I = row.getCell(8);
            switch (cell_C.getCellType()) {
            case Cell.CELL_TYPE_FORMULA:
                reporting_Year_Formula_Cell_Formula = cell_C.getCellFormula();
                if (reporting_Year_Formula_Cell_Formula.contains("$")) {
                    reporting_Year_Formula_Cell_Formula = reporting_Year_Formula_Cell_Formula
                            .replaceAll("\\$", "").replaceAll(" ", "");
                }

                //verify the formula is correct or not.
                if ((cell_B.getCellType() == Cell.CELL_TYPE_FORMULA)
                        && (reporting_Year_Formula_Cell_Formula.charAt(19) == 'D')) {
                    if (!(cell_B.getCellFormula().substring(20, cell_B.getCellFormula().length())
                            .equals(reporting_Year_Formula_Cell_Formula.substring(20,
                                    reporting_Year_Formula_Cell_Formula.length())))) {
                        genrateError(cell_C, errorModelList, "Operation_Standard");
                    }
                }
                //else throw an error
                else {
                    genrateError(cell_C, errorModelList, "Operation_Standard");
                }
                verify_E(cell_E, cell_B, errorModelList);
                verify_F(cell_F, cell_B, errorModelList);
                verify_G(cell_G, cell_B, errorModelList);
                verify_H(cell_H, cell_B, errorModelList);
                verify_I(cell_I, cell_B, errorModelList);
                break;
            case Cell.CELL_TYPE_STRING:
                reporting_Year_Formula_Cell_Value = cell_C.getStringCellValue();
                if (reporting_Year_Formula_Cell_Value.contains("USD")) {

                } else if (reporting_Year_Formula_Cell_Value.equalsIgnoreCase("Unit")) {
                    start = start + 4;
                } else {
                    genrateError(cell_C, errorModelList, "Operation_Standard");
                }
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            default:
                genrateError(cell_C, errorModelList, "Operation_Standard");
                break;
            }
        } catch (NullPointerException nullexcp) {
            continue;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

From source file:info.toegepaste.controller.UploadController.java

public void fileUploadListener(FileUploadEvent e) {

    // Get uploaded file from the FileUploadEvent to use with primefaces
    this.file = e.getFile();
    // Get uploaded file to use with Apache POI
    InputStream POIFile;/*from   ww  w.j  a  v a  2  s. c  o m*/
    XSSFWorkbook workbook = null;
    try {
        POIFile = e.getFile().getInputstream();
        //Create workbook
        workbook = new XSSFWorkbook(POIFile);
    } catch (IOException ex) {

    }
    // Print out the information of the file
    System.out.println(
            "Uploaded File Name Is :: " + file.getFileName() + " :: Uploaded File Size :: " + file.getSize());
    //Create a worksheet (needed to get rows)
    XSSFSheet worksheet = workbook.getSheetAt(0);
    //Divide worksheet into rows
    Iterator<Row> rowIterator = worksheet.iterator();
    //Get Classgroup from line 1 cell 2
    XSSFRow currentRow = (XSSFRow) rowIterator.next();
    Iterator<Cell> klasIter = currentRow.cellIterator();
    XSSFCell klasCell = currentRow.getCell(1);
    //Get Course from line 2 cell 2
    currentRow = (XSSFRow) rowIterator.next();
    Iterator<Cell> courseIter = currentRow.cellIterator();
    XSSFCell courseCell = currentRow.getCell(1);
    //Get subject from line 3 cell 2
    System.out.println("Stuff");
    currentRow = (XSSFRow) rowIterator.next();
    Iterator<Cell> subjectIter = currentRow.cellIterator();
    XSSFCell subjectCell = currentRow.getCell(1);
    System.out.println("Subject:" + subjectCell.toString());
    System.out.println("Subject:" + subjectCell.getStringCellValue());
    //Get total possible score from line 4 cell 2
    currentRow = (XSSFRow) rowIterator.next();
    Iterator<Cell> totalScoreIter = currentRow.cellIterator();
    XSSFCell totalScoreCell = currentRow.getCell(1);
    //Skip line 5 & 6
    currentRow = (XSSFRow) rowIterator.next();
    currentRow = (XSSFRow) rowIterator.next();
    currentRow = (XSSFRow) rowIterator.next();
    List<Classgroup> group = classgroupService.getWithName(klasCell.getStringCellValue());

    //Persist new  exam to database
    Exam newExam = new Exam();
    Classgroup newGroup = new Classgroup();
    //Check if classgroup already exists, create if it doesnt
    if (group.isEmpty()) {

        newGroup.setName(klasCell.getStringCellValue());
        newGroup.setCourses(null);
        classgroupService.insert(newGroup);

    } else {
        newGroup = group.get(0);

    }

    newExam.setClassgroup(newGroup);
    List<Course> course = courseService.getWithName(courseCell.getStringCellValue());
    Course newCourse = new Course();
    //Check if course exists, if not create
    if (course.isEmpty()) {
        newCourse.setName(courseCell.getStringCellValue());
        int year = Calendar.getInstance().get(Calendar.YEAR);
        newCourse.setYear(year);
        newCourse.setClassgroup(newGroup);
        int maand = Calendar.getInstance().get(Calendar.MONTH);
        if (maand <= 6 && maand >= 1) {
            newCourse.setSemester(2);
        } else {
            newCourse.setSemester(1);
        }
        courseService.insert(newCourse);
    } else {
        newCourse = course.get(0);
    }

    newExam.setCourse(newCourse);

    newExam.setName(subjectCell.getStringCellValue());
    // double totalScoreValue = ;
    // String totalScoreWorkaround =String.valueOf(totalScoreValue);
    newExam.setTotal((int) totalScoreCell.getNumericCellValue());
    examService.insert(newExam);

    //Read file to end, cell 0 student number, cell 1 name, cell 2 score
    while (rowIterator.hasNext()) {
        XSSFCell userNrCell = currentRow.getCell(0);
        System.out.println(userNrCell.toString());
        int StudentNumber = 0;
        // String StudentNumberWorkaround =userNrCell.getStringCellValue();
        StudentNumber = (int) userNrCell.getNumericCellValue();
        List<Student> currentStudent = studentsService.getStudentInListByNumber(StudentNumber);
        Student newStudent = new Student();
        XSSFCell userNameCell = currentRow.getCell(1);
        //Check if student exists, else create
        if (currentStudent.isEmpty()) {

            String fullName = userNameCell.getStringCellValue();
            String nameArray[] = fullName.split(" ");
            newStudent.setFirstname(nameArray[0]);
            newStudent.setLastname(nameArray[1]);
            newStudent.setNumber(StudentNumber);
            newStudent.setEmail("r0" + StudentNumber + "@student.thomasmore.be");
            newStudent.setClassgroup(newGroup);
            newStudent.setPassword(null);
            studentsService.insert(newStudent);

        } else {
            newStudent = currentStudent.get(0);
        }
        //Add score to student
        List<Score> currentScore = scoreService.checkIfScoreExists(newStudent, newExam);
        XSSFCell scoreCell = currentRow.getCell(2);
        Score scoreEntry = new Score();
        if (currentScore.isEmpty()) {

            scoreEntry.setExam(newExam);
            scoreEntry.setScore((int) scoreCell.getNumericCellValue());
            if (currentStudent.isEmpty()) {
                scoreEntry.setStudent(newStudent);
            } else {
                scoreEntry.setStudent(currentStudent.get(0));
            }

            scoreService.insert(scoreEntry);
        } else {
            scoreEntry = currentScore.get(0);
        }

        currentRow = (XSSFRow) rowIterator.next();
    }

}

From source file:localization.excel.java

public static void convert(String filePath) {
    Vector<String> zFile;
    if (filePath.endsWith(".zip")) {
        zFile = readzipfile(filePath);/*from  w  w w  . j  a  v  a 2  s . c o m*/
        for (String s : zFile) {
            if (s.endsWith(".xlsx")) {
                //System.out.println(s);
                convert(s);
            }
        }
    } else if (!filePath.endsWith(".xlsx")) {
        return;
    } else {
        try {
            FileInputStream file = new FileInputStream(new File(filePath));
            System.out.println(filePath);
            //Get the workbook instance for XLS file 
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            XSSFRow row;
            XSSFCell cell;
            rowNumber = sheet.getPhysicalNumberOfRows();
            try {
                for (int i = 0; i < rowNumber; i++) {
                    row = sheet.getRow(i);
                    if (row != null) {
                        int columnNum = row.getPhysicalNumberOfCells();
                        //System.out.println(columnNum);
                        for (int j = 0; j < columnNum; j++) {
                            cell = row.getCell(j);

                            if (j == 0) {
                                String name = cell.getRichStringCellValue().getString();
                                if (name.equalsIgnoreCase("Esri")) {
                                    langNumber++;
                                }
                                //System.out.println(name);
                            }
                        }
                        if (i == 3) {
                            cell = row.getCell(30);
                            XSSFCellStyle cs = cell.getCellStyle();
                            cell = row.createCell(32);
                            cell.setCellValue("Additional Charge per language");
                            cell.setCellStyle(cs);
                        }
                    }
                }
            } catch (Exception e) {

            }
            System.out.println(langNumber);
            double total = Double.parseDouble(sheet.getRow(langNumber + 3).getCell(29).getRawValue());

            double subTotal = total / langNumber;
            DecimalFormat df = new DecimalFormat("#.000");
            for (int i = 0; i < langNumber; i++) {
                cell = sheet.getRow(i + 4).createCell(32);
                cell.setCellValue("$" + df.format(subTotal));
            }

            file.close();
            FileOutputStream outFile = new FileOutputStream(filePath);
            workbook.write(outFile);
            outFile.close();
            rowNumber = 0;
            langNumber = 0;
            System.out.println("Done");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

From source file:Logica.LLeerExcel.java

public static void LLeerExcel(File fileName) {

    try {//from  ww w .  ja  va  2s.  co m
        InputStream inp = new FileInputStream(fileName);

        XSSFWorkbook wb = new XSSFWorkbook(inp);
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = sheet.getRow(13);
        Cell cell = row.getCell(13);

        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue("x");

        FileOutputStream fileOut = new FileOutputStream(
                "C:\\Users\\CLAUDIA\\Documents\\porteria\\formato.xlsx");
        wb.write(fileOut);

        fileOut.close();
    } catch (Exception e) {
        System.out.println(e);
    }
}

From source file:Logica.LLeerExcel.java

public static void LLeerExcel1(File fileName) {

    try {/* w  w w.j ava  2s  .c o  m*/
        InputStream inp = new FileInputStream(fileName);

        XSSFWorkbook wb = new XSSFWorkbook(inp);
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = sheet.getRow(2);
        Cell cell = row.getCell(13);

        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue("x");

        FileOutputStream fileOut = new FileOutputStream(
                "C:\\Users\\CLAUDIA\\Documents\\porteria\\formato.xlsx");
        wb.write(fileOut);

        fileOut.close();
    } catch (Exception e) {
        System.out.println(e);
    }
}