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

public class ExcelTest : MonoBehaviour
{
    void Start()
    {
        string _filePath = Application.streamingAssetsPath + "/学生信息.xlsx";

        WriteExcel(_filePath);
        ReadExcel(_filePath);

        _filePath = Application.streamingAssetsPath + "/DataTest.xlsx";
        List<DataTest> datas = new List<DataTest> {
           new DataTest(0,"先民",100,1.85f) ,
           new DataTest(1,"星宇",110,1.5f) ,
           new DataTest(2,"刘敏",10,1.8f)
        };

        WriteExcel(_filePath, datas);

    }

    void WriteExcel(string _filePath)
    {
        string _sheetName = "详情";
        FileInfo fileInfo = new FileInfo(_filePath);
        if (fileInfo.Exists)
        {
            fileInfo.Delete();   //删除旧文件，并创建一个新的 excel 文件。
            fileInfo = new FileInfo(_filePath);
        }

        //通过ExcelPackage打开文件
        using (ExcelPackage package = new ExcelPackage(fileInfo))
        {
            //在 excel 空文件添加新 sheet，并设置名称。
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(_sheetName);

            //添加列名
            worksheet.Cells[1, 1].Value = "学号";
            worksheet.Cells[1, 2].Value = "姓名";
            worksheet.Cells[1, 3].Value = "性别";

            //添加一行数据
            worksheet.Cells[2, 1].Value = 100001;
            worksheet.Cells[2, 2].Value = "张三";
            worksheet.Cells[2, 3].Value = "男";

            //添加一行数据
            worksheet.Cells[3, 1].Value = 100002;
            worksheet.Cells[3, 2].Value = "李四";
            worksheet.Cells[3, 3].Value = "女";

            //添加一行数据
            worksheet.Cells[4, 1].Value = 120033;
            worksheet.Cells[4, 2].Value = "Saw";
            worksheet.Cells[4, 3].Value = "男";

            //保存excel
            package.Save();
        }
    }

    void ReadExcel(string _filePath)
    {
        //获取Excel文件的信息
        FileInfo fileInfo = new FileInfo(_filePath);
        if (!fileInfo.Exists)
            return;

        using (ExcelPackage excelPackage = new ExcelPackage(fileInfo))
        {
            ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[1];
            int rowCount = worksheet.Dimension.End.Row;
            int columnCount = worksheet.Dimension.End.Column;
            for (int i = 0; i < rowCount; i++)//End.Row获得当前表格的最大行数
            {
                string str = null;
                for (int j = 0; j < columnCount; j++)
                {
                    str += worksheet.Cells[i + 1, j + 1].Value.ToString();
                    if (j < columnCount - 1)
                    {
                        str += ",";
                    }
                }
                Debug.Log($"ReadExcel: line {i + 1} " + str);
            }
        }
    }

    void WriteExcel<T>(string _filePath, List<T> datas) where T : BaseData
    {
        if (datas == null || datas.Count <= 0)
            return;

        string _sheetName = "详情";
        FileInfo fileInfo = new FileInfo(_filePath);
        if (fileInfo.Exists)
        {
            fileInfo.Delete();   //删除旧文件，并创建一个新的 excel 文件。
            fileInfo = new FileInfo(_filePath);
        }

        //通过ExcelPackage打开文件
        using (ExcelPackage package = new ExcelPackage(fileInfo))
        { 
            BaseData data = datas[0];
            _sheetName = data.GetType().Name;

            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(_sheetName); //在 excel 空文件添加新 sheet，并设置名称。


            //变量名、变量类型、 注释
            string[,] keys = data.GetDataKey(); 
            int rowKey = 3;
            int columnKey = keys.Length / rowKey;

            //rowKey = 0;

            for (int i = 0; i < rowKey; i++)
            {
                for (int j = 0; j < columnKey; j++)
                {
                    worksheet.Cells[i + 1, j + 1].Value = keys[i, j];
                }
            }

            //数据
            int row = datas.Count;
            for (int i = 0; i < row; i++)
            {
                data = datas[i];
                string[] valueArr = data.GetDataValue();
                int column = valueArr.Length;
                for (int j = 0; j < column; j++)
                {
                    worksheet.Cells[i + 1 + rowKey, j + 1].Value = valueArr[j];
                }
            }

            //保存excel
            package.Save();
        }
    }
}



static class Utils
{
    public static string GetTypeStr(this System.Type type)
    {

        if (type == typeof(string))
        {
            return "string";
        }
        else if (type == typeof(float))
        {
            return "float";
        }
        else if (type == typeof(int))
        {
            return "int";
        }
        else if (type == typeof(long))
        {
            return "long";
        }
        else if (type == typeof(double))
        {
            return "double";
        }

        return type.Name;
    }
}
abstract class BaseData
{
    public abstract string[] GetDataValue();

    public abstract string[,] GetDataKey();

}
 
class DataTest : BaseData
{
    public int id;
    public string name;
    public int leve;
    public float height;

    public DataTest()
    {

    }

    public DataTest(int _id, string _name, int _leve, float _height)
    {
        id = _id;
        name = _name;
        leve = _leve;
        height = _height;
    }


    public override string[,] GetDataKey()
    {
        return new string[,] {
        { nameof(id)  ,  nameof(name) , nameof(leve),  nameof(height) },
        { id.GetType().GetTypeStr(), name.GetType().GetTypeStr(), leve.GetType().GetTypeStr(), height.GetType().GetTypeStr()},
        { "唯一ID", "姓名", "等级", "身高" }
        };
    }

    public override string[] GetDataValue()
    {
        return new string[] { id.ToString(), name.ToString(), leve.ToString(), height.ToString() };
    }

}