Client/GameClient/Assets/Editor/Excel/ExcelReader.cs

using System.IO;
using UnityEditor;
using UnityEngine;
using System.Data;
using GFGEditor;
using OfficeOpenXml;
using System;

namespace GFGEditor
{
    public class ExcelReader
    {
        public delegate void RowCollectionHandler(ExcelWorksheet rowCollection, string configName, string configArrayName);

        public static void ReadExcel(RowCollectionHandler rowCollectionHandler)
        {
            string[] files = Directory.GetFiles(ExcelConfig.excelsCacheFolderPath);
            int totalCount = files.Length;
            string strCfgArrayDispose = "";
            string strCfgArrayInit = "";
            for (int i = 0; i < totalCount; i++)
            {
                string filePath = files[i];
                string fileName = Path.GetFileNameWithoutExtension(filePath);
                if (!fileName.Contains("~"))
                {
                    ET.Log.Debug($"fileName {fileName}");
                    Stream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);
                    ExcelPackage excelPackage = new ExcelPackage(stream);
                    //IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                    //DataSet result = excelReader.AsDataSet();

                    HandleTableCollection(excelPackage.Workbook.Worksheets, rowCollectionHandler, out string dispose, out string init);
                    strCfgArrayDispose = strCfgArrayDispose + dispose;
                    strCfgArrayInit = strCfgArrayInit + init;
                }
            }
            string functionDispose = CodeTemplateFactory.FunctionDisposeTemplate;
            functionDispose = functionDispose.Replace("{StrCfgArrayDispose}", strCfgArrayDispose);
            string disposeAllCfgsCache = CodeTemplateFactory.DisposeAllCfgsCacheTemplate;
            disposeAllCfgsCache = disposeAllCfgsCache.Replace("{FunctionDispose}", functionDispose);
            using (StreamWriter sw = new StreamWriter(ExcelConfig.handleAllCfgsCache + "DisposeAllCfgsCache.cs"))
            {
                sw.Write(disposeAllCfgsCache);
            }

            string functionInit = CodeTemplateFactory.FunctionInitTemplate;
            functionInit = functionInit.Replace("{StrCfgArrayInit}", strCfgArrayInit);
            string initAllCfgsCache = CodeTemplateFactory.InitAllCfgsCacheTemplate;
            initAllCfgsCache = initAllCfgsCache.Replace("{FunctionInit}", functionInit);
            using (StreamWriter sw = new StreamWriter(ExcelConfig.handleAllCfgsCache + "InitAllCfgsCache.cs"))
            {
                sw.Write(initAllCfgsCache);
            }
        }

        private static void HandleTableCollection(ExcelWorksheets Worksheets, RowCollectionHandler rowCollectionHandler, out string strDispose, out string strInit)
        {
            strDispose = "";
            strInit = "";
            for (int i = 1; i <= Worksheets.Count; i++)
            {
                ExcelWorksheet worksheet = Worksheets[i];
                string configArrayName = HandleTable(worksheet, rowCollectionHandler);
                if (string.IsNullOrEmpty(configArrayName)) continue;
                string dispose = CodeTemplateFactory.StrCfgArrayDisposeTemplate.Replace("{configArrayName}", configArrayName);
                strDispose = strDispose + "\n" + dispose;
                string init = CodeTemplateFactory.StrCfgArrayInitTemplate.Replace("{configArrayName}", configArrayName);
                strInit = strInit + "\n" + init;
            }
        }

        private static string HandleTable(ExcelWorksheet worksheet, RowCollectionHandler rowCollectionHandler)
        {
            string[] names = worksheet.Name.Split('_');
            if (names.Length < 2)
            {
                //未正确命名的表格
                return "";
            }
            if (worksheet.Name.Contains("#"))
            {
                //被注释的表格
                return "";
            }
            string configItemName = names[1];
            string managerTag = "";
            if (names.Length > 2)
            {
                managerTag = names[2];
            }
            //文件名及管理器名
            string configManagerName = string.Format(ExcelConfig.CONFIG_ARRAY_TEMPLATE, configItemName, managerTag);

            rowCollectionHandler(worksheet, configItemName, configManagerName);

            return configManagerName;
        }


        public static void WriteExcle()
        {
            DeleteExcle();
            string[] files = Directory.GetFiles(ExcelConfig.excelsFolderPath);
            int totalCount = files.Length;
            for (int i = 0; i < totalCount; i++)
            {
                string filePath = files[i];
                string fileName = Path.GetFileNameWithoutExtension(filePath);
                if (!fileName.Contains("~") && !fileName.Contains("0000自动生表工具") && !fileName.Contains("zzzzGFG数值"))
                {
                    string newPath = ExcelConfig.excelsCacheFolderPath + fileName + ".xlsx";
                    Stream newStream = new FileStream(newPath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
                    ExcelPackage newExcelPackage = new ExcelPackage(newStream);
                    ExcelWorksheets newWorksheets = newExcelPackage.Workbook.Worksheets;

                    Stream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);
                    ExcelPackage excelPackage = new ExcelPackage(stream);
                    ExcelWorksheets Worksheets = excelPackage.Workbook.Worksheets;
                    for (int j = 1; j <= Worksheets.Count; j++)
                    {
                        ExcelWorksheet worksheet = Worksheets[j];
                        string[] names = worksheet.Name.Split('_');
                        if (names.Length < 2) continue;

                        ExcelWorksheet newWorksheet = null;
                        for (int k = 1; k <= newWorksheets.Count; k++)
                        {
                            string[] newNames = newWorksheets[k].Name.Split('_');
                            if (names.Length < 2 || names.Length != newNames.Length) continue;

                            for (int h = 1; h < names.Length; h++)
                            {
                                if (newNames[h] == names[h])
                                {
                                    newWorksheet = newWorksheets[k];
                                }
                                else
                                {
                                    newWorksheet = null;
                                    break;
                                }
                            }
                        }
                        if (newWorksheet == null)
                        {
                            newWorksheets.Add(worksheet.Name);
                            newWorksheet = newWorksheets[newWorksheets.Count];
                        }
                        WriteTitle(newWorksheet, worksheet);
                        // Debug.Log(worksheet.Name + "写入完成");
                    }
                    newExcelPackage.Save();
                    excelPackage.Save();
                    newStream.Close();
                    stream.Close();
                    newStream.Dispose();
                    stream.Dispose();
                    Debug.Log(fileName + "写入完成");
                }
            }
        }

        private static void WriteTitle(ExcelWorksheet newWorksheet, ExcelWorksheet worksheet)
        {
            int newColumnNum = newWorksheet.Dimension == null ? 0 : newWorksheet.Dimension.End.Column;

            int columnNum = worksheet.Dimension.End.Column;
            for (int i = 1; i <= columnNum; i++)
            {
                string name = worksheet.Cells[3, i].Text.Trim();
                string type = worksheet.Cells[2, i].Text.Trim();
                if (name.Length == 0)
                {
                    continue;
                }
                bool needWriteColumn = true;
                for (int j = 1; j <= newColumnNum; j++)
                {
                    string newName = newWorksheet.Cells[3, j].Text.Trim();
                    string newType = newWorksheet.Cells[2, j].Text.Trim();
                    if (name == newName && type == newType)
                    {
                        needWriteColumn = false;
                    }
                }
                if (needWriteColumn)
                {
                    newColumnNum = newWorksheet.Dimension == null ? 0 : newWorksheet.Dimension.End.Column;
                    newWorksheet.InsertColumn(newColumnNum + 1, 1);

                    for (int k = 1; k <= 4; k++)
                    {
                        newWorksheet.Cells[k, newColumnNum + 1].Value = worksheet.Cells[k, i].Value;
                    }
                }
            }
            WriteCell(newWorksheet, worksheet);
        }
        private static void WriteCell(ExcelWorksheet newWorksheet, ExcelWorksheet worksheet)
        {
            int newRowNum = newWorksheet.Dimension == null ? 0 : newWorksheet.Dimension.End.Row;
            newWorksheet.InsertRow(newRowNum + 1, 2);
            int newColumnNum = newWorksheet.Dimension == null ? 0 : newWorksheet.Dimension.End.Column;

            int rowNum = worksheet.Dimension.End.Row;
            int columnNum = worksheet.Dimension.End.Column;
            for (int i = 5; i <= rowNum; i++)
            {
                newRowNum = newWorksheet.Dimension == null ? 0 : newWorksheet.Dimension.End.Row;
                newWorksheet.InsertRow(newRowNum + 1, 1);
                int column = 0;
                for (int j = 1; j <= columnNum; j++)
                {
                    string title = worksheet.Cells[3, j].Text.Trim();
                    string type = worksheet.Cells[2, j].Text.Trim();
                    if (string.IsNullOrEmpty(title)) continue;
                    for (int k = 1; k <= newColumnNum; k++)
                    {
                        string newTitle = newWorksheet.Cells[3, k].Text.Trim();
                        string newType = newWorksheet.Cells[2, k].Text.Trim();
                        if (newTitle == title && newType == type)
                        {
                            column = k;
                            break;
                        }
                    }

                    newWorksheet.Cells[newRowNum + 1, column].Value = worksheet.Cells[i, j].Value;
                }
            }
            newRowNum = newWorksheet.Dimension == null ? 0 : newWorksheet.Dimension.End.Row;
            newWorksheet.InsertRow(newRowNum + 1, 2);
        }
        private static void DeleteExcle()
        {
            string path = ExcelConfig.excelsCacheFolderPath;
            if (Directory.Exists(path) == false)
            {
                Directory.CreateDirectory(path);
            }
            DirectoryInfo dir = new DirectoryInfo(path);
            // FileInfo[] files = dir.GetFiles();
            string[] files = Directory.GetFiles(path);
            try
            {
                foreach (var item in files)
                {
                    File.Delete(item);
                }
                return;
            }
            catch (Exception)
            {
                ET.Log.Debug("Delete Failed!");
                return;

            }

        }

    }
}