Json字符串转excel表格文件

假如我们有一段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>

java-excel-jxl这个包请下载  https://jaist.dl.sourceforge.net/project/jexcelapi/jexcelapi/2.6.6/jexcelapi_2_6_6.zip  解压。

 

https://blog.csdn.net/skypig555/article/details/77623245

http://j2e.kpoda.com/