spring + jxls报表的导出
xlsx模板,采用流传输,导出报表。
maven
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>[2.6.0-SNAPSHOT,)</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>[1.2.0-SNAPSHOT,)</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-jexcel</artifactId>
<version>[1.0.8,)</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>[2.0.5,)</version>
</dependency>
jxls工具类
public class JxlsUtils{
private static final String TEMPLATE_PATH="excel";
public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException {
Context context = new Context();
if (model != null) {
for (String key : model.keySet()) {
context.putVar(key, model.get(key));
}
}
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
// 设置解析模板语法
jxlsHelper.setEvaluateFormulas(true);
Transformer transformer = jxlsHelper.createTransformer(is, os);
// JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
// Map<String, Object> funcs = new HashMap<String, Object>();
// funcs.put("utils", new JxlsUtils()); //添加自定义功能
// 旧版本添加自定义功能
// evaluator.getJexlEngine().setFunctions(funcs);
// 新版本添加自定义功能
// JexlBuilder jb = new JexlBuilder();
// jb.namespaces(funcs);
// JexlEngine je = jb.create();
// evaluator.setJexlEngine(je);
jxlsHelper.processTemplate(context, transformer);
}
public static void exportExcel(File xls, File out, Map<String, Object> model) throws FileNotFoundException, IOException {
exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
}
public static void exportExcel(String templateName, OutputStream os, Map<String, Object> model) throws FileNotFoundException, IOException {
File template = getTemplate(templateName);
if(template!=null){
exportExcel(new FileInputStream(template), os, model);
}
}
/**
* 导出excel到字节数组中
*
* @param templateName
* @param beans
* @return
*/
public static byte[] exportExcel2Bytes(String templateName, Map<String, Object> beans) {
ByteArrayOutputStream os = null;
try {
os = new ByteArrayOutputStream();
ClassPathResource classPathResource = new ClassPathResource(TEMPLATE_PATH + templateName);
JxlsUtil.exportExcel(classPathResource.getInputStream(), os, beans);
return os.toByteArray();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (os != null) {
try {
os.close();
} catch (IOException ex) {
ex.printStackTrace();
}
}
}
return new byte[0];
}
//获取jxls模版文件
public static File getTemplate(String name){
String templatePath = JxlsUtils.class.getClassLoader().getResource(TEMPLATE_PATH).getPath();
File template = new File(templatePath, name);
if(template.exists()){
return template;
}
return null;
}
// 日期格式化
public String dateFmt(Date date, String fmt) {
if (date == null) {
return "";
}
try {
SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
return dateFmt.format(date);
} catch (Exception e) {
e.printStackTrace();
}
return "";
}
// if判断
public Object ifelse(boolean b, Object o1, Object o2) {
return b ? o1 : o2;
}
}
实体类
导出代码
@RequestMapping(value = "exportDeviceModelMsg",method = RequestMethod.GET)
public ResponseEntity<byte[]> exportDeviceModelMsg(HttpServletRequest request, HttpServletResponse response) {
ByteArrayOutputStream os = new ByteArrayOutputStream();
List<User> myTestList = setData();
try {
Map<String , Object> model = new HashMap<>();
model.put("myTestList", myTestList);
JxlsUtils.exportExcel("test.xlsx", os, model);
//下载表格
HttpHeaders headers = new HttpHeaders();
String downloadFileName = "小明测试" + UUID.randomUUID().toString().substring(0, 4) + ".xlsx";
//防止中文名乱码
downloadFileName = new String(downloadFileName.getBytes("UTF-8"), "ISO-8859-1");
headers.setContentDispositionFormData("attachment", downloadFileName);
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
//返回
byte[] bytes = os.toByteArray();
os.close();
return new ResponseEntity<byte[]>(bytes, headers, HttpStatus.CREATED);
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
}
模拟数据:
public static List<User> setData(){
List<User> arrayList = new ArrayList();
for (int i = 0; i < 5; i++) {
User user = new User();
user.setId(i);
user.setAge(i * 10 + i);
user.setName("test-" + i);
user.setSex(i > 3 ? "男":"女");
user.setBeginDt("201"+i + "-01-01");
user.setEndDt("201"+i + "-12-12");
user.setCycleDateList("tst");
arrayList.add(user);
}
return arrayList;
}
xlsx模板,采用字节数组转成字符串返回前段,导出报表。
@GetMapping("/exportProject")
public CommonResult exportStareWith(HttpServletRequest request,
@RequestParam("type") Integer type) throws BusException, AccessDeniedException {
User user = this.getCurrentUser();
byte[] bytes = reportService.exportStareWith(ProjectPhaseEnum.parse(type), user);
return CommonResult.success(bytes);
}
前段代码:
// 接口返回的是字符串
testBtn4 () {
axios({
method: 'get',
url: 'http://localhost:8099/approval/?resultIds=34'
}).then((data) => {
// 获取返回的字符串数据
var raw = window.atob(data.data)
// 转编码
var uInt8Array = new Uint8Array(raw.length)
for (var i = 0; i < raw.length; i++) {
uInt8Array[i] = raw.charCodeAt(i)
}
const blob = new Blob([uInt8Array], {
type: 'application/octet-stream'
})
// 下载文件
const link = document.createElement('a')
link.style.display = 'none'
link.href = URL.createObjectURL(blob)
link.download = '导出报表' + Date.parse(new Date()) + '.zip'
document.body.appendChild(link)
link.click()
document.body.removeChild(link)
this.lcoalMsg = 'end'
})
},