Unity（四十二）：EPPlus读写Excel数据、JSON解析

// Assets/Example_01/Editor/HandleExcel.cs

using System;
using System.Collections.Generic;
using System.IO;
using Example_01.Scripts;
using OfficeOpenXml;
using UnityEditor;
using UnityEngine;

public class HandleExcel
{
	/// 

	/// 读取Excel
	/// 
	[MenuItem("Assets/Excel/Read File", false, 0)]
	public static void ReadExcel()
	{
		string[] ids = Selection.assetGUIDs;
		foreach (var id in ids)
		{
			string path = $"{Environment.CurrentDirectory}/{AssetDatabase.GUIDToAssetPath(id)}";

			Debug.Log(path);

			using FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);

			ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
			using ExcelPackage excel = new ExcelPackage(fs);

			// 获取Sheet集合
			ExcelWorksheets worksheets = excel.Workbook.Worksheets;
			foreach (var worksheet in worksheets)
			{
				// 获取当前工作表名称
				Debug.Log(worksheet.Name);

				int colCount = worksheet.Dimension.End.Column;
				int rowCount = worksheet.Dimension.End.Row;
				for (int row = 1; row <= rowCount; row++)
				{
					for (int col = 1; col <= colCount; col++)
					{
						string text = worksheet.Cells[row, col].Text;
						Debug.Log($"({row}, {col}) ---> {text}");
					}
				}
			}
		}
	}
}

// Assets/Example_01/Editor/HandleExcel.cs

using System;
using System.Collections.Generic;
using System.IO;
using Example_01.Scripts;
using OfficeOpenXml;
using UnityEditor;
using UnityEngine;

public class HandleExcel
{
	/// 

	/// 创建与写入Excel
	/// 
	[MenuItem("Assets/Excel/Create & Write File", false, 1)]
	public static void CreateWriteExcel()
	{
		string path = $"{Application.dataPath}/Example_01/Excels/{DateTime.Now:yyyy-MM-dd hhmmss}.xlsx";
		Debug.Log(path);

		FileInfo file = new FileInfo(path);

		ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
		using ExcelPackage excel = new ExcelPackage(file);

		// 向excel中写入数据
		ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add("Sheet 1");
		worksheet.Cells[1, 1].Value = "Lee";
		worksheet = excel.Workbook.Worksheets.Add("Sheet 2");
		worksheet.Cells[1, 1].Value = "Prosper";
		// 保存
		excel.Save();

		AssetDatabase.Refresh();
	}
}

// Assets/Example_01/Editor/HandleExcel.cs

using System;
using System.Collections.Generic;
using System.IO;
using Example_01.Scripts;
using OfficeOpenXml;
using UnityEditor;
using UnityEngine;

public class HandleExcel
{
	/// 

	/// 读取Excel数据并生成Asset静态资源文件
	/// 
	[MenuItem("Assets/Excel/Create Asset File", false, 2)]
	public static void CreateExcelData()
	{
		ExcelData script = ScriptableObject.CreateInstance();
		string[] ids = Selection.assetGUIDs;
		foreach (var id in ids)
		{
			string path = $"{Environment.CurrentDirectory}/{AssetDatabase.GUIDToAssetPath(id)}";

			using FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);

			ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
			using ExcelPackage excel = new ExcelPackage(fs);

			// 获取Sheet集合
			ExcelWorksheets worksheets = excel.Workbook.Worksheets;
			foreach (var worksheet in worksheets)
			{
				List list = new List();

				int colCount = worksheet.Dimension.End.Column;
				int rowCount = worksheet.Dimension.End.Row;
				for (int row = 1; row <= rowCount; row++)
				{
					for (int col = 1; col <= colCount; col++)
					{
						string text = worksheet.Cells[row, col].Text;
						list.Add(new ExcelDataInfo
						{
							row = row,
							col = col,
							text = text
						});
					}
				}

				script.sheets.Add(new ExcelDataSheet
				{
					name = worksheet.Name,
					list = list
				});
			}
		}

		// 对象转换成json
		string json = JsonUtility.ToJson(script.sheets);
		Debug.Log(json);
		// json转换成对象
		List data = JsonUtility.FromJson>(json);
		Debug.Log(data);

		// 将资源保存到本地
		string savePath =
			$"Assets/Example_01/Resources/ExcelAssetData {DateTime.Now:yyyy-MM-dd hhmmss}.asset";
		AssetDatabase.CreateAsset(script, savePath);
		AssetDatabase.SaveAssets();
		AssetDatabase.Refresh();
	}
}

// Assets/Example_01/Scripts/ExcelData.cs

using System.Collections.Generic;
using UnityEngine;

namespace Example_01.Scripts
{
	public class ExcelData : ScriptableObject
	{
		public List sheets = new List();
	}

	[System.Serializable]
	public class ExcelDataSheet
	{
		[Tooltip("Sheet页名称")] public string name;
		[Tooltip("Sheet页具体内容")] public List list;
	}

	[System.Serializable]
	public class ExcelDataInfo
	{
		[Tooltip("行")] public int row;
		[Tooltip("列")] public int col;
		[Tooltip("内容")] public string text;
	}
}

Unity中JSON不支持字典类型，通过继承ISerializationCallbackReceiver接口实现字典序列化

// Assets/Example_01/Editor/HandleExcel.cs

using System;
using System.Collections.Generic;
using System.IO;
using Example_01.Scripts;
using OfficeOpenXml;
using UnityEditor;
using UnityEngine;

public class HandleExcel
{
	/// 

	/// Unity中JSON不支持字典类型，通过继承ISerializationCallbackReceiver接口实现字典序列化
	/// 
	[MenuItem("Assets/Excel/Load Dictionary Data", false, 3)]
	public static void LoadDictionaryData()
	{
		Data data = new Data
		{
			["firstName"] = "Prosper",
			["lastName"] = "Lee"
		};

		string json = JsonUtility.ToJson(data);
		Debug.Log(json); // {"keys":["firstName","lastName"],"values":["Prosper","Lee"]}

		data = JsonUtility.FromJson>(json);
		Debug.Log($"{data["firstName"]}{data["lastName"]}"); // ProsperLee
	}

	private class Data : ISerializationCallbackReceiver
	{
		public List keys;
		public List values;

		private Dictionary _data = new Dictionary();

		public TV this[TK key]
		{
			get => !_data.ContainsKey(key) ? default : _data[key];
			set => _data[key] = value;
		}

		public void OnBeforeSerialize()
		{
			keys = new List();
			values = new List();

			foreach (KeyValuePair item in _data)
			{
				keys.Add(item.Key);
				values.Add(item.Value);
			}
		}

		public void OnAfterDeserialize()
		{
			_data = new Dictionary();
			for (int i = 0; i < keys.Count; i++)
			{
				_data[keys[i]] = values[i];
			}

			keys = null;
			values = null;
		}
	}
}