Java读取Excel文件转换成JSON并转成List——(七)

时间:2022-06-13 12:24:18

Jar包

Java读取Excel文件转换成JSON并转成List——(七)

Java读取Excel文件转换成JSON并转成List——(七)

Java读取Excel文件转换成JSON并转成List——(七)

 

 实体类

 1 package bean;
 2 
 3 public class Question {
 4 
 5     private String timu; //题干
 6     private String leixing;  //类型
 7     private String axuanxiang;  //A选项
 8     private String bxuanxiang;   //B选项
 9     private String cxuanxiang;   //C选项
10     private String dxuanxiang;   //D选项
11     private String daan;          //答案
12     private String jiexi;        //解析
13     public String getTimu() {
14         return timu;
15     }
16     public void setTimu(String timu) {
17         this.timu = timu;
18     }
19     public String getLeixing() {
20         return leixing;
21     }
22     public void setLeixing(String leixing) {
23         this.leixing = leixing;
24     }
25 
26     public String getAxuanxiang() {
27         return axuanxiang;
28     }
29     public void setAxuanxiang(String axuanxiang) {
30         this.axuanxiang = axuanxiang;
31     }
32     public String getBxuanxiang() {
33         return bxuanxiang;
34     }
35     public void setBxuanxiang(String bxuanxiang) {
36         this.bxuanxiang = bxuanxiang;
37     }
38     public String getCxuanxiang() {
39         return cxuanxiang;
40     }
41     public void setCxuanxiang(String cxuanxiang) {
42         this.cxuanxiang = cxuanxiang;
43     }
44     public String getDxuanxiang() {
45         return dxuanxiang;
46     }
47     public void setDxuanxiang(String dxuanxiang) {
48         this.dxuanxiang = dxuanxiang;
49     }
50     public String getDaan() {
51         return daan;
52     }
53     public void setDaan(String daan) {
54         this.daan = daan;
55     }
56     public String getJiexi() {
57         return jiexi;
58     }
59     public void setJiexi(String jiexi) {
60         this.jiexi = jiexi;
61     }
62     @Override
63     public String toString() {
64         return "Question [timu=" + timu + ", leixing=" + leixing + ", axuanxiang=" + axuanxiang + ", bxuanxiang="
65                 + bxuanxiang + ", cxuanxiang=" + cxuanxiang + ", dxuanxiang=" + dxuanxiang + ", daan=" + daan
66                 + ", jiexi=" + jiexi + "]";
67     }
68     
69 
70     
71 }

 Excel转成JSON

 1 package Action.Excel2;
 2 
 3 import jxl.Cell;
 4 import jxl.Sheet;
 5 import jxl.Workbook;
 6 import net.sf.json.JSONArray;
 7 import net.sf.json.JSONObject;
 8 
 9 import java.io.File;
10 
11 public class Excel2JSON {
12 
13     /**
14      * 将Excel数据转为JSON格式数组(题对象list)
15      *@param name Excel路径
16      *@return Json格式数组
17      */
18     public static String excel2Json(String name) {
19         Sheet sheet;
20         Workbook book;
21         Cell cell1, cell2, cell3, cell4, cell5,cell6,cell7,cell8;
22         JSONArray array = new JSONArray();
23         try {
24             //为要读取的excel文件名  "F://a.xls"
25             book = Workbook.getWorkbook(new File("F://a.xls"));
26 
27             //获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)
28             sheet = book.getSheet(0);
29 
30             for (int i = 2; i < sheet.getRows(); i++) {
31                 //获取每一行的单元格
32                 cell1 = sheet.getCell(0, i);//(列,行)
33                 cell2 = sheet.getCell(1, i);
34                 cell3 = sheet.getCell(2, i);
35                 cell4 = sheet.getCell(3, i);
36                 cell5 = sheet.getCell(4, i);
37                 cell6 = sheet.getCell(5, i);
38                 cell7 = sheet.getCell(6, i);
39                 cell8 = sheet.getCell(7, i);
40                 if ("".equals(cell1.getContents())) {//如果读取的数据为空
41                     break;
42                 }
43                 JSONObject object = new JSONObject();
44                 object.put("timu",cell1.getContents());
45                 object.put("leixing",cell2.getContents());
46                 object.put("axuanxiang",cell3.getContents());
47                 object.put("bxuanxiang",cell4.getContents());
48                 object.put("cxuanxiang",cell5.getContents());
49                 object.put("dxuanxiang",cell6.getContents());
50                 object.put("daan",cell7.getContents());
51                 object.put("jiexi",cell8.getContents());
52                 array.add(object);
53             }
54             System.out.println(array.toString());
55             book.close();
56         } catch (Exception e) {
57             e.printStackTrace();
58         }
59         return array.toString();
60     }
61 }

 

 JSON转成List

 1 package Action.Excel2;
 2 
 3 import java.util.ArrayList;
 4 import java.util.HashMap;
 5 import java.util.List;
 6 import java.util.Map;
 7 
 8 import org.junit.Test;
 9 
10 import com.google.gson.Gson;
11 import com.google.gson.reflect.TypeToken;
12 import com.sun.org.apache.bcel.internal.generic.Type;
13 
14 import bean.Question;
15 
16 public class JSON2BeanList {
17     
18     
19     
20 //    测试JSON转list
21     /**
22      * 将字符串转为list集合对象
23      *@param json json数组字符串
24      *@return list集合
25      */
26     @SuppressWarnings("unchecked")
27     public static  List<Question> json2list(String json){
28         List<Question> list = new ArrayList<Question>();
29         Gson gson = new Gson();
30         list = (List<Question>) gson.fromJson(json, new TypeToken<List<Question>>(){}.getType());
31         return list;
32     }
33     
34 }

 

 测试:

Java读取Excel文件转换成JSON并转成List——(七)

Java代码

package Action.Excel2;

import java.util.List;

import org.junit.Test;
import org.junit.validator.PublicClassValidator;

import com.sun.org.apache.xml.internal.resolver.helpers.PublicId;

import bean.Question;

public class test1 {

    @Test
    public void test1(){
        String excel2Json = Excel2JSON.excel2Json("F://a.xls");
        List<Question> json2list = (List<Question>)JSON2BeanList.json2list(excel2Json);
        System.out.println(json2list);
        System.out.println(json2list.size());
        for(Question q:json2list){
            System.out.println(q);
            System.out.println(q.getAxuanxiang());
        }
    }
}

 

 

[{"timu":"你喜欢吃什么","leixing":"单选","axuanxiang":"1","bxuanxiang":"2","cxuanxiang":"3","dxuanxiang":"4","daan":"1","jiexi":"1"},{"timu":"你喜欢吃什么","leixing":"单选","axuanxiang":"1","bxuanxiang":"2","cxuanxiang":"3","dxuanxiang":"4","daan":"1","jiexi":"1"},{"timu":"你喜欢吃什么","leixing":"单选","axuanxiang":"1","bxuanxiang":"2","cxuanxiang":"3","dxuanxiang":"4","daan":"1","jiexi":"1"},{"timu":"你喜欢吃什么","leixing":"单选","axuanxiang":"1","bxuanxiang":"2","cxuanxiang":"3","dxuanxiang":"4","daan":"1","jiexi":"1"},{"timu":"你喜欢吃什么","leixing":"单选","axuanxiang":"1","bxuanxiang":"2","cxuanxiang":"3","dxuanxiang":"4","daan":"1","jiexi":"1"}]
[Question [timu=你喜欢吃什么, leixing=单选, axuanxiang=1, bxuanxiang=2, cxuanxiang=3, dxuanxiang=4, daan=1, jiexi=1], Question [timu=你喜欢吃什么, leixing=单选, axuanxiang=1, bxuanxiang=2, cxuanxiang=3, dxuanxiang=4, daan=1, jiexi=1], Question [timu=你喜欢吃什么, leixing=单选, axuanxiang=1, bxuanxiang=2, cxuanxiang=3, dxuanxiang=4, daan=1, jiexi=1], Question [timu=你喜欢吃什么, leixing=单选, axuanxiang=1, bxuanxiang=2, cxuanxiang=3, dxuanxiang=4, daan=1, jiexi=1], Question [timu=你喜欢吃什么, leixing=单选, axuanxiang=1, bxuanxiang=2, cxuanxiang=3, dxuanxiang=4, daan=1, jiexi=1]]
5
Question [timu=你喜欢吃什么, leixing=单选, axuanxiang=1, bxuanxiang=2, cxuanxiang=3, dxuanxiang=4, daan=1, jiexi=1]
1
Question [timu=你喜欢吃什么, leixing=单选, axuanxiang=1, bxuanxiang=2, cxuanxiang=3, dxuanxiang=4, daan=1, jiexi=1]
1
Question [timu=你喜欢吃什么, leixing=单选, axuanxiang=1, bxuanxiang=2, cxuanxiang=3, dxuanxiang=4, daan=1, jiexi=1]
1
Question [timu=你喜欢吃什么, leixing=单选, axuanxiang=1, bxuanxiang=2, cxuanxiang=3, dxuanxiang=4, daan=1, jiexi=1]
1
Question [timu=你喜欢吃什么, leixing=单选, axuanxiang=1, bxuanxiang=2, cxuanxiang=3, dxuanxiang=4, daan=1, jiexi=1]
1