当前位置: 首页 > 工具软件 > EXCEL-UTIL4J > 使用案例 >

json文件 数据导入excel----->(jxl)

顾承平
2023-12-01
package com.haobo.upark;

import com.mchange.v1.util.ArrayUtils;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import org.apache.commons.collections.ListUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.io.FileUtils;
import org.codehaus.jackson.map.ObjectMapper;
import org.junit.Test;
import org.junit.experimental.theories.ParametersSuppliedBy;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WritableFont;
import jxl.write.WritableCellFormat;




import java.io.File;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.security.Key;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Created by zs on 2017/6/19.
 */
public class SwaggerConvertExcel {
    private Logger logger = LoggerFactory.getLogger(SwaggerConvertExcel.class);
    private String swaggerFile = "F:\\zs\\swagger.json";
    private ObjectMapper objectMapper = new ObjectMapper();
    private  String excelname="F:\\zs\\后台管理系统接口文档.xlsx";
    private WritableWorkbook wwb = null;
    private String name="";//接口名(路径)
    private String  rc="";//传入的参数
    private String cc="";//出去的参数
    private String https="";//请求方式
    private String return1="";//返回的类型
    private String desc="";//功能描述
    private int coll=0;//行数
    String paras[]=new String[100];
    //private List parameters=new ArrayList();

    @Test
    public void generator() throws Exception{
        //取得xls
        File file=new File(excelname);
        if (!file.exists()) {
            file.createNewFile();
        }
        //创建操作表格的对象
        wwb = Workbook.createWorkbook(file);
        WritableSheet ws = wwb.createSheet("Test Shee 1", 0);
        //设置字体第一列的-->这里就是表格最上面的文字 的字体啊这些的
        WritableFont bold = new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD);
        WritableCellFormat wcfFormat = new WritableCellFormat(bold);
        wcfFormat.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中

        //设置字体2
        WritableFont bold1 = new WritableFont(WritableFont.ARIAL, 15);
        WritableCellFormat wcfFormat1 = new WritableCellFormat(bold1);
        wcfFormat1.setAlignment(jxl.format.Alignment.LEFT);//单元格中的内容水平方向居中
        wcfFormat1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
        //设置列宽
//        for (int j=0;j<6;j++) {
//            ws.setColumnView(j+0, 30);
//        }


        //单元格是字符串格式!第一个是代表列数,第二是代表行数,第三个代表要写入的内容,第四个代表字体格式  (0代表excel的第一行或者第一列)
        //重置表格,要插入到的Excel表格的行号,默认从0开始
        Label labelId= new Label(0, 0, "序号",wcfFormat);
        Label labelName= new Label(1, 0,"接口名",wcfFormat);
        Label labelrucan= new Label(2, 0, "入参",wcfFormat);
        Label labelchucan= new Label(3, 0, "出参",wcfFormat);
        Label labelreturn= new Label(4, 0, "返回类型",wcfFormat);
        Label labeldesc= new Label(5, 0, "功能描述",wcfFormat);
        Label labelhttp= new Label(6, 0, "请求方式",wcfFormat);
        ws.addCell(labelId);
        ws.addCell(labelName);
        ws.addCell(labelrucan);
        ws.addCell(labelchucan);
        ws.addCell(labelreturn);
        ws.addCell(labeldesc);
        ws.addCell(labelhttp);

        Map<String,Object> json=new HashMap<>();//取得swagger
        Map<String,Object> paths=new HashMap<>();    //paths节点的map
        Map<String,Object> jk=new HashMap<>(); //取接口路径名对应的--> map
        Map<String,Object> definitions=new HashMap<>();//返回值--map
        Map<String, Object> summary1=new HashMap<>();
        List<Map<String, Object>> parametersList=new ArrayList<>();//取传入参数list
        Map<String ,Object> response =new HashMap<>();
        Map<String ,Object> schema =new HashMap<>();


        //取得swagger 着就是解析json文件去的里面的数据
        String content = FileUtils.readFileToString(new File(swaggerFile));
        json = objectMapper.readValue(content, Map.class);


        logger.info("{}", MapUtils.getObject(json,"paths"));

        //paths
        paths= MapUtils.getMap(json, "paths");
        //遍历path
        for (Map.Entry<String, Object> path : paths.entrySet()) {

            //获取接口名--,描述
            name= path.getKey();

            //jk名对应的--> map
            jk=MapUtils.getMap(paths,  path.getKey());

            for (Map.Entry<String, Object> summary : jk.entrySet()) {

                //post:get
                https = summary.getKey();

                //post:get名对应的-->map
                summary1= MapUtils.getMap(jk, summary.getKey());

                //接口功能描述    //取接口参数
                desc = (String) summary1.get("summary");

                if (summary1.get("parameters") != null) {
                    rc ="";
                     parametersList = (List) summary1.get("parameters");
                    for (Map<String, Object> parameters : parametersList) {
                        rc = rc+"{"+"                                                      ";
                        int a = 1;
                        for (Map.Entry<String, Object> parameterss : parameters.entrySet()) {
                            rc = rc + "参数" + (a) + ":" + parameterss.getKey() + "=" + parameterss.getValue() + "                                                      ";
                            a++;
                        }
                        rc=rc+"}"+"                                                      ";
                    }
                }else{
                    rc="";
                }

            }
            if(summary1.get("responses")!=null){
                return1="";
                cc="";
                response=(Map)summary1.get("responses");
                schema=(Map) response.get("200");
                return1="description"+"="+schema.get("description")+"                                                      "+"schema"+"="+schema.get("schema");
                definitions= MapUtils.getMap(json, "definitions");
                for (Map.Entry<String ,Object> cs :schema.entrySet()){
                    //for (Map.Entry<String ,Object> cs :definitions.entrySet()){
                    String css[]=cs.getValue().toString().split("/");
                    String keycs=css[css.length-1].replace("}","");
                    cc=cc+definitions.get(keycs);
                   // System.out.println(definitions.get(keycs)+"==================>"+keycs+"+======================================>>"+cs.getValue());
                }
            }else {
                cc="";
            }
            //definitions

            //Map<String,Object> JsonResults= MapUtils.getMap(definitions, "JsonResult");

            //换行
            WritableCellFormat  hx=   new WritableCellFormat(bold1);
            hx.setAlignment(jxl.format.Alignment.LEFT);
            hx.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);
            hx.setWrap(true);
            ws.setColumnView(coll+1, 44);

            Label labelId_i= new Label(0, coll+1, coll+"",wcfFormat1);
            Label labelName_i= new Label(1, coll+1,name+"",wcfFormat1);
            Label labelrucan_i= new Label(2, coll+1, rc+"",hx);
            Label labelchucan_i= new Label(3, coll+1, cc+"",hx);
            Label labelreturn_i= new Label(4, coll+1, return1+"",hx);
            Label labeldesc_i= new Label(5, coll+1, desc+"",wcfFormat1);
            Label labelhttp_i= new Label(6, coll+1, https+"",wcfFormat1);

            ws.addCell(labelId_i);
            ws.addCell(labelName_i);
            ws.addCell(labelrucan_i);
            ws.addCell(labelreturn_i);
            ws.addCell(labeldesc_i);
            ws.addCell(labelhttp_i);
            ws.addCell(labelchucan_i);
            coll++;
             //关闭Excel工作簿对像
        }
        wwb.write();
        wwb.close();
    }



}
 类似资料: