Cannot get a text value from a numeric cell Poi

Question

I m trying to consume data from a spreadsheet in Excel  but always of this error  already tried formatting the worksheet to text and number and still the error persists   I saw a person using it resolved cell setCellType   Cell CELL TYPE STRING     but I do not know where I fit this passage in my code   WebElement searchbox   driver findElement By name  j username     WebElement searchbox2   driver findElement By name  j password               try       FileInputStream file   new FileInputStream new File  C   paulo xls          HSSFWorkbook workbook   new HSSFWorkbook file       HSSFSheet sheet   workbook getSheetAt 0        for  int i 1  i  lt   sheet getLastRowNum    i             String j username   sheet getRow i  getCell 0  getStringCellValue            String j password   sheet getRow i  getCell 0  getStringCellValue             searchbox sendKeys j username           searchbox2 sendKeys j password           searchbox submit               driver manage   timeouts   implicitlyWait 10000  TimeUnit MILLISECONDS              workbook close        file close      catch  FileNotFoundException fnfe        fnfe printStackTrace      catch  IOException ioe        ioe printStackTrace

User · Answer

use the code  cell setCellType Cell CELL TYPE STRING   before reading the string value  Which can help you  I am using POI version 3 17 Beta1 version   sure the version compatibility also

User · Answer

Cell cell   sheet getRow i  getCell 0       cell setCellType   Cell CELL TYPE STRING        String j username   cell getStringCellValue        UPDATE  Ok  as have been said in comments  despite this works it isn t correct method of retrieving data from an Excel s cell    According to the manual here      If what you want to do is get a String value for your numeric cell    stop   This is not the way to do it  Instead  for fetching the string   value of a numeric or boolean or date cell  use DataFormatter instead    And according to the DataFormatter API      DataFormatter contains methods for formatting the value stored in an   Cell  This can be useful for reports and GUI presentations when you   need to display data exactly as it appears in Excel  Supported formats   include currency  SSN  percentages  decimals  dates  phone numbers    zip codes  etc    So  right way to show numeric cell s value is as following    DataFormatter formatter   new DataFormatter      creating formatter using the default locale  Cell cell   sheet getRow i  getCell 0    String j username   formatter formatCellValue cell     Returns the formatted value of a cell as a String regardless of the cell type

User · Answer

CellType cell   row getCell j  getCellTypeEnum     switch cell        case NUMERIC          intVal   row getCell j  getNumericCellValue            System out print intVal           break      case STRING          stringVal   row getCell j  getStringCellValue            System out print stringVal           break

User · Answer

public class B3PassingExcelDataBase          Test         Import the data  row start at 3 and column at 1       public static void imortingData    throws IOException            FileInputStream  file new         FileInputStream   Users Downloads Book2 xlsx            XSSFWorkbook book new XSSFWorkbook file           XSSFSheet sheet book getSheet  Sheet1             int rowNum sheet getLastRowNum            System out println rowNum              get the row and value and assigned to variable to use in application         for  int r 3 r lt rowNum r                      Rows stays same but column num changes and this is for only one person  It iterate for other               XSSFRow currentRow sheet getRow r                 String fName currentRow getCell 1  toString                 String lName currentRow getCell 2  toString                 String phone currentRow getCell 3  toString                 String email currentRow getCell 4  toString                     passing the data                 yogen findElement By name  firstName    sendKeys fName                        yogen findElement By name  lastName    sendKeys lName                     yogen findElement By name  phone    sendKeys phone                            yogen close

User · Answer

As explained in the Apache POI Javadocs  you should not use cell setCellType Cell CELL TYPE STRING  to get the string value of a numeric cell  as you ll loose all the formatting  Instead  as the javadocs explain  you should use DataFormatter  What DataFormatter does is take the floating point value representing the cell is stored in the file  along with the formatting rules applied to it  and returns you a string that look like it the cell does in Excel   So  if you re after a String of the cell  looking much as you had it looking in Excel  just do       Create a formatter  do this once  DataFormatter formatter   new DataFormatter Locale US             for  int i 1  i  lt   sheet getLastRowNum    i              Row r   sheet getRow i           if  r    null                   empty row  skip           else              String j username   formatter formatCellValue row getCell 0               String j password    formatter formatCellValue row getCell 1                   Use these                The formatter will return String cells as-is  and for Numeric cells will apply the formatting rules on the style to the number of the cell

User · Answer

This will work   WebElement searchbox   driver findElement By name  j username     WebElement searchbox2   driver findElement By name  j password                try          FileInputStream file   new FileInputStream new File  C   paulo xls            HSSFWorkbook workbook   new HSSFWorkbook file          HSSFSheet sheet   workbook getSheetAt 0        for  int i 1  i  lt   sheet getLastRowNum    i                  HSSFCell j username   sheet getRow i  getCell 0              HSSFCell j password   sheet getRow i  getCell 0                 Setting the Cell type as String             j username setCellType j username CELL TYPE STRING              j password setCellType j password CELL TYPE STRING               searchbox sendKeys j username toString                 searchbox2 sendKeys j password toString                   searchbox submit                        driver manage   timeouts   implicitlyWait 10000  TimeUnit MILLISECONDS                 workbook close          file close            catch  FileNotFoundException fnfe          fnfe printStackTrace           catch  IOException ioe          ioe printStackTrace

User · Answer

Formatter will work fine in this case   import org apache poi ss usermodel DataFormatter   FileInputStream fis   new FileInputStream workbookName   Workbook workbook   WorkbookFactory create fis   Sheet sheet   workbook getSheet sheetName   DataFormatter formatter   new DataFormatter    String val   formatter formatCellValue sheet getRow row  getCell col    list add val       Adding value to list

User · Answer

This is one of the other method to solve the Error   Cannot get a text value from a numeric cell    Poi      Go to the Excel Sheet  Drag and Select the Numerics which you are importing Data from the Excel sheet  Go to Format   Number   Then Select  Plain Text  Then Export as  xlsx  Now Try to Run the Script  Hope works Fine       Cannot get a text value from a numeric cell    Poi    img

User · Answer

Using the DataFormatter this issue is resolved  Thanks to  Gagravarr  for the initial post   DataFormatter formatter   new DataFormatter     String empno   formatter formatCellValue cell0

User · Answer

Use that code it definitely works and I modified it   import java io FileInputStream  import java io IOException  import java sql Connection  import java sql DriverManager  import java sql PreparedStatement  import org apache poi poifs filesystem POIFSFileSystem    import org apache poi ss usermodel Row  import org apache poi ss usermodel     public class TestApp        public static void main String   args  throws Exception            try                Class forName   Class forName  com mysql jdbc Driver                Connection con   null              con   DriverManager getConnection  jdbc mysql   localhost tables    root    root                con setAutoCommit false               PreparedStatement pstm   null              FileInputStream input   new FileInputStream  C   Users  Desktop  a1 xls                POIFSFileSystem fs   new POIFSFileSystem input               Workbook workbook              workbook   WorkbookFactory create fs               Sheet sheet   workbook getSheetAt 0               Row row              for  int i   1  i  lt   sheet getLastRowNum    i                      row    Row  sheet getRow i                   String name   row getCell 0  getStringCellValue                    String add   row getCell 1  getStringCellValue                     int  contact    int  row getCell 2  getNumericCellValue                     String email   row getCell 3  getStringCellValue                     String sql    INSERT INTO employee  name  address  contactNo  email  VALUES      name           add          contact          email                         pstm    PreparedStatement  con prepareStatement sql                   pstm execute                    System out println  Import rows     i                             con commit                pstm close                con close                input close                System out println  Success import excel to mysql table              catch  IOException e

User · Answer

If you are processing in rows with cellIterator    then this worked for me         DataFormatter formatter   new DataFormatter         while cellIterator hasNext                                         cell   cellIterator next            String val                           switch cell getCellType                           case Cell CELL TYPE NUMERIC                  val   String valueOf formatter formatCellValue cell                    break              case Cell CELL TYPE STRING                  val   formatter formatCellValue cell                   break

[java] Cannot get a text value from a numeric cell “Poi”

Examples related to java

Examples related to apache-poi