469,142 Members | 1,117 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,142 developers. It's quick & easy.

store data from excel in list after reading with java

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:

Expand|Select|Wrap|Line Numbers
  1. import java.io.FileInputStream;
  2. import java.io.IOException;
  3. import java.util.ArrayList;
  4. import java.util.Iterator;
  5. import java.util.List;
  6.  
  7. import org.apache.poi.hssf.usermodel.HSSFCell;
  8. import org.apache.poi.hssf.usermodel.HSSFRow;
  9. import org.apache.poi.hssf.usermodel.HSSFSheet;
  10. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  11. import org.apache.poi.ss.usermodel.Cell;
  12.  
  13. public class readexcel{
  14.  
  15. @SuppressWarnings({ "unchecked", "unchecked" })
  16. public static void main(String[] args) throws Exception {
  17.  
  18. //
  19. // An excel file name. You can create a file name with a full
  20. // path information.
  21. //
  22. String filename = "C:\\Users\\Efi\\Documents\\test5.xls";
  23.  
  24. //
  25. // Create an ArrayList to store the data read from excel sheet.
  26. //
  27. List sheetData = new ArrayList();
  28. FileInputStream fis = null;
  29. try {
  30. //
  31. // Create a FileInputStream that will be use to read the
  32. // excel file.
  33. //
  34. fis = new FileInputStream(filename);
  35.  
  36. //
  37. // Create an excel workbook from the file system.
  38. //
  39. HSSFWorkbook workbook = new HSSFWorkbook(fis);
  40. //
  41. // Get the first sheet on the workbook.
  42. //
  43. HSSFSheet sheet = workbook.getSheetAt(0);
  44.  
  45. //
  46. // When we have a sheet object in hand we can iterator on
  47. // each sheet's rows and on each row's cells. We store the
  48. // data read on an ArrayList so that we can printed the
  49. // content of the excel to the console.
  50. //
  51. Iterator rows = sheet.rowIterator();
  52. while (rows.hasNext()) {
  53. HSSFRow row = (HSSFRow) rows.next();
  54. Iterator cells = row.cellIterator();
  55.  
  56. List data = new ArrayList();
  57. while (cells.hasNext()) {
  58. HSSFCell cell = (HSSFCell) cells.next();
  59. data.add(cell);
  60. }
  61.  
  62. sheetData.add(data);
  63. }
  64. } catch (IOException e) {
  65. e.printStackTrace();
  66. } finally {
  67. if (fis != null) {
  68. fis.close();
  69. }
  70. }
  71.  
  72. showExcelData(sheetData);
  73. }
  74.  
  75. private static void showExcelData(List sheetData) {
  76. //
  77. // Iterates the data and print it out to the console.
  78. //
  79. for (int i = 0; i < sheetData.size(); i++) {
  80. List list = (List) sheetData.get(i);
  81. for (int j = 0; j < list.size(); j++) {
  82. Cell cell = (Cell) list.get(j);
  83. if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
  84. System.out.print(cell.getNumericCellValue());
  85. } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  86. System.out.print(cell.getRichStringCellValue());
  87. } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
  88. System.out.print(cell.getBooleanCellValue());
  89. }
  90. if (j < list.size() - 1) {
  91. System.out.print(", ");
  92. }
  93. }
  94. System.out.println("");
  95. }
  96. }
  97. }
What i have to add to do what i explain you?
Apr 9 '13 #1
13 32129
r035198x
13,262 8TB
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".
Apr 9 '13 #2
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?
Apr 9 '13 #3
r035198x
13,262 8TB
Read 3(i) in my reply again.
Apr 9 '13 #4
Yes, could you be more explanatory with code?
Apr 9 '13 #5
r035198x
13,262 8TB
Create a class with id, name and salary as properties. You know how to do that, right?
Apr 9 '13 #6
yes!next what i have to do?
Apr 9 '13 #7
r035198x
13,262 8TB
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
Expand|Select|Wrap|Line Numbers
  1. List<Record> excelData = new ArrayList<Record>(); 
Apr 9 '13 #8
could you help me with the code?
Apr 9 '13 #9
r035198x
13,262 8TB
Not before you try it yourself.
Apr 9 '13 #10
Could you provide me somewhere a link, an article to study?
Apr 9 '13 #11
r035198x
13,262 8TB
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.
Apr 9 '13 #12
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
Apr 9 '13 #13
Prutuja
1 Bit
Make a class that has three properties: id, name, and salary.
Java
2 Weeks Ago #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Forconi Boris via .NET 247 | last post: by
2 posts views Thread by jmensch | last post: by
36 posts views Thread by Crirus | last post: by
3 posts views Thread by piotrek | last post: by
1 post views Thread by sunnyK | last post: by
1 post views Thread by SagarDoke | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.