etoy-xgame/etoy/Pileline/Cmd/CmdExcelToCsv.cs

using ExcelDataReader;
using System.Collections.Concurrent;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;

namespace etoy
{
    internal class CmdExcelToCsv : Command
    {
        private int _total = 0;
        private readonly List<Exception> _errors = new List<Exception>();
        private readonly ConcurrentStack<string> _dones = new ConcurrentStack<string>();
        private readonly Regex _removeStart = new Regex("^( *)[\n\r\t]+");
        private readonly Regex _removeEnd = new Regex("[\n\r\t]+( *)$", RegexOptions.RightToLeft);

        public override string Description => "Excel转Csv";

        protected override void OnProcess()
        {
            if (Directory.Exists(Context.Option.CsvOutput))
                Directory.Delete(Context.Option.CsvOutput, true);
            Directory.CreateDirectory(Context.Option.CsvOutput);

            string csvOutput = Context.Option.CsvOutput;

            var files = Directory.GetFiles(Context.Option.ExcelInput, "*.xlsx", SearchOption.AllDirectories).Where(file => !file.Contains("~$")).ToArray();

            _total = files.Length;

            Task.Factory.StartNew(OnLoop);

            Start(files, csvOutput);
        }

        private void OnLoop()
        {
            while (_dones.Count < _total)
            {
                Thread.Sleep(1);
            }

            if (_errors.Count > 0)
            {
                SetException(new Exception(string.Join('\n', (from e in _errors select $"## ERROR: {e}").ToArray())));
            }
            else
            {
                Completed();
            }
        }

        private void Start(string[] files, string output)
        {
            for (int i = 0, length = files.Length; i < length; ++i)
            {
                var file = files[i];

                Task.Factory.StartNew(index =>
                {
                    try
                    {
                        Convert(file, output);

                        _dones.Push(file);

                        SetProgress(_dones.Count / (float)_total);
                    }
                    catch (Exception e)
                    {
                        --_total;
                        _errors.Add(e);
                    }
                }, i);
            }
        }

        private string GetTableName(DataTable dt)
        {
            var result = dt.TableName.Trim();
            result = result
                    .Replace("\r", string.Empty)
                    .Replace("\n", string.Empty)
                    .Replace("\t", string.Empty);
            if (result.Length > 64)
                result = result.Substring(0, 64);
            return result;
        }

        private void Convert(string source, string destination)
        {
            using var excel = new FileStream(source, FileMode.Open, FileAccess.Read);
            var reader = ExcelReaderFactory.CreateReader(excel);
            var tables = reader.AsDataSet().Tables;
            var sb = new StringBuilder();

            string excelFileName = Path.GetFileNameWithoutExtension(source);

            foreach (DataTable table in tables)
            {
                if (table.TableName.Contains('#'))
                    continue;

                sb.Clear();

                string extension;
                string tableName;
                var rowOffset = 1; // 正式数据行数偏移值

                if (excelFileName == "__Metadata" || excelFileName == "共用枚举结构体")
                {
                    extension = ".metadata";
                    tableName = "Metadata";
                }
                else if (excelFileName == "__KeyValue" || excelFileName == "Key_Value")
                {
                    extension = ".kv";
                    tableName = "KeyValue";
                }
                else
                {
                    extension = ".csv";
                    tableName = GetTableName(table);
                    rowOffset = 4;// 前4行0-3（字段名，字段类型，tag，字段描述）不需要保留\n\t等等
                }

                string csvName = string.Concat(tableName, extension);
                string csvPath = Path.Combine(destination, csvName);
                if (File.Exists(csvPath))
                    throw new Exception($"表重复了, Name: {tableName}, ExcelName: {excelFileName}");

                int rows = table.Rows.Count;
                int columns = table.Columns.Count;

                if (rows == 0)
                    continue;

                for (int i = 0; i < rows; ++i)
                {
                    if (i < rowOffset) 
                    {
                        for (int j = 0; j < columns; ++j)
                        {
                            string value = table.Rows[i][j].ToString()
                                .Replace("\t", string.Empty)
                                .Replace("\n", string.Empty)
                                .Replace("\r", string.Empty)
                                .Replace("\"", "\"\"");

                            sb.Append($"\"{value}\"");

                            if (j < columns - 1)
                                sb.Append(',');
                        }
                    }
                    else
                    {
                        for (int j = 0; j < columns; ++j)
                        {
                            string value = table.Rows[i][j].ToString();
                            string value1 = _removeStart.Replace(value, "$1");
                            string value2 = _removeEnd.Replace(value1, "$1");
                            string value3 = value2
                                .Replace("\t", string.Empty)
                                .Replace("\n", "\\n")
                                .Replace("\r", "\\n")
                                .Replace("\"", "\"\"");

                            sb.Append($"\"{value3}\"");

                            if (j < columns - 1)
                                sb.Append(',');
                        }
                    }

                    sb.AppendLine();
                }

                File.WriteAllText(csvPath, sb.ToString(), Encoding.UTF8);
            }
        }
    }
}