用Java代码中poi操作excel生成散点图 java poi生成excel文件

转载

mob64ca13fd163c 2024-06-20 20:34:33

文章标签 java excel apache 超链接字段 文章分类 Java 后端开发

直入主题：

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;

/**
 * poi excel工具
 * @author zhangxiaodong
 * @date 2023/3/20 11:35
 */
@Slf4j
public class ExcelUtils {
    /**
     * 通用头样式 可自定义
     * @param headCellStyle
     * @return
     */
    public static CellStyle getHeadCellStyle(CellStyle headCellStyle) {
        headCellStyle.setAlignment(HorizontalAlignment.CENTER);// 设置水平对齐
        headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 设置垂直对齐
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);//设置背景颜色
        return headCellStyle;
    }

    /**
     * 通用内容样式 可自定义
     * @param contentCellStyle
     * @return
     */
    public static CellStyle getContentCellStyle(CellStyle contentCellStyle) {
        contentCellStyle.setAlignment(HorizontalAlignment.CENTER);// 设置水平对齐
        contentCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 设置垂直对齐
        return contentCellStyle;
    }

    /**
     * 通用超链接样式 可自定义
     * @param linkCellStyle
     * @param font
     * @return
     */
    public static CellStyle getLinkCellStyle(CellStyle linkCellStyle, Font font) {
        linkCellStyle.setAlignment(HorizontalAlignment.CENTER);// 设置水平对齐
        linkCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 设置垂直对齐
        font.setColor(IndexedColors.BLUE.index);//超链接字体颜色
        font.setUnderline((byte) 1);//下划线
        linkCellStyle.setFont(font);
        return linkCellStyle;
    }

    /**
     * 通用poi生成excel工具 - 单个sheet
     * @param fileName 生成的文件路径
     * @param headMaps 头map，key值：表头字段对应的字段名（驼峰），value值：表头展示名 注：若字段展示名带有超链接字样可自动为该列设置超链接格式
     * @param contList 内容数据集，超链接拼接规则如下：表格展示内容 + ##&& + 链接url
     * @param titleList 表字段之上的表头
     */
    public static void createExcel(String fileName, String sheetName, LinkedHashMap<String,String> headMaps, List contList,List<String> titleList) {
        if (StringUtils.isBlank(fileName) || StringUtils.isBlank(sheetName)
            ||headMaps.isEmpty() || contList.isEmpty()){
            return;
        }
        //表头
        BufferedOutputStream out = null;
        try {
            out = new BufferedOutputStream(new FileOutputStream(fileName));
            Workbook workbook = new XSSFWorkbook();
            //通用头样式
            CellStyle headCellStyle = workbook.createCellStyle();
            headCellStyle = getHeadCellStyle(headCellStyle);
            //通用内容样式
            CellStyle contentCellStyle = workbook.createCellStyle();
            contentCellStyle = getContentCellStyle(contentCellStyle);
            //设置超链接样式
            CellStyle linkCellStyle = workbook.createCellStyle();
            linkCellStyle = getLinkCellStyle(linkCellStyle,workbook.createFont());
            createSheet(workbook,sheetName,headMaps,contList,titleList,headCellStyle,contentCellStyle,linkCellStyle);
            workbook.write(out);
        } catch (Exception e) {
            log.error(e.toString());
            throw new RuntimeException("导出数据异常！");
        }finally {
            try {
                out.close();
            } catch (IOException e) {
                log.error(e.toString());
            }
        }
    }

    /**
     * 通用poi生成excel工具 - 多个sheet
     * @param fileName 生成的文件路径
     * @param sheetNames 多个sheet名称集合
     * @param headMaps 字段map，key：sheetName value：字段数据（key值：表头字段对应的字段名（驼峰），value值：表头展示名）
     *                 注：若字段展示名带有超链接字样可自动为该列设置超链接格式
     * @param contMaps 内容数据集 key：sheetName value：数据集合，超链接拼接规则如下：表格展示内容 + ##&& + 链接url
     * @param titleMaps 表字段之上的表头 key：sheetName value：表头名称
     */
    public static void createExcel(String fileName,
                                   List<String> sheetNames,
                                   Map<String,LinkedHashMap<String,String>> headMaps,
                                   Map<String,List> contMaps,
                                   Map<String,List<String>> titleMaps) {
        if (StringUtils.isBlank(fileName) || sheetNames.isEmpty()
                ||headMaps.isEmpty() || contMaps.isEmpty()){
            return;
        }
        BufferedOutputStream out = null;
        try {
            out = new BufferedOutputStream(new FileOutputStream(fileName));
            Workbook workbook = new XSSFWorkbook();
            //通用头样式
            CellStyle headCellStyle = workbook.createCellStyle();
            headCellStyle = getHeadCellStyle(headCellStyle);
            //通用内容样式
            CellStyle contentCellStyle = workbook.createCellStyle();
            contentCellStyle = getContentCellStyle(contentCellStyle);
            //设置超链接样式
            CellStyle linkCellStyle = workbook.createCellStyle();
            linkCellStyle = getLinkCellStyle(linkCellStyle,workbook.createFont());
            //多个sheet循环创建
            for (String sheetName : sheetNames){
                List contList = contMaps.get(sheetName);
                List<String> titleList = null;
                if (titleMaps != null && titleMaps.size()>0) titleList = titleMaps.get(sheetName);
                LinkedHashMap<String,String>  headMap = headMaps.get(sheetName);
                createSheet(workbook,sheetName,headMap,contList,titleList,headCellStyle,contentCellStyle,linkCellStyle);
            }
            workbook.write(out);
        } catch (Exception e) {
            log.error(e.toString());
            throw new RuntimeException("导出数据异常！");
        }finally {
            try {
                out.close();
            } catch (IOException e) {
                log.error(e.toString());
            }
        }
    }

    /**
     * 通用poi生成excel工具2 - 在已有的文件中增加sheet
     *
     * @param headMaps         头map，key值：表头字段对应的字段名（驼峰），value值：表头展示名
     * @param contList         内容数据集
     * @param titleList        表字段之上的表头
     * @param headCellStyle    表头样式
     * @param contentCellStyle 内容样式
     * @param linkCellStyle    超链接样式
     */
    public static void createSheet(Workbook workbook, String sheetName, LinkedHashMap<String,String> headMaps, List contList, List<String> titleList, CellStyle headCellStyle, CellStyle contentCellStyle, CellStyle linkCellStyle) throws IllegalAccessException {
        if (StringUtils.isBlank(sheetName) ||headMaps.isEmpty() || contList.isEmpty()){
            return;
        }
        Sheet sheet = workbook.createSheet(sheetName);
        sheet.setDefaultColumnWidth(40);
        //如有表头则合并单元格
        int k = 0;
        if (titleList != null && titleList.size()>0){
            k = titleList.size();
            for (int z = 0;z < titleList.size();z ++){
                //合并单元格 参数说明：1：开始行 2：结束行  3：开始列 4：结束列
                sheet.addMergedRegion(new CellRangeAddress(z,z,0,headMaps.size()-1));
                Row row = sheet.createRow(z);
                row.setHeight((short) 400);//行高
                Cell cell = row.createCell(0);
                cell.setCellStyle(contentCellStyle);
                cell.setCellValue(titleList.get(z));
            }
        }
        //查询标题带有超链接字段位置，并添加超链接
        Integer headLinkUrlSize = null;
        //数据组装
        for (int i = 0; i < contList.size()+1;i++){
            Row row = sheet.createRow(k + i);
            //第一行表字段特殊处理
            if (i == 0){
                row.setHeight((short) 400);//行高
                int j = 0;
                for (String key : headMaps.keySet()){
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(headCellStyle);
                    cell.setCellValue(headMaps.get(key));
                    //记录超链接位置
                    if (headMaps.get(key).contains("超链接")){
                        headLinkUrlSize = j ;
                    }
                    j++;
                }
            //数据内容处理
            }else {
                Hyperlink link = null;
                //超链接位置不为空，创建超链接样式
                if (headLinkUrlSize != null){
                    CreationHelper createHelper = workbook.getCreationHelper();
                    link = createHelper.createHyperlink(HyperlinkType.FILE);
                }
                row.setHeight((short) 400);
                //反射获取字段值，为excel单元格赋值
                Object obj = contList.get(i - 1);
                Class<?> cla = obj.getClass();
                Field[] fields = cla.getDeclaredFields();
                List<String> headKeyList = headMaps.keySet().stream().collect(Collectors.toList());
                for (int j=0;j<fields.length;j++) {
                    Field field = fields[j];
                    field.setAccessible(true);
                    String keyName = field.getName();
                    if (headKeyList.contains(keyName)){
                        //在匹配到字段名的位置插入值
                        Integer cellSize = headKeyList.indexOf(keyName);
                        Cell cell = row.createCell(cellSize);
                        String content = field.get(obj) == null ? "" : field.get(obj).toString();
                        //如果超链接位置匹配，添加超链接  ##&& 用于拼接展示文本和超链接
                        if (headLinkUrlSize != null && cellSize.equals(headLinkUrlSize)){
                            link.setAddress(content.split("##&&")[1]);
                            cell.setHyperlink(link);
                            cell.setCellStyle(linkCellStyle);
                        }else {
                            cell.setCellStyle(contentCellStyle);
                        }
                        cell.setCellValue(content.split("##&&")[0]);
                    }
                }
            }
        }
    }
}

调用单个sheet创建案例，业务代码可忽略：

public String exportExcel(CollectLabourArticleDto dto) {
        if (StringUtils.isBlank(dto.getLabourTime())){
            throw new CustomException("请选择需要导出的劳务所属月份！");
        }
        dto.setPageNum(null);
        dto.setPageSize(null);
        List<CollectLabourArticleListVo> list = getLabourArticlePage(dto);
        if (list == null || list.isEmpty()){
            return null;
        }
        //组装标题的链接
        for (int no = 0;no < list.size();no++){
            CollectLabourArticleListVo l = list.get(no);
            l.setNo(no + 1);
            //按照：展示内容 + ##&& + url 范式拼接
            l.setTitle(l.getTitle() + "##&&https://"+urlPre+"/#/manuscript/detail?id="+l.getId());
        }
        File file1 = new File(basePath);
        if (!file1.exists()) {//如果文件夹不存在
            file1.mkdir();//创建文件夹
        }
        //文件名
        String fileName = "文章明细"+System.currentTimeMillis()+".xlsx";
        //表字段 key值为实体类中驼峰字段（方便反射取值），value为excel表展示字段
        LinkedHashMap<String, String> headMaps = new LinkedHashMap<>();
        headMaps.put("no","序号");
        headMaps.put("title","标题（超链接）");//超链接样式
        headMaps.put("labourChannel","劳务所属频道");
        headMaps.put("labourAuthor","劳务所属作者");
        headMaps.put("money","预计稿费");
        //表头
        Set<String> labourChannelSet = list.stream().filter(l -> StringUtils.isNotBlank(l.getLabourChannel())).map(CollectLabourArticleListVo::getLabourChannel).collect(Collectors.toSet());
        String channels = "";
        if (labourChannelSet != null && labourChannelSet.size()>0){
            channels = StringUtils.join(labourChannelSet,"、");
        }
        List<String> titleList = new ArrayList<>();
        String title = dto.getLabourTime() + " " + channels + " 工作总结";
        titleList.add(title);
        ExcelUtils.createExcel(basePath  + fileName,"文章明细",headMaps,list,titleList);
        return fileName;
    }