I need to read excel spread sheet files(*.xls), validate some fields (say mandatory fields and date validation) and then insert data in to DB table.
if any field is not satisfying the rule( say having invalid date), entire row should be listed in separate bad record.
please suggest me how to parse and work on excel sheet. I have gone through Apache POI (http://poi.apache.org/), but no idea how can I make this useful?
Hi Rakesh,
Please post your queries in appropriate threads. Processing excel is not related to B2B.
As for your question, it is all about using the poi jars and writing java services to do your job. Please check the javadoc of poi for the available methods.
Regards,
Mani
Hi Manikandan,
Sorry for the inconvenience. Can you please guide me about where to post my queries related to excel sheet processing in webMethods, if B2B Integration is not the correct place?
Also, I need to know the suitable approach to implement the requirement to read the excel sheet, validate and then insert data to DB table or report error with any invalid row.
Regards,
Rakesh
Are you trying to read xls or .csv file and for csv it can be parsed/handled via FlatFile Adapter services via FFSchema definition. Did you ever check and tried this csv route if the interface requestor agreed?
HTH,
RMG
Hi Rakesh,
The code would look something like below,
InputStream inp = new FileInputStream(“workbook.xls”);
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(2);
Cell cell = row.getCell(3);
After you reach the cell, you can get its value and do whatever you want with it!
Check out the below links for help,
http://poi.apache.org/spreadsheet/quick-guide.html
http://poi.apache.org/spreadsheet/examples.html
Since this is mostly related to ESB, you could have posted this question in, “ESB & Integration Community”.
Regards,
Mani
PS : Using eclipse (designer) for Java development is much better. Try it out!
Thank you Manikandan! This was very helpful! 8)