Excel文件导入导出功能

package org.jeecg.common.util;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DatePattern;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.shiro.SecurityUtils;
import org.jeecg.common.system.vo.LoginUser;
import org.jeecgframework.poi.excel.ExcelExportUtil;
import org.jeecgframework.poi.excel.def.NormalExcelConstants;
import org.jeecgframework.poi.excel.entity.ExportParams;
import org.jeecgframework.poi.excel.entity.enmus.ExcelType;
import org.jeecgframework.poi.excel.view.JeecgEntityExcelView;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 导出返回信息
 */
@Slf4j
@RefreshScope
public class EasyExcelUtils {

    public static <T> ModelAndView exportXlsByData(HttpServletRequest request, List<T> pageList, Class<T> clazz, String title) {
        // Step.1 组装查询条件
        LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();

        // Step.2 获取导出数据
        List<T> exportList = pageList;

        // 过滤选中数据
        String selections = request.getParameter("selections");
        if (oConvertUtils.isNotEmpty(selections)) {
            List<String> selectionList = Arrays.asList(selections.split(","));
            if(Arrays.stream(clazz.getDeclaredFields()).filter(field -> field.getName().equals("id")).collect(Collectors.toList()).size()>0){
                exportList = pageList.stream().filter(item -> selectionList.contains(getId(item))).collect(Collectors.toList());
            }else{
                exportList = pageList;
            }
        } else {
            exportList = pageList;
        }

        // Step.3 AutoPoi 导出Excel
        ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
        mv.addObject(NormalExcelConstants.FILE_NAME, title); //此处设置的filename无效 ,前端会重更新设置一下
        mv.addObject(NormalExcelConstants.CLASS, clazz);
        //update-begin--Author:liusq  Date:20210126 for：图片导出报错，ImageBasePath未设置--------------------
        ExportParams  exportParams=new ExportParams(title + "报表", "导出人:" + sysUser.getRealname(), title);
        //update-end--Author:liusq  Date:20210126 for：图片导出报错，ImageBasePath未设置----------------------
        mv.addObject(NormalExcelConstants.PARAMS,exportParams);
        mv.addObject(NormalExcelConstants.DATA_LIST, exportList);
        return mv;
    }

    private static <T> String getId(T item) {
        try {
            return PropertyUtils.getProperty(item, "id").toString();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }


    public <T> void exportXlsByData(HttpServletResponse response, String tableName, List<T> list, Class<T> clazz) {
        String dateStr = LocalDateTime.now().format(DateTimeFormatter.ofPattern(DatePattern.PURE_DATETIME_PATTERN));
        String excelName = tableName + "-" + dateStr + ".xlsx";
        try (OutputStream outputStream = response.getOutputStream()) {
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName, "UTF-8"));
            response.addHeader("Pragma", "no-cache");
            response.addHeader("Cache-Control", "no-cache");

            EasyExcel.write(outputStream, clazz)
                    // 设置单元格宽度自适应
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    // 设置单元格高度和字体
                    //.registerWriteHandler(getHeightAndFontStrategy())
                    .sheet(excelName)
                    .doWrite(list);
            outputStream.flush();
            log.info("下载{}条记录到文件{}", list.size(), excelName);
        } catch (IOException e) {
            log.warn("导出"+tableName+"表格失败", e);
        }
    }


    public static <T> void createExcelData(List<T> list, Class<T> clazz, String fileName,String ctxPath) {
        //指定文件
        //多个map，对应了多个sheet
        List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
        Date start = new Date();
        if(oConvertUtils.listIsNotEmpty(list)){// 存在vulnerabilityList的情况
            Map<String, Object> map = new HashMap<String, Object>();
            ExportParams exportParams=new ExportParams( "报表", "导出人:系统管理员" , "报表");
            exportParams.setType(ExcelType.XSSF);
            map.put("title",exportParams);//表格Title
            map.put(NormalExcelConstants.PARAMS,exportParams);//表格Title
            map.put(NormalExcelConstants.CLASS,clazz);//表格对应实体
            map.put(NormalExcelConstants.DATA_LIST, list);//数据集合
            listMap.add(map);
        }

        File file = new File(ctxPath + File.separator + "excel" + File.separator );
        try {
            String os = System.getProperty("os.name");
            if (!file.exists()) {
                if(os.contains("Windows")){
                    file.createNewFile();
                }else{
                    file.setWritable(true, false);
                    file.mkdirs();
                }
            }
            String savePath = file.getPath() + File.separator + fileName;
            File saveFile = new File(savePath);
            Workbook workbook = ExcelExportUtil.exportExcel(listMap,ExcelType.XSSF);
            FileOutputStream out = new FileOutputStream(saveFile);
            workbook.write(out);//保存Excel文件
            out.close();//关闭文件流
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        log.info("创建了excel+++++++++++++++++++++++++++++++++++++"+fileName);
    }


    public static void createExcelData(String filename, Map<String, Object> map){

        Date start = new Date();
        Random random=new Random();
        String fileName = "D:/opt/upFiles/excel/alibaba.test"+random.nextInt()+".xlsx";
        //指定文件
        ExcelWriter excelWriter = EasyExcel.write(fileName).build();
        int i = 0;
        for (String sheetName : map.keySet())
        {
            List<T> list = (List<T>) Convert.toList(map.get(sheetName));
            //构建表1
            WriteSheet sheet = EasyExcel.writerSheet(i, sheetName).build();
            WriteTable table = EasyExcel.writerTable(i).head(T.class).needHead(true).build();
            excelWriter.write(list, sheet, table);
            i++;
            list.clear();
        }
        /// 千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
        System.out.println("生成预警数据完成时间:"+(new Date().getTime() - start.getTime()));

    }







}