Excel 读写、公式、生成cs

cwl72

已于 2024-09-19 10:50:50 修改

阅读量414

点赞数 10

文章标签： excel unity

于 2024-01-04 17:13:56 首次发布

本文链接：https://blog.csdn.net/chenwenlong72/article/details/135391695

版权

文章介绍了如何在Unity编辑器中使用C#编写脚本，处理Excel和CSV文件，包括读取、写入数据以及自动生成配置类。脚本涉及了OfficeOpenXml库的操作，如创建Excel工作表，读取单元格值，以及根据Excel数据动态生成C#类。

摘要由CSDN通过智能技术生成

补充、表格公式、计算

        ExcelRange cellData = null;

        //赋值时  表格公式 跟  公式值 只能存在一个
        //公式值  最后  统一计算 自动赋值
        cellData = cell[row, cloumn + 0];
        cellData.Formula = idfx;     //表格公式 （E56+(A4*3-49)/2）
        if (string.IsNullOrEmpty(idfx))
            cellData.Value = id;     //表格公式值 （152.789）

        // 计算公式的结果
        package.Workbook.Calculate();

        //保存excel
        package.Save();

表格编辑

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/0a5b4228fa22468da0c706727a4fba96.png

using System.Collections;
using System.Collections.Generic;
using OfficeOpenXml;
using System.IO;
using UnityEngine;
using System.Text;
using System;

public class ExcelMgrHelp : Singleton<ExcelMgrHelp>
{

    public void Start()
    {
        string _filePath = Application.streamingAssetsPath + "/学生信息.xlsx";
        WriteExcel(_filePath);
        ReadExcel(_filePath);


        _filePath = Application.streamingAssetsPath + "/DataTest.xlsx";
        List<DataTest> datas = new List<DataTest> {
           new DataTest(0,"先民",100,1.85f) ,
           new DataTest(1,"星宇",110,1.5f) ,
           new DataTest(2,"刘敏",10,1.8f)
        };

        WriteExcel(_filePath, datas);
        List<DataTest> datas2 = ReadExcel<DataTest>(_filePath);

    }

    void WriteExcel(string _filePath)
    {
        string _sheetName = "详情";
        FileInfo fileInfo = new FileInfo(_filePath);
        if (fileInfo.Exists)
        {
            fileInfo.Delete();   //删除旧文件，并创建一个新的 excel 文件。
            fileInfo = new FileInfo(_filePath);
        }

        //通过ExcelPackage打开文件
        using (ExcelPackage package = new ExcelPackage(fileInfo))
        {
            //在 excel 空文件添加新 sheet，并设置名称。
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(_sheetName);

            //添加列名
            worksheet.Cells[1, 1].Value = "学号";
            worksheet.Cells[1, 2].Value = "姓名";
            worksheet.Cells[1, 3].Value = "性别";

            //添加一行数据
            worksheet.Cells[2, 1].Value = 100001;
            worksheet.Cells[2, 2].Value = "张三";
            worksheet.Cells[2, 3].Value = "男";

            //添加一行数据
            worksheet.Cells[3, 1].Value = 100002;
            worksheet.Cells[3, 2].Value = "李四";
            worksheet.Cells[3, 3].Value = "女";

            //添加一行数据
            worksheet.Cells[4, 1].Value = 120033;
            worksheet.Cells[4, 2].Value = "Saw";
            worksheet.Cells[4, 3].Value = "男";

            //保存excel
            package.Save();
        }
    }

    void ReadExcel(string _filePath)
    {
        //获取Excel文件的信息
        FileInfo fileInfo = new FileInfo(_filePath);
        if (!fileInfo.Exists)
            return;

        using (ExcelPackage excelPackage = new ExcelPackage(fileInfo))
        {
            ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[1];
            int rowCount = worksheet.Dimension.End.Row;
            int columnCount = worksheet.Dimension.End.Column;
            for (int i = 0; i < rowCount; i++)//End.Row获得当前表格的最大行数
            {
                string str = null;
                for (int j = 0; j < columnCount; j++)
                {
                    str += worksheet.Cells[i + 1, j + 1].Value.ToString();
                    if (j < columnCount - 1)
                    {
                        str += ",";
                    }
                }
                Debug.Log($"ReadExcel: line {i + 1} " + str);
            }
        }
    }


   

    public void WriteExcel<T>(string _filePath, List<T> datas) where T : BaseData
    {
        if (datas == null || datas.Count <= 0 || string.IsNullOrEmpty(_filePath))
            return;

        FileInfo fileInfo = new FileInfo(_filePath);
        if (fileInfo.Exists)
        {
            fileInfo.Delete();   //删除旧文件，并创建一个新的 excel 文件。
            fileInfo = new FileInfo(_filePath);
        }

        using (ExcelPackage package = new ExcelPackage(fileInfo))
        {
            WriteExcel(package, datas);
        }
    }

    public void WriteExcel<T>(ExcelPackage package, List<T> datas) where T : BaseData
    {
        BaseData data = datas[0];
        string _sheetName = data.GetType().Name;

        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(_sheetName); //在 excel 空文件添加新 sheet，并设置名称。


        //变量名、变量类型、 注释
        string[,] keys = data.GetDataKey();
        int rowKey = 3;
        int columnKey = keys.Length / rowKey;

        //rowKey = 0;

        for (int i = 0; i < rowKey; i++)
        {
            for (int j = 0; j < columnKey; j++)
            {
                worksheet.Cells[i + 1, j + 1].Value = keys[i, j];
            }
        }

        //数据
        int row = datas.Count;
        for (int i = 0; i < row; i++)
        {
            data = datas[i];
            string[] valueArr = data.GetDataValue();
            int column = valueArr.Length;
            for (int j = 0; j < column; j++)
            {
                worksheet.Cells[i + 1 + rowKey, j + 1].Value = valueArr[j];
            }
        }

        //保存excel
        package.Save();
    }



    public List<T> ReadExcel<T>(string _filePath) where T : BaseData
    {
        if (string.IsNullOrEmpty(_filePath))
            return null;


        FileInfo fileInfo = new FileInfo(_filePath);
        if (!fileInfo.Exists)
            return null;

        using (ExcelPackage package = new ExcelPackage(fileInfo))
        {
            List<T> datas = ReadExcel<T>(package); 

            return datas;
        }  
    }

    public List<T> ReadExcel<T>(byte[] bytes) where T : BaseData
    {
        if (bytes == null || bytes.Length <= 0)
            return null;

        Stream fileInfo = new MemoryStream(bytes);

        using (ExcelPackage package = new ExcelPackage(fileInfo))
        {
            List<T> datas = ReadExcel<T>(package);

            return datas;
        } 
    }

    public List<T> ReadExcel<T>(ExcelPackage package) where T : BaseData
    {

        List<T> datas = new List<T>();

        //通过ExcelPackage打开文件

        ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
        int rowCount = worksheet.Dimension.End.Row;
        int columnCount = worksheet.Dimension.End.Column;

        int rowKey = 3;
        //变量名、变量类型、 注释
        string[,] keys = new string[rowKey, columnCount];
        for (int i = 0; i < rowKey; i++)
        {
            for (int j = 0; j < columnCount; j++)
            {
                keys[i, j] = worksheet.Cells[i + 1, j + 1].Value?.ToString();
            }
        }

        //数据
        for (int i = rowKey; i < rowCount; i++)
        {
            object obj = Activator.CreateInstance(typeof(T));
            T t = (T)obj;
            //方式1
            for (int j = 0; j < columnCount; j++)
            {
                string FieldName = keys[0, j];
                string FieldType = keys[1, j];
                string Value = worksheet.Cells[i + 1, j + 1].Value?.ToString();

                Type Ts = obj.GetType();
                System.Reflection.FieldInfo info = Ts.GetField(FieldName);
                if (!string.IsNullOrEmpty(Value))
                    info.SetValue(obj, Value.GetTypeStr(FieldType));
            }

            //方式2
            //t.SetDataValue(worksheet.Cells, i + 1, 1);

            Debug.Log(t.ToString());
            datas.Add(t);
        }

        return datas;
    }


}


public static class Utils
{
    public static string GetTypeStr(this System.Type type)
    {

        if (type == typeof(string))
        {
            return "string";
        }
        else if (type == typeof(float))
        {
            return "float";
        }
        else if (type == typeof(int))
        {
            return "int";
        }
        else if (type == typeof(long))
        {
            return "long";
        }
        else if (type == typeof(double))
        {
            return "double";
        }
        else if (type == typeof(byte))
        {
            return "byte";
        }
        else if (type == typeof(DateTime))
        {
            return "DateTime";
        }

        return type.Name;
    }

    public static object GetTypeStr(this string Value, string FieldType)
    {
        switch (FieldType)
        {
            case "string": return Value;
            case "float": return float.Parse(Value);
            case "int": return int.Parse(Value);
            case "long": return long.Parse(Value);
            case "double": return double.Parse(Value);
            case "byte": return byte.Parse(Value);
            case "DateTime": return System.DateTime.Parse(Value);

            default: break;
        }

        return Value;
    }
}

public interface BaseData
{
    public abstract string[] GetDataValue();

    public abstract string[,] GetDataKey();

    public abstract void SetDataValue(ExcelRange cell, int row, int cloumn);

    public abstract string ToString();
}

class DataTest : BaseData
{
    public int id;
    public string name;
    public int leve;
    public float height;

    public DataTest()
    {

    }

    public DataTest(int _id, string _name, int _leve, float _height)
    {
        id = _id;
        name = _name;
        leve = _leve;
        height = _height;
    }


    public virtual string[,] GetDataKey()
    {
        return new string[,] {
        { nameof(id)  ,  nameof(name) , nameof(leve),  nameof(height) },
        { id.GetType().GetTypeStr(), name.GetType().GetTypeStr(), leve.GetType().GetTypeStr(), height.GetType().GetTypeStr()},
        { "唯一ID", "姓名", "等级", "身高" }
        };
    }

    public virtual string[] GetDataValue()
    {
        return new string[] { id.ToString(), name.ToString(), leve.ToString(), height.ToString() };
    }

    public virtual void SetDataValue(ExcelRange cell, int row, int cloumn)
    {
        id = int.Parse(cell[row, cloumn].Value.ToString());
        name = cell[row, cloumn + 1].Value.ToString();
        leve = int.Parse(cell[row, cloumn + 2].Value.ToString());
        height = float.Parse(cell[row, cloumn + 3].Value.ToString());
    }

    public override string ToString()
    {
        return string.Format("{0}: {1} {2} {3} {4}", typeof(DataTest), id, name, leve, height);
    }

}

编辑器 csv 、Excel 生成脚本

using System.Collections;
using System.Collections.Generic;
using UnityEngine;

using UnityEditor;
using System.Text;
using System.IO;
using OfficeOpenXml;
using System;

public class ExcelcsvEditor
{
    [MenuItem("Assets/cwl/csv to cs")]
    static void ReadData_csv()
    {
        string userFile_Path = AssetDatabase.GetAssetPath(Selection.activeObject);
        //string userFile_Path = Application.streamingAssetsPath + "/csv/";

        int endindex = userFile_Path.LastIndexOf('.');
        if (endindex == -1)
        {
            string[] files = Directory.GetFiles(userFile_Path, "*.csv", SearchOption.AllDirectories);
            for (int i = 0; i < files.Length; i++)
            {
                ReadData_csv(files[i].Replace("\\", "/"));
            }
        }
        else
        {
            ReadData_csv(userFile_Path);
        }
    }

    static void ReadData_csv(string userFile_Path)
    {
        //string userFile_Path = Application.streamingAssetsPath + string.Format("/Configs/{0}.csv", fileName);

        string getString = File.ReadAllText(userFile_Path);
        CsvReaderByString csr = new CsvReaderByString(getString);

        // 读取数据
        int row = csr.RowCount;
        int cloumn = csr.ColCount;
        Debug.Log(row + " , " + cloumn);

        int endindex = userFile_Path.LastIndexOf('.');
        int stindex = userFile_Path.LastIndexOf('/');
        stindex++;
        string fileName = userFile_Path.Substring(stindex, endindex - stindex);

        string CreateClassAction(int row, int cloum)
        {
            return csr[row, cloum];
        }

        string data = CreateClass(CreateClassAction, row, cloumn, fileName);
        string fname = Path.GetDirectoryName(userFile_Path);
        string className = fileName + ".cs";

        IOUtil.CreateFile(fname + $"/{className}", data);
        //IOUtil.CreateFile(Application.dataPath+ @"\Scripts\Common\Game\Define\GameConfigs\GameConfigs_SQL" + $"/{className}", data); 
        AssetDatabase.Refresh();


    }


    [MenuItem("Assets/cwl/excel to cs")]
    static void ReadData_Excel()
    {
        //string userFile_Path = Environment.CurrentDirectory + "/AppRes/Excels/Register/新用户信息 - 副本.xlsx";
        //string userFile_Path = Application.streamingAssetsPath + "/DataTest.xlsx";
        string userFile_Path = AssetDatabase.GetAssetPath(Selection.activeObject);

        int endindex = userFile_Path.LastIndexOf('.');
        if (endindex == -1)
        {
            string[] files = Directory.GetFiles(userFile_Path, "*.xlsx", SearchOption.AllDirectories);
            for (int i = 0; i < files.Length; i++)
            {
                ReadData_Excel(files[i].Replace("\\", "/"));
            }
        }
        else
        {
            ReadData_Excel(userFile_Path);
        }
    }

    static void ReadData_Excel(string userFile_Path)
    {
        FileInfo fileInfo = new FileInfo(userFile_Path);
        using (ExcelPackage excelPackage = new ExcelPackage(fileInfo))
        {
            ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[1];

            // 读取数据
            int row = worksheet.Dimension.End.Row;
            int cloumn = worksheet.Dimension.End.Column;
            string fileName = worksheet.Name;
            Debug.Log(row + " , " + cloumn);

            string CreateClassAction(int row, int cloum)
            {
                return worksheet.Cells[row, cloum].Value.ToString();
            }

            string data = CreateClass(CreateClassAction, row, cloumn, fileName);
            string fname = Path.GetDirectoryName(fileInfo.FullName);
            string className = fileInfo.Name.Replace(fileInfo.Extension, ".cs");

            IOUtil.CreateFile(fname + $"/{className}", data);
            //IOUtil.CreateFile(Application.dataPath + @"\Scripts\Common\Game\Define\GameConfigs\GameConfigs_SQL" + $"/{className}", data);
        }

        AssetDatabase.Refresh();
    }

    static string CreateClass(Func<int, int, string> GetCell, int row, int cloumn, string className)
    {
        //1 id
        //2 int
        //3 注释
        //4-》 数据
        //string className = null;
        string dataKey1 = null, dataKey2 = null, dataKey3 = null;
        string getdataValue = null, setdataValue = null, tostring = null;
        string ctorParameter = null, ctorValue = null;

        //className = worksheet.Name;
        StringBuilder bulider = new StringBuilder();

        bulider.AppendLine("using System.Collections.Generic;");
        bulider.AppendLine("using System;");
        bulider.AppendLine("using OfficeOpenXml;");
        bulider.AppendLine();
        bulider.AppendLine("//代码生成，勿动");
        bulider.AppendLine();
        bulider.AppendLine(string.Format("public partial class {0}: GameConfigDataBase,BaseData", className));
        bulider.AppendLine("{");
        for (int j = 0; j < cloumn; j++)
        {
            int curCloumn = j + 1;

            string typeName = GetCell(1, curCloumn);  //1 id
            string mtype = GetCell(2, curCloumn); //2 int
            string zushi = GetCell(3, curCloumn); //3 注释

            //属性  
            bulider.AppendLine("   /// <summary>");
            bulider.AppendLine(string.Format("   /// {0}", zushi));
            bulider.AppendLine("   /// <summary>");
            bulider.AppendLine(string.Format("   public {0} {1};", mtype, typeName));
            bulider.AppendLine("");

            //构造函数 无参

            //构造函数 有参
            ctorParameter += ($"{mtype} _{typeName}");
            ctorValue += ($"        {typeName} = _{typeName};\r\n");

            //GetDataKey
            dataKey1 += $"nameof({typeName})";
            dataKey2 += $"\"{mtype}\"";//$"{typeName}.GetType().GetTypeStr()";
            dataKey3 += $"\"{zushi}\"";

            //GetDataValue
            getdataValue += $"{typeName } == null ? null : {typeName }.ToString()";

            //GetDataValue  
            if (setdataValue == null)
                setdataValue += $"        object obj = null;\r\n";

            setdataValue += $"        obj = cell[row, cloumn + {j}].Value;\r\n";
            setdataValue += $"        {typeName} =";
            if (mtype == "string")
            {
                setdataValue += $"obj == null ?default: obj.ToString();";
            }
            else
            {
                setdataValue += $"obj == null ? default : {mtype}.Parse(obj.ToString());";
            }
            setdataValue += "\r\n";

            //ToString()
            tostring += $"{{{typeName}}}";

            if (j < cloumn - 1)
            {
                dataKey1 += ",";
                dataKey2 += ",";
                dataKey3 += ",";
                getdataValue += ",";
                tostring += " ";

                ctorParameter += ", ";
            }
        }

        bulider.AppendLine();
        bulider.AppendLine("    public override string getFilePath()");
        bulider.AppendLine("	{");
        bulider.AppendLine($"		return \"{className}\";");
        bulider.AppendLine("	}");
        bulider.AppendLine();

        bulider.AppendLine();
        bulider.AppendLine($"    public {className}()");
        bulider.AppendLine("	{");
        bulider.AppendLine();
        bulider.AppendLine("	}");
        bulider.AppendLine();

        bulider.AppendLine();
        bulider.AppendLine($"    public {className}({ctorParameter})");
        bulider.AppendLine("	{");
        bulider.AppendLine($"{ctorValue}");
        bulider.AppendLine("	}");
        bulider.AppendLine();

        bulider.AppendLine();
        bulider.AppendLine("    public virtual string[,] GetDataKey()");
        bulider.AppendLine("	{");
        bulider.AppendLine("        return new string[,] {");
        bulider.AppendLine($"        {{{dataKey1}}},");
        bulider.AppendLine($"        {{{dataKey2}}},");
        bulider.AppendLine($"        {{{dataKey3}}}");
        bulider.AppendLine("        };");
        bulider.AppendLine("	}");
        bulider.AppendLine();

        bulider.AppendLine();
        bulider.AppendLine("    public virtual string[] GetDataValue()");
        bulider.AppendLine("	{");
        bulider.AppendLine($"        return new string[] {{{getdataValue}}};");
        bulider.AppendLine("	}");
        bulider.AppendLine();

        bulider.AppendLine();
        bulider.AppendLine("    public virtual void SetDataValue(ExcelRange cell, int row, int cloumn)");
        bulider.AppendLine("	{");
        bulider.AppendLine($"{setdataValue}");
        bulider.AppendLine("	}");
        bulider.AppendLine();

        bulider.AppendLine();
        bulider.AppendLine("    public override string ToString()");
        bulider.AppendLine("	{");
        bulider.AppendLine($"        return  $\"{{typeof({className})}}: {tostring}\";");
        bulider.AppendLine("	}");
        bulider.AppendLine();


        bulider.AppendLine("}");



        Debug.Log(bulider.ToString());

        return bulider.ToString();
    }
}