java把excel内容上传到mysql实例代码

javascript/59194.html">javascript/31572.html">java;">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 {	//记录类的输出信息­	static Log log = LogFactory.getLog(TestExcel.class);	//获取Excel文档的路径­	//.xlsx文件用XSSFWorkbook .xlx 用HSSFWorkbook 	public static String filePath = "D://demoExcel.xlsx";	public static void main(String[] args) {		try {			// 创建对Excel工作簿文件的引用­			XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(filePath));			// 在Excel文档中，第一张工作表的缺省索引是0			// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);­			XSSFSheet sheet = wookbook.getSheet("Sheet1");			//获取到Excel文件中的所有行数­			int rows = sheet.getPhysicalNumberOfRows();			//遍历行			for (int i = 0; i < rows; i++) {				// 读取左上端单元格				XSSFRow row = sheet.getRow(i);				// 行不为空­				if (row != null) {					//获取到Excel文件中的所有的列­					int cells = row.getPhysicalNumberOfCells();					String value = "";					//遍历列­					for (int j = 0; j < cells; j++) {						//获取到列的值­						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;							}						}					}					// 将数据插入到mysql数据库中­					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("插入成功");					} else{						System.out.println("插入失败");					}				}			}		}		catch (FileNotFoundException e) {			e.printStackTrace();		}		catch (IOException e) {			e.printStackTrace();		}	}}

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;	}}

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;	}}