Unity 将表Excel表转成csv表

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using Excel;
using SKJZ.Core;
using System.Data;
using System.Diagnostics;


namespace SKJZ
{
    namespace Editor
    {
        /// <summary>
        /// 将表Excel表转成csv表
        /// </summary>
        public class ExcelConvertor : Singleton<ExcelConvertor>
        {
            readonly Dictionary<string, DataSet> _needConvertExcelFiles = new Dictionary<string, DataSet>();
            private string _excelPath;
            private string _excelExtension;
            private string _outputPath;

            private const string DefaultOutputFileSeparatorChar = " ";
            private const string DefaultOutPutFileExtension = "csv";


            public void Excute(string excelPath, string outputPath, string excelExtension)
            {
                _excelPath = excelPath;
                _outputPath = outputPath;
                _excelExtension = excelExtension;

                if (string.IsNullOrEmpty(_excelPath) || string.IsNullOrEmpty(_outputPath) || string.IsNullOrEmpty(_excelExtension))
                {
                    Debug.WriteLine("Excute some path is null or empty");
                    return;
                }

                if (NeedConvertExcels())
                {
                    ConverToCsv();
                }
            }

            private bool NeedConvertExcels()
            {
                _needConvertExcelFiles.Clear();
                foreach (string file in Directory.GetFileSystemEntries(_excelPath,  "*." + _excelExtension))
                {
                    var filename = Path.GetFileNameWithoutExtension(file);
                    using (FileStream stream = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                    {
                        string extension = Path.GetExtension(file);

                        if (String.CompareOrdinal(extension, ".xls") == 0)
                        {
                            using (IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream))
                            {
                                var result = excelReader.AsDataSet();

                                excelReader.IsFirstRowAsColumnNames = true;
                                _needConvertExcelFiles.Add(filename, result);
                                excelReader.Close();
                            }
                        }
                        else if (String.CompareOrdinal(extension, ".xlsx") == 0)
                        {
                            using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
                            {
                                var result = excelReader.AsDataSet();

                                //excelReader.IsFirstRowAsColumnNames = true;
                                _needConvertExcelFiles.Add(filename, result);
                                excelReader.Close();
                            }
                        }
                        else
                        {
                            UnityEngine.Debug.Log("extension is not xls or xlsx");

                            return false;
                        }
                    }
                }

                return true;
            }

            private void ConverToCsv()
            {
                if (_needConvertExcelFiles.Count == 0) return;

                if (Directory.Exists(_outputPath))
                {
                    Directory.Delete(_outputPath, true);
                    Directory.CreateDirectory(_outputPath);
                }
                else
                {
                    Directory.CreateDirectory(_outputPath);
                }

                foreach (var File in _needConvertExcelFiles)
                {
                    var content = GetExcelFile(File.Value);
                    var output = string.Format("{0}\\{1}.{2}", _outputPath, File.Key, DefaultOutPutFileExtension);
                    using (var sw = new StreamWriter(output, false, Encoding.UTF8))
                    {
                        sw.Write(content);
                    }
                }
            }

            private static StringBuilder GetExcelFile(DataSet dataTabale, int ind = 0)
            {
                var content = new StringBuilder();
                var rowNumber = 0;

                while (rowNumber < dataTabale.Tables[ind].Rows.Count)
                {
                    for (int i = 0; i < dataTabale.Tables[ind].Columns.Count; i++)
                    {
                        content.Append(dataTabale.Tables[ind].Rows[rowNumber][i]);
                        if (i != dataTabale.Tables[ind].Columns.Count - 1)
                        {
                            content.Append(DefaultOutputFileSeparatorChar);
                        }
                    }
                    content.Append(Environment.NewLine);

                    rowNumber++;
                }
                return content;
            }
        }
    }
}

//********************************************************************
// 文件名: TableToolWindow.cs
// 描述: 将Execl表转为csv，并生成代码
// 作者: U-xia
// 创建时间: 2015-02-13
//
// 修改历史:
// 2015-02-13 U-xia创建,实现主要功能
//********************************************************************


using System;
using UnityEngine;
using UnityEditor;
using System.IO;
using System.Text;
using SKJZ.Core;
using SKJZ.Editor;


public class TableToolWindow : EditorWindow
{
    public static TableToolWindow window;

    [MenuItem("Window/SKJZ/TableWindow")]
    static void Init()
    {
        window = GetWindow<TableToolWindow>();
        window.Show();
        window.title = "TableTool";
        window.minSize = new Vector2(600, 600);

        Singleton<TableTool>.Instance.Init();
    }

    void OnGUI()
    {
        Singleton<TableTool>.Instance.DrewUI();
    }
}

public class TableTool :Singleton<TableTool>
{
    private const string TableSourceName = "表导入路径";
    private const string TableOutputName = "表导出路径";
    private const string CodeServerOutputName = "服务器代码导出路径";
    private const string CodeClientOutputName = "客户端代码导出路径";

    private const string OpenTablePanelTitle = "选择表";
    private const string SaveTablePanelTitle = "保存表";
    private const string SaveCodePanelTitle = "保存代码";

    private const string BtnNameFolder = "文件夹";
    private const string BtnNameFile = "文件";

    private const string DefaultExtension = "xlsx";

    private const string DefaultTableSourceFolderName = "Table";
    private const string DefaultTableOutputFolderName = "Cvs";
    private const string DefaultServerCodeSourceFolderName = "SeverCode";
    private const string DefaultClientCodeSourceFolderName = "ClientCode";


    private const string TableSourcePathKey = "TableTool_TableSourcePathKey";
    private const string TableOutputPathKey = "TableTool_TableOutputPathKey";
    private const string CodeServerOutputPathKey = "TableTool_CodeServerOutputPathKey";
    private const string CodeClientOutputPathKey = "TableTool_CodeClientOutputPathKey";

    private string _tableSourcePath = string.Empty;
    private string _tableOutputPath = string.Empty;
    private string _codeServerOutputPath = string.Empty;
    private string _codeClientOutputPath = string.Empty;

    private ItemData _tableSourceItemData;
    private ItemData _tableOutputItemData;
    private ItemData _tableServerCodeItemData;
    private ItemData _tableClientCodeItemData;


    public void Init()
    {
        //选择Excel表格
        _tableSourceItemData = new ItemData();
        _tableSourceItemData.LabelName = TableSourceName;
        _tableSourceItemData.Path = _tableSourcePath;
        _tableSourceItemData.BtnFolderName = BtnNameFolder;
        _tableSourceItemData.BtnFileName = BtnNameFile;
        _tableSourceItemData.BtnFolderCallback = DoTableSourceFolder;
        _tableSourceItemData.BtnFileCallback = DoTableSourceFile;
        _tableSourceItemData.IsBtnFileActive = true;
        _tableSourceItemData.DefaultFolderName = DefaultTableSourceFolderName;
        _tableSourceItemData.PlayerPrefsKey = TableSourcePathKey;

        _tableSourceItemData.Init();


        //导出csv表
        _tableOutputItemData = new ItemData();
        _tableOutputItemData.LabelName = TableOutputName;
        _tableOutputItemData.Path = _tableOutputPath;
        _tableOutputItemData.BtnFolderName = BtnNameFolder;
        _tableOutputItemData.BtnFileName = BtnNameFile;
        _tableOutputItemData.BtnFolderCallback = DoTableOutput;
        _tableOutputItemData.BtnFileCallback = null;
        _tableOutputItemData.IsBtnFileActive = false;
        _tableOutputItemData.DefaultFolderName = DefaultTableOutputFolderName;
        _tableOutputItemData.PlayerPrefsKey = TableOutputPathKey;

        _tableOutputItemData.Init();

        //为服务器导出代码
        _tableServerCodeItemData = new ItemData();
        _tableServerCodeItemData.LabelName = CodeServerOutputName;
        _tableServerCodeItemData.Path = _codeServerOutputPath;
        _tableServerCodeItemData.BtnFolderName = BtnNameFolder;
        _tableServerCodeItemData.BtnFileName = BtnNameFile;
        _tableServerCodeItemData.BtnFolderCallback = DoCodeServer;
        _tableServerCodeItemData.BtnFileCallback = null;
        _tableServerCodeItemData.IsBtnFileActive = false;
        _tableServerCodeItemData.DefaultFolderName = DefaultServerCodeSourceFolderName;
        _tableServerCodeItemData.PlayerPrefsKey = CodeServerOutputPathKey;

        _tableServerCodeItemData.Init();

        //为客户端导出代码
        _tableClientCodeItemData = new ItemData();
        _tableClientCodeItemData.LabelName = CodeClientOutputName;
        _tableClientCodeItemData.Path = _codeClientOutputPath;
        _tableClientCodeItemData.BtnFolderName = BtnNameFolder;
        _tableClientCodeItemData.BtnFileName = BtnNameFile;
        _tableClientCodeItemData.BtnFolderCallback = DoCodeClient;
        _tableClientCodeItemData.BtnFileCallback = null;
        _tableClientCodeItemData.IsBtnFileActive = false;
        _tableClientCodeItemData.DefaultFolderName = DefaultClientCodeSourceFolderName;
        _tableClientCodeItemData.PlayerPrefsKey = CodeClientOutputPathKey;

        _tableClientCodeItemData.Init();
    }


    //绘制UI
    public void DrewUI()
    {
        GUILayout.Space(2);

        GUILayout.BeginVertical();
        DrewItem(_tableSourceItemData);
        DrewItem(_tableOutputItemData);
        DrewItem(_tableServerCodeItemData);
        DrewItem(_tableClientCodeItemData);

        GUILayout.Space(100);
        DrewBtn();

        GUILayout.EndVertical();
    }


    /// <summary>
    /// 绘制一个单元
    /// </summary>
    /// <param name="labelName"></param>
    /// <param name="pathStr"></param>
    /// <param name="btnFolderCallback"></param>
    /// <param name="btnFileCallback"></param>
    private void DrewItem(ItemData itemData)
    {
        if (itemData == null) return;

        GUILayout.Space(1);

        GUILayout.BeginHorizontal();

        GUILayout.Label(itemData.LabelName, GUILayout.Width(70), GUILayout.Height(20));

        GUILayout.FlexibleSpace();
        itemData.Path = GUILayout.TextField(itemData.Path, GUILayout.Width(400), GUILayout.Height(20));

        GUILayout.FlexibleSpace();
        GUI.color = Color.green;
        if (GUILayout.Button(BtnNameFolder, GUILayout.Height(20)) && itemData.BtnFolderCallback != null)
        {
            itemData.BtnFolderCallback();
        }

        GUILayout.FlexibleSpace();
        if (!itemData.IsBtnFileActive)
        {
            GUI.color = Color.red;
        }

        if (GUILayout.Button(BtnNameFile, GUILayout.Height(20)) && itemData.BtnFileCallback != null && itemData.IsBtnFileActive)
        {
            itemData.BtnFileCallback();
        }

        GUI.color = Color.white;
        GUILayout.EndHorizontal();

        GUILayout.Space(1);
    }


    void DrewBtn()
    {
        GUILayout.BeginHorizontal();
        if (GUILayout.Button("导出", GUILayout.Height(20)))
        {
            DoOkBtn();
        }
        if (GUILayout.Button("取消", GUILayout.Height(20)))
        {
            DoCancelBtn();
        }
        GUILayout.EndHorizontal();
    }

    void DoOkBtn()
    {
        Singleton<ExcelConvertor>.Instance.Excute(_tableSourceItemData.Path, _tableOutputItemData.Path, DefaultExtension);
    }

    void DoCancelBtn()
    {
        EditorWindow.GetWindow<TableToolWindow>().Close();
    }


    void DoTableSourceFolder()
    {
        _tableSourceItemData.Path = SKJZEditorUtility.OpenFolderPanel(OpenTablePanelTitle, _tableSourceItemData.Path, DefaultTableSourceFolderName);
    }

    void DoTableSourceFile()
    {
        _tableSourceItemData.Path = SKJZEditorUtility.OpenFilePanel(OpenTablePanelTitle, _tableSourceItemData.Path, DefaultExtension);
    }

    void DoTableOutput()
    {
        _tableOutputItemData.Path = SKJZEditorUtility.OpenFolderPanel(SaveTablePanelTitle, _tableOutputItemData.Path, DefaultTableOutputFolderName);
    }


    void DoCodeServer()
    {
        _tableServerCodeItemData.Path = SKJZEditorUtility.OpenFolderPanel(CodeServerOutputName, _tableServerCodeItemData.Path, DefaultServerCodeSourceFolderName);
    }

    private void DoCodeClient()
    {
        _tableClientCodeItemData.Path = SKJZEditorUtility.OpenFolderPanel(CodeClientOutputName, _tableClientCodeItemData.Path, DefaultClientCodeSourceFolderName);
    }

    public class ItemData
    {
        public string LabelName;

        private string _path;

        public string Path
        {
            get { return _path; }
            set
            {
                _path = value;

                WritePlayerPrefs();
            }
        }
        public string BtnFolderName;
        public string BtnFileName;
        public Action BtnFolderCallback;
        public Action BtnFileCallback;
        public bool IsBtnFileActive;
        public string DefaultFolderName;
        public string PlayerPrefsKey;

        public void Init()
        {
            ReadPlayerPrefs();
        }


        //读取上次使用的路径
        private void ReadPlayerPrefs()
        {
            _path = PlayerPrefs.GetString(PlayerPrefsKey, _path);
        }


        /// <summary>
        /// 记录上次使用的路径
        /// </summary>
        public void WritePlayerPrefs()
        {
            PlayerPrefs.SetString(PlayerPrefsKey, _path);
        }
    }

}

//********************************************************************
// 文件名: SKJZEditorUtility.cs
// 描述: Unity编辑器扩展工具类
// 作者: U-xia
// 创建时间: 2015-02-13
//
// 修改历史:
// 2015-00-00 U-xia 创建 添加OpenFolderPanel(); OpenFilePanel();
//********************************************************************


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


public class SKJZEditorUtility 
{
    /// <summary>
    /// 打开文件夹
    /// </summary>
    /// <param name="windowTitle">面板标题</param>
    /// <param name="path">文件夹路径</param>
    /// <param name="defaultFolder">选择的默认文件夹</param>
    /// <returns>选择的文件路径</returns>
    public static string OpenFolderPanel(string windowTitle, string path, string defaultFolder)
    {
        var pathTemp = string.IsNullOrEmpty(path) ? string.Empty : Path.GetDirectoryName(path);

        if (!string.IsNullOrEmpty(pathTemp) && Path.IsPathRooted(pathTemp))
        {
            string[] arr = pathTemp.Split(Path.AltDirectorySeparatorChar);

            var stringBuilder = new StringBuilder();

            for (int i = 0; i < arr.Length - 1; i++)
            {
                stringBuilder.Append(arr[i]);
                stringBuilder.Append(Path.AltDirectorySeparatorChar);
            }

            pathTemp = stringBuilder.ToString();
        }

        pathTemp = EditorUtility.OpenFolderPanel(windowTitle, pathTemp, defaultFolder);

        return string.IsNullOrEmpty(pathTemp) ? path : pathTemp;
    }


    /// <summary>
    /// 打开文件
    /// </summary>
    /// <param name="windowTitle">面板标题</param>
    /// <param name="path">文件夹路径</param>
    /// <param name="extension">默认后缀</param>
    /// <returns>选择的文件路径</returns>
    public static string OpenFilePanel(string windowTitle, string path, string extension)
    {
        var pathTemp = string.IsNullOrEmpty(path) ? string.Empty : Path.GetDirectoryName(path);

        pathTemp = EditorUtility.OpenFilePanel(windowTitle, pathTemp, extension);

        return string.IsNullOrEmpty(pathTemp) ? path : pathTemp;
    }
}