I need to invoke an excel worksheet on clicking a button on JSP page and after filling in the Excel sheet, the data must be stored in a Database system like Oracle/ MySQL.
I would appreciate if anyone helps me out..
I can see 2 options here..
1) Create macros in Excel that will send the data from the browser to server. Keep this excel on server and show it on browser to user. On updating the excel your macros will save the data in DB.
Note: There are browser restrictions here, you need to investigate more on this option.
Or check out for example Excel taglib in Coldtags suite:
For your requirement, first get a java Excel API:
Apache POI, JExcelApi, OpenXLS are opensource apis available
When you click a button on JSP, use one of the above API's to create a Excel Templete with pre populated data and allow the user to download that excel. Download to Excel can be done by outputting the excel document to Servlet OutputStream using the above API's
Now the user can update the document and you can do one of the two option:
1. Give a File Upload option on your JSP and ask the user to upload the file once he has entered all the data.
2. In the servlet get the Uploaded Excel file and use the Excel Apis to Read the Excel data and update them to your database using your business logic code.
1. Find some WebDAV api (Milton) that can be embedded to your web appliation.
2. WebDAV will expose your excel file as Web Base File System
2. MS Office has inbuild WebDAV client and it can directly save your document into WebDAV server.
3. Using your WebDAV api you should be able to know when the user has saved the file and then you can use your Excel API to read the file and save data to Database
Hope this will guide you to achieve your needs...