运行环境
产品安装
产品使用
模板制作
二次开发
PublicCMS对excel导出功能做了简化封装，视图名返回com.publiccms.common.view.ExcelView，即可完成excel文件的渲染，该类构造方法参数有两个，文件名和消费者，消费者用于构造数据体，文件名也可以在后续方法中传入，如果没有文件名，则请求路径export就可能被当作文件名
@RequestMapping("export")
public ExcelView export() {
ExcelView view = new ExcelView(workbook -> {
Sheet sheet = workbook.createSheet("sheetname");
int i = 0, j = 0;
Row row = sheet.createRow(i++);
row.createCell(j++).setCellValue("id");
row.createCell(j++).setCellValue("title");
row = sheet.createRow(i++);
row.createCell(j++).setCellValue("id");
row.createCell(j++).setCellValue("title");
});
view.setFilename("filename");
return view;
}实际开发中，我们需要根据请求参数查询数据主体，excel表头、文件名等需要进行国际化处理，可以参考下面具体示例：
package com.publiccms.controller.admin.cms;
import java.io.Serializable;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.support.RequestContextUtils;
import com.publiccms.common.annotation.Csrf;
import com.publiccms.common.constants.CommonConstants;
import com.publiccms.common.handler.PageHandler;
import com.publiccms.common.tools.CommonUtils;
import com.publiccms.common.tools.DateFormatUtils;
import com.publiccms.common.tools.ExtendUtils;
import com.publiccms.common.tools.LanguagesUtils;
import com.publiccms.common.view.ExcelView;
import com.publiccms.entities.cms.CmsPlace;
import com.publiccms.entities.cms.CmsPlaceAttribute;
import com.publiccms.entities.sys.SysExtendField;
import com.publiccms.entities.sys.SysSite;
import com.publiccms.entities.sys.SysUser;
import com.publiccms.logic.component.site.SiteComponent;
import com.publiccms.logic.component.template.MetadataComponent;
import com.publiccms.logic.component.template.TemplateComponent;
import com.publiccms.logic.service.cms.CmsPlaceAttributeService;
import com.publiccms.logic.service.cms.CmsPlaceService;
import com.publiccms.logic.service.log.LogOperateService;
import com.publiccms.logic.service.sys.SysUserService;
import com.publiccms.views.pojo.entities.CmsPlaceMetadata;
/**
*
* cmsPlaceController
*
*/
@Controller
@RequestMapping("cmsPlace")
public class CmsPlaceAdminController {
protected final Log log = LogFactory.getLog(getClass());
@Autowired
private CmsPlaceService service;
@Autowired
private CmsPlaceAttributeService attributeService;
@Autowired
private SysUserService sysUserService;
@Autowired
private MetadataComponent metadataComponent;
@Autowired
protected LogOperateService logOperateService;
@Autowired
protected SiteComponent siteComponent;
/**
* @param site
* @param path
* @param userId
* @param status
* @param itemType
* @param itemId
* @param startPublishDate
* @param endPublishDate
* @param orderField
* @param orderType
* @param request
* @param model
* @return view name
*/
@RequestMapping("export")
// csrf验证
@Csrf
// 日期字段自动组装需要声明格式
public ExcelView export(@RequestAttribute SysSite site, String path, Long userId, Integer[] status, String itemType,
Long itemId, @DateTimeFormat(pattern = "yyyy-MM-dd") Date startPublishDate,
@DateTimeFormat(pattern = "yyyy-MM-dd") Date endPublishDate, String orderField, String orderType,
HttpServletRequest request, ModelMap model) {
if (CommonUtils.notEmpty(path)) {
path = path.replace("//", CommonConstants.SEPARATOR);
}
// 从请求中获取国际化信息，也可以根据用户、站点设置等获取
Locale locale = RequestContextUtils.getLocale(request);
String filePath = siteComponent.getWebTemplateFilePath(site, TemplateComponent.INCLUDE_DIRECTORY + path);
CmsPlaceMetadata metadata = metadataComponent.getPlaceMetadata(filePath);
// 数据查询
PageHandler page = service.getPage(site.getId(), userId, path, itemType, itemId, startPublishDate, endPublishDate,
CommonUtils.getMinuteDate(), status, false, orderField, orderType, 1, PageHandler.MAX_PAGE_SIZE);
@SuppressWarnings("unchecked")
List<CmsPlace> entityList = (List<CmsPlace>) page.getList();
// 相关数据主键组装
Map<String, List<Serializable>> pksMap = new HashMap<>();
for (CmsPlace entity : entityList) {
List<Serializable> userIds = pksMap.computeIfAbsent("userIds", k -> new ArrayList<>());
List<Serializable> ids = pksMap.computeIfAbsent("ids", k -> new ArrayList<>());
userIds.add(entity.getUserId());
userIds.add(entity.getCheckUserId());
ids.add(entity.getId());
}
// 相关数据查询，与组装，减少数据库查询次数
Map<Serializable, SysUser> userMap = new HashMap<>();
Map<Serializable, CmsPlaceAttribute> attributeMap = new HashMap<>();
if (null != pksMap.get("userIds")) {
List<Serializable> userIds = pksMap.get("userIds");
List<SysUser> entitys = sysUserService.getEntitys(userIds.toArray(new Serializable[userIds.size()]));
for (SysUser entity : entitys) {
userMap.put(entity.getId(), entity);
}
List<Serializable> ids = pksMap.get("ids");
List<CmsPlaceAttribute> attributes = attributeService.getEntitys(ids.toArray(new Serializable[ids.size()]));
for (CmsPlaceAttribute attribute : attributes) {
attributeMap.put(attribute.getPlaceId(), attribute);
}
}
ExcelView view = new ExcelView(workbook -> {
Sheet sheet = workbook
.createSheet(LanguagesUtils.getMessage(CommonConstants.applicationContext, locale, "page.content"));
int i = 0, j = 0;
// 创建表头行，增加字段
Row row = sheet.createRow(i++);
row.createCell(j++).setCellValue(LanguagesUtils.getMessage(CommonConstants.applicationContext, locale, "page.id"));
row.createCell(j++).setCellValue(LanguagesUtils.getMessage(CommonConstants.applicationContext, locale, "page.title"));
row.createCell(j++).setCellValue(LanguagesUtils.getMessage(CommonConstants.applicationContext, locale, "page.url"));
row.createCell(j++).setCellValue(
LanguagesUtils.getMessage(CommonConstants.applicationContext, locale, "page.content.promulgator"));
row.createCell(j++)
.setCellValue(LanguagesUtils.getMessage(CommonConstants.applicationContext, locale, "page.clicks"));
row.createCell(j++)
.setCellValue(LanguagesUtils.getMessage(CommonConstants.applicationContext, locale, "page.publish_date"));
row.createCell(j++)
.setCellValue(LanguagesUtils.getMessage(CommonConstants.applicationContext, locale, "page.create_date"));
row.createCell(j++)
.setCellValue(LanguagesUtils.getMessage(CommonConstants.applicationContext, locale, "page.status"));
row.createCell(j++)
.setCellValue(LanguagesUtils.getMessage(CommonConstants.applicationContext, locale, "page.inspector"));
// 增加扩展数据的字段列
if (CommonUtils.notEmpty(metadata.getExtendList())) {
for (SysExtendField extend : metadata.getExtendList()) {
row.createCell(j++).setCellValue(extend.getName());
}
}
SysUser user;
CmsPlaceAttribute attribute;
DateFormat dateFormat = DateFormatUtils.getDateFormat(DateFormatUtils.FULL_DATE_FORMAT_STRING);
// 遍历数据 逐行写入数据
for (CmsPlace entity : entityList) {
row = sheet.createRow(i++);
j = 0;
row.createCell(j++).setCellValue(entity.getId().toString());
row.createCell(j++).setCellValue(entity.getTitle());
row.createCell(j++).setCellValue(entity.getUrl());
user = userMap.get(entity.getUserId());
row.createCell(j++).setCellValue(null == user ? null : user.getNickName());
row.createCell(j++).setCellValue(String.valueOf(entity.getClicks()));
row.createCell(j++).setCellValue(dateFormat.format(entity.getPublishDate()));
row.createCell(j++).setCellValue(dateFormat.format(entity.getCreateDate()));
row.createCell(j++).setCellValue(LanguagesUtils.getMessage(CommonConstants.applicationContext, locale,
"page.status.place.data." + entity.getStatus()));
// 相关数据的解包与使用
user = userMap.get(entity.getCheckUserId());
row.createCell(j++).setCellValue(null == user ? null : user.getNickName());
// 扩展数据遍历写入
if (CommonUtils.notEmpty(metadata.getExtendList())) {
attribute = attributeMap.get(entity.getId());
Map<String, String> map = ExtendUtils.getExtendMap(null == attribute ? null : attribute.getData());
for (SysExtendField extend : metadata.getExtendList()) {
row.createCell(j++).setCellValue(map.get(extend.getId().getCode()));
}
}
}
});
DateFormat dateFormat = DateFormatUtils.getDateFormat(DateFormatUtils.SHORT_DATE_FORMAT_STRING);
// 设置文件名
view.setFilename(metadataComponent.getPlaceMetadata(filepath).getAlias()+"_"+dateFormat.format(new Date()));
return view;
}
}
保存页面
更多文档
数据结构
0条评论
发表评论