读取excel到数据库

lz使用poi的jar,maven依赖见lz的 poi读取数据写入excel:

http://blog.csdn.net/imthemostshuaiin626/article/details/75103353

主要解决了长数字在excel中为科学记数法转化为原数据的问题(具体为手机号码)

@SuppressWarnings("resource")
    public static void main(String[] args) {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring_bean.xml");
        JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
        String path="X:\\Users\\xb\\Desktop\\excel\\xx号段.xlsx";
        int i = 1;//从第几行开始
        excel2sqlDate(path, jdbcTemplate,i);


    }

@SuppressWarnings("deprecation")
    private static void excel2sqlDate(String path,JdbcTemplate jdbcTemplate,int r){
        DecimalFormat df = new DecimalFormat("0");  
        List<User> users = new ArrayList<>();
        try {
            // 构造 XSSFWorkbook 对象，strPath 传入文件路径
            XSSFWorkbook xwb = new XSSFWorkbook(path);
            // 读取第一章表格内容
            XSSFSheet sheet = xwb.getSheetAt(0);
            // 定义 row、cell
            XSSFRow row;
            //XSSFCell cell;

            // 循环输出表格中的内容
            for (int i = sheet.getFirstRowNum()+r; i < sheet.getPhysicalNumberOfRows(); i++)
            {
                row = sheet.getRow(i);
                for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++)
                {
                    // 通过 row.getCell(j).toString() 获取单元格内容，
                    if (j==0) {
                        if (row.getCell(0)!=null) {
                            row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                        }
                        if (row.getCell(2) != null) {
                            row.getCell(2).setCellType(Cell.CELL_TYPE_NUMERIC);//科学记数法的转化
                        }
                        if (row.getCell(3) != null) {
                            row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                        }
                        User user = new User();
                        user.setId(row.getCell(0).toString());
                        user.setName(row.getCell(1).toString());
                        user.setPhoneNum(df.format(row.getCell(2).getNumericCellValue()));
                        user.setCount(Integer.parseInt(row.getCell(3).toString()));
                        users.add(user);
                        System.out.println(row.getCell(0)+"-"+row.getCell(1)+"-"+df.format(row.getCell(2).getNumericCellValue())+"-"+row.getCell(3));
                    }
                }
            }

        } catch (IOException e) {
            e.printStackTrace();

        }
        String sql="insert into xxx(name,phone,count) values(?,?,?) ";
        for(User user :users){
            jdbcTemplate.update(sql,user.getName(),user.getPhoneNum(),user.getCount());

        }
    }