hutool导出excel工具和例子

最新推荐文章于 2024-10-03 08:26:33 发布

bananachong

最新推荐文章于 2024-10-03 08:26:33 发布

阅读量822

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/bananachong/article/details/107343100

版权

java 专栏收录该内容

28 篇文章 0 订阅

订阅专栏

1.导入依赖

<!-- hutool -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.3.7</version>
        </dependency>

2.代码示例

2.1controller层

@PostMapping(value = "/exportPaperExcel")
    public void exportExcel(HttpServletResponse response, @RequestBody SysExcelPaperDTO sysExcelPaperDTO) throws Exception{
        if (CollUtil.isEmpty(sysExcelPaperDTO.getCols())){
            throw new ExamServerException("导出列不可为空");
        }
        Map<String,String> map = MapUtil.of("paperid", "试卷编号");
        map.put("papername", "试卷名称");
        map.put("papertype", "试卷类型");
        map.put("starttime", "开始时间");
        map.put("endtime", "结束时间");
        map.put("username", "创建人");
        FileUtil.exportExcel(response,sysExcelPaperDTO.getCols(),map,syssPaperManageService.exportExcel(sysExcelPaperDTO));
    }

2.1.1传入的参数

@Data
public class SysExcelPaperDTO{
    
    @ApiModelProperty("导出字段")
    private List<String> cols;
}

2.2 service层

/**
     * 导出Excel
     * @param sysExcelPaperDTO
     * @return
     */
    public List<Map<String,Object>> exportExcel(SysExcelPaperDTO sysExcelPaperDTO){

        SysUser sysUser= CommonUtil.getLoginUser();
        List<SysPaperManageVO>list=syssPaperManageMapper.getPaperList(sysUser.getUsercode(),sysExcelPaperDTO.getPapername(),sysExcelPaperDTO.getPapertype(),null);
        List<Map<String,Object>> sysExamExcelVOS = new ArrayList<>();
        Map<String,Object> sysNoticeExcelVO;
        String[] cols = {"paperid","papername","papertype","starttime","endtime","username"};
        List listItem;
        if (CollUtil.isNotEmpty(list)){
            for (SysPaperManageVO SysExamVO : list) {
                listItem = Arrays.asList(SysExamVO.getPaperid(),SysExamVO.getPapername(),SysExamVO.getPapertype()
                        , DateUtil.format(SysExamVO.getStarttime(),"yyyy-MM-dd HH:mm:ss"),DateUtil.format(SysExamVO.getEndtime(),"yyyy-MM-dd HH:mm:ss"),SysExamVO.getUsername());
                sysNoticeExcelVO = new HashMap<>();
                for (int i = 0; i < cols.length; i++) {
                    if (sysExcelPaperDTO.getCols().contains(cols[i])){
                        sysNoticeExcelVO.put(cols[i],listItem.get(i));
                    }
                }
                sysExamExcelVOS.add(sysNoticeExcelVO);
            }
        }
        return sysExamExcelVOS;
    }

2.3 工具类 FileUtil

import cn.hutool.core.io.IoUtil;
import cn.hutool.core.text.csv.CsvUtil;
import cn.hutool.core.text.csv.CsvWriter;
import cn.hutool.core.util.CharsetUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.sax.handler.RowHandler;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.exam.config.SystemProperties;
import com.exam.constants.enums.FileTypeEnum;
import com.exam.constants.enums.ResourceTypeEnum;
import com.exam.constants.enums.UploadPathEnum;
import com.exam.entity.SysResource;
import com.exam.exception.ExamServerException;
import com.exam.listen.ExcelListener;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.*;
@Slf4j
public class FileUtil {
public static  void exportExcel(HttpServletResponse response,List<String> cols,Map<String,String> header,List data) throws Exception{
        ExcelWriter writer = ExcelUtil.getWriter();
        for (String col : cols) {
            writer.addHeaderAlias(col, header.get(col));
        }
        writer.write(data, true);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename="+ UUID.randomUUID().toString().replace("-","") +".xls");
        ServletOutputStream out= null;
        try {
            out = response.getOutputStream();
            writer.flush(out, true);
        } catch (Exception e) {
            throw e;
        }finally {
            // 关闭writer，释放内存
            writer.close();
        }
        IoUtil.close(out);
    }
}