json导出为excel
package com.util;
import jxl.CellView;
import jxl.Workbook;
import jxl.format.*;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Iterator;
/**
* json导出为excel
* Created by zyb on 2017/3/13.
*/
public class JsonExportExcel {
@SuppressWarnings({ "MethodCanBeVariableArityMethod", "Annotation" })
public static void main(String[] args) {
// 数据的格式如下：data对应JSONArray
//String data = "{'title':['姓名','年龄','性别'],'data': [{'name':'张三', 'age':'20', 'sex':'男'}, {'name':'李四', 'age':'21', 'sex':'女'}, {'name':'王五', 'age':'22', 'sex':'男'}]}";
//String datas = "{'title':[],'data': [{'name':'张三', 'age':'20', 'sex':'男'}, {'name':'李四', 'age':'21', 'sex':'女'}, {'name':'王五', 'age':'22', 'sex':'男'}]}";
//String datas2 = "{'title':[],'data':[{column:'网站总根 > demoZD > demoZD',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 联盟动态 > 联盟动态',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 联盟动态 > 本部动态 > 本部动态',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 联盟动态 > 成员动态 > 成员动态',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 数据信息 > 数据信息',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 数据信息 > 联盟企业 > 联盟企业',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 数据信息 > 海外分支机构或办事处 > 海外分支机构或办事处',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 政策信息 > 政策信息',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 国别信息 > 国别信息',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 项目合作 > 项目合作',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 研究成果 > 研究成果',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'5',20170311:'0',20170312:'0',20170313:'0',count:'5'},{column:'网站总根 > demoZD > 入会说明 > 入会说明',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 入会说明 > 联盟会员规章制度 > 联盟会员规章制度',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 入会说明 > 入会申请条件 > 入会申请条件',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 入会说明 > 入会申请流程 > 入会申请流程',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 入会说明 > 入会申请资料下载 > 入会申请资料下载',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 关于我们 > 关于我们',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 关于我们 > 联盟简介 > 联盟简介',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 关于我们 > 组织结构 > 组织结构',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 关于我们 > 合作机构 > 合作机构',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 关于我们 > 业务范围 > 业务范围',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 关于我们 > 联盟大事记 > 联盟大事记',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 关于我们 > 联系方式 > 联系方式',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 通知公告 > 通知公告',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'0',20170311:'0',20170312:'0',20170313:'0',count:'0'},{column:'网站总根 > demoZD > 要闻咨询 > 要闻咨询',20170301:'0',20170302:'0',20170303:'0',20170304:'0',20170305:'0',20170306:'0',20170307:'0',20170308:'0',20170309:'0',20170310:'27',20170311:'0',20170312:'0',20170313:'0',count:'27'}]}";
// 将函数调用的结果返回给result
//JSONObject result = JsonExportExcel.createExcel("/Users/ZYB/Downloads/a.xls", datas2);
// 输出结果
//System.out.println(result);
}
/**
* 创建excel
*
* @param src 待保存的文件路径 带文件名和后缀xls
* @param strData 待保存的json数据 格式为 "{'title':['title','title2',...],'data':[{'key':'val','key2':'val2'},{...}]}"
* 如果title设置表头则使用，没有则用data key值作表头
* @return 成功{"result":"successed"}或{"result":"error"}、{"error":"Exception原因"}
*/
@SuppressWarnings({ "WeakerAccess", "Annotation", "ForeachStatement" })
public static JSONObject createExcel(String src, String strData) {
JSONObject result = new JSONObject(); // 用来反馈函数调用结果
try {
// 将String的data转换为JSONObject对象jsonObject
JSONObject jsonObject = JSONObject.fromObject(strData);
// 新建文件
File file = new File(src);
file.createNewFile();
OutputStream outputStream = new FileOutputStream(file);// 创建工作薄
WritableWorkbook writableWorkbook = Workbook.createWorkbook(outputStream);
WritableSheet sheet = writableWorkbook.createSheet("First sheet", 0);// 创建新的一页
//给sheet电子版中所有的列设置默认的列的宽度;
sheet.getSettings().setDefaultColumnWidth(10);
CellView cellView = new CellView();
cellView.setAutosize(true); //设置自动大小
sheet.setColumnView(0, cellView);//根据内容自动设置列宽
WritableCellFormat cellFormat = new WritableCellFormat();
//设置背景颜色;
cellFormat.setBackground(Colour.WHITE);
//设置自动换行;
cellFormat.setWrap(true);
//设置文字居中对齐方式;
cellFormat.setAlignment(Alignment.CENTRE);
//设置垂直居中;
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
//给单元格加边框
cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
//得到title对应的JSONArray
JSONArray jsonArrayTitle = jsonObject.getJSONArray("title");
//得到data对应的JSONArray
JSONArray jsonArray = jsonObject.getJSONArray("data");
Label label; // 单元格对象
int column = 0; // 列数计数
JSONObject first = jsonArray.getJSONObject(0);
Iterator<String> iterator = first.keys(); // 得到data的key集合
//如果title设置表头则使用，没有则用data key值作表头
if (jsonArrayTitle != null && jsonArrayTitle.size() > 0) {
// 将表头添加到excel
for (Object o : jsonArrayTitle) {
label = new Label(column++, 0, (String) o, cellFormat); // 第一个参数是单元格所在列,第二个参数是单元格所在行,第三个参数是值
sheet.addCell(label); // 将单元格加到页
}
} else {
while (iterator.hasNext()) { // 遍历key集合
label = new Label(column++, 0, iterator.next(),
cellFormat); // 第一个参数是单元格所在列,第二个参数是单元格所在行,第三个参数是值,第四个是单元格的样式等
sheet.addCell(label); // 将单元格加到页
}
}
// 遍历json对象
for (int i = 0; i < jsonArray.size(); i++) {
JSONObject item = jsonArray.getJSONObject(i); // 得到数组的每项
iterator = item.keys(); // 得到key集合
column = 0;// 从第0列开始放
while (iterator.hasNext()) {
String key = iterator.next(); // 得到key
String value = item.getString(key); // 得到key对应的value
label = new Label(column++, (i + 1), value,
cellFormat); // 第一个参数是单元格所在列,第二个参数是单元格所在行,第三个参数是值,第四个是单元格的样式等
sheet.addCell(label); // 将单元格加到页
}
}
writableWorkbook.write(); // 加入到文件中
writableWorkbook.close(); // 关闭文件，释放资源
} catch (Exception e) {
result.put("result", "error"); // 将调用该函数的结果返回
result.put("error", e.getMessage()); // 将调用该函数失败的原因返回
return result;
}
result.put("result", "successed");
return result;
}
}
