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

获取EPPlus

下载EPPlus

将得到的EPPlus.dll移动到Plugins文件夹下

读取Excel数据

// 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
{
	/// <summary>
	/// 读取Excel
	/// </summary>
	[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}");
					}
				}
			}
		}
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

创建并向Excel中写入数据

// 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
{
	/// <summary>
	/// 创建与写入Excel
	/// </summary>
	[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();
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

读取Excel数据并生成Asset静态资源文件

// 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
{
	/// <summary>
	/// 读取Excel数据并生成Asset静态资源文件
	/// </summary>
	[MenuItem("Assets/Excel/Create Asset File", false, 2)]
	public static void CreateExcelData()
	{
		ExcelData script = ScriptableObject.CreateInstance<ExcelData>();
		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<ExcelDataInfo> list = new List<ExcelDataInfo>();

				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<ExcelDataInfo> data = JsonUtility.FromJson<List<ExcelDataInfo>>(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();
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

// Assets/Example_01/Scripts/ExcelData.cs

using System.Collections.Generic;
using UnityEngine;

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

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

	[System.Serializable]
	public class ExcelDataInfo
	{
		[Tooltip("行")] public int row;
		[Tooltip("列")] public int col;
		[Tooltip("内容")] public string text;
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

JSON解析

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

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

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

	private class Data<TK, TV> : ISerializationCallbackReceiver
	{
		public List<TK> keys;
		public List<TV> values;

		private Dictionary<TK, TV> _data = new Dictionary<TK, TV>();

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

		public void OnBeforeSerialize()
		{
			keys = new List<TK>();
			values = new List<TV>();

			foreach (KeyValuePair<TK, TV> item in _data)
			{
				keys.Add(item.Key);
				values.Add(item.Value);
			}
		}

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

			keys = null;
			values = null;
		}
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69