[Unity]关于Unity读取Excel动态生成脚本与二进制文件(附源码)

我一开始使用了OleDb的方式来读取Excel,结果报了NotImplementedException: OleDb is not implemented这个异常,网上关于这方面的消息也很少,后来在stackoverflow上才找到相关信息,Mono并不支持OleDb.

所以后来还是使用Excel.dll来读取,这里我发现如果是.xls文件,只使用Excel.dll就可以了,如果是.xlsx就另需ICSharpCode.SharpZipLib.dll这个库

两个库的网盘链接: https://pan.baidu.com/s/1lsFBAy0PZJKP6eLzGNdpjQ 提取码: 5kwe 

这里需要注意的是.xls使用CreateBinaryReader方法而.xlsx使用CreateOpenXmlReader方法,如图:

剩余的就没有什么特别需要注意的了,具体看源码.

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using UnityEditor;
using UnityEngine;
using Excel;

/// <summary>
/// 将Excel文件转换为脚本
/// </summary>
public class Excel2Script : MonoBehaviour
{
    /// <summary>
    /// 每行类型
    /// </summary>
    private enum RowType : byte
    {
        FIELD_NAME = 4,
        FIELD_TYPE = 5,
        DATA_START_ROW = 6
    }

    /// <summary>
    /// 放置Excel文件的路径
    /// </summary>
    private static readonly string ExcelPath = $"{Application.dataPath}/Excel2Script/Excel";

    /// <summary>
    /// 放置要生成的脚本文件的路径
    /// </summary>
    private static readonly string ScriptPath = $"{Application.dataPath}/Excel2Script/Script";

    /// <summary>
    /// 放置要生成的二进制文件的路径
    /// </summary>
    private static readonly string BytePath = $"{Application.dataPath}/Excel2Script/Byte";

    [MenuItem("Tools/Excel To Script")]
    private static void _Excel2Script()
    {
        foreach (string filePath in Directory.EnumerateFiles(ExcelPath, "*.xls"))
        {
            string[][] data = LoadExcel(filePath);
            CreateScript(filePath, data);
        }

        AssetDatabase.Refresh();
        Debug.Log("Excel转换成CS文件完成");
    }

    [MenuItem("Tools/Excel To Byte")]
    private static void _Excel2Byte()
    {
        foreach (string filePath in Directory.EnumerateFiles(ExcelPath, "*.xls"))
        {
            string[][] data = LoadExcel(filePath);
            CreateByte(filePath, data);
        }

        AssetDatabase.Refresh();
        Debug.Log("Excel转换成二进制文件完成");
    }

    /// <summary>
    /// 读取Excel数据并保存为字符串锯齿数组
    // </summary>
    private static string[][] LoadExcel(string filePath)
    {
        FileInfo fileInfo = new FileInfo(filePath);
        FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);

        DataSet dataSet = fileInfo.Extension == ".xls"
            ? ExcelReaderFactory.CreateBinaryReader(stream).AsDataSet()
            : ExcelReaderFactory.CreateOpenXmlReader(stream).AsDataSet();

        DataRowCollection rows = dataSet.Tables[0].Rows;
        string[][] data = new string[rows.Count][];
        for (int i = 0; i < rows.Count; ++i)
        {
            int columnCount = rows[i].ItemArray.Length;
            string[] columnArray = new string[columnCount];
            for (int j = 0; j < columnArray.Length; ++j)
            {
                columnArray[j] = rows[i].ItemArray[j].ToString();
            }

            data[i] = columnArray;
        }

        return data;
    }

    /// <summary>
    /// 通过Excel数据生成脚本文件
    /// </summary>
    private static void CreateScript(string filePath, string[][] data)
    {
        StringBuilder scriptStr = new StringBuilder();
        string className = new FileInfo(filePath).Name.Split('.')[0];
        scriptStr.AppendLine("using System.Collections.Generic;\n");
        scriptStr.AppendLine($"public class {className}");
        scriptStr.AppendLine("{");
        string[] filedTypeArray = data[(int) RowType.FIELD_TYPE];
        string[] filedNameArray = data[(int) RowType.FIELD_NAME];
        for (int i = 1; i < filedTypeArray.Length; ++i)
        {
            scriptStr.AppendLine($"\tpublic {filedTypeArray[i].PadRight(10, ' ')}\t{filedNameArray[i]};");
        }

        scriptStr.AppendLine("}");
        string path = $"{ScriptPath}/{className}.cs";
        File.Delete(path);
        File.WriteAllText(path, scriptStr.ToString());
    }

    /// <summary>
    /// 创建二进制文件
    /// </summary>
    private static void CreateByte(string filePath, string[][] data)
    {
        string className = new FileInfo(filePath).Name.Split('.')[0];
        string path = $"{BytePath}/{className}";
        File.Delete(path);
        using (FileStream fileStream = new FileStream(path, FileMode.Create))
        {
            List<Type> types = GetTypeByFieldType(data);

            using (BinaryWriter binaryWriter = new BinaryWriter(fileStream))
            {
                for (int i = (int) RowType.DATA_START_ROW; i < data.Length; ++i)
                {
                    for (int j = 0; j < types.Count; ++j)
                    {
                        byte[] bytes = GetField(types[j], data[i][j + 1]);

                        binaryWriter.Write(bytes);
                    }
                }
            }
        }
    }

    private static byte[] GetField(Type type, string data)
    {
        if (IsListType(type))
        {
            string[] dataArray = data.Split('|');
            List<byte> byteList = BitConverter.GetBytes(dataArray.Length).ToList();
            for (int i = 0; i < dataArray.Length; ++i)
            {
                byteList.AddRange(GetBasicField(type.GenericTypeArguments[0], dataArray[i]).ToList());
            }
            
            return byteList.ToArray();
        }

        return GetBasicField(type, data);
    }

    private static byte[] GetBasicField(Type type, string data)
    {
        byte[] bytes = null;
        if (type == typeof(int))
        {
            bytes = BitConverter.GetBytes(int.Parse(data));
        }
        else if (type == typeof(float))
        {
            bytes = BitConverter.GetBytes(float.Parse(data));
        }
        else if (type == typeof(string))
        {
            List<byte> dataBytes = Encoding.Default.GetBytes(data).ToList();
            List<byte> lengthBytes = BitConverter.GetBytes(dataBytes.Count).ToList();
            lengthBytes.AddRange(dataBytes);
            bytes = lengthBytes.ToArray();
        }

        if (bytes == null) throw new Exception($"{nameof(name)}.GetBasicField: 其类型未配置或不是基础类型 Type:{type} Data:{data}");
        return bytes;
    }

    private static List<Type> GetTypeByFieldType(string[][] data)
    {
        List<Type> types = new List<Type>();
        string[] temp = data[(int) RowType.FIELD_TYPE];
        for (int i = 1; i < temp.Length; ++i)
        {
            if (temp[i] == "int") types.Add(typeof(int));
            else if (temp[i] == "float") types.Add(typeof(float));
            else if (temp[i] == "string") types.Add(typeof(string));
            else if (temp[i] == "List<int>") types.Add(typeof(List<int>));
            else if (temp[i] == "List<float>") types.Add(typeof(List<float>));
        }

        return types;
    }

    private static bool IsListType(Type type)
    {
        if (type == typeof(List<int>)) return true;
        if (type == typeof(List<float>)) return true;
        if (type == typeof(List<string>)) return true;
        return false;
    }
}