1、任务需求:
{"create":{"_id":"1100901"}}
{"kpCode":"901","kpId":"1100901","kpName":"生命的世界","kpPid":"0","kpSetId":"11","position":1,"xkId":"9"}
{"create":{"_id":"1109001"}}
{"kpCode":"9001","kpId":"1109001","kpName":"形形色色的生物","kpPid":"1100901","kpSetId":"11","position":1,"xkId":"9"}
{"create":{"_id":"1190001"}}
{"kpCode":"90001","kpId":"1190001","kpName":"生物的多样性","kpPid":"1109001","kpSetId":"11","position":1,"xkId":"9"}
{"create":{"_id":"1190002"}}
{"kpCode":"90002","kpId":"1190002","kpName":"生物区别于非生物的特征","kpPid":"1190001","kpSetId":"11","position":1,"xkId":"9"}
{"create":{"_id":"1109002"}}
{"kpCode":"9002","kpId":"1109002","kpName":"生物与环境的相互影响","kpPid":"1190002","kpSetId":"11","position":1,"xkId":"9"}
{"create":{"_id":"1190003"}}
{"kpCode":"90003","kpId":"1190003","kpName":"环境因素影响生物的形态、生理和分布","kpPid":"1109002","kpSetId":"11","position":1,"xkId":"9"}
{"create":{"_id":"1190004"}}
{"kpCode":"90004","kpId":"1190004","kpName":"生物能影响和改变环境","kpPid":"1190003","kpSetId":"11","position":1,"xkId":"9"}
{"create":{"_id":"1100902"}}
{"kpCode":"902","kpId":"1100902","kpName":"探索生命","kpPid":"0","kpSetId":"11","position":2,"xkId":"9"}
{"create":{"_id":"1109003"}}
{"kpCode":"9003","kpId":"1109003","kpName":"生物学是探索生命的科学","kpPid":"1100902","kpSetId":"11","position":2,"xkId":"9"}
{"create":{"_id":"1190005"}}
{"kpCode":"90005","kpId":"1190005","kpName":"生物学是探索生命的科学","kpPid":"1109003","kpSetId":"11","position":2,"xkId":"9"}
{"create":{"_id":"1109004"}}
{"kpCode":"9004","kpId":"1109004","kpName":"生物学研究的基本方法","kpPid":"1190005","kpSetId":"11","position":2,"xkId":"9"}
{"create":{"_id":"1190006"}}
把Excel表转化成上边格式
2、技术要点:关键在于字符串拼接
StringBuilder sb1=new StringBuilder(object.toString());
sb1.append("\n");
StringBuilder sb2=new StringBuilder(cell4.getContents() +""+ kpCode);
sb2.insert(0, "{\"create\":{\"_id\":\"");
sb2.append("\"}}\n");
sb2.append(sb1);
EsStr.append(sb2);
3、关键代码:
package Excel2Es;
import java.io.File;
import java.io.FileWriter;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import com.alibaba.fastjson.JSONObject;
public class Excel2Es {
public static void main(String[] args) {
Sheet sheet;
Workbook book;
Cell cell1, cell2, cell3, cell4, cell5, cell6, cell7;
try {
//要读取的excel文件
book = Workbook.getWorkbook(new File(args[0]));
//获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)
sheet = book.getSheet(0);
int position = 0;
StringBuilder EsStr =new StringBuilder();
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);
JSONObject object = new JSONObject();
String kpPid = "0";
if(!"".equals(cell1.getContents())){
kpPid = "0";
}else if(i>1){
cell6 = sheet.getCell(3, i-1);
cell7= sheet.getCell(4, i-1);
String kpCode1 = null;
if(cell7.getContents().length()==3){
kpCode1 = "00"+cell7.getContents();
}else if(cell7.getContents().length()==4){
kpCode1 = "0"+cell7.getContents();
}else if(cell7.getContents().length()==5){
kpCode1 = cell7.getContents();
}
kpPid = cell6.getContents()+""+kpCode1;
}
object.put("kpPid",kpPid);
if(!"".equals(cell1.getContents())){
object.put("kpName",cell1.getContents());
}else if(!"".equals(cell2.getContents())){
object.put("kpName",cell2.getContents());
}else if(!"".equals(cell3.getContents())){
object.put("kpName",cell3.getContents());
}
object.put("kpSetId",cell4.getContents());
object.put("kpCode",cell5.getContents());
String kpCode = null;
if(cell5.getContents().length()==3){
kpCode = "00"+cell5.getContents();
}else if(cell5.getContents().length()==4){
kpCode = "0"+cell5.getContents();
}else if(cell5.getContents().length()==5){
kpCode = cell5.getContents();
}
object.put("kpId", cell4.getContents() +""+ kpCode);
if (!"".equals(cell1.getContents())) {
position++;
}
object.put("position",position);
String s = cell5.getContents();
object.put("xkId",s.subSequence(0, 1));
StringBuilder sb1=new StringBuilder(object.toString());
sb1.append("\n");
StringBuilder sb2=new StringBuilder(cell4.getContents() +""+ kpCode);
sb2.insert(0, "{\"create\":{\"_id\":\"");
sb2.append("\"}}\n");
sb2.append(sb1);
EsStr.append(sb2);
}
System.out.println(EsStr.toString());
FileWriter writer;
writer = new FileWriter(args[1]);
writer.write(EsStr.toString());
writer.flush();
writer.close();
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
上边代码需要通过命令行操作