easyexcel导入导出

千次阅读

2022-01-14 14:44:03

一、环境

1、开发工具：idea2018.1

2、jar 管理：maven 3.3.3

3、项目类型:springboot

二、pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.6</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.ygq</groupId>
    <artifactId>apps</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>apps</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <!--springboot项目初始maven-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <!--mysql连接-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-core</artifactId>
            <version>3.4.1</version>
        </dependency>


        <!--easyexcel 核心坐标-若整合到自己项目仅导入以下两个即可-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
        <dependency>
            <groupId>com.github.whvcse</groupId>
            <artifactId>easy-captcha</artifactId>
            <version>1.6.2</version>
        </dependency>

        <!--工具包BeanUtils-->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.6</version>
        </dependency>


    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

三、代码

1、ExcelUtils (核心工具类)


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.DateUtils;
import com.ygq.apps.domain.Student;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * excel工具类
 *
 */
public class ExcelUtils {

    /**
     * Excel导出
     *
     * @param response      response
     * @param fileName      文件名
     * @param sheetName     sheetName
     * @param list          数据List
     * @param pojoClass     对象Class
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String sheetName, List<?> list,
                                     Class<?> pojoClass) throws IOException {
        if(StringUtils.isBlank(fileName)){
            //当前日期
            fileName = DateUtils.format(new Date());
        }

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" +  fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), pojoClass).sheet(sheetName).doWrite(list);
    }

    /**
     * Excel导出，先sourceList转换成List<targetClass>，再导出
     *
     * @param response      response
     * @param fileName      文件名
     * @param sheetName     sheetName
     * @param sourceList    原数据List
     * @param targetClass   目标对象Class
     */
    public static void exportExcelToTarget(HttpServletResponse response, String fileName, String sheetName, List<?> sourceList,
                                     Class<?> targetClass) throws Exception {
        List<Object> targetList = new ArrayList (sourceList.size());
        for(Object source : sourceList){
            Object target = targetClass.newInstance();
            BeanUtils.copyProperties(source, target);
            targetList.add(target);
        }
        exportExcel(response, fileName, sheetName, targetList, targetClass);
    }

    public static List<Object> importExcelToTarget( MultipartFile file,Class<?> targetClass) throws Exception {
        List<Object> list = EasyExcel.read(file.getInputStream()).head(targetClass).sheet().doReadSync();
        return  list;
    }

}

2、controller层接口


@RestController
@RequestMapping("excel")
public class EasyExcelController {

    @Autowired
    StudentService studentService;

    /**
     * 导入
     * @param file
     */
    @PostMapping("import")
    public void importExcel(@RequestParam("file") MultipartFile file) {
        try {
            List<Object> list = ExcelUtils.importExcelToTarget(file, Student.class);
            for (Object student : list) {
                Student stu= (Student) student;
               System.out.println(stu);
            }
        } catch (Exception e) {
            System.out.println("导入失败" + e);
        }
    }

    /**
     * 导出
     * @param response
     */
    @GetMapping("export")
    public void exporttExcel(HttpServletResponse response) {
        try {
            List<Student> list = studentService.list();
            ExcelUtils.exportExcel(response,null,null,list,Student.class);
        } catch (Exception e) {
            System.out.println("导出失败" + e);
        }
    }


    @GetMapping("list")
    public List<Student> list() {
        List<Student> list = studentService.list();
        System.out.println(list);
        return list;
    }
}

3、实体注解配置方式

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.*;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    @ExcelProperty("用户id",index=0)
    private Long id;
    @ExcelProperty("姓名",index=1)
    private String name;
    @ExcelProperty("年龄",index=2)
    private Integer age;
    @ExcelProperty("性别",index = 3,converter = SexConverter.class)
    private Integer sex;
    @ExcelProperty("地址",index=4)//导出Excel表格的字段名称
    private String address;

    @ExcelIgnore//导出时忽略某个字段
    private Long product_id;
}

字段转换

import com.alibaba.excel.enums.CellDataTypeEnum;
import  com.alibaba.excel.converters.Converter;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

public class SexConverter implements Converter<Integer> {
	@Override
	public Class supportJavaTypeKey() {
		return null;
	}

	@Override
	public CellDataTypeEnum supportExcelTypeKey() {
		return null;
	}

	@Override
	public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
		return null;
	}

	@Override
	public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
		if(integer == 1){
			return new CellData("男");
		}else {
			return new CellData("女");
		}
	}

}

四、多sheet导入导出

4.1 多sheet导出

public class Test{
    @Data
    @ColumnWidth(20)
    public static class TestVO {
        @ExcelProperty( value = "姓名",index = 0)
        private String name;
        @ExcelProperty( value = "年龄",index = 1)
        private int age;
        @ExcelProperty( value = "学校",index = 2)
        private String school;
    }
     /**
     * 多个sheet导入测试
     * @throws FileNotFoundException
     */
    @Test
    public void sheetImport() throws FileNotFoundException {
        // 输出流
        OutputStream outputStream = null;
        outputStream = new FileOutputStream(new File("D:/1.xlsx"));

        // 导出的数据
        List<TestVO> dataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            TestVO testVO = new TestVO();
            testVO.setAge(i + 20);
            testVO.setName("vo" + i);
            testVO.setSchool("school" + i);
            dataList.add(testVO);
        }

        // 标题
        List<String> headList = Arrays.asList("姓名", "年龄", "学校");

        // 测试多sheel导出
        ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
        WriteSheet test1 = EasyExcel.writerSheet(0, "test1").head(TestVO.class).build();
        WriteSheet test2 = EasyExcel.writerSheet(1, "test2").head(TestVO.class).build();
        excelWriter.write(dataList,test1).write(dataList,test2);
        excelWriter.finish();
    }
}

如果导出需加样式, 示例

        // 表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 单元格样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 初始化表格样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        WriteSheet test1 = EasyExcel.writerSheet(0, "test1").head(TestVO.class).
                registerWriteHandler(horizontalCellStyleStrategy).build();

4.2 多sheet导入

public class Test{
    @Data
    @ColumnWidth(20)
    public static class TestVO {
        @ExcelProperty( value = "姓名",index = 0)
        private String name;
        @ExcelProperty( value = "年龄",index = 1)
        private int age;
        @ExcelProperty( value = "学校",index = 2)
        private String school;
    }
    @Data
    @ColumnWidth(20)
    public static class TestVO1 {
        @ExcelProperty( value = "姓名",index = 0)
        private String name;
        @ExcelProperty( value = "年龄",index = 1)
        private int age;
        @ExcelProperty( value = "学校",index = 2)
        private String school;
    }
    /**
     * 测试导入多个sheet导入
     * @throws Exception
     */
    @Test
    public void read() throws Exception {
        String filePath = "D:/1.xlsx";
        InputStream inputStream = null;
        inputStream = new FileInputStream(new File(filePath));
        AnalysisEventListenerImpl<Object> listener = new AnalysisEventListenerImpl<>();
        ExcelReader excelReader = EasyExcel.read(inputStream,listener).build();
        // 第一个sheet读取类型
        ReadSheet readSheet1 = EasyExcel.readSheet(0).head(TestVO.class).build();
        // 第二个sheet读取类型
        ReadSheet readSheet2 = EasyExcel.readSheet(1).head(TestVO1.class).build();
        // 开始读取第一个sheet
        excelReader.read(readSheet1);
        List<Object> list = listener.getDatas();
        list.forEach((user)->{
            TestVO user1= (TestVO) user;
            System.out.println(user1.getName()+", "+user1.getAge()+", "+user1.getSchool());
        });
        // 清空之前的数据
        listener.getDatas().clear();
        // 开始读取第二个sheet
        excelReader.read(readSheet2);
        System.out.println("---------------------------------");
        List<Object> list2 = listener.getDatas();
        list2.forEach((user)->{
            TestVO1 user2= (TestVO1) user;
            System.out.println(user2.getName()+", "+user2.getAge()+", "+user2.getSchool());
        });
    }
}

java excel

更多相关内容

java使用EasyExcel导入导出excel（csdn）————程序.pdf
2021-12-01 22:37:35

java使用EasyExcel导入导出excel（csdn）————程序

收起

EasyExcel导入导出

2022-06-27 20:57:02

EasyExcel导入导出

Spring Boot + EasyExcel 导入导出

一常用注解

@ExcelProperty 指定当前字段对应excel中的那一列。可以根据名字或者Index去匹配。当然也可以不写，默认第一个字段就是index=0，以此类推。千万注意，要么全部不写，要么全部用index，要么全部用名字去匹配。千万别三个混着用，除非你非常了解源代码中三个混着用怎么去排序的。

@ExcelIgnore 默认所有字段都会和excel去匹配，加了这个注解会忽略该字段

@DateTimeFormat 日期转换，用String去接收excel日期格式的数据会调用这个注解。里面的value参照java.text.SimpleDateFormat

@NumberFormat 数字转换，用String去接收excel数字格式的数据会调用这个注解。里面的value参照java.text.DecimalFormat

@ExcelIgnoreUnannotated默认不加ExcelProperty 的注解的都会参与读写,加了不会参与

二依赖

<!-- easyexcel 主要依赖 -->
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>2.1.4</version>
</dependency>

三监听

/**
 * EasyExcel 导入监听
 */
public class ExcelListener extends AnalysisEventListener {
    //可以通过实例获取该值
    private List<Object> datas = new ArrayList<Object>();
 
    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        datas.add(o);//数据存储到list，供批量处理，或后续自己业务逻辑处理。
        doSomething(o);//根据自己业务做处理
    }
 
    private void doSomething(Object object) {
        //1、入库调用接口(得到数据后进行处理(存储到数据库))
    }
 
    //返回结果集对象
    public List<Object> getDatas() {
        return datas;
    }
 
    public void setDatas(List<Object> datas) {
        this.datas = datas;
    }
 
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // datas.clear();//解析结束销毁不用的资源
    }
}

注意

	监听器中不能直接使用@Autowired和@Resource注入,要使用构造方法
	/**
	 * 提供带参构造方法，在这里需要通过构造方法的方式获取对应的service层
	 * 谁调用这个监听器谁提供需要的service
	 * @param clueService
	 */
	public ExcelListener(ITbClueService clueService) {
		this.clueService = clueService;
		this.resultDTO = new ImportResultDTO();
	}

四实体类(用于封装excel的每一行数据)

@Data
public class TbClueExcelVo{


    /** 客户手机号 手机号(11位手机号，不可有空格) */
    @ExcelProperty(value = "手机号(11位手机号，不可有空格)",index = 0)
    private String phone;

    /** 渠道 */
    @ExcelProperty(value = "渠道来源",index = 1)
    private String channel;
    
    /** 活动编号 (来源于活动列表8位字母或数字)*/
    @ExcelProperty(value = "活动编号(来源于活动列表8位字母或数字)",index = 2)
    private String activityCode;


    /** "客户姓名 **/
    @ExcelProperty(value = "客户姓名",index = 3)
    private String name;
    
    /** 意向学科 */
    @ExcelProperty(value = "意向学科",index = 4)
    private String subject;

    /** 意向级别 */
    @ExcelProperty(value = "意向级别",index = 5)
    private String level;
    
    /** 性别 */
    @ExcelProperty(value = "性别",index = 6)
    private String sex;

    /** 年龄 */
    @ExcelProperty(value = "年龄",index = 7)
    private Long age;
}

五 controller

public R importData(MultipartFile file) throws Exception {
	ExcelListener excelListener = new ExcelListener(tbClueService);//tb..Service防止空指针
	EasyExcel.read(file.getInputStream(), TbClueExcelVo.class, excelListener).sheet().doRead();
	return R.success(excelListener.getDatas());
}

六、接口导出Excel (HttpServletResponse response, HttpServletRequest request)

hutool工具类实现

try {
    String filenames = "111111";
    String userAgent = request.getHeader("User-Agent");
    if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
        filenames = URLEncoder.encode(filenames, "UTF-8");
    } else {
        filenames = new String(filenames.getBytes("UTF-8"), "ISO-8859-1");
    }
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    response.addHeader("Content-Disposition", "filename=" + filenames + ".xlsx");
    EasyExcel.write(response.getOutputStream(), Test.class)
        .sheet("sheet")
        //此步骤是开启EasyEXCEL自适应列宽
        .registerWriteHandler(new CustomCellWriteHandler())
        .doWrite(testList);
} catch (Exception e) {
}

自适应列宽

/**

 * EasyEXCEL自适应列宽
 * 在导出时注册registerWriteHandler(new CustomCellWriteHandler())
 */
public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

七本地导入、导出

List<Test> testList = new ArrayList<>();
try {
    String strUrl = "C:\\Users\\Administrator\\Desktop\\json.xlsx";
    File multipartFile = new File(strUrl);
    InputStream inputStream = new FileInputStream(multipartFile);
    //实例化实现了AnalysisEventListener接口的类
    ExcelListener listener = new ExcelListener();
    //传入参数
    ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener);
    //读取信息
    excelReader.read(new Sheet(1, 0, Test.class));
    //获取数据
    List<Object> list = listener.getDatas();
    if (list.size() > 1) {
        for (int i = 0; i < list.size(); i++) {
            Testobj = (Test) list.get(i);
        }
    }
} catch (Exception e) {
    System.out.println(e.getMessage());
}
try {
    String strUrl = "C:\\Users\\Administrator\\Desktop\\json"+System.currentTimeMillis()+".xlsx";
    EasyExcel.write(strUrl,Test.class).sheet("sheet").doWrite(testList);
} catch (Exception e) {
}

收起

展开全文

java

easyexcel导入导出

2022-05-15 17:23:58

'收起' : '展开' }} 查询重置列表导入模板下载导入导出 import request from '@/utils/request' import dowrequest from "@/utils/downloadrequest"; /** * 下载导入模板 * @param {*} id */ export function ...

<template>
  <div>
    <div class="elian-box">
      <div class="elian-title">
        <i></i>
        <div>查询</div>
      </div>
      <el-form
        ref="form"
        :model="form"
        label-width="120px"
        @keyup.enter.native.prevent="formSearch"
      >
        <el-row :gutter="10">

          <el-col :span="6" class="form-button-group">
            <el-form-item label-width="0">
              <el-button @click="collapsed = !collapsed"
                >{{ collapsed ? '收起' : '展开' }}
                <i
                  :class="{
                    'el-icon-arrow-down': !collapsed,
                    'el-icon-arrow-up': collapsed
                  }"
                />
              </el-button>
              <el-button
                type="primary"
                :loading="gridLoading"
                @click="formSearch()"
                >查询</el-button
              >
              <el-button @click="clearForm('form'), formSearch()"
                >重置</el-button
              >
            </el-form-item>
          </el-col>
        </el-row>
      </el-form>
    </div>
    <elian-tool>
      <template #left>
        <div class="elian-title">
          <i></i>
          <div>列表</div>
        </div></template
      >
      <template #right>
        <el-button size="small" @click="downloadTemplateExcel" style="float: left;margin-top: 10px;margin-right: 10px">导入模板下载</el-button>
        <el-upload
          :action="importExcel()"
          :file-list="fileList"
          :limit="9"
          :data="routerInfo"
          :show-file-list="false"
          :on-success="handleSuccess"
          :filetype="['xlxs']"
          :headers="$download.addHeadToken()"
          style="float: left"
        >
        <el-button type="primary" style="margin-right: 10px">导入</el-button>
        </el-upload>
        <el-button type="primary" @click="exportExcel" >导出</el-button>
      </template>
    </elian-tool>
    <div class="elian-box">
      <elian-grid
        ref="table"
        :data="tableData"
        :loading="gridLoading"
        :page-size="pageSize"
        :page-sizes="pageSizes"
        :current-page="currentPage"
        :total="total"
        height="200"
        resize
        @pagination-current-change="paginationCurrentChange"
        @pagination-size-change="paginationSizeChange"
        @selection-change="selectionChange"
      >
        <el-table-column
          label="序号"
          type="index"
          
          fixed
          :index="handleIndex"
          width="50"
        >
        </el-table-column>
        <el-table-column
          prop="tenditmId"
          label="招标项目代码"
          min-width="240"
          
          show-overflow-tooltip
        >
        </el-table-column>
      </elian-grid>
    </div>
  </div>
</template>
<script>
import gridMixins from '@/components/ElianGrid/grid-mixins' //列表方法引入
import formMixins from '@/mixins/form' //form方法引入
import download from "downloadjs";
import { getToken } from "@/utils/auth";
import { queryDrugDclaFilterPage, deleteDrugDclaFilterById, downloadTemplateExcel, importExcel, exportExcel } from '@/api/std/drugDclaFilter' //列表接口引入
export default {
  props: {
    routerInfo: {
      type: Object
    }
  },
  components: {},
  mixins: [gridMixins, formMixins],
  data() {
    return {
      fileList:[],
      form: {},
      tableData: []
    }
  },
  created() {},
  methods: {
    getToken,
    importExcel,
    //多选的事件
    selectionChange() {},
    // 列表 数据获取
    async getList() {
      let response
      this.gridLoading = true
      try {
        this.queryData.confirmStas = 0
        response = await queryDrugDclaFilterPage(this.queryData)
      } catch (error) {
        this.gridLoading = false
        return
      }
      const { current, total, records } = response.data
      this.tableData = records
      this.currentPage = current
      this.total = total
      this.gridLoading = false
    },

    deletedetel(drugInfoId) {
      this.$confirm('是否确认删除?', '提示', {
        confirmButtonText: '确定',
        cancelButtonText: '取消',
        type: 'warning'
      })
        .then(() => {
          deleteDrugDclaFilterById(drugInfoId).then(res => {
            if (res.success === true) {
              this.$message({
                type: 'success',
                message: '删除成功!'
              })
              this.getList()
            }
          })
        })
        .catch(() => {
          this.$message({
            type: 'info',
            message: '已取消删除'
          })
        })
    },
    downloadTemplateExcel(){
      this.$confirm('是否下载导入模板?', '提示', {
        confirmButtonText: '确定',
        cancelButtonText: '取消',
        type: 'warning'
      }).then(() => {
        downloadTemplateExcel().then(resp => {
          this.$message.success("导入下载模板成功!")
          let mimetypes =
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' // 此mimetype根据文档类型切换
          let blob = new Blob([resp.data], {type: mimetypes})
          let filename = decodeURI(resp.headers.filename)
          download(blob, filename, mimetypes)
        })
      })
    },
    exportExcel(){
      this.$confirm('是否导出?', '提示', {
        confirmButtonText: '确定',
        cancelButtonText: '取消',
        type: 'warning'
      }).then(() => {
        exportExcel(this.queryData).then(resp => {
          this.$message.success("导出成功!")
          let mimetypes =
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' // 此mimetype根据文档类型切换
          let blob = new Blob([resp.data], {type: mimetypes})
          let filename = decodeURI(resp.headers.filename)
          download(blob, filename, mimetypes)
        })
      })
    },
    handleSuccess(res){
      if (res.success==true){
        this.$message.success("导入成功")
      }else{
        this.$message.warning("导入失败")
      }
      this.getList();
    },
  }
}
</script>

import request from '@/utils/request'
import dowrequest from "@/utils/downloadrequest";

/**
 * 下载导入模板
 * @param {*} id
 */
export function downloadTemplateExcel() {
  return dowrequest.get(
    '/tps-local-bd/web/std/dlcg/drugDclaFilter/downloadTemplateExcel'
  )
}
/**
 * 导入
 * @returns {string}
 */
export function importExcel() {
  return '/tps-local-bd/web/std/dlcg/drugDclaFilter/import';
}

/**
 * 导出
 * @returns {string}
 */
export function exportExcel(params) {
  return dowrequest.post(
    '/tps-local-bd/web/std/dlcg/drugDclaFilter/export',params
  )
}

controller
/**
	 * 下载模板文件
	 *
	 * @param response
	 */
	@SneakyThrows
	@ApiOperation(value = "下载导入模板", notes = "下载导入模板")
	@GetMapping("/downloadTemplateExcel")
	public void downloadTemplateExcel(HttpServletResponse response) {
		List<ExDrugDclaFilter> data = new ArrayList<>();
		Map<Integer,String[]> mapDropDown = new HashMap<>();
		//0未确认，1已确认，2已生成
		String[] confirmStas = {"未确认","已确认","已生成"};
		mapDropDown.put(4,confirmStas);
		ExcelUtil.writeExcel(response, data,mapDropDown,"中选申报药品信息导入模板","sheet", ExDrugDclaFilter.class);
	}

	@PostMapping("/export")
	@ApiOperation(value = "excel导出", notes = "Excel导出")
	public void excelDownload(HttpServletResponse response, @RequestBody DrugDclaFilterDTO drugDclaFilterDTO) {
		drugDclaFilterService.excelDownload(response,drugDclaFilterDTO);
	}

	@SneakyThrows
	@PostMapping("/import")
	@ApiOperation(value = "excel导入", notes = "excel导入")
	public Result<String> excelImport(@RequestParam("file") MultipartFile file, @CurrentUserContext UserContext userContext) {
		if (ObjectUtil.isEmpty(file)){
			return Results.failure("file 为空！");
		}
		return drugDclaFilterService.excelImport(file.getInputStream(), userContext);
	}

service
    
@Override
    @SneakyThrows
    public Result<String> excelImport(@NotNull InputStream inputStream, UserContext userContext) {
        String redisKey = "drugDclaFilterExcel"+ IdWorker.getIdStr();
        EasyExcel.read(inputStream, ExDrugDclaFilter.class, new DrugDclaFilterExcelDataListener(
                drugDclaFilterBO, userContext, redisKey, cacheUtil)).sheet().doRead();
        String errStr = cacheUtil.get(redisKey,String.class);
        // 如果有异常信息返回失败
        if (StringUtils.isNotBlank(errStr)){
            log.debug(errStr);
            // 清空
            cacheUtil.del(redisKey);
            return Results.failure(errStr);
        }else {
            return Results.success("导入成功！");
        }
    }

    @SneakyThrows
    @Override
    public void excelDownload(HttpServletResponse response, DrugDclaFilterDTO drugDclaFilterDTO) {
        // 查询数据 最好根据查询日期查询 临时写死5001 测试 可以切sheet
        drugDclaFilterDTO.setSize(5001);
        List<DrugDclaFilterDTO> list = this.queryList(drugDclaFilterDTO);
        if (CollectionUtil.isNotEmpty(list)){
            ExcelUtil.writeExcel(response,DozerUtil.convertList(list, ExDrugDclaFilter.class),
                    "中选申报药品信息导出", ExDrugDclaFilter.class);
        }
    }

dto

@Data
public class ExDrugDclaFilter {

    @ColumnWidth(15)
    private String tenditmId;
}


package cn.hsa.spp.std.listener;

import cn.hsa.spp.comm.cache.CacheUtil;
import cn.hsa.spp.comm.common.context.UserContext;
import cn.hsa.spp.comm.common.exception.Result;
import cn.hsa.spp.comm.util.DozerUtil;
import cn.hsa.spp.comm.util.ValidationUtil;
import cn.hsa.spp.std.bo.DrugDclaFilterBO;
import cn.hsa.spp.std.dao.model.DrugDclaFilterDO;
import cn.hsa.spp.std.dto.ExDrugDclaFilter;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import lombok.extern.slf4j.Slf4j;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.concurrent.TimeUnit;

/**
 * EXCEL导入的监听器
 *
 * @author songlc
 * @version V1.0
 * @since 2020-9-02 19:45
 */
@Slf4j
public class DrugDclaFilterExcelDataListener extends AnalysisEventListener<ExDrugDclaFilter> {
    /**
     * 每隔3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 3000;

    private DrugDclaFilterBO drugDclaFilterBO;

    /**
     * 用户信息
     */
    private UserContext userContext;

    /**
     * 缓存工具
     */
    private CacheUtil cacheUtil;

    /**
     * 用来存储异常信息
     */
    private String redisKey;

    /**
     * 异常信息
     */
    private StringBuffer errorStr = new StringBuffer();

    List<DrugDclaFilterDO> list = new ArrayList<>();


    List<DrugDclaFilterDO> listCheck = new ArrayList<>();


    public DrugDclaFilterExcelDataListener(DrugDclaFilterBO drugDclaFilterBO,UserContext userContext, String redisKey, CacheUtil cacheUtil) {
        this.drugDclaFilterBO = drugDclaFilterBO;
        this.userContext = userContext;
        this.redisKey = redisKey;
        this.cacheUtil = cacheUtil;
    }

    @Override
    public void invoke(ExDrugDclaFilter exDrugDclaFilter, AnalysisContext analysisContext) {
        // 逻辑判断 并添加到List容器中
        Result<String> result = ValidationUtil.fastFailValidate(exDrugDclaFilter);
        if (ObjectUtil.isNotEmpty(result) && result.getCode() == 0){
            DrugDclaFilterDO drugDclaFilterDO = DozerUtil.convert(exDrugDclaFilter, DrugDclaFilterDO.class);
            //Optional.ofNullable(drugDclaFilterDO).map(o -> list.add(o));
            Optional.ofNullable(drugDclaFilterDO).map(o -> listCheck.add(o));
        }else {
            errorStr.append("第")
                    .append(analysisContext.readRowHolder().getRowIndex())
                    .append("行:")
                    .append(result.getMessage())
                    .append(" ");
        }
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
/*        if (list.size() >= BATCH_COUNT) {
            drugDclaFilterBO.saveBatch(list);
            // 存储完成清理 list
            list.clear();
        }*/
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 收尾
/*        if (CollectionUtil.isNotEmpty(list)){
            drugDclaFilterBO.saveBatch(list);
        }*/
        // 存缓存 调用方获取展示
        cacheUtil.set(redisKey,errorStr.toString(),60, TimeUnit.SECONDS);
        // 没有异常信息 调用导入功能
        if (StrUtil.isBlank(errorStr.toString())){
            if (CollectionUtil.isNotEmpty(listCheck)){
                // 填充数据
                listCheck.forEach(o -> this.convertBaseDo(o,userContext));
                drugDclaFilterBO.saveOrUpdateAll(listCheck);
            }
        }

       /* Map<String, List<DrugDclaFilterDO>> collect = listCheck.parallelStream().collect(Collectors.groupingBy(o -> o.getDrugCode() + "_" +
                o.getTenditmId() + "_0"));

        List<DrugDclaFilterDO> addList = new ArrayList<>();
        List<DrugDclaFilterDO> updateList = new ArrayList<>();
        for (Map.Entry<String,List<DrugDclaFilterDO>> entry : collect.entrySet()){
            if (entry.getValue().size() > 1){
                updateList.addAll(entry.getValue());
            }else {
                addList.addAll(entry.getValue());
            }
        }
        if (CollectionUtil.isNotEmpty(addList)){
            drugDclaFilterBO.saveBatch(list);
        }
        if (CollectionUtil.isNotEmpty(updateList)){
            drugDclaFilterBO.updateAll(updateList);
        }*/
    }

    /**
     * 转换
     * @param userContext
     */
    public DrugDclaFilterDO convertBaseDo(DrugDclaFilterDO t, UserContext userContext){
        t.setDrugInfoId(IdWorker.getIdStr());
        t.setConfirmStas("0");
        t.setRid(IdWorker.getIdStr());
        t.setCrterId(userContext.getUserId());
        t.setCrterName(userContext.getUserName());
        t.setCrteOptinsNo(userContext.getOrgId());
        t.setCrteTime(LocalDateTime.now());
        t.setOptTime(LocalDateTime.now());
        t.setOpterId(userContext.getUserId());
        t.setOpterName(userContext.getUserName());
        t.setOptinsNo(userContext.getOrgId());
        t.setUpdtTime(LocalDateTime.now());
        t.setInvdFlag("0");
        return t;
    }
}

工具类

package cn.hsa.spp.comm.util;


import cn.hsa.spp.comm.common.exception.ServiceException;
import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * EXCEL的工具类
 *
 * @author liwenjun
 * @version V1.0
 * @since 2019-12-02 18:03
 */
@Slf4j
public class ExcelUtil {

    private static final int MAXROWS = 5000;

    /**
     * 生成EXCEL工具类
     *
     * @param fileName
     * @param sheetName
     * @param data
     * @param clazz
     * @throws FileNotFoundException
     */
    public static void expExcel(String fileName, String sheetName, List data, Class clazz) {
        // 生成EXCEL并指定输出路径
        OutputStream out = null;
        ExcelWriter excelWriter = null;
        try {
            out = new FileOutputStream(fileName);
            excelWriter = EasyExcel.write(out, clazz).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(data, writeSheet);
        } catch (Exception e) {
            throw new ServiceException("导出EXCEL异常");
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                log.info(LogUtil.getBaseLog() + "生成EXCEL异常" + e.getMessage());
            }
        }
    }

    private ExcelUtil() {
    }

    /**
     * excel下载
     *
     * @param response  返回对象
     * @param fileName  文件名
     * @param sheetName 页签名称
     * @param head      对象
     * @param data      数据
     */
    public static void writeExcel(HttpServletResponse response, String fileName, String sheetName, Class head, List data) {
        try (OutputStream outputStream = response.getOutputStream()) {
            setRespHeader(response, fileName);

            EasyExcel.write(outputStream, head).sheet(sheetName).doWrite(data);
        } catch (IOException e) {
            log.error("excel下载失败", e);
            throw new ServiceException("excel下载失败");
        }
    }

    /**
     * 设置响应头
     *
     * @param response
     * @param fileName
     * @throws UnsupportedEncodingException
     */
    public static void setRespHeader(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileNameUrl = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("content-disposition", "attachment;filename=" + fileNameUrl + ".xlsx");
        response.setHeader("filename", fileNameUrl + ".xlsx");
    }

    /**
     * 获取默认表头内容的样式
     * @return
     */
    private static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy(){
        /** 表头样式 **/
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景色（浅灰色）
        // 可以参考：https://www.cnblogs.com/vofill/p/11230387.html
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 字体大小
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        /** 内容样式 **/
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 内容字体样式（名称、大小）
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //设置内容垂直居中对齐
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置内容水平居中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 头样式与内容样式合并
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 导出
     * @param response
     * @param data
     * @param fileName
     * @param sheetName
     * @param clazz
     * @throws Exception
     */
    public static void writeExcel(HttpServletResponse response, List<?> data, String fileName, String sheetName, Class<?> clazz) throws Exception {
        long exportStartTime = System.currentTimeMillis();
        log.info("报表导出Size: "+data.size()+"条。");
        setRespHeader(response,fileName);
        EasyExcel.write(response.getOutputStream(), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(getDefaultHorizontalCellStyleStrategy()).doWrite(data);
        log.debug("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
    }

    /**
     * 导出 下拉框
     * @param response
     * @param data
     * @param mapDropDown Map<Integer,String[]> key是列的index 从0开始  value 是数据集
     * @param fileName
     * @param sheetName
     * @param clazz
     */
    @SneakyThrows
    public static void writeExcel(HttpServletResponse response, List<?> data, Map<Integer,String[]> mapDropDown, String fileName, String sheetName, Class<?> clazz){
        long exportStartTime = System.currentTimeMillis();
        log.info("报表导出Size: "+data.size()+"条。");
        setRespHeader(response,fileName);
        EasyExcel.write(response.getOutputStream(), clazz)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet(sheetName)
                .registerWriteHandler(getDefaultHorizontalCellStyleStrategy())
                .registerWriteHandler(new TemplateCellWriteHandlerDate(mapDropDown))
                .doWrite(data);
        log.debug("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
    }

    /**
     * @author QiuYu
     * @createDate 2020-11-16
     * @param response
     * @param data  查询结果
     * @param fileName 导出文件名称
     * @param clazz 映射实体class类
     * @param <T>  查询结果类型
     * @throws Exception
     */
    public static<T> void writeExcel(HttpServletResponse response, List<T> data, String fileName, Class<?> clazz) throws Exception {
        long exportStartTime = System.currentTimeMillis();
        log.info("报表导出Size: "+data.size()+"条。");

        //List<List<T>> lists = SplitListUtil.splitList(data,MAXROWS); // 分割的集合
        List<List<T>> lists = CollectionUtil.split(data, MAXROWS); // 分割的集合
        setRespHeader(response,fileName);
        OutputStream out = response.getOutputStream();
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out, clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(getDefaultHorizontalCellStyleStrategy());
        ExcelWriter excelWriter = excelWriterBuilder.build();
        ExcelWriterSheetBuilder excelWriterSheetBuilder;
        WriteSheet writeSheet;
        if (CollectionUtil.isNotEmpty(lists)){
            for (int i =1;i<=lists.size();i++){
                excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
                excelWriterSheetBuilder.sheetNo(i);
                excelWriterSheetBuilder.sheetName("sheet"+i);
                writeSheet = excelWriterSheetBuilder.build();
                excelWriter.write(lists.get(i-1),writeSheet);
            }
        }
        out.flush();
        excelWriter.finish();
        out.close();
        log.debug("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
    }
}

收起

展开全文

elementui vue.js javascript

easyExcel导入导出

2021-06-03 21:30:46

项目中会遇到导入Excel的需求，使用POI的话可能会比较麻烦，最近使用阿里的easyExcel过程记录下。下例为本地环境测试：一、新建Springboot项目并添加依赖 easyExcel依赖： <dependency> <groupId>...

项目中会遇到导入Excel的需求，使用POI的话可能会比较麻烦，最近使用阿里的easyExcel过程记录下。下例为本地环境测试：

一、新建Springboot项目并添加依赖

easyExcel依赖：

       <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>easyexcel</artifactId>
          <version>2.2.6</version>
      </dependency>

lombok依赖（不是必需）：

      <dependency>
          <groupId>org.projectlombok</groupId>
          <artifactId>lombok</artifactId>
          <optional>true</optional>
      </dependency>

二、新建Model

 @Data
public class Demo extends BaseRowModel {

  @ExcelProperty(index = 0, value = "ID")
  private String id;

  @ExcelProperty(index = 1,value = "姓名")
  private String name;

  @ExcelProperty(index = 2,value = "地址")
  private String address;

}

说明：easyExcel是根据index读取Excel中的每列数据，value可以不用写，但是为了清晰建议写上；如果给前端返回时也用此model，会返回cellStyleMap什么的，可以用@jsonIgnore注解忽略掉。
本地Excel截图：
在这里插入图片描述

三、编写相关utils

ExcelUtil

public class ExcelUtil<T> {

    /***
     * 读取Excel方法
     * 要求类必须继承BaseRowModel
     */
    public static <T extends BaseRowModel> ArrayList<T> readExcel(MultipartFile excel, Class<T> clazz) {
        try {
            InputStream in = new BufferedInputStream(excel.getInputStream());
            GeneralExcelListener<T> listener = new GeneralExcelListener<>();
            ExcelReader excelReader = new ExcelReader(in, ExcelTypeEnum.XLS, listener);
            excelReader.read(new Sheet(1, 1, clazz));
            return listener.getSuccessDatas();

        } catch (Exception e) {

            return new ArrayList<T>();
        }

    }

    /**
     * 导出 Excel ：一个 sheet，带表头
     *
     * @param response  HttpServletResponse
     * @param list      数据 list，每个元素为一个 BaseRowModel
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object    映射实体类，Excel 模型
     */
    public static void writeExcel(HttpServletResponse response,
                                  List<? extends BaseRowModel> list,
                                  String fileName, String sheetName,
                                  BaseRowModel object) {
        ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();

    }

    /**
     * 导出文件时为Writer生成OutputStream
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
        try {
            return response.getOutputStream();
        } catch (IOException e) {
            throw new RuntimeException("导出文件时为Writer生成OutputStream失败！");
        }
    }

    /**
     * 返回 ExcelReader
     *
     * @param excel         需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     */
    private static ExcelReader getReader(MultipartFile excel,
                                         ExcelListener excelListener) {
        String filename = excel.getOriginalFilename();
        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
            throw new RuntimeException("文件格式错误！");
        }
        InputStream inputStream;
        try {
            inputStream = new BufferedInputStream(excel.getInputStream());
            return new ExcelReader(inputStream, null, excelListener, false);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}

ExcelListener

public class ExcelListener extends AnalysisEventListener {

    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

GeneralExcelListener

public class GeneralExcelListener<T> extends AnalysisEventListener<T> {
    private final Logger logger = LoggerFactory.getLogger(this.getClass());


    //自定义用于暂时存储data。
    //可以通过实例获取该值
    private ArrayList<T> successDatas = new ArrayList<>();

    public GeneralExcelListener() {

    }

    @Override
    public void invoke(T o, AnalysisContext analysisContext) {
    //此处是读取Excel每行数据，可通过analysisContext进行分析
        successDatas.add(o);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }
    public ArrayList<T> getSuccessDatas() {
        return successDatas;
    }
}

四、使用示例

导入示例

@PostMapping(value = "/import")
public ModelAndView import(@RequestParam(value = "file", required = true) MultipartFile file) {
 
    ModelAndView mv = new ModelAndView();
    //一行代码即可获取解析得到的Excel中的数据
    List<Test> datas = ExcelUtil.readExcel(file, Test.class);
    //然后对获取的数据，在service层进行业务处理
    ResultMsg result = provShareConfigService.insertList(datas);
    return mv;
}

导出示例

@PostMapping(value = "/export")
public void export(HttpServletRequest request, HttpServletResponse response, @RequestBody JSONObject ob) {
    //前端传来的参数
    String date = ob.getString("date");
    String fileName = "退赔导出";
    String sheetName = "sheet1";
    //从数据库得到的list集合
    List<Test> list = testService.selectAll(date);
    //一行代码即可导出
    ExcelUtil.writeExcel(response, list, fileName, sheetName, new Test());
}

五、编写测试用例(导入)

由于前端提供的一般都是MultipartFile，本地测试的话进行了转换。

    @Test
    public void test2() throws IOException {
        File file = new File("C:\\Users\\Light\\Desktop\\demo.xlsx");
        FileInputStream inputStream = new FileInputStream(file);
        MultipartFile multipartFile = new MockMultipartFile(file.getName(),file.getName(),
                ContentType.APPLICATION_OCTET_STREAM.toString(),inputStream);
        //使用ExcelUtil读取
        List<Demo> datas = ExcelUtil.readExcel(multipartFile, Demo.class);
        System.out.println(datas);
    }

执行后可以看到控制台打印信息：

[Demo(id=1, name=张三, address=北京), Demo(id=2, name=李四, address=上海), Demo(id=3, name=王五, address=广州), Demo(id=4, name=田六, address=深圳)]

拿到转换后的集合，可以对数据进行处理，然后进行持久化操作。导出自己可以做完持久化后进行尝试。

参考网上教程：原文地址

收起

展开全文

java excel poi

EasyExcel导入导出工具类
2022-06-02 13:02:30

/** * @Author: 海绵宝宝 * @Explain: easyExcel导入导出工具类 * @DateTime: 2022/6/2 12:58 * @Params: * @Return */ @Slf4j public class ExcelUtil { public ExcelUtil() { } /** * 导出execl * * @param ...

收起

后端
java使用EasyExcel导入导出excel
2022-03-02 21:43:58

一、准备工作 1、导包 | <``dependency``> ... } } | 以上，就生成完毕了四、结果以上就是java使用EasyExcel导入导出excel的详细内容，更多关于java 用EasyExcel导入导出excel的资料请关注脚本之家其它相关文章！

收起

java 开发语言后端 apache
Java easyexcel 导入导出总结
2022-03-18 09:36:12

0.前提-----引入依赖 <...easyexcel</artifactId> <version>2.1.1</version> </dependency> 1.导入 1.实体 package org.mnur.marketing.api.dto.vo; import

收起

java eureka
简单好用的EasyExcel导入导出工具类
2022-05-26 14:37:48

在Spring-Boot项目中引入EasyExcel依赖， <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.0.5</version> </...

收起

spring java 后端
EasyExcel导入导出下载模板（带下拉）
千次阅读 2022-03-22 16:13:59

EasyExcel导入导出网上资料很多，这里就不多做描述了，总之就是一款比较轻而小，易用的excel操作工具包；这里在项目中使用到，做下笔记。以及表格中下拉框的实现；声明：本文思路是借鉴于某大神的，然后自己进行二...

收起

java 后端
Spring Boot + EasyExcel 导入导出，好用到爆！
2022-01-01 00:21:22

点击关注公众号，利用碎片时间学习来源：www.jianshu.com/p/4e6aa6342b33EasyExcel是阿里巴巴开源poi插件之一，主要解决了poi框架使用复杂，sax解析模...

收起

java spring poi 大数据编程语言
EasyExcel导入导出详解
千次阅读 2020-11-20 13:47:40

先说POI，有过报表导入导出经验的同学，应该听过或者使用。 Apache POI是Apache软件基金会的开源函式库，提供跨平台的Java API实现Microsoft Office格式档案读写。但是存在如下一些问题： 1.1 学习使用成本较高对...

收起

java poi
easyexcel导入导出简单实现
2021-06-07 15:19:41

easyexcel的开发文档地址：https://www.yuque.com/easyexcel/doc/quickstart 简单前端页面搭建效果：前端一个vue页面代码，注意用的是elementui组件 <template> <div id="app"> <el-container&...

收起
使用EasyExcel导入导出Excel
2022-05-11 00:17:22

使用EasyExcel导入导出Excel 1、官方网站 https://github.com/alibaba/easyexcel 快速开始：https://www.yuque.com/easyexcel/doc/easyexcel 使用场景在日常开发中我们难免需要导入数据可以用EasyExcel来解决 2...

收起
Spring Boot + EasyExcel导入导出，简直太好用了！
2022-08-01 07:09:52

在EasyExcel执行write方法之后，获得ExcelWriterBuilder类，通过该类的registerWriteHandler方法可以设置一些处理策略。/***设置单元格样式(仅用于示例)**@return样式策略*///表头策略//表头水平对齐居中//背景色//...

收起
Spring Boot + EasyExcel 导入导出，好用到爆，可以扔掉 POI 了
2021-11-22 22:49:15

官方doc地址：https://www.yuque.com/easyexcel/doc/easyexcelEasyExcel优势注解式自定义操作。输入输出简单，提供输入输出过程的接口支持一定程度的单元格合并等灵活化操作二、常用注解@ExcelProperty指定当前字段...

收起

excel
SpringBoot集成EasyExcel导入导出excel表格
2021-12-25 17:17:25

1.demo 1.引入依赖  <dependency> <groupId>com.alibaba<...easyexcel</artifactId> <version>2

收起

java spring
Springboot 整合 easyexcel导入导出excel
万次阅读 热门讨论 2020-04-19 15:40:01

1. 引入核心依赖 <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.6</version> ...

收起

easyexcel excel导入导出
EasyExcel导入导出excel工具类
千次阅读 2019-12-07 16:51:54

easyexcel依赖 <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>1.1.2-beat1</v...

收起

excel工具类
easyExcel导入导出，插入数据到数据库（实现下拉菜单）
2021-08-13 16:48:18

首先，引入easyExcel的依赖包 <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.10</version> </dependency> ...

收起

java poi excel
Springboot整合easyExcel导入导出Excel
千次阅读 2019-12-10 19:30:12

最近公司有个需求要求可以导入、导出excel，因此在此记录学习一下如何使用Springboot整合easyExcel；需求：数据库中有张user表，有个业务要求可以导入、导出“用户名单.xls”表一、准备：创建项目：关于...

收起

easyExcel
Easyexcel导入导出多个sheet
千次阅读 2021-05-07 13:41:33

EasyExcel对于导入导出的操作十分简洁，记录一下多个sheet且内容不一致的导入导出。导出下载提示：其中部分代码操作Dao层可以删除，可以自己创建ExportUserExcel 对象进行测试，思路数据映射到excel中。这里...

收起

excel java
快速入门，使用EasyExcel导入导出文件
千次阅读 2019-08-30 10:36:12

maven依赖包  <dependency> <groupId>com.alibaba</groupId> <...easyexcel</artifactId> ...

收起

EasyExcel
Springboot+EasyExcel导入导出
千次阅读 2020-05-28 18:58:13

为什么要用EasyExcel 由于apache poi和jxl，excelPOI都有一个严重的问题，就是非常消耗内存，特别处理数据量多时，速度慢并且时有异常发生，所以改用由阿里研发的easyExcel更可靠一些,它的官方建议对于1000行以内的...

收起
SpringBoot使用easyExcel导入导出Excel文件
千次阅读 2020-12-31 13:48:23

SpringBoot使用easyExcel导入导出Excel文件一、项目原因最近公司业务上有导入导出Excel的需求，自己有点看不懂，于是上网看了一些大神的思路，学会了一些，给大家分享一下。二、项目的思路 Excel导入浏览文件夹...

收起

java excel mysql

一、环境

二、pom.xml

三、代码

1、ExcelUtils (核心工具类)

2、controller层接口

3、实体注解配置方式

四、多sheet导入导出

4.1 多sheet导出

4.2 多sheet导入

java使用EasyExcel导入导出excel（csdn）————程序.pdf

EasyExcel导入导出

Spring Boot + EasyExcel 导入导出

一 常用注解

二 依赖

三 监听

注意

四 实体类(用于封装excel的每一行数据)

五 controller

六、接口导出Excel (HttpServletResponse response, HttpServletRequest request)

hutool工具类实现

自适应列宽

七 本地导入、导出

easyexcel导入导出

easyExcel导入导出

一、新建Springboot项目并添加依赖

二、新建Model

三、编写相关utils

ExcelUtil

ExcelListener

GeneralExcelListener

四、使用示例

导入示例

导出示例

五、编写测试用例(导入)

EasyExcel导入导出工具类

java使用EasyExcel导入导出excel

Java easyexcel 导入导出总结

简单好用的EasyExcel导入导出工具类

EasyExcel导入导出下载模板（带下拉）