Java导出excel Java实体转Map
/**
* 下载Map类型数据的公共方法 EXCEL
* @param mapList 需要导出的数据
* @param templateCode 模板名称或文件地址
* @param dateKey 字段名及对应的列
* @param count 开始放入数据的行
* @param fileName 下载文件名
* @param response response
*/
public void exportUtil(List<Map<String, String>> mapList, String templateCode, Map<Integer, String> dateKey, String fileName, int count, HttpServletResponse response) {
//从微服务下载模板
//FileTeplResVo teplResVo = fileTemplateApi.queryFileId(ModuleConstant.MARKET, templateCode);
//String fileId = teplResVo.getFileId();
//FileVo fileVo = fileUploadApi.downLoad(fileId, ModuleConstant.MARKET, AuthUtil.getUserId(), false);
//获取文件模板
try (InputStream inStream = this.getClass().getResourceAsStream(templateCode);
OutputStream outputStream = response.getOutputStream();
XSSFWorkbook wb = new XSSFWorkbook(inStream)) {
//设置response属性
this.setResponse(response, Optional.ofNullable(fileName).orElse("my.xlsx"));
//读取了模板第一个sheet页
XSSFSheet sheet = wb.getSheetAt(InternalNumConstant.ZERO);
//样式
XSSFCellStyle cellStyle = this.getCellStyle(wb);
Row rowTemp;
//循环数据的行
for (Map item : mapList) {
rowTemp = sheet.createRow(count);
//循环列
for (Map.Entry<Integer, String> item1 : dateKey.entrySet()) {
// 设置单元格内容
rowTemp.createCell(item1.getKey()).setCellValue(Optional.ofNullable(item.get(item1.getValue())).map(x -> x.toString()).orElse(""));
// 设置样式
rowTemp.getCell(item1.getKey()).setCellStyle(cellStyle);
}
count++;
}
//设置统计时间
rowTemp = sheet.createRow(count);
DateTimeFormatter formatter = DateTimeFormatter.ofPattern(InternalStrConstant.DATE_FORMAT_STR);
rowTemp.createCell(dateKey.size() - 1).setCellValue("统计日期:" + LocalDate.now().format(formatter));
wb.write(outputStream);
outputStream.flush();
} catch (IOException e) {
log.error(e.getMessage());
}
}
/**
* 获取cell样式 加黑色边框
* @param wb excel文件
* @return XSSFCellStyle样式
*/
public XSSFCellStyle getCellStyle(XSSFWorkbook wb) {
//样式
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
return cellStyle;
}
/**
* 设置response属性
* @param response response
* @param fileName 模板文件名
* @exception UnsupportedEncodingException 异常
*/
public static void setResponse(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
// 清空输出流
response.reset();
// 定义输出类型
response.setContentType(InternalStrConstant.CONTENT_TYPE);
// 定义输出类型 ;charset=UTF-8
response.setContentType(InternalStrConstant.CONTENT_TYPE_UTF_8);
//设置Http响应头告诉浏览器下载这个附件
response.setHeader("content-disposition",
"attachment; filename=" + URLEncoder.encode(fileName, InternalStrConstant.UTF_8));
}
如需导出实体对象的数据,可以将实体转化为map
/**
* 实体转map通用方法
* @param bean 实体
* @return map集合
* @throws IntrospectionException IntrospectionException
* @throws InvocationTargetException InvocationTargetException
* @throws IllegalAccessException IllegalAccessException
*/
public static Map convertBean(Object bean) throws IntrospectionException, InvocationTargetException, IllegalAccessException {
Class type = bean.getClass();
Map returnMap = new HashMap();
BeanInfo beanInfo = Introspector.getBeanInfo(type);
PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
for (PropertyDescriptor descriptor : propertyDescriptors) {
String propertyName = descriptor.getName();
if (!"class".equals(propertyName)) {
Method readMethod = descriptor.getReadMethod();
Object result = readMethod.invoke(bean);
if (result != null) {
returnMap.put(propertyName, result);
} else {
returnMap.put(propertyName, "");
}
}
}
return returnMap;
}
使用方法举例:
public void download(String unitId, String period, HttpServletResponse response) {
//查询数据
List<Map<String, String>> queryList = this.differenceAnalysis(unitId, period).getItems();
//生成序号列
int oderNumber = InternalNumConstant.ONE;
for (Map item : queryList) {
item.put("oderNumber", oderNumber);
oderNumber++;
}
//设置地段对应的列
Map<Integer, String> mapKey = new HashMap<>();
mapKey.put(0, "oderNumber");
mapKey.put(1, "unitName");
mapKey.put(2, "byUnit");
mapKey.put(3, "byPostCategory");
mapKey.put(4, "byMajor");
mapKey.put(5, "unitAndPostCategory");
mapKey.put(6, "unitAndMajor");
mapKey.put(7, "majorAndPostCategory");
//导出excel
this.exportUtil(queryList, "模板名称或文件地址", mapKey, "导出的文件名称.xlsx", 2, response);
}