store data from excel in list after reading with java

[mixahlel]

Hi,
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. 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\\Efi\\Documents\\test5.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?

[r035198x]

1.) Use code tags when posting code.

2.) Much better to just csv the excel and load directly to database using mysql's LOAD DATA INFILE command.

3.) If you insist on using Java then you can either

i.) create a class that represents a row of data. Then you read the data creating a list of records of that type. You then pass that list to a method that loads the values into the database. You only need to write one method that generates an insert of an object that represents that row.
ii.) Insert the data into the DB as you read the excel file. So for each record that you read just create the insert statement for it and execute it or batch.

Either way, read up on "executing a batch insert using JDBC".

[mixahlel]

No exactly! Let me be more specific! The excel files that i have contain the above:
ID NAME SALARY
232 SAM 4587
121 TIM 1254
542 PAUL 1247
12 JIM 4587

At the beginning i managed to read them with the code i send you before but i would like the result that shown in console to put them in a list!
How i will do this?

[r035198x]

Read 3(i) in my reply again.

[mixahlel]

Yes, could you be more explanatory with code?

[r035198x]

Create a class with id, name and salary as properties. You know how to do that, right?

[mixahlel]

yes!next what i have to do?

[r035198x]

So, let's say you called the class Record, then when you read the data for a line put it into a Record object and add that object to a list of records

List<Record> excelData = new ArrayList<Record>(); 

[mixahlel]

could you help me with the code?

[r035198x]

Not before you try it yourself.

[mixahlel]

Could you provide me somewhere a link, an article to study?

[r035198x]

About which part exactly? Are you completely new to Java? If so then you should start by learning the language using the oracle tutorial here http://docs.oracle.com/javase/tutorial/java/ not trying to read excel files and writing to databases.

[mixahlel]

I have to do for a project in my school. That's why i am writing in here. But ok..thank you for your time

[Prutuja]

Make a class that has three properties: id, name, and salary.
Java