store data from excel in list after reading with java

Question

i am using APACHE POI to read the data from excel files. I would like to store them in lists (like list in c) the result because afterwards I will try to store them in mysql database calling only list[0], list[1] for example. What i will try to do is make this list and after i will use jdbc driver and giving this list to make the tables in mysql. The code for reading excel file is the above:

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

public class readexcel{

@SuppressWarnings({ "unchecked", "unchecked" }) public static void main(String[] args) throws Exception {

//
 // An excel file name. You can create a file name with a full
 // path information.
 //
String filename = "C:\\Users\\xxx\\Documents\\test.xls";

//
// Create an ArrayList to store the data read from excel sheet.
//
List sheetData = new ArrayList();
FileInputStream fis = null;
try {
//
// Create a FileInputStream that will be use to read the
// excel file.
//
fis = new FileInputStream(filename);

 //
 // Create an excel workbook from the file system.
//
HSSFWorkbook workbook = new HSSFWorkbook(fis);
//
// Get the first sheet on the workbook.
//
HSSFSheet sheet = workbook.getSheetAt(0);

 //
  // When we have a sheet object in hand we can iterator on
// each sheet's rows and on each row's cells. We store the
// data read on an ArrayList so that we can printed the
// content of the excel to the console.
 //
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator cells = row.cellIterator();

List data = new ArrayList();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
data.add(cell);
 }

sheetData.add(data);
 }
 } catch (IOException e) {
e.printStackTrace();
 } finally { 
 if (fis != null) {
 fis.close();
 }
 }

 showExcelData(sheetData);
  }

 private static void showExcelData(List sheetData) {
  //
    // Iterates the data and print it out to the console.
  //
for (int i = 0; i < sheetData.size(); i++) {
 List list = (List) sheetData.get(i);
for (int j = 0; j < list.size(); j++) {
Cell cell = (Cell) list.get(j);
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.print(cell.getNumericCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
System.out.print(cell.getRichStringCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
System.out.print(cell.getBooleanCellValue());
}
if (j < list.size() - 1) {
System.out.print(", ");
}
}
System.out.println("");
}
}
}

What i have to add to do what i explain you?

Answer 1

change your conditions part to this should be work :

 if (cell.getCellType() == NUMERIC) {
    System.out.print(cell.getNumericCellValue());
    } else if (cell.getCellType() == STRING) {
    System.out.print(cell.getRichStringCellValue());
    } else if (cell.getCellType() == BOOLEAN) {
    System.out.print(cell.getBooleanCellValue());
    }

Answer 2

Look the code below

public List<ArrayList<String>> readExcelData2(String excelFile) throws IOException {

    List<ArrayList<String>> depts = new ArrayList<ArrayList<String>>();

    FileInputStream excelFileToRead = new FileInputStream(new File(excelFile));

    XSSFWorkbook wb = new XSSFWorkbook(excelFileToRead);

    XSSFSheet sheet = wb.getSheetAt(0);

    XSSFRow row;
    XSSFCell cell;
    int maxDataCount = 0;

    Iterator<Row> rows = sheet.rowIterator();

    while (rows.hasNext()) {
        row = (XSSFRow) rows.next();
        // skip the first row because it will be header
        if (row.getRowNum() == 0) {
            maxDataCount = row.getLastCellNum();
            continue;
        }

        // if the row is empty stop the loop, do not go further
        if (this.isRowEmpty(row, maxDataCount)) {
            // exit processing
            break;
        }

        // define arraylist object to store list of departments of each row
        ArrayList<String> innerArrayList = new ArrayList<String>();

        for (int cn = 0; cn < maxDataCount; cn++) {
            cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK);

            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                innerArrayList.add(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                innerArrayList.add(String.valueOf(cell.getNumericCellValue()));
                break;

            default:
                innerArrayList.add(cell.getStringCellValue());
                break;
            }

        }
        depts.add(innerArrayList);

    }
    return depts;
}

public boolean isRowEmpty(Row row, int lastCellNo) {
    for (int c = row.getFirstCellNum(); c < lastCellNo; c++) {
        Cell cell = row.getCell(c, Row.CREATE_NULL_AS_BLANK);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            return false;
        }
    }
    return true;
}

Answer 3

I found this function usable

public static ArrayList<ArrayList<String>> GetExcelTableInto2DArrayListString(String excelFile, boolean debug){

    ArrayList<ArrayList<String>> OUT = new ArrayList<ArrayList<String>>();  
        File myFile = new File(excelFile); 
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(myFile);
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 

        // Finds the workbook instance for XLSX file 
        XSSFWorkbook myWorkBook = null;
        try {
            myWorkBook = new XSSFWorkbook (fis);
        } catch (IOException e) {
            e.printStackTrace();
        } 

        // Return first sheet from the XLSX workbook 
        XSSFSheet mySheet = myWorkBook.getSheetAt(0); 

        // Get iterator to all the rows in current sheet 
        Iterator<Row> rowIterator = mySheet.iterator(); 

        // Traversing over each row of XLSX file 
        int count=1;
        while (rowIterator.hasNext()) { 
            Row row = rowIterator.next();
            ArrayList<String> InnerArray = new ArrayList<String>() ;
            if(debug)System.out.print(count + ". \t");
        // For each row, iterate through each columns 
            Iterator<Cell> cellIterator = row.cellIterator(); 

            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next(); 

                switch (cell.getCellType()) { 
                case Cell.CELL_TYPE_STRING: 
                    String c = cell.getStringCellValue();
                    if(debug)System.out.print(c + "\t");
                    InnerArray.add(c);
                    break; 
                case Cell.CELL_TYPE_NUMERIC: 
                    int n = (int) cell.getNumericCellValue();
                    if(debug)System.out.print(n + "\t");
                    InnerArray.add(String.valueOf(n));
                    break; 
                case Cell.CELL_TYPE_BOOLEAN:
                    boolean b = cell.getBooleanCellValue();
                    if(debug)System.out.print(b + "\t");
                    InnerArray.add(String.valueOf(b));
                break; 
                default : 
                    } 
                }
            if(debug)System.out.println("");
            OUT.add(InnerArray);
            count++; 
            }

    return OUT;
}

Answer 4

initialize the array list way before starting to iterate the sheet, the array list must have a scope to persist anywhere in the row and column iteration of the excel sheet .

ArrayList myList = new ArrayList()

the put these line inside the cell iteration loop which is being performed n row basis

if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) 
{
System.out.print(cell.getNumericCellValue());
myList.add(cell.getNumericCellValue());
} 
else if (cell.getCellType() == Cell.CELL_TYPE_STRING) 
{
System.out.print(cell.getRichStringCellValue());
myList.add(cell.getRichStringCellValue());
} 
else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) 
{
System.out.print(cell.getBooleanCellValue());
myList.add(cell.getBooleanCellValue());
}

now you process this list to insert data in to your DataBase