Java常⽤excel导出实现
最近在做很多报表相关的开发，包括使⽤shell和hive将⼤数据汇总，然后页⾯做展⽰，还有将结果导出excel发送给相关负责⼈，由于导出表格的时候，有很多场景，现在针对⼏种常⽤场景，进⾏⼀些整理，⽅便以后使⽤。
1.少量数据，使⽤l.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)throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这⾥de可以防⽌中⽂乱码当然和easyexcel没有关系
response.setHeader("Content-disposition","attachment;filename*=utf-8''"+ fileName +".xlsx");
// 这⾥需要指定写⽤哪个class去写，然后写到第⼀个sheet，名字为模板然后⽂件流会⾃动关闭
ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.OutputStream(), clazz) .inMemory(true)
.includeColumnFiledNames(includeColumnNames)
.sheet("模板");
for(WriteHandler handler : writeHandler){
}
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 = de("XXX分析明细报表导出"+  w().toString().substring(0,10)).replaceAll("\\+","%20");
//List类型的数据
List<XXXXXResp> data = Data();
}
2.⼤数据量导出使⽤CSV
public void export(HttpServletRequest request, HttpServletResponse response)throws IOException, InterruptedException {
String startTime = Parameter("beginDate");
String endTime = Parameter("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= AllDetail(startTime, endTime);
String fileName ="XXXX明细导出-";
try{
OutputStream os = OutputStream();
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){
<("导出数据失败 ,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="+ de(fn, utf));
}
/**
* CSV⽂件列分隔符
*/
浏览器json格式化*/
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++){
(i))){
buf.append("null".((i)))?"-": ((i))+"").append(CSV_COLUMN_SEPARATOR);
}
}
buf.append(CSV_ROW_SEPARATOR);
}
}
// 写出响应
os.String().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 InterruptedExcepti on, FileNotFoundException {
long start = System.currentTimeMillis();
String fileDir = SRC_DIR;
//统计条数，每个表格5w条
int count = untDetail(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;
List<XXXXXVO> vo = XXXXXMapper.findDetail(startTime,endTime);
log.info("开始导出⽂件：第"+ j +"个⽂件----"+ fileName +"size :"+ dataList.size());
try{
}catch(IOException e){
e.printStackTrace();
}
}
);
}
cdl.await();
/
/将⽂件夹压缩
//将压缩⽂件响应给浏览器
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 {

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。