Example usage for org.apache.poi.xssf.usermodel XSSFCell getStringCellValue

List of usage examples for org.apache.poi.xssf.usermodel XSSFCell getStringCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFCell
  6. getStringCellValue

Introduction

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

Prototype

@Override
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:com.griffinslogistics.book.BookForTransportationJSFController.java

public void postProcessXLS(Object document) {
    XSSFWorkbook wb = (XSSFWorkbook) document;

    try {//from  w  w  w.  j  a va  2 s .  c om
        XSSFSheet sheet = wb.getSheetAt(0);

        for (int i = 2; i <= sheet.getPhysicalNumberOfRows(); i++) {
            Row row = sheet.getRow(i);
            row.setHeightInPoints(30);

            XSSFCell cell = sheet.getRow(i).getCell(0);
            String text = cell.getStringCellValue();
            boolean value = Boolean.parseBoolean(text);

            if (value) {
                cell.setCellValue("");
            } else {
                cell.setCellValue("?");
            }
        }
    } catch (Exception e) {
        String message = e.getMessage();
        e.printStackTrace();
    }
}

From source file:com.heimaide.server.common.utils.excel.ExportExcel.java

License:Open Source License

private void exportFile(List<String> list, String rootPath, HttpServletResponse response,
        List<List<Object>> bodyList) throws FileNotFoundException, IOException {
    String importFileName = rootPath + "WEB-INF" + File.separator + "xlsx" + File.separator + "order.xlsx";
    String tmpDir = rootPath + "tmpDir" + File.separator;
    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(importFileName));
    this.styles = createStyles(wb);
    //?excel?  /*from   www.  jav  a  2 s.  c o  m*/
    //??sheet  
    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFCell cell1 = sheet.getRow(1).getCell(0);
    String cell1str = cell1.getStringCellValue();
    //????
    cell1str = cell1str.replace("X", list.get(0));
    cell1.setCellValue(cell1str);
    XSSFCell cell2 = sheet.getRow(2).getCell(0);
    String cell2str = cell2.getStringCellValue();
    //??
    cell2str = cell2str.replace("X", list.get(1));
    //?
    cell2str = cell2str.replace("Y", list.get(2));
    //???
    cell2str = cell2str.replace("Z", list.get(3));
    cell2.setCellValue(cell2str);
    XSSFCell cell3 = sheet.getRow(3).getCell(12);
    String cell3str = cell3.getStringCellValue();
    cell3str = cell3str.replace("XX", list.get(4));
    cell3.setCellValue(cell3str);

    XSSFCell cell4 = sheet.getRow(sheet.getLastRowNum() - 1).getCell(0);
    String cell4str = cell4.getStringCellValue();
    cell4str = cell4str.replace("X", list.get(5));
    cell4.setCellValue(cell4str);

    XSSFCell cell5 = sheet.getRow(sheet.getLastRowNum() - 4).getCell(12);
    XSSFCell cell6 = sheet.getRow(sheet.getLastRowNum() - 4).getCell(13);
    String str1 = String.valueOf(list.get(6));
    String str2 = String.valueOf(list.get(7));
    str1 = str1.substring(0, str1.indexOf("."));
    str2 = str2.substring(0, str2.indexOf("."));
    cell5.setCellValue(str1);

    cell6.setCellValue(str2);

    XSSFCell cell7 = sheet.getRow(sheet.getLastRowNum() - 3).getCell(12);
    XSSFCell cell8 = sheet.getRow(sheet.getLastRowNum() - 3).getCell(13);
    cell7.setCellValue(Double.parseDouble(list.get(6)) / 10);
    cell8.setCellValue(Double.parseDouble(list.get(7)) / 10);
    //                 int columnNum =sheet.getRow(3).getLastCellNum();
    //                 int column =0;
    //                 for(int i=0;i<columnNum;i++)
    //                 {
    //                    System.out.println("==="+sheet.getRow(3).getCell(i).getStringCellValue());
    //                    column =i;
    //                 }

    int num = 5;
    for (int i = 0; i < bodyList.size(); i++) {
        List<Object> strList = bodyList.get(i);
        sheet.shiftRows(num, bodyList.size() + num, 1, true, false);
        sheet.createRow(num);

        for (int j = 0; j < strList.size(); j++) {
            Row row = sheet.getRow(num);
            addCell(row, j, strList.get(j));
            //                       Cell cell = row.createCell(j);
            //                       cell.setCellValue(strList.get(j));
        }
        num++;
    }

    //??  
    String tmpName = tmpDir + "tmp_hmd.xlsx";
    File dirFile = new File(tmpDir);
    if (!dirFile.exists()) {
        dirFile.mkdir();
    }
    File tmpFile = new File(tmpName);
    FileOutputStream fos = new FileOutputStream(tmpFile);
    wb.write(fos);

    XSSFWorkbook wb1 = new XSSFWorkbook(new FileInputStream(tmpName));
    response.reset();
    response.setContentType("application/octet-stream; charset=utf-8");
    response.setHeader("Content-Disposition",
            "attachment; filename=" + Encodes.urlEncode("?" + list.get(0) + "?.xlsx"));
    wb1.write(response.getOutputStream());

    tmpFile.delete();
}

From source file:com.jitendrasinghnz.excelreadutility.ExcelReadStringArrayXSLX.java

License:Open Source License

public String convertCellToString(XSSFCell cell) {
    int type;//from w ww .  jav  a2s . c om
    if (cell == null) {
        type = Cell.CELL_TYPE_BLANK;
    } else {
        type = cell.getCellType();
    }
    Object result;
    switch (type) {
    case Cell.CELL_TYPE_STRING:
        result = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        result = cell.getNumericCellValue();
        break;
    case Cell.CELL_TYPE_BLANK:
        result = "";
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        result = cell.getBooleanCellValue();
        break;
    case Cell.CELL_TYPE_ERROR:
        result = cell.getErrorCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        result = cell.getCellFormula();
        break;

    default:
        throw new RuntimeException("There are no support fot the cell type");
    }
    return result.toString();
}

From source file:com.niles.excel2json.objects.ExcelFile.java

License:Open Source License

public void process() throws Exception {
    File excel = new File(path);
    FileInputStream fis = SystemTools.getFileInputStream(excel);

    XSSFWorkbook wb = null;//from   w w  w  .  j  av a  2s . co m

    try {
        wb = new XSSFWorkbook(fis);
    } catch (IOException ex) {
        logger.error("Unable to process file [{}]\r\n{}", path, ex.getMessage());
        return;
    }

    this.fileName = excel.getName().replace(StringConstants.XLSX, "");
    this.folderPath = fileName + " Files";

    folderPath = SystemTools.createFolder(folderPath);

    int sheetCount = wb.getNumberOfSheets();

    int currentSheetNumber = 0;
    int rowCount = 0;
    int columnCount = 0;

    HashMap<Integer, String> headers = new HashMap<Integer, String>();

    /*
     * Itterate through the Excel sheets here and convert them to JSON
     */
    while (currentSheetNumber < sheetCount) {
        XSSFSheet current = wb.getSheetAt(currentSheetNumber);
        sheetName = current.getSheetName();

        // System.out.println(sheetName);
        if (current.getRow(0) == null) {
            // logger.error("[{}] Sheet contains no data", sheetName);
        } else {
            //logger.info("[{}] Processing sheet", sheetName);
            rowCount = current.getLastRowNum() + 1;
            columnCount = current.getRow(0).getPhysicalNumberOfCells();

            // System.out.println("Col Count: " + columnCount);
            // System.out.println("Row Count: " + rowCount);
            JSONArray myJSONArray = new JSONArray();
            JSONObject currentJSONObject = null;
            for (int a = 0; a < rowCount; a++) {
                XSSFRow currentRow = current.getRow(a);

                if (a == 0) {
                    logger.info("[{}] Loading header information", sheetName);
                    for (int currentCellNumber = 0; currentCellNumber < columnCount; currentCellNumber++) {
                        XSSFCell currentCell = currentRow.getCell(currentCellNumber);

                        String header = "Header" + currentCellNumber;

                        if (currentCell != null) {
                            if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                                header = currentCell.getStringCellValue();
                            }

                            if (currentCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                                header = currentCell.getNumericCellValue() + "";
                            }
                        }

                        headers.put(currentCellNumber, header.replaceAll(" ", ""));
                    }
                } else {
                    currentJSONObject = new JSONObject();

                    for (int currentCellNumber = 0; currentCellNumber < columnCount; currentCellNumber++) {
                        XSSFCell currentCell = currentRow.getCell(currentCellNumber);

                        String value = "";

                        if (currentCell != null) {
                            if (currentCell.getCellType() != XSSFCell.CELL_TYPE_ERROR
                                    && currentCell.getCellType() != XSSFCell.CELL_TYPE_FORMULA) {
                                if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                                    value = currentCell.getStringCellValue();
                                }
                                if (currentCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                                    value = currentCell.getNumericCellValue() + "";
                                }
                                if (currentCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
                                    value = currentCell.getBooleanCellValue() + "";
                                }

                                if (value == null) {
                                    value = "";
                                }
                            }
                        }

                        currentJSONObject.put(headers.get(currentCellNumber), value);
                    }
                    myJSONArray.add(currentJSONObject);
                }
            }
            writeToJson(myJSONArray);
        }
        currentSheetNumber++;
    }
}

From source file:com.smanempat.controller.ControllerClassification.java

private void showXLSX(JTextField txtFileDirectory, JTable tablePreview)
        throws FileNotFoundException, IOException {
    DefaultTableModel tableModel = new DefaultTableModel();
    File fileName = new File(txtFileDirectory.getText());
    FileInputStream inputStream = new FileInputStream(fileName);
    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
    XSSFSheet sheet = workbook.getSheetAt(0);

    int rowValue = sheet.getLastRowNum() + 1;
    int colValue = sheet.getRow(0).getLastCellNum();
    String[][] data = new String[rowValue][colValue];
    String[] colName = new String[colValue];
    for (int i = 0; i < rowValue; i++) {
        XSSFRow row = sheet.getRow(i);/*from   w  w  w  . ja  v  a 2 s .co m*/
        for (int j = 0; j < colValue; j++) {
            XSSFCell cell = row.getCell(j);
            int type = cell.getCellType();
            Object returnCellValue = null;
            if (type == 0) {
                returnCellValue = cell.getNumericCellValue();
            } else if (type == 1) {
                returnCellValue = cell.getStringCellValue();
            }

            data[i][j] = returnCellValue.toString();
        }
    }

    for (int i = 0; i < colValue; i++) {
        colName[i] = data[0][i];
    }

    tableModel = new DefaultTableModel(data, colName);
    tablePreview.setModel(tableModel);
    tableModel.removeRow(0);
}

From source file:com.smanempat.view.ReadWorkbook.java

private String nilaiCell(XSSFCell pCell) {
    int tipe = pCell.getCellType();
    Object nilaiBalik = null;/*from w w w . j a  v a 2 s  . co  m*/

    if (tipe == 0) {
        nilaiBalik = pCell.getNumericCellValue();
    } else if (tipe == 1) {
        nilaiBalik = pCell.getStringCellValue();
    }

    return nilaiBalik.toString();
}

From source file:com.xandrev.altafitcalendargenerator.XLSExtractor.java

public HashMap<Integer, ArrayList<TimeTrack>> importExcelSheet(String fileName) {
    HashMap<Integer, String> tmpHours = new HashMap<Integer, String>();
    HashMap<Integer, ArrayList<TimeTrack>> out = new HashMap<Integer, ArrayList<TimeTrack>>();
    init(out);/*from ww w.j  a  va  2s . c om*/
    try {
        Workbook workBook = WorkbookFactory.create(new FileInputStream(fileName));
        Sheet sheet = workBook.getSheetAt(0);
        Iterator rowIter = sheet.rowIterator();
        int rowIdx = 0;
        boolean started = false;
        boolean finished = false;
        while (rowIter.hasNext() && !finished) {
            XSSFRow row = (XSSFRow) rowIter.next();
            if (row != null && !started) {
                XSSFCell cell = row.getCell(0);
                if (cell != null) {
                    String value = cell.getStringCellValue();
                    if (value == null || value.isEmpty() || !"HORA".equals(value)) {
                        rowIdx++;
                        started = true;
                        continue;
                    }
                }
                row = (XSSFRow) rowIter.next();
            }

            Iterator<Cell> cellIter = row.cellIterator();
            int cellIndex = 0;
            while (cellIter.hasNext()) {
                XSSFCell cell = (XSSFCell) cellIter.next();
                if (cell != null) {
                    String value = cell.getStringCellValue();
                    installHashMap(tmpHours, out, cellIndex, rowIdx, value);
                }
                cellIndex++;
            }
            rowIdx++;
        }

    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
    return out;
}

From source file:com.xhsoft.framework.common.file.ExcelHandle.java

License:Open Source License

/**
 * ????.xlsx?/*from  w  w  w . j  ava2s. co m*/
 * @params {:,:}
 * @return String
 * @author lijiangwei
 * @since 2012-11-12
 */
private String getCellValue(XSSFCell xls_cell) {
    String value = "";

    switch (xls_cell.getCellType()) {
    case XSSFCell.CELL_TYPE_BLANK:
        value = "";
        break;
    case XSSFCell.CELL_TYPE_BOOLEAN:
        value = String.valueOf(xls_cell.getBooleanCellValue());
        break;
    case XSSFCell.CELL_TYPE_ERROR:
        break;
    case XSSFCell.CELL_TYPE_FORMULA:
        String.valueOf(xls_cell.getCellFormula());
        break;
    case XSSFCell.CELL_TYPE_NUMERIC:
        value = String.valueOf(xls_cell.getNumericCellValue());
        break;
    case XSSFCell.CELL_TYPE_STRING:
        value = String.valueOf(xls_cell.getStringCellValue());
        break;
    default:
        break;
    }

    return value;
}

From source file:com.yanglb.utilitys.codegen.core.reader.BaseReader.java

License:Apache License

/**
 * ?Cell???/*  w  w w .  j ava  2s  .c o  m*/
 * @param cell
 * @return
 */
public String getCellStringValue(XSSFCell cell) {
    String result = null;
    int type = cell.getCellType();
    if (type == Cell.CELL_TYPE_FORMULA)
        type = cell.getCachedFormulaResultType();
    if (type == Cell.CELL_TYPE_BLANK)
        return null;
    if (type == Cell.CELL_TYPE_ERROR) {
        return "#VALUE!";
    }

    switch (type) {
    case Cell.CELL_TYPE_BOOLEAN:
        result = String.valueOf(cell.getBooleanCellValue());
        break;

    case Cell.CELL_TYPE_STRING:
        result = cell.getStringCellValue();
        break;

    case Cell.CELL_TYPE_NUMERIC: {
        if (cell.getCellStyle().getDataFormat() == DataFormatType.FORMAT_DATE) {
            Date date = cell.getDateCellValue();
            String format = "yyyy/MM/dd";//cell.getCellStyle().getDataFormatString();
            if (cell.getCellStyle().getDataFormatString().contains(":")) {
                // 
                format = "yyyy/MM/dd HH:mm:ss";
            }
            SimpleDateFormat df = null;
            try {
                df = new SimpleDateFormat(format);
            } catch (IllegalArgumentException e) {
                //df = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss");
            }
            result = df.format(date);
        } else {
            // ?? 0
            Number number = cell.getNumericCellValue();
            result = number.toString();
            if (result.indexOf('.') != -1) {
                result = result.replaceAll("[0]*$", "");
            }
            if (result.endsWith(".")) {
                result = result.substring(0, result.length() - 1);
            }
        }
    }
        break;
    }
    return result;
}

From source file:Creator.TaskManagerPanel.java

private void loadDefaultTasks() {

    String path = "/Creator/textFiles/tasks.xlsx";
    InputStream loc = this.getClass().getResourceAsStream(path);
    importedTasks = new ArrayList<>();
    try {//from  w w w . j  a va2 s .  c  o  m

        XSSFWorkbook wb = new XSSFWorkbook(loc);
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row;
        XSSFCell cell;
        String[] rowData;
        int rows, cols; // No of rows
        rows = sheet.getPhysicalNumberOfRows();

        for (int i = 1; i < rows; i++) {

            row = sheet.getRow(i);
            if (row != null) {
                cols = row.getPhysicalNumberOfCells();
                rowData = new String[cols];

                for (int j = 0; j < cols; j++) {

                    cell = row.getCell(j);
                    if (cell != null) {
                        switch (cell.getCellType()) {
                        case 1: // string
                            rowData[j] = cell.getStringCellValue();
                            break;
                        case 2: // int
                            rowData[j] = String.valueOf(cell.getNumericCellValue());
                            break;
                        case 3: // blank
                            System.out.println("Blank data @ [" + i + "][" + j + "]");
                            rowData[j] = "no data @ [" + i + "][" + j + "]";
                            break;
                        case 4: // boolean
                            rowData[j] = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case 5: // error
                            rowData[j] = String.valueOf(cell.getErrorCellString());
                            break;
                        default:
                            System.out.println("default @ [" + i + "][" + j + "]");
                            rowData[j] = "default @ [" + i + "][" + j + "]";
                            break;

                        }

                    } else {
                        System.out.println("null @ [" + i + "][" + j + "]");
                        rowData[j] = "nullValue @ [" + i + "][" + j + "]";
                    }
                }
                rowData[5] = "'" + rowData[5] + "'";
                importedTasks.add(rowData);

            }

        }

        wb.close();

    } catch (Exception e) {
        System.out.println("Error reading excel file " + e.getMessage());
    }

}