Read data from xlsx using JAVA apache poi fails to read the last cell of a row

huangapple go评论74阅读模式
英文:

Read data from xlsx using JAVA apache poi fails to read the last cell of a row

问题

以下是从9个Excel单元格读取数据的代码。

Excel数据格式位于代码末尾。
它有两行。
它从不读取最后一个单元格的数据,尝试了所有组合。

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Exceldata {
    public static XSSFWorkbook workbook = null;
    public static XSSFSheet sheet = null;
    public static XSSFRow row = null;
    public static XSSFCell cell = null;
    public static String[] col_name = new String[9];
    public static String[][] user_data = new String[2][9];

    public static void main(String[] args) {
        try {
            File src = new File("C:\\Users\\Public\\Testdata.xlsx");
            FileInputStream fis = new FileInputStream(src);
            XSSFWorkbook book = new XSSFWorkbook(fis);
            XSSFSheet sheet_userdata = book.getSheet("UserDetails");
            for (int p = 0; p < book.getNumberOfSheets(); p++) {
                System.out.println("Sheet Name is : " + book.getSheetName(p));
            }
            row = sheet_userdata.getRow(0);
            for (int j = 0; j < 9; j++) {
                cell = row.getCell(j);
                col_name[j] = String.valueOf(cell.getStringCellValue());
                System.out.println(col_name[j]);
            }
            // Read data from cells
            for (int i = 0; i < sheet_userdata.getLastRowNum(); i++) {
                row = sheet_userdata.getRow(i + 1);
                for (int k = 0; k < 9; k++) {
                    if ((cell = row.getCell(k)) != null) {
                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_STRING:
                                if (k == 1 || k == 2 || k == 3 || k == 5 || k == 6 || k == 7 || k == 8) {
                                    user_data[i][k] = String.valueOf(cell.getStringCellValue());
                                    System.out.println(col_name[k] + " is : ");
                                    System.out.println(user_data[i][k]);
                                }
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if (k == 0 || k == 4) {
                                    user_data[i][k] = String.valueOf((long) cell.getNumericCellValue());
                                    System.out.println(col_name[k] + " is : ");
                                    System.out.println(user_data[i][k]);
                                }
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                user_data[i][k] = cell.getStringCellValue();
                                System.out.println(user_data[i][k]);
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                if (k == 8) {
                                    user_data[i][k] = String.valueOf(cell.getStringCellValue());
                                    System.out.println(col_name[k] + " is : ");
                                    System.out.println(user_data[i][k]);
                                }
                                break;
                            case Cell.CELL_TYPE_FORMULA:
                                user_data[i][k] = String.valueOf(cell.getCellFormula());
                                System.out.println(user_data[i][k]);
                                break;
                            case Cell.CELL_TYPE_ERROR:
                                user_data[i][k] = String.valueOf(cell.getErrorCellString());
                                System.out.println(user_data[i][k]);
                                break;
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

XLS表格数据有两行,类似于以下内容(注意:状态单元格为空):

 S.No.|    ID        |First Name  |Last Name  |Contact number |Email id   |Usermode   |channel    |Status
1    test@test.com Ram          singh       45455666        xyz@test.com Password    XYZ        empty cell
英文:

Below is the code to read data from 9 excel cells.

Excel data format is at the end of code.
It has two rows.
It never reads the last cells data,tried all the combinations

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Exceldata {
public static XSSFWorkbook workbook = null;
public static XSSFSheet sheet = null;
public static XSSFRow row = null;
public static XSSFCell cell = null;
public static  String[] col_name= new String[9];
public static String[][] user_data=new String[2][9];
public static void main(String[] args) {
try {
File src= new File(&quot;C:\\Users\\Public\\Testdata.xlsx&quot;);
FileInputStream fis=new FileInputStream(src);
XSSFWorkbook book= new XSSFWorkbook(fis);         
XSSFSheet sheet_userdata= book.getSheet(&quot;UserDetails&quot;);          
for(int p=0;p&lt;book.getNumberOfSheets();p++) 
{
System.out.println(&quot;Sheet Name is : &quot;+ book.getSheetName(p));
}
row=sheet_userdata.getRow(0);
for(int j =0;j&lt;9;j++) 
{
cell=row.getCell(j);
col_name[j]=String.valueOf(cell.getStringCellValue());
System.out.println(col_name[j]);
}
//Read  data from cells
for(int i=0;i&lt;sheet_userdata.getLastRowNum();i++)
{
row=sheet_userdata.getRow(i+1);
for(int k=0;k&lt;9;k++) 
{
if((cell=row.getCell(k))!= null) 
{
switch(cell.getCellType())
{
case Cell.CELL_TYPE_STRING:
if(k==1) 
{
user_data[i][k]=String.valueOf(cell.getStringCellValue());
System.out.println(col_name[k]+&quot; is : &quot;);
System.out.println(user_data[i][k]);
}else if(k==2)
{
user_data[i][k]=String.valueOf(cell.getStringCellValue());
System.out.println(col_name[k]+&quot; is : &quot;);
System.out.println(user_data[i][k]);
}
else if(k==3) 
{
user_data[i][k]=String.valueOf(cell.getStringCellValue());
System.out.println(col_name[k]+&quot; is : &quot;);
System.out.println(user_data[i][k]);
}
else if(k==5) 
{
user_data[i][k]=String.valueOf(cell.getStringCellValue());
System.out.println(col_name[k]+&quot; is : &quot;);
System.out.println(user_data[i][k]);
}
else if(k==6) 
{
user_data[i][k]=String.valueOf(cell.getStringCellValue());
System.out.println(col_name[k]+&quot; is : &quot;);
System.out.println(user_data[i][k]);
}
else if(k==7) 
{
user_data[i][k]=String.valueOf(cell.getStringCellValue());
System.out.println(col_name[k]+&quot; is : &quot;);
System.out.println(user_data[i][k]);
}
else if(k==8) 
{
user_data[i][k]=String.valueOf((cell.getStringCellValue()));
System.out.println(col_name[k]+&quot; is : &quot;);
System.out.println(user_data[i][k]);
}
break;
case Cell.CELL_TYPE_NUMERIC:
if(k==0) 
{
user_data[i][k]=String.valueOf((long)(cell.getNumericCellValue()));
System.out.println(col_name[k]+&quot; is : &quot;);                                        
System.out.println(user_data[i][k]);                                                                              
}
else if(k==4) 
{
user_data[i][k]=String.valueOf((long)(cell.getNumericCellValue()));
System.out.println(col_name[k]+&quot; is : &quot;);
System.out.println(user_data[i][k]);
}
break;
case Cell.CELL_TYPE_BOOLEAN:
user_data[i][k]=cell.getStringCellValue();
System.out.println(user_data[i][k]);
break;
case Cell.CELL_TYPE_BLANK:
//user_data[i][k]=&quot;&quot;;
if(k==8) 
{
user_data[i][k]=String.valueOf((cell.getStringCellValue()));
System.out.println(col_name[k]+&quot; is : &quot;);
System.out.println(user_data[i][k]);
}
break;
case Cell.CELL_TYPE_FORMULA:
user_data[i][k]=String.valueOf(cell.getCellFormula());
System.out.println(user_data[i][k]);
break;
case Cell.CELL_TYPE_ERROR:
user_data[i][k]=String.valueOf(cell.getErrorCellString());
System.out.println(user_data[i][k]);
break;
}
}
}
}
}catch(Exception e) 
{
e.printStackTrace();
}
}
}

XLS sheet data has two rows like this( NOte : Status cell is empty)

S.No.| ID |First Name |Last Name |Contact number |Email id |Usermode |channel |Status
1 test@test.com Ram singh 45455666 xyz@test.com Password XYZ empty cell

答案1

得分: 0

Option 1
你已经检查了单元格为空的情况,但没有检查是否为""(空字符串)的情况。有可能的情况是,如果你在 Excel 表中手动添加了数据,该单元格不是空白的,可能包含一些空格等。尝试添加以下内容:

if ((row.getCell(k)) != &quot;&quot;) {
    // 现在检查列是否为8
    if (k == 8)

Option 2
该单元格的类型可能为 UNKNOWN,因此也要添加以下情况:

case Cell.CellType _NONE

Option 3
你期望该单元格的类型为空白,但它可能是数字或字符串,因此尝试在这两种情况的 switch 语句中都添加 K==8 的条件,或者首先确定该单元格的具体类型。

英文:

Option 1
you have checked cell null condition but not "" this condition.There might be chances that if you have added data manually to excel sheet that cell is not BLANK and have some space etc. Try to Add this

if((row.getCell(k))!= &quot;&quot;){
//now check column is 8
if(k==8)

Option 2
That cell type might be UNKNOWN so add this case as well

case Cell.CellType _NONE 

Option 3
Your are expecting that cell type as blank but it could be either numeric or string so try to add K==8 condition in both of this switch case or find out specific type of that cell first.

答案2

得分: 0

实际上,当单元格为空白或空白,或者在使用 getcell(int k) 函数时其中没有值时,它会产生空指针异常。因此,解决方案是在 if((cell=row.getCell(k))!=null) 之前添加一行代码,将空值转换为空白,就像这样:
cell=row.getcell(k,row.CREATE_NULL_AS_BLANK)。

英文:

Actually it gives a NULL pointer exception when a cell is blank or empty or no values in it when using getcell(int k);,So the solution is before the if((cell=row.getCell(k))!=null) add a line to convert the null to blank like this
cell=row.getcell(k,row.CREATE_NULL_AS_BLANK).

huangapple
  • 本文由 发表于 2020年8月28日 03:55:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/63623318.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定