jsp上传excel到mysql_通过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;

}

}