最近在做很多报表相关的开发，包括使用shell和hive将大数据汇总，然后页面做展示，还有将结果导出excel发送给相关负责人，由于导出表格的时候，有很多场景，现在针对几种常用场景，进行一些整理，方便以后使用。
1.少量数据，使用alibaba.excel.EasyExcelExcelUtils.java：
@Slf4j
public class ExcelUtils {
public static void templateWrite(InputStream templateInputStream,
File outer,
String sheetName,
Class clazz,
List datas,
WriteHandler writeHandler) {
EasyExcel.write(outer, clazz)
.needHead(false)
.inMemory(true)
.registerWriteHandler(writeHandler)
.withTemplate(templateInputStream)
.sheet(sheetName)
.doWrite(datas);
}
public static void export(HttpServletResponse response,
List data,
List includeColumnNames,
Class clazz,
String fileName,
WriteHandler... writeHandler) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.write(response.getOutputStream(), clazz)
.inMemory(true)
.includeColumnFiledNames(includeColumnNames)
.sheet("模板");
for (WriteHandler handler : writeHandler) {
excelWriterSheetBuilder.registerWriteHandler(handler);
}
excelWriterSheetBuilder.doWrite(data);
}
使用样例：
public void export(XXXXReq req, HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException, IOException {
//定义导出的字段
List includeColumnNames = Arrays.asList
("aaa","bbb","ccc","ddd","eee","......");
//定义导出文件名
String fileName = URLEncoder.encode("XXX分析明细报表导出" + LocalDateTime.now().toString().substring(0,10)).replaceAll("\+", "%20");
//List类型的数据
List data = XXXXDetail.getData();
ExcelUtils.export(response,data.getList(),includeColumnNames,XXXXXResp.class,fileName);
}
2.大数据量导出使用CSV
public void export(HttpServletRequest request, HttpServletResponse response) throws IOException, InterruptedException {
String startTime = request.getParameter("beginDate");
String endTime = request.getParameter("endDate");
long start = System.currentTimeMillis();
//定义导出的字段
List titles = Arrays.asList
("aaa","bbb","ccc","ddd","eee","fff","....");
List titleStrs = Arrays.asList
("列名1","列名2","列名3","列名4","列名5","列名6","......");
List vo= XXXXXMapper.getAllDetail(startTime, endTime);
String fileName = "XXXX明细导出-";
try {
OutputStream os = response.getOutputStream();
this.responseSetProperties(fileName, response);
this.doExport(vo, titleStrs,os,titles);
os.close();
long millis = System.currentTimeMillis() - start;
long second = millis / 1000;
log.info("导出数据成功:数量为--->" + vo.size()+",花费时长："+second + "s");
}catch (Exception e){
log.error("导出数据失败 ,error:{}",e.getMessage());
}
}
public void responseSetProperties(String fileName, HttpServletResponse response)
throws UnsupportedEncodingException {
// 设置文件后缀
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
String fn = fileName + sdf.format(new Date()) + ".csv";
// 读取字符编码
String utf = "utf-8";
// 设置响应
response.setContentType("application/ms-txt.numberformat:@");
response.setCharacterEncoding(utf);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fn, utf));
}
private static final String CSV_COLUMN_SEPARATOR = ",";
private static final String CSV_ROW_SEPARATOR = "rn";
public void doExport(List dataList, List titleStr, OutputStream os,List titles)
throws Exception {
// 保证线程安全
StringBuffer buf = new StringBuffer();
// 组装表头
for (String title : titleStr) {
buf.append(title).append(CSV_COLUMN_SEPARATOR);
}
buf.append(CSV_ROW_SEPARATOR);
// 组装数据
if (CollectionUtils.isNotEmpty(dataList)) {
for (XXXXXVO data : dataList) {
//转成jsonString
String jsonList ="{"xxxx":"xxxx"......}";
//需要注意，导出实体类需要每个属性都是String类型，否则需要转换
Map map = JSONObject.parseObject(jsonList,Map.class);
for (int i = 0; i < titles.size(); i++) {
if (map.containsKey(titles.get(i))){
buf.append("null".equals(map.get(titles.get(i))) ? "-" : map.get(titles.get(i))+"").append(CSV_COLUMN_SEPARATOR);
}
}
buf.append(CSV_ROW_SEPARATOR);
}
}
// 写出响应
os.write(buf.toString().getBytes("GBK"));
os.flush();
}
3.大数据量，使用EasyExcel，多线程分多个文件，合并压缩包导出
//文件定义相关
private static final String SRC_DIR = "app/tempexcel/src/";
private static final String DELETE_DIR = "app/tempexcel/src/";
private static final String XLSX_SUFFIX = ".xlsx";
private static final String FILE_PREFIX = "XXX明细导出文件_";
private static final String ZIP_SUFFIX = ".zip";
public void doExportDetail(HttpServletResponse response ,String startTime, String endTime,List includeColumnNames) throws InterruptedException, FileNotFoundException {
long start = System.currentTimeMillis();
String fileDir = SRC_DIR;
FileUtils.creatFileDirs(fileDir);
//统计条数,每个表格5w条
int count = XXXXXMapper.countDetail(startTime,endTime)/50000;
CountDownLatch cdl = new CountDownLatch(count);
//xlsx文件时间格式化
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddmmssSSS");
//zip文件时间格式化
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMMdd");
for (int i = 0; i <= count; i++) {
int j = i+1;
//多线程导出文件
String fileName = fileDir + FILE_PREFIX + sdf.format(new Date()) + Math.random() +"-" + j + XLSX_SUFFIX;
executorService.execute(() -> {
List vo = XXXXXMapper.findDetail(startTime,endTime);
log.info("开始导出文件：第" + j + "个文件----" + fileName + "size :" + dataList.size());
try {
ExcelUtils.export(vo, includeColumnNames, fileName);
} catch (IOException e) {
e.printStackTrace();
}
cdl.countDown();
}
);
}
cdl.await();
//将文件夹压缩
FileUtils.toZip(SRC_DIR,new FileOutputStream(new File(SRC_DIR + FILE_PREFIX + sdf1.format(new Date()) + ZIP_SUFFIX)),false);
//将压缩文件响应给浏览器
FileUtils.sendZip(response,new File(SRC_DIR + FILE_PREFIX + sdf1.format(new Date()) + ZIP_SUFFIX));
//删除文件夹
boolean isDelete = FileUtils.deleteDir(DELETE_DIR);
System.out.println(isDelete);
long millis = System.currentTimeMillis() - start;
long second = millis / 1000;
log.info("Page Thread 导出数据，花费：" + second + "s/ " + millis + "ms");
}
FileUtils.java:
public class FileUtils {
private static final int BUFFER_SIZE = 2 * 1024;
public static void toZip(String srcDir, OutputStream out, boolean KeepDirStructure)
throws RuntimeException {
log.info("正在压缩文件。。。");
long start = System.currentTimeMillis();
ZipOutputStream zos = null;
try {
zos = new ZipOutputStream(out);
File sourceFile = new File(srcDir);
compress(sourceFile, zos, sourceFile.getName(), KeepDirStructure);
long end = System.currentTimeMillis();
log.info("压缩完成，耗时：" + (end - start) + " ms");
} catch (Exception e) {
throw new RuntimeException("zip error from ZipUtils", e);
} finally {
if (zos != null) {
try {
zos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
public static void toZip(List srcFiles, OutputStream out) throws RuntimeException {
long start = System.currentTimeMillis();
ZipOutputStream zos = null;
try {
zos = new ZipOutputStream(out);
for (File srcFile : srcFiles) {
byte[] buf = new byte[BUFFER_SIZE];
zos.putNextEntry(new ZipEntry(srcFile.getName()));
int len;
FileInputStream in = new FileInputStream(srcFile);
while ((len = in.read(buf)) != -1) {
zos.write(buf, 0, len);
}
zos.closeEntry();
in.close();
}
long end = System.currentTimeMillis();
System.out.println("压缩完成，耗时：" + (end - start) + " ms");
} catch (Exception e) {
throw new RuntimeException("zip error from ZipUtils", e);
} finally {
if (zos != null) {
try {
zos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
private static void compress(File sourceFile, ZipOutputStream zos, String name,
boolean KeepDirStructure) throws Exception {
byte[] buf = new byte[BUFFER_SIZE];
if (sourceFile.isFile()) {
// 向zip输出流中添加一个zip实体，构造器中name为zip实体的文件的名字
zos.putNextEntry(new ZipEntry(name));
// copy文件到zip输出流中
int len;
FileInputStream in = new FileInputStream(sourceFile);
while ((len = in.read(buf)) != -1) {
zos.write(buf, 0, len);
}
// Complete the entry
zos.closeEntry();
in.close();
} else {
//是文件夹
File[] listFiles = sourceFile.listFiles();
if (listFiles == null || listFiles.length == 0) {
// 需要保留原来的文件结构时,需要对空文件夹进行处理
if (KeepDirStructure) {
// 空文件夹的处理
zos.putNextEntry(new ZipEntry(name + "/"));
// 没有文件，不需要文件的copy
zos.closeEntry();
}
} else {
for (File file : listFiles) {
// 判断是否需要保留原来的文件结构
if (KeepDirStructure) {
// 注意：file.getName()前面需要带上父文件夹的名字加一斜杠,
// 不然最后压缩包中就不能保留原来的文件结构,即：所有文件都跑到压缩包根目录下了
compress(file, zos, name + "/" + file.getName(), KeepDirStructure);
} else {
compress(file, zos, file.getName(), KeepDirStructure);
}
}
}
}
}
public static void sendZip(HttpServletResponse response, File zipFile) {
log.info("正在发送zip包");
OutputStream outputStream = null;
BufferedInputStream fis = null;
try {
// 以流的形式下载文件。
fis = new BufferedInputStream(new FileInputStream(zipFile.getPath()));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
// 清空response
response.reset();
outputStream = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(zipFile.getName().getBytes("UTF-8"), "ISO-8859-1"));
outputStream.write(buffer);
outputStream.flush();
log.info("发送成功。");
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (fis != null) { fis.close(); }
if (outputStream != null) { outputStream.close(); }
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static boolean deleteFile(File dirFile) {
// 如果dir对应的文件不存在，则退出
if (!dirFile.exists()) {
return false;
}
if (dirFile.isFile()) {
return dirFile.delete();
} else {
for (File file : dirFile.listFiles()) {
deleteFile(file);
}
}
return dirFile.delete();
}
public static void creatFileDirs(String path) {
//首先要创建目标路径
File files = new File(path);
if (!files.exists()) {
if (files.mkdirs()) {
log.info("创建多个临时目录成功");
} else {
log.info("创建多个临时目录失败.....");
}
}
}
public static boolean deleteDir(String dir) {
File file = new File(dir);
boolean delete ;
if (file.isDirectory()) {
String[] children = file.list();
if(children.length>0){
for (int i=0; i 
