昨天测试让帮忙处理一下接口返回数据,数据量太大,处理费劲😂
主要需求:
接口返回的数据是个长json,需要把其中的某些字段提取出来存到excel
需要提取的字段与数据:ID,NAME,NM_CODE,SPEC_ID
思路:
1.因为json过长,所以就先存到txt中,用fileReader和StringBuffer获取整个json字符串
2.获取到的字符串通过fastjson转换为List<Map>格式
3.把转换后的list转换成excel工作簿,通过fileOutputStream写到excel文件中
读取json文件并获取字符串
public static String getStringFromJsonFile(String fileName) {
System.out.println("读取文件...");
String jsonResult = "";
try {
File file = new File(fileName);
FileReader fileReader = new FileReader(file);
Reader reader = new InputStreamReader(new FileInputStream(file),"utf-8");
int ch = 0;
StringBuffer sb = new StringBuffer();
while ((ch = reader.read()) != -1) {
sb.append((char) ch);
}
fileReader.close();
reader.close();
jsonResult = sb.toString();
System.out.println("读取文件完成");
return jsonResult;
} catch (IOException e) {
e.printStackTrace();
return null;
}
}
从List<Map> 转换到Excel中
public static void listToExcel(List<Map> list) {
System.out.println("数据转换到Excel中...");
// 定义一个新的工作簿
XSSFWorkbook wb = new XSSFWorkbook();
// 创建一个Sheet页
XSSFSheet sheet = wb.createSheet("First sheet");
//设置行高
sheet.setDefaultRowHeight((short) (2 * 256));
//设置列宽
sheet.setColumnWidth(0, 4000);
sheet.setColumnWidth(1, 4000);
sheet.setColumnWidth(2, 4000);
XSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 16);
//获得表格第一行
XSSFRow row = sheet.createRow(0);
//根据需要给第一行每一列设置标题
XSSFCell cell = row.createCell(0);
cell.setCellValue("ID");
cell = row.createCell(1);
cell.setCellValue("NAME");
cell = row.createCell(2);
cell.setCellValue("NM_CODE");
cell = row.createCell(3);
cell.setCellValue("SPEC_ID");
XSSFRow rows;
XSSFCell cells;
//循环拿到的数据给所有行每一列设置对应的值
for (int i = 0; i < list.size(); i++) {
// 在这个sheet页里创建一行
rows = sheet.createRow(i + 1);
// 该行创建一个单元格,在该单元格里设置值
String ID = list.get(i).get("ID").toString();
String NAME = list.get(i).get("NAME").toString();
String NM_CODE = list.get(i).get("NM_CODE").toString();
String SPEC_ID = list.get(i).get("SPEC_ID").toString();
cells = rows.createCell(0);
cells.setCellValue(ID);
cells = rows.createCell(1);
cells.setCellValue(NAME);
cells = rows.createCell(2);
cells.setCellValue(NM_CODE);
cells = rows.createCell(3);
cells.setCellValue(SPEC_ID);
}
try {
File file = new File("D:\\test\\data.xls");
FileOutputStream fileOutputStream = new FileOutputStream(file);
wb.write(fileOutputStream);
fileOutputStream.close();
System.out.println("数据转换完成");
} catch (IOException e) {
e.printStackTrace();
}
}
上面实现了从json(或txt文件)到字符串,list到excel的过程,现在需要把String到List做转换
这里用的是com.alibaba.fastjson包
public static void main(String[] args) {
List list = new ArrayList();
System.out.println("读取文件...");
String data = getStringFromJsonFile("D:\\test\\NRF.txt");
System.out.println("读取文件完成");
JSONObject jsonObject = JSONObject.parseObject(data);
if (jsonObject.containsKey("data")){
JSONObject jsonObject1 = jsonObject.getJSONObject("data");
if (jsonObject1.containsKey("items")){
JSONArray jsonArray = jsonObject1.getJSONArray("items");
System.out.println("jsonArray.size()--"+jsonArray.size());
for (int i = 0;i<jsonArray.size();i++){
JSONObject jsonObject2 = jsonArray.getJSONObject(i);
if (jsonObject2.containsKey("entityInfo")){
Map map1 = new HashMap();
JSONObject jsonObject3 = jsonObject2.getJSONObject("entityInfo");
String ID = "";
String NAME = "";
String NM_CODE = "";
String SPEC_ID = "";
if (jsonObject3.containsKey("ID")){
ID = jsonObject3.getString("ID");
}
if (jsonObject3.containsKey("NAME")){
NAME = jsonObject3.getString("NAME");
}
if (jsonObject3.containsKey("NM_CODE")){
NM_CODE = jsonObject3.getString("NM_CODE");
}
if (jsonObject3.containsKey("SPEC_ID")){
SPEC_ID = jsonObject3.getString("SPEC_ID");
}
map1.put("ID",ID);
map1.put("NAME",NAME);
map1.put("NM_CODE",NM_CODE);
map1.put("SPEC_ID",SPEC_ID);
list.add(map1);
}
}
}
}
System.out.println("list:::"+list);
listToExcel(list);
}
附:用到的jar包