英文:
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("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)
{
user_data[i][k]=String.valueOf(cell.getStringCellValue());
System.out.println(col_name[k]+" is : ");
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]+" is : ");
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]+" is : ");
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]+" is : ");
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]+" is : ");
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]+" is : ");
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]+" is : ");
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]+" is : ");
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]+" 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:
//user_data[i][k]="";
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 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)) != "") {
// 现在检查列是否为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))!= ""){
//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).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论