读取Excel文件并用jdbc连接将内容导入MySQL数据库

仅支持修改 1 次

于 2021-11-29 08:59:55 发布

阅读量1k

点赞数 2

分类专栏： java 文章标签： java 开发语言后端

本文链接：https://blog.csdn.net/ffff_csdn/article/details/121601934

版权

java 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

Excel文件

依赖

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.15</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.15</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.25</version>
    </dependency>

代码

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.IOException;
import java.sql.*;

public class ReadToSql {
    public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException, InvalidFormatException {
        // Excel 路径         
        String path = "F:\\a\\b\\c.xlsx";
        File file = new File(path);
        // 获取数据库连接
        String URL = "jdbc:mysql://localhost:3306/db15?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
        String USER = "root";
        String PASSWORD = "123456";
        //1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2.获得数据库链接
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        //3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
        String sql = "insert into sheet(id,KM_ID,attachment_name,file_name,file_size,admin) values(null,?,?,?,?,?)";
        PreparedStatement pst = conn.prepareStatement(sql);
        // 获取工作簿
        Workbook workbook = WorkbookFactory.create(file);
        // 获取当前表
        Sheet sheet = workbook.getSheetAt(0);
        // 获取总行数
        int totalRows = sheet.getPhysicalNumberOfRows();
        // 获取表头的总列数
        int totalCols = sheet.getRow(0).getPhysicalNumberOfCells();
        // 遍历行
        for (int i = 1; i < totalRows; i++) {
            // 遍历列
            for (int j = 0; j < totalCols; j++) {
                // 获取 i 行 j 列
                Cell cell = sheet.getRow(i).getCell(j);
//             判断该列是否为 null
                if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                    pst.setString(j + 1, null);
                    continue;
                }

                if (j == totalCols - 1) {
                    pst.setString(j + 1, cell.getStringCellValue());
                } else {
                    // 判断是否是字符类型
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        pst.setString(j + 1, cell.getStringCellValue());
                    } else {
                        // 单元格为数值类型
                        cell.setCellType(CellType.NUMERIC);
                        pst.setInt(j + 1, (int) cell.getNumericCellValue());
                    }
                }
            }
            // 执行 SQL
            pst.executeUpdate();
        }
        pst.close();
        conn.close();
    }
}