代码
@Test
void bigData() throws FileNotFoundException {
FileOutputStream fileOutputStream = new FileOutputStream("E:\\tmp\\user.xlsx");
ArrayList<User> users = new ArrayList<>();
for (int i = 0; i < 100 * 30000; i++) {
User user = new User();
user.setCjsj(new Date().toString());
user.setId(i);
user.setLxfs(i + "");
user.setYxds("广州");
user.setTzqy("广东");
users.add(user);
}
ArrayList<List<User>> objects = new ArrayList<>();
int size = 100 * 200;
int sheetNum = users.size() / size + (users.size() % size > 0 ? 1 : 0);
for (int i = 0; i < sheetNum; i++) {
objects.add(users.subList(i * size, (i + 1) * size <= users.size() ? (i + 1) * size : users.size()));
}
ExcelWriter build = EasyExcel.write(fileOutputStream, User.class)
.build();
for (int i = 0; i < objects.size(); i++) {
build.write(objects.get(i), EasyExcel.writerSheet("表格" + i).build());
}
build.finish();
}
从数据库实现
@Test
void sqlexport() throws FileNotFoundException {
FileOutputStream fileOutputStream = new FileOutputStream("E:\\tmp\\user.xlsx");
ExcelWriter build = EasyExcel.write(fileOutputStream, User.class)
.build();
Integer number = fileService.querySum();
int size = 100 * 1000;
int sheetNum = number / size + (number % size > 0 ? 1 : 0);
for (int i = 0; i < sheetNum; i++) {
List<User> users = fileService.queryUserByPage(i * size, size);
build.write(users, EasyExcel.writerSheet("表格" + i).build());
}
build.finish();
}
dao层
public Integer querySum() {
String sql = "select count(*) from user";
Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
return integer > 0 ? integer : 0;
}
public List<User> queryUserByPage(Integer start, Integer size) {
String sql = String.format("select * from user limit %s,%s", start, size);
List<User> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
return users;
}