Excel导出XML和CS脚本

第一次写博客，好紧张啊，有没有潜规则，用不用脱啊，该怎么说啊，打多少字才显的有文采啊，我写的这么好会不会太招遥，写的这么深奥别人会不会看不懂啊，好激动啊，怎么才能装成是经常写博客的样子，好紧张啊 ╯△╰

---

第一篇博客就跟各位同学们分享下如何在unity中制作一个把Excel表格导出成XML和CS脚本的工具吧！
首先说下用到的第三方库：NPOI 具体我就不介绍了，大家自行百度吧！下面附上下载地址：

点击下载NPOI

---

好了，下面正式开始！
首先打开unity，新建一个工程，在Project窗口右键->Create->C# Script（貌似有点啰嗦了≥﹏≤） ,创建一个cs脚本，命名为ConvertExcelWindow继承EditorWindow放到Assets/Editor文件夹下。下面附上完整代码及注释：

using System;
using UnityEngine;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using UnityEditor;
using Debug = UnityEngine.Debug;

public class ConvertExcelWindow : EditorWindow
{
    //导出脚本路径
    public static string scriptPath = "Assets/Scripts/ExcelData";

    //导出XML路径
    public static string xmlPath = "Assets/Res/Config";

    //Excel表格路径（会递归查找所有子目录并导出）
    public static string excelRootPath;

    //是否导出脚本
    private static bool exportScript = true;

    //是否导出XML
    private static bool exportXml = true;

    //创建一个按钮用来打开我们自定义的窗口
    [MenuItem("Tools/Convert Excel", false, 1)]
    static void ConvertExcel()
    {
        Rect rect = new Rect(0f, 0f, 500f, 150f);
        EditorWindow win = EditorWindow.GetWindowWithRect<ConvertExcelWindow>(rect, true, "Convert Excel", true);
        win.Show();
    }

    //窗口布局写在这个方法里
    private void OnGUI()
    {
        //都是一些简单的控件，就不一行一行写注释了
        EditorGUIUtility.labelWidth = 80;

        EditorGUILayout.BeginVertical();

        GUILayout.Space(20);

        GUILayout.BeginHorizontal();
        exportScript = EditorGUILayout.ToggleLeft("导出脚本", exportScript);
        GUILayout.Space(-180);
        EditorGUILayout.SelectableLabel("脚本生成路径: " + scriptPath);
        if (GUILayout.Button("打开目录"))
        {
            //打开脚本文件夹
            System.Diagnostics.Process.Start(scriptPath.Replace('/', '\\'));
        }
        GUILayout.EndHorizontal();

        GUILayout.Space(5);

        GUILayout.BeginHorizontal();
        exportXml = EditorGUILayout.ToggleLeft("导出Xml", exportXml);
        GUILayout.Space(-180);
        EditorGUILayout.SelectableLabel("Xml生成路径: " + xmlPath);
        if (GUILayout.Button("打开目录"))
        {
            //打开XML文件夹
            System.Diagnostics.Process.Start(xmlPath.Replace('/', '\\'));
        }
        GUILayout.EndHorizontal();

        GUILayout.Space(5);

        GUILayout.BeginHorizontal();
        EditorGUILayout.PrefixLabel("Excel根目录");
        excelRootPath = EditorGUILayout.TextField(excelRootPath);
        GUILayout.EndHorizontal();

        GUILayout.Space(5);

        if (GUILayout.Button("导出"))
        {
            if (string.IsNullOrEmpty(excelRootPath))
            {
                EditorUtility.DisplayDialog("提示", "Excel路径不能为空", "确定");
                return;
            }
            //获取路径下所有以xls，xlsx结尾的表格绝对路径
            List<string> files = Directory.GetFiles(excelRootPath, "*.xls", SearchOption.AllDirectories).ToList();
            //遍历Excel并执行导出方法
            for (int i = 0; i < files.Count; i++)
            {
                //这里为啥要用Try呢？防止导出报错导致进度条清除不了
                try
                {
                    //这里显示导出进度条
                    EditorUtility.DisplayProgressBar("converting...", files[i], (float)i / files.Count);
                    //调用导出方法传入表格路径
                    ExportCSharpClass(files[i]);
                }
                catch (Exception e)
                {
                    EditorUtility.ClearProgressBar();
                    throw new Exception(e.Source + "\n" + e.Message + "\n" + e.StackTrace);
                }
            }
            EditorUtility.ClearProgressBar();
        }
        EditorGUILayout.EndVertical();
    }

    /// <summary>
    /// 导出Excel
    /// </summary>
    /// <param name="filePath">文件路径</param>
    public static void ExportCSharpClass(string filePath)
    {
        if (!exportScript && !exportXml) return;
        Stopwatch stopwatch = new Stopwatch();
        stopwatch.Reset();
        stopwatch.Start();
        FileInfo info = new FileInfo(filePath);
        //去掉文件只读属性
        info.Attributes &= ~FileAttributes.ReadOnly;
        XmlDocument doc = new XmlDocument();
        XmlNode root = doc.CreateElement("info");
        doc.AppendChild(root);
        StringBuilder code = new StringBuilder();
        //保存表格每列的名称，用于给cs脚本添加注释
        Dictionary<int, string> noteDict = new Dictionary<int, string>();
        //保存表格字段名称，用于创建脚本字段名称和XML标签名称
        Dictionary<int, string> fieldDict = new Dictionary<int, string>();
        using (FileStream fs = File.Open(filePath, FileMode.Open))
        {
            //支持两种Excel格式，这里只读取Excel的第一张表
            ISheet sheet = null;
            if (filePath.EndsWith(".xls"))
            {
                HSSFWorkbook book = new HSSFWorkbook(fs);
                sheet = book.GetSheetAt(0);
            }
            if (filePath.EndsWith(".xlsx"))
            {
                XSSFWorkbook book2 = new XSSFWorkbook(fs);
                sheet = book2.GetSheetAt(0);
            }
            if (sheet == null)
            {
                EditorUtility.DisplayDialog("提示", "解析错误！文件格式不支持！", "Fuck");
                return;
            }
            //通过两个循环给noteDict和fieldDict赋值
            for (int i = 0; i < 2; i++)
            {
                IRow row = sheet.GetRow(i);
                for (int j = 0; j < row.LastCellNum; j++)
                {
                    ICell cell = row.GetCell(j);
                    string str = cell == null ? "" : cell.ToString();
                    if (i == 0)
                    {
                        noteDict.Add(j, str);
                    }
                    else if (i == 1)
                    {
                        fieldDict.Add(j, str);
                    }
                }
            }
            string fileName = sheet.SheetName;
            if (exportXml)
            {
                XmlNode item = null;
                for (int i = 0; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    //表格前两行是汉字说明和字段名称
                    if (i > 1)
                    {
                        //单独读取ID字段
                        item = doc.CreateElement("item");
                        XmlAttribute a = doc.CreateAttribute(fieldDict[0]);
                        if (row.GetCell(0) == null)
                        {
                            Debug.LogError(string.Format("第{0}行的id为空", i + 1));
                            return;
                        }
                        if (string.IsNullOrEmpty(row.GetCell(0).ToString())) continue;
                        a.Value = row.GetCell(0).ToString();
                        item.Attributes.Append(a);
                        root.AppendChild(item);
                    }
                    for (int j = 0; j < fieldDict.Count; j++)
                    {
                        //其他字段解析
                        if (i > 1 && item != null)
                        {
                            XmlNode node = doc.CreateElement(fieldDict[j]);
                            string str = "";
                            //如果Cell不为空的话把Cell的值赋给str
                            if (row.GetCell(j) != null && !string.IsNullOrEmpty(row.GetCell(j).ToString()))
                            {
                                str = row.GetCell(j).ToString();
                            }
                            else
                            {
                                //如果Cell为空(每填数值) 并且Cell是整形数值就给默认数值0 其他类型默认空字符串
                                if (fieldDict[j].StartsWith("n32")) str = "0";
                            }
                            node.InnerText = str;
                            item.AppendChild(node);
                        }
                    }
                }
                //保存XML
                string savePath = xmlPath + "/" + fileName + ".xml";
                if (!Directory.Exists(xmlPath)) Directory.CreateDirectory(xmlPath);
                doc.Save(savePath);
            }
            //导出cs代码
            if (exportScript)
            {
                code.Append("using System;");
                code.Append("\n\npublic class " + fileName + " : IConfig\n{");
                code.Append("\n\n\tpublic int UniqueId { get{ return id; }}");
                //根据字段名称创建字段
                for (int i = 0; i < fieldDict.Count; i++)
                {
                    code.Append("\n\n\t/// <summary>\n\t/// " + noteDict[i] + "\n\t/// </summary>");
                    string fieldName = fieldDict[i];
                    string type = "";
                    //这里只区分了整形和字符串两种类型 有需要的话可以拓展
                    if (fieldName.StartsWith("sz")) type = "string";
                    else type = "int";
                    code.Append("\n\tpublic " + type + " " + fieldName + ";");
                }
                code.Append("\n\n}");
                string codePath = scriptPath + "/" + fileName + ".cs";
                if (!Directory.Exists(scriptPath)) Directory.CreateDirectory(scriptPath);
                //保存
                using (StreamWriter sw = new StreamWriter(codePath, false))
                {
                    sw.Write(code.ToString());
                    sw.Flush();
                }
            }
            AssetDatabase.Refresh();
        }
        stopwatch.Stop();
        Debug.Log("转换Excel！耗时：" + stopwatch.Elapsed.TotalMilliseconds + " 毫秒");
    }
}

上面的脚本编译完之后在unity的菜单栏会多出一个Tools选项，点击Tools选择Convert Excel会打开下面的窗口

输入Excel路径 单击导出！结束！！

PS：

• 一定要放到Editor文件夹下（如果你要打包发布的话）
• 如果导出的XML是公式而不是最终计算的结果，可以复制Excel的列表再选择粘贴数值
• 有不好的地方欢迎各位大佬批评指正

---