Java imports data from excel into mysql

Java imports data from excel into mysql

Sometimes in our actual work, we need to import data from Excel into the database. If the amount of data is hundreds or even more, I believe that a little bit of ctrlc and ctrlv is not a solution. Here we take the MySQL database as an example to store the data in Excel into the database.

My idea is: first take out the data from Excel, and then transfer the data to the database. To operate Excel, you need jxl.jar, and to operate the database, you can use the most basic jdbc, which requires the jar package mysql-connector-java-5.0.8-bin.jar.

Let's take a look at the Excel screenshot:

Let’s look at the final rendering:

The entire code is posted below:

package browser; 
 
import java.io.File; 
import java.sql.*; 
import jxl.Cell; 
import jxl.Sheet; 
import jxl.Workbook; 
 
public class PushExcelToMysql { 
 public static void main(String[] args) throws Exception { 
 
 Sheet sheet; 
 Workbook workbook; 
 Cell [][] cells=new Cell[3][3]; 
 try{ 
 workbook = Workbook.getWorkbook(new File("E:\\lalala.xls")); 
 sheet = workbook.getSheet(0); 
 for(int i=0;i<3;i++){ 
 for(int j=0;j<3;j++){ 
  cells[i][j]=sheet.getCell(j,i); 
 } 
 } 
 }catch (Exception e) { 
 e.printStackTrace(); 
 } 
 
 try{ 
 Class.forName("com.mysql.jdbc.Driver"); 
 }catch(Exception e){ 
 e.printStackTrace(); 
 } 
 Connection c=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8", "root","root"); 
 String sql="insert into tosql(name,age,sex) values(?,?,?)"; 
 PreparedStatement ps=c.prepareStatement(sql); 
 for(int i=0;i<3;i++){ 
 ps.setString(1, cells[i][0].getContents()); 
 ps.setString(2, cells[i][1].getContents()); 
 ps.setString(3, cells[i][2].getContents()); 
 ps.execute(); 
 } 
 System.out.println("ok"); 
 } 
}

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:
  • How to add data to an existing Excel table in Java
  • Batch import method of excel table data in Java
  • Java implements the method of batch importing excel table data into the database
  • Java uses POI to parse Excel and get all cell data examples
  • How to import Excel data using Idea or Datagrip

<<:  WeChat applet silent login and maintenance of custom login state detailed explanation

>>:  A brief introduction to Tomcat's overall structure

Recommend

Vue.js implements the nine-grid image display module

I used Vue.js to make a nine-grid image display m...

The difference between ${param} and #{param} in MySQL

The parameter passed by ${param} will be treated ...

HTML form application includes the use of check boxes and radio buttons

Including the use of check boxes and radio buttons...

Illustration of the process of using FileZilla to connect to the FTP server

When I first started setting up an ftp server on ...

Detailed explanation of Nginx static file service configuration and optimization

Root directory and index file The root directive ...

Vue state management: using Pinia instead of Vuex

Table of contents 1. What is Pinia? 2. Pinia is e...

How to monitor multiple JVM processes in Zabbix

1. Scenario description: Our environment uses mic...

MySQL 8.0.15 version installation tutorial connect to Navicat.list

The pitfalls 1. Many tutorials on the Internet wr...

Vue2 cube-ui time selector detailed explanation

Table of contents Preface 1. Demand and Effect ne...

MySQL merges multiple rows of data based on the group_concat() function

A very useful function group_concat(), the manual...

Detailed tutorial on installing mysql under Linux

1. Shut down the mysql service # service mysqld s...

Detailed steps to install the NERDTree plugin in Vim on Ubuntu

NERDTree is a file system browser for Vim. With t...

Linux uses dual network card bond and screwdriver interface

What is bond NIC bond is a technology that is com...