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