JAVA随机生成一些Excel表的字段，读取Excel表中的字段，上传到Mysql数据库中

首先Maven导包

前两个是读取Excel的jar包，第三个是插入数据的jar包，最后一个是mysql数据库连接包，数据是随便生成的，只是测试而已。

   <dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>            
  <version>3.17</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.15</version>
</dependency>
<dependency>
  <groupId>net.sourceforge.jexcelapi</groupId>
  <artifactId>jxl</artifactId>
  <version>2.6.10</version>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.8</version>
</dependency>

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
 
 
public class ExcelDemo{
 
 
        /*
           判断是否是excel文件
        */
        public static Workbook getExcel(String path) throws Exception{
            Workbook workBook = null;
            File file = new File(path);
            if(!file.exists()){
                System.out.print("this file isn't exists");
                workBook = null;
            }else{
                String fileType = path.substring(path.lastIndexOf(".")); //得到文件后缀
                InputStream is = new FileInputStream(path);
                if(".xls".equals(fileType)){
                    workBook = new HSSFWorkbook(is);
                }else if(".xlsx".equals(fileType))
                    workBook = new XSSFWorkbook(is);
                else
                    System.out.print("format error");
            }
            return workBook;
        }
 
 
 
        /*
         * 往Excel中插入数据
         */
        public static void writeExcel(int cloumnCount,String filePath) throws Exception{
            OutputStream out = null;
            try {
                // 获取总列数
                int columnNumCount = cloumnCount;
                // 读取Excel文档
                Workbook workBook = getExcel(filePath);
                // sheet 对应一个工作页
                Sheet sheet = workBook.getSheetAt(0);
                /**
                 * 删除原有数据，除了属性列
                 */
                int rowNumber = sheet.getLastRowNum();    // 第一行从0开始算
                System.out.println("remove row：" + rowNumber);
                for (int i = 1; i <= rowNumber; i++) {
                    Row row = sheet.getRow(i);
                    sheet.removeRow(row);
                }
                // 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
                out =  new FileOutputStream(filePath);
                workBook.write(out);
                /**
                 * 往Excel中写新数据
                 */
                for (int j = 0; j < 100000; j++) {
                    // 创建一行：从第二行开始，跳过属性列
                    Row row = sheet.createRow(j+1);
                    // 得到要插入的每一条记录
                    for (int k = 0; k <= columnNumCount; k++) {
                        // 在一行内循环
                        Cell first = row.createCell(0);
                        first.setCellValue(j+1);
                        Cell second = row.createCell(1);
                        second.setCellValue(j+3);
                        Cell third = row.createCell(2);
                        third.setCellValue(j*2);
                    }
                }
                // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
                out =  new FileOutputStream(filePath);
                workBook.write(out);
            } catch (Exception e) {
                e.printStackTrace();
            } finally{
                try {
                    if(out != null){
                        out.flush();
                        out.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            System.out.println("export success");
        }
 
        /*
         *  把数据插入数据库中
         */
        public static void insertToDatabase(Workbook workBook) throws Exception{
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/ffsdb?charaterEncoding=UTF-8&"
                    +"useServerPrepStmts=true&rewriteBatchedStatements=true";
            String username = "root";
            String password = "123456";
            Connection conn = DriverManager.getConnection(url,username,password);
            conn.setAutoCommit(false);//关闭自动提交
            String sql = "INSERT INTO usr(uid,userName,uphone) VALUES(?,CONCAT('姓名',?),?);";
            PreparedStatement psmt = conn.prepareStatement(sql);
 
 
            Long start = System.currentTimeMillis();
            Sheet sheet=workBook.getSheetAt(0);//读取sheet(从0计数)
            int rowNum=sheet.getLastRowNum();//读取行数(从0计数)
            for(int i=1;i<=rowNum;i++){
                Row row = sheet.getRow(i); //获得i行
                psmt.setString(1, (row.getCell(0).toString()));
                psmt.setString(2, (row.getCell(1).toString()));
                psmt.setString(3, (row.getCell(2).toString()));
                psmt.executeUpdate();
            }
            conn.commit();          //一起提交
            psmt.close();
            conn.close();
            Long end = System.currentTimeMillis();
            System.out.println("ok,time is"+(end-start));
        }
 
        public static void main(String[] args) throws Exception {
            String filePath = "D:\\exceltest.xlsx";
            Workbook wb = ExcelDemo.getExcel(filePath);
            ExcelDemo.writeExcel(3,filePath);
            ExcelDemo.insertToDatabase(wb);
 
        }
 
    }

运行截图