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