Reading Excel file using poi and shift it to two-dimensional array

Question

I am using POI to read excel file and convert it to a two-dimensional array. Following is code section:

import java.io.FileInputStream;

import org.apache.poi.ss.usermodel.CellValue;
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 XlsxRead_2 {

public XlsxRead_2(){
    getvalue_1();
}



public static void getvalue_1(){
    XSSFRow row;
    XSSFCell cell;
    String [][] value =null;
    double[][] nums =null;

    try {
        FileInputStream inputStream = new FileInputStream("TEST.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

        //get sheet number
        int sheetCn = workbook.getNumberOfSheets();

        for(int cn = 0; cn < sheetCn; cn++){

            //get 0th sheet data
            XSSFSheet sheet = workbook.getSheetAt(cn);

            //get number of rows from sheet
            int rows = sheet.getPhysicalNumberOfRows();

            //get number of cell from row
            int cells = sheet.getRow(cn).getPhysicalNumberOfCells(); 


            for (int r = 0; r < rows; r++) {
                row = sheet.getRow(r); // bring row
                if (row != null) {
                    for (int c = 0; c < cells; c++) {
                        cell = row.getCell(c);
                        value  = new String[rows][cells];
                        nums= new double [rows][cells];

                        if (cell != null) {

                            switch (cell.getCellType()) {                   

                            case XSSFCell.CELL_TYPE_FORMULA:
                                value [r][c]= cell.getCellFormula();
                                break;

                            case XSSFCell.CELL_TYPE_NUMERIC:
                                value [r][c]= "" + cell.getNumericCellValue();
                                break;

                            case XSSFCell.CELL_TYPE_STRING:
                                value [r][c]= "" + cell.getStringCellValue();
                                break;

                            case XSSFCell.CELL_TYPE_BLANK:
                                value [r][c]= "[BLANK]";
                                break;

                            case XSSFCell.CELL_TYPE_ERROR:
                                value [r][c]= "" + cell.getErrorCellValue();
                                break;
                            default:                    
                            }                               
                            System.out.print(value);

                        } else {
                            System.out.print("[null]\t");
                        }
                    } // for(c) 
                    System.out.print("\n");
                }
            } // for(r) 
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}


public class Starter {

public static void main(String[] args) {


    XlsxRead_2 gv=new XlsxRead_2();
}

}

However, it didn't work properly. Attached is wrong result in JAVA. When i worked it without using array, it work properly. Any suggestions is appreciated.

Result in java:
[[Ljava.lang.String;@167fdd33[[Ljava.lang.String;@1e965684
[[Ljava.lang.String;@4d95d2a2[[Ljava.lang.String;@53f65459
[[Ljava.lang.String;@3b088d51[[Ljava.lang.String;@1786dec2
[[Ljava.lang.String;@74650e52[[Ljava.lang.String;@15d0c81b
[[Ljava.lang.String;@6acdbdf5[[Ljava.lang.String;@4b1c1ea0
[[Ljava.lang.String;@3712b94[[Ljava.lang.String;@2833cc44
[[Ljava.lang.String;@33f88ab[[Ljava.lang.String;@27a8c74e

looks like you are close. The problem is that you are printing a reference to an Object (in this case String[rows][cells]). You need to iterate throw this array an print each array Object seperately. — Scary Wombat
– Scary Wombat, Commented Mar 9, 2015 at 5:38

emin · Accepted Answer · 2015-03-09 08:20:47Z

You should create array after first for loop. And also you should create values array for each sheet. And one more point you should print value[r][c] not value I hope that helps you.

public class XlsxRead_2 {

   public static void main(String[] args) {
       XlsxRead_2 xread2 = new XlsxRead_2();
   }

   public XlsxRead_2() {
       getvalue_1();
   }

   public static void getvalue_1() {
       XSSFRow row;
       XSSFCell cell;
       String[][] value = null;
       double[][] nums = null;

       try {
           FileInputStream inputStream = new FileInputStream("TEST.xlsx");
           XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

           // get sheet number
           int sheetCn = workbook.getNumberOfSheets();

           for (int cn = 0; cn < sheetCn; cn++) {

               // get 0th sheet data
               XSSFSheet sheet = workbook.getSheetAt(cn);

               // get number of rows from sheet
               int rows = sheet.getPhysicalNumberOfRows();

               // get number of cell from row
               int cells = sheet.getRow(cn).getPhysicalNumberOfCells();

               value = new String[rows][cells];

               for (int r = 0; r < rows; r++) {
                   row = sheet.getRow(r); // bring row
                   if (row != null) {
                       for (int c = 0; c < cells; c++) {
                           cell = row.getCell(c);
                           nums = new double[rows][cells];

                           if (cell != null) {

                               switch (cell.getCellType()) {

                               case XSSFCell.CELL_TYPE_FORMULA:
                                   value[r][c] = cell.getCellFormula();
                                   break;

                               case XSSFCell.CELL_TYPE_NUMERIC:
                                   value[r][c] = ""
                                        + cell.getNumericCellValue();
                                   break;

                               case XSSFCell.CELL_TYPE_STRING:
                                   value[r][c] = ""
                                        + cell.getStringCellValue();
                                   break;

                               case XSSFCell.CELL_TYPE_BLANK:
                                  value[r][c] = "[BLANK]";
                                  break;

                               case XSSFCell.CELL_TYPE_ERROR:
                                  value[r][c] = ""+cell.getErrorCellValue();
                                break;
                            default:
                            }
                            System.out.print(value[r][c]);

                        } else {
                            System.out.print("[null]\t");
                        }
                    } // for(c)
                    System.out.print("\n");
                }
            } // for(r)
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
  }
}

Changing code as your advice, It works properly. Thank you! :)

Collectives™ on Stack Overflow

Reading Excel file using poi and shift it to two-dimensional array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related