记录一下java将excel数据转成json数据

冷不防 2022-11-19 04:07 418阅读 0赞

直接上代码,我自己能看懂就行(嘻嘻)

import com.alibaba.excel.EasyExcelFactory;
    import com.alibaba.excel.metadata.Sheet;
    import cwbb.qtkjzd_mjfyl.qtkjzdhxzgsb01341.XjllbmjfylzzGridlb;
    import cwbb.qtkjzd_mjfyl.qtkjzdhxzgsb01341.YwhdbmjfylzzGridlb;
    import cwbb.qtkjzd_mjfyl.qtkjzdhxzgsb01341.ZcfzbmjfylzzGridlb;
    import net.sf.json.JSONArray;
    import org.junit.Test;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.math.BigDecimal;
    import java.util.*;
    
    
    public class MainTests {
        
    
        @Test
        public void test1() {
        
            // 读取 excel 表格的路径
            String readPath = "D:\\）.xlsx";
            try {
        
                // sheetNo --> 读取哪一个 表单
                // headLineMun --> 从哪一行开始读取( 不包括定义的这一行，比如 headLineMun为2 ，那么取出来的数据是从 第三行的数据开始读取 )
                // clazz --> 将读取的数据，转化成对应的实体，需要 extends BaseRowModel
                Sheet sheet = new Sheet(3, 5, XjllbmjfylzzGridlb.class);
                List<Object> readList = EasyExcelFactory.read(new FileInputStream(readPath), sheet);
                LinkedList<Object> l = new LinkedList<>();
                for (Object obj : readList) {
        
                    Long l1 = ((XjllbmjfylzzGridlb) obj).getEwbhxh();
                    String l2 = ((XjllbmjfylzzGridlb) obj).getXmmc();
                    BigDecimal l3 = ((XjllbmjfylzzGridlb) obj).getJe();
                    LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                    if (l1 != null) {
        
                        map.put("行次", l1);
                        map.put("项目", l2);
                        map.put("金额", l3);
                        l.add(map);
                    }
                }
                JSONArray jsonArray = JSONArray.fromObject(l);
                System.out.println(jsonArray);
    
            } catch (FileNotFoundException e) {
        
                e.printStackTrace();
            }
        }
    
        @Test
        public void test2() {
        
            String readPath = ".xlsx";
            try {
        
                Sheet sheet = new Sheet(2, 6, YwhdbmjfylzzGridlb.class);
                List<Object> readList = EasyExcelFactory.read(new FileInputStream(readPath), sheet);
                LinkedList<Object> l = new LinkedList<>();
                for (Object obj : readList) {
        
                    Long l1 = ((YwhdbmjfylzzGridlb) obj).getEwbhxh();
                    String l2 = ((YwhdbmjfylzzGridlb) obj).getXmmc();
                    BigDecimal l3 = ((YwhdbmjfylzzGridlb) obj).getFxdxbys();
                    BigDecimal l4 = ((YwhdbmjfylzzGridlb) obj).getXdxbys();
                    BigDecimal l5 = ((YwhdbmjfylzzGridlb) obj).getHjbys();
                    BigDecimal l6 = ((YwhdbmjfylzzGridlb) obj).getFdxdxbnljs();
                    BigDecimal l7 = ((YwhdbmjfylzzGridlb) obj).getXdxbnljs();
                    BigDecimal l8 = ((YwhdbmjfylzzGridlb) obj).getHjbnljs();
                    LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                    if (l1 != null) {
        
                        if (l1 == 13) {
        
                            map.put("行次", l1);
                            map.put("项目", "成本1");
                            l.add(map);
                        } else {
        
                            map.put("行次", l1);
                            map.put("项目", l2);
                            l.add(map);
                        }
                    }
                }
                JSONArray jsonArray = JSONArray.fromObject(l);
                System.out.println(jsonArray);
            } catch (Exception e) {
        
                e.printStackTrace();
            }
        }
    
        @Test
        public void test3() {
        
            String readPath = "D:\\5.xlsx";
            try {
        
                Sheet sheet = new Sheet(1, 5, ZcfzbmjfylzzGridlb.class);
                List<Object> readList = EasyExcelFactory.read(new FileInputStream(readPath), sheet);
                LinkedList<Object> l = new LinkedList<>();
                for (Object obj : readList) {
        
                    Long l1 = ((ZcfzbmjfylzzGridlb) obj).getEwbhxh();
                    String l2 = ((ZcfzbmjfylzzGridlb) obj).getZcxmmc();
                    BigDecimal l3 = ((ZcfzbmjfylzzGridlb) obj).getNcyeZc();
                    BigDecimal l4 = ((ZcfzbmjfylzzGridlb) obj).getQmyeZc();
                    if (l1 != null) {
        
                        LinkedHashMap<String, Object> map1 = new LinkedHashMap<>();
                        map1.put("行次", l1);
                        l.add(map1);
                    }
                }
                for (Object obj : readList) {
        
                    Long l5 = ((ZcfzbmjfylzzGridlb) obj).getEwbhxh2();
                    String l6 = ((ZcfzbmjfylzzGridlb) obj).getQyxmmc();
                    BigDecimal l7 = ((ZcfzbmjfylzzGridlb) obj).getNcyeQy();
                    BigDecimal l8 = ((ZcfzbmjfylzzGridlb) obj).getQmyeQy();
                    if (l5 != null) {
        
                        LinkedHashMap<String, Object> map2 = new LinkedHashMap<>();
                        map2.put("行次", l5);
                        l.add(map2);
                    }
                }
                JSONArray jsonArray = JSONArray.fromObject(l);
                System.out.println(jsonArray);
            } catch (FileNotFoundException e) {
        
                e.printStackTrace();
            }
        }
    }

实体类

public class XjllbmjfylzzGridlb extends BaseRowModel {
        
        /** 二维表行序号 */
        /*index代表读取的是excel的第几列,从0开始*/
        @ExcelProperty(index = 1)
        private Long ewbhxh;
        
        /** 项目名称 */
        @ExcelProperty(index = 0)
        private String xmmc;
    
        /** 金额 */
        @ExcelProperty(index = 2)
        private BigDecimal je;
    
        public XjllbmjfylzzGridlb() {
        
        }
    
        public XjllbmjfylzzGridlb(Long ewbhxh, String xmmc, BigDecimal je) {
        
            this.ewbhxh = ewbhxh;
            this.xmmc = xmmc;
            this.je = je;
        }
    
        public Long getEwbhxh() {
        
            return ewbhxh;
        }
    
        public void setEwbhxh(Long ewbhxh) {
        
            this.ewbhxh = ewbhxh;
        }
    
        public String getXmmc() {
        
            return xmmc;
        }
    
        public void setXmmc(String xmmc) {
        
            this.xmmc = xmmc;
        }
    
        public BigDecimal getJe() {
        
            return je;
        }
    
        public void setJe(BigDecimal je) {
        
            this.je = je;
        }
    }