【Excel数据转换成知识图谱Json】

using Excel;
using ForceDirectedDiagram.Scripts.ForceDirectedDiagram;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using UnityEngine;

public class MyExcel : MonoBehaviour
{
    private ForceDiagramSourceDto dto;

    private Dictionary<string, int> groupDict;

    private Dictionary<string, string> idDict;
    void Start()
    {
        groupDict = new Dictionary<string, int>();
        idDict = new Dictionary<string, string>();
        dto = new ForceDiagramSourceDto();
        ReadExcel("/Test.xlsx");
    }

    public void ReadExcel(string xmlName)
    {
        FileStream stream = File.Open(Application.dataPath + xmlName, FileMode.Open, FileAccess.Read, FileShare.Read);
        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);//读取 2007及以后的版本

        DataSet result = excelReader.AsDataSet();

        if (stream != null)
        {
            stream.Close();
        }

        int[] counts = GetCount(result.Tables[0]);
        int rows = counts[0];
        int columns = counts[1];
        int groupId = 0;
        for (int i = 2; i < rows; i++)
        {
            NodeDto node = new NodeDto();
            node.id = result.Tables[0].Rows[i][0].ToString();
            node.label = result.Tables[0].Rows[i][1].ToString();
            node.description = result.Tables[0].Rows[i][2].ToString();
            node.groupname = result.Tables[0].Rows[i][3].ToString();
            idDict[node.label] = node.id;
            if (groupDict.ContainsKey(node.groupname))
            {
                int id = 0;
                groupDict.TryGetValue(node.groupname, out id);
                node.group = id;
                node.subgroup = id;
            }
            else
            {
                groupDict[node.groupname] = groupId;
                node.group = groupId;
                node.subgroup = groupId;
                groupId++;
            }
            node.label = result.Tables[0].Rows[i][1].ToString();
            dto.nodes.Add(node);
        }
        for (int i = 2; i < rows; i++)
        {
            if (result.Tables[0].Rows[i][4]!=null)
            {
                LinkDto link = new LinkDto();
                link.source = result.Tables[0].Rows[i][0].ToString();
                string target = result.Tables[0].Rows[i][4].ToString();
                if (idDict.ContainsKey(target))
                {
                    link.target = idDict[target];
                    link.length = 1;
                    dto.links.Add(link);
                }
            }
        }
        string json = JsonUtility.ToJson(dto);
       // Debug.Log(json);
        var path = Application.streamingAssetsPath + "/A.json";
        File.WriteAllText(path, json);
    }

    private int[] GetCount(DataTable dt)
    {
        int i = dt.Rows.Count;
        for (int m = 0; m < dt.Rows.Count; m++)
        {
            if (string.IsNullOrEmpty(dt.Rows[m][0].ToString()))
            {
                i = m;
                break;
            }
        }

        int j = dt.Columns.Count;
        for (int n = 0; n < dt.Columns.Count; n++)
        {
            if (string.IsNullOrEmpty(dt.Rows[0][n].ToString()))
            {
                j = n;
                break;
            }
        }
        return new int[] { i, j };
    }
}