Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
Win a copy of Emmy in the Key of Code this week in the General Computing forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Paweł Baczyński
  • Piet Souris
  • Vijitha Kumara

Read the excel through JAva and save in Database

 
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Team,

I need to upload once excel file and read it and save in database using POI,Please let me know how to do validations as well like emp id as 8 chars ,

Plaese reply if some sample code is there,

Thanks,
Uj
 
Rancher
Posts: 43011
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can store files in the DB as a BLOB; POI has nothing to do with that.

Reading and processing the file would involve using the POI API, about which you can read up at http://poi.apache.org/spreadsheet/index.html and its linked pages; there are lots of code examples.
 
Greenhorn
Posts: 2
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
package com.howtodoinjava.demo.poi;
//import statements
public class ReadExcelDemo
{
public static void main(String[] args)
{
try
{
FileInputStream file = new FileInputStream(new File("howtodoinjava_demo.xlsx"));

//Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);

//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);

//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();

while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
//Check the cell type and format accordingly
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
}
}
System.out.println("");
}
file.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}

Output:

ID NAME LASTNAME
1.0 Amit Shukla
2.0 Lokesh Gupta
3.0 John Adwards
4.0 Brian Schultz
 
santhosh yacham
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
create preparedstatemt object and while reading the values push that values into query dynamically ...u are going to save....
 
ujwwala tem
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Santosh,

I have already developed some sample what you have posted, read the values but I need to save each and every row from excel into database and also wanted to ignore heading part from excel,
Please let me know if you have sample code,

Thanks,
Uj
 
Bartender
Posts: 3648
16
Mac OS X Firefox Browser Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The logical approach is to create a bean for each row in the excel (same representation as the database table) and save to database.
 
Ulf Dittmer
Rancher
Posts: 43011
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

ujwwala tem wrote:I need to save each and every row from excel into database


That code works row by row; what difficulty are you facing adding the DB save code for each row?

also wanted to ignore heading part from excel,


Assuming that the "headings" are in particular rows (maybe the first or second one), or otherwise recognizable programmatically (maybe it's bold), there should be no problem skipping those rows.

K. Tsang wrote:The logical approach is to create a bean for each row in the excel


How would creating beans help (unless one wanted to use some ORM tool)?
 
ujwwala tem
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Tahnks all for your reply but I am beginner,Request you to give saome sample code to parse the excel and store in oracle database,

As raising query in miy mind how row by row I will store in database ,how I will populate each as a dao object,Please guide,

Thanks,
Uj
 
Ulf Dittmer
Rancher
Posts: 43011
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, we don't simply hand out code here, but we'll help you write it yourself.

I advise to break the problem down into manageable pieces: start by parsing the file and extracting the information you need. Only then think about storing anything in the DB, and how that might be accomplished.
 
ujwwala tem
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Ulf,

Yes I will start by my own,will let you know if I face difficulty,

Thanks,
Uj
 
ujwwala tem
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

Still I have few questions,

I need to prepare use caes for reading the excel and storing in database,

1.Once validated I need to store the data in DB
2.what if on nth record the DB goes down
2.how should I set the batch size and how it will give exception if not insreted all,

Plaese clarify the use cases and any scenarios you experts know in this case,

Tahnks,
Uj
 
Ulf Dittmer
Rancher
Posts: 43011
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use cases are something that's used for requirements gathering - shouldn't you -or whoever gave you this task- know what the software is supposed to do?
 
ujwwala tem
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ulf,

There is no requirements gathering as such,we are designing for ourself ease the task,everytime the Hr users send us excel,we manually update from databse,instead of thet we will give them page and they will upload from UI,

Please suggest for the different scenarios in this case I have to think of,with my above message,Also one thought coming in my mind as if there are 2 records ,sometimes 100 records so If I set the batchsize of 100 how the code will be like ,if it reaches the batch size then only we execute the batch but how to set the minimum and if it is sometimes only one record then how to execute with batch,,
Please reply in detail,

Thanks,
Uj
 
Ulf Dittmer
Rancher
Posts: 43011
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, that doesn't make sense. You need to know what your software is supposed to do, only then can you even design it. And before that, implementation questions are premature.

As to the batch size, I advise to play around with it to learn how it works. Then you can make an informed decision whether it makes sense to use in your scenarios. The https://coderanch.com/how-to/java/PaginationOrPaging page may have some applicable information on the subject.
 
ujwwala tem
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ulf,

Actually we have to think of the scanarios may incur as this is internal application,

Also Please let me know your inputs on how to set the batch size (sometimes we get 2 records or 100 records),Please reply,

Thanks,
Uj
 
Sheriff
Posts: 24654
58
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

ujwwala tem wrote:Also Please let me know your inputs on how to set the batch size (sometimes we get 2 records or 100 records),Please reply



If you only have to update a few records, it's hardly worth bothering with using batches. I certainly wouldn't bother unless I later found there was a performance problem which that would fix.
 
Ulf Dittmer
Rancher
Posts: 43011
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Actually we have to think of the scanarios may incur as this is internal application,


Well, that's kind of what I'm saying: *You* need to think about how this software will be used; that will drive design decisions. We don't know anything about that.
 
Beauty is in the eye of the tiny ad.
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!