最近在做很多报表相关的开发,包括使用shell和hive将大数据汇总,然后页面做展示,还有将结果导出excel发送给相关负责人,由于导出表格的时候,有很多场景,现在针对几种常用场景,进行一些整理,方便以后使用。
1.少量数据,使用alibaba.excel.EasyExcel
ExcelUtils.java:
@Slf4j
public class ExcelUtils {
/**
* Excel模板导出
*
* @param templateInputStream
* @param outer
* @param sheetName
* @param clazz
* @param datas
* @param writeHandler
*/
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);
}
/**
* 导出
*
* @param response response
* @param data 数据
* @param includeColumnNames 需要导出的列名
* @param clazz
* @param fileName
* @param writeHandler
* @throws IOException
*/
public static void export(HttpServletResponse response,
List data,
List<String> 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<String> includeColumnNames = Arrays.asList
("aaa","bbb","ccc","ddd","eee","......");
//定义导出文件名
String fileName = URLEncoder.encode("XXX分析明细报表导出" + LocalDateTime.now().toString().substring(0,10)).replaceAll("\\+", "%20");
//List类型的数据
List<XXXXXResp> 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<String> titles = Arrays.asList
("aaa","bbb","ccc","ddd","eee","fff","....");
List<String> titleStrs = Arrays.asList
("列名1","列名2","列名3","列名4","列名5","列名6","......");
List<XXXXXVO> 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());
}
}
/**
* 设置Header
*
* @param fileName
* @param response
* @throws UnsupportedEncodingException
*/
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));
}
/**
* CSV文件列分隔符
*/
private static final String CSV_COLUMN_SEPARATOR = ",";
/**
* CSV文件行分隔符
*/
private static final String CSV_ROW_SEPARATOR = "\r\n";
/**
* @param dataList
* 集合数据
* @param titles
* 表头部数据
*
* @param os
* 输出流
*/
public void doExport(List<XXXXXVO> dataList, List<String> titleStr, OutputStream os,List<String> 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<String,String> 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<String> 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<XXXXXVO> 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;
/**
* 压缩成ZIP 方法 * @param srcDir 压缩文件夹路径
*
* @param out 压缩文件输出流
* @param KeepDirStructure 是否保留原来的目录结构,true:保留目录结构;
* false:所有文件跑到压缩包根目录下(注意:不保留目录结构可能会出现同名文件,会压缩失败)
* @throws RuntimeException 压缩失败会抛出运行时异常
*/
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();
}
}
}
}
/**
* 压缩成ZIP 方法 * @param srcFiles 需要压缩的文件列表
*
* @param out 压缩文件输出流
* @throws RuntimeException 压缩失败会抛出运行时异常
*/
public static void toZip(List<File> 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();
}
}
}
}
/**
* 递归压缩方法
*
* @param sourceFile 源文件
* @param zos zip输出流
* @param name 压缩后的名称
* @param KeepDirStructure 是否保留原来的目录结构,true:保留目录结构;
* false:所有文件跑到压缩包根目录下(注意:不保留目录结构可能会出现同名文件,会压缩失败)
* @throws Exception
*/
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);
}
}
}
}
}
/**
* 向浏览器发送zip包
*
* @param response
*/
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();
}
}
}
/**
* @Description //TODO
* @Date 2021/3/26 20:24
* @param dirFile
* @return boolean
*/
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();
}
/**
* -创建多个目录(子目录)
*
* @param path 路径
*/
public static void creatFileDirs(String path) {
//首先要创建目标路径
File files = new File(path);
if (!files.exists()) {
if (files.mkdirs()) {
log.info("创建多个临时目录成功");
} else {
log.info("创建多个临时目录失败.....");
}
}
}
/**
* 递归删除临时目录下所有文件以及文件夹
* @param dir
* @return
*/
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<children.length; i++) {
boolean success = deleteDir(file.getPath()+"/"+children[i]);
if (!success) {
return false;
}
}
}
delete = file.delete();
}else {
delete = file.delete();
}
return delete;
}
}