yuanyourdomain小站

POI简单操作

<!-- 导入依赖   -->
  <dependencies>
    <!-- poi 03版本依赖 .xls-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>4.1.2</version>
    </dependency>
    <!--poi 07版本依赖 .xlsx格式-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.2</version>
    </dependency>
    <!--日期格式化工具-->
    <dependency>
      <groupId>joda-time</groupId>
      <artifactId>joda-time</artifactId>
      <version>2.10.1</version>
    </dependency>
    <!--test-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>
  </dependencies>

poi 03版本依赖和poi 07版本依赖的区别也就是.xls和.xlsx的区别,他们对应的导入的依赖也不同.xls对应的就是poi03的.xlsx对应的就是poi07版本的

对象说明

创建一工作簿
创建一个工作表
创建第一行
创建单元格 注：和以上图片对应,建议使用07.xlsx格式的

public class ExcelWrite03 {
    String PATH = "E:\\work\\Learningmaterials\\Learningmaterials\\扩展组件接入\\接入代码\\poi easyexcel";

    /**
     * 03版本
     * .xls
     * @throws FileNotFoundException
     */
    @Test
    public void testWrite03() throws IOException {
        //1.创建一工作簿
        Workbook workbook = new HSSFWorkbook();

        //2.创建一个工作表 :数据表1,第一个sheet的名称
        Sheet sheet = workbook.createSheet("数据表1");

        //3.创建第一行
        Row row1 = sheet.createRow(0);// 第一行
        // 4.创建一个单元格
        //第一行的第一个格子
        Cell cell1 = row1.createCell(0);// 第一行的第一列
        cell1.setCellValue("第一行第一个格子");
        //第一行的第二个格子
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("第一行第二个格子");

        // 第二行
        Row row2 = sheet.createRow(1);// 第二行
        Cell cell21 = row2.createCell(0);// 第二行的第一列
        cell21.setCellValue("时间");
        //第二行的第二列
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

        // 生成表,IO流, 03版本使用xls后缀
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"03.xls");
        //写入流
        workbook.write(fileOutputStream);

        //关闭流
        fileOutputStream.close();

        System.out.println("文件输出完毕");
    }

    /**
     * 07版本
     * .xlsx
     * @throws Exception
     */
    @Test
    public void testWrite07() throws Exception {
        // 1.创建工作簿
        Workbook workbook = new XSSFWorkbook();
        // 3.创建工作表
        Sheet sheet = workbook.createSheet("考核成绩表");
        // 3.创建第一行
        Row row1 = sheet.createRow(0);// 第一行
        // 4.创建单元格
        Cell cell1 = row1.createCell(0);// 第一行的第一列
        cell1.setCellValue("语文");
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue(100);
        // 第二行
        Row row2 = sheet.createRow(1);// 第一行
        Cell cell21 = row2.createCell(0);// 第一行的第一列
        cell21.setCellValue("时间");
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
        // 生成表,IO流,07版本使用xlsx后缀
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"07.xlsx");
        workbook.write(fileOutputStream);
        // 关闭流
        fileOutputStream.close();
        System.out.println("考核成绩表07输出完毕");
    }
}

POI大文件写入HSSF

缺点：最多只能处理65536行，否则会抛出异常java.lang.IllegalArgumentException:Invalid row number (65536) outside allowable range (0.. 65535) 优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快；

03版本测试 : new HSSFWorkbook()

/**
03版本测试
*/
 @Test
    public void testwrite03BigData() throws IOException {
        //时间
        long begin = System.currentTimeMillis();
        //创建一个薄
        Workbook workbook = new HSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            //行
            Row row = sheet.createRow(rowNum);//第多少行
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                //单元格
                Cell cell = row.createCell(cellNum);//第cellNum个单元格
                cell.setCellValue(cellNum);//值
            }
        }
        FileOutputStream fos = new FileOutputStream(PATH + "03版本Excel大量数据测试.xls");
        workbook.write(fos);
        fos.close();
        System.out.println("over");
        long end = System.currentTimeMillis();
        System.out.println((double) (end - begin) / 1000);
    }

07版本测试 :new XSSFWorkbook()

缺点：写数据时速度非常慢，非常耗内存，数据量过大时会发生内存溢出；优点：可以写较大的数据量，没有限制；只做一下修改

   /**
     * 07版本测试
     * @throws IOException
     */
    @Test
    public void testwrite07BigData() throws IOException {
        //时间
        long begin = System.currentTimeMillis();
        //创建一个薄
        Workbook workbook = new XSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            //行
            Row row = sheet.createRow(rowNum);//第多少行
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                //单元格
                Cell cell = row.createCell(cellNum);//第cellNum个单元格
                cell.setCellValue(cellNum);//值
            }
        }
        FileOutputStream fos = new FileOutputStream(PATH + "07版本Excel大量数据测试.xlsx");
        workbook.write(fos);
        fos.close();
        System.out.println("over");
        long end = System.currentTimeMillis();
        System.out.println((double) (end - begin) / 1000);
    }

大文件写SXSSF :new SXSSFWorkbook()

优点：可以写非常大的数据量，如100万条甚至更多条，写数据速度快，占用更少的内存；缺点：过程中会产生临时文件，需要清理临时文件；

@Test
     public void testWrite07BigDataFast() throws Exception {
         //记录开始时间
         long begin = System.currentTimeMillis();
         //创建一个SXSSFWorkbook
         Workbook workbook = new SXSSFWorkbook();
         //创建一个sheet
         Sheet sheet = workbook.createSheet();
         //xls文件最大支持65536行
         for (int rowNum = 0; rowNum < 100000; rowNum++) {
             //创建一个行
             Row row = sheet.createRow(rowNum);
             for (int cellNum = 0; cellNum < 10; cellNum++) {//创建单元格
                 Cell cell = row.createCell(cellNum);
                 cell.setCellValue(cellNum);
             }
         }
         System.out.println("over");
         FileOutputStream out = new FileOutputStream(path+"bigdata07-fast.xlsx");
         workbook.write(out);
         // 操作结束，关闭文件
         out.close();
         //清除临时文件
         ((SXSSFWorkbook)workbook).dispose();
         //记录结束时间
         long end = System.currentTimeMillis();
         System.out.println((double)(end - begin)/1000);//3.148
     }

POI读操作

03版本 :new HSSFWorkbook（）

public class ExcelReadTest {
    String path="E:\\work\\Learningmaterials\\Learningmaterials\\扩展组件接入\\接入代码\\";
    @Test
    public void testRead03() throws Exception{
        //获取文件流
        InputStream fileInputStream = new FileInputStream(path + "读操作03.xls");

        //1、创建一个工作簿
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        //2、得到一个工作表
        Sheet sheet = workbook.getSheetAt(0);//获取第一个Sheet

        //读取第一行第一列
        Row row = sheet.getRow(0);
        Cell cell11 = row.getCell(0);
        //输出单元格内容,一定要注意类型，
      //  cell11.getNumericCellValue() 获取数值类型
        System.out.println(cell11.getRichStringCellValue());//获取String类型
        //操作结束，关闭文件
        fileInputStream.close();
    }

}

**07版本 new XSSFWorkbook() **

	@Test
    public void testRead07() throws Exception{
        //获取文件流
        InputStream fileInputStream = new FileInputStream(path + "读操作07.xlsx");

        //1、创建一个工作簿
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        //2、得到一个工作表
        Sheet sheet = workbook.getSheetAt(0);

        //读取第一行第一列
        Row row = sheet.getRow(0);
        Cell cell11 = row.getCell(0);
        //输出单元格内容,一定要注意类型
        System.out.println(cell11.getRichStringCellValue());//今日新增观众
        //操作结束，关闭文件
        fileInputStream.close();
}

读取不同的数据类型

读取数据如上

  //读取不同类型
    @Test
    public void testTypeCell() throws Exception{
        //获取文件流
        InputStream is = new FileInputStream(path+"result年初及2024目标.xls");
    //创建一个工作簿，使用 excel能操作的这边都可以操作
        Workbook workbook = new HSSFWorkbook(is);
     //的到第一个Sheet
        Sheet sheet = workbook.getSheetAt(0);

         //获取表格的标题
        Row rowTitle = sheet.getRow(0);//获取最上面一行也就是表头/标题的内容
        if (rowTitle!=null){

            int cellCount = rowTitle.getPhysicalNumberOfCells();//获取表格中所有的列
            for (int countNum = 0; countNum <cellCount ; countNum++) {
                Cell cell = rowTitle.getCell(countNum);//得到的就是表头的数据 年度,品种,品种大类名称,年初数量,目标数量
                if (cell!=null){
                    int cellType = cell.getCellType();
                    String stringCellValue = cell.getStringCellValue();
                    System.out.print(stringCellValue+" | ");
                }

            }
            System.out.println();
        }
        //获取表格的数据
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows(); //获取所有的行数据
        for (int rowNum = 1; rowNum <physicalNumberOfRows ; rowNum++) {//跳过第一行的数据从第二行开始
            Row rowData = sheet.getRow(rowNum);
           if (rowData!=null){
            //读取列的数据
           int cellCount=  rowTitle.getPhysicalNumberOfCells();
               for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                   Cell cell = rowData.getCell(cellNum);
                   //匹配列的数据类型
                   if (cell!=null){
                       int cellType = cell.getCellType();
                       //判断单元格类型
                       String cellValue="";
                       switch (cellType){
                               case HSSFCell.CELL_TYPE_STRING://字符串
                               cellValue=String.valueOf(cell.getStringCellValue());
                               break;
                               case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                               cellValue = String.valueOf(cell.getBooleanCellValue());
                               break;
                               case HSSFCell.CELL_TYPE_BLANK://空
                               break;
                               case HSSFCell.CELL_TYPE_NUMERIC://数字(日期、普通数字)
                               if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
                                   Date date = cell.getDateCellValue();
                                   cellValue = new DateTime(date).toString("yyyy-MM-dd");
                               } else {// 不是日期格式，则防止当数字过长时以科学计数法显示
                                   cell.setCellType(CellType.STRING);//转换为字符串输出
                                   cellValue = cell.toString();
                               }
                               break;
                               case Cell.CELL_TYPE_ERROR:
                               System.out.print("【数据类型错误】");
                               break;
                       }
                       System.out.print(cellValue+" | ");
                   }
               }
               System.out.println();
           }
        }
    }

输出数据如下

POI导出

java

package com.baosight.mgvs.vs.ny.utils;

import com.baosight.iplat4j.core.data.ibatis.dao.Dao;
import com.baosight.iplat4j.core.ei.EiInfo;
import com.baosight.iplat4j.core.ioc.spring.PlatApplicationContext;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.*;

/**
 * 环保数据下载
 */


@RestController
@RequestMapping(value = "/downLoad")

public class downLoadHBFile {

    /**
     * 环保数据下载
     * @return
     * @throws IOException
     */
    @RequestMapping(value = "/downLoadExcel")
    @ResponseBody
    public EiInfo uploadExcel(
            @RequestParam String type,
            @RequestParam String date,
            HttpServletResponse response
              ) throws IOException {
        EiInfo inInfo = new EiInfo();
        Dao dao = (Dao) PlatApplicationContext.getBean("nycbDao");

        //创建一个SXSSFWorkbook
        Workbook workbook = new SXSSFWorkbook();
        //创建一个sheet
        Sheet sheet = workbook.createSheet();


        List<String> listTitle = new ArrayList<>();
        listTitle.add("年月");
        listTitle.add("环保类型");
        listTitle.add("项目名称");
        listTitle.add("单位");
        listTitle.add("预留项目");
        listTitle.add("数值");


        //写入表头
        Row rowTitle = sheet.createRow(0);
        //写入表头
        for (int i = 0; i < listTitle.size(); i++) {
            Cell cell = rowTitle.createCell(i);
            cell.setCellValue(listTitle.get(i));
        }



        //写入数据
        Map<String, Object> params = new HashMap<>();

        String qtrName=type.replaceAll("下载","维护");
        params.put("prodDate",date);
        params.put("qtrName",type.replaceAll("下载","维护"));

        List<Map<String, Object>> resultList=null;

        if (qtrName.equals("人员报表下载")){
            resultList = dao.query("VSNYHBWH01.getQueryDownLoad", params, 0, -999999);
        }else {
            resultList = dao.query("VSNYHBWH01.getQueryDownLoadRy", params, 0, -999999);
        }

        //// 设置第一列的宽度为25个字符宽
        sheet.setColumnWidth(0, 25 * 256);

        List<Map<String, Object>> resultEp=new ArrayList<>();

        //重新对返回的数据进行封装
        for (Map<String, Object> map : resultList) {
            Map<String, Object> maps=  new LinkedHashMap<>();
            maps.put("TIME",map.get("TIME"));
            maps.put("TYPE",map.get("TYPE"));
            maps.put("ITEM",map.get("ITEM"));
            maps.put("UNIT",map.get("UNIT"));
            maps.put("RESERVED_ITEM_1",map.get("RESERVED_ITEM_1"));
            maps.put("IND_VALUE",map.get("IND_VALUE"));
            maps.put("ROW_NUM",map.get("ROW_NUM"));
//            System.out.println(map);
            resultEp.add(maps);
        }


        for (Map<String, Object> map : resultEp) {
            System.out.println(map);
        }


        for (int rowNum = 0; rowNum < resultEp.size(); rowNum++) {
            //创建一个行
            Row row = sheet.createRow(rowNum);
            int count=0;
            for (Map.Entry<String, Object> entry : resultEp.get(rowNum).entrySet()) {
                Cell cell = row.createCell(count);
                cell.setCellValue(entry.getValue()==null?"":entry.getValue().toString());
                count++;
            }
        }
        response.setContentType("APPLICATION/OCTET-STREAM");
        String fileNameTo = URLEncoder.encode(type+".xlsx", "UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + fileNameTo);
        try {
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
              outputStream.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        return inInfo;
    }

    }
}

前端

            downLoad(){
                let  url="";
                var curWwwPath = window.document.location.href;
                if (curWwwPath.indexOf("localhost") != -1)
                    url = 'http://localhost:8080/mgvs-vspc';
                if (curWwwPath.indexOf("eplattest.baogang.info") != -1)
                    url = 'http://msgt.eplattest.baogang.info/mgvs-vspc';
                if (curWwwPath.indexOf("eplat.baogang.info") != -1)
                    url = 'http://msgt.eplat.baogang.info/mgvs-vspc';

                               //http://msgt.eplat.baogang.info/mgvs-vspc/upload/uploadExcel
                window.open(`${url}/downLoad/downLoadExcel?type=${this.qtrTypeNameDon}&date=${this.dialogFormData.prodDate}`);


                // $.ajax({
                //     type: "GET",
                //     //定义响应类型为 blob
                //     xhrFields: {
                //         responseType: 'blob'
                //     },
                //     async: true,
                //     contentType: "application/json",
                //     data: {
                //         type:this.qtrTypeNameDon,
                //         date: this.dialogFormData.prodDate
                //     },
                //     url: url+"/downLoad/downLoadExcel",
                //     success: function(result) {
                //         //转换为blob对象,通过a标签来实现文件下载
                //         const blob = new Blob([result], { type: 'application/octet-stream' })
                //         const a = document.createElement('a')
                //         a.href = URL.createObjectURL(blob)
                //         a.download = '文件名字.pdf'
                //         a.click()
                //     },
                //     error: function(xhr,status,error) {
                //         console.log('请求失败',error);
                //     }
                // });

            },

JSP

<el-button type="success" size="mini" icon="el-icon-download" @click="downLoad()">下载</el-button>

计算公式

@Test
public void testFormula() throws Exception {
    FileInputStream fileInputStream = new FileInputStream(PATH + "计算公式.xls");
    Workbook workbook = new HSSFWorkbook(fileInputStream);
    Sheet sheetAt = workbook.getSheetAt(0);

    //获取计算公式所在的行
    Row row = sheetAt.getRow(4);
    //计算公式的第几个单元格
    Cell cell = row.getCell(0);

    //拿到计算公式
    FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

    //输出单元格内容
    int cellType = cell.getCellType();
    switch (cellType){
        case Cell.CELL_TYPE_FORMULA://公式
            //获取单元格的计算公式
            String formula = cell.getCellFormula();
            System.out.println(formula);

            //计算
            CellValue evaluate = formulaEvaluator.evaluate(cell);
            String cellValue = evaluate.formatAsString();
            System.out.println(cellValue);
            break;
    }
}

EasyExcel基本操作

easyExcel 官网地址 https://easyexcel.opensource.alibaba.com/


  <!--导入依赖-->
  <dependencies>
    <!--fastjson-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version >1.2.62</version>
    </dependency>
    <!--easyexcel-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version >2.2.0-beta2</version>
    </dependency>
    <!--lombok-->
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version >1.18.12</version>
    </dependency>
    <!--日期格式化工具-->
    <dependency>
      <groupId>joda-time</groupId>
      <artifactId>joda-time</artifactId>
      <version>2.10.1</version>
    </dependency>
    <!--test-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>
  </dependencies>

实体类

package com.yuan;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
}

测试类

package com.yuan;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EasyExcelTest {

    String path="E:\\work\\Learningmaterials\\Learningmaterials\\扩展组件接入\\接入代码\\poi easyexcel";

    private List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }


        return list;
    }

    // 最简单的写
    @Test
    public void simpleWrite() {
        // 写法1
        String fileName = path+"EasyExcel.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        //write(fileName,格式类)
        //sheet(表明)
        //doWrite(数据)
        // .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())  表头自适应

//        EasyExcel.write(fileName, DemoData.class)
//                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())  //表头自适应
//                .sheet("模板").doWrite(data());

        EasyExcel.write(fileName, DemoData.class)
                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20)) // 简单的列宽策略，列宽20
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)30,(short)20)) // 简单的行高策略：头行高30，内容行高20
                .sheet("模板").doWrite(data());

    }
}

EasyExcel常用注解

@ExcelProperty
@ColumnWith 列宽
@ContentFontStyle 文本字体样式
@ContentLoopMerge 文本合并
@ContentRowHeight 文本行高度
@ContentStyle 文本样式
@HeadFontStyle 标题字体样式
@HeadRowHeight 标题高度
@HeadStyle 标题样式
@ExcelIgnore 忽略项
@ExcelIgnoreUnannotated 忽略未注解

字段注解	类注解
@ColumnWith(列宽)	@ColumnWidth(全局列宽)
@ExcelProperty(字段配置)	@HeadFontStyle(头样式)
	@HeadRowHeight(标题高度)
	@ContentFontStyle(内容字体样式)
	@ContentRowHeight(内容高度)

@ExcelProperty

必要的一个注解，注解中有三个参数value,index,converter分别代表列明，列序号，数据转换方式 value和index只能二选一，通常不用设置converter

value 通过标题文本对应
index 通过文本行号对应
converter 转换器，通常入库和出库转换使用，如性别入库0和1，出库男和女

public class ImeiEncrypt {
    @ExcelProperty(value = "值")
    private String valueField;

    @ExcelProperty(value = 1,converter =IndustryIdConverter.class)
    private String indexField;

    @ExcelProperty(value = "值对应和转换器",converter =IndustryIdConverter.class)
    private String valueAndConverterField;
}

@ColumnWith

设置列宽度,只有一个参数value，value的单位是字符长度，最大可以设置255个字符，因为一个excel单元格最大可以写入的字符个数就是255个字符。

public class ImeiEncrypt {
    @ColumnWidth(value = 18)
    private String imei;
}

@ContentFontStyle

用于设置单元格内容字体格式的注解

参数	含义
fontName	字体名称
fontHeightInPoints	字体高度
italic	是否斜体
strikeout	是否设置删除水平线
color	字体颜色
typeOffset	偏移量
underline	下划线
bold	是否加粗
charset	编码格式

@ContentLoopMerge

用于设置合并单元格的注解

参数：

参数	含义
eachRow
columnExtend

@ContentRowHeight

用于设置行高

参数：

参数	含义
value	行高，`-1`代表自动行高

@ContentStyle

设置内容格式注解

参数：

参数	含义
dataFormat	日期格式
hidden	设置单元格使用此样式隐藏
locked	设置单元格使用此样式锁定
quotePrefix	在单元格前面增加`符号，数字或公式将以字符串形式展示
horizontalAlignment	设置是否水平居中
wrapped	设置文本是否应换行。将此标志设置为`true`通过在多行上显示使单元格中的所有内容可见
verticalAlignment	设置是否垂直居中
rotation	设置单元格中文本旋转角度。03版本的Excel旋转角度区间为-90°90°，07版本的Excel旋转角度区间为0°180°
indent	设置单元格中缩进文本的空格数
borderLeft	设置左边框的样式
borderRight	设置右边框样式
borderTop	设置上边框样式
borderBottom	设置下边框样式
leftBorderColor	设置左边框颜色
rightBorderColor	设置右边框颜色
topBorderColor	设置上边框颜色
bottomBorderColor	设置下边框颜色
fillPatternType	设置填充类型
fillBackgroundColor	设置背景色
fillForegroundColor	设置前景色
shrinkToFit	设置自动单元格自动大小

@HeadFontStyle

用于定制标题字体格式

参数	含义
fontName	设置字体名称
fontHeightInPoints	设置字体高度
italic	设置字体是否斜体
strikeout	是否设置删除线
color	设置字体颜色
typeOffset	设置偏移量
underline	设置下划线
charset	设置字体编码
bold	设置字体是否加粗

@HeadRowHeight

设置标题行行高

参数	含义
value	设置行高，-1代表自动行高

@HeadStyle

设置标题样式

参数	含义
dataFormat	日期格式
hidden	设置单元格使用此样式隐藏
locked	设置单元格使用此样式锁定
quotePrefix	在单元格前面增加`符号，数字或公式将以字符串形式展示
horizontalAlignment	设置是否水平居中
wrapped	设置文本是否应换行。将此标志设置为`true`通过在多行上显示使单元格中的所有内容可见
verticalAlignment	设置是否垂直居中
rotation	设置单元格中文本旋转角度。03版本的Excel旋转角度区间为-90°90°，07版本的Excel旋转角度区间为0°180°
indent	设置单元格中缩进文本的空格数
borderLeft	设置左边框的样式
borderRight	设置右边框样式
borderTop	设置上边框样式
borderBottom	设置下边框样式
leftBorderColor	设置左边框颜色
rightBorderColor	设置右边框颜色
topBorderColor	设置上边框颜色
bottomBorderColor	设置下边框颜色
fillPatternType	设置填充类型
fillBackgroundColor	设置背景色
fillForegroundColor	设置前景色
shrinkToFit	设置自动单元格自动大小

@ExcelIgnore

不将该字段转换成Excel

@ExcelIgnoreUnannotated

没有注解的字段都不转换

EasyExcel合并单元格/写多个Sheet/同一个sheet中写多个表

需要2.2.1以上的版本依赖

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

最原始的方法

package com.yuan;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Test {
    public static void writeExcel() {
        // 写excel的路径，当前项目路径下
        String fileName = getPath();
        // 构建ExcelWriter
        ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build();

        // 构建sheet
        WriteSheet writeSheet = EasyExcel.writerSheet("模板1").head(DemoData.class).build();
        // 写sheet
        excelWriter.write(data1(), writeSheet);
        excelWriter.finish();
    }

    private static String getPath() {
        return System.getProperty("user.dir") + "/" + System.currentTimeMillis() + ".xlsx";
    }

    private static List<DemoData> data1() {
        List<DemoData> list = new ArrayList();
        for (int i = 0; i < 3; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + 1);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        for (int i = 0; i < 3; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + 2);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        for (int i = 0; i < 4; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + 3);
            data.setDate(new Date());
            data.setDoubleData(0.57);
            list.add(data);
        }
        return list;
    }

    public static void main(String[] args) {
        writeExcel();
    }
}

单元格合并后方法

package com.yuan;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

// 自定义合并策略 该类继承了AbstractMergeStrategy抽象合并策略，需要重写merge()方法
  public class CustomMergeStrategy extends AbstractMergeStrategy {

    /**
     * 分组，每几行合并一次
     */
    private List<Integer> exportFieldGroupCountList;

    /**
     * 目标合并列index
     */
    private Integer targetColumnIndex;

    // 需要开始合并单元格的首行index
    private Integer rowIndex;

    // exportDataList为待合并目标列的值 
    public CustomMergeStrategy(List<String> exportDataList, Integer targetColumnIndex) {
      this.exportFieldGroupCountList = getGroupCountList(exportDataList);
      this.targetColumnIndex = targetColumnIndex;
    }


    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {

      if (null == rowIndex) {
        rowIndex = cell.getRowIndex();
      }
      // 仅从首行以及目标列的单元格开始合并，忽略其他
      if (cell.getRowIndex() == rowIndex && cell.getColumnIndex() == targetColumnIndex) {
        mergeGroupColumn(sheet);
      }
    }

    private void mergeGroupColumn(Sheet sheet) {
      int rowCount = rowIndex;
      for (Integer count : exportFieldGroupCountList) {
        if(count == 1) {
          rowCount += count;
          continue ;
        }
        // 合并单元格
        CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1, targetColumnIndex, targetColumnIndex);
        sheet.addMergedRegionUnsafe(cellRangeAddress);
        rowCount += count;
      }
    }

    // 该方法将目标列根据值是否相同连续可合并，存储可合并的行数 
    private List<Integer> getGroupCountList(List<String> exportDataList){
      if (CollectionUtils.isEmpty(exportDataList)) {
        return new ArrayList<>();
      }

      List<Integer> groupCountList = new ArrayList<>();
      int count = 1;

      for (int i = 1; i < exportDataList.size(); i++) {
        if (exportDataList.get(i).equals(exportDataList.get(i - 1))) {
          count++;
        } else {
          groupCountList.add(count);
          count = 1;
        }
      }
      // 处理完最后一条后
      groupCountList.add(count);
      return groupCountList;
    }



  }

package com.yuan;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

public class Test {
    public static void writeExcel() {

        String fileName = System.getProperty("user.dir") + "/" + System.currentTimeMillis() + ".xlsx";
        ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build();

        List<DemoData> demoDataList = data1();
        // 写sheet的时候注册相应的自定义合并单元格策略
        WriteSheet writeSheet = EasyExcel.writerSheet("模板1").head(DemoData.class)
                .registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(DemoData::getString).collect(Collectors.toList()), 0))
                .build();
        excelWriter.write(demoDataList, writeSheet);
        excelWriter.finish();
    }

    private static String getPath() {
        return System.getProperty("user.dir") + "/" + System.currentTimeMillis() + ".xlsx";
    }

    private static List<DemoData> data1() {
        List<DemoData> list = new ArrayList();
        for (int i = 0; i < 3; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + 1);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        for (int i = 0; i < 3; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + 2);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        for (int i = 0; i < 4; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + 3);
            data.setDate(new Date());
            data.setDoubleData(0.57);
            list.add(data);
        }
        return list;
    }

    public static void main(String[] args) {
        writeExcel();
    }
}

写多个Sheet

package com.yuan;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

public class Test {

   //写多个Sheet
    public static void writeExcel() {
        String fileName = getPath();
        ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build();

        List<DemoData> demoDataList = data1();
        WriteSheet writeSheet = EasyExcel.writerSheet("模板1").head(DemoData.class)
                .registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(DemoData::getString).collect(Collectors.toList()), 0))
                .registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(o -> o.getDoubleData().toString()).collect(Collectors.toList()), 2))
                .build();
        excelWriter.write(demoDataList, writeSheet);

        WriteSheet writeSheet1 = EasyExcel.writerSheet("模板2").head(DemoData.class).build();
        excelWriter.write(data1(), writeSheet1);
        excelWriter.finish();
    }

    private static String getPath() {
        return System.getProperty("user.dir") + "/" + System.currentTimeMillis() + ".xlsx";
    }

    private static List<DemoData> data1() {
        List<DemoData> list = new ArrayList();
        for (int i = 0; i < 3; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + 1);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        for (int i = 0; i < 3; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + 2);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        for (int i = 0; i < 4; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + 3);
            data.setDate(new Date());
            data.setDoubleData(0.57);
            list.add(data);
        }
        return list;
    }

    public static void main(String[] args) {
        writeExcel();
    }
}

同一个sheet中写多个表

package com.yuan;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

public class Test {


    //**同一个sheet中写多个表**
    public static void writeExcel() {
        String fileName = getPath();
        ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("模板").needHead(Boolean.FALSE).build();
        List<DemoData> demoDataList = data1();
        // 需要表头设置为true，WriteTable一些属性会继承自WriteSheet
        WriteTable writeTable = EasyExcel.writerTable(1).head(DemoData.class).needHead(Boolean.TRUE)
                .registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(DemoData::getString).collect(Collectors.toList()), 0))
                .registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(o -> o.getDoubleData().toString()).collect(Collectors.toList()), 2))
                .build();
        excelWriter.write(demoDataList, writeSheet, writeTable);

        WriteTable writeTable1 = EasyExcel.writerTable(2).head(DemoData.class).needHead(Boolean.TRUE).build();
        excelWriter.write(data1(), writeSheet, writeTable1);
        excelWriter.finish();
    }

    private static String getPath() {
        return System.getProperty("user.dir") + "/" + System.currentTimeMillis() + ".xlsx";
    }

    private static List<DemoData> data1() {
        List<DemoData> list = new ArrayList();
        for (int i = 0; i < 3; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + 1);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        for (int i = 0; i < 3; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + 2);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        for (int i = 0; i < 4; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + 3);
            data.setDate(new Date());
            data.setDoubleData(0.57);
            list.add(data);
        }
        return list;
    }

    public static void main(String[] args) {
        writeExcel();
    }
}

EasyExcel多层表头与动态表头

动态表头

package com.yuan.dachu;

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

@Data
    public class MyData {
        @ExcelProperty("用户名称")
        private String userName;
        @ExcelProperty("用户性别")
        private String userSex;
        @ExcelProperty("用户年龄")
        private Integer userAge;
    }

测试

package com.yuan.dachu;

import com.alibaba.excel.EasyExcel;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

public class ExcelImport {

    String PATH="E:\\work\\Learningmaterials\\Learningmaterials\\扩展组件接入\\接入代码\\";
    @Test
    public void myData2() {
        // 写法1
        String fileName = PATH + "myData2" + System.currentTimeMillis() + ".xlsx";
        EasyExcel.write(fileName)
                //文件的表头
                .head(genHead())
                //从第0行开始，默认为0
//                .relativeHeadRowIndex(0)
                //sheet名称
                .sheet("模板")
                .doWrite(null);
    }

      /**
     * 文件的表头数据
     * @return
     */
    private List<List<String>> genHead() {
        //表头list的生成，实际的表头信息可以从数据库或者缓存等处动态读取
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("名称");
        List<String> head1 = new ArrayList<String>();
        head1.add("年龄");
        List<String> head2 = new ArrayList<String>();
        head2.add("性别");
        list.add(head0);
        list.add(head1);
        list.add(head2);
        return list;
    }

}

多层表头

实体类

package com.yuan.dachu;

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

@Data
    public class MyComplexData {
        @ExcelProperty({"用户", "名称"})
        private String name;
        @ExcelProperty({"用户", "年龄"})
        private Integer age;
        @ExcelProperty({"用户", "性别"})
        private String sex;

        @ExcelProperty({"地址", "国家"})
        private String country;
        @ExcelProperty({"地址", "省份"})
        private String province;

        @ExcelProperty( "手机号")
        private String phone;
    }

测试类

  @Test
    public void MyComplexData() {
        // 文件名
        String fileName = PATH + "MyComplexData" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, MyComplexData.class)
                //sheet名称
                .sheet("模板")
                .doWrite(null);
    }

多级动态表头

 private List<List<String>> gemComplexHead() {
        //表头list的生成，实际的表头信息可以从数据库或者缓存等处动态读取
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        //第一层
        head0.add("用户");
        //第二层
        head0.add("名称");
        List<String> head1 = new ArrayList<String>();
        //第一层
        head1.add("用户");
        //第二层
        head1.add("年龄");
        List<String> head2 = new ArrayList<String>();
        //第一层
        head2.add("用户");
        //第二层
        head2.add("性别");

        list.add(head0);
        list.add(head1);
        list.add(head2);


        List<String> head3 = new ArrayList<String>();
        head3.add("手机号");
        List<String> head4 = new ArrayList<String>();
        //第一层
        head4.add("地址");
        //第二层
        head4.add("国家");
        List<String> head5 = new ArrayList<String>();
        //第一层
        head5.add("地址");
        //第二层
        head5.add("省份");

        list.add(head3);
        list.add(head4);
        list.add(head5);
        return list;
    }

    @Test
    public void MyComplexData2() {
        // 文件名
        String fileName = PATH + "MyComplexData2" + System.currentTimeMillis() + ".xlsx";
        EasyExcel.write(fileName)
                //文件的表头
                .head(gemComplexHead())
                .sheet("模板")
                .doWrite(null);
    }

EasyExcel自定义样式类

package com.yuan.dachu;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.AbstractCellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.yuan.ce.DemoData;
import org.apache.poi.ss.usermodel.*;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class CustomCellWriteHandler  extends AbstractCellWriteHandler {

    String PATH="E:\\work\\Learningmaterials\\Learningmaterials\\扩展组件接入\\接入代码\\";
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
         Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        // 设置行高
        short height = 650;
        row.setHeight(height);
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        Sheet sheet = writeSheetHolder.getSheet();
        sheet.setColumnWidth(cell.getColumnIndex(), 3000);
        //当前行的第i列
        int i = cell.getColumnIndex();
        //不处理第一行
        if (0 != cell.getRowIndex()) {
        	//此处判断为不符合要求 所以需求进行标红处理  可以根据自己需求进行判断使用  或者全量使用
//            if (cell.getStringCellValue().contains("_false")) {
                // 根据单元格获取workbook
                Workbook workbook = cell.getSheet().getWorkbook();
                //设置行高
                writeSheetHolder.getSheet().getRow(cell.getRowIndex()).setHeight((short) (1.4 * 256));
                // 单元格策略
                WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                // 设置背景颜色白色
                contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                // 设置垂直居中为居中对齐
                contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                // 设置左右对齐为靠左对齐
                contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
                // 设置单元格上下左右边框为细边框
                contentWriteCellStyle.setBorderBottom(BorderStyle.MEDIUM);
                contentWriteCellStyle.setBorderLeft(BorderStyle.MEDIUM);
                contentWriteCellStyle.setBorderRight(BorderStyle.MEDIUM);
                contentWriteCellStyle.setBorderTop(BorderStyle.MEDIUM);
                // 创建字体实例
                WriteFont cellWriteFont = new WriteFont();
                // 设置字体大小
                cellWriteFont.setFontName("宋体");
                cellWriteFont.setFontHeightInPoints((short) 12);
                //设置字体颜色
                cellWriteFont.setColor(IndexedColors.RED1.getIndex());
                //单元格颜色
//                contentWriteCellStyle.setFillForegroundColor(IndexedColors.RED1.getIndex());
                contentWriteCellStyle.setWriteFont(cellWriteFont);
                CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, contentWriteCellStyle);
                //设置当前行第i列的样式
                cell.getRow().getCell(i).setCellStyle(cellStyle);
            }
    //}

    }
}

测试类

  @Test
    public void test(){
        String fileName = PATH + "MyComplexData2" + System.currentTimeMillis() + ".xlsx";
        EasyExcel.write(fileName, DemoData.class)
                .inMemory(true)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//自定义样式
                .registerWriteHandler(new CustomCellWriteHandler())//自定义样式
                .sheet().doWrite(data());

    }

    //数据
    private List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
//            data.setString("bbb");
            list.add(data);
        }


        return list;
    }

实体类

package com.yuan.ce;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
}

EasyExcel读取操作

实体类

package com.yuan;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class ReadStudent {
    // 第一列
    @ExcelProperty(value = "年度",index = 0)
    private String year;
    // 第二列
    @ExcelProperty(value = "品种",index = 1)
    private String variety;

    // 第二列
    @ExcelProperty(value = "品种名称",index = 2)
    private String varietyName;

    // 第三列
    @ExcelProperty(value = "目标数量",index = 3)
    private double targeNum;
}

创建监听器读取表格数据

package com.yuan;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 读取excel监听器
 */
public class ReadFromExcelListener extends AnalysisEventListener<ReadStudent> {
    // 封装读出来的数据
    List<ReadStudent> list = new ArrayList<>();

    // 一行一行的读取excel内容
    @Override
    public void invoke(ReadStudent readStudent, AnalysisContext analysisContext) {
        System.out.println(readStudent);
        list.add(readStudent);
    }
    // 读取表头的信息
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头的信息：" + headMap);
    }


    // 读取后才执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

测试方法

package com.yuan;


import com.alibaba.excel.EasyExcel;

public class ReadFromExcel {
    public static void main(String[] args) {

        String fileName = "E:\\work\\Learningmaterials\\Learningmaterials\\扩展组件接入\\接入代码\\result年初及2024目标.xls";
        EasyExcel.read(fileName,ReadStudent.class,new ReadFromExcelListener()).sheet().doRead();

    }
}

SpringBoot+elementUI实现导入excel

jsp

<div style="position: absolute;left: 60%">
    <el-upload
            class="upload-demo"
            name="excelFile"
            :data="postParm"
            :on-error="uploadFalse"
            :on-success="uploadSuccess"
            :before-upload="beforeAvatarUpload"
            action="/mgvs-vspc/upload/uploadExcel"
            multiple>
        <i class="el-icon-upload"></i>
        <el-button size="small" type="primary">点击上传</el-button>
    </el-upload>
</div>

data() {
        return {
            //批量弹出框
            open1: false,

            fileList: [],
            postParm:{
                businessName: "设备excel"
            }
        };
    },

      methods: {
            uploadFalse(response, file, fileList) {
                alert("文件上传失败！");
            },
            uploadSuccess(response, file, fileList) {
                console.log('response',response);
                //文件导入成功

                if (response ===  "文件上传成功") {
                    this.$message.success("文件上传成功");
                    this.open1= false;
                    this.getData();
                }else {
                    this.$message.success("文件上传失败");
                    this.open1 = false;
                }

            },
            // 上传前对文件的大小的判断
            beforeAvatarUpload(file) {
                const extension = file.name.split(".")[1] === "xls";
                const extension2 = file.name.split(".")[1] === "xlsx";
                // const extension3 = file.name.split(".")[1] === "doc";
                // const extension4 = file.name.split(".")[1] === "docx";
                const isLt2M = file.size / 1024 / 1024 < 10;
                if (!extension && !extension2 ) {
                    alert("上传模板只能是 xls、xlsx格式!");
                }
                if (!isLt2M) {
                    console.log("上传模板大小不能超过 10MB!");
                }
               return extension || extension2 || ( isLt2M);
            }
      }

java代码

package com.baosight.mgvs.vs.ny.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.Map;

@RestController
@RequestMapping("/upload")
public class HbUpload {

    @RequestMapping(value = "/uploadExcel", method = RequestMethod.POST)
    @ResponseBody
    public Object uploadExcel( @RequestParam("excelFile") MultipartFile excelFile,
                               @RequestParam(value = "businessName",required = true) String businessName) throws IOException {
        try{

            String name = excelFile.getOriginalFilename();

            InputStream inputStream = excelFile.getInputStream();//获取的表格转成文件流的形式
            //1、创建一个工作簿
            Workbook workbook =null;

            if(name.substring(name.length() - 5).equals(".xlsx")) {
                workbook= new XSSFWorkbook(inputStream);
            }else if (name.substring(name.length() - 4).equals(".xls")){
                workbook= new HSSFWorkbook(inputStream);
            }else {
                return "仅支持xlsx,xls格式";
            }
            //获取第一个Sheet
            Sheet sheet = workbook.getSheetAt(0);
            //获取表格的标题
            Row rowTitle = sheet.getRow(0);//获取最上面一行也就是表头/标题的内容
            if (rowTitle!=null){
                int cellCount = rowTitle.getPhysicalNumberOfCells();//获取表格中所有的列
                for (int countNum = 0; countNum <cellCount ; countNum++) {
                    Cell cell = rowTitle.getCell(countNum);//获取表头的数据
                    if (cell!=null){
                        int cellType = cell.getCellType();
                        String stringCellValue = cell.getStringCellValue();
                        System.out.print(stringCellValue+" | ");
                    }
                }
                System.out.println();
            }
            //获取表格的数据
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows(); //获取所有的行数据
            for (int rowNum = 1; rowNum <physicalNumberOfRows ; rowNum++) {//跳过第一行的数据从第二行开始
                Row rowData = sheet.getRow(rowNum);
                if (rowData!=null) {
                    //读取列的数据
                    int cellCount = rowTitle.getPhysicalNumberOfCells();
                    for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                        Cell cell = rowData.getCell(cellNum);
                        //匹配列的数据类型
                        if (cell != null) {
                            int cellType = cell.getCellType();
                            //判断单元格类型
                            String cellValue = "";
                            switch (cellType) {
                                case HSSFCell.CELL_TYPE_STRING://字符串
                                    cellValue = String.valueOf(cell.getStringCellValue());
                                    break;
                                case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                                    cellValue = String.valueOf(cell.getBooleanCellValue());
                                    break;
                                case HSSFCell.CELL_TYPE_BLANK://空
                                    break;
                                case HSSFCell.CELL_TYPE_NUMERIC://数字(日期、普通数字)
//                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
//                                        Date date = cell.getDateCellValue();
//                                        cellValue = new DateTime(date).toString("yyyy-MM-dd");
//                                    } else {// 不是日期格式，则防止当数字过长时以科学计数法显示
//                                        cell.setCellType(CellType.STRING);//转换为字符串输出
//                                        cellValue = cell.toString();
//                                    }
                                    break;
                                case Cell.CELL_TYPE_ERROR:
                                    System.out.print("【数据类型错误】");
                                    break;
                            }
                            System.out.print(cellValue + " | ");
                        }
                    }
                    System.out.println();
                }
            }
            return "文件上传成功";
        }catch (Exception ex){
            System.out.println(ex.getMessage());
            return "文件上传有误";
        }
    }

    @RequestMapping(value = "/getCe", method = RequestMethod.GET)
    public Object getTest() throws IOException {
        return "访问成功";
    }
}