mysql table column name num1, num2, num3, num4, num5, num6 table name Excel
Upload method
package com.web.connection;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class TestExcel {//Record the output information of the class static Log log = LogFactory.getLog(TestExcel.class);//Get the path to the Excel document//.xlsx file with XSSFWorkbook .xlx Use HSSFWorkbook public static String filePath = "D://demoExcel.xlsx";public static void main(String[] args) {try {// Create a reference to Excel workbook file XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(filePath));// In Excel document, the default index of the first worksheet is 0// Its statement is: HSSFSheet sheet = workbook.getSheetAt(0);XSSFSheet sheet = wookbook.getSheet("Sheet1");//Get all rows in the Excel file int rows = sheet.getPhysicalNumberOfRows();//Transweep the row for (int i = 0; i < rows; i++) {//Read the upper left cell XSSFRow row = sheet.getRow(i);//The row is not empty if (row != null) {//Get all columns in the Excel file int cells = row.getPhysicalNumberOfCells();String value = "";//Tranquility column for (int j = 0; j < cells; j++) {//Get the value of the column XSSFCell cell = row.getCell(j);if (cell != null) {switch (cell.getCellType()) {case HSSFCell.CELL_TYPE_FORMULA:break;case HSSFCell.CELL_TYPE_NUMERIC:value += cell.getNumericCellValue() + ",";break;case HSSFCell.CELL_TYPE_STRING:value += cell.getStringCellValue() + ",";break;default:value += "0";break;}}}// Insert data into mysql database String[] val = value.split(",");TestEntity entity = new TestEntity();entity.setNum1(val[0]);entity.setNum2(val[1]);entity.setNum3(val[2]);entity.setNum4(val[3]);entity.setNum5(val[4]);entity.setNum6(val[5]);TestMethod method = new TestMethod();int a=method.add(entity);if(a>0){System.out.println("Insert successful");} else{System.out.println("Insert failed");}}}}} catch (FileNotFoundException e) {e.printStackTrace();}catch (IOException e) {e.printStackTrace();}}}Where TestEntity is an entity class that stores the data queried from the excel table.
package com.web.connection;public class TestEntity {private String num1;private String num2;private String num3;private String num4;private String num5;private String num6;public TestEntity(){}public String getNum1() {return num1;}public void setNum1(String num1) {this.num1 = num1;}public String getNum2() {return num2;}public void setNum2(String num2) {this.num2 = num2;}public String getNum3() {return num3;}public void setNum3(String num3) {this.num3 = num3;}public String getNum4() {return num4;}public void setNum4(String num4) {this.num4 = num4;}public String getNum5() {return num5;}public void setNum5(String num5) {this.num5 = num5;}public String getNum6() {return num6;}public void setNum6(String num6) {this.num6 = num6;}}TestMethod is a SQL statement that inserts data into the mysql table
package com.web.connection;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;public class TestMethod {public int add(TestEntity te){Connection con = DBconnection.getConnection();PreparedStatement pstmt = null;int count = 0;String sql = "insert into Excel(num1,num2,num3,num4,num5,num6) values(?,?,?,?,?)";try {pstmt = con.prepareStatement(sql);pstmt.setString(1, te.getNum1());pstmt.setString(2, te.getNum2());pstmt.setString(3, te.getNum3());pstmt.setString(4, te.getNum4());pstmt.setString(5, te.getNum5());pstmt.setString(6, te.getNum6());count = pstmt.executeUpdate();/** if(count==0){ throw new DataAlreadyExistException(); }*/}catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {try {pstmt.close();}catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}DBconnection.closeConnection();}return count;}}Summarize
The above is the entire content of this article about uploading excel content to mysql instance code by Java. I hope it will be helpful to everyone. Interested friends can continue to refer to other related topics on this site. If there are any shortcomings, please leave a message to point it out. Thank you friends for your support for this site!