1 / 27

Import Data From Excel File into Database

This guide covers the process of importing student information from an Excel file into a database using the jxl.jar library. It explains the structure of an Excel file, detailing workbooks, sheets, rows, and cells containing data. We will address the challenges of importing student details and provide step-by-step solutions, including database design for storing student information such as IDs, names, and gender. Additionally, the guide includes coding examples for uploading the Excel file and developing JSP pages to display stored data.

malha
Télécharger la présentation

Import Data From Excel File into Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Import Data From Excel File into Database

  2. Contents • Understanding Excel structure • Understanding jxl.jar library • Problem: Import student information from Excel File • Solution

  3. 1. Understand Excel structure • One Excel File data store in a workbook • Each workbook contents a lot of sheets • Each sheet contents a lot of rows • Each row contents a lot of cells • Cell contents the data

  4. 2. Understanding jxl.jar library • Workbook Object • Sheet Object • Cell Object Reference: http://jexcelapi.sourceforge.net/

  5. 3. Problem: Import student information from Excel File There is a list of students information in excel file. See the picture bellow:

  6. Write a program read all students information and then store it into your database. • Write a jsp file to view all students information stored in the database

  7. Sample Interface

  8. 4. Solution 4.1 Analysis and design database to store student information 4.2 Coding

  9. 4.1 Analysis and design database to store student information Each student object include student id, first name, last name, and gender. Create a database name “iwp” and then create student table to store student information include fields: • Student ID (Primary key) • Last Name • First Name • Gender

  10. 4.1 Coding • Step 1: Copy upload file bean and jxl.jar library to web server • Step 2: Write code

  11. Step 1: Copy upload file bean class and jxl.jar library • Copy com package to tomcat install_dir/webapps/ROOT/WEB-INF/class using for upload file from client to Server • Copy jxl.jar library to tomcatinstall_dir/webapps/ROOT/WEB-INF/lib using for read excel file

  12. Step 2: Write code • Developing importstudentinformation.jsp to upload excel file from client to server and then read student information from excel to store into database • Developing listofstudents.jsp to view all students in student table

  13. a. Upload excel file and read data – importstudentinformation.jsp

  14. b. Developing listofstudents.jsp

  15. References http://jexcelapi.sourceforge.net/ http://oms.saigontech.edu.vn

  16. Practice Exercise • Import the list of employees in excel file and create a jsp file to view all employees stored in database.

  17. Practice Exercise 1 We have a list of courses (Each course includes ID, Name, Credit). To prepares for student register class of each semester. AAS program director will decide list of courses will be offered in the next semester. Design database to store list of course will be offered for the each semester. Create a jsp file allow the program directors select courses will be offer.

  18. Requirement Select your group. Each group have maximum 4 members. • Two member design analysis and design database, and enter template data. • One person design interface • One person prepare slide to present • After finish design database 3 person will sit together and coding, testing.

More Related