1.导出为单个Excel表,2.一个Excel包含多个sheel表 3.导出多个Excel并压缩

1.普通单个Excel导出

1.Excel表中的字段与要导出的数据一致

public class **OrderDTO** {
	@ExcelProperty(value = {"Excel表名", "Excel表字段"})
	@ColumnWidth(20)列宽
	private String orderSn;
}

2.Excel工具类编写

public class ExcelUtils {

    /**
     * 导出excel核心方法
     *
     * @param
     */
    public static void export(HttpServletResponse response, Class head, List data, String sheetName) throws IOException {
        //给定导出实体类
        EasyExcel.write(response.getOutputStream(), head)
                //给定工作表名称
                .sheet(sheetName)
                //给定导出数据
                .doWrite(data);
    }
    /**
     * 设置请求头、文件名
     *
     * @param fileName excel文件名
     */
    public static void setResponse(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        //编码设置成UTF-8，excel文件格式为.xlsx
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        // 这里URLEncoder.encode可以防止中文乱码 和easyexcel本身没有关系
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    }
}

3.实现类编写ServiceImpl

@Override
public void getEcsResourceExcel(HttpServletResponse response) throws IOException {
    List<EcsPreviewModel> ecsPreviewList = ecsService.getEcsPreviewList();
    String fileName = URLEncoder.encode("ECS", "UTF-8");
    ExcelUtils.setResponse(response, fileName);
    ExcelUtils.export(response, EcsPreviewModel.class, ecsPreviewList, "ECS");
}

Excel含多个sheel表的导出

3.实现类编写ServiceImpl

@Override
    public void getBatchExcel(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        List<RPreviewModel> rPreviewList = rService.getRPreviewList();
        List<SPreviewModel> sPreviewList = sService.getSPreviewList();
        List<OPreviewModel> oPreviewList = oService.getOPreviewList();
        List<EPreviewModel> ePreviewList = eService.getEPreviewList();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        try {
            String fileName = URLEncoder.encode("资源预览表", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            //新建ExcelWriter
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            //获取sheet0对象
            WriteSheet eSheet = EasyExcel.writerSheet(0, "sheel名称").head(EPreviewModel.class).build();
            //向sheet0写入数据 传入空list这样只导出表头
            excelWriter.write(ePreviewList,eSheet);

            WriteSheet rSheet = EasyExcel.writerSheet(1, "sheel名称").head(RPreviewModel.class).build();
            excelWriter.write(rPreviewList,rSheet);

            WriteSheet sSheet = EasyExcel.writerSheet(2, "sheel名称").head(SPreviewModel.class).build();
            excelWriter.write(sPreviewList,sSheet);

            WriteSheet oSheet = EasyExcel.writerSheet(3, "sheel名称").head(OPreviewModel.class).build();
            excelWriter.write(oPreviewList,oSheet);
            //关闭流
            excelWriter.finish();
        } catch (IOException e) {
            log.error("导出异常{}", e.getMessage());
        }
    }

3.导出多个Excel并压缩

3.导出为Excel表并进行压缩(ServiceImpl)

@Override
@Transactional(rollbackFor = Exception.class)
public void export(List<String> list, HttpServletRequest request, HttpServletResponse response) {
	String dir = String.valueOf(System.currentTimeMillis());
	//1.生成excel文件
	createExcel(list, dir);
	//2.压缩文件
	try {
		ZipUtil.zip(filePath + File.separator + tempDir);
	} catch (IOException e) {
		log.info("压缩文件失败！", e);
		throw new RuntimeException("压缩文件失败！");
	}
	//3.下载文件
	File file = new File(filePath + File.separator + tempDir + ".zip");
	try {
		response = DownLoadHelper.filenameEncode(tempDir + File.separator + ".zip", request, response);
		FileUtils.copyFile(file , response.getOutputStream());
	} catch (IOException e) {
		log.info("下载文件失败！", e);
		throw new RuntimeException("下载文件失败！");
	} finally {
		try {
			FileUtils.forceDelete(new File(filePath + File.separator + tempDir));
			FileUtils.forceDelete(file );
		} catch (IOException e) {
			log.info("删除文件失败！");
		}
	}
}

2.创建excel文件

	private void createExcel(List<String> list, String tempDir) {
		**//所有要导出的商品信息**
		List<OrderDTO> procurementProductDTOS = procurementOrderMapper.productByList(list);
					//订单去重
					data1 = data1.stream().distinct().collect(Collectors.toList());
					//合计行
					InvoiceProductDTO productDTO = new InvoiceProductDTO();
					productDTO.setIdx("合计");
					.....
					productDTO.setEstimateAmount(totalAmount.toString());
					data2.add(productDTO);
					Sheet sheet = new Sheet(n + 1, 0);
					sheet.setSheetName(carNameList.get(n));
					Table table1 = new Table(1);
					table1.setClazz(InvoiceOrderDTO.class);
					writer.write(data1, sheet, table1);
					Table table2 = new Table(2);
					table2.setClazz(InvoiceProductDTO.class);
					writer.write(data2, sheet, table2);
				}
				writer.finish();
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			}

		});

	}

时间转化将毫秒值转化为天时分秒

final long milliseconds = 5478965412358l;
  final long day = TimeUnit.MILLISECONDS.toDays(milliseconds);
  
  final long hours = TimeUnit.MILLISECONDS.toHours(milliseconds)
    - TimeUnit.DAYS.toHours(TimeUnit.MILLISECONDS.toDays(milliseconds));
  
  final long minutes = TimeUnit.MILLISECONDS.toMinutes(milliseconds)
    - TimeUnit.HOURS.toMinutes(TimeUnit.MILLISECONDS.toHours(milliseconds));
  
  final long seconds = TimeUnit.MILLISECONDS.toSeconds(milliseconds)
    - TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(milliseconds));
  
  final long ms = TimeUnit.MILLISECONDS.toMillis(milliseconds)
    - TimeUnit.SECONDS.toMillis(TimeUnit.MILLISECONDS.toSeconds(milliseconds));
  
  System.out.println("milliseconds :-" + milliseconds);
  System.out.println(String.format("%d Days %d Hours %d Minutes %d Seconds %d Milliseconds", day, hours, minutes, seconds, ms));