简介使用apache的poi封装公共导出

首先编写工具类：

package com.echronos.psi.utils;

import com.echronos.psi.vo.KVResponseVO;
import com.google.common.collect.Maps;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 导出 XSSFSheet 工具类
 *
 * @author: nangua
 * @create: 2020-11-26 14:14
 */
public class ExcelXSSFExportUtil {
    /**
     * @param dataList
     *            待导出数据
     * @param kvResponseDTOList
     *            导出列名
     * @param sheetName
     *            sheet名
     * @param sheetNum
     *            sheet位置
     * @param workbook
     * @param <T>
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     */
    public static <T> void getSheet(List<T> dataList, List<KVResponseVO> kvResponseDTOList, String sheetName,
                                    int sheetNum, SXSSFWorkbook workbook) throws NoSuchFieldException, IllegalAccessException {

        // 1.创建工作表
        SXSSFSheet sheet = createSheet(workbook, kvResponseDTOList, sheetNum, sheetName);
        Map<Integer, CellStyle> styleMap = Maps.newHashMap();

        // 2.操作单元格;将数据列表写入excel
        if (!CollectionUtils.isEmpty(dataList)) {
            Font font = workbook.createFont();
            // 设置字体跟原来xls字体一致
            font.setFontHeightInPoints((short)10);
            font.setFontName("Arial");

            for (int j = 0; j < dataList.size(); j++) {
                SXSSFRow rowN = sheet.createRow(j + 1);
                T obj = dataList.get(j);
                int index = 0;
                SXSSFCell cell;
                for (KVResponseVO kvResponse : kvResponseDTOList) {
                    CellStyle cellStyle = styleMap.get(index);
                    if (cellStyle == null) {
                        cellStyle = workbook.createCellStyle();
                        cellStyle.setAlignment(HorizontalAlignment.CENTER);
                        styleMap.put(index, cellStyle);
                    }
                    cell = rowN.createCell(index);
                    cellStyle.setFont(font);

                    Object value = ObjectUtil.getValue(obj, kvResponse.getKey());
                    if (value == null) {
                        cell.setCellValue("");
                    } else if (value instanceof BigDecimal) {
                        BigDecimal bigDecimal = (BigDecimal)value;
                        if (kvResponse.getValue().contains("率") || kvResponse.getValue().contains("比例")||kvResponse.getValue().contains("(%)")) {
                            cell.setCellValue(
                                bigDecimal.divide(BigDecimal.valueOf(100),4, BigDecimal.ROUND_DOWN).doubleValue());
                            DataFormat df = workbook.createDataFormat();
                            cellStyle.setDataFormat(df.getFormat("0.00%"));
                        } else {
                            cell.setCellValue(bigDecimal.doubleValue());
                            DataFormat df = workbook.createDataFormat();
                            cellStyle.setDataFormat(df.getFormat("0.00"));
                        }
                    } else if (value instanceof Date) {
                        cell.setCellValue((Date)value);
                        DataFormat format = workbook.createDataFormat();
                        cellStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
                    } else {
                        cell.setCellValue(String.valueOf(value));
                        cellStyle.setDataFormat((short)0);
                    }
                    cell.setCellStyle(cellStyle);
                    index++;
                }
            }
        }
    }

    /**
     * 创建工作表
     *
     * @param workbook
     * @param kvResponseDTOList
     * @param sheetName
     * @return
     */
    private static SXSSFSheet createSheet(SXSSFWorkbook workbook, List<KVResponseVO> kvResponseDTOList, int sheetNum,
                                          String sheetName) {
        // 2.创建工作表
        SXSSFSheet sheet = workbook.createSheet(sheetName);
        workbook.setSheetName(sheetNum, sheetName);

        // 设置默认列宽
        sheet.setDefaultColumnWidth(20);
        CellStyle style = workbook.createCellStyle();
        // 创建一个居中的格式
        style.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        // 粗体显示
        font.setBold(true);
        // 设置字体跟原来xls字体一致
        font.setFontHeightInPoints((short)10);
        font.setFontName("Arial");
        style.setFont(font);

        SXSSFRow row0 = sheet.createRow(0);
        int i = 0;
        for (KVResponseVO kvResponse : kvResponseDTOList) {
            SXSSFCell row0cell = row0.createCell(i);
            // 加载单元格样式
            row0cell.setCellStyle(style);
            row0cell.setCellValue(kvResponse.getValue());
            i++;
        }
        return sheet;
    }

    /**
     * 发送响应流方法
     *
     * @param response
     * @param fileName
     */
    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename="+fileName+";"+"filename*=utf-8''"+fileName+".xlsx");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

其次创建一个基础的Controller：

package com.xxx.psi.controller;


import com.xxx.commons.Result;
import com.xxx.commons.model.RequestUser;
import com.xxx.commons.utils.RequestUserUtils;
import com.xxx.psi.dto.SystemConfigDTO;
import com.xxx.psi.dto.report.BaseSystemDecimals;
import com.xxx.psi.manager.SystemConfigManager;
import com.xxx.psi.utils.CodeConstants;
import com.xxx.psi.utils.ExcelXSSFExportUtil;
import com.xxx.psi.utils.HttpResponseConstants;
import com.xxx.psi.vo.KVResponseVO;
import com.xxx.user.api.feign.IUserService;
import com.xxx.user.api.req.QueryUserReq;
import com.xxx.user.api.resp.UserInfoResp;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

/**
 * date:   2021-05-13 11:37
 */
@RestController
public class BaseController<T> {

    @Autowired
    private IUserService userService;

    @Autowired
    private SystemConfigManager systemConfigManager;

    public RequestUser getUserInfo(){
        //获取当前用户
        RequestUser requestUser = RequestUserUtils.getUser();
        QueryUserReq queryUserReq = new QueryUserReq();
        queryUserReq.setId(requestUser.getId());
        Result<UserInfoResp> result = userService.getUserById(queryUserReq);
        //处理当前登录用户昵称乱码问题
        if (HttpResponseConstants.OK.equals(result.getCode())){
            if (null != result.getData()){
                requestUser.setNickname(result.getData().getNickname());
            }
        }
        return requestUser;
    }

    public Integer currentUserName(){
        return currentUser().getMemberId();
    }

    /**
     * 导出公共方法
     * @param fileName  文件名称
     * @param list 导出集合
     * @param kvResponseList 字典表字段名
     * @param dictionaryBasic 字典表KEY名
     * @param response 响应文件流
     * @param sheetNum  SHEET 页
     */
    public void export(String fileName, List<T> list, List<KVResponseVO> kvResponseList, String dictionaryBasic,
                       HttpServletResponse response, int sheetNum) throws NoSuchFieldException, IllegalAccessException, IOException {
        SXSSFWorkbook wb = new SXSSFWorkbook();
        ExcelXSSFExportUtil.getSheet(list, kvResponseList, dictionaryBasic, sheetNum, wb);
        ExcelXSSFExportUtil.setResponseHeader(response, fileName);
        OutputStream os = response.getOutputStream();
        wb.write(os);
        wb.dispose();
        os.flush();
        os.close();
    }
}

最后使用这接口里的方法：

 /**
     * 销售报表导出
     * @return
     * @throws IllegalAccessException
     * @throws NoSuchFieldException
     * @throws IOException
     */
    @ApiOperation(value="销售报表导出")
    @PostMapping("/SalesExport")
    public Result<?> SalesExport(HttpServletResponse response) throws IllegalAccessException, NoSuchFieldException, IOException {
        //查询系统设置小数位
        BaseSystemDecimals decimals = selectSystemDecimals(CodeConstants.SYSTEM_SETTINGS + currentUser().getCompanyId());
        // 拿所有的skuId
        List<Integer> skuIds = new ArrayList<>();
        List<SalesOrPurchaseAccounts> accounts = new ArrayList<>();
        List<SalesDeliveryOrderSkuVO> allSkuIds = salesReportMapper.findAllSkuIds(currentUser().getCompanyId());
        allSkuIds.stream().filter(salesDeliveryOrderSkuVO -> skuIds.add(salesDeliveryOrderSkuVO.getSkuId())).collect(Collectors.toList());
        //获取流水详情
        List<SalesReportFlowDTO> reportFlows = new ArrayList<>();
        for (Integer skuId : skuIds) {
            SalesReportDTO salesReportDTO = new SalesReportDTO();
            salesReportDTO.setSkuId(skuId);
            salesReportDTO.setTenantId(currentUser().getCompanyId());
            List<SalesReportFlowDTO> allSalesOrdersBySkuIds = salesReportMapper.findAllSalesOrdersBySkuIds(salesReportDTO);
            reportFlows.addAll(allSalesOrdersBySkuIds);
        }
        //组装数据
        reportFlows.forEach( salesReportFlowDTO -> {
            SalesOrPurchaseAccounts salesOrPurchaseAccounts = new SalesOrPurchaseAccounts();
            salesOrPurchaseAccounts.setSkuName(salesReportFlowDTO.getSkuName());
            salesOrPurchaseAccounts.setOrderDate(DateTimeUtils.getDateFormatter(salesReportFlowDTO.getOrderDate().plusHours(8)));
            salesOrPurchaseAccounts.setCostPrice(new BigDecimal(salesReportFlowDTO.getCostPrice()).setScale(decimals.getPrice()).toString());
            salesOrPurchaseAccounts.setCustomerName(salesReportFlowDTO.getCustomerName());
            salesOrPurchaseAccounts.setAmount(new BigDecimal(salesReportFlowDTO.getAmount()).setScale(decimals.getPrice()).toString());
            salesOrPurchaseAccounts.setQuantity(new BigDecimal(salesReportFlowDTO.getQuantity()).setScale(decimals.getQuantity()).toString());
            salesOrPurchaseAccounts.setUnit(salesReportFlowDTO.getUnit());
            salesOrPurchaseAccounts.setOrderNo(salesReportFlowDTO.getOrderNo());
            accounts.add(salesOrPurchaseAccounts);
        });
        //查询字典模板（这里可以使用枚举模板也行，也可以自定义数据库模板）
        ExportDictionary dictionary = exportDictionaryService.selectByKey(ExportConstants.SALES);
        List<KVResponseVO> kvResponseList = JSONObject.parseArray(dictionary.getValue(), KVResponseVO.class);
        export(ExportConstants.SALES_FILE,accounts,kvResponseList, ExportConstants.SALES,response,0);
        return Result.build();
    }

以下是建表模型：