欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

POI Excel导出合并单元格

程序员文章站 2024-02-19 11:10:28
...
 /**
     * 
     * @param dataList 数据
     * @param headNameMap 标题
     * @param type 类型 1 xls 2 xlsx
     * @param mergeIndex 需要合并的列 从1开始  0是序号
     * @param col 以哪一列为准进行合并
     * @return
     * @throws Exception
     */
    public static byte[] toExcel(List<?> dataList, Map<String, String> headNameMap, int type,int[] mergeIndex,int col) throws Exception {
        Workbook workbook;
        if (type == 1) {
            workbook = new XSSFWorkbook();
        } else if (type == 2) {
            workbook = new SXSSFWorkbook();
        } else {
            workbook = new HSSFWorkbook();
        }
        List<Method> methodList = null;
        Sheet sheet = workbook.createSheet("数据列表");

        int index = sheet.getPhysicalNumberOfRows();
        for (int i = 0; i < dataList.size(); i++) {
            Object object = dataList.get(i);
            if (methodList == null) {
                Method[] methods = object.getClass().getMethods();
                methodList = new ArrayList<>();
                Row rowHead = sheet.createRow(index);
                Iterator<Map.Entry<String, String>> iterator = headNameMap.entrySet().iterator();
                int c = 0;
                while (iterator.hasNext()) {
                    Map.Entry<String, String> entry = iterator.next();
                    for (int m = 0; m < methods.length; m++) {
                        if (methods[m].getName().toLowerCase().equals(("get" + entry.getKey()).toLowerCase())) {
                            methodList.add(methods[m]);
                            Cell cell = rowHead.createCell(c);
                            setCellValue(cell, entry.getValue());
                            c++;
                        }
                        if (methods[m].getName().toLowerCase().equals(("getlist"))){
                            Object invoke = methods[m].invoke(object);
                        }
                    }
                }
            }
            Row row = sheet.createRow(index + 1);
            for (int m = 0; m < methodList.size(); m++) {
                Object value = methodList.get(m).invoke(object);
                Cell cell = row.createCell(m );
                Object textValue = getValue(value);
                setCellValue(cell, textValue);

            }
            index++;
        }
        String str=null;
        int strBeginIndex=0;
        int strEndIndex=0;
        for (int i = 0; i <mergeIndex.length; i++) {
            int j=0;
            int start=0;
            for (Row row : sheet) {
                if (j==0){
                    j++;
                    continue ;
                }
                if (strBeginIndex==0){
                    strBeginIndex=sheet.getRow(j-1).getRowNum();
                }
                // 合并日期占两行(4个参数,分别为起始行,结束行,起始列,结束列)
                // 行和列都是从0开始计数,且起始结束都会合并
                if (StringUtil.isEmpty(str)){
                    str = row.getCell(col).getStringCellValue();
                    if (str.equals(sheet.getRow(j).getCell(col).getStringCellValue())){
                        strBeginIndex=row.getRowNum();
                    }
                }else if (str.equals(row.getCell(col).getStringCellValue())){
                    if (strBeginIndex==0){
                        strBeginIndex=sheet.getRow(j-1).getRowNum();
                    }
                    if (sheet.getLastRowNum()==j){
                        //末尾合并
                        strBeginIndex =strBeginIndex-1;
                        strEndIndex =strEndIndex+1;
                        if(strBeginIndex!=strEndIndex){
                            CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i],  mergeIndex[i]);
                            sheet.addMergedRegion(region);
                        }
                        strBeginIndex=0;
                        start=0;
                    }
                }else if (!str.equals(row.getCell(col).getStringCellValue())){
                    //需合并
                    strEndIndex=row.getRowNum();
                    if (start==0&&strBeginIndex>0&&strEndIndex>0){
                        strEndIndex =strEndIndex-1;
                        if(strBeginIndex!=strEndIndex){
                            //首行合并
                            CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex,  mergeIndex[i],  mergeIndex[i]);
                            sheet.addMergedRegion(region);
                        }
                        strBeginIndex=0;
                        start=1;
                    }else if (strBeginIndex>0&&strEndIndex>0){
                        //中间行合并
                        strEndIndex =strEndIndex-1;
                        if(strBeginIndex!=strEndIndex){
                            CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex,  mergeIndex[i],  mergeIndex[i]);
                            sheet.addMergedRegion(region);
                        }
                        strBeginIndex=0;
                    }
                    str=row.getCell(col).getStringCellValue();
                }
                j++;
            }
        }
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        workbook.close();
        System.out.println("export finish!");
        return baos.toByteArray();
    }
private static Object getValue(Object value) {
        Object textValue = "";
        if (value != null) {
            if (value instanceof Boolean) {
                textValue = (Boolean) value ? "是" : "否";
            } else if (value instanceof Date) {
                textValue = DateUtils.format((Date) value, "yyyy-MM-dd HH:mm:ss");
            } else if (value instanceof String) {
                String val = (String) value;
                textValue = StringUtil.isEmpty(val) || "null".equalsIgnoreCase(val) ? "" : val;
            } else {
                textValue = value;
            }
        }
        return textValue;
    }
private static void setCellValue(Cell cell, Object value) {
        if (value != null) {
            if (value instanceof Integer) {
                cell.setCellValue((Integer) value);
            } else if (value instanceof Boolean) {
                Boolean booleanValue = (Boolean) value;
                cell.setCellValue(booleanValue);
            } else if (value instanceof Date) {
                Date dateValue = (Date) value;
                cell.setCellValue(dateValue);
            } else if (value instanceof Float) {
                Float floatValue = (Float) value;
                cell.setCellValue(floatValue);
            } else if (value instanceof Double) {
                Double doubleValue = (Double) value;
                cell.setCellValue(doubleValue);
            } else if (value instanceof Long) {
                Long longValue = (Long) value;
                cell.setCellValue(longValue);
            } else {
                cell.setCellValue(value.toString());
            }
        }
    }

test:

public static void main(String[] args) throws Exception {
        List<Model> list=new ArrayList<>();
        Model model0=new Model("2201812011052199002","中国南通16","富氢水杯",new BigDecimal("0.00"),new BigDecimal("2"));
        list.add(model0);
        Model model1=new Model("220181201105219900","中国南通1","果蔬肉类智能生态仪",null,new BigDecimal("20"));
        list.add(model1);
        Model model2=new Model("220181201105219900","中国南通1","果蔬肉类智能生态仪",new BigDecimal("100"),new BigDecimal("200"));
        list.add(model2);
        Model model3=new Model("2201812011107273028","中国浙江18","量子眼镜",new BigDecimal("3"),new BigDecimal("4"));
        list.add(model3);
        Model model4=new Model("2201812011107273028","中国浙江18","汽车负氧离子氧吧",new BigDecimal("5"),new BigDecimal("2"));
        list.add(model4);
        Model model5=new Model("2201812011107563899","中国浙江19","天下美抗菌套盒",new BigDecimal("1"),new BigDecimal("7"));
        list.add(model5);
        Model model6=new Model("2201812011108176372","中国浙江20","参元颗粒(0.8g*30瓶)",new BigDecimal("8"),new BigDecimal("3"));
        list.add(model6);
        Model model7=new Model("2201812011107563899","中国浙江21","米饭脱糖仪",new BigDecimal("4"),new BigDecimal("6"));
        list.add(model7);
        Model model8=new Model("2201812011107563899","中国浙江21","米饭脱糖1仪",new BigDecimal("4"),new BigDecimal("6"));
        list.add(model8);
        Model model=new Model();
        Map<String,String> map=getFieldAnnotation(model);
        byte[] bytes = toExcel(list, map, 1,new int[]{0,1},0);
        File file=new File("D:\\demo1.xls");
        if(file.exists()){
            file.delete();
        }
        FileOutputStream fos = new FileOutputStream(file);
        fos.write(bytes,0,bytes.length);
        fos.flush();
        fos.close();
    }

Model:

package com.eg.cdt.saas.operator.common.model;

import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;
import com.alibaba.excel.annotation.ExcelProperty;

@Data
public class Model implements Serializable {
    @ExcelProperty(value = {"订单号"},index = 1)
    private String orderNo;
    @ExcelProperty(value = {"厂家"},index = 2)
    private String changJia;
    @ExcelProperty(value = {"商品"},index = 3)
    private String goods;
    @ExcelProperty(value = {"价格1"},index = 4)
    private BigDecimal price1;
    @ExcelProperty(value = {"价格2"},index = 5)
    private BigDecimal price2;

    public Model() {
    }

    public Model(String orderNo, String changJia, String goods, BigDecimal price1, BigDecimal price2) {
        this.orderNo = orderNo;
        this.changJia = changJia;
        this.goods = goods;
        this.price1 = price1;
        this.price2 = price2;
    }
}
 public static Map<String, String> getFieldAnnotation(Object object) {
        Field[] fields = object.getClass().getDeclaredFields();
        Map<String, String> resultMap = new LinkedHashMap();
        List<Field> fieldList = Arrays.stream(fields)
                .filter(field -> {
                    ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                    if (annotation != null && annotation.index() > 0) {
                        field.setAccessible(true);
                        return true;
                    }
                    return false;
                }).sorted(Comparator.comparing(field -> {
                    int index = 0;
                    ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                    if (annotation != null) {
                        index = annotation.index();
                    }
                    return index;
                })).collect(Collectors.toList());
        fieldList.forEach(e->{
            resultMap.put(e.getName(), e.getAnnotation(ExcelProperty.class).value()[0]);
        });
        return resultMap;
    }