java技术：easyexcel同时导出多个excel到web（zip压缩文件）

04-10 1344阅读 0评论

目录

环境设置

一、表头设计：

1、利用列表

2、注解形式(推荐)

二、导出zip（可实现任意个excel文件）

三、单元格策略

只要相同的列就合并

合并判断

四、动态标题实现

五、整合（动态标题+单元格合并）

实体类举例

六、感兴趣可看（封装函数说明）

参考：

环境设置

        
            com.alibaba
            easyexcel
            2.2.0-beta2
      

一、表头设计：

1、利用列表

private static List head() {
        List list = new ArrayList();
        List head0 = new ArrayList();
        head0.add("序号");
        list.add(head0);
        Map map = getHeader();
        map.forEach((k, v) -> {
            String deviceCategory = k;
            List ls = v;
            ls.forEach(e -> {
                List head = new ArrayList();
                head.add(deviceCategory);
                head.add(e);
                list.add(head);
            });
​
•    });
•    List head1 = new ArrayList();
•    head1.add("备注");
•    list.add(head1);
•    List head2 = new ArrayList();
•    head2.add("埋深");
•    list.add(head2);
•    return list;
}
private static Map getHeader() {
    Map map = new HashMap();
    List aList = new ArrayList();
    List sList = new ArrayList();
    List subList = new ArrayList();
    String column1 = "X";
    aList.add(column1);
    String column2 = "Y";
    aList.add(column2);
    String column3 = "B";
    sList.add(column3);
    String column4 = "L";
    sList.add(column4);
    String subColumn = "其它";
    subList.add(subColumn);
    subList.add("小计3");
    map.put("坐标", aList);
    map.put("经纬度", sList);
    return map;
}

2、注解形式(推荐)

@Data
@ApiModel("角色管理")
public class TSRoleVo extends ExcelModel {
​
@ExcelIgnore
@ApiModelProperty("id")
private String id;
​
•    
•    @ExcelProperty(value = {"角色表列表","导出人:${title}","角色名称"} , index = 0)
•    @ApiModelProperty(value = "角色名称")
•    @ColumnWidth(25)
•    private String roleName;//角色名称
•    
​
@ExcelProperty(value = {"角色表列表","导出人:${title}","角色编码"} , index = 1)
@ApiModelProperty(value = "角色编码")
@ColumnWidth(25)
private String roleCode;//角色编码
​
@ExcelProperty(value = {"角色表列表","导出人:${title}","部门权限组ID"} , index = 2)
@ApiModelProperty(value = "部门权限组ID")
@ColumnWidth(25)
private String departAgId;//组织机构ID  部门权限组ID
​
@Override
public boolean validation(Map validationArgs) {
    return false;
}
​
}
ps：
​
宽:@ColumnWidth(25)
​
内容的高:@ContentRowHeight(10)
​
表头的高：@HeadRowHeight(20)

二、导出zip（可实现任意个excel文件）

对于每个excel拥有两个sheet且 每个sheet实体类数据不同

public class ReceiveTaskUtils {
    public static void addFileToZip(File file, ZipOutputStream zos) throws IOException {
        InputStream fileInputStream = new FileInputStream(file);
        //zip中要放文件称为zipEntry
        ZipEntry zipEntry = new ZipEntry(file.getName());
        zos.putNextEntry(zipEntry);
        byte[] bytes = new byte[1024];
        int len;
        while ((len = fileInputStream.read(bytes)) != -1) {
            //读的内容会自动放到zip条目中，因此zipentry再输出流读完需要关闭
            zos.write(bytes, 0, len);
        }
        //要关闭这个 zos.closeEntry();
        zos.closeEntry();
        fileInputStream.close();
    }
​
    static void deleteFile(String path) {
        File file = new File(path);
        if (file.exists()) {
            file.delete();
        }
    }
​
    /**
     * 获取Date的月份
     */
    static int getMonth(Date Time) {
​
        LocalDate localDate = Time.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
        int month = localDate.getMonthValue();
        return month;
​
    }
    private static String formatToDate(Date receiveCliDate) {
​
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yy");
        String formattedDate = dateFormat.format(receiveCliDate);
        return formattedDate;
    }
    //写到本地{
    public static void export(ReceiveExportVo result, HttpServletResponse response, String type) throws Exception {
//设置输出流格式
        String name = (type.contains("已完成") ? "采购完成" : "领受任务");
        Date cliTime = (type.contains("已完成") ? CompleteCliDate : ReceiveCliDate);
        String month=String.valueOf(getMonth(cliTime));
        response.setContentType("application/zip");
        response.setCharacterEncoding("UTF-8");
        String fileName = name + System.currentTimeMillis() + ".zip";
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
//        创建excel文件
        String fileName1 = ".\单一来源.xlsx";
        //合并策略：
        int[] mergeColumeIndex ={1,2,3,4,5,6,7,8,9,10,11,12};
        //从第二行后开始合并
        // 调用合并单元格工具类，此工具类是没有根据合并，数据相同就合并了
        String titleDate=formatToDate(ReceiveCliDate);
        ExcelMergeUtil excelFillCellMergeStrategy = new ExcelMergeUtil(2,mergeColumeIndex,titleDate,month);
        //设置样式 标题和内容
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        log.info("创建excel之前");
        try (ExcelWriter excelWriter = EasyExcel.write(fileName1).build()) {
            WriteSheet writeSheet1 = EasyExcel.writerSheet(month + "月单一来源进行中项目统计")
                    .registerWriteHandler(excelFillCellMergeStrategy)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .head(SingReceiveExport1Vo.class).build();
            List singReceiveExport1VoList = result.getSingReceiveExport1VoList();
            excelWriter.write(singReceiveExport1VoList, writeSheet1);
            WriteSheet writeSheet2 = EasyExcel.writerSheet(month + "月已安排审价项目统计")
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(excelFillCellMergeStrategy)
                    .head(SingReceiveExport2Vo.class).build();
            List singReceiveExport2VoList = result.getSingReceiveExport2VoList();
            excelWriter.write(singReceiveExport2VoList, writeSheet2);
        }
        String fileName2 = ".\竞争型.xlsx";
        try (ExcelWriter excelWriter = EasyExcel.write(fileName2).build()) {
            WriteSheet writeSheet3 = EasyExcel.writerSheet(month + "月正在进行中竞争性采购项目")
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(excelFillCellMergeStrategy)
                    .head(CompeteReceiveExport1Vo.class).build();
            List competeReceiveExport1VoList = result.getCompeteReceiveExport1VoList();
            excelWriter.write(competeReceiveExport1VoList, writeSheet3);
            WriteSheet writeSheet4 = EasyExcel.writerSheet(month + "月已开标项目")
                    .registerWriteHandler(excelFillCellMergeStrategy)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .head(CompeteReceiveExport2Vo.class).build();
            List competeReceiveExport2VoList = result.getCompeteReceiveExport2VoList();
            excelWriter.write(competeReceiveExport2VoList, writeSheet4);
        }
        //压缩路径
//        String zipFilePath =".\"+".zip";
        // 创建输出流
        FileOutputStream fos = new FileOutputStream(fileName);
        ZipOutputStream zos = new ZipOutputStream(fos);
        // 压缩文件
        File file1 = new File(fileName1);
        File file2 = new File(fileName2);
        addFileToZip(file1, zos);
        addFileToZip(file2, zos);
        zos.close();
        fos.close();
        //将压缩文件输入流传给response输出流
        InputStream fileInputStream = new FileInputStream(fileName);
        OutputStream outputStream = response.getOutputStream();
        byte[] bytes = new byte[1024 * 8];
        int len;
        while((len=fileInputStream.read(bytes))!=-1) {
            outputStream.write(bytes,0,len);
        }
        fileInputStream.close();
        outputStream.close();
//        删除文件
        deleteFile(fileName1);
        deleteFile(fileName2);
        deleteFile(fileName);
    }
}
​
​

三、单元格策略

只要相同的列就合并（图片仅供参考）

java技术：easyexcel同时导出多个excel到web（zip压缩文件）

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.Data;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
​
@Data
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
    private int[] mergeColumnIndex;
    private int mergeRowIndex;
​
public ExcelFillCellMergeStrategy() {
}
​
public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
    this.mergeRowIndex = mergeRowIndex;
    this.mergeColumnIndex = mergeColumnIndex;
}
​
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
​
}
​
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
   //当前行
    int curRowIndex = cell.getRowIndex();
    //当前列
    int curColIndex = cell.getColumnIndex();
​
•    if (curRowIndex > mergeRowIndex) {
•        for (int i = 0; i  
 

合并判断（图片仅供参考）
 

java技术：easyexcel同时导出多个excel到web（zip压缩文件）
 
 

package com.ph.rfwg.util;
​
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
​
public class ExcelMergeUtil implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
​
public ExcelMergeUtil() {
}
​
public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex) {
    this.mergeRowIndex = mergeRowIndex;
    this.mergeColumnIndex = mergeColumnIndex;
}
​
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
​
    //当前行
    int curRowIndex = cell.getRowIndex();
    //当前列
    int curColIndex = cell.getColumnIndex();
​
    if (curRowIndex > mergeRowIndex) {
        for (int i = 0; i  

 
 

四、动态标题实现
 

public class CustomerTitleHandler implements CellWriteHandler {
    private String title;
    PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");
    public CustomerTitleHandler(String title) {
        this.title = title;
    }
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
        if (head！=null) {
        //获取一个标题列表
            List headNameList = head.getHeadNameList();
            if (CollectionUtils.isNotEmpty(headNameList)) {
                Properties properties = new Properties();
                //需要多个动态变量直接在后面加，properties相当于map会自动映射
                properties.setProperty("title", title);
                headNameList.set(0, placeholderHelper.replacePlaceholders(headNameList.get(0), properties));
            }
        }
    }
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                   CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                             List list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
}
 
 

五、整合（动态标题+单元格合并）
 

实体类举例
 
 
 
/**
     *需求单位联系人/电话
     */
    @ColumnWidth(25)
    @ExcelProperty(value={"${titleDate}","需求单位联系人/电话"})
//    @ExcelProperty(value={"","需求单位联系人/电话"})
    private String demandUnitPeople;
    /**
     *单一来源承制单位
     * plan
     */
    @ColumnWidth(40)
    @ExcelProperty(value={"单一来源正在进行中审价项目统计","单一来源承制单位"})
    private String singleSourceProvider;
 
 
    /**
     *承制单位联系人及电话
     */
    @ColumnWidth(40)
    @ExcelProperty(value={"单一来源正在进行中审价项目统计","承制单位联系人及电话"})
    private String manufacturerPhone;
 
 
 

package com.example.procurementmanagement.util;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.PropertyPlaceholderHelper;
import java.util.List;
import java.util.Properties;
/**
 * author:lanjie
 * 单元格合并策略
 * describe：合并相同字段的列，但需要判断首列是否合并（条件合并）
 * ps:可复用
 */
@Slf4j
public class ExcelMergeUtil implements CellWriteHandler {
    //需要合并的列数组
    private int[] mergeColumnIndex;
    //合并起始行
    private int mergeRowIndex;
    private String titleDate;
    private String titleMonth;
    PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");
    public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex,String titleDate,String titleMonth) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
        this.titleDate=titleDate;
        this.titleMonth=titleMonth;
    }
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
            List headNameList = head.getHeadNameList();
            if (CollectionUtils.isNotEmpty(headNameList)){
                Properties properties = new Properties();
                properties.setProperty("titleDate", titleDate);
                properties.setProperty("titleMonth", titleMonth);
                headNameList.set(0, placeholderHelper.replacePlaceholders(headNameList.get(0), properties));
                log.info("headNameList:{}",headNameList);
            }
        }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
            log.info("定义单元格以后！");
    }
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List

相关阅读：
1、主机不开怎么设置可以打印(主机不开怎么设置可以打印文件)
2、天选3060主机怎么样设置独显直连(天选3060主机怎么样设置独显直连功能)
3、电脑主机灯关了怎么设置开机密码(电脑主机灯关了怎么设置开机密码呢)
4、医院干部管理权限履行备案手续（医院干部管理权限履行备案手续流程）
5、全国应急管理厅操作证备案查询（全国应急管理厅操作证备案查询官网）
免责声明
本网站所收集的部分公开资料来源于AI生成和互联网，转载的目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
		

		
		

		文章版权声明：除非注明，否则均为主机测评原创文章，转载或复制请以超链接形式并注明出处。
			

	




	
相关阅读

	



  
  	
		

	
发表评论

	

	
	
	

		

		
		

	

	

		

			
		

	

	

		

			快捷回复：
			
			
			
			表情：
			
		

		
			

	


		

		

			
评论列表 （暂无评论，1344人围观）

		

		
 还没有评论，来说两句吧...
		
						
  

				

		
	





	
目录[+]