EasyPOI 是一款Excel导入/导出操作的工具包,特点在于减少Java代码。
业务场景
需求:
将模板生成的Excel文件打成压缩包导出。
要求:
不能在服务器生成冗余临时文件;
程序打成jar包执行时,可以准确找到Excel模板文件。
1. Service
import org.springframework.http.ResponseEntity;
/**
* <p> @Title ExportService
* <p> @Description 导出测试Service
*
* @author ACGkaka
* @date 2020/9/15 15:10
*/
public interface ExportService {
/**
* 通过字节流导出文件
*
* @return ResponseEntity
*/
ResponseEntity exportByByte();
}
2. ServiceImpl
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
/**
* <p> @Title ExportServiceImpl
* <p> @Description 导出测试ServiceImpl
*
* @author ACGkaka
* @date 2020/9/15 15:11
*/
@Service
@AllArgsConstructor
public class ExportServiceImpl {
UserMapper userMapper;
@Override
public ResponseEntity exportByByte() {
List<User> list = userMapper.queryAll();
Map<String, Object> map = new HashMap<>();
map.put("entityList", list);
// 导出, 模板文件在 resources/file/demo 下
// 这样存储的好处是, 如果打成jar包不影响模板文件的定位
TemplateExportParams params = new TemplateExportParams("file/demo/" + "excel_template.xlsx");
Workbook workbook = ExcelExportUtil.exportExcel(params, map);
// 字节缓冲区, 是内存读写流, 不同于指向硬盘的流, 字节数组是成员变量, 当数组不再使用的时候, GC会自动回收, 不用手动关闭流
ByteArrayOutputStream out = new ByteArrayOutputStream();
try {
workbook.write(out);
// 打压缩包
out = zip(new ByteArrayInputStream(out.toByteArray()), "test.xlsx");
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
headers.setContentDispositionFormData("attachment", URLEncoder.encode("导出文件.xlsx", "utf-8"));
return new ResponseEntity<>(out.toByteArray(), headers, HttpStatus.CREATED);
} catch (IOException e) {
throw new RuntimeException("临时文件写入失败");
}
}
/**
* 打压缩包
*
* @param in 要打包的文件流
* @param fileName 要打包的文件名
*/
public static ByteArrayOutputStream zip(InputStream in, String fileName) {
ByteArrayOutputStream out = new ByteArrayOutputStream();
try (ZipOutputStream zipOut = new ZipOutputStream(out)) {
zipOut.putNextEntry(new ZipEntry(fileName));
IOUtils.copy(in, zipOut);
} catch (IOException e) {
throw new RuntimeException("打包异常: " + e.getMessage());
}
return out;
}
}