<kbd id="afajh"><form id="afajh"></form></kbd>
<strong id="afajh"><dl id="afajh"></dl></strong>
    2. 

    <del id="afajh"><form id="afajh"></form></del>
    

    5. 

        1. <th id="afajh"><progress id="afajh"></progress></th>

          <b id="afajh"><abbr id="afajh"></abbr></b>
          <th id="afajh"><progress id="afajh"></progress></th>
          史上最全的 Excel 導入導出
          共
                8018字，需瀏覽
                    17分鐘
                
           ·
          2021-11-09 02:58
          
                    

                    
                    
                    
                    
          點擊關注公眾號，Java干貨及時送達
          喝水不忘挖井人，感謝阿里巴巴項目組提供了easyexcel工具類，github地址：

          https://github.com/alibaba/easyexcel
          文章目錄
          • 環(huán)境搭建
          • 讀取excel文件
            • 默認讀取
            • 指定讀取
            • 默認讀取
            • 指定讀取
            • 小于1000行數(shù)據(jù)
            • 大于1000行數(shù)據(jù)
          • 導出excle
            • 無模型映射導出
            • 模型映射導出
            • 單個Sheet導出
            • 多個Sheet導出
          • 工具類
          • 測試類
          環(huán)境搭建
          • easyexcel 依賴（必須）
          • springboot (不是必須)
          • lombok （不是必須）
          <dependency>
          ???<groupId>com.alibabagroupId>
          ???<artifactId>easyexcelartifactId>
          ???<version>1.1.2-beat1version>
          ?dependency>
          ??
          ?<dependency>
          ???<groupId>org.projectlombokgroupId>
          ???<artifactId>lombokartifactId>
          ???<version>1.18.2version>
          ?dependency>
          讀取excel文件
          小于1000行數(shù)據(jù)
          默認讀取
          讀取Sheet1的全部數(shù)據(jù)
          String?filePath = "/home/chenmingjian/Downloads/學生表.xlsx";
          ?List<Object> objects = ExcelUtil.readLessThan1000Row(filePath);
          指定讀取
          下面是學生表.xlsx中Sheet1，Sheet2的數(shù)據(jù)
          獲取Sheet1表頭以下的信息
          String?filePath = "/home/chenmingjian/Downloads/學生表.xlsx";
          //第一個1代表sheet1, 第二個1代表從第幾行開始讀取數(shù)據(jù)，行號最小值為0
          Sheet sheet = new?Sheet(1, 1);
          List<Object> objects = ExcelUtil.readLessThan1000Row(filePath,sheet);
          獲取Sheet2的所有信息
          String?filePath = "/home/chenmingjian/Downloads/學生表.xlsx";
          ?Sheet sheet = new?Sheet(2, 0);
          ?List<Object> objects = ExcelUtil.readLessThan1000Row(filePath,sheet);
          大于1000行數(shù)據(jù)
          默認讀取
          String?filePath = "/home/chenmingjian/Downloads/學生表.xlsx";
          List<Object> objects = ExcelUtil.readMoreThan1000Row(filePath);
          指定讀取
          String?filePath = "/home/chenmingjian/Downloads/學生表.xlsx";
          Sheet sheet = new?Sheet(1, 2);
          List<Object> objects = ExcelUtil.readMoreThan1000Row(filePath，sheet);
          導出excle
          單個Sheet導出
          無模型映射導出
          String filePath = "/home/chenmingjian/Downloads/測試.xlsx";
          List<List> data = new?ArrayList<>();
          data.add(Arrays.asList("111","222","333"));
          data.add(Arrays.asList("111","222","333"));
          data.add(Arrays.asList("111","222","333"));
          List head = Arrays.asList("表頭1", "表頭2", "表頭3");
          ExcelUtil.writeBySimple(filePath,data,head);
          結(jié)果
          模型映射導出
          1、定義好模型對象
          package?com.springboot.utils.excel.test;

          import?com.alibaba.excel.annotation.ExcelProperty;
          import?com.alibaba.excel.metadata.BaseRowModel;
          import?lombok.Data;
          import?lombok.EqualsAndHashCode;

          /**
          ?* @description:
          ?* @author: chenmingjian
          ?* @date: 19-4-3 14:44
          ?*/
          @EqualsAndHashCode(callSuper?= true)
          @Data
          public class TableHeaderExcelProperty extends BaseRowModel {

          ????/**
          ?????* value: 表頭名稱
          ?????* index: 列的號, 0表示第一列
          ?????*/
          ????@ExcelProperty(value?= "姓名", index = 0)
          ????private String name;

          ????@ExcelProperty(value?= "年齡",index = 1)
          ????private int age;

          ????@ExcelProperty(value?= "學校",index = 2)
          ????private String school;
          }

          2、調(diào)用方法
          String filePath = "/home/chenmingjian/Downloads/測試.xlsx";
          ArrayList data = new?ArrayList<>();
          ??for(int?i = 0; i < 4; i++){
          ??????TableHeaderExcelProperty tableHeaderExcelProperty = new?TableHeaderExcelProperty();
          ??????tableHeaderExcelProperty.setName("cmj"?+ i);
          ??????tableHeaderExcelProperty.setAge(22?+ i);
          ??????tableHeaderExcelProperty.setSchool("清華大學"?+ i);
          ??????data.add(tableHeaderExcelProperty);
          ??}

          ??ExcelUtil.writeWithTemplate(filePath,data);
          多個Sheet導出
          1、定義好模型對象
          package?com.springboot.utils.excel.test;

          import?com.alibaba.excel.annotation.ExcelProperty;
          import?com.alibaba.excel.metadata.BaseRowModel;
          import?lombok.Data;
          import?lombok.EqualsAndHashCode;

          /**
          ?* @description:
          ?* @author: chenmingjian
          ?* @date: 19-4-3 14:44
          ?*/
          @EqualsAndHashCode(callSuper?= true)
          @Data
          public class TableHeaderExcelProperty extends BaseRowModel {

          ????/**
          ?????* value: 表頭名稱
          ?????* index: 列的號, 0表示第一列
          ?????*/
          ????@ExcelProperty(value?= "姓名", index = 0)
          ????private String name;

          ????@ExcelProperty(value?= "年齡",index = 1)
          ????private int age;

          ????@ExcelProperty(value?= "學校",index = 2)
          ????private String school;
          }
          2、調(diào)用方法
          ArrayList list1 = new?ArrayList<>();
          ?for(int?j = 1; j < 4; j++){
          ??????ArrayList list?= new?ArrayList<>();
          ??????for(int?i = 0; i < 4; i++){
          ??????????TableHeaderExcelProperty tableHeaderExcelProperty = new?TableHeaderExcelProperty();
          ??????????tableHeaderExcelProperty.setName("cmj"?+ i);
          ??????????tableHeaderExcelProperty.setAge(22?+ i);
          ??????????tableHeaderExcelProperty.setSchool("清華大學"?+ i);
          ??????????list.add(tableHeaderExcelProperty);
          ??????}

          ??????Sheet sheet = new?Sheet(j, 0);
          ??????sheet.setSheetName("sheet"?+ j);

          ??????ExcelUtil.MultipleSheelPropety multipleSheelPropety = new?ExcelUtil.MultipleSheelPropety();
          ??????multipleSheelPropety.setData(list);
          ??????multipleSheelPropety.setSheet(sheet);

          ??????list1.add(multipleSheelPropety);

          ??}

          ??ExcelUtil.writeWithMultipleSheel("/home/chenmingjian/Downloads/aaa.xlsx",list1);
          工具類
          /**
          ?* @description:
          ?* @author: chenmingjian
          ?* @date: 19-3-18 16:16
          ?*/
          @Slf4j
          public?class?ExcelUtil?{

          ???private?static?Sheet initSheet;

          ???static?{
          ??????initSheet = new?Sheet(1, 0);
          ??????initSheet.setSheetName("sheet");
          ??????//設置自適應寬度
          ??????initSheet.setAutoWidth(Boolean.TRUE);
          ???}

          ???/**
          ????* 讀取少于1000行數(shù)據(jù)
          ????* @param?filePath 文件絕對路徑
          ????* @return
          ????*/
          ???public?static?List readLessThan1000Row(String filePath){
          ??????return?readLessThan1000RowBySheet(filePath,null);
          ???}

          ???/**
          ????* 讀小于1000行數(shù)據(jù), 帶樣式
          ????* filePath 文件絕對路徑
          ????* initSheet ：
          ????* sheetNo: sheet頁碼，默認為1
          ????* headLineMun: 從第幾行開始讀取數(shù)據(jù)，默認為0, 表示從第一行開始讀取
          ????* clazz: 返回數(shù)據(jù)List 中Object的類名
          ????*/
          ???public?static?List readLessThan1000RowBySheet(String filePath, Sheet sheet){
          ??????if(!StringUtils.hasText(filePath)){
          ?????????return?null;
          ??????}

          ??????sheet = sheet != null?? sheet : initSheet;

          ??????InputStream fileStream = null;
          ??????try?{
          ?????????fileStream = new?FileInputStream(filePath);
          ?????????return?EasyExcelFactory.read(fileStream, sheet);
          ??????} catch?(FileNotFoundException e) {
          ?????????log.info("找不到文件或文件路徑錯誤, 文件：{}", filePath);
          ??????}finally?{
          ?????????try?{
          ????????????if(fileStream != null){
          ???????????????fileStream.close();
          ????????????}
          ?????????} catch?(IOException e) {
          ????????????log.info("excel文件讀取失敗, 失敗原因：{}", e);
          ?????????}
          ??????}
          ??????return?null;
          ???}

          ???/**
          ????* 讀大于1000行數(shù)據(jù)
          ????* @param?filePath 文件覺得路徑
          ????* @return
          ????*/
          ???public?static?List readMoreThan1000Row(String filePath){
          ??????return?readMoreThan1000RowBySheet(filePath,null);
          ???}

          ???/**
          ????* 讀大于1000行數(shù)據(jù), 帶樣式
          ????* @param?filePath 文件覺得路徑
          ????* @return
          ????*/
          ???public?static?List readMoreThan1000RowBySheet(String filePath, Sheet sheet){
          ??????if(!StringUtils.hasText(filePath)){
          ?????????return?null;
          ??????}

          ??????sheet = sheet != null?? sheet : initSheet;

          ??????InputStream fileStream = null;
          ??????try?{
          ?????????fileStream = new?FileInputStream(filePath);
          ?????????ExcelListener excelListener = new?ExcelListener();
          ?????????EasyExcelFactory.readBySax(fileStream, sheet, excelListener);
          ?????????return?excelListener.getDatas();
          ??????} catch?(FileNotFoundException e) {
          ?????????log.error("找不到文件或文件路徑錯誤, 文件：{}", filePath);
          ??????}finally?{
          ?????????try?{
          ????????????if(fileStream != null){
          ???????????????fileStream.close();
          ????????????}
          ?????????} catch?(IOException e) {
          ????????????log.error("excel文件讀取失敗, 失敗原因：{}", e);
          ?????????}
          ??????}
          ??????return?null;
          ???}

          ???/**
          ????* 生成excle
          ????* @param?filePath 絕對路徑, 如：/home/chenmingjian/Downloads/aaa.xlsx
          ????* @param?data 數(shù)據(jù)源
          ????* @param?head 表頭
          ????*/
          ???public?static?void writeBySimple(String filePath, List<List> data, List head){
          ??????writeSimpleBySheet(filePath,data,head,null);
          ???}

          ???/**
          ????* 生成excle
          ????* @param?filePath 絕對路徑, 如：/home/chenmingjian/Downloads/aaa.xlsx
          ????* @param?data 數(shù)據(jù)源
          ????* @param?sheet excle頁面樣式
          ????* @param?head 表頭
          ????*/
          ???public?static?void writeSimpleBySheet(String filePath, List<List> data, List head, Sheet sheet){
          ??????sheet = (sheet != null) ? sheet : initSheet;

          ??????if(head != null){
          ?????????List<List> list?= new?ArrayList<>();
          ?????????head.forEach(h -> list.add(Collections.singletonList(h)));
          ?????????sheet.setHead(list);
          ??????}

          ??????OutputStream outputStream = null;
          ??????ExcelWriter writer = null;
          ??????try?{
          ?????????outputStream = new?FileOutputStream(filePath);
          ?????????writer = EasyExcelFactory.getWriter(outputStream);
          ?????????writer.write1(data,sheet);
          ??????} catch?(FileNotFoundException e) {
          ?????????log.error("找不到文件或文件路徑錯誤, 文件：{}", filePath);
          ??????}finally?{
          ?????????try?{
          ????????????if(writer != null){
          ???????????????writer.finish();
          ????????????}

          ????????????if(outputStream != null){
          ???????????????outputStream.close();
          ????????????}

          ?????????} catch?(IOException e) {
          ????????????log.error("excel文件導出失敗, 失敗原因：{}", e);
          ?????????}
          ??????}

          ???}

          ???/**
          ????* 生成excle
          ????* @param?filePath 絕對路徑, 如：/home/chenmingjian/Downloads/aaa.xlsx
          ????* @param?data 數(shù)據(jù)源
          ????*/
          ???public?static?void writeWithTemplate(String filePath, List?extends BaseRowModel> data){
          ??????writeWithTemplateAndSheet(filePath,data,null);
          ???}

          ???/**
          ????* 生成excle
          ????* @param?filePath 絕對路徑, 如：/home/chenmingjian/Downloads/aaa.xlsx
          ????* @param?data 數(shù)據(jù)源
          ????* @param?sheet excle頁面樣式
          ????*/
          ???public?static?void writeWithTemplateAndSheet(String filePath, List?extends BaseRowModel> data, Sheet sheet){
          ??????if(CollectionUtils.isEmpty(data)){
          ?????????return;
          ??????}

          ??????sheet = (sheet != null) ? sheet : initSheet;
          ??????sheet.setClazz(data.get(0).getClass());

          ??????OutputStream outputStream = null;
          ??????ExcelWriter writer = null;
          ??????try?{
          ?????????outputStream = new?FileOutputStream(filePath);
          ?????????writer = EasyExcelFactory.getWriter(outputStream);
          ?????????writer.write(data,sheet);
          ??????} catch?(FileNotFoundException e) {
          ?????????log.error("找不到文件或文件路徑錯誤, 文件：{}", filePath);
          ??????}finally?{
          ?????????try?{
          ????????????if(writer != null){
          ???????????????writer.finish();
          ????????????}

          ????????????if(outputStream != null){
          ???????????????outputStream.close();
          ????????????}
          ?????????} catch?(IOException e) {
          ????????????log.error("excel文件導出失敗, 失敗原因：{}", e);
          ?????????}
          ??????}

          ???}

          ???/**
          ????* 生成多Sheet的excle
          ????* @param?filePath 絕對路徑, 如：/home/chenmingjian/Downloads/aaa.xlsx
          ????* @param?multipleSheelPropetys
          ????*/
          ???public?static?void writeWithMultipleSheel(String filePath,List multipleSheelPropetys){
          ??????if(CollectionUtils.isEmpty(multipleSheelPropetys)){
          ?????????return;
          ??????}

          ??????OutputStream outputStream = null;
          ??????ExcelWriter writer = null;
          ??????try?{
          ?????????outputStream = new?FileOutputStream(filePath);
          ?????????writer = EasyExcelFactory.getWriter(outputStream);
          ?????????for?(MultipleSheelPropety multipleSheelPropety : multipleSheelPropetys) {
          ????????????Sheet sheet = multipleSheelPropety.getSheet() != null?? multipleSheelPropety.getSheet() : initSheet;
          ????????????if(!CollectionUtils.isEmpty(multipleSheelPropety.getData())){
          ???????????????sheet.setClazz(multipleSheelPropety.getData().get(0).getClass());
          ????????????}
          ????????????writer.write(multipleSheelPropety.getData(), sheet);
          ?????????}

          ??????} catch?(FileNotFoundException e) {
          ?????????log.error("找不到文件或文件路徑錯誤, 文件：{}", filePath);
          ??????}finally?{
          ?????????try?{
          ????????????if(writer != null){
          ???????????????writer.finish();
          ????????????}

          ????????????if(outputStream != null){
          ???????????????outputStream.close();
          ????????????}
          ?????????} catch?(IOException e) {
          ????????????log.error("excel文件導出失敗, 失敗原因：{}", e);
          ?????????}
          ??????}

          ???}


          ???/*********************匿名內(nèi)部類開始，可以提取出去******************************/

          ???@Data
          ???public?static?class?MultipleSheelPropety{

          ??????private?List?extends BaseRowModel> data;

          ??????private?Sheet sheet;
          ???}

          ???/**
          ????* 解析監(jiān)聽器，
          ????* 每解析一行會回調(diào)invoke()方法。
          ????* 整個excel解析結(jié)束會執(zhí)行doAfterAllAnalysed()方法
          ????*
          ????* @author: chenmingjian
          ????* @date: 19-4-3 14:11
          ????*/
          ???@Getter
          ???@Setter
          ???public?static?class?ExcelListener?extends?AnalysisEventListener?{

          ??????private?List datas = new?ArrayList<>();

          ??????/**
          ???????* 逐行解析
          ???????* object : 當前行的數(shù)據(jù)
          ???????*/
          ??????@Override
          ??????public?void invoke(Object object, AnalysisContext context) {
          ?????????//當前行
          ?????????// context.getCurrentRowNum()
          ?????????if?(object != null) {
          ????????????datas.add(object);
          ?????????}
          ??????}


          ??????/**
          ???????* 解析完所有數(shù)據(jù)后會調(diào)用該方法
          ???????*/
          ??????@Override
          ??????public?void doAfterAllAnalysed(AnalysisContext context) {
          ?????????//解析結(jié)束銷毀不用的資源
          ??????}

          ???}

          ???/************************匿名內(nèi)部類結(jié)束，可以提取出去***************************/

          }
          測試類
          /**
          ?* @description: 測試類
          ?* @author: chenmingjian
          ?* @date: 19-4-4 15:24
          ?*/
          @SpringBootTest
          @RunWith(SpringRunner.class)
          public?class?Test {

          ????/**
          ?????* 讀取少于1000行的excle
          ?????*/
          ????@org.junit.Test
          ????public?void?readLessThan1000Row(){
          ????????String?filePath = "/home/chenmingjian/Downloads/測試.xlsx";
          ????????List<Object> objects = ExcelUtil.readLessThan1000Row(filePath);
          ????????objects.forEach(System.out::println);
          ????}

          ????/**
          ?????* 讀取少于1000行的excle，可以指定sheet和從幾行讀起
          ?????*/
          ????@org.junit.Test
          ????public?void?readLessThan1000RowBySheet(){
          ????????String?filePath = "/home/chenmingjian/Downloads/測試.xlsx";
          ????????Sheet sheet = new?Sheet(1, 1);
          ????????List<Object> objects = ExcelUtil.readLessThan1000RowBySheet(filePath,sheet);
          ????????objects.forEach(System.out::println);
          ????}

          ????/**
          ?????* 讀取大于1000行的excle
          ?????* 帶sheet參數(shù)的方法可參照測試方法readLessThan1000RowBySheet()
          ?????*/
          ????@org.junit.Test
          ????public?void?readMoreThan1000Row(){
          ????????String?filePath = "/home/chenmingjian/Downloads/測試.xlsx";
          ????????List<Object> objects = ExcelUtil.readMoreThan1000Row(filePath);
          ????????objects.forEach(System.out::println);
          ????}


          ????/**
          ?????* 生成excle
          ?????* 帶sheet參數(shù)的方法可參照測試方法readLessThan1000RowBySheet()
          ?????*/
          ????@org.junit.Test
          ????public?void?writeBySimple(){
          ????????String?filePath = "/home/chenmingjian/Downloads/測試.xlsx";
          ????????ListObject          >> data = new?ArrayList<>();
          ????????data.add(Arrays.asList("111","222","333"));
          ????????data.add(Arrays.asList("111","222","333"));
          ????????data.add(Arrays.asList("111","222","333"));
          ????????List<String> head = Arrays.asList("表頭1", "表頭2", "表頭3");
          ????????ExcelUtil.writeBySimple(filePath,data,head);
          ????}


          ????/**
          ?????* 生成excle, 帶用模型
          ?????* 帶sheet參數(shù)的方法可參照測試方法readLessThan1000RowBySheet()
          ?????*/
          ????@org.junit.Test
          ????public?void?writeWithTemplate(){
          ????????String?filePath = "/home/chenmingjian/Downloads/測試.xlsx";
          ????????ArrayList data = new?ArrayList<>();
          ????????for(int i = 0; i < 4; i++){
          ????????????TableHeaderExcelProperty tableHeaderExcelProperty = new?TableHeaderExcelProperty();
          ????????????tableHeaderExcelProperty.setName("cmj"?+ i);
          ????????????tableHeaderExcelProperty.setAge(22?+ i);
          ????????????tableHeaderExcelProperty.setSchool("清華大學"?+ i);
          ????????????data.add(tableHeaderExcelProperty);
          ????????}
          ????????ExcelUtil.writeWithTemplate(filePath,data);
          ????}


          ????/**
          ?????* 生成excle, 帶用模型,帶多個sheet
          ?????*/
          ????@org.junit.Test
          ????public?void?writeWithMultipleSheel(){
          ????????ArrayList list1 = new?ArrayList<>();
          ????????for(int j = 1; j < 4; j++){
          ????????????ArrayList list = new?ArrayList<>();
          ????????????for(int i = 0; i < 4; i++){
          ????????????????TableHeaderExcelProperty tableHeaderExcelProperty = new?TableHeaderExcelProperty();
          ????????????????tableHeaderExcelProperty.setName("cmj"?+ i);
          ????????????????tableHeaderExcelProperty.setAge(22?+ i);
          ????????????????tableHeaderExcelProperty.setSchool("清華大學"?+ i);
          ????????????????list.add(tableHeaderExcelProperty);
          ????????????}

          ????????????Sheet sheet = new?Sheet(j, 0);
          ????????????sheet.setSheetName("sheet"?+ j);

          ????????????ExcelUtil.MultipleSheelPropety multipleSheelPropety = new?ExcelUtil.MultipleSheelPropety();
          ????????????multipleSheelPropety.setData(list);
          ????????????multipleSheelPropety.setSheet(sheet);

          ????????????list1.add(multipleSheelPropety);

          ????????}

          ????????ExcelUtil.writeWithMultipleSheel("/home/chenmingjian/Downloads/aaa.xlsx",list1);

          ????}


          ????/*******************匿名內(nèi)部類，實際開發(fā)中該對象要提取出去**********************/
          ????
          ????
          ????/**
          ?????* @description:
          ?????* @author: chenmingjian
          ?????* @date: 19-4-3 14:44
          ?????*/
          ????@EqualsAndHashCode(callSuper = true)
          ????@Data
          ????public?static?class?TableHeaderExcelProperty extends?BaseRowModel {

          ????????/**
          ?????????* value: 表頭名稱
          ?????????* index: 列的號, 0表示第一列
          ?????????*/
          ????????@ExcelProperty(value = "姓名", index = 0)
          ????????private?String?name;

          ????????@ExcelProperty(value = "年齡",index = 1)
          ????????private?int age;

          ????????@ExcelProperty(value = "學校",index = 2)
          ????????private?String?school;
          ????}

          ????/*******************匿名內(nèi)部類，實際開發(fā)中該對象要提取出去**********************/

          }
          來源：blog.csdn.net/qq_32258777/article/details/89031479

          往
          期
          推
          薦
          1、致歉！抖音Semi Design承認參考阿里Ant Design
          2、對比7種分布式事務方案，還是偏愛阿里開源的Seata，真香！
          3、Redis存儲結(jié)構(gòu)體信息，選hash還是string？
          4、掃盲 docker 常用命令
          5、最全分布式Session解決方案
          6、21 款 yyds 的 IDEA插件
          7、真香！用 IDEA 神器看源碼，效率真高！
          點分享
          點收藏
          點點贊
          點在看
          
                
          瀏覽
                    29
          點贊
    
          評論
    
          收藏
    
          分享
        
          手機掃一掃分享
          分享
    
          
        舉報
    
          評論
          圖片
          表情
          推薦
        
          點贊
    
          評論
    
          收藏
    
          分享
        
          手機掃一掃分享
          分享
    
          
        舉報
    
          

          <kbd id="afajh"><form id="afajh"></form></kbd>
          <strong id="afajh"><dl id="afajh"></dl></strong>
            2. 

            <del id="afajh"><form id="afajh"></form></del>
            

            5. 

                1. <th id="afajh"><progress id="afajh"></progress></th>

                  <b id="afajh"><abbr id="afajh"></abbr></b>
                    <th id="afajh"><progress id="afajh"></progress></th>
                    

免费看黄色A
|
黄色影院在线观看
|
在线观看亚洲免费
|
AV一二三
|
五月天丁香
|