Controller层
@ApiOperation(value = "限额配置-导出Excel")
@GetMapping("/exportExcel")
public byte[] exportExcel(@ApiParam(value = "用户iv-user Code", required = true) @RequestHeader(value = "iv-user") String userCode,
@RequestParam @ApiParam(required = true, name = "targetId", value = "流程编号")String targetId,
@RequestParam @ApiParam(required = true, name = "startPeriodId", value = "开始日期") Integer startPeriodId,
@RequestParam @ApiParam(required = true, name = "endPeriodId", value = "结束日期") Integer endPeriodId,
HttpServletResponse response) throws IOException {
log.info("配置部门规划-净资本预算表-导出Excel | DeptConfigExcelController.exportExcel | 任务开始,流程编号：targetId=={}, 当前操作人：userCode==={}", targetId, userCode);
HSSFWorkbook hwb = new HSSFWorkbook();
InputStream input = null;
try {
//根据用户code：userCode，获取该用户所属部门
FlowUser userInfo = deptConfigService.getUserInfo(userCode);
String depName = userInfo.getDepartName();
log.info("配置部门规划-净资本预算表-导出Excel | 开始解析Excel, 流程编号：targetId=={}, 当前操作人所属部门：depName==={}", targetId, depName);
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
OutputStream outputStream = response.getOutputStream();
//业务及资本规划表: ERMP_DATA.CAPTIAL_DEP_PROJ_BUSINESS
// 根据流程编码：targetId 和 部门名称：depName 查库里是否有数据，有，就导出库里；无，导出项目中的excel文件
if(mapper.findProjCount("CAPTIAL_DEP_PROJ_BUSINESS", targetId, depName) > 0){
exportExcelService.exportExcel(depName, targetId, startPeriodId, endPeriodId, hwb);
hwb.write(outputStream);
}else{
//fileName：项目中的文件名称
Resource resource = new ClassPathResource("file/" + fileName + ".xlsx");
input = resource.getInputStream();
Workbook wb = WorkbookFactory.create(input);
exportExcelService.exportExcelTemplate(depName, startPeriodId, endPeriodId, wb);
wb.write(outputStream);
}
outputStream.close();
} catch (Exception e) {
log.error("DeptConfigExcelController exportExcel fail.",e);
throw new IOException("限额配置-净资本预算表-导出Excel | 导入Excel出现异常，异常信息：" + e.getMessage());
} finally {
if (input != null) {
IOUtils.closeQuietly(input);
}
}
return hwb.getBytes();
}
Service层
package com.hs.report.service.impl.capitalallocation;
import com.hs.report.enums.BusinessExcelEnum;
import com.hs.report.mapper.ermp.CaptialDepProjMapper;
import com.hs.report.oracle.dao.PeriodDimDao;
import com.hs.report.service.capitalallocation.DeptConfigExportExcelService;
import com.hs.report.view.capitalallocation.CaptialDepProjBusinessVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 描述：净资本预算表 导出
*
* @version 1.0
* @Author k0411242
* @Date 2021年11月23日
*/
@Slf4j
@Service
public class DeptConfigExportExcelServiceImpl implements DeptConfigExportExcelService {
private static final String TABLE_TITLE = "tableTitle";
private static final String BG_TABLE_CELL = "BGTableCell";
private static final String ALIGN_TABLE_CELL = "alignTableCell";
private static final String ALIGN_BOLD_TABLE_CELL = "alignBoldTableCell";
private static final String ALIGN_LEFT_TABLE_CELL = "alignLeftTableCell";
private static final String COLOR_YELLOW_TABLE_CELL = "colorYellowCell";
//Excel 需要标黄的单元格
private static final List<Integer> YELLOWCOLORS = Arrays.asList(8,13,26,35,47,54,59,63,64,65,69,76,80,83,84,90,96,100);
//Excel 需要体积加粗的单元格
private static final List<Integer> BOLDS = Arrays.asList(63,80,83,96,99,100);
@Autowired
private PeriodDimDao periodDimDao;
@Autowired
private CaptialDepProjMapper mapper;
@Override
public void exportExcel(String depName, String targetId, Integer startPeriodId, Integer endPeriodId, HSSFWorkbook wb) {
log.info("配置部门规划-净资本预算表-导出Excel | 任务开始,流程编号：targetId=={}, 部门名称：depName==={}", targetId, depName);
//定义报表数据中的行数
int rownum = 0;
//根据开始日期：startPeriodId 和结束日期：endPeriodId 获取月的个数以及所有月份
int periodCount = periodDimDao.queryYearMonthCount(startPeriodId, endPeriodId);
List<Integer> periodList = periodDimDao.queryYearMonthList(startPeriodId, endPeriodId);
//定义总列数
int columnNumber = 5 + periodCount*2;
//给每一列设置大小值
int[] columnWidth = {50,19,19,19,19,19,19,10,10,10,75};
HSSFSheet sheet = wb.createSheet(depName);
for(int i = 0; i < columnNumber; i++){
sheet.setColumnWidth(i,columnWidth[i] * 256); // 单独设置每列的宽
}
//存储所有的样式
Map<String, HSSFCellStyle> cellStyleMap = getCellStyleMap(wb);
//第一部分：1-6行，需要设置自动计算
int oneRowNum = getOneTable(rownum, columnNumber, depName, targetId, wb, sheet, periodList, cellStyleMap);
//第二部分 第7行表头
int twoRowNum = getTwoTable(oneRowNum, columnNumber, depName, targetId, wb, sheet, periodList, cellStyleMap);
//第三部分 第8行表头
int threeRowNum = getThreeTable(twoRowNum, columnNumber, depName, targetId, wb, sheet, periodList, cellStyleMap);
//第四部分 第9行开始读取库里数据
getFourTable(threeRowNum, columnNumber, depName, targetId, wb, sheet, periodList, cellStyleMap);
//设置第8行冻结
sheet.createFreezePane(11,8);
//单元格添加 函数 有效
for(BusinessExcelEnum item : BusinessExcelEnum.values()) {
HSSFRow row = sheet.getRow(item.getRow());
HSSFCell cellS = row.getCell(item.getCell());
//取得公式单元格的公式,重新设置
cellS.setCellFormula(item.getFunction());
}
//强制让Excel Sheet 执行公式
wb.setForceFormulaRecalculation(true);
}
private void getFourTable(int rownum, int columnNumber, String depName, String targetId, HSSFWorkbook wb, HSSFSheet sheet,
List<Integer> periodList, Map<String, HSSFCellStyle> styleMap) {
//定义报表数据中的行数
rownum = rownum + 1;
HSSFRow rowSubTitle;
//根据流程编码：targetId 和 部门名称：depName 查库里数据
List<CaptialDepProjBusinessVO> projVOList = mapper.queryProjVOData(targetId, depName);
for(int i = 0; i < projVOList.size(); i++){
CaptialDepProjBusinessVO voData = projVOList.get(i);
rowSubTitle = sheet.createRow(i + rownum);
HSSFCell datacell = null;
//9-63行没有合并单元格，之后有合并单元格
//voData.getPx() 在 8-62之间没有单元格，大于62合并单元格
if(voData.getPx() >= 8 && voData.getPx() <= 62){
for (int j = 0; j < columnNumber; j++) {
//第9行设置函数以及月份设置底色
if(voData.getPx() == 8 || voData.getPx() == 26){
//字体加粗
if(j == 0){
datacell = rowSubTitle.createCell(j);
//设置单元格样式：字体加粗 靠左对齐
datacell.setCellStyle(styleMap.get(BG_TABLE_CELL));
datacell.setCellValue(getAllCellValue(voData, j));
}
//月份单元格设置
if(j != 0 && j <= periodList.size()*2){
datacell = rowSubTitle.createCell(j);
//设置单元格样式：添加底色 居中
datacell.setCellStyle(styleMap.get(COLOR_YELLOW_TABLE_CELL));
datacell.setCellValue(getAllCellValue(voData, j));
}
if(j > periodList.size()*2){
datacell = rowSubTitle.createCell(j);
if(j == columnNumber-1){
//设置单元格样式：字体非加粗 靠左对齐
datacell.setCellStyle(styleMap.get(ALIGN_LEFT_TABLE_CELL));
}else{
//设置单元格样式：字体非加粗 居中
datacell.setCellStyle(styleMap.get(ALIGN_BOLD_TABLE_CELL));
}
datacell.setCellValue(getAllCellValue(voData, j));
}
}else{
datacell = rowSubTitle.createCell(j);
if(j == 0 || j == columnNumber-1){
//设置单元格样式：字体非加粗 靠左对齐
datacell.setCellStyle(styleMap.get(ALIGN_LEFT_TABLE_CELL));
}else{
//设置单元格样式：字体非加粗 居中
datacell.setCellStyle(styleMap.get(ALIGN_BOLD_TABLE_CELL));
}
if(YELLOWCOLORS.contains(voData.getPx()) && j != 0 && j <= periodList.size()*2){
//设置单元格样式：添加底色 居中
datacell.setCellStyle(styleMap.get(COLOR_YELLOW_TABLE_CELL));
}
if(j > 0 && j <= periodList.size()*2){
//函数 计算 需要返回数组类型
datacell.setCellValue(getDoubleCellValue(voData, j));
}else{
datacell.setCellValue(getAllCellValue(voData, j));
}
}
}
}else{
//63行开始，月份部分有合并单元格
for (int j = 0; j < columnNumber; j++) {
//合并单元格
if(j % 2 != 0 && j <= periodList.size()*2){
datacell = rowSubTitle.createCell(j);
//函数 计算 需要返回数组类型
datacell.setCellValue(getDoubleCellValue(voData, j));
if(YELLOWCOLORS.contains(voData.getPx()) && j <= periodList.size() * 2){
//设置单元格样式：添加底色 居中
datacell.setCellStyle(styleMap.get(COLOR_YELLOW_TABLE_CELL));
}else{
//设置单元格样式：字体非加粗 居中
datacell.setCellStyle(styleMap.get(ALIGN_BOLD_TABLE_CELL));
}
CellRangeAddress region = new CellRangeAddress(i + rownum,i + rownum,j,j + 1);
sheet.addMergedRegion(region);
setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, wb); //给合并过的单元格加边框
}else{
datacell = rowSubTitle.createCell(j);
if(j == 0){
if(BOLDS.contains(voData.getPx())){
//设置单元格样式：字体加粗 靠左对齐
datacell.setCellStyle(styleMap.get(BG_TABLE_CELL));
}else{
//设置单元格样式：字体非加粗 靠左对齐
datacell.setCellStyle(styleMap.get(ALIGN_LEFT_TABLE_CELL));
}
}else if(j == columnNumber-1){
//设置单元格样式：字体非加粗 靠左对齐
datacell.setCellStyle(styleMap.get(ALIGN_LEFT_TABLE_CELL));
}else{
//设置单元格样式：字体非加粗 居中
datacell.setCellStyle(styleMap.get(ALIGN_BOLD_TABLE_CELL));
}
datacell.setCellValue(getAllCellValue(voData, j));
}
}
}
}
}
private double getDoubleCellValue(CaptialDepProjBusinessVO voData, int cellIndex) {
switch(cellIndex){
case 1:
return getDoubleValue(voData.getVal1());
case 2:
return getDoubleValue(voData.getVal2());
case 3:
return getDoubleValue(voData.getVal3());
case 4:
return getDoubleValue(voData.getVal4());
case 5:
return getDoubleValue(voData.getVal5());
case 6:
return getDoubleValue(voData.getVal6());
default:
return 0.00;
}
}
private double getDoubleValue(String val) {
return StringUtils.isNotEmpty(val) ? Double.parseDouble(val) : 0.00;
}
private String getAllCellValue(CaptialDepProjBusinessVO voData, int cellIndex) {
switch(cellIndex){
case 0:
return voData.getProjName();
case 1:
return voData.getVal1();
case 2:
return voData.getVal2();
case 3:
return voData.getVal3();
case 4:
return voData.getVal4();
case 5:
return voData.getVal5();
case 6:
return voData.getVal6();
case 7:
return voData.getCoefficient();
case 8:
return voData.getStandard();
case 9:
return voData.getTakeUp();
case 10:
return voData.getRemark();
default:
return "";
}
}
private int getThreeTable(int rownum, int columnNumber, String depName, String targetId, HSSFWorkbook wb, HSSFSheet sheet,
List<Integer> periodList, Map<String, HSSFCellStyle> styleMap) {
//定义报表数据中的行数
rownum = rownum + 1;
// 创建表头单元格样式 以及表头的字体样式
//第8行表头
HSSFRow rowSubTitle = sheet.createRow(rownum);
String[] subTitle = {"1. 市场风险资本准备", "未对冲", "已对冲", "未对冲", "已对冲", "未对冲", "已对冲", "", "\\", "", ""};
// 创建表头的列
for (int i = 0; i < columnNumber; i++) {
HSSFCell cellRiskTitle = rowSubTitle.createCell(i);
cellRiskTitle.setCellValue(subTitle[i]);
if(i == 0){
//设置单元格样式：字体加粗 靠左对齐
cellRiskTitle.setCellStyle(styleMap.get(BG_TABLE_CELL));
}else{
cellRiskTitle.setCellStyle(styleMap.get(ALIGN_TABLE_CELL));
}
}
return rownum;
}
private int getTwoTable(int rownum, int columnNumber, String depName, String targetId, HSSFWorkbook wb, HSSFSheet sheet,
List<Integer> periodList, Map<String, HSSFCellStyle> styleMap) {
// 创建表头单元格样式 以及表头的字体样式
//第7行表头
HSSFRow rowSubTitle = sheet.createRow(rownum);
rowSubTitle.setHeightInPoints(40);
String[] subTitle = {"业务项目（填报非标黄行）", getCellValue(String.valueOf(periodList.get(0))), "", getCellValue(String.valueOf(periodList.get(1))), "",
getCellValue(String.valueOf(periodList.get(2))), "", "净资本计算系数", "计算标准", "本期净资本占用", "备注"};
// 创建表头的列
for (int i = 0; i < columnNumber; i++) {
//合并单元格
if(i % 2 != 0 && i < periodList.size()*2){
HSSFCell cellChangeTitle = rowSubTitle.createCell(i);
cellChangeTitle.setCellValue(subTitle[i]);
cellChangeTitle.setCellStyle(styleMap.get(TABLE_TITLE));
CellRangeAddress region = new CellRangeAddress(rownum,rownum,i,i + 1);
sheet.addMergedRegion(region);
setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, wb); //给合并过的单元格加边框
}else{
HSSFCell cellRiskTitle = rowSubTitle.createCell(i);
cellRiskTitle.setCellValue(subTitle[i]);
cellRiskTitle.setCellStyle(styleMap.get(TABLE_TITLE));
}
}
return rownum;
}
private int getOneTable(int rownum, int columnNumber, String depName, String targetId, HSSFWorkbook wb, HSSFSheet sheet,
List<Integer> periodList, Map<String, HSSFCellStyle> styleMap) {
// 创建表头单元格样式 以及表头的字体样式
HSSFRow rowSubTitle = sheet.createRow(rownum);
rowSubTitle.setHeightInPoints(20);
String[] subTitle = {"业务规划结果（自动计算）", getCellValue(String.valueOf(periodList.get(0))), getCellValue(String.valueOf(periodList.get(1))),
getCellValue(String.valueOf(periodList.get(2)))};
// 创建表头的列
for (int i = 0; i < columnNumber-4; i++) {
if(i == 0){
HSSFCell cellRiskTitle = rowSubTitle.createCell(i);
cellRiskTitle.setCellValue(subTitle[i]);
cellRiskTitle.setCellStyle(styleMap.get(TABLE_TITLE));
}
//合并单元格
if(i % 2 != 0){
HSSFCell cellChangeTitle = rowSubTitle.createCell(i);
cellChangeTitle.setCellValue(subTitle[(i+1) / 2]);
cellChangeTitle.setCellStyle(styleMap.get(TABLE_TITLE));
CellRangeAddress region = new CellRangeAddress(rownum,rownum,i,i + 1);
sheet.addMergedRegion(region);
setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, wb); //给合并过的单元格加边框
}
}
//根据流程编码：targetId 和 部门名称：depName 查库里数据
List<CaptialDepProjBusinessVO> projVOList = mapper.queryProjVOTopData(targetId, depName);
//创建单元格，并设置值
rownum = rownum + 1;
for(int i = 0; i < projVOList.size(); i++){
CaptialDepProjBusinessVO voData = projVOList.get(i);
rowSubTitle = sheet.createRow(i + rownum);
HSSFCell datacell = null;
for (int j = 0; j < columnNumber-4; j++) {
if(j == 0){
datacell = rowSubTitle.createCell(j);
//设置单元格样式：字体加粗 靠左对齐
datacell.setCellStyle(styleMap.get(BG_TABLE_CELL));
datacell.setCellValue(voData.getProjName());
}
//合并单元格
if(j % 2 != 0){
datacell = rowSubTitle.createCell(j);
datacell.setCellStyle(styleMap.get(ALIGN_TABLE_CELL));
datacell.setCellValue(getAllCellValue(voData, j));
CellRangeAddress region = new CellRangeAddress(i + rownum,i + rownum,j,j + 1);
sheet.addMergedRegion(region);
setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, wb); //给合并过的单元格加边框
}
}
}
return rownum + projVOList.size();
}
//给合并后的单元格加样式
public void setBorderStyle(int border, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook wb){
RegionUtil.setBorderBottom(border, region, sheet, wb); //下边框
RegionUtil.setBorderLeft(border, region, sheet, wb); //左边框
RegionUtil.setBorderRight(border, region, sheet, wb); //右边框
RegionUtil.setBorderTop(border, region, sheet, wb); //上边框
}
private Map<String, HSSFCellStyle> getCellStyleMap(HSSFWorkbook wb){
Map<String, HSSFCellStyle> styleMap = new HashMap<>();
styleMap.put(TABLE_TITLE, createTableTitleStyle(wb));
styleMap.put(BG_TABLE_CELL, createBGTableCellStyle(wb));
styleMap.put(ALIGN_TABLE_CELL, createAlignTableCellStyle(wb));
styleMap.put(ALIGN_BOLD_TABLE_CELL, createAlignBoldTableCellStyle(wb));
styleMap.put(ALIGN_LEFT_TABLE_CELL, createAlignLeftTableCellStyle(wb));
styleMap.put(COLOR_YELLOW_TABLE_CELL, createYellowTitleStyle(wb));
return styleMap;
}
//创建表头单元格样式 以及表头的字体样式
private HSSFCellStyle createTableTitleStyle(HSSFWorkbook wb){
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.DARK_YELLOW.index);//添加背景色
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true); //设置自动换行
HSSFFont titleFont = (HSSFFont) wb.createFont();
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleFont.setFontName("宋体");
titleFont.setFontHeightInPoints((short) 9);
style.setFont(titleFont);
return style;
}
//创建数据单元格样式 以及字体样式
private HSSFCellStyle createBGTableCellStyle(HSSFWorkbook wb){
HSSFCellStyle style = wb.createCellStyle();
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true); //设置自动换行
HSSFFont titleFont = (HSSFFont) wb.createFont();
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗
titleFont.setFontName("宋体");
titleFont.setFontHeightInPoints((short) 9);
style.setFont(titleFont);
return style;
}
//创建数据单元格样式 以及字体样式 数据居中
private HSSFCellStyle createAlignTableCellStyle(HSSFWorkbook wb){
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true); //设置自动换行
//保留两位小数
HSSFDataFormat format= wb.createDataFormat();
style.setDataFormat(format.getFormat("#,##0.00"));
HSSFFont titleFont = (HSSFFont) wb.createFont();
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗
titleFont.setFontName("宋体");
titleFont.setFontHeightInPoints((short) 9);
style.setFont(titleFont);
return style;
}
//创建数据单元格样式 以及字体样式 字体不加粗居中
private HSSFCellStyle createAlignBoldTableCellStyle(HSSFWorkbook wb){
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true); //设置自动换行
//保留两位小数
HSSFDataFormat format= wb.createDataFormat();
style.setDataFormat(format.getFormat("#,##0.00"));
HSSFFont titleFont = (HSSFFont) wb.createFont();
titleFont.setFontName("宋体");
titleFont.setFontHeightInPoints((short) 9);
style.setFont(titleFont);
return style;
}
//创建单元格样式 单元格添加黄色
private HSSFCellStyle createYellowTitleStyle(HSSFWorkbook wb){
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.YELLOW.index);//添加背景色
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true); //设置自动换行
//保留两位小数
HSSFDataFormat format= wb.createDataFormat();
style.setDataFormat(format.getFormat("#,##0.00"));
HSSFFont titleFont = (HSSFFont) wb.createFont();
titleFont.setFontName("宋体");
titleFont.setFontHeightInPoints((short) 9);
style.setFont(titleFont);
return style;
}
//创建数据单元格样式 以及字体样式 字体不加粗
private HSSFCellStyle createAlignLeftTableCellStyle(HSSFWorkbook wb){
HSSFCellStyle style = wb.createCellStyle();
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true); //设置自动换行
//保留两位小数
HSSFDataFormat format= wb.createDataFormat();
style.setDataFormat(format.getFormat("#,##0.00"));
HSSFFont titleFont = (HSSFFont) wb.createFont();
titleFont.setFontName("宋体");
titleFont.setFontHeightInPoints((short) 9);
style.setFont(titleFont);
return style;
}
@Override
public void exportExcelTemplate(String depName, Integer startPeriodId, Integer endPeriodId, Workbook wb) {
log.info("限额配置-净资本预算表-导出Excel | 任务开始, 部门名称：depName==={}", depName);
List<Integer> periodList = periodDimDao.queryYearMonthList(startPeriodId, endPeriodId);
Sheet sheet = wb.getSheetAt(0);
wb.setSheetName(0,depName);
//重新设置单元格的值
Cell cell1 = sheet.getRow(0).getCell(1); //第1行,第2列
cell1.setCellValue(getCellValue(String.valueOf(periodList.get(0))));
Cell cell2 = sheet.getRow(0).getCell(3); //第1行,第4列
cell2.setCellValue(getCellValue(String.valueOf(periodList.get(1))));
Cell cell3 = sheet.getRow(0).getCell(5); //第1行,第6列
cell3.setCellValue(getCellValue(String.valueOf(periodList.get(2))));
Cell cell4 = sheet.getRow(6).getCell(1); //第7行,第2列
cell4.setCellValue(getCellValue(String.valueOf(periodList.get(0))));
Cell cell5 = sheet.getRow(6).getCell(3); //第7行,第4列
cell5.setCellValue(getCellValue(String.valueOf(periodList.get(1))));
Cell cell6 = sheet.getRow(6).getCell(5); //第7行,第6列
cell6.setCellValue(getCellValue(String.valueOf(periodList.get(2))));
}
//截取日期
private String getCellValue(String value) {
return value.substring(0,4) + "年" + getZero(value.substring(value.length()-2)) + "月底规划规模（单位：亿元）";
}
//日期如果前一位为0，去掉0
private String getZero(String sub) {
return "0".equals(sub.substring(0,1)) ? sub.substring(1) : sub;
}
}
动态获取公式
package com.hs.report.enums;
/**
* 描述：业务及资本规划表 Excel 函数
*
* @version 1.0
* @Author k0411242
* @Date 2021年11月24日
*/
public enum BusinessExcelEnum {
//1-净资本占用
JZBZY_ONE(1, 1 , "SUMPRODUCT(B10:B26*H10:H26*I10:I26)+SUMPRODUCT(C10:C26*H10:H26*0.05)+SUMPRODUCT(B28:B63*H28:H63*I28:I63)+" +
"SUMPRODUCT(C28:C63*H28:H63*0.01)+SUMPRODUCT(B67:C80*H67:H80*I67:I80)+SUMPRODUCT(B82:C83*H82:H83*I82:I83)+" +
"SUMPRODUCT(B86:C96*H86:H96*I86:I96)+SUMPRODUCT(B98:C100*H98:H100*I98:I100)"),
JZBZY_TWO(1,3,"SUMPRODUCT(D10:D26*H10:H26*I10:I26)+SUMPRODUCT(E10:E26*H10:H26*0.05)+SUMPRODUCT(D28:D63*H28:H63*I28:I63)+" +
"SUMPRODUCT(E28:E63*H28:H63*0.01)+SUMPRODUCT(D67:E80*H67:H80*I67:I80)+SUMPRODUCT(D82:E83*H82:H83*I82:I83)+" +
"SUMPRODUCT(D86:E96*H86:H96*I86:I96)+SUMPRODUCT(D98:E100*H98:H100*I98:I100)"),
JZBZY_THREE(1,5,"SUMPRODUCT(F10:F26*H10:H26*I10:I26)+SUMPRODUCT(G10:G26*H10:H26*0.05)+SUMPRODUCT(F28:F63*H28:H63*I28:I63)+" +
"SUMPRODUCT(G28:G63*H28:H63*0.01)+SUMPRODUCT(F67:G80*H67:H80*I67:I80)+SUMPRODUCT(F82:G83*H82:H83*I82:I83)+" +
"SUMPRODUCT(F86:G96*H86:H96*I86:I96)+SUMPRODUCT(F98:G100*H98:H100*I98:I100)"),
//2-自营权益类证券及其衍生品规模
ZYQY_ONE(2,1,"SUM(B10:B26)+SUM(C10:C26)*0.05"),
ZYQY_TWO(2,3,"SUM(D10:D26)+SUM(E10:E26)*0.05"),
ZYQY_THREE(2,5,"SUM(F10:F26)+SUM(G10:G26)*0.05"),
//3-自营非权益类证券及其衍生品规模
ZYFQY_ONE(3,1,"SUM(B28:B63)+SUM(C28:C63)*0.01"),
ZYFQY_TWO(3,3,"SUM(D28:D63)+SUM(E28:E63)*0.01"),
ZYFQY_THREE(3,5,"SUM(F28:F63)+SUM(G28:G63)*0.01"),
//4-融资（融券）业务规模
RZRQ_ONE(4,1,"SUM(B67:C76)"),
RZRQ_TWO(4,3,"SUM(D67:E76)"),
RZRQ_THREE(4,5,"SUM(F67:G76)"),
//5-表内外资产规模
GENERAL_ONE(5,1,"B9+C9+B27+C27+SUM(B67:C80)+SUM(B82:C83)+SUM(B86:C96)*0.3%+B102*15%+B103*10%+B104*5%"),
GENERAL_TWO(5,3,"D9+E9+D27+E27+SUM(D67:E80)+SUM(D82:E83)+SUM(D86:E96)*0.3%+D102*15%+D103*10%+D104*5%"),
GENERAL_THREE(5,5,"F9+G9+F27+G27+SUM(F67:G80)+SUM(F82:G83)+SUM(F86:G96)*0.3%+F102*15%+F103*10%+F104*5%"),
//其中：（1）权益类证券及其衍生品规模
QYL_ONE(8,1,"SUM(B10:B26)"),
QYL_TWO(8,2,"SUM(C10:C26)"),
QYL_THREE(8,3,"SUM(D10:D26)"),
QYL_FOUR(8,4,"SUM(E10:E26)"),
QYL_FIVE(8,5,"SUM(F10:F26)"),
QYL_SIX(8,6,"SUM(G10:G26)"),
//非权益类证券及其衍生品规模
FQYL_ONE(26,1,"SUM(B28:B63)"),
FQYL_TWO(26,2,"SUM(C28:C63)"),
FQYL_THREE(26,3,"SUM(D28:D63)"),
FQYL_FOUR(26,4,"SUM(E28:E63)"),
FQYL_SIX(26,6,"SUM(G28:G63)"),
FQYL_FIVE(26,5,"SUM(F28:F63)"),
//本期净资本占用 列
JZB_TEN_TEN(9,9,"G10*H10*I10+F10*H10*0.05"),
JZB_ELEVEN_TEN(10,9,"F11*H11*I11+G11*H11*0.05"),
JZB_TWELVE_TEN(11,9,"F12*H12*I12+G12*H12*0.05"),
JZB_THIRTEEN_TEN(12,9,"F13*H13*I13+G13*H13*0.05"),
JZB_FIFTEEN_TEN(14,9,"F15*H15*I15+G15*H15*0.05"),
JZB_SIXTEEN_TEN(15,9,"F16*H16*I16+G16*H16*0.05"),
JZB_SEVENTEEN_TEN(16,9,"F17*H17*I17+G17*H17*0.05"),
JZB_EIGHTEEN_TEN(17,9,"F18*H18*I18+G18*H18*0.05"),
JZB_NINETEEN_TEN(18,9,"F19*H19*I19+G19*H19*0.05"),
JZB_TWENTY_TEN(19,9,"F20*H20*I20+G20*H20*0.05"),
JZB_TWENTY_ONE_TEN(20,9,"F21*H21*I21+G21*H21*0.05"),
JZB_TWENTY_TWO_TEN(21,9,"F22*H22*I22+G22*H22*0.05"),
JZB_TWENTY_THREE_TEN(22,9,"F23*H23*I23+G23*H23*0.05"),
JZB_TWENTY_FOUR_TEN(23,9,"F24*H24*I24+G24*H24*0.05"),
JZB_TWENTY_FIVE_TEN(24,9,"F25*H25*I25+G25*H25*0.05"),
JZB_TWENTY_EIGHT_TEN(27,9,"F28*H28*I28+G28*H28*0.01"),
JZB_TWENTY_NINE_TEN(28,9,"F29*H29*I29+G29*H29*0.01"),
JZB_THIRTY_TEN(29,9,"F30*H30*I30+G30*H30*0.01"),
JZB_THIRTY_ONE_TEN(30,9,"F31*H31*I31+G31*H31*0.01"),
JZB_THIRTY_TWO_TEN(31,9,"F32*H32*I32+G32*H32*0.01"),
JZB_THIRTY_THREE_TEN(32,9,"F33*H33*I33+G33*H33*0.01"),
JZB_THIRTY_FOUR_TEN(33,9,"F34*H34*I34+G34*H34*0.01"),
JZB_THIRTY_FIVE_TEN(34,9,"F35*H35*I35+G35*H35*0.01"),
JZB_THIRTY_SIX_TEN(36,9,"F37*H37*I37+G37*H37*0.01"),
JZB_THIRTY_EIGHT_TEN(37,9,"F38*H38*I38+G38*H38*0.01"),
JZB_THIRTY_NINE_TEN(38,9,"F39*H39*I39+G39*H39*0.01"),
JZB_FORTY_TEN(39,9,"F40*H40*I40+G40*H40*0.01"),
JZB_FORTY_ONE_TEN(40,9,"F41*H41*I41+G41*H41*0.01"),
JZB_FORTY_TWO_TEN(41,9,"F42*H42*I42+G42*H42*0.01"),
JZB_FORTY_THREE_TEN(42,9,"F43*H43*I43+G43*H43*0.01"),
JZB_FORTY_FOUR_TEN(43,9,"F44*H44*I44+G44*H44*0.01"),
JZB_FORTY_FIVE_TEN(44,9,"F45*H45*I45+G45*H45*0.01"),
JZB_FORTY_SIX_TEN(45,9,"F46*H46*I46+G46*H46*0.01"),
JZB_FORTY_SEVEN_TEN(46,9,"F47*H47*I47+G47*H47*0.01"),
JZB_FORTY_NINE_TEN(48,9,"F49*H49*I49+G49*H49*0.01"),
JZB_FIFTY_TEN(49,9,"F50*H50*I50+G50*H50*0.01"),
JZB_FIFTY_ONE_TEN(50,9,"F51*H51*I51+G51*H51*0.01"),
JZB_FIFTY_TWO_TEN(51,9,"F52*H52*I52+G52*H52*0.01"),
JZB_FIFTY_THREE_TEN(52,9,"F53*H53*I53+G53*H53*0.01"),
JZB_FIFTY_FOUR_TEN(53,9,"F54*H54*I54+G54*H54*0.01"),
JZB_FIFTY_SIX_TEN(55,9,"F56*H56*I56+G56*H56*0.01"),
JZB_FIFTY_SEVEN_TEN(56,9,"F57*H57*I57+G57*H57*0.01"),
JZB_FIFTY_EIGHT_TEN(57,9,"F58*H58*I58+G58*H58*0.01"),
JZB_FIFTY_NINE_TEN(58,9,"F59*H59*I59+G59*H59*0.01"),
JZB_SIXTY_ONE_TEN(60,9,"F61*H61*I61+G61*H61*0.01"),
JZB_SIXTY_TWO_TEN(61,9,"F62*H62*I62+G62*H62*0.01"),
JZB_SIXTY_SEVEN_TEN(66,9,"F67*H67*I67"),
JZB_SIXTY_EIGHT_TEN(67,9,"F68*H68*I68"),
JZB_SIXTY_NINE_TEN(68,9,"F69*H69*I69"),
JZB_SEVENTY_ONE_TEN(70,9,"F71*H71*I71"),
JZB_SEVENTY_TWO_TEN(71,9,"F72*H72*I72"),
JZB_SEVENTY_THREE_TEN(72,9,"F73*H73*I73"),
JZB_SEVENTY_FOUR_TEN(73,9,"F74*H74*I74"),
JZB_SEVENTY_FIVE_TEN(74,9,"F75*H75*I75"),
JZB_SEVENTY_SIX_TEN(75,9,"F76*H76*I76"),
JZB_SEVENTY_EIGHT_TEN(77,9,"F78*H78*I78"),
JZB_SEVENTY_NINE_TEN(78,9,"F79*H79*I79"),
JZB_EIGHTY_TEN(79,9,"F80*H80*I80"),
JZB_EIGHTY_TWO_TEN(81,9,"F82*H82*I82"),
JZB_EIGHTY_THREE_TEN(82,9,"F83*H83*I83"),
JZB_EIGHTY_SIX_TEN(85,9,"F86*H86*I86"),
JZB_EIGHTY_SEVEN_TEN(86,9,"F87*H87*I87"),
JZB_EIGHTY_EIGHT_TEN(87,9,"F88*H88*I88"),
JZB_EIGHTY_NINE_TEN(88,9,"F89*H89*I89"),
JZB_NINETY_TEN(89,9,"F90*H90*I90"),
JZB_NINETY_TWO_TEN(91,9,"F92*H92*I92"),
JZB_NINETY_THREE_TEN(92,9,"F93*H93*I93"),
JZB_NINETY_FOUR_TEN(93,9,"F94*H94*I94"),
JZB_NINETY_FIVE_TEN(94,9,"F95*H95*I95"),
JZB_NINETY_SIX_TEN(95,9,"F96*H96*I96"),
JZB_NINETY_EIGHT_TEN(97,9,"F98*H98*I98"),
JZB_NINETY_NINE_TEN(98,9,"F99*H99*I99"),
JZB_ONE_HUNDRED_TEN(99,9,"F100*H100*I100"),
;
private final Integer row;
private final Integer cell;
private final String function;
BusinessExcelEnum(Integer row, Integer cell, String function) {
this.row = row;
this.cell = cell;
this.function = function;
}
public Integer getRow() {
return row;
}
public Integer getCell() {
return cell;
}
public String getFunction() {
return function;
}
public static String getFunction(int row, int cell){
for(BusinessExcelEnum item : BusinessExcelEnum.values()){
if(row == item.row && cell == item.cell){
return item.getFunction();
}
}
return null;
}
}