java在excel表建测试数据,往excel表追加数据

1.依赖包

<dependencies>
        <!-- xls -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <!-- xlsx -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <!-- test -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

2.创建数据实体

/**
 * @program:CreateData
 * @description:
 * @author:ww
 * @create:2022-01-07 10:56
 */
public class User {
    private String name;
    private String phone;
    private String dept;
    public User(String name, String phone, String dept) {
        this.name = name;
        this.phone = phone;
        this.dept = dept;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getDept() {
        return dept;
    }

    public void setDept(String dept) {
        this.dept = dept;
    }

    @Override
    public String toString() {
        return "User{" +
                "name='" + name + '\'' +
                ", phone='" + phone + '\'' +
                ", dept='" + dept + '\'' +
                '}';
    }
}

3.将数据写入excel
注意excel一次最多写入数据32767行,超过会报错

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.poifs.filesystem.POIFSFileSystem;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Random;

/**
 * @program:CreateData
 * @description:创建数据到excel表
 * @author:ww
 * @create:2022-01-07 10:26
 */
public class Create {
    public static void main(String[] args) throws IOException {
        String url = "E:/蓝凌专属钉体验企业-通讯录模板.xls";
        FileInputStream fs;
        fs = new FileInputStream(url);
        //使用poi提供的方法得到excel信息
        POIFSFileSystem ps = new POIFSFileSystem(fs);
        //1.创建workbook
        HSSFWorkbook wb = new HSSFWorkbook(ps);
        //2.选中sheet
        HSSFSheet sheet = wb.getSheetAt(0);
        //分别得到最后一行的行号,和一条记录的最后一个单元格
        FileOutputStream out = new FileOutputStream(url);//写入数据
        
        for (int i = 0; i < 5000; i++) {
            User user = getusers(i);
            //让行号从最后一行后面开始
            int hang = sheet.getLastRowNum() + 1;
            System.out.println(hang);
            HSSFRow row;
            //向现有行号后追加数据
            row = sheet.createRow((short) hang);
            //设置列,从0开始
            row.createCell(1).setCellValue(user.getName());
            row.createCell(2).setCellValue(user.getPhone());
            row.createCell(3).setCellValue(user.getDept());
        }
        out.flush();
        wb.write(out);
        out.close();
    }
//随机生成用户方法
    public static User getusers(int i) {
        System.out.println("---------------------createUser");
        String[] depts = {"部门219", "部门922", "部门293", "部门294", "部门295"
                , "部门926", "部门932", "部门943", "部门591", "部门6191"
                , "部门791", "部门891", "部门991", "部门1019", "部门1191"
                , "部门1291", "部门1319", "部门1941", "部门1591", "部门1691"};
        String[] phonesstart = {"13", "15", "18","16","17"};
        Random random = new Random();
        String ph = phonesstart[random.nextInt(5)] + (random.nextInt(900) + 100) + (random.nextInt(90) + 10) + (random.nextInt(9000) + 1000);
        System.out.println("ph--------  " + ph + " " + ph.length());
        User user = new User("何雪顾" + i, ph, depts[random.nextInt(20)]);
        return user;
    }
}