import data from excel file into database. contents 1.understanding excel structure 2.understanding...
TRANSCRIPT
![Page 1: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/1.jpg)
Import Data From Excel File into
Database
![Page 2: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/2.jpg)
Contents
1. Understanding Excel structure
2. Understanding jxl.jar library3. Problem: Import student
information from Excel File4. Solution
![Page 3: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/3.jpg)
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
![Page 4: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/4.jpg)
Workbook Object Sheet Object Cell ObjectReference:
http://jexcelapi.sourceforge.net/
2. Understanding jxl.jar library
![Page 5: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/5.jpg)
3. Problem: Import student information from Excel FileThere is a list of students information in
excel file. See the picture bellow:
![Page 6: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/6.jpg)
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
![Page 7: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/7.jpg)
Sample Interface
![Page 8: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/8.jpg)
![Page 9: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/9.jpg)
4. Solution
4.1 Analysis and design database to store student information
4.2 Coding
![Page 10: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/10.jpg)
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:
1. Student ID (Primary key)2. Last Name3. First Name4. Gender
![Page 11: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/11.jpg)
![Page 12: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/12.jpg)
Step 1: Copy upload file bean and jxl.jar library to web server
Step 2: Write code
4.1 Coding
![Page 13: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/13.jpg)
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 tomcat install_dir/webapps/ROOT/WEB-INF/lib using for read excel file
![Page 14: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/14.jpg)
a. Developing importstudentinformation.jsp to upload excel file from client to server and then read student information from excel to store into database
b. Developing listofstudents.jsp to view all students in student table
Step 2: Write code
![Page 15: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/15.jpg)
a. Upload excel file and read data – importstudentinformation.jsp
![Page 16: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/16.jpg)
![Page 17: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/17.jpg)
![Page 18: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/18.jpg)
![Page 19: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/19.jpg)
![Page 20: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/20.jpg)
![Page 21: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/21.jpg)
b. Developing listofstudents.jsp
![Page 22: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/22.jpg)
![Page 23: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/23.jpg)
![Page 24: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/24.jpg)
References
http://jexcelapi.sourceforge.net/
http://oms.saigontech.edu.vn
![Page 25: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/25.jpg)
Practice Exercise
Import the list of employees in excel file and create a jsp file to view all employees stored in database.
![Page 26: Import Data From Excel File into Database. Contents 1.Understanding Excel structure 2.Understanding jxl.jar library 3.Problem: Import student information](https://reader035.vdocuments.us/reader035/viewer/2022070408/56649e555503460f94b4cccf/html5/thumbnails/26.jpg)
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.