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 json2list(String json){
28 List list = new ArrayList();
29 Gson gson = new Gson();
30 list = (List) gson.fromJson(json, new TypeToken>(){}.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 json2list = (List)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

閱讀更多 程序員界的彭于晏 的文章

關鍵字: Excel JSON 技術