feilong-client/Assets/Scripts/Framework/Editor/Asset/Addressable/ExcelHelper.cs

using System;
using System.Collections.Generic;
using System.IO;
using XGame.Framework.Asset;
using XGame.Framework.Asset.Addressable;
using XGame.Framework.Asset.Addressable.Data;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using UnityEditor;
using UnityEngine;

namespace XGame.Editor.Asset
{
    public class ExcelHelper
    {
        public static void AddressableAssetsToExcel(AddressableClassify classify)
        {
            AddressableAssetsToExcel(AddressableHelper.LoadAssetManifest(classify));
        }
        public static void AddressableAssetsToExcel(AddressableInfosSo manifest)
        {
            if (manifest == null)
            {
                Debug.LogError("AddressableAssetInfoManifest is null.");
                return;
            }

            var filePath = EditorUtility.SaveFilePanel("Save Addressable Asset Manifest", Application.dataPath, "AddressableAsset", "xlsx");
            if (string.IsNullOrEmpty(filePath))
            {
                return;
            }
            var assetInfos = manifest.GetAssetInfos();
            assetInfos.Sort(((info0, info1) => string.Compare(info0.GetAssetPath(), info1.GetAssetPath(), StringComparison.CurrentCultureIgnoreCase)));

            IWorkbook workbook = null;
            //若文件已存在
            //if (File.Exists(filePath))

            //根据指定的文件格式创建对应的类
            if (Path.GetExtension(filePath).Equals(".xls"))
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                workbook = new XSSFWorkbook();
            }
            //创建表单
            //所有资源表单
            CreateSheet(workbook, "所有资源", assetInfos);
            //名字不匹配的资源表单
            var count = 0;
            var mismatchAssetInfos = assetInfos.FindAll((info => {
                EditorUtility.DisplayProgressBar("收集资源中...", info.GetAssetPath(), (++count) / (float)assetInfos.Count);
                return AddressableHelper.IsValidPath(info.GetAssetPath()) && !AddressableHelper.IsDefaultAddressableName(info.addressableName, info.assetGUID);
            }));
            EditorUtility.ClearProgressBar();
            CreateSheet(workbook, "名字不匹配的资源", mismatchAssetInfos);
            try
            {
                FileStream fs = File.OpenWrite(filePath);
                workbook.Write(fs);//向打开的这个Excel文件中写入表单并保存。  
                fs.Close();
            }
            catch (Exception e)
            {
                Debug.LogException(e);
            }
            Debug.Log("AddressableAssetsToExcel finish.");
        }

        private static void CreateSheet(IWorkbook workbook, string sheetName, List<AssetInfo> assetInfos)
        {
            //创建一个表单
            ISheet sheet = workbook.CreateSheet(sheetName);
            //标题格式
            var stytleTitle = CreateTitleStytle(workbook);
            IRow row = sheet.CreateRow(0);
            ICell cell;
            //设置列宽
            int[] columnWidth = { 128, 32 };
            string[] titles = { "Asset Path", "Addressable Name" };
            int colCount = columnWidth.Length;
            for (int i = 0; i < colCount; i++)
            {
                //设置列宽度，256*字符数，因为单位是1/256个字符
                sheet.SetColumnWidth(i, 256 * columnWidth[i]);
                cell = row.CreateCell(i);
                cell.CellStyle = stytleTitle;
                SetCellValue(cell, titles[i]);
            }
            var stringStytle = CreateStringStytle(workbook);
            var rowCount = assetInfos.Count;
            for (int i = 0; i < rowCount; i++)
            {
                var assetInfo = assetInfos[i];
                row = sheet.CreateRow(i + 1);
                //path
                cell = row.CreateCell(0);
                cell.CellStyle = stringStytle;
                SetCellValue(cell, assetInfo.GetAssetPath());
                //名字
                cell = row.CreateCell(1);
                cell.CellStyle = stringStytle;
                SetCellValue(cell, assetInfo.addressableName);
            }
        }

        private static ICellStyle CreateTitleStytle(IWorkbook wb)
        {
            //标题的样式
            var style = wb.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;//文字水平对齐方式
            style.VerticalAlignment = VerticalAlignment.Center;//文字垂直对齐方式
            //设置边框
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            style.WrapText = true;//自动换行
            //style.IsLocked = false;//设置该单元格为非锁定

            IFont font = wb.CreateFont();//字体
            font.FontName = "楷体";
            font.Color = HSSFColor.Black.Index;//字体颜色
            font.Boldweight = (short)FontBoldWeight.Bold;//字体加粗样式
            style.SetFont(font);//样式里的字体设置具体的字体样式
            return style;
        }

        private static ICellStyle CreateStringStytle(IWorkbook wb)
        {
            ICellStyle style = wb.CreateCellStyle();//样式
            style.Alignment = HorizontalAlignment.Left;//文字水平对齐方式
            style.VerticalAlignment = VerticalAlignment.Center;//文字垂直对齐方式

            IFont font = wb.CreateFont();//字体
            font.FontName = "楷体";
            font.Color = HSSFColor.Black.Index;//字体颜色
            font.Boldweight = (short)FontBoldWeight.Normal;//字体加粗样式
            style.SetFont(font);//样式里的字体设置具体的字体样式
            //设置背景色
            //style.FillForegroundColor = HSSFColor.Yellow.Index;
            //style.FillPattern = FillPattern.SolidForeground;
            //style.FillBackgroundColor = HSSFColor.Yellow.Index;
            
            return style;
        }

        /// <summary>
        /// 根据数据类型设置不同类型的cell
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="obj"></param>
        private static void SetCellValue(ICell cell, object obj)
        {
            var objType = obj.GetType();
            if (objType == typeof(int))
            {
                cell.SetCellValue((int)obj);
            }
            else if (objType == typeof(double))
            {
                cell.SetCellValue((double)obj);
            }
            else if (objType == typeof(IRichTextString))
            {
                cell.SetCellValue((IRichTextString)obj);
            }
            else if (objType == typeof(string))
            {
                cell.SetCellValue(obj.ToString());
            }
            else if (objType == typeof(DateTime))
            {
                cell.SetCellValue((DateTime)obj);
            }
            else if (objType == typeof(bool))
            {
                cell.SetCellValue((bool)obj);
            }
            else
            {
                cell.SetCellValue(obj.ToString());
            }
        }
    }

}