数据导出功能开发

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;
    }

}