关于Excel解析工具类（word 也可以）

最新推荐文章于 2024-05-14 15:50:36 发布

JackSparrow001

最新推荐文章于 2024-05-14 15:50:36 发布

阅读量802

点赞数

分类专栏：工具类文章标签： java 数据库大数据

本文链接：https://blog.csdn.net/m0_61115595/article/details/127934715

版权

工具类专栏收录该内容

2 篇文章 0 订阅

订阅专栏

你要准备的：

1.引入的依赖

2.你要映射对应字段的实体类

3. 调用工具类

1.首先要引入的依赖：

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel-core</artifactId>
            <version>3.1.0</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.6</version>
        </dependency>

2.映射的实体类（举例）

import cn.hutool.core.date.DatePattern;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.fastjson.annotation.JSONField;
import com.baomidou.mybatisplus.annotation.*;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.time.LocalDateTime;
import java.util.Date;

/**
 * 团队组建
 *
 * @date 2022-11-09 10:39:04
 */
@NoArgsConstructor
@AllArgsConstructor
@Data
public class RiskModel implements Serializable {


    /**
     * id
     */
    @ExcelProperty(value = "编号", index = 1)
    private String id;

    /**
     * 风险名称
     */
    @ExcelProperty(value = "风险名称", index = 2)
    private String name;
    /**
     * 风险来源
     */
    @ExcelProperty(value = "风险来源", index = 3)
    private String origin;
    /**
     * 概率
     */
    @ExcelProperty(value = "概率（P）", index = 4)
    private String probability;
    /**
     * 影响
     */
    @ExcelProperty(value = "影响(I)", index = 5)
    private String effect;



}

3. Utils工具类：

package com.dict.utils;

import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.dict.ExceptionHandler.BusException;
import com.dict.ExceptionHandler.BusMsgEnum;
import com.dict.config.AutoColumnWidthWriteHandler;
import com.dict.config.ModelExcelListener;
import com.dict.config.XHorizontalCellStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.Charsets;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xslf.usermodel.XMLSlideShow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @date 2022-11-7
 */
@Slf4j
public class FileUtil {

    /**
     * 导入文件解析数据
     * @param file 需要导入的文件
     * @param <T>
     * @param type 文件类型
     * @param rowNumber   从第几行开始解析
     * @return 返回解析后的实体类对象集合
     */
    public static <T> List<T> importExcel(MultipartFile file,Class<T> c,String type,int rowNumber) {
        if (file.isEmpty()) {
            log.error(type+BusMsgEnum.FILE_TEMPLATE_DOES_NOT_EXIST.getMsg());
            throw new RuntimeException(type+BusMsgEnum.FILE_TEMPLATE_DOES_NOT_EXIST.getMsg());
        }
        List<T> list;
        try {
            list = EasyExcel.read(file.getInputStream(), c, new ModelExcelListener()).sheet().headRowNumber(rowNumber).doReadSync();
        } catch (Exception e) {
            log.error(type+BusMsgEnum.TEMPLATE_EXPORT_EXCEPTION.getMsg(),e);
            throw new RuntimeException(type+BusMsgEnum.TEMPLATE_EXPORT_EXCEPTION.getMsg());
        }
        if (CollUtil.isEmpty(list)) {
            throw new RuntimeException(type + "导入的数据是空的!");
        }
        return list;
    }

    /**
     * 数据导出到excel,excel是通过流自动生成的
     * @param list  导出的数据
     * @param sheetName  sheet名
     * @param clas  excelModel类
     */
    public static <T> void exportData(List<T> list,String type , String sheetName, HttpServletResponse response, Class clas) {
        try ( OutputStream outputStream = response.getOutputStream();){
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode(sheetName+".xls", "utf-8"));
            response.setHeader("Access-Control-Expose-Headers", "content-Disposition");

            ExcelWriter writer = EasyExcel.write(outputStream).build();
            //WriteSheet sheet = EasyExcel.writerSheet(0, "sheet").head(clas).build();
            EasyExcel.write(outputStream)
                    .head(clas)
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet("sheet1")
                    .registerWriteHandler(new AutoColumnWidthWriteHandler())
                    .registerWriteHandler(new XHorizontalCellStyleStrategy())
                    .doWrite(list);
            //writer.write(list, sheet);
            writer.finish();
        }catch (FileNotFoundException e){
            log.error(type+BusMsgEnum.FILE_TEMPLATE_DOES_NOT_EXIST.getMsg(),e);
            throw new RuntimeException(type+BusMsgEnum.FILE_TEMPLATE_DOES_NOT_EXIST.getMsg());
        }catch (IOException e){
            log.error(type+BusMsgEnum.TEMPLATE_EXPORT_EXCEPTION.getMsg(),e);
            throw new RuntimeException(type+BusMsgEnum.TEMPLATE_EXPORT_EXCEPTION.getMsg());
        }

    }

    /**
     * excel导出数据，导出到固定模板里面
     * @param liste
     * @param templateUrl
     * @param response
     * @param fileName
     */
    public static void exportData(List<T> liste,String type,Map<String,String> map, String templateUrl, HttpServletResponse response, String fileName) {
        //这里是按照哪种格式响应给浏览器  这里是按照xlsx的格式
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        response.setContentType("application/vnd.ms-excel");
        //响应给浏览器的文本格式
        response.setCharacterEncoding(Charsets.UTF_8.name());
        try (OutputStream outputStream = response.getOutputStream();){

            ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(templateUrl).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(0,"第一个").build();
            FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.VERTICAL).forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(new FillWrapper("list", liste), fillConfig, writeSheet);
            excelWriter.fill(map, writeSheet);
            excelWriter.finish();

        }catch (FileNotFoundException e){
            log.error(type+BusMsgEnum.FILE_TEMPLATE_DOES_NOT_EXIST.getMsg(),e);
            throw new RuntimeException(type+BusMsgEnum.FILE_TEMPLATE_DOES_NOT_EXIST.getMsg());
        }catch (IOException e){
            log.error(type+BusMsgEnum.TEMPLATE_EXPORT_EXCEPTION.getMsg(),e);
            throw new RuntimeException(type+BusMsgEnum.TEMPLATE_EXPORT_EXCEPTION.getMsg());
        }

    }


    /**
     * 模板excel导出不带参数
     * @param excelResource 模板路径
     * @param downloadFileName 文件导出名
     * @return 返回解析后的实体类对象集合
     */
    public static void downloadTemplate(HttpServletResponse response,String excelResource,String downloadFileName,String type){

        try (FileInputStream fis = new FileInputStream(excelResource);
             OutputStream outputStream=response.getOutputStream();){
            Workbook workbook = new XSSFWorkbook(fis);
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode(downloadFileName, "utf-8"));
            response.setHeader("Access-Control-Expose-Headers", "content-Disposition");

            workbook.write(outputStream);
            outputStream.flush();
        }catch (FileNotFoundException e){
            log.error(type+BusMsgEnum.FILE_TEMPLATE_DOES_NOT_EXIST.getMsg(),e);
            throw new RuntimeException(type+BusMsgEnum.FILE_TEMPLATE_DOES_NOT_EXIST.getMsg());
        }catch (IOException e){
            log.error(type+BusMsgEnum.TEMPLATE_EXPORT_EXCEPTION.getMsg(),e);
            throw new RuntimeException(type+BusMsgEnum.TEMPLATE_EXPORT_EXCEPTION.getMsg());
        }

    }

    /**
     * 模板word导出,不需要带出参数
     * @param wordResource 模板路径
     * @param downloadFileName 文件导出名
     * @return 返回解析后的实体类对象集合
     */
    public static void downloadWordTemplate(HttpServletResponse response,String wordResource,String downloadFileName,String type){
        try (FileInputStream fis = new FileInputStream(wordResource);
             OutputStream outputStream=response.getOutputStream();){

            XWPFDocument document=new XWPFDocument (fis);
            response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-type", "application/octet-stream");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(downloadFileName, "UTF-8"));
            document.write(outputStream);
            outputStream.flush();
        }catch (FileNotFoundException e){
            log.error(type+BusMsgEnum.FILE_TEMPLATE_DOES_NOT_EXIST.getMsg(),e);
            throw new RuntimeException(type+BusMsgEnum.FILE_TEMPLATE_DOES_NOT_EXIST.getMsg());
        }catch (IOException e){
            log.error(type+BusMsgEnum.TEMPLATE_EXPORT_EXCEPTION.getMsg(),e);
            throw new RuntimeException(type+BusMsgEnum.TEMPLATE_EXPORT_EXCEPTION.getMsg());
        }

    }

    /**
     * 模板word导出,需要带出参数
     * @param wordResource 模板路径
     * @param downloadFileName 文件导出名
     * @return 返回解析后的实体类对象集合
     */
    public static void downloadWordTemplate(HttpServletResponse response,String type,String wordResource, String downloadFileName, Map<String,String> map, List<String[]> tableList){

        try {

            PoiWordUtils.changWord(response,wordResource,downloadFileName,map,tableList);

        }catch (Exception e){
            log.error(type+BusMsgEnum.TEMPLATE_EXPORT_EXCEPTION.getMsg(),e);
            throw new RuntimeException(type+BusMsgEnum.TEMPLATE_EXPORT_EXCEPTION.getMsg());
        }

    }

    /**
     * 模板ppt导出
     * @param wordResource 模板路径
     * @param downloadFileName 文件导出名
     * @return 返回解析后的实体类对象集合
     */
    public static void downloadPptTemplate(HttpServletResponse response,String wordResource,String downloadFileName,String type){
        try (FileInputStream fis = new FileInputStream(wordResource);
             OutputStream outputStream=response.getOutputStream();){

            XMLSlideShow ppt=new XMLSlideShow(fis);
            response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-type", "application/octet-stream");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(downloadFileName, "UTF-8"));
            ppt.write(outputStream);
            outputStream.flush();
        }catch (FileNotFoundException e){
            log.error(type+BusMsgEnum.FILE_TEMPLATE_DOES_NOT_EXIST.getMsg(),e);
            throw new RuntimeException(type+BusMsgEnum.FILE_TEMPLATE_DOES_NOT_EXIST.getMsg());
        }catch (IOException e){
            log.error(type+BusMsgEnum.TEMPLATE_EXPORT_EXCEPTION.getMsg(),e);
            throw new RuntimeException(type+BusMsgEnum.TEMPLATE_EXPORT_EXCEPTION.getMsg());
        }

    }

    /**
     * 导入
     * @param file 需要导入的文件
     * @param <T>
     * @return 返回解析后的实体类对象集合
     */
    public static <T> List<T> importExcelSheet(MultipartFile file,int sheet,Class<T> c){
        if (file == null){
            return null;
        }
        List<T> list = null;
        try {
            list = EasyExcel.read(file.getInputStream(),c,new ModelExcelListener()).sheet(sheet).doReadSync();
        }catch (NoSuchElementException e){
            e.printStackTrace();
        } catch (Exception e) {
            throw new BusException(BusMsgEnum.FILE_FORMAT_WRONG);
        }
        return list;
    }

}