假如我们有一段json串,该json串是由一系列结构相同的数据集合组成,如下:
{
"data": [
{
"groupId": "com.test.demo",
"artifactId": "demo-api",
"version": "1.0.0-release",
"latestSnapshot": "2.0.0-SNAPSHOT"
},
{
"groupId": "com.test.demo.core",
"artifactId": "demo-core",
"version": "1.1.3",
"latestSnapshot": "1.3.0-SNAPSHOT"
}
]
}
现在,想要转换成excel表单:
针对该需求,于是在网上找了个excel工具包。
现在将实现代码贴出来,希望给正有此需求的同仁些许帮助。
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import lombok.extern.slf4j.Slf4j;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Iterator;
/**
* @Author changle
* @Time 17/8/1.
* @Desc json转变为Excel演示
*/
@Slf4j
public class JsonToExcelDemo {
public static void main(String[] args) {
parseJsonToExcel("/var/tmp/jsonToExcel-demo.xls", "data", mavenInfos);
}
static void parseJsonToExcel(String saveFileName, String rootNodeName, String sourceJson){
try{
File filewrite=new File(saveFileName);
filewrite.createNewFile();
OutputStream os = new FileOutputStream(filewrite);
JSONObject jsonObject = JSONObject.fromObject(sourceJson);
createExcel(os, jsonObject, rootNodeName);
log.info("解析完毕");
}catch (Exception e){
e.printStackTrace();
}
}
public static void createExcel(OutputStream os, JSONObject apiJarInfos, String rootNodeName) throws WriteException,IOException {
//创建工作薄
WritableWorkbook workbook = Workbook.createWorkbook(os);
//创建新的一页
WritableSheet sheet = workbook.createSheet("First Sheet",0);
JSONArray jsonArray = (JSONArray)apiJarInfos.get(rootNodeName);
JSONObject jsonObjectHeader = jsonArray.getJSONObject(0);
String[] headers = createTableHeader(jsonObjectHeader, sheet);
int size = jsonArray.size()+1;
for(int i=1; i<size; i++){
JSONObject jsonObject = jsonArray.getJSONObject(i-1);
int j = 0;
for (String key : headers){
Label cellValue = new Label(j, i, jsonObject.get(key).toString());
sheet.addCell(cellValue);
j++;
}
}
//把创建的内容写入到输出流中,并关闭输出流
workbook.write();
workbook.close();
os.close();
}
static String[] createTableHeader(JSONObject jsonObjectHeader, WritableSheet sheet) throws WriteException {
//遍历JSONObject中的key
Iterator iterable = jsonObjectHeader.keys();
String[] headers = new String[jsonObjectHeader.size()];
int i = 0;
while (iterable.hasNext()){
//创建要显示的内容,创建一个单元格,第一个参数为列坐标,第二个参数为行坐标,第三个参数为内容
String headerName = iterable.next().toString();
Label cell = new Label(i, 0, headerName);
sheet.addCell(cell);
headers[i] = headerName;
i++;
log.info(headerName);
}
return headers;
}
private static final String mavenInfos ="{ \n" +
" \"data\": [\n" +
" {\n" +
" \"groupId\": \"com.test.demo\",\n" +
" \"artifactId\": \"demo-api\",\n" +
" \"version\": \"1.0.0-release\",\n" +
" \"latestSnapshot\": \"2.0.0-SNAPSHOT\"\n" +
" },\n" +
" {\n" +
" \"groupId\": \"com.test.demo.core\",\n" +
" \"artifactId\": \"demo-core\",\n" +
" \"version\": \"1.1.3\",\n" +
" \"latestSnapshot\": \"1.3.0-SNAPSHOT\"\n" +
" }\n" +
" ]\n" +
"}";
}
执行main方法,得到/var/tmp/api-sources.xls文件。和预想中的效果一样。
需要用到的jar包:
<dependency>
<groupId>org.json</groupId>
<artifactId>json</artifactId>
<version>20160212</version>
</dependency>
<dependency>
<groupId>java-excel-jxl</groupId>
<artifactId>java-excel-jxl</artifactId>
<version>1.0.0</version>
</dependency>