多个Sheet根据不同条件对应字段导出

最新推荐文章于 2023-01-12 05:38:59 发布

#朱松涛

最新推荐文章于 2023-01-12 05:38:59 发布

阅读量182

点赞数

分类专栏：导出文章标签： java

本文链接：https://blog.csdn.net/qq_42860724/article/details/118891782

版权

导出专栏收录该内容

1 篇文章 0 订阅

订阅专栏

----------------------------------------------Controller

/**
 * @param id 产品id
 * @description 母产品详情导出
 * @return: R
 * @author zhusongtao
 * @date 2021/04/8 10：13
 */
@ApiOperation("母产品详情导出")
@PostMapping("/exportAllyScoreCount/{id}")
public R exportAllyScoreCount(HttpServletRequest request, HttpServletResponse response ,
                              @PathVariable(value = "id") @ApiParam(name = "id", value = "产品id") Long id){
    return R.ok().data(productService.exportAllyScoreCount(request,response,id));
}

-----------------------------------------------Service

/**
 * @param id 产品id
 * @description 母产品详情导出
 * @return: R
 * @author zhusongtao
 * @date 2021/04/8 10：13
 */
R exportAllyScoreCount(HttpServletRequest request, HttpServletResponse response, long id);

-----------------------------------------------ServiceImpl

    private static final String S1 = "1";
    private static final String S2 = "2";
    private static final String S3 = "3";
    
/**
 * @param id 产品id
 * @description 母产品详情导出
 * @return: R
 * @author zhusongtao
 * @date 2021/04/8 10：13
 */
@Override
public R exportAllyScoreCount(HttpServletRequest request, HttpServletResponse response, long id) {
    if (ObjectUtil.isNull(id)) {
        throw new BusinessException("err : The parameter(id) is null");
    }
    // 封装详情数据
    Product product = copyEncapsulation(id);
    if (ObjectUtil.isNull(product)) {
        throw new BusinessException("未查询到'" + id + "'数据");
    }
    //文件格式
    String reportDate = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
    String fileName = System.currentTimeMillis() + "-" + reportDate + ".xlsx";
    try {
        response.setHeader("Content-Disposition", "attachment; filename=".concat(fileName + ".xlsx"));
        response.setHeader("Connection", "close");
        response.setHeader("Content-Type", "application/json;charset=UTF-8");
        OutputStream ouputStream = response.getOutputStream();

        //合作机构
        String[] headers1 = {"托管人", "基金管理人", "投资机构", "底层顾问", "投资顾问机构"};
        //产品费率
        String[] headers2 = {"浮动管理费%", "托管费%", "运营服务费%", "固定管理费%", "固定代销服务费%", "追赶浮动代销服务费%", "浮动代销服务费%", "其他%",
                "认购费扣除方式%", "固定管理费分成比例%", "浮动管理费分成比例%", "追赶浮动代销服务费分成比例", "浮动代销服务费分成比例", "认购/申购折扣率"};
        //资金交收要素
        String[] headers5 = {"认购确认日", "认购交收日", "认购交收方式", "认购资金交收路径", "申购确认日", "申购交收日", "申购交收方式", "申购资金交收路径", "赎回确认日",
                "赎回交收日", "赎回资金交收路径", "分红确认日", "分红交收日", "分红资金交收路径"};
        String[] headers6 = {"金额区间(万元)", "计费策略", "最低金额：元", "最高金额：元", "费率(%)"};
        String[] headers7 = {"持有时间(天)", "计费策略", "最低金额：元", "最高金额：元", "费率(%)"};
        String[] headers8 = {"是否热销", "是否优选", "产品点评", "产品特点", "推荐视频", "基金经理"};
        //list转化为Excel对象
        ExportExcelUtils eeu = new ExportExcelUtils();
        XSSFWorkbook workbook = new XSSFWorkbook();
        eeu.exportExcel(workbook, 0, "产品基本要素", headers0(product), data0(product), ouputStream);
        eeu.exportExcel(workbook, 1, "合作机构", headers1, data1(product), ouputStream);
        eeu.exportExcel(workbook, 2, "产品费率", headers2, data2(product), ouputStream);
        eeu.exportExcel(workbook, 3, "买入卖出规则", headers3(product), data3(product), ouputStream);
        eeu.exportExcel(workbook, 4, "账户信息", headers4(product), data4(product), ouputStream);
        eeu.exportExcel(workbook, 5, "资金交收要素", headers5, data5(product), ouputStream);
        eeu.exportExcel(workbook, 6, "认购费设置", headers6, data6(product), ouputStream);
        eeu.exportExcel(workbook, 7, "赎回费设置", headers7, data7(product), ouputStream);
        eeu.exportExcel(workbook, 8, "线上营销", headers8, data8(product), ouputStream);
        //再关闭输入流。
        workbook.write(ouputStream);
        ouputStream.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return R.ok();
}

//根据不同的条件检索对应字段进行封装--------拿headers0做案例

 /**
 * @param product product
 * @description 封装产品基本要素sheet
 * @return: R
 * @author zhusongtao
 * @date 2021/04/8 10：13
 */
private String[] headers0(Product product) {

    String[] headers0 = new String[]{};
    if (null != product.getInterfaceType() && !"".equals(product.getInterfaceType())) {
        //数据转流方式 1.进入代销系统,2.进入TA系统,3.使用Excel导入
        if (S1.equals(product.getInterfaceType())) {
            //产品基本要素
            headers0 = new String[]{"产品代码", "公司主体", "产品名称", "销售方式", "产品备案编号", "产品大类", "产品形态", "产品经理", "产品经理对应部门", "产品缩写",
                    "募集开始时间", "募集结束时间", "是否自主发行", "风险等级", "产品期限", "产品实际期限(年)", "产品状态", "产品审批状态", "双录设置", "是否支持线上签约",
                    "数据流转方式", "币种", "人数", "基金代码", "基金名称", "净值类型", "展示净值参考指标", "净值参考指标", "投资范围", "缴款方式", "门槛收益",
                    "业绩报酬计提基准", "产品描述", "适用业务日历", "产品成立日", "纸质合同号份数", "投资方向", "是否需要回访","回访节点", "是否允许转让"};
        } else if (S2.equals(product.getInterfaceType())) {
            headers0 = new String[]{"产品代码", "公司主体", "产品名称", "销售方式", "产品备案编号", "产品大类", "产品形态", "产品经理", "产品经理对应部门", "产品缩写",
                    "募集开始时间", "募集结束时间", "是否自主发行", "风险等级", "产品期限", "产品实际期限(年)", "产品状态", "产品审批状态", "双录设置", "是否支持线上签约",
                    "数据流转方式", "币种", "人数", "TA代码", "净值类型", "展示净值参考指标", "净值参考指标", "投资范围", "缴款方式",
                    "门槛收益", "业绩报酬计提基准", "产品描述", "适用业务日历", "产品成立日", "纸质合同号份数", "投资方向", "是否需要回访","回访节点" ,"是否允许转让"};
        } else {
            headers0 = new String[]{"产品代码", "公司主体", "产品名称", "销售方式", "产品备案编号", "产品大类", "产品形态", "产品经理", "产品经理对应部门", "产品缩写",
                    "募集开始时间", "募集结束时间", "是否自主发行", "风险等级", "产品期限", "产品实际期限(年)", "产品状态", "产品审批状态", "双录设置", "是否支持线上签约",
                    "数据流转方式", "币种", "人数", "净值类型", "展示净值参考指标", "净值参考指标", "投资范围", "缴款方式", "门槛收益",
                    "业绩报酬计提基准", "产品描述", "适用业务日历", "产品成立日", "纸质合同号份数", "投资方向", "是否需要回访","回访节点", "是否允许转让"};
        }
    } else {
            headers0 = new String[]{"产品代码", "公司主体", "产品名称", "销售方式", "产品备案编号", "产品大类", "产品形态", "产品经理", "产品经理对应部门", "产品缩写",
                    "募集开始时间", "募集结束时间", "是否自主发行", "风险等级", "产品期限", "产品实际期限(年)", "产品状态", "产品审批状态", "双录设置", "是否支持线上签约",
                    "数据流转方式", "币种", "人数", "净值类型", "展示净值参考指标", "净值参考指标", "投资范围", "缴款方式", "门槛收益",
                    "业绩报酬计提基准", "产品描述", "适用业务日历", "产品成立日", "纸质合同号份数", "投资方向", "是否需要回访", "回访节点", "是否允许转让"};
    }
    return headers0;
}

 /**
 * @param product product
 * @description 封装产品基本要素
 * @return: R
 * @author zhusongtao
 * @date 2021/04/21 13：50
 */
private List<List<String>> data0(Product product) {
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    List<List<String>> data0 = new ArrayList<List<String>>();
    List<String> rowData0 = new ArrayList<String>();
    //产品代码
    if (product.getProductCode() != null && !"".equals(product.getProductCode())) {
        rowData0.add(product.getProductCode());
    }else {rowData0.add("");}
    //公司主体 1上海陆享 杭州陆浦
    if (product.getCompanyId() != null && !"".equals(product.getCompanyId().toString())) {
        rowData0.add(product.getCompanyId().toString().equals(S1) ? BusinessEnum.COMPANY_SHLX_1.getLabel() : BusinessEnum.COMPANY_HZLP_2.getLabel());
    }else {rowData0.add("");}
    //产品名称
    if (product.getProductName() != null && !"".equals(product.getProductName())) {
        rowData0.add(product.getProductName());
    }else {rowData0.add("");}
    //销售方式 1直销 2代销
    if (product.getSaleType() != null && !"".equals(product.getSaleType())) {
        rowData0.add(product.getSaleType().equals(S1) ? BusinessEnum.SALE_TYPE_CONSIGN_1.getLabel() : BusinessEnum.SALE_TYPE_DIRECT_2.getLabel());
    }else {rowData0.add("");}
    //产品备案编号
    if (product.getFilingNo() != null && !"".equals(product.getFilingNo())) {
        rowData0.add(product.getFilingNo());
    }else {rowData0.add("");}
    //产品大类 1二级市场,2一级市场,3地产基金
    if (product.getProductType1() != null && !"".equals(product.getProductType1())) {
        backProductType(product,rowData0);
    }else {rowData0.add("");}
    //产品形态 1私募基金,2资管计划,3有限合伙
    if (product.getProductCategory() != null && !"".equals(product.getProductCategory())) {
        backProductCategory(product,rowData0);
    }else {rowData0.add("");}
    //产品经理
    if (product.getProductManager() != null ) {
        if(ObjectUtil.isNotEmpty(product.getProductManager().getUsername())){
            rowData0.add(product.getProductManager().getUsername());
        }else {rowData0.add("");}
    }else {rowData0.add("");}
    //产品经理对应部门
    if (product.getProductManagerDept() != null && !"".equals(product.getProductManagerDept())) {
        rowData0.add(product.getProductManagerDept());
    }else {rowData0.add("");}
    //产品缩写
    if (product.getAbbreviation() != null && !"".equals(product.getAbbreviation())) {
        rowData0.add(product.getAbbreviation());
    }else {rowData0.add("");}
    //募集期开始
    if (product.getRecruitmentFrom() != null && !"".equals(product.getRecruitmentFrom().toString())) {
        rowData0.add(formatter.format(product.getRecruitmentFrom()));
    }else {rowData0.add("");}
    //募集期结束
    if (product.getRecruitmentTo() != null && !"".equals(product.getRecruitmentTo().toString())) {
        rowData0.add(formatter.format(product.getRecruitmentTo()));
    }else {rowData0.add("");}
    //是否自主发行 是/否
    if (product.getIsIndependentIssue() != null && !"".equals(product.getIsIndependentIssue())) {
        rowData0.add(product.getIsIndependentIssue().equals(S1) ? BusinessEnum.WHETHER_YES_1.getLabel() : BusinessEnum.WHETHER_NO_2.getLabel());
    }else {rowData0.add("");}
    //风险等级1.R1,2.R2,3.R3,4.R4,5.R5
    if (product.getRiskLevel() != null && !"".equals(product.getRiskLevel())) {
        backRisk(product,rowData0);
    }else {rowData0.add("");}
    //产品期限
    if (product.getPeriod() != null && !"".equals(product.getPeriod())) {
        rowData0.add(product.getPeriod());
    }else {rowData0.add("");}
    //产品实际期限（年）
    if (product.getActualPeriod() != null && !"".equals(product.getActualPeriod().toString())) {
        rowData0.add(product.getActualPeriod().toString());
    }else {rowData0.add("");}
    //产品状态 1.募集中,2.存续中,3.封闭中,4.清算中,5.已清算,6.发行失败
    if (product.getStatus() != null && !"".equals(product.getStatus())) {
        backState(product,rowData0);
    }else {rowData0.add("");}
    //产品审批状态 10.待提交,20.待审批,30.已通过,40.已驳回,50.已通过
    if (product.getApproveStatus() != null && !"".equals(product.getApproveStatus())) {
        backApproveStatus(product,rowData0);
    }else {rowData0.add("");}
    //双录设置
    if (product.getVideoType() != null && !"".equals(product.getVideoType())) {
        backVideoType(product,rowData0);
    } else {rowData0.add("");}
    //是否支持线上签约 0-否,1-是
    if (product.getContractType() != null && !"".equals(product.getContractType())) {
        rowData0.add(product.getContractType().equals(S1) ? BusinessEnum.WHETHER_YES_1.getLabel() : BusinessEnum.WHETHER_NO_2.getLabel());
    }else {rowData0.add("");}
    //数据转流方式 1.进入代销系统,2.进入TA系统,3.使用Excel导入
    if (product.getInterfaceType() != null && !"".equals(product.getInterfaceType())) {
        backInterface(product,rowData0);
    } else {rowData0.add("");}
    //币种 1 人民币  2 美元
    if (product.getCurrency() != null && !"".equals(product.getCurrency())) {
        rowData0.add(product.getCurrency().equals(S1) ? BusinessEnum.RMB_YUAN_1.getLabel() : BusinessEnum.DOLLAR_2.getLabel());
    }else {rowData0.add("");}
    //人数
    if (product.getPeopleNum() != null && !"".equals(product.getPeopleNum().toString())) {
        rowData0.add(product.getPeopleNum().toString());
    }else {rowData0.add("");}
    //根据数据转流方式导出字段
    if (ObjectUtil.isNotEmpty(product.getInterfaceType())) {
        backInterfaceType(product,rowData0);
    }else {rowData0.add("");}
    //净值类型 1.母产品,2.子产品
    if (product.getNetType() != null && !"".equals(product.getNetType())) {
        rowData0.add(product.getNetType().equals(S1) ? BusinessEnum.PRODUCT_TYPE_PRODUCT_1.getLabel() : BusinessEnum.PRODUCT_TYPE_SUB_PRODUCT_2.getLabel());
    }else {rowData0.add("");}
    //展示净值参考指标1
    if (product.getNetDisplayIndex1() != null) {
        rowData0.add(product.getNetDisplayIndex1().getNetIndex());
    }else {rowData0.add("");}
    //净值参考指标+所占权重
    if (product.getNetIndex() != null && !"".equals(product.getNetIndex().toString())) {
        backNetIndex(product,rowData0);
    }else {rowData0.add("");}
    //投资范围
    if (product.getInvestScope() != null && !"".equals(product.getInvestScope())) {
        rowData0.add(product.getInvestScope());
    }else {rowData0.add("");}
    //缴款方式 1.一次性缴纳,2.分次缴纳
    if (product.getPayType() != null && !"".equals(product.getPayType())) {
        rowData0.add(product.getPayType().equals(S1) ? BusinessEnum.ONE_TIME_PAYMENT_1.getLabel() : BusinessEnum.PAYMENT_BY_INSTALLMENTS_2.getLabel());
    }else {rowData0.add("");}
    //门槛收益
    if (product.getIncomeThreshold() != null && !"".equals(product.getIncomeThreshold())) {
        rowData0.add(product.getIncomeThreshold());
    }else {rowData0.add("");}
    //业绩报酬计提基准
    if (product.getAchievementStandard() != null && !"".equals(product.getAchievementStandard())) {
        rowData0.add(product.getAchievementStandard());
    }else {rowData0.add("");}
    //产品描述
    if (product.getDescription() != null && !"".equals(product.getDescription())) {
        rowData0.add(product.getDescription());
    }else {rowData0.add("");}
    //适用业务日历 1境内工作日历 2香港交易日历 3境内交易日里 4香港工作日历 5自然日历
    if (product.getCalendar() != null && !"".equals(product.getCalendar())) {
        bacCalendark(product,rowData0);
    }else {rowData0.add("");}
    //产品成立日
    if (product.getProductFundDate() != null && !"".equals(product.getProductFundDate().toString())) {
        rowData0.add(formatter.format(product.getProductFundDate()));
    }else {rowData0.add("");}
    //纸质合同号份数
    if (product.getContractNoCopies() != null && !"".equals(product.getContractNoCopies().toString())) {
        rowData0.add(product.getContractNoCopies().toString());
    }else {rowData0.add("");}
    //投资方向
    if (product.getInvestmentOrientation() != null && !"".equals(product.getInvestmentOrientation())) {
        rowData0.add(product.getInvestmentOrientation());
    }else {rowData0.add("");}
    //是否需要回访 0.否, 1.是
    if (product.getWhetherReturnVisit() != null && !"".equals(product.getWhetherReturnVisit())) {
        rowData0.add(product.getWhetherReturnVisit() == "1" ? "是":"否");
    }else {rowData0.add("");}
    //回访节点 1.封账后, 2.成立后 “是否需要回访”选择“是”,该字段显示
    if (product.getReturnVisitNode() != null && !"".equals(product.getReturnVisitNode())) {
        rowData0.add(product.getReturnVisitNode() == "1" ? "封账后":"成立后");
    }else {rowData0.add("");}
    //是否允许转让 0.否, 1.是
    if (product.getWhetherAssignment() != null && !"".equals(product.getWhetherAssignment())) {
        rowData0.add(product.getWhetherAssignment() == "1" ? "是":"否");
    }else {rowData0.add("");}
    data0.add(rowData0);
    return data0;
}

/**
 * @param product product,rowData0
 * @description  产品状态 1.募集中,2.存续中,3.封闭中,4.清算中,5.已清算,6.发行失败
 * @return: R
 * @author zhusongtao
 * @date 2021/04/29 13：50
 */
private void backState(Product product,List<String> rowData0){
    switch (product.getStatus()) {
        case "1":
            rowData0.add(BusinessEnum.PRODUCT_COLLECTION_1.getLabel());
            break;
        case "2":
            rowData0.add(BusinessEnum.PRODUCT_IN_EXISTENCE_2.getLabel());
            break;
        case "3":
            rowData0.add(BusinessEnum.PRODUCT_CLOSED_3.getLabel());
            break;
        case "4":
            rowData0.add(BusinessEnum.PRODUCT_IN_LIQUIDATION_4.getLabel());
            break;
        case "5":
            rowData0.add(BusinessEnum.PRODUCT_CLEARED_5.getLabel());
            break;
        case "6":
            rowData0.add(BusinessEnum.PRODUCT_THE_ISSUE_FAILED_6.getLabel());
            break;
        default:
            rowData0.add(product.getStatus());
            break;
    }
}

---------------------------------------------------------ExportExcelUtils

package com.lupu.common.util;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

import java.io.OutputStream;
import java.util.List;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.*;


/**
 * description:这个主要的工作是将list转化为Excel对象
 * @author BlingSun
 * @version 1.0 2018/10/31 16:00 by BlingSun  创建
 **/
public class ExportExcelUtils {
    /**
     *  生成excel工作表格
     *  格式为
     *
     *  heads[0]                    heads[1]                heads[2]                heads[3]                heads[4]    ...
     *  data[0].fieldNames[0]       data[0].fieldNames[1]   data[0].fieldNames[2]   data[0].fieldNames[3]   data[0].fieldNames[4]   ....
     *  data[1].fieldNames[0]       data[1].fieldNames[1]   data[1].fieldNames[2]   data[1].fieldNames[3]   data[1].fieldNames[4]   ....
     *  ......
     *
     * @param data 这个需要装的数据
     * @param fieldNames 这个指对应的的数据对象的字段名
     * @param heads excel的表头
     * @param <T>  插入表格数据的值
     * @return 完整的Excel表格
     */
    static public<T>  Workbook createWorkbook(List<T> data, String[] fieldNames, String[] heads){

        //创建一个excel表
        Workbook workbook = new SXSSFWorkbook(100);
        Sheet sheet = workbook.createSheet();

        //创建表头
        Row row = sheet.createRow(0);
        for (int cn = 0; cn < heads.length; cn++) {
            Cell cell = row.createCell(cn);
            cell.setCellValue(heads[cn]);
        }

        //这个用表标注当前是第几行
        int line=1;
        //添加数据
        for(T item:data){
            //创建一行的row数据
            row=sheet.createRow(line++);
            //填充row
            for(int i=0;i<fieldNames.length;i++){
                Cell cell = row.createCell(i);
                cell.setCellValue(TagUtil.getFieldsByName(item,fieldNames[i])+"");
            }
        }


        return workbook;

    }

    /**
     * @Title: exportExcel
     * @Description: 导出Excel的方法
     * @author: evan @ 2014-01-09
     * @param workbook
     * @param sheetNum (sheet的位置，0表示第一个表格中的第一个sheet)
     * @param sheetTitle  （sheet的名称）
     * @param headers    （表格的标题）
     * @param result   （表格的数据）
     * @param out  （输出流）
     * @throws Exception
     */
    public void exportExcel(XSSFWorkbook workbook, int sheetNum,
                            String sheetTitle, String[] headers, List<List<String>> result,
                            OutputStream out) throws Exception {
        for(int i=0; i<9 ;i++){

        }
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle);

        // 设置表格默认列宽度为20个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成一个样式
        XSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor((short) 13);// 设置背景色
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
//        style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
//        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        XSSFFont font = workbook.createFont();

        font.setFontHeightInPoints((short) 12);
        font.setFontName("黑体"); // 字体

        // 把字体应用到当前的样式
        style.setFont(font);

        // 指定当单元格内容显示不下时自动换行
        style.setWrapText(true);

        // 产生表格标题行
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell((short) i);

            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text.toString());
        }
        // 遍历集合数据，产生数据行
        if (result != null) {
            int index = 1;
            for (List<String> m : result) {
                row = sheet.createRow(index);
                int cellIndex = 0;
                for (String str : m) {
                    XSSFCell cell = row.createCell((short) cellIndex);
                    cell.setCellValue(str);
                    cellIndex++;
                }
                index++;
            }
        }
    }

}

#朱松涛

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
多个Sheet根据不同条件对应字段导出

----------------------------------------------Controller/** * @param id 产品id * @description 母产品详情导出 * @return: R * @author zhusongtao * @date 2021/04/8 10：13 */@ApiOperation("母产品详情导出")@PostMapping("/exportAllyScoreCount/{id}")public R exportAllyS
复制链接

扫一扫

专栏目录