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

Introduction

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

Prototype

String getStringCellValue();

Source Link

Document

Get the value of the cell as a string

For numeric cells we throw an exception.

Usage

From source file:cn.lhfei.fu.service.impl.HomeworkDataBuildFactory.java

License:Apache License

@Override
public boolean importDataByExcel(String filePath, Map<String, Object> params) throws Exception {
    InputStream inp = new FileInputStream(filePath);

    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);/*from   w w  w. j ava 2  s  . com*/

    int totalRows = sheet.getPhysicalNumberOfRows();
    Cell homeworkNameCell = null; // ??
    Cell academicYearCell = null; // 
    Cell semesterCell = null; // 
    Cell teacherIdCell = null; // ?      
    Cell teacherNameCell = null; // ??      
    Cell classCell = null; // ???
    Cell courseCodeCell = null; // ?      
    Cell courseNameCell = null; // ??         

    String homeworkName = null;
    String teacherId = null;
    String teacherName = null;
    String courseName = null;
    String courseCode = null;
    String academicYear = null;
    String semester = null;
    String className = null;

    HomeworkBaseModel model = null;
    Date currentDate = new Date();
    List<HomeworkBaseModel> homeworkList = new ArrayList<HomeworkBaseModel>();

    for (int i = 1; i < totalRows; i++) {
        Row row = sheet.getRow(i);

        homeworkNameCell = row.getCell(0); // ??
        academicYearCell = row.getCell(1); // 
        semesterCell = row.getCell(2); // 
        teacherIdCell = row.getCell(3); // ?
        teacherNameCell = row.getCell(4); // ??
        classCell = row.getCell(5); // ???
        courseCodeCell = row.getCell(6); // ?
        courseNameCell = row.getCell(7); // ??

        if (homeworkNameCell != null) {
            homeworkName = homeworkNameCell.getStringCellValue().trim();
        }
        if (academicYearCell != null) {
            academicYear = academicYearCell.getStringCellValue().trim();
        }
        if (semesterCell != null) {
            semester = semesterCell.getStringCellValue().trim();
        }
        if (teacherIdCell != null) {
            teacherId = teacherIdCell.getStringCellValue().trim();
        }
        if (teacherNameCell != null) {
            teacherName = teacherNameCell.getStringCellValue().trim();
        }
        if (courseCodeCell != null) {
            courseCode = courseCodeCell.getStringCellValue().trim();
        }
        if (courseNameCell != null) {
            courseName = courseNameCell.getStringCellValue().trim();
        }
        if (classCell != null) {
            className = classCell.getStringCellValue().trim();

            model = new HomeworkBaseModel();

            model.setName(homeworkName);
            model.setAcademicYear(academicYear);
            model.setSemester(semester);
            model.setTeacherId(teacherId);
            model.setTeacherName(teacherName);
            model.setClassName(className);
            model.setCourseCode(courseCode);
            model.setCourseName(courseName);

            model.setActionType(OperationTypeEnum.SC.getCode().toString());
            model.setOperationTime(currentDate);
            model.setCreateTime(currentDate);
            model.setModifyTime(currentDate);

            homeworkList.add(model);

        }

        log.info("teacher={}, course={}, xn={}, xq={}, homework={}", teacherName, courseName, academicYear,
                semester, className);
    }

    for (HomeworkBaseModel baseModel : homeworkList) {
        excelFactoryMapper.importHomework(baseModel);
    }

    return false;
}

---

From source file:cn.lhfei.fu.service.impl.TeacherServiceImplTest.java

License:Apache License

@Test
public void updateTitle() throws Exception {
    String filePath = "src\\test\\resource\\excel\\??? - 2014-10-31???.xlsx";
    String teacherId = "";
    String teacherTitle = "";

    InputStream inp = new FileInputStream(filePath);

    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);//from ww  w.java 2 s. com

    int totalRows = sheet.getPhysicalNumberOfRows();

    Cell teacherIdCell = null;
    Cell teacherTitleCell = null;

    for (int i = 1; i < totalRows; i++) {
        Row row = sheet.getRow(i);
        teacherIdCell = row.getCell(1);
        teacherTitleCell = row.getCell(8);

        teacherId = teacherIdCell.getStringCellValue();

        if (teacherId != null && teacherId.startsWith("S")) {
            teacherId = teacherId.replaceAll("S", "");
        }

        teacherTitle = teacherTitleCell.getStringCellValue();

        log.info("ID: {}, Title: {}", teacherId, teacherTitle);

        teacherService.updateTeacherTitle(teacherId, teacherTitle);
    }

}

---

From source file:cn.lhfei.fu.service.impl.ThesisDataBuildFactory.java

License:Apache License

@Override
public boolean importDataByExcel(String filePath, Map<String, Object> params) throws Exception {

    Date currentDate = new Date();
    List<ThesisBase> thesisList = new ArrayList<ThesisBase>();

    InputStream inp = new FileInputStream(filePath);
    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);/*from  www.j a v a2  s  .co m*/

    int totalRows = sheet.getPhysicalNumberOfRows();

    Cell classCell = null;
    Cell studentNameCell = null;
    Cell studentIdCell = null;

    String className = "";
    String studentId = "";
    String studentName = "";

    String desc = "?";
    Integer degree = (Integer) params.get("degree");

    for (int i = 1; i < totalRows; i++) {
        Row row = sheet.getRow(i);

        classCell = row.getCell(0);
        studentNameCell = row.getCell(1);
        studentIdCell = row.getCell(2);

        className = classCell.getStringCellValue();
        studentName = studentNameCell.getStringCellValue();
        studentId = studentIdCell.getStringCellValue();

        ThesisBase model = new ThesisBase();
        model.setClassName(className);
        model.setStudentName(studentName);
        model.setStudentId(studentId);
        model.setDegree(degree);
        model.setDesc(desc);
        model.setActionType("" + OperationTypeEnum.PLSC.getCode());

        model.setOperationTime(currentDate);
        model.setCreateTime(currentDate);
        model.setModifyTime(currentDate);

        thesisList.add(model);
    }

    log.info("Batch size: ", thesisList.size());

    for (ThesisBase thesis : thesisList) {

        thesisBaseService.save(thesis);
    }

    return false;
}

---

From source file:cn.mypandora.util.MyExcelUtil.java

License:Apache License

/**
 * ??ExcelTitle//from  w  w w  . j a  v  a2 s.c  o m
 *
 * @param excelFile
 * @param sheetName sheet???
 * @return
 */
public static List<String> scanExcelTitles(File excelFile, String... sheetName) {
    List<String> titles = new ArrayList<>();
    try {
        Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFile));

        Sheet sheet;
        if (sheetName.length == 0) {
            sheet = workbook.getSheetAt(0);
        } else {
            sheet = workbook.getSheet(sheetName[0]);
        }
        Row row = sheet.getRow(0);
        if (row != null) {
            int i = 0;
            while (true) {
                Cell cell = row.getCell(i);
                if (cell == null) {
                    break;
                }
                titles.add(cell.getStringCellValue());
                i++;
            }
        }
    } catch (Exception e) {
        logger.debug("Scan Excel [" + excelFile.getPath() + excelFile.getName() + "] Error");
        throw new RuntimeException(e);
    }
    return titles;
}

---

From source file:cn.study.innerclass.PoiUtil.java

License:Open Source License

public static Object getCellData(Cell cell, FormulaEvaluator formula) {
    if (cell == null) {
        return null;
    }//from  w w  w  .  j a  va 2  s .co  m
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        System.out.println(cell.getRichStringCellValue().getString());
        return cell.getRichStringCellValue().getString();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            System.out.println(cell.getDateCellValue());
            return cell.getDateCellValue();
        } else {
            System.out.println(cell.getNumericCellValue());
            return cell.getNumericCellValue();
        }
    case Cell.CELL_TYPE_BOOLEAN:
        System.out.println(cell.getBooleanCellValue());
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_FORMULA:
        System.out.println(cell.getStringCellValue());

        switch (formula.evaluate(cell).getCellType()) {
        case Cell.CELL_TYPE_STRING:
            System.out.println(formula.evaluate(cell).getStringValue());
            return formula.evaluate(cell).getStringValue();
        case Cell.CELL_TYPE_NUMERIC:
            System.out.println(formula.evaluate(cell).getNumberValue());
            return formula.evaluate(cell).getNumberValue();
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.println(formula.evaluate(cell).getBooleanValue());
            return formula.evaluate(cell);

        }
    default:
        return null;
    }
}

---

From source file:co.com.smartcode.bitcom.managedbeans.crud.utils.ExcelUtils.java

private static String getString(Row row, int i) {
    Cell cell = row.getCell(i);
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        DecimalFormat format = new DecimalFormat("#");
        return format.format(cell.getNumericCellValue());
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    }// w  w w  .ja v a  2 s  . co  m
    return null;
}

---

From source file:CODIGOS.Planilha.java

public static void lerPlanilha(String arquivo) {

    FileInputStream fisPlanilha = null;

    try {//from   ww w.j av a 2s  .  c  o  m

        File dir = new File("C:\\GA6");
        File file = new File(dir, arquivo + ".xlsx");
        fisPlanilha = new FileInputStream(file);

        /*CRIA UM WORKBOOK = PLANILHA TODA COM TODAS AS ABAS*/
        XSSFWorkbook workbook = new XSSFWorkbook(fisPlanilha);

        /*RECUPERAMOS APENAS A PRIMEIRA ABA OU PRIMEIRA PLANILHA*/
        XSSFSheet sheet = workbook.getSheetAt(0);

        /*RETORNA TODAS AS LINHAS DA PLANILHA 0 */
        Iterator<Row> rowIterator = sheet.iterator();

        /*VARRE TODAS AS LINHAS DA PLANILHA 0*/
        while (rowIterator.hasNext()) {

            //recebe cada linha da planilha
            Row row = rowIterator.next();

            //pegamos todas as celulas desta linha
            Iterator<Cell> cellIterator = row.iterator();

            //varremos todas as celulas da linha atual
            while (cellIterator.hasNext()) {

                //criamos uma celula
                Cell cell = cellIterator.next();

                switch (cell.getCellType()) {

                case Cell.CELL_TYPE_STRING:
                    PS[count] = "" + cell.getStringCellValue();
                    count++;
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    PS[count] = "" + cell.getNumericCellValue();
                    PS[count] = PS[count].replace(".0", "");
                    count++;
                    break;

                }

            }

        }

    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "Arquivo " + arquivo + " no encontrado.", "Warning",
                JOptionPane.WARNING_MESSAGE);
        System.exit(0);
    } catch (IOException ex) {
        Logger.getLogger(Planilha.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            fisPlanilha.close();
        } catch (IOException ex) {
            Logger.getLogger(Planilha.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

}

---

From source file:com.abixen.platform.service.businessintelligence.multivisualisation.service.impl.parser.ExcelParserServiceImpl.java

License:Open Source License

private DataValue parseAsString(Cell cell) {
    DataValueString dataValueString = new DataValueString();
    dataValueString.setValue(cell.getStringCellValue());
    return dataValueString;
}

---

From source file:com.accenture.bean.PlanoExcel.java

public void extraiPlanilha() {
    try {/*from   ww  w  .j ava2s.c  o m*/
        //Leitura
        FileInputStream arquivo = new FileInputStream(new File(fileName));

        // Carregando workbook
        XSSFWorkbook wb = new XSSFWorkbook(arquivo);
        // Selecionando a primeira aba
        XSSFSheet s = wb.getSheetAt(1);

        // Caso queira pegar valor por referencia
        CellReference cellReference = new CellReference("M8");
        Row row = s.getRow(cellReference.getRow());
        Cell cell = row.getCell(cellReference.getCol());
        System.out.println("Valor Refe:" + cell.getStringCellValue());

        // Fazendo um loop em todas as linhas
        for (Row rowFor : s) {
            // FAzendo loop em todas as colunas
            for (Cell cellFor : rowFor) {
                try {
                    // Verifica o tipo de dado
                    if (cellFor.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        // Na coluna 6 tenho um formato de data
                        if (cellFor.getColumnIndex() == 6) {
                            // Se estiver no formato de data
                            if (DateUtil.isCellDateFormatted(cellFor)) {
                                // Formatar para o padrao brasileiro
                                Date d = cellFor.getDateCellValue();
                                DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
                                System.out.println(df.format(d));
                            }
                        } else {
                            // Mostrar numerico
                            System.out.println(cellFor.getNumericCellValue());
                        }
                    } else {
                        // Mostrar String
                        System.out.println(cellFor.getStringCellValue());
                    }
                } catch (Exception e) {
                    // Mostrar Erro
                    System.out.println(e.getMessage());
                }
            }
            // Mostrar pulo de linha
            System.out.println("------------------------");
        }

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

---

From source file:com.accenture.control.ExcelDAO.java

public String[] carregaPlanilhaFuncionalidade() throws IOException, ClassNotFoundException, SQLException {

    Plano plano = new Plano();
    ManipulaDadosSQLite banco = new ManipulaDadosSQLite();
    String[] funcionalidade = null;
    try {/*from w w  w . j a  v a  2 s .c  o  m*/
        FileInputStream arquivo = new FileInputStream(new File(ExcelDAO.fileName));
        XSSFWorkbook workbook = new XSSFWorkbook(arquivo);
        //setado a planilha de configuraes
        XSSFSheet sheetPlano = workbook.getSheetAt(2);
        //linha pa
        int linha = 1;
        int coluna = 4;

        funcionalidade = new String[sheetPlano.getLastRowNum()];
        int index = 0;
        for (int count = 1; count < sheetPlano.getLastRowNum(); count++) {
            Row row = sheetPlano.getRow(count);
            for (int countColuna = 0; countColuna < 1; countColuna++) {
                Cell cell = row.getCell(coluna, Row.CREATE_NULL_AS_BLANK);
                System.out.println(cell.getColumnIndex() + "-" + cell.getRowIndex());
                if (cell.getCellType() == CELL_TYPE_BLANK) {
                    System.out.println("Campo vazio");
                } else if (cell.getCellType() == CELL_TYPE_NUMERIC) {
                    double valor = cell.getNumericCellValue();

                    System.out.println(valor);
                } else {
                    String valor = cell.getStringCellValue();
                    System.out.println(valor);
                    funcionalidade[index] = valor;
                    System.out.println(funcionalidade[index]);
                    banco.insertTabelaConf("TB_FUNCIONALIDADE", "DESC_FUNCIONALIDADE", valor);
                    index++;
                }
            }
        }

    } catch (FileNotFoundException ex) {
        Logger.getLogger(ExcelDAO.class.getName()).log(Level.SEVERE, null, ex);

    }
    return funcionalidade;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »