读取excel写入数据库 WorkbookFactory 【vaynexiao】

准备xlsx文件，数据如图

pom

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>

java

import org.apache.poi.ss.usermodel.*;
import java.io.File;

public class Test {
    public static void main(String[] args) throws Exception{
        Workbook workbook = WorkbookFactory.create(new File("C:\\Environment\\user.xlsx"));
        //获取一张表
        Sheet sheet = workbook.getSheetAt(1);
        for (int i=1; i<=sheet.getLastRowNum(); i++) {//跳过第一行，取得其他行数据
            Row row=sheet.getRow(i);//取得第i行数据
            for (int j=0; j<row.getLastCellNum(); j++) {
                Cell cell=row.getCell(j);//取得第j列数据
                cell.setCellType(CellType.STRING);
                String value = cell.getStringCellValue();
                System.out.print(value);
                System.out.print("===");
            }
            System.out.println();
        }
//        11===张三===0002===信息部===男===
//        12===李四===0003===管理部===男===
//        13===王五===0004===财务部===男===
//        14===李磊===0005===行政部===男===
//        15===王大拿===0006===人事部===女===
//        16===谢大脚===0007===技术部===女===
    }
}

读取并写入数据库

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.IOException;

public class Test01 {
    public static void main(String[] args) throws IOException, InvalidFormatException {
        Workbook workbook = WorkbookFactory.create(new File("D:\\company_info.xls"));
        System.out.println("sheets" + workbook.getNumberOfSheets());
        //获取一张表
        Sheet sheet = workbook.getSheetAt(0);
        for (int i=1;i<=sheet.getLastRowNum();i++) {//跳过第一行
            Row row=sheet.getRow(i);//取得第i行数据
            CUserDto userDto=new CUserDto();
            String []str=new String[row.getLastCellNum()];
            for (int j=0;j<row.getLastCellNum();j++) {
                Cell cell=row.getCell(j);//取得第j列数据
                cell.setCellType(CellType.STRING);
                str[j]=cell.getStringCellValue().trim();
                System.out.print(str[j]+" ");
            }
            //System.out.println();
            //封装对象信息
            userDto.setRoleId(2);
            userDto.setUsername(str[0]);
            userDto.setPassword(str[1]);
            userDto.setCompany_name(str[2]);
            userDto.setCompany_code(str[3]);
            userDto.setRegion_code(Integer.parseInt(str[4]));
            userDto.setFirst_cp_code(Integer.parseInt(str[5]));
            userDto.setSecond_cp_code(Integer.parseInt(str[6]));
            userDto.setFirst_industry_code(Integer.parseInt(str[7]));
            userDto.setContact_name(str[8]);
            userDto.setContact_phone(str[9]);
            userDto.setContact_address(str[10]);
            list.add(userDto); //加入到集合中
        }
    }
}

相关****
https://www.bilibili.com/video/BV1Hb411b7G4