java uploads excel content to the mysql instance code

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!