基于servlet、jsp、mysql的Javaweb导入导出数据格式为excel文件的项目

水深无声 2024-03-16 13:23 23阅读 0赞

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项目结构： ###

![07dfdf7c46254300acc78aa813743456.png][]

### 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>
        
        <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>
    
        
        <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导入： ####

选择导入的文件：

![9783359b1b164b508d60781d25387943.png][]

点击导入：

![41ae4ee753504a0abee8791cfc38ad5c.png][]

结果：

![41033f5f1539419bb6bbb353d5b11602.png][]

![74729223d3994ff68abd2617a9aa9c6a.png][]

#### 5.2 导出： ####

点击导出：

![728a6c60739446c1b885adc9315a39fe.png][]

结果：

![e0a0542e44b0407a8f923e9dbc717a4d.png][]

![dc440c30f59e4dde9157b2ea851cdc46.png][]

![44857fcf818f48fcac04c1e282714375.png][] ![9a1743c70d214176b7ded2b5f43634fa.png][]

数据库中的：

![f1f8b3d0d6074b6a9cadf64515289ae1.png][]

[07dfdf7c46254300acc78aa813743456.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/4ced553295134a48b0136ebb8259afd0.png
[9783359b1b164b508d60781d25387943.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/2dfb4e59557d4658afd257f1b6aee5c0.png
[41ae4ee753504a0abee8791cfc38ad5c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/0dbf4a06efa940ae93501ae34a51c997.png
[41033f5f1539419bb6bbb353d5b11602.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/6d9187e1f63a4b16b31c275512ba2299.png
[74729223d3994ff68abd2617a9aa9c6a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/33c773feb8b9437d964ec7bc8897fd4d.png
[728a6c60739446c1b885adc9315a39fe.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/45268b7aed59483e8f8fcc57397aab5f.png
[e0a0542e44b0407a8f923e9dbc717a4d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/8798978835e245789db1f3abceb21b9b.png
[dc440c30f59e4dde9157b2ea851cdc46.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/4ac423f58e59483284c3dfc5aa6ed7d8.png
[44857fcf818f48fcac04c1e282714375.png]: https://img-blog.csdnimg.cn/direct/44857fcf818f48fcac04c1e282714375.png
[9a1743c70d214176b7ded2b5f43634fa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/090fa9f756e4450babab2e4ec082a2fd.png
[f1f8b3d0d6074b6a9cadf64515289ae1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/e26d5d0a8b9143d8be21168ad90928e3.png