By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,805 Members | 1,280 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,805 IT Pros & Developers. It's quick & easy.

store data from excel in list after reading with java

P: 9
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
Share this Question
Share on Google+
12 Replies


10K+
P: 13,264
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

P: 9
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

10K+
P: 13,264
Read 3(i) in my reply again.
Apr 9 '13 #4

P: 9
Yes, could you be more explanatory with code?
Apr 9 '13 #5

10K+
P: 13,264
Create a class with id, name and salary as properties. You know how to do that, right?
Apr 9 '13 #6

P: 9
yes!next what i have to do?
Apr 9 '13 #7

10K+
P: 13,264
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

P: 9
could you help me with the code?
Apr 9 '13 #9

10K+
P: 13,264
Not before you try it yourself.
Apr 9 '13 #10

P: 9
Could you provide me somewhere a link, an article to study?
Apr 9 '13 #11

10K+
P: 13,264
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

P: 9
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

Post your reply

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