Java读取Excel文件转换成JSON并转成List(实用)

Jar包

实体类

 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代码

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