JAVA 根据excel内容自动创建数据库表

最新推荐文章于 2024-01-24 15:41:13 发布

置顶 @橙子先生

最新推荐文章于 2024-01-24 15:41:13 发布

阅读量2k

点赞数

文章标签： java 数据库开发语言

橙子先生原文

本文链接：https://blog.csdn.net/nibaba124/article/details/103136354

版权

前言

首先用到 java读取excel文件和 sql拼接

一：引入maven 依赖

       <!-- 引入poi，解析workbook视图 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>
          <!--如果引用的3.14版本poi-ooxml 报错，请使用3.15版本
           <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>-->
        </dependency>
        <!-- 处理excel和上面功能是一样的-->

二：整合Poi和msyql 语句

package com.example.demo;


import org.apache.poi.ss.usermodel.*;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

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




@RunWith(SpringRunner.class)
@SpringBootTest
public class XulUtil {

     

    /** 获取文件夹路径*/
    public static void main(String[] args) {
        readExcel("E:\\XXXXX.xls");
    }

    /**执行获取文件列和行*/
    public static void readExcel(String path) {
        File file = new File(path);
        FileInputStream fis = null;
        Workbook workBook = null;
        Statement statement=null;
        PreparedStatement pStemt = null;
        if (file.exists()) {
            try {
                /**获取msyql连接类型*/
                Connection conn =sql();
                if (!conn.isClosed()){statement = conn.createStatement();}
                workBook = WorkbookFactory.create(new FileInputStream(new File(path)));
                int numberOfSheets = workBook.getNumberOfSheets();
                // sheet工作表
                for (int s = 0; s < numberOfSheets; s++) {
                    Sheet sheetAt = workBook.getSheetAt(s);
                    //获取工作表名称
                    String sheetName = sheetAt.getSheetName();
                    //获取当前Sheet的总行数
                    int rowsOfSheet = sheetAt.getPhysicalNumberOfRows();
                    System.out.println("当前表格的总行数:" + rowsOfSheet);
                    for (int r = 1; r < rowsOfSheet; r++) {
                        Row row = sheetAt.getRow(r);
                        if (row == null) {
                            continue;
                        } else {
                            int rowNum = row.getRowNum() + 1;
                            System.out.println("当前行:" + rowNum);
                            // 总列(格)
                            Cell cell0 = row.getCell(0);
                            Cell cell1 = row.getCell(1);
                            Cell cell2 = row.getCell(2);
                            String sqlAdd="create table "+sheetName+"("+cell1+" "+" "+cell2+" NOT NULL COMMENT '"+cell0+"',PRIMARY KEY ("+cell1+"))";
                            String sqlUpdate="alter table "+sheetName+" add "+cell1+" "+" "+cell2+" COMMENT '"+cell0+"'";
                            ResultSet rs = conn.getMetaData().getTables(null, null, sheetName, null);
                           if (rs.next()) {
                               if(!"id".equals(cell1.toString())){
                                   statement.execute(sqlUpdate);
                                 }else {
                                   statement.execute(sqlAdd);
                               }
                           }
                        }
                    }
                }
              } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            System.out.println("文件不存在!");
        }
    }


   /**获取数据库连接*/
        public  static  Connection  sql() {
            String driver = "com.mysql.cj.jdbc.Driver";
            String url = "jdbc:mysql://localhost:3306/engineer?useUnicode=yes&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=UTC&useSSL=false";
            String user = "root";
            String password = "root";
            Connection conn=null;
            try {
                Class.forName(driver);
                conn = DriverManager.getConnection(url, user, password);
                return conn;
            } catch (Exception e) {
                e.printStackTrace();
            }
            return conn;
        }



}

三：这里可以处理下，写的比较粗糙, 还有流的关闭也没操作。。。。。。。