using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using OfficeOpenXml;
using System.IO;
using System;
using SimpleJSON;

/*使用方法

    using OfficeOpenXml;
    FileInfo fileInfo = new FileInfo(filePath);
    using (ExcelPackage package = new ExcelPackage(fileInfo))
    {
        ...
        脚本A(package);
        脚本B(package);
        ...
        package.Save(); //如果要保存就加这一行（记得关闭excel！！！）
    }

使用方法*/
public class ExcelEditor
{
    //获取所有Sheet名称
	public static List<string> ReadExcelSheetsInfo(ExcelPackage package)
	{
        // 获取Sheet数量
        int sheetCount = package.Workbook.Worksheets.Count;
        // 获取所有Sheet名称
        List<string> sheetNames = new List<string>();
        foreach (ExcelWorksheet worksheet in package.Workbook.Worksheets)
        {
            sheetNames.Add(worksheet.Name);
        }
        return sheetNames;
    }

    //修改Sheet名称
    public static bool RenameSheet(ExcelPackage package, string oldSheetName, string newSheetName)
    {
        try
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets[oldSheetName];
            worksheet.Name = newSheetName;
            Debug.Log($"修改Sheet名\"{oldSheetName}\"为\"{newSheetName}\"");
            return true;
        }
        catch (System.InvalidOperationException e)
        {
            Debug.LogError($"请关闭Excel后重试");
            return false;
        }
        catch (System.ArgumentException)
        {
            Debug.LogError($"Sheet名重复请重试");
            return false;
        }
        catch (System.Exception e)
        {
            Debug.Log(e.GetType());
            Debug.LogException(e);
            return false;
        }
    }

    //获取excel数据
    public static string GetCellData(ExcelPackage package, string sheetName, string cellId)
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetName];
        var value = worksheet.Cells[cellId].Value;
        return value?.ToString() ?? string.Empty;
    }

    //修改excel文件（地址索引）
    public static bool ModifyExcel(ExcelPackage package, string sheetMame, string cellId, string newData)
    {
        try
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetMame];
            worksheet.Cells[cellId].Value = newData; // 通过单元格地址
            return true;
        }
        catch (System.InvalidOperationException e)
        {
            Debug.LogError($"请关闭Excel后重试");
            return false;
        }
        catch (System.Exception e)
        {
            Debug.LogException(e);
            return false;
        }
    }

    //修改excel文件（行列索引）
    public static bool ModifyExcel(ExcelPackage package, string sheetMame, int cell_row, int cell_column, string newData)
    {
        try
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetMame];
            worksheet.Cells[cell_row, cell_column].Value = newData; // 通过行列索引
            package.Save();
            return true;
        }
        catch (System.InvalidOperationException e)
        {
            Debug.LogError($"请关闭Excel后重试");
            return false;
        }
        catch (System.Exception e)
        {
            Debug.LogException(e);
            return false;
        }
    }

    //删除连续多行数据[start_row, end_row]
    public static bool RemoveExcelRows(ExcelPackage package, string sheetMame, int start_row, int end_row)
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetMame];
        try
        {
            for (int i = end_row; i >= start_row; i--)
            {
                worksheet.DeleteRow(i);
            }
            return true;
        }
        catch (System.InvalidOperationException e)
        {
            Debug.LogError($"请关闭Excel后重试");
            return false;
        }
        catch (System.Exception e)
        {
            Debug.LogException(e);
            return false;
        }
    }

    //读取excel的jason参数
    public static JSONNode Loader(string fileName)
    {
        return JSON.Parse(File.ReadAllText("GenerateDatas/json/" + fileName + ".json"));
    }
}