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

mysql 表列名 num1,num2,num3,num4,num5,num6 表名excle

上传的方法

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

其中 testentity 为用存放从excel表中查询到的数据的实体类

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 为往mysql表中插入数据 的sql语句

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

总结

以上就是本文关于java把excel内容上传到mysql实例代码的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站其他相关专题，如有不足之处，欢迎留言指出。感谢朋友们对本站的支持！