-2

I am trying with the following code to fetch all the data of excel. The number of rows in excel are 5 and columns are 20.

PFB below Code

package testRunner;
import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;

public class ReadWriteExcel {

    public static void main(String args[])
    //public static void ReadExcel() throws IOException
    {
        try
        {
        //Specify the File path which you want to Create or Write
    File src=new File("D:\\eclipse-workspace\\CucumberWithTestNGForSelenium\\PersonalInformation.xlsx");
    //Load the file
    FileInputStream fis=new FileInputStream(src);
    //Load the Workbook
    @SuppressWarnings("resource")
    XSSFWorkbook wb=new XSSFWorkbook(fis);
    //get the sheet which you want to modify or create
    XSSFSheet sh1=wb.getSheetAt(0);

    //Finding the number of rows
    int firstRow=sh1.getFirstRowNum();
    int lastRow=sh1.getLastRowNum()+1;
    int no_of_rows=lastRow-firstRow;



    for(int i=0;i<no_of_rows;i++)
    {
        //Finding the number of Columns

        int no_of_columns=sh1.getRow(i).getLastCellNum();


        for(int j=0;j<no_of_columns;j++)
        {
    System.out.print(" "+ sh1.getRow(i).getCell(j).getStringCellValue()+ " ");
        }
        System.out.println();
    }
        }
        catch(Exception e)
        {
            e.getMessage();
        }}}

And in Console,the first row is displaying all columns but from the second row,its displaying only 3-4 columns.

PFB

WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by org.apache.poi.openxml4j.util.ZipSecureFile$1 (file:/C:/Users/Mehak/.m2/repository/org/apache/poi/poi-ooxml/3.17/poi-ooxml-3.17.jar) to field java.io.FilterInputStream.in
WARNING: Please consider reporting this to the maintainers of org.apache.poi.openxml4j.util.ZipSecureFile$1
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
 Title  FirstName  LastName  EmailValidation  Password  Date  Month  Year  SignUpCheckbox  AddessFirstName  AddressLastName  Company  MainAddress  AddressLine2  city  State  PostalCode  Country  Mobile  AddressAlias 
 Mr  Simon Duffy  aduffy@abc.com

enter image description here

Excel Part continued on second image :_

enter image description here

mehak
  • 59
  • 8

2 Answers2

0

I suggest you have a look at the Cell getStringCellValue() java docs:

/**
 * Get the value of the cell as a string
 * <p>
 * For numeric cells we throw an exception. For blank cells we return an empty string.
 * For formulaCells that are not string Formulas, we throw an exception.
 * </p>
 * @return the value of the cell as a string
 */
String getStringCellValue();

It could be that the CellType of the other cells on row 2 are not of type STRING. You can try writing a utility method like so (works as of poi 3.16):

private static String myCellStringValue(Cell cell, CellType cellType) {
 String data = EMPTY;

 try {
  switch (cellType) {
   case STRING:
    data = cell.getRichStringCellValue().toString();
    break;
   case NUMERIC:
    data = String.valueOf(cell.getNumericCellValue());
    break;
   case BOOLEAN:
    data = String.valueOf(cell.getBooleanCellValue());
    break;
   case FORMULA:
    data = myCellStringValue(cell, cell.getCachedFormulaResultTypeEnum());
    break;
   case BLANK:
   case ERROR:
   case _NONE:
    break;
  }
 } catch (Exception e) {
  //your catch clause
 }
 return data;
}

And then call the utility method on your loop:

for (int j = 0; j < no_of_columns; j++) {
 Cell cell = sh1.getRow(i).getCell(j);
 System.out.print(" " + myCellStringValue(cell, cell.getCellTypeEnum()) + " ");
}
iamkenos
  • 1,226
  • 4
  • 21
  • 45
  • I had tried with giving the value of i as i=2 in the above code then System.out.print(" "+ sh1.getRow(2).getCell(j).getStringCellValue()+ " "); It displays all the columns of row in that case. This issue of not displaying all the columns of next row is only coming if i fetch the whole data.So, I am guessing that there is no issue with the datatype of columns – mehak Jun 05 '18 at 11:18
  • Can you upload your excel file and link it in your question? – iamkenos Jun 06 '18 at 01:52
  • Shared images of Excel data – mehak Jun 07 '18 at 06:01
  • Well this is the exception I got from your code: java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell – iamkenos Jun 07 '18 at 07:24
  • Consider giving the myCellStringValue method a try. It ran fine for me using that approach. Also, I suggest using try-with-resources for fis or make sure to close your input stream after your operations. – iamkenos Jun 07 '18 at 07:29
0

Can you try changing your System.out.print(" "+ sh1.getRow(i).getCell(j).getStringCellValue()+ " "); inside the for loop with the code below and check if it solves your issue.

DataFormatter formatter = new DataFormatter();
String val = formatter.formatCellValue(sh1.getRow(i).getCell(j));
System.out.print(" "+ val + " ");

OR

String val = sh1.getRow(i).getCell(j).toString();
System.out.print(" "+ val + " ");

OR

System.out.print(" "+ sh1.getRow(i).getCell(j)+ " ");
Sijin
  • 128
  • 9