Example usage for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. getStringCellValue

Introduction

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

Prototype

public 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:include.excel_import.Outter.java

License:Open Source License

private void pump(Vector vector, HSSFCell hssfcell, String s) {
    String s1 = getCellType(hssfcell);
    if (s1.equals("INT")) {
        DecimalFormat decimalformat = new DecimalFormat("##");
        String s2 = String.valueOf(decimalformat.format(hssfcell.getNumericCellValue()));
        vector.addElement(s2);/*  w w w. ja  v  a2  s  .c  o m*/
    } else if (s1.equals("INT1")) {
        DecimalFormat decimalformat1 = new DecimalFormat("##");
        String s3 = String.valueOf(decimalformat1.format(hssfcell.getNumericCellValue()));
        vector.addElement(s3);
    } else if (s1.equals("DOUBLE")) {
        DecimalFormat decimalformat2 = new DecimalFormat("##.##");
        String s4 = String.valueOf(decimalformat2.format(hssfcell.getNumericCellValue()));
        vector.addElement(s4);
    } else if (s1.equals("STRING"))
        vector.addElement(hssfcell.getStringCellValue());
    else if (s1.equals("DATE")) {
        SimpleDateFormat simpledateformat = new SimpleDateFormat("yyyy-MM-dd");
        String s5 = String.valueOf(simpledateformat.format(hssfcell.getDateCellValue()));
        vector.addElement(s5);
    } else if (s1.equals("BLANK"))
        if (s.equals("STRING"))
            vector.addElement(" ");
        else if (s.equals("INT"))
            vector.addElement(new Integer(0));
        else if (s.equals("DOUBLE"))
            vector.addElement(new Double(0.0D));
        else if (s.equals("DATE"))
            vector.addElement("0000-00-00");
        else if (s.equals("UNKNOWN"))
            vector.addElement(" ");
}

From source file:include.excel_import.Outter.java

License:Open Source License

public boolean validateValue() {
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        HSSFSheet hssfsheet = wb.getSheetAt(i);
        String s = wb.getSheetName(i);
        for (int j = 1; j < getRowCount(hssfsheet); j++) {
            HSSFRow hssfrow = hssfsheet.getRow(j);
            HSSFRow hssfrow1 = hssfsheet.getRow(0);
            for (int k = 0; k < getColumnCount(hssfsheet); k++) {
                HSSFCell hssfcell = hssfrow1.getCell((short) k);
                String s1 = hssfcell.getStringCellValue();
                HSSFCell hssfcell1 = hssfrow.getCell((short) k);
                if (!validateType(hssfcell1, s1, s)) {
                    message += "" + (k + 1) + " : " + (j + 1) + "????<br>";
                    return false;
                }/*from w  ww.  j  av  a 2s  . c  o m*/
            }

        }

    }

    return true;
}

From source file:include.excel_import.XlsInfo.java

License:Open Source License

public Vector getColumnsName(String sheetName) {
    if (columnsNames.containsKey(sheetName)) {
        return (Vector) columnsNames.get(sheetName);
    }//from   w  w w.  ja  v a 2 s.  c  o  m
    HSSFSheet sheet = getSheetForSheetName(sheetName);
    getColumnsName_result.clear();
    HSSFRow row = sheet.getRow((short) 0);
    int cellssize = row.getPhysicalNumberOfCells();
    for (int i = 0; i < cellssize; i++) {
        HSSFCell cell = row.getCell((short) i);
        getColumnsName_result.addElement(cell.getStringCellValue());
        cell = null;
    }
    columnsNames.put(sheetName, getColumnsName_result);
    sheet = null;
    row = null;

    //System.gc();
    return getColumnsName_result;
}

From source file:include.excel_import.XlsInfo.java

License:Open Source License

private void dump(Vector result, HSSFCell cell, String itemType) {
    String type = getCellDataType(cell);
    String num, date = null;//  w  w  w.  j a  va2  s .com
    if (type.equals("INT")) {
        num = String.valueOf(intFormat.format(cell.getNumericCellValue()));
        result.addElement(num);
    } else if (type.equals("STRING")) {
        result.addElement(cell.getStringCellValue());
    } else if (type.equals("DATE")) {
        date = String.valueOf(yyyymmddFormat.format(cell.getDateCellValue()));
        result.addElement(date);
    } else if (type.equals("DOUBLE")) {
        num = String.valueOf(doubleFormat.format(cell.getNumericCellValue()));
        result.addElement(num);
    } else if (type.equals("INT1")) {
        num = String.valueOf(intFormat.format(cell.getNumericCellValue()));
        result.addElement(num);
    } else if (type.equals("BLANK")) {
        if (itemType.equals("STRING")) {
            result.addElement(" ");
        } else if (itemType.equals("INT")) {
            result.addElement(new Integer(0));
        } else if (itemType.equals("DOUBLE")) {
            result.addElement(new Double(0.00));
        } else if (itemType.equals("DATE")) {
            result.addElement("0000-00-00");
        } else if (itemType.equals("UNKNOWN")) {
            result.addElement(" ");
        }
    }
    type = null;
    num = null;
    date = null;
}

From source file:include.excel_import.XlsValidator.java

License:Open Source License

public boolean validateValue() {
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        HSSFSheet sheet = wb.getSheetAt(i);
        String sheetName = wb.getSheetName(i);
        int rowCount = xlsInfo.getRowCount(sheetName);
        for (int j = 1; j < rowCount; j++) {
            HSSFRow row = sheet.getRow(j);
            HSSFRow row1 = sheet.getRow(0);//??itemname
            for (int n = 0; n < xlsInfo.getColumnCount(sheetName); n++) {
                HSSFCell cell1 = row1.getCell((short) n);
                String itemname = cell1.getStringCellValue();
                HSSFCell cell = row.getCell((short) n);
                if (!validateType(cell, itemname, sheetName)) {
                    message.append("").append(n + 1);
                    message.append(" : ").append(j + 1);
                    message.append("????<br>");
                    return false;
                }/* ww  w  .j  av  a 2  s .  co  m*/
                cell1 = null;
                itemname = null;
                cell = null;
            }
            row = null;
            row1 = null;
        }
        sheet = null;
        sheetName = null;
    }
    return true;
}

From source file:info.jtrac.domain.ExcelFile.java

License:Apache License

public ExcelFile(InputStream is) {
    POIFSFileSystem fs = null;/*from w ww  .  j a  v a 2 s  .c  o m*/
    HSSFWorkbook wb = null;
    try {
        fs = new POIFSFileSystem(is);
        wb = new HSSFWorkbook(fs);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow r = null;
    HSSFCell c = null;
    int row = 0;
    int col = 0;
    columns = new ArrayList<Column>();
    //========================== HEADER ====================================
    // column headings are important, this routine assumes that the first
    // row is a header row and that reaching an empty cell means end of data
    r = sheet.getRow(row);
    while (true) {
        c = r.getCell((short) col);
        if (c == null) {
            break;
        }
        String value = c.getStringCellValue();
        if (value == null || value.trim().length() == 0) {
            break;
        }
        Column column = new Column(value.trim());
        columns.add(column);
        col++;
    }
    //============================ DATA ====================================
    rows = new ArrayList<List<Cell>>();
    while (true) {
        row++;
        r = sheet.getRow(row);
        if (r == null) {
            break;
        }
        List<Cell> rowData = new ArrayList<>(columns.size());
        boolean isEmptyRow = true;
        for (col = 0; col < columns.size(); col++) {
            c = r.getCell((short) col);
            Object value = null;
            switch (c.getCellType()) {
            case (HSSFCell.CELL_TYPE_STRING):
                value = c.getStringCellValue();
                break;
            case (HSSFCell.CELL_TYPE_NUMERIC):
                // value = c.getDateCellValue();
                value = c.getNumericCellValue();
                break;
            case (HSSFCell.CELL_TYPE_BLANK):
                break;
            default: // do nothing
            }
            if (value != null && value.toString().length() > 0) {
                isEmptyRow = false;
                rowData.add(new Cell(value));
            } else {
                rowData.add(new Cell(null));
            }
        }
        if (isEmptyRow) {
            break;
        }
        rows.add(rowData);
    }
}

From source file:info.toegepaste.www.service.ProjectServiceImpl.java

public IngelezenFile getExcelScores(InputStream fs) {
    ArrayList<String> lijstNr = new ArrayList<String>();
    ArrayList<String> lijstNaam = new ArrayList<String>();
    ArrayList<String> lijstScore = new ArrayList<String>();
    IngelezenFile file = null;// w  ww. ja  v  a2  s .  c o  m
    //String fileContent;
    //Part filePart;
    try {

        //FileInputStream fileInputStream = new FileInputStream("C:\\Users\\Jeroen\\Desktop\\resultaten.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(fs);
        HSSFSheet worksheet = workbook.getSheet("Blad1");
        HSSFRow row1 = worksheet.getRow(0);

        HSSFCell cel = worksheet.getRow(3).getCell((short) 1);
        cel.setCellType(Cell.CELL_TYPE_STRING);

        String klas = worksheet.getRow(0).getCell((short) 1).getStringCellValue();
        String vak = worksheet.getRow(1).getCell((short) 1).getStringCellValue();
        String test = worksheet.getRow(2).getCell((short) 1).getStringCellValue();
        String totaalPunt = cel.getStringCellValue();

        int i = 6;
        // deze lus werkt niet als je regels weg doet uit een excel file, dus minder dan 3 studenten kan niet, meer wel!
        while (worksheet.getRow(i) != null && worksheet.getRow(i).getCell((short) 1) != null) {
            HSSFCell scorecel = worksheet.getRow(i).getCell((short) 2);
            scorecel.setCellType(Cell.CELL_TYPE_STRING);
            HSSFCell naamcel = worksheet.getRow(i).getCell((short) 1);
            naamcel.setCellType(Cell.CELL_TYPE_STRING);
            HSSFCell nrcel = worksheet.getRow(i).getCell((short) 0);
            nrcel.setCellType(Cell.CELL_TYPE_STRING);

            lijstNr.add(worksheet.getRow(i).getCell((short) 0).getStringCellValue());
            lijstNaam.add(worksheet.getRow(i).getCell((short) 1).getStringCellValue());
            lijstScore.add(worksheet.getRow(i).getCell((short) 2).getStringCellValue());

            i++;
        }

        file = new IngelezenFile(klas, vak, test, Integer.parseInt(totaalPunt), lijstNr, lijstNaam, lijstScore);

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    //return lijst;

    // upload scores
    insertTest(file);
    return file;
}

From source file:info.vancauwenberge.filedriver.filereader.xls.XlsFileReader.java

License:Mozilla Public License

public void openFile(File f) throws ReadException {
    trace.trace("Reading " + f.getAbsolutePath());
    //If the file is existing, open and read it
    try {//  www  . j av a2  s  .co  m
        FileInputStream fin = new FileInputStream(f);
        POIFSFileSystem poifs = new POIFSFileSystem(fin);
        wb = new HSSFWorkbook(poifs);
    } catch (IOException e) {
        throw new ReadException("Error while trying to read file " + f.getAbsolutePath(), e);
    }
    currentSheet = wb.getSheet(sheetName);
    //If we do not have a sheet with the given name, throw exception.
    if (currentSheet == null)
        throw new ReadException("No sheet with name " + sheetName + " found in file " + f.getAbsolutePath(),
                null);
    nextRowNumber = currentSheet.getFirstRowNum();

    //If we have a aheader row, read it to get the actual schema
    if (hasHeader) {
        if (useHeaderNames) {
            HSSFRow row = currentSheet.getRow(nextRowNumber);
            //Last cell num is zero based => +1
            String[] fields = new String[row.getLastCellNum()];
            trace.trace("Number of fields:" + fields.length);
            Iterator<Cell> iter = row.cellIterator();
            while (iter.hasNext()) {
                HSSFCell element = (HSSFCell) iter.next();
                String value = element.getStringCellValue();
                fields[element.getCellNum()] = value;
            }
            //We might have some nulls in the array. Default them.
            for (int i = 0; i < fields.length; i++) {
                String string = fields[i];
                if (string == null)
                    fields[i] = "_Unknown_" + i + "_";
            }
            schema = fields;
            nextRowNumber++;
        }
    }
}

From source file:info.vancauwenberge.filedriver.filereader.xls.XlsFileReader.java

License:Mozilla Public License

public Map<String, String> readRecord() throws ReadException {
    HSSFRow row = currentSheet.getRow(nextRowNumber);
    if (row != null) {
        //We have data.
        Map<String, String> result = new HashMap<String, String>(schema.length);
        //Do not use the iterator (row.cellIterator()): this will cause to skip empty cells!
        //Use the schema to loop over the cells
        for (short i = 0; i < schema.length; i++) {
            String fieldName = schema[i];
            HSSFCell cel = row.getCell(i);
            if (cel != null) {
                String value = "";
                if (cel.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                    //                  TODO: make this configurable: conversion from double to string
                    value = cel.getNumericCellValue() + "";
                } else {
                    value = cel.getStringCellValue();
                }//from w ww . ja va  2  s .  c o  m
                result.put(fieldName, value);
            } else {
                result.put(fieldName, "");
            }
        }
        nextRowNumber++;
        return result;
    } else {
        return null;
    }
}

From source file:is.idega.idegaweb.egov.fsk.business.FSKBusinessBean.java

License:Open Source License

@Override
public Map importExcelFile(UploadFile file, Object coursePK, int column) {
    Map map = new HashMap();

    try {/*from  w ww  .  j  a  v a  2s. c o m*/
        Course course = getCourse(coursePK);
        Group group = course.getGroup();

        FileInputStream input = new FileInputStream(file.getRealPath());
        HSSFWorkbook wb = new HSSFWorkbook(input);

        HSSFSheet sheet = wb.getSheetAt(0);

        NumberFormat format = NumberFormat.getNumberInstance();
        format.setGroupingUsed(false);
        format.setMinimumIntegerDigits(10);

        Collection imported = new ArrayList();
        Collection alreadyImported = new ArrayList();
        Collection outsideCommune = new ArrayList();
        Collection outsideAgeRange = new ArrayList();
        Collection invalidPersonalID = new ArrayList();
        Collection noUserFound = new ArrayList();

        for (int a = sheet.getFirstRowNum(); a <= sheet.getLastRowNum(); a++) {
            HSSFRow row = sheet.getRow(a);
            HSSFCell cell = row.getCell((short) (column - 1));
            if (cell == null) {
                continue;
            }

            String personalID = null;
            if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                personalID = cell.getStringCellValue();
            } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                personalID = String.valueOf(new Double(cell.getNumericCellValue()).longValue());
            } else {
                personalID = cell.getStringCellValue();
            }

            try {
                personalID = format.format(format.parse(personalID.replaceAll("-", "")));
            } catch (ParseException e1) {
                e1.printStackTrace();
                continue;
            }

            if (SocialSecurityNumber.isValidSocialSecurityNumber(personalID, getDefaultLocale())) {
                try {
                    User user = getUserBusiness().getUser(personalID);
                    if (!group.hasRelationTo(((Integer) user.getPrimaryKey()).intValue())) {
                        IWTimestamp dateOfBirth = new IWTimestamp(user.getDateOfBirth());
                        dateOfBirth.setMonth(1);
                        dateOfBirth.setDay(1);
                        Age age = new Age(dateOfBirth.getDate());

                        if (age.getYears(course.getStartDate()) < 6
                                || age.getYears(course.getStartDate()) > 18) {
                            outsideAgeRange.add(user);
                            continue;
                        }

                        if (!getUserBusiness().isCitizenOfDefaultCommune(user)) {
                            outsideCommune.add(user);
                            continue;
                        }

                        group.addGroup(user);
                        imported.add(user);
                    } else {
                        alreadyImported.add(user);
                    }
                } catch (FinderException e) {
                    noUserFound.add(personalID);
                }
            } else {
                invalidPersonalID.add(personalID);
            }
        }

        map.put(FSKConstants.REGISTRATION_CODE_REGISTERED, imported);
        map.put(FSKConstants.REGISTRATION_CODE_ALREADY_REGISTERED, alreadyImported);
        map.put(FSKConstants.REGISTRATION_CODE_OUTSIDE_COMMUNE, outsideCommune);
        map.put(FSKConstants.REGISTRATION_CODE_OUTSIDE_AGE_RANGE, outsideAgeRange);
        map.put(FSKConstants.REGISTRATION_CODE_INVALID_PERSONAL_ID, invalidPersonalID);
        map.put(FSKConstants.REGISTRATION_CODE_NO_USER_FOUND, noUserFound);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return map;
}