java导出复杂excel

最新推荐文章于 2024-03-22 15:15:33 发布
最新推荐文章于 2024-03-22 15:15:33 发布
阅读量3.2k 收藏 19
点赞数 3
分类专栏： Excel导出 文章标签： excel java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/new_guaika/article/details/114868414
版权

Java 导出复杂excel,多表格中有合并以及描述

在这里插入图片描述

实体类

package com.hs.report.entity.week;
import lombok.Builder;
import lombok.Data;
import java.io.Serializable;
@Data
@Builder
public class SnapshotReq<T> implements Serializable {
    private static final long serialVersionUID = 4403232919132430023L;
    private String type;
    private Integer offset;
	/**
	*json字符串
	**/
    private T content;

    private Integer subNo;

    private Integer periodId;

    private String flowId;
}

Controller层

package com.hs.report.controller.week;
import com.hs.report.controller.BaseController;
import com.hs.report.entity.week.SnapshotReq;
import com.hs.report.enums.DeptType;
import com.hs.report.service.week.ExportExcelForGSService;
import com.hs.report.service.week.ExportExcelForOTCService;
import com.hs.report.service.week.ExportExcelForZTService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
@Api(tags = "导出Excel模板")
@RestController
@RequestMapping("/investmentReport/week")
public class ExportWeekExcelController extends BaseController {

    private static String fileName = "交易类业务周报%s组";

    @Autowired
    private ExportExcelForZTService exportExcelForZTService;

    @Autowired
    private ExportExcelForGSService exportExcelForGSService;

    @Autowired
    private ExportExcelForOTCService exportExcelForOTCService;

    @ApiOperation(value = "导出")
    @PostMapping("/export")
    public byte[] export(@RequestBody List<SnapshotReq> snapshots, HttpServletResponse response) throws IOException {
        if(CollectionUtils.isEmpty(snapshots)){
            return null;
        }
        SnapshotReq snapshotDO = snapshots.get(0);
        Integer periodId = getFlowPeriodId(snapshotDO.getFlowId());

        if(StringUtils.isNotEmpty(snapshotDO.getType())) {
            HSSFWorkbook wb = new HSSFWorkbook();
            switch (Integer.valueOf(snapshotDO.getType())) {
                //证投
                case 1:
                    exportExcelForZTService.exportExcelForZT(wb, snapshots);
                    break;
                //固收
                case 2:
                    exportExcelForGSService.exportExcelForGS(wb,snapshots);
                    break;
                //OTC
                case 3:
                    exportExcelForOTCService.exportExcelForOTC(wb,snapshots);
                    break;
            }

            try {
                String exportFileName = String.format(fileName, DeptType.getByType(Integer.valueOf(snapshotDO.getType()))) + periodId;
                exportFileName = URLEncoder.encode(exportFileName, "UTF-8");
                response.setHeader("Content-Disposition", "attachment;Filename=" + exportFileName + ".xls");
                OutputStream outputStream = response.getOutputStream();
                wb.write(outputStream);
                outputStream.close();
                return wb.getBytes();
            } catch (Exception ex) {
                throw new IOException("导出Excel出现异常，异常信息：" + ex.getMessage());
            }
        }
        return null;
    }
}

Service层

package com.hs.report.service.impl.week;

import com.hs.report.entity.week.BusinessTypeDO;
import com.hs.report.entity.week.LossPayfDO;
import com.hs.report.entity.week.SnapshotReq;
import com.hs.report.entity.week.WeekLossPayfDO;
import com.hs.report.enums.DeptType;
import com.hs.report.service.week.ExportExcelForGSService;
import com.hs.report.utils.JsonListUtil;
import com.hs.report.utils.NumberUtil;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

@Service
public class ExportExcelForGSServiceImpl implements ExportExcelForGSService {
    @Override
    public void exportExcelForGS(HSSFWorkbook wb, List<SnapshotReq> snapshots) {
        //定义报表数据中的行数
        int rownum = 0;

        int columnNumber = 7;
        int[] columnWidth = {14,14,14,14,14,14,14,14};

        HSSFSheet sheet = wb.createSheet(DeptType.GS.getName());
        sheet.setDisplayGridlines(false);
        for(int i = 0; i < columnNumber; i++){
            sheet.setColumnWidth(i,columnWidth[i] * 256); // 单独设置每列的宽
        }

        //本周重点推进工作  描述
        String weekDesc = "";

        //下周重点推进工作  描述
        String lastWeekDesc = "";

        //T0周损益对比 描述
        String weekLossDesc = "";
        List<WeekLossPayfDO> weekList = new ArrayList<>();

        //市场状况及交易部门损益 描述
        String marketDesc = "";
        List<LossPayfDO> lossPayList = new ArrayList<>();

        //固定收益部损益分析 描述
        String fixedDesc = "";

        //表格第三部分
        List<BusinessTypeDO> businessList = new ArrayList<>();

        //日常工作部 描述
        String dailyDesc = "";

        for(SnapshotReq snapshotReq : snapshots){
            switch (snapshotReq.getOffset()) {
                case 1:
                    weekDesc = getContentStr((String)snapshotReq.getContent());
                    break;
                case 2:
                    lastWeekDesc = getContentStr((String)snapshotReq.getContent());
                    break;
                case 3:
                    weekLossDesc = getContentStr((String)snapshotReq.getContent());
                    break;
                case 4:
                    if(CollectionUtils.isNotEmpty((List<WeekLossPayfDO>)snapshotReq.getContent())){
                        String json = JsonListUtil.listToJson((List<WeekLossPayfDO>)snapshotReq.getContent());
                        weekList =  JsonListUtil.jsonToList(json, WeekLossPayfDO.class);
                    }
                    break;
                case 5:
                    marketDesc = getContentStr((String)snapshotReq.getContent());
                    break;
                case 7:
                    if(CollectionUtils.isNotEmpty((List<LossPayfDO>)snapshotReq.getContent())){
                        String json = JsonListUtil.listToJson((List<LossPayfDO>)snapshotReq.getContent());
                        lossPayList =  JsonListUtil.jsonToList(json, LossPayfDO.class);
                    }
                    break;
                case 10:
                    fixedDesc = getContentStr((String)snapshotReq.getContent());
                    break;
                case 11:
                    if(CollectionUtils.isNotEmpty((List<BusinessTypeDO>)snapshotReq.getContent())){
                        String json = JsonListUtil.listToJson((List<BusinessTypeDO>)snapshotReq.getContent());
                        businessList =  JsonListUtil.jsonToList(json, BusinessTypeDO.class);
                    }
                    break;
                case 12:
                    dailyDesc = getContentStr((String)snapshotReq.getContent());
                    break;
            }
        }

        //本周重点推进工作：
        int weekRowNum = getTextDesc(rownum,wb,sheet,columnNumber,weekDesc,"● 本周重点推进工作：");

        //下周重点关注工作
        int lastWeekRowNum = getTextDesc(weekRowNum,wb,sheet,columnNumber,lastWeekDesc,"● 下周重点关注工作：");

        //T0周损益对比
        int weekLossRowNum = getTextDesc(lastWeekRowNum,wb,sheet,columnNumber,weekLossDesc,"● T0周损益对比：");

        //第一个表格内容
        int oneTitleText = getOneTitleText(weekLossRowNum,wb,sheet,columnNumber,weekList);

        //市场状况及交易部门损益
        int marketRowNum = getTextDesc(oneTitleText,wb,sheet,columnNumber,marketDesc,"一、市场状况及交易部门损益");

        //第二个表格内容
        int twoTitleText = getTwoTitleText(marketRowNum,wb,sheet,columnNumber,lossPayList);

        //部门损益分析
        rownum = twoTitleText + 1;
        HSSFCellStyle styleWeek = createTableDescStyleNo(wb);
        styleWeek.setWrapText(true);
        HSSFRow rowWeek = sheet.createRow(rownum);
        HSSFCell cellWeek = rowWeek.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(rownum,rownum,0,columnNumber));
        cellWeek.setCellValue("二、部门损益分析");
        cellWeek.setCellStyle(styleWeek);

        //固定收益部损益分析
        int fixedRowNum = getTextDesc(rownum,wb,sheet,columnNumber,fixedDesc,"2.固定收益部损益分析");

        //第三个表格内容
        int threeTitleText = getThreeTitleText(fixedRowNum,wb,sheet,columnNumber - 2,businessList);

        //三、日常工作
        rownum = threeTitleText + 1;
        HSSFCellStyle styleDaily = createTableDescStyleNo(wb);
        styleDaily.setWrapText(true);
        HSSFRow rowDaily = sheet.createRow(rownum);
        HSSFCell cellDaily = rowDaily.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(rownum,rownum,0,columnNumber));
        cellDaily.setCellValue("三、日常工作");
        cellDaily.setCellStyle(styleDaily);

        //固定收益部
        int dailyRowNum = getTextDesc(rownum,wb,sheet,columnNumber,dailyDesc,"固定收益部");


    }

    private int getThreeTitleText(int rownum, HSSFWorkbook wb, HSSFSheet sheet, int columnNumber, List<BusinessTypeDO> businessList) {
        if(CollectionUtils.isNotEmpty(businessList)){
            int oneRightText = getThousandDesc(rownum,wb,sheet,columnNumber);

            String perPurpose = "";//上一个数据描述
            String purpose = "";//当前数据描述
            boolean flag = true;//用来记录是否是第一次循环
            int endMergeCol = 0;//要融合的最后行

            //创建单元格，并设置值
            rownum = oneRightText + 1;
            for(int i = 0; i < businessList.size(); i++) {
                BusinessTypeDO voData = businessList.get(i);
                HSSFRow rowChangeTitle = sheet.createRow(i + rownum);
                HSSFCell datacell = null;
                for (int j = 0; j < columnNumber; j++) {
                    if(j % 2 == 0){
                        HSSFCellStyle tableCellStyle;
                        if(j == 2){
                            tableCellStyle = createBGTableCellStyleLeft(wb);
                        } else if(j == 4){
                            tableCellStyle = createBGTableCellStyleNo(wb);
                        }else{
                            tableCellStyle = createBGTableCellStyle(wb);
                        }
                        if(j == 0){
                            datacell = rowChangeTitle.createCell(j);
                            datacell.setCellStyle(tableCellStyle);
                            setValue(datacell,getBusinessTypeValue(voData, j));

                            purpose = voData.getTitle();

                            if(flag){//如果是第一次循环，将perPurpose初始化，以便后面的第一次判断
                                perPurpose = purpose;
                                flag = false;
                            }

                            if(purpose.equals(perPurpose)){
                                endMergeCol++;
                            }else{//两者不相同时，融合之前相同的行
                                int lastRow = i + rownum;
                                if(endMergeCol != 0){
                                    lastRow = i + rownum - 1;
                                }
                                CellRangeAddress region = new CellRangeAddress(i + rownum - endMergeCol,lastRow,j,j + 1);
                                sheet.addMergedRegion(region);
                                setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, wb);   //给合并过的单元格加边框
                                endMergeCol = 1;
                                perPurpose = purpose;
                            }

                            //最后一行时
                            if(i == businessList.size() - 1 && endMergeCol > 0){
                                int lastRow = businessList.size() + rownum;
                                if(endMergeCol != 0){
                                    lastRow = businessList.size() + rownum - 1;
                                }
                                CellRangeAddress region = new CellRangeAddress(businessList.size() + rownum - endMergeCol,lastRow,j,j + 1);
                                sheet.addMergedRegion(region);
                                setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, wb);   //给合并过的单元格加边框
                                endMergeCol = 0;
                                perPurpose = purpose;
                            }

                        }else{
                            datacell = rowChangeTitle.createCell(j);
                            datacell.setCellStyle(tableCellStyle);
                            setValue(datacell,getBusinessTypeValue(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 + businessList.size();
        }

        return rownum;
    }

    private String getBusinessTypeValue(BusinessTypeDO voData, int cellIndex) {
        switch(cellIndex){
            case 0:
                return getContentStr(voData.getTitle());
            case 2:
                return getContentStr(voData.getSubtitle());
            case 4:
                return getContentStr(voData.getValue());
            default:
                return "";
        }
    }

    private int getTwoTitleText(int rownum, HSSFWorkbook wb, HSSFSheet sheet, int columnNumber, List<LossPayfDO> lossPayList) {
        int oneRightText = getThousandDesc(rownum,wb,sheet,columnNumber);

        rownum = oneRightText + 1;

        // 创建表头单元格样式 以及表头的字体样式
        HSSFCellStyle changeTitleStyle = createTableTitleStyle(wb);
        HSSFRow rowChangeTitle = sheet.createRow(rownum);
        String[] riskTitle = {"部门", "权益敞口", "固收敞口", "现货市值","期货敞口","周损益","月损益","年损益"};
        for (int i = 0; i <= columnNumber; i++) {
            HSSFCell cellRiskTitle = rowChangeTitle.createCell(i);
            cellRiskTitle.setCellValue(riskTitle[i]);
            cellRiskTitle.setCellStyle(changeTitleStyle);
        }

        //创建单元格，并设置值
        rownum = rownum + 1;
        if(CollectionUtils.isNotEmpty(lossPayList)){
            for(int i = 0; i < lossPayList.size(); i++){
                LossPayfDO lossPayfDO = lossPayList.get(i);
                rowChangeTitle = sheet.createRow(i + rownum);
                HSSFCell datacell = null;
                for (int j = 0; j <= columnNumber; j++) {
                    HSSFCellStyle tableCellStyle = createBGTableCellStyleNo(wb);
                    datacell = rowChangeTitle.createCell(j);
                    datacell.setCellStyle(tableCellStyle);
                    setValue(datacell,getRiskCellValue(lossPayfDO, j));
                }
            }
            rownum = rownum + lossPayList.size();
        }else{
            rownum = rownum + 1;
        }


        return rownum;
    }

    private String getRiskCellValue(LossPayfDO lossPayfDO, int cellIndex) {
        switch(cellIndex){
            case 0:
                return getContentStr(lossPayfDO.getDepName());
            case 1:
                return getDobleToString(lossPayfDO.getRiskExpo());
            case 2:
                return getDobleToString(lossPayfDO.getGsck());
            case 3:
                return getDobleToString(lossPayfDO.getActlMval());
            case 4:
                return getDobleToString(lossPayfDO.getFutrNetPos());
            case 5:
                return getDobleToString(lossPayfDO.getWeekLossPayf());
            case 6:
                return getDobleToString(lossPayfDO.getMthLossPayf());
            case 7:
                return getDobleToString(lossPayfDO.getYearLossPayf());
            default:
                return "";
        }
    }

    private String getDobleToString(double riskExpo) {
        return riskExpo == 0.0 ? "" : Double.toString(riskExpo);
    }

    private int getOneTitleText(int rownum, HSSFWorkbook wb, HSSFSheet sheet, int columnNumber,List<WeekLossPayfDO> weekList) {
        int oneRightText = getThousandDesc(rownum,wb,sheet,columnNumber);

        rownum = oneRightText + 1;

        // 创建表头单元格样式 以及表头的字体样式
        HSSFCellStyle changeTitleStyle = createTableTitleStyle(wb);
        HSSFRow rowChangeTitle = sheet.createRow(rownum);
        String[] riskTitle = {"部门", "本周损益", "上周损益", "年平均周损益"};
        for (int i = 0; i < columnNumber; i++) {
            if(i % 2 == 0){
                HSSFCell cellChangeTitle = rowChangeTitle.createCell(i);
                cellChangeTitle.setCellValue(riskTitle[i / 2]);
                cellChangeTitle.setCellStyle(changeTitleStyle);
                CellRangeAddress region = new CellRangeAddress(rownum,rownum,i,i + 1);
                sheet.addMergedRegion(region);
                setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, wb);   //给合并过的单元格加边框
            }
        }

        //创建单元格，并设置值
        rownum = rownum + 1;
        if(CollectionUtils.isNotEmpty(weekList)){
            for(int i = 0; i < weekList.size(); i++){
                WeekLossPayfDO weekLossPayfDO = weekList.get(i);
                rowChangeTitle = sheet.createRow(i + rownum);
                HSSFCell datacell = null;
                for (int j = 0; j < columnNumber; j++) {
                    if(j % 2 == 0){
                        HSSFCellStyle tableCellStyle = createBGTableCellStyleNo(wb);
                        datacell = rowChangeTitle.createCell(j);
                        datacell.setCellStyle(tableCellStyle);
                        setValue(datacell,getChangeCellValue(weekLossPayfDO, j));
                        CellRangeAddress region = new CellRangeAddress(i + rownum,i + rownum,j,j + 1);
                        sheet.addMergedRegion(region);
                        setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, wb);   //给合并过的单元格加边框
                    }
                }
            }
            rownum = rownum + weekList.size();
        }else{
            rownum = rownum + 1;
        }

        return rownum;
    }

    private void setValue(HSSFCell cell, String value){
        if (NumberUtil.isNumeric(value)) {
            cell.setCellValue(Double.valueOf(value));
        } else {
            cell.setCellValue(value);
        }
    }

    private String getChangeCellValue(WeekLossPayfDO weekLossPayfDO, int cellIndex) {
        switch(cellIndex){
            case 0:
                return getContentStr(weekLossPayfDO.getDeptName());
            case 2:
                return weekLossPayfDO.getWeekLoss() == null ? "" : weekLossPayfDO.getWeekLoss().toString();
            case 4:
                return weekLossPayfDO.getLastWeekLoss() == null ? "" : weekLossPayfDO.getLastWeekLoss().toString();
            case 6:
                return weekLossPayfDO.getYearLoss() == null ? "" : weekLossPayfDO.getYearLoss().toString();
            default:
                return "";
        }
    }

    //创建单元格样式 以及字体样式
    private HSSFCellStyle createBGTableCellStyleLeft(HSSFWorkbook wb){
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        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.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 9);
        style.setFont(titleFont);

        return style;
    }

    //创建单元格样式 以及字体样式
    private HSSFCellStyle createBGTableCellStyleNo(HSSFWorkbook wb){
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        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 createBGTableCellStyle(HSSFWorkbook wb){
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        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;
    }

    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 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.SEA_GREEN.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);
        HSSFFont titleFont = (HSSFFont) wb.createFont();
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 9);
        style.setFont(titleFont);

        return style;
    }

    private int getThousandDesc(int rownum, HSSFWorkbook wb, HSSFSheet sheet, int columnNumber){
        rownum = rownum + 1;

        HSSFCellStyle styleWeek = createTableDescStyleRight(wb);
        styleWeek.setWrapText(true);
        HSSFRow rowWeek = sheet.createRow(rownum);
        HSSFCell cellWeek = rowWeek.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(rownum,rownum,0,columnNumber));
        cellWeek.setCellValue("(万元)");
        cellWeek.setCellStyle(styleWeek);
        return rownum;
    }

    //设置字体靠右
    private HSSFCellStyle createTableDescStyleRight(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        HSSFFont titleFont = (HSSFFont) wb.createFont();
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 11);
        style.setFont(titleFont);
        return style;
    }

    private String getContentStr(String content) {
        return StringUtils.isNotEmpty(content) ? content : StringUtils.EMPTY;
    }

    private int getTextDesc(int rownum, HSSFWorkbook wb, HSSFSheet sheet, int columnNumber, String desc, String titleDesc) {
        rownum = rownum + 1;

        HSSFCellStyle styleWeek = createTableDescStyleNo(wb);
        styleWeek.setWrapText(true);
        HSSFRow rowWeek = sheet.createRow(rownum);
        HSSFCell cellWeek = rowWeek.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(rownum,rownum,0,columnNumber));
        cellWeek.setCellValue(titleDesc);
        cellWeek.setCellStyle(styleWeek);

        rownum = rownum + 1;
        HSSFRow row = sheet.createRow(rownum);
        row.setHeightInPoints(110);
        HSSFCell cell = row.createCell(0);

        HSSFCellStyle style = wb.createCellStyle();
        style.setWrapText(true);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cell.setCellStyle(style);
        cell.setCellValue(desc + "\n");

        CellRangeAddress region = new CellRangeAddress(rownum,rownum,0,columnNumber);
        sheet.addMergedRegion(region);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, wb);   //给合并过的单元格加边框


        return rownum + 1;
    }

    //设置字体不加粗
    private HSSFCellStyle createTableDescStyleNo(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        HSSFFont titleFont = (HSSFFont) wb.createFont();
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 11);
        style.setFont(titleFont);
        return style;
    }
}
怪咖基德
关注
  • 3
    点赞
  • 踩
  • 19
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
JAVA大量数据导出excel
caijigskg的博客
05-09 570
背景：因项目需要导出3万行+，90列+的数据到excel，使用传统的apache poi 直接导出，导致504连接超时无法导出。然后改造方法，异步导出。
java导出复杂excel表格_复杂的POI导出Excel表格（多行表头、合并单元格）
weixin_39849479的博客
02-13 1063
/*** 导出excel (HSSFWorkbook)*/public voidexportExcel() {/**第一步，创建一个Workbook，对应一个Excel文件*/HSSFWorkbook wb= newHSSFWorkbook();/**第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet*/HSSFSheet sheet= wb.createSheet...
java导出带有复杂表头的Excel
goodtimealways的博客
10-25 3485
前几天用layui框架做列表导出Excel的功能,但是发现复杂的表头无法使用自带插件导出,然后在搞了下面的代码,可以支持复杂的表头导出,直接作为工具类用就好了 import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; impor...
Java导出复杂的表格文件（.xls）
最新发布
qq_64451313的博客
03-22 243
- EasyPoi依赖 -->
java导出复杂打印格式的Excel
qq_37053589的博客
10-23 808
要做成： 打印区域为： 划分的小单元是一盒的数据，也就是8行为一个单元来填入数据，这个看你具体的业务。更常见的是以一行为单位。 使用工具：某大佬的插件，稍微改动了一下打印部分的设置 ExcelTemplate插件代码https://gitee.com/jiangyongbing24/poi/blob/master/src/export/ExcelTemplate.java 博客原文 https://blog.csdn.net/jiangyongbing008/article/details/100795
java导出复杂Excel内容数据动态循环
06-30
java导出复杂Excel内容数据动态循环
java导出excel复杂表头
02-20
java工程，源代码，可直接运行，需自己改造成web下载方式。
java实现导出Excel多行表头复杂模板
12-20
java实现导出Excel多行表头复杂模板 一般我们都会选择poi来导出，选择一个比较好的ExcelUtils 但是对于初学者而言不了解poi的，还需从poi API文档去学习，如果是自学的话更好，如果是公司安排的任务，没有接触过有...
java复杂模板excel导出例子
11-02
java复杂模板excel导出例子
java基于模板导出Excel表格
09-14
除了平时简单的数据导出需求外，我们也经常会遇到一些有固定格式或者模板要求的数据导出，这个时候poi导出方式变得复杂且冗长，因此采用EasyExcel的方式进行导出，可以大大提高我们的开发效率和简洁我们的代码。...
java POI 导出复杂要求的表格
04-17
NULL 博文链接：https://zhaining123129-gmail-com.iteye.com/blog/1832560
java导出excel 复杂的 合并单元格
wwy11123的博客
03-29 853
java导出excel 复杂的 合并单元格
java 导出复杂格式的 Excel 多级表头
Yoke______的博客
09-30 521
java 导出复杂格式的 Excel 多级表头
JAVA POI导出复杂表头Excel
Coco_chun的博客
08-24 3622
参考文章 https://blog.csdn.net/u013585096/article/details/83503519 我的结果表格样式 //Excel HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); Sheet sheet = hssfWorkbook.createSheet("sheet1"); ...
poi导出excel复杂表头表格数据
chuosuikuai8063的博客
09-19 1717
import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache....
Java springboot EasyExcel Excel自定义表头导出demo
weixin_43969830的博客
04-19 2796
现有一个业务，需要将列表数据导出excel，并返回给前端。表头有合并、拆分，结构比较复杂，使用EasyExcel将数据导出。这里采用了两种返回方式：导出到本地、直接返回给前端。1、直接执行ExcelService中的main方法，将数据保存到本地。2、启动springboot项目，浏览器分别访问。
easyExcel
weixin_43887572的博客
11-19 1082
easyexcel注解 11个注解 @ExcelProperty @ColumnWith 列宽 @ContentFontStyle 文本字体样式 @ContentLoopMerge 文本合并 @ContentRowHeight 文本行高度 @ContentStyle 文本样式 @HeadFontStyle 标题字体样式 @HeadRowHeight 标题高度 @HeadStyle 标题样式 @ExcelIgnore 忽略项 @ExcelIgnoreUnannotated 忽略未注解 字段注解 类注解 @Co
使用easy excel导出复杂表头的excel
次日清晨的博客
12-15 5678
使用easy excel导出复杂表头的excel 今天想写一个双层表头的excel导出,一开始使用的是poi来画发现太麻烦, 于是就想到了使用easy-excel的模板填充来实现,将导出写成了一个简单的工具类, 供参考 最终是要实现为这样的一个效果 , 红色为表头,绿色为表体,于是我先做出了一个模板 然后将表头数据封装成一个map,表体数据为一个list , 去调用工具类就实现了 以下为工具类代码,导入了easy-excel就可以使用 package com.org.inventory.util;
java 导出复杂excel
09-03
在Java中，导出复杂的Excel文件可以使用Apache POI库。Apache POI是一个强大的Java库，用于创建和操作Microsoft Office格式的文件，包括Excel文件。 要导出复杂的Excel文件，可以按照以下步骤进行： 1. 导入Apache...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值