这是第一个简书的文章，希望之后越走越远！
功能：
- 数据是个长json串，json中有许多需要提取的字段信息存入Excel中
- 需要提取的字段与数据：fname，title，auth，date，copyright
- 注：D:/jsonToExcel.txt文件中保存的是json格式的数据，解析后使用poi写入到excel文件中
Java代码
import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.OutputStream;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;
public class Spider {
public static void main(String[] args) {
spider();
}
public static void spider() {
StringBuilder sb = new StringBuilder();
try {
FileReader in = new FileReader(new File("D:/jsonToExcel.txt"));
BufferedReader inBR = new BufferedReader(in);
String stext = null;
//读取每一行内容
while ((stext = inBR.readLine()) != null) {
sb.append(stext);
}
inBR.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
//创建文件
String uploadFile = "D:/jsonToExcel.xlsx";
OutputStream fos = new FileOutputStream(uploadFile);
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFSheet sheet = workBook.createSheet();
XSSFRow row = null;
XSSFCell cell = null;
row = sheet.createRow(0);
String[] names = {"序号", "fname", "title", "copyright", "date", "auth"};
for (int index = 0; index < 6; index++) {
cell = row.createCell(index);
cell.setCellValue(names[index]);
}
int count = 1;
JSONArray jsonArray = new JSONArray(sb.toString());
for (Object object : jsonArray) {
JSONObject item = (JSONObject) object;
String fname = item.getString("fname");
String title = item.getString("title");
String copyright = item.getString("copyright");
String date = item.getJSONObject("ss").getString("date");
JSONArray auths = item.getJSONArray("auth");
String au = "";
for (Object object2: auths) {
JSONObject item2 = (JSONObject) object2;
String hel = item2.getString("hel");
String fst = item2.getString("fst");
String lst = item2.getString("lst");
au += hel + " " + fst+ " " + lst + ",";
}
row = sheet.createRow(count);
cell = row.createCell(0);
cell.setCellValue(count);
cell = row.createCell(1);
cell.setCellValue(fname);
cell = row.createCell(2);
cell.setCellValue(title);
cell = row.createCell(3);
cell.setCellValue(copyright);
cell = row.createCell(4);
cell.setCellValue(date);
cell = row.createCell(5);
cell.setCellValue(au);
count++;
}
//写入Excel中
workBook.write(fos);
} catch (Exception e) {
e.printStackTrace();
}
}
}
json串(也就是jsonToExcel.txt)
[
{
"ss": {
"name": "1.",
"snm": "1",
"date": "2021-01-01"
},
"title": "title1",
"fname": "name1",
"auth": [
{
"hel": "Mrs.",
"fst": "El",
"lst": "Feinger"
},
{
"hel": "Prf.",
"fst": "Nalia",
"lst": "Kulesva"
},
{
"hel": "Dr.",
"fst": "All",
"lst": "Vinok"
}
],
"copyright": "Copyright 2021 All rights reserved.",
"cb": false
},
{
"ss": {
"name": "1.",
"snm": "1",
"date": "2021-01-01"
},
"title": "title2",
"fname": "name2",
"auth": [
{
"hel": "Mr.",
"fst": "go",
"lst": "Miso"
},
{
"hel": "Dr.",
"fst": "Rap",
"lst": "Ro"
}
],
"copyright": "Copyright 2021.",
"cb": false
},
{
"ss": {
"name": "1.",
"snm": "1",
"date": "2021-01-01"
},
"title": "title3",
"fname": "name3",
"auth": [
{
"hel": "Dr.",
"fst": "Vad",
"lst": "Ghin"
},
{
"hel": "Ms.",
"fst": "Slna",
"lst": "Leba"
},
{
"hel": "Dr.",
"fst": "Alina",
"lst": "Cha"
}
],
"copyright": "Copyright 2021 All rights reserved.",
"cb": false
},
{
"ss": {
"name": "1.",
"snm": "1",
"date": "2021-01-01"
},
"title": "title4",
"fname": "name4",
"auth": [
{
"hel": "Ms.",
"fst": "Yu",
"lst": "Lei"
},
{
"hel": "Prf.",
"fst": "Ming",
"lst": "Li"
}
],
"copyright": "Copyright 2021.",
"cb": false
},
{
"ss": {
"name": "1.",
"snm": "1",
"date": "2021-01-01"
},
"title": "title5",
"fname": "name5",
"auth": [
{
"hel": "Mr.",
"fst": "Kyke",
"lst": "Tan"
},
{
"hel": "Prof.",
"fst": "Nos",
"lst": "Contor"
}
],
"copyright": "Copyright notice: For more information - please contact the main auththor.",
"cb": false
}
]
pom.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.json</groupId>
<artifactId>JsonToExcel</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.json</groupId>
<artifactId>json</artifactId>
<version>20160807</version>
</dependency>
</dependencies>
</project>
参考于：https://blog.csdn.net/bowei026/article/details/105001875
2021，勤奋学习，成就自我！平凡之路，一起走过！
