easyexcel
/**
* 针对较少的记录数(20W以内大概)可以调用该方法一次性查出然后写入到EXCEL的一个SHEET中
* 注意： 一次性查询出来的记录数量不宜过大，不会内存溢出即可。
* @throws IOException
*/
public static void writeExcelOneSheetOnceWrite() throws IOException {
FileSystemView fsv = FileSystemView.getFileSystemView();
File com=fsv.getHomeDirectory(); //这便是读取桌面路径的方法了
String path = com.getPath();
// 生成EXCEL并指定输出路径
OutputStream out = new FileOutputStream(path+"\\withoutHead1.xlsx");
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
// 设置SHEET
Sheet sheet = new SXSSFSheet(1, 0);
sheet.get("sheet1");
// 设置标题
Table table = new XSSFTable(1);
table.getSheetName()
List<List<String>> titles = new ArrayList<List<String>>();
titles.add(Arrays.asList("用户ID"));
titles.add(Arrays.asList("名称"));
titles.add(Arrays.asList("年龄"));
titles.add(Arrays.asList("生日"));
titles.add(Arrays.asList("生日0"));
titles.add(Arrays.asList("生日1"));
titles.add(Arrays.asList("生日2"));
titles.add(Arrays.asList("生日3"));
titles.add(Arrays.asList("生日4"));
titles.add(Arrays.asList("生日5"));
titles.add(Arrays.asList("生日6"));
titles.add(Arrays.asList("生日7"));
titles.add(Arrays.asList("生日8"));
titles.add(Arrays.asList("生日9"));
titles.add(Arrays.asList("生日10"));
titles.add(Arrays.asList("生日11"));
titles.add(Arrays.asList("生日12"));
titles.add(Arrays.asList("生日13"));
titles.add(Arrays.asList("生日14"));
titles.add(Arrays.asList("生日15"));
titles.add(Arrays.asList("生日16"));
titles.add(Arrays.asList("生日17"));
table.s(titles);
// 查询数据导出即可 比如说一次性总共查询出100条数据
List<List<String>> userList = new ArrayList<>();
for (int i = 1; i < 200000; i++) {
userList.add(Arrays.asList(
"ID_" + i, "小明" + i,
String.valueOf(i),
new Date().toString(), new Date().toString(), new Date().toString(),
new Date().toString(), new Date().toString(), new Date().toString(),
new Date().toString(), new Date().toString(), new Date().toString(),
new Date().toString(), new Date().toString(), new Date().toString(),
new Date().toString(), new Date().toString(), new Date().toString(),
new Date().toString(), new Date().toString(), new Date().toString(),
new Date().toString())
);
}
long l = System.currentTimeMillis();
writer.write0(userList, sheet, table);
writer.finish();
long l0 = System.currentTimeMillis();
long ll=l0-l;
System.out.println(ll);
}
/**
* 针对105W以内的记录数可以调用该方法分多批次查出然后写入到EXCEL的一个SHEET中
* 注意：
* 每次查询出来的记录数量不宜过大，根据内存大小设置合理的每次查询记录数，不会内存溢出即可。
* 数据量不能超过一个SHEET存储的最大数据量105W
*
* @throws IOException
*/
public static void writeExcelOneSheetMoreWrite() throws IOException {
FileSystemView fsv = FileSystemView.getFileSystemView();
File com=fsv.getHomeDirectory(); //这便是读取桌面路径的方法了
String path = com.getPath();
// 生成EXCEL并指定输出路径
OutputStream out = new FileOutputStream(path+"\\withoutHead2.xlsx");
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
// 设置SHEET
Sheet sheet = new Sheet(1, 0);
sheet.setSheetName("sheet1");
// 设置标题
Table table = new Table(1);
List<List<String>> titles = new ArrayList<List<String>>();
titles.add(Arrays.asList("用户ID"));
titles.add(Arrays.asList("名称"));
titles.add(Arrays.asList("年龄"));
titles.add(Arrays.asList("生日"));
table.setHead(titles);
// 模拟分批查询：总记录数50条，每次查询20条， 分三次查询 最后一次查询记录数是10
Integer totalRowCount = 1000000;
Integer pageSize = 500;
Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1);
long l = System.currentTimeMillis();
// 注： 此处仅仅为了模拟数据，实用环境不需要将最后一次分开，合成一个即可， 参数为： currentPage = i+1; pageSize = pageSize
for (int i = 0; i < writeCount; i++) {
// 前两次查询 每次查20条数据
if (i < writeCount - 1) {
List<List<String>> userList = new ArrayList<>();
for (int j = 0; j < pageSize; j++) {
userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
}
writer.write0(userList, sheet, table);
} else if (i == writeCount - 1) {
// 最后一次查询 查多余的10条记录
List<List<String>> userList = new ArrayList<>();
Integer lastWriteRowCount = totalRowCount - (writeCount - 1) * pageSize;
for (int j = 0; j < lastWriteRowCount; j++) {
userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
}
writer.write0(userList, sheet, table);
}
}
long ll = System.currentTimeMillis();
System.out.println(ll-l);
writer.finish();
}
/**
* 针对几百万的记录数可以调用该方法分多批次查出然后写入到EXCEL的多个SHEET中
* 注意：
* perSheetRowCount % pageSize要能整除 为了简洁，非整除这块不做处理
* 每次查询出来的记录数量不宜过大，根据内存大小设置合理的每次查询记录数，不会内存溢出即可。
* @throws IOException
*/
public static void writeExcelMoreSheetMoreWrite() throws IOException {
FileSystemView fileSystemView=FileSystemView.getFileSystemView();
File homeDirectory = fileSystemView.getHomeDirectory();
String path = homeDirectory.getPath();
// 生成EXCEL并指定输出路径
OutputStream out = new FileOutputStream(path+"\\withoutHead3.xlsx");
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
// 设置SHEET名称
String sheetName = "测试SHEET";
// 设置标题
Table table = new Table(1);
List<List<String>> titles = new ArrayList<List<String>>();
titles.add(Arrays.asList("用户ID"));
titles.add(Arrays.asList("名称"));
titles.add(Arrays.asList("年龄"));
titles.add(Arrays.asList("生日"));
table.setHead(titles);
// 模拟分批查询：总记录数250条，每个SHEET存100条，每次查询20条 则生成3个SHEET，前俩个SHEET查询次数为5， 最后一个SHEET查询次数为3 最后一次写的记录数是10
// 注：该版本为了较少数据判断的复杂度，暂时perSheetRowCount要能够整除pageSize， 不去做过多处理 合理分配查询数据量大小不会内存溢出即可。
//总数
Integer totalRowCount = 250;
//每页数量
Integer perSheetRowCount = 100;
//每次查询数量
Integer pageSize = 20;
//页数
Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount)
: (totalRowCount / perSheetRowCount + 1);
//每页查询次数（目前只考虑整除的情况）
Integer previousSheetWriteCount = perSheetRowCount / pageSize;
//最后一页写次数
Integer lastSheetWriteCount =
//如果总数整除以每页数就等于
totalRowCount % perSheetRowCount == 0 ?
//每页查询次数
previousSheetWriteCount :
//不然就是总数除以每页数量取余数，再进行取每页数量取余数等于0
(totalRowCount % perSheetRowCount % pageSize == 0 ?
//那么最后一页写次数就等于总数除以每页数量取余数，再进行取每页数量取整数
//反之等于总数除以每页数量取余数，再进行取每页数量取整数加1
totalRowCount % perSheetRowCount / pageSize : (totalRowCount % perSheetRowCount / pageSize + 1));
for (int i = 0; i < sheetCount; i++) {
// 创建SHEET
Sheet sheet = new Sheet(i, 0);
sheet.setSheetName(sheetName + i);
if (i < sheetCount - 1) {
// 前2个SHEET, 每个SHEET查5次 每次查20条 每个SHEET写满100行 2个SHEET合计200行 实用环境：参数： currentPage: j+1 + previousSheetWriteCount*i, pageSize: pageSize
for (int j = 0; j < previousSheetWriteCount; j++) {
List<List<String>> userList = new ArrayList<>();
for (int k = 0; k < 20; k++) {
userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
}
writer.write0(userList, sheet, table);
}
} else if (i == sheetCount - 1) {
// 最后一个SHEET 实用环境不需要将最后一次分开，合成一个即可， 参数为： currentPage = i+1; pageSize = pageSize
for (int j = 0; j < lastSheetWriteCount; j++) {
// 前俩次查询 每次查询20条
if (j < lastSheetWriteCount - 1) {
List<List<String>> userList = new ArrayList<>();
for (int k = 0; k < 20; k++) {
userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
}
writer.write0(userList, sheet, table);
} else if (j == lastSheetWriteCount - 1) {
// 最后一次查询 将剩余的10条查询出来
List<List<String>> userList = new ArrayList<>();
Integer lastWriteRowCount = totalRowCount - (sheetCount - 1) * perSheetRowCount - (lastSheetWriteCount - 1) * pageSize;
for (int k = 0; k < lastWriteRowCount; k++) {
userList.add(Arrays.asList("ID_" + Math.random(), "小明1", String.valueOf(Math.random()), new Date().toString()));
}
writer.write0(userList, sheet, table);
}
}
}
}
writer.finish();
}
//数据量少的(20W以内吧)：一个SHEET一次查询导出
/**
*
* @param dataList // 最终要写入excel的数据
* @param objList //要导出对象的集合
* @param titles //表头 表头每列都是个集合
* @param sheetName //页面
* @param excelName //excelName
* @return
* @throws Exception
*/
public static void exportSysSystemExcel(List<List<String>> dataList, List<?> objList,List<List<String>> titles,String sheetName,String excelName, HttpServletResponse response) throws Exception {
FileSystemView fsv = FileSystemView.getFileSystemView();
File com=fsv.getHomeDirectory(); //这便是读取桌面路径的方法了
String path = com.getPath();
// 生成EXCEL并指定输出路径
// OutputStream out = new FileOutputStream(path+"\\withoutHead1.xls");
OutputStream out = null;
// ServletOutputStream out = null;
try {
out = response.getOutputStream();
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
// 设置EXCEL名称
String fileName = new String((excelName).getBytes(), "UTF-8");
// 设置SHEET名称
Sheet sheet = new Sheet(1, 0);
sheet.setSheetName(sheetName);
// 设置标题
Table table = new Table(1);
table.setHead(titles);
if (!CollectionUtils.isEmpty(objList)) {
objList.forEach(element -> {
//获取该对象的所有属性
Field[] declaredFields = element.getClass().getDeclaredFields();
List<String> list=new ArrayList<>();
List<Field> fields = Arrays.asList(declaredFields);
//注意实现序列化的接口，排除serialVersionUID
for (Field filed:fields) {
String name = filed.getName();
if(name.equals("serialVersionUID")){ continue; }
filed.setAccessible(true);
String value = null;
try {
value=String.valueOf(filed.get(element)) ;
} catch (IllegalAccessException e) {
e.printStackTrace();
}
list.add(value);
}
dataList.add(list);
});
}
long l = System.currentTimeMillis();
writer.write0(dataList, sheet, table);
// 下载EXCEL Content-Disposition中指定的类型是文件的扩展名
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xlsx");
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
writer.finish();
out.flush();
long ll = System.currentTimeMillis();
System.out.println(">>>>>>>:"+(ll-l));
} finally {
if (out != null) {
try {
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
public static void main(String[] args) throws IOException {
writeExcelOneSheetOnceWrite();
}
public void read() {
List<RespCustomerDailyImport> lists = new ArrayList<>();
MyListener<RespCustomerDailyImport> myListener = new MyListener<RespCustomerDailyImport>(lists);
ExcelReaderBuilder read = EasyExcel.read("C:\\Users\\pc\\Desktop\\滴滴滴.xlsx", RespCustomerDailyImport.class, myListener);
ExcelReaderSheetBuilder sheet = read.sheet();
sheet.doRead();
}
public static void write() {
ExcelWriterBuilder write = EasyExcel.write("C:\\Users\\pc\\Desktop\\滴滴滴0.xlsx", RespCustomerDailyImport.class);
ExcelWriterSheetBuilder sheet = write.sheet();
List list=new ArrayList();
for (int i = 0; i <5 ; i++) {
RespCustomerDailyImport respCustomerDailyImport=new RespCustomerDailyImport();
respCustomerDailyImport.setAccountReceivableQuota(BigDecimal.valueOf(111));
respCustomerDailyImport.setCustomerName(String.valueOf(111));
respCustomerDailyImport.setDailyInterestRate(BigDecimal.valueOf(111));
respCustomerDailyImport.setMisCode(String.valueOf(111));
respCustomerDailyImport.setMonthlyQuota(BigDecimal.valueOf(1111));
list.add(respCustomerDailyImport);
}
sheet.doWrite(list);
}
public static void main(String[] args) {
write();
}
@Data
public class RespCustomerDailyImport {
@ExcelProperty(value = "客户编码",index = 0)
private String customerName;
@ExcelProperty(value = "MIS编码",index = 1)
private String misCode;
@ExcelProperty(value = "月度滚动额",index = 2)
private BigDecimal monthlyQuota;
@ExcelProperty(value = "最新应收账款余额",index = 4)
private BigDecimal accountReceivableQuota;
@NumberFormat("#.##%")
@ExcelProperty(value = "本月利率(年化）",index = 3)
@ExcelIgnore
private BigDecimal dailyInterestRate;
}
@GetMapping("/export0")
public void export(HttpServletResponse response) throws IOException {
// 生成数据
List<RespCustomerDailyImport> respCustomerDailyImports = new ArrayList<>();
for (int i = 0; i < 50; i++) {
RespCustomerDailyImport respCustomerDailyImport =new RespCustomerDailyImport();
respCustomerDailyImport.setMisCode(String.valueOf(i));
respCustomerDailyImport.setCustomerName("customerName" + i);
respCustomerDailyImport.setMonthlyQuota(new BigDecimal(String.valueOf(i)));
respCustomerDailyImport.setAccountReceivableQuota(new BigDecimal(String.valueOf(i)));
respCustomerDailyImport.setDailyInterestRate(new BigDecimal(String.valueOf(i)));
respCustomerDailyImports.add(respCustomerDailyImport);
}
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("导出", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), RespCustomerDailyImport.class)
.sheet("sheet0")
// 设置字段宽度为自动调整，不太精确
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.doWrite(respCustomerDailyImports);
}
一点点学习，一丝丝进步。不懈怠，才不会被时代淘汰
