1、第一步Excel转化为Json
文理科 科目 LINE_NAME LINE_TYPE LINE_STD LINE_UP LINE_DOWN
ol_wk ol_sx 前10% 1 0.1 -1 -1
前30% 1 0.1 -1 -1
前60% 1 0.1 -1 -1
ol_yw 前10% 1 0.1 -1 -1
前30% 1 0.1 -1 -1
前60% 1 0.1 -1 -1
ol_lk ol_wl 前10% 1 0.1 -1 -1
前30% 1 0.1 -1 -1
前60% 1 0.1 -1 -1
ol_hx 前10% 1 0.1 -1 -1
前30% 1 0.1 -1 -1
前60% 1 0.1 -1 -1
ol_qk ol_sw 前10% 1 0.1 -1 -1
前30% 1 0.1 -1 -1
前60% 1 0.1 -1 -1
ol_ls 前10% 1 0.1 -1 -1
前30% 1 0.1 -1 -1
前60% 1 0.1 -1 -1
ol_sx 前10% 1 0.1 -1 -1
前30% 1 0.1 -1 -1
前60% 1 0.1 -1 -1
ol_wl 前10% 1 0.1 -1 -1
前30% 1 0.1 -1 -1
前60% 1 0.2 -1 -1
{
"on_line":{
"ol_wk":{
"ol_sx":[
{"line_down":"-1","line_name":"ǰ10%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ30%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ60%","line_std":"1","line_type":"-1","line_up":"0.1"}],
"ol_yw":[
{"line_down":"-1","line_name":"ǰ10%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ30%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ60%","line_std":"1","line_type":"-1","line_up":"0.1"}]},
"ol_lk":{
"ol_wl":[
{"line_down":"-1","line_name":"ǰ10%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ30%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ60%","line_std":"1","line_type":"-1","line_up":"0.1"}],
"ol_hx":[
{"line_down":"-1","line_name":"ǰ10%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ30%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ60%","line_std":"1","line_type":"-1","line_up":"0.1"}]},
"ol_qk":{
"ol_sw":[
{"line_down":"-1","line_name":"ǰ10%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ30%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ60%","line_std":"1","line_type":"-1","line_up":"0.1"}],
"ol_ls":[
{"line_down":"-1","line_name":"ǰ10%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ30%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ60%","line_std":"1","line_type":"-1","line_up":"0.1"}],
"ol_sx":[
{"line_down":"-1","line_name":"ǰ10%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ30%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ60%","line_std":"1","line_type":"-1","line_up":"0.1"}],
"ol_wl":[
{"line_down":"-1","line_name":"ǰ10%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ30%","line_std":"1","line_type":"-1","line_up":"0.1"},
{"line_down":"-1","line_name":"ǰ60%","line_std":"1","line_type":"-1","line_up":"0.2"}]}
}
}
2、第二步:代码的艺术
package olExcel2Json;
import java.io.File;
import java.io.FileWriter;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import com.alibaba.fastjson.JSONObject;
public class olExcel2Json {
public static void main(String[] args) {
Sheet sheet;
Workbook book;
Cell cell1, cell2, cell3, cell4, cell5, cell6, cell7;
StringBuilder EsStr =new StringBuilder();
try {
book = Workbook.getWorkbook(new File(args[0]));
sheet = book.getSheet(0);
for (int i = 1; i < sheet.getRows(); i++) {
cell1 = sheet.getCell(0, i);
cell2 = sheet.getCell(1, i);
cell3 = sheet.getCell(2, i);
cell4 = sheet.getCell(3, i);
cell5 = sheet.getCell(4, i);
cell6 = sheet.getCell(5, i);
cell7 = sheet.getCell(6, i);
JSONObject object = new JSONObject();
object.put("line_name",cell3.getContents());
object.put("line_std",cell4.getContents());
object.put("line_up",cell5.getContents());
object.put("line_type",cell6.getContents());
object.put("line_down",cell7.getContents());
StringBuilder sb1=new StringBuilder(object.toString());
if (!"".equals(cell2.getContents())) {
sb1.insert(0, "\""+cell2.getContents()+"\""+":[\n\t\t\t");
}
if(i%3==0){
sb1.append("],\n\t");
}else{
sb1.append(",\n\t\t");
}
if (!"".equals(cell1.getContents())) {
sb1.insert(0, "\""+cell1.getContents()+"\""+":{\n\t\t");
}else{
sb1.insert(0, "\t");
}
if(!"".equals(cell1.getContents()) && i != 1){
sb1.insert(0, "}");
}
if(i==1){
sb1.insert(0, "{\n\"on_line\":{\n\t");
}
EsStr.append(sb1);
}
EsStr.append("}\n}");
String jsonStr = EsStr.toString();
String jsonStr1 = jsonStr.replaceAll("],\n\t}", "]},\n\t");
String jsonStr2 = jsonStr1.substring(0, jsonStr1.length()-5);
String jsonStr3 = jsonStr2+"\n\t}\n}";
System.out.println(jsonStr3);
FileWriter writer;
writer = new FileWriter(args[1]);
writer.write(jsonStr3);
writer.flush();
writer.close();
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
3、总结
构造特殊字符串,然后替换
字符串截取
字符串拼接