读取Excel数据到集合中

1.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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>WorkForExcel</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>

        <!--读取excel文件-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>5.2.12.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.3.5</version>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.6</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>

    </dependencies>

</project>

2. java代码进行实现

import java.io.*;
import java.util.ArrayList;
import java.util.List;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
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.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.junit.jupiter.api.Test;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;

/**
 * Created by Lenovo on 2018/1/21.
 */
public class ReadExcelTools {







    @Test
    public void test(){
        File file = new File("C:\\Users\\24473\\Desktop\\住院收据1.xlsx");
        System.out.println(file.getName());//获取文件名称 gdwechat.xlsx
        List<Menu> all = new ArrayList<>();
        try(FileInputStream fileInputStream = new FileInputStream(file)){
            ExcelReader sheet1 = ExcelUtil.getReader(fileInputStream, "sheet1");
            System.out.println(sheet1);//cn.hutool.poi.excel.ExcelReader@28ec166e
            sheet1.addHeaderAlias("费用金额(J)","parent_code");

         /*   sheet1.addHeaderAlias("费用金额(J)","formula_name");
            sheet1.addHeaderAlias("ICU(I)","formula_name");
            sheet1.addHeaderAlias("医保外扣除(Y)","formula_name");

            sheet1.addHeaderAlias("中高端不合理费用(Z)","formula_name");
            sheet1.addHeaderAlias("统筹报销(T)","formula_name");
            sheet1.addHeaderAlias("其他报销(Q)","formula_name");
            sheet1.addHeaderAlias("其他报销扣除（K）","formula_name");
            sheet1.addHeaderAlias("其他中高端扣费（G）","formula_name");
            sheet1.addHeaderAlias("非责任费用(F)","formula_name");
            sheet1.addHeaderAlias("ICU其他报销（X）","formula_name");
            sheet1.addHeaderAlias("ICU社保报销(S)","formula_name");

*/



            all = sheet1.readAll(Menu.class);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        /**
         *[Menu(menuId=1, menuName=首页, menuUrl=/home, parentId=null, menuLevel=0, menuStatus=1, menuIcon=iconfont icon-index, menuIndex=1, urlName=null)]
         */
        System.out.println(all);//打印读取的list数据
        System.out.println(all.size());//list条数 1
    }



    @Test
    public void test1(){
        File file = new File("C:\\Users\\24473\\Desktop\\工作簿2.xlsx");
        System.out.println(file.getName());//获取文件名称 gdwechat.xlsx
        List<Menu> all = new ArrayList<>();
        try(FileInputStream fileInputStream = new FileInputStream(file)){
            ExcelReader sheet1 = ExcelUtil.getReader(fileInputStream, "sheet1");
            System.out.println(sheet1);//cn.hutool.poi.excel.ExcelReader@28ec166e

            sheet1.addHeaderAlias("菜单url","menuUrl");
            sheet1.addHeaderAlias("父菜单id","parentId");
            sheet1.addHeaderAlias("菜单级别","menuLevel");
            sheet1.addHeaderAlias("菜单状态","menuStatus");
            sheet1.addHeaderAlias("菜单icon","menuIcon");
            sheet1.addHeaderAlias("菜单排序","menuIndex");



            all = sheet1.readAll(Menu.class);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        /**
         *[Menu(menuId=1, menuName=首页, menuUrl=/home, parentId=null, menuLevel=0, menuStatus=1, menuIcon=iconfont icon-index, menuIndex=1, urlName=null)]
         */
        System.out.println(all);//打印读取的list数据
        System.out.println(all.size());//list条数 1
    }


}

3java实体类的创建：

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;


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

@Data
//@Table(name="")
@AllArgsConstructor
@NoArgsConstructor
public class Menu {

   /* private Integer menuId;
    private String menuName;
    private String menuUrl;
    private Integer parentId;
    private Integer menuLevel;
    private Integer menuStatus;
    private String menuIcon;
    private Integer menuIndex;*/


//    @Id
    Long formula_id;
//    父公式代码
    String parent_code;
    String formula_name;		//			公式名称
    String formula_code;		//		公式代码
    Integer formula_type;		//		类型   0：非医疗收据   1：药房收据   2:门诊收据  3：住院收据
    String IS_EFFECT;				//是否有效
    String REMARK;			//描述
    Long QUALITY_ID;		//		属性ID
    String CRE_USER_ID;		//	创建人
    Date CRE_TIME;			//创建时间
    Date LAST_UPDATE_TIME;	//		最后更新时间
   String LAST_UPDATE_ID;	//	最后更新id
    Long ENTITY_ID;		//		实体id

}