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实例代码的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站其他相关专题，如有不足之处，欢迎留言指出。感谢朋友们对本站的支持！

原文链接：http://www.cnblogs.com/leirenyuan/p/6008143.html