java uploads excel content to the mysql instance code
- 2020-12-16 05:58:15
- OfStack
mysql table column name num1 num2, num3, num4, num5, num6 Excle table name
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);
// To obtain Excel Path to document
//.xlsx The file with XSSFWorkbook .xlx with HSSFWorkbook
public static String filePath = "D://demoExcel.xlsx";
public static void main(String[] args) {
try {
// To create the Excel Reference to a workbook file
XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(filePath));
// in Excel In the document, the 1 The default index of a worksheet is 0
// Its statement is: HSSFSheet sheet = workbook.getSheetAt(0);
XSSFSheet sheet = wookbook.getSheet("Sheet1");
// Access to the Excel The number of lines in the file
int rows = sheet.getPhysicalNumberOfRows();
// Traverse line
for (int i = 0; i < rows; i++) {
// Read the cell at the top left
XSSFRow row = sheet.getRow(i);
// Line is not empty
if (row != null) {
// Access to the Excel All the columns in the file
int cells = row.getPhysicalNumberOfCells();
String value = "";
// Traverse the list
for (int j = 0; j < cells; j++) {
// Gets 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 In the 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 the success ");
} else{
System.out.println(" Insert the failure ");
}
}
}
}
catch (FileNotFoundException e) {
e.printStackTrace();
}
catch (IOException e) {
e.printStackTrace();
}
}
}
TestEntity is the entity class that holds 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 the 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 Excle(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 block
e.printStackTrace();
}
finally {
try {
pstmt.close();
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBconnection.closeConnection();
}
return count;
}
}
conclusion
That's the end of this article on java uploading excel content to the mysql instance code, and I hope you found it helpful. Interested friends can continue to refer to other related topics in this site, if there is any deficiency, welcome to comment out. Thank you for your support!