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