目录
基于servlet、jsp、mysql的Javaweb导入导出数据格式为excel文件的项目
0.0项目结构:
0.1项目依赖【用的是maven】
0.2创建学生类:
0.3编写dao层 并在resources创建链接数据库的配置文件
db.properties:
工具类
1.创建数据库表
2.编写导入数据的 Servlet
3.编写导出数据的 Servlet
4.编写前端页面
5.运行结果:
5.1导入:
5.2 导出:
基于servlet、jsp、mysql的Javaweb导入导出数据格式为excel文件的项目
0.0项目结构:
0.1项目依赖【用的是maven】
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>ex_in</artifactId>
<packaging>war</packaging>
<version>1.0-SNAPSHOT</version>
<name>ex_in Maven Webapp</name>
<url>http://maven.apache.org</url>
<dependencies>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/javax.servlet.jsp/javax.servlet.jsp-api -->
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>javax.servlet.jsp-api</artifactId>
<version>2.3.3</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<!--mysql的驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
</dependencies>
<build>
<finalName>ex_in</finalName>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>7</source>
<target>7</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
0.2创建学生类:
package pojo;
/**
* 功能:
* 作者:captain_dong
* 日期:2023/12/10 20:46
*/
public class Student {
private int id;
private String name;
private int age;
private String sex;
private String address;
// 构造函数
public Student(int id, String name, int age, String sex, String address) {
this.id = id;
this.name = name;
this.age = age;
this.sex = sex;
this.address = address;
}
// Getter 和 Setter 方法
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
0.3编写dao层 并在resources创建链接数据库的配置文件
db.properties
db.properties:
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/(填自己的数据库名)?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
username=root
password=******(填自己的)
工具类
package dao;
import java.sql.*;
import java.util.Properties;
/**
* 连接数据库的操作,用户名,密码,使用jdbc连接
*/
public class DBUtil {
private static String driver;
private static String username;
private static String password;
public static String url;
private static Properties properties=new Properties();
public DBUtil() {
}
static {
try {
/**
* 读文件:db.properties
*/
properties.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties"));
driver=properties.getProperty("driver");
url=properties.getProperty("url");
username=properties.getProperty("username");
password=properties.getProperty("password");
Class.forName(driver);
}catch (Exception e){
throw new ExceptionInInitializerError(e);
}
}
/**
* 设计获得连接对象的方法:getConnectionDb()
* @return
*/
public static Connection getConnection(){
Connection connection=null;
try{
connection= DriverManager.getConnection(url,username,password);
}catch (SQLException e){
e.printStackTrace();
}
return connection;
}
/**
* 执行更新操作的方法
*
* @param sql SQL 更新语句
* @param params SQL 参数值
* @return 受影响的行数
*/
public static int executeUpdate(String sql, Object... params) {
Connection connection = null;
PreparedStatement preparedStatement = null;
int rowsAffected = 0;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
// 设置参数值
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
// 执行更新操作
rowsAffected = preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
free(null, preparedStatement, connection);
}
return rowsAffected;
}
/**
* 设计释放结果集、语句和连接的方法 free()
* @param resultSet
* @param preparedStatement
* @param connection
*/
public static void free(ResultSet resultSet, PreparedStatement preparedStatement,Connection connection){
if (resultSet!= null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(preparedStatement!=null){
try{
preparedStatement.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if (connection!=null){
try{
connection.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
}
package dao;
/**
* 功能:
* 作者:captain_dong
* 日期:2023/12/10 20:49
*/
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import pojo.Student;
public class StudentDAO {
/**
* 获取学生列表
* @param sql SQL 查询语句
* @return 学生列表
*/
public static List<Student> getStudentList(String sql) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Student> students = new ArrayList<>();
try {
// 获取数据库连接
conn = DBUtil.getConnection();
// 执行 SQL 查询语句
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
// 处理查询结果集
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String sex = rs.getString("sex");
String address = rs.getString("address");
Student student = new Student(id, name, age, sex, address);
students.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
DBUtil.free(rs, pstmt, conn);
}
return students;
}
}
1.创建数据库表
首先,我们需要创建一个用于存储 Excel 数据的数据库表。在该表中,我们可以为每个字段设置一个对应的列名,在导入和导出数据时使用这些列名来操作数据。
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` int(11) NOT NULL,
`sex` varchar(10) NOT NULL,
`address` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
);
2.编写导入数据的 Servlet
我们编写一个 Servlet 类来实现从 Excel 文件中读取数据并将其保存到数据库中。具体操作步骤如下:
package servlet;
import dao.DBUtil;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
@WebServlet("/ImportExcelServlet")
public class ImportExcelServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws IOException {
// 获取上传的 Excel 文件
String filePath = "D:/student.xls";
File file = new File(filePath);
// 读取 Excel 文件中的数据
Workbook workbook = null;
try {
workbook = Workbook.getWorkbook(file);
} catch (BiffException e) {
throw new RuntimeException(e);
}
Sheet sheet = workbook.getSheet(0);
int rows = sheet.getRows();
for (int i = 1; i < rows; i++) {
Cell[] cells = sheet.getRow(i);
String name = cells[0].getContents();
int age = Integer.parseInt(cells[1].getContents());
String sex = cells[2].getContents();
String address = cells[3].getContents();
// 将数据插入到数据库中
DBUtil.executeUpdate("INSERT INTO student(name,age,sex,address) VALUES(?,?,?,?)", name, age, sex, address);
}
workbook.close();
response.setContentType("text/html;charset=UTF-8");
response.getWriter().write("数据导入成功!");
}
}
在该类中,我们先获取上传的 Excel 文件。然后,使用 jxl 库来读取 Excel 文件中的数据,并将其插入到数据库中。
3.编写导出数据的 Servlet
我们编写一个 Servlet 类来实现从数据库读取数据并将其导出为 Excel 文件。具体操作步骤如下:
package servlet;
import dao.StudentDAO;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import pojo.Student;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.util.List;
@WebServlet("/ExportExcelServlet")
public class ExportExcelServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)throws IOException {
// 从数据库中读取数据
String sql = "SELECT * FROM student";
List<Student> students = StudentDAO.getStudentList(sql);
// 导出数据到 Excel 文件
String fileName = "D:/data.xls";
WritableWorkbook workbook = Workbook.createWorkbook(new File(fileName));
WritableSheet sheet = workbook.createSheet("sheet1", 0);
try {
sheet.addCell(new Label(0, 0, "姓名"));
} catch (WriteException e) {
throw new RuntimeException(e);
}
try {
sheet.addCell(new Label(1, 0, "年龄"));
} catch (WriteException e) {
throw new RuntimeException(e);
}
try {
sheet.addCell(new Label(2, 0, "性别"));
} catch (WriteException e) {
throw new RuntimeException(e);
}
try {
sheet.addCell(new Label(3, 0, "地址"));
} catch (WriteException e) {
throw new RuntimeException(e);
}
for (int i = 0; i < students.size(); i++) {
Student student = students.get(i);
try {
sheet.addCell(new Label(0, i + 1, student.getName()));
} catch (WriteException e) {
throw new RuntimeException(e);
}
try {
sheet.addCell(new Label(1, i + 1, String.valueOf(student.getAge())));
} catch (WriteException e) {
throw new RuntimeException(e);
}
try {
sheet.addCell(new Label(2, i + 1, student.getSex()));
} catch (WriteException e) {
throw new RuntimeException(e);
}
try {
sheet.addCell(new Label(3, i + 1, student.getAddress()));
} catch (WriteException e) {
throw new RuntimeException(e);
}
}
workbook.write();
try {
workbook.close();
} catch (WriteException e) {
throw new RuntimeException(e);
}
// 下载导出的 Excel 文件
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=data.xls");
// 显示数据导出成功的消息
response.sendRedirect(request.getContextPath() + "/exportSuccess.jsp");
}
}
在该类中,我们先从数据库中读取数据。然后,使用 jxl 库将数据导出到 Excel 文件中。最后,我们设置响应内容类型和头信息,并将 Excel 文件作为附件下载
4.编写前端页面
我们编写一个 JSP 页面来实现数据导入和导出操作。具体操作步骤如下:
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>导入导出 Excel 数据</title>
</head>
<body>
<h1>导入导出 Excel 数据</h1>
<form action="${pageContext.request.contextPath}/ImportExcelServlet" method="post" enctype="multipart/form-data">
<input type="file" name="file">
<br>
<input type="submit" value="导入数据">
</form>
<br>
<a href="${pageContext.request.contextPath}/ExportExcelServlet">导出数据</a>
</body>
</html>
导出成功:
<%--
Created by IntelliJ IDEA.
User: CaptainDong
Date: 2023/12/10
Time: 22:21
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Data Export Success</title>
</head>
<body>
<h1>数据导出成功!</h1>
<p>数据导出成功!</p>
</body>
</html>
- 在该页面中,我们使用表单上传 Excel 文件,并将其提交给 ImportExcelServlet 进行数据导入。同时,我们在页面上添加一个链接,点击该链接将执行 ExportExcelServlet 并将数据导出为 Excel 文件。
- 完成以上步骤后,我们就可以通过浏览器访问该项目,并进行 Excel 数据的导入导出操作。
5.运行结果:
5.1导入:
选择导入的文件:
点击导入:
结果:
5.2 导出:
点击导出:
结果:
数据库中的: