Unity 讀取Excel工作表 以及排行榜的制作

一、讀取Excel

/// <summary>
    ///返回數(shù)據(jù)的集合 
    ///數(shù)據(jù)的格式為 每一行為一條數(shù)據(jù)
    ///例："Me|40|29|2019530" 
    /// </summary>
    /// <returns></returns>
    public List<string> LoadData()
    {
        // StreamingAssets目錄下的  黨員信息.xlsx文件的路徑：Application.streamingAssetsPath + "/黨員信息.xlsx" 
        FileStream fileStream = File.Open(Application.streamingAssetsPath + "/排行榜.xlsx",     
        FileMode.Open, FileAccess.Read);
        IExcelDataReader excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
        // 表格數(shù)據(jù)全部讀取到result里
        DataSet result = excelDataReader.AsDataSet();

        // 獲取表格有多少列 
        int columns = result.Tables[0].Columns.Count;
        // 獲取表格有多少行 
        int rows = result.Tables[0].Rows.Count;

        //獲取表格的行數(shù) -1去掉第一行
        //testRow = rows - 1;

        //Debug.Log("行" + rows);
        // 根據(jù)行列依次打印表格中的每個數(shù)據(jù) 

        List<string> excelDta = new List<string>();

        //第一行為表頭，不讀取
        for (int i = 1; i < rows; i++)
        {
            value = null;
            all = null;
            for (int j = 0; j < columns; j++)
            {
                // 獲取表格中指定行指定列的數(shù)據(jù) 
                value = result.Tables[0].Rows[i][j].ToString();
                //Debug.Log("value = " + value);
                if (value == "")
                {
                    continue;
                }
                all = all + value + "|";
            }
            if (all != null)
            {
                print(all);
                excelDta.Add(all);
            }
        }
        return excelDta;
    }

二、重新寫入Excel

 /// <summary>
    /// list內(nèi)容格式
    /// "Me|40|29|2019530" 
    /// </summary>
    /// <param name="newList"></param>
    public void WriteExcel(List<string> newList)
    {
        //自定義excel的路徑

        string path = Application.streamingAssetsPath + "/排行榜.xlsx";
        // print(Application.dataPath);

        FileInfo newFile = new FileInfo(path);

        if (newFile.Exists)
        {
            //創(chuàng)建一個新的excel文件

            newFile.Delete();

            newFile = new FileInfo(path);
        }

        //通過ExcelPackage打開文件
        using (ExcelPackage package = new ExcelPackage(newFile))
        {
            //在excel空文件添加新sheet

            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("message");
            //添加列名

            worksheet.Cells[1, 1].Value = "playerName";

            worksheet.Cells[1, 2].Value = "accuracy";

            worksheet.Cells[1, 3].Value = "usedTime";

            worksheet.Cells[1, 4].Value = "rankingTime";

            for (int i = 0; i < newList.Count; i++)
            {
                string[] messages = newList[i].Split('|'); //趙一|黨員|1年|趙一.png| 
                string itemName = messages[0];
                string itemWork = messages[1];
                string itemYear = messages[2];
                string imageName = messages[3];
                //添加一行數(shù)據(jù)

                int num = i + 2;

                worksheet.Cells["A" + num].Value = itemName;

                worksheet.Cells["B" + num].Value = itemWork;

                worksheet.Cells["C" + num].Value = itemYear;

                worksheet.Cells["D" + num].Value = imageName;
            }

            //保存excel
            package.Save();
            print("重寫完成");
        }
    }

三、排行榜的制作

 /// <summary>
    /// tmpSubmit - 游戲結(jié)束輸入
    /// </summary>
    public void SubmitRanking(string tmpSubmit)
    {
        startRanking = LoadData();

        Debug.Log(startRanking.Count);
        //Debug.Log("Excel:"+startRanking.Count);
        //Debug.Log("EndExcel:"+startRanking[startRanking.Count-1]);
        if (startRanking.Count == 8)
        {
            if (FloatReckonRatio(startRanking[startRanking.Count - 1]) < FloatReckonRatio(tmpSubmit))
            {
                startRanking[startRanking.Count - 1] = tmpSubmit;
                ListSort(startRanking);
                //Debug.Log(startRanking[startRanking.Count-1]);
                //把最后一面頂替掉然后進行排序
                Debug.Log("進入排行榜成功");
            }
            else
            {
                Debug.Log("進入排行榜失敗");
            }

            Debug.Log("有八位數(shù)據(jù)");
        }
        else
        {
            startRanking.Add(tmpSubmit);
            ListSort(startRanking);
            Debug.Log("未夠十位數(shù)");
        }

        startRanking = LoadData();
        for (int i = 0; i < startRanking.Count; i++)
        {
            Debug.Log(startRanking[i]);
        }
    }

    /// <summary>
    /// 計算是 占比值 玩家分數(shù)
    /// </summary>
    /// <param name="tmpFormerRan"></param>
    /// <returns></returns>
    public float FloatReckonRatio(string tmpFormerRan)
    {
        float ratioFloat = 0;

        for (int i = 0; i < tmpFormerRan.Length; i++)
        {
            string[] tmpString = tmpFormerRan.Split('|');

            float accuracy = float.Parse(tmpString[1]) / 100f;
            float usedTime = 1 / float.Parse(tmpString[2]);
            float total = accuracy + usedTime;

            ratioFloat = total;
        }
        return ratioFloat;
    }

    /// <summary>
    /// 讓List進行重新排序排序
    /// </summary>
    /// <param name="tmpList"></param>
    public List<string> ListSort(List<string> tmpList)
    {
        List<string> _tmpData = new List<string>();

        //數(shù)據(jù)至少有兩個 才開始進行排序
        if (tmpList.Count > 1)
        {
            _tmpData = tmpList;
            for (int i = 0; i < _tmpData.Count; i++)
            {
                for (int j = 0; j < _tmpData.Count - 1 - i; j++)
                {
                    if (FloatReckonRatio(_tmpData[j]) < FloatReckonRatio(_tmpData[j + 1]))
                    {
                        string data = _tmpData[j];
                        _tmpData[j] = _tmpData[j + 1];
                        _tmpData[j + 1] = data;
                    }
                }
            }
        }
        else
        {
            _tmpData = tmpList;
        }

        //讀取重新建表
        WriteExcel(_tmpData);
        return _tmpData;
    }

整個腳本

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using System.IO;
using Excel;
using OfficeOpenXml;
using System.Data;

public class TestExcel : MonoBehaviour
{

    public static TestExcel instance;
    string value;
    string all;
    int testRow;

    private List<string> storageExcel = new List<string>();
    private List<string> startRanking;              //開始的時候存儲的排行榜數(shù)據(jù)
    private string submit;

    private void Awake()
    {
        instance = this;
    }

    private void Start()
    {
        submit = "Me|40|29|2019530";
        SubmitRanking(submit);
    }

    /// <summary>
    ///返回數(shù)據(jù)的集合 
    ///數(shù)據(jù)的格式為 每一行為一條數(shù)據(jù)
    ///例："Me|40|29|2019530" 
    /// </summary>
    /// <returns></returns>
    public List<string> LoadData()
    {
        // StreamingAssets目錄下的  黨員信息.xlsx文件的路徑：Application.streamingAssetsPath + "/黨員信息.xlsx" 
        FileStream fileStream = File.Open(Application.streamingAssetsPath + "/排行榜.xlsx", FileMode.Open, FileAccess.Read);
        IExcelDataReader excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
        // 表格數(shù)據(jù)全部讀取到result里
        DataSet result = excelDataReader.AsDataSet();

        // 獲取表格有多少列 
        int columns = result.Tables[0].Columns.Count;
        // 獲取表格有多少行 
        int rows = result.Tables[0].Rows.Count;

        //獲取表格的行數(shù) -1去掉第一行
        //testRow = rows - 1;

        //Debug.Log("行" + rows);
        // 根據(jù)行列依次打印表格中的每個數(shù)據(jù) 

        List<string> excelDta = new List<string>();

        //第一行為表頭，不讀取
        for (int i = 1; i < rows; i++)
        {
            value = null;
            all = null;
            for (int j = 0; j < columns; j++)
            {
                // 獲取表格中指定行指定列的數(shù)據(jù) 
                value = result.Tables[0].Rows[i][j].ToString();
                //Debug.Log("value = " + value);
                if (value == "")
                {
                    continue;
                }
                all = all + value + "|";
            }
            if (all != null)
            {
                print(all);
                excelDta.Add(all);
            }
        }
        return excelDta;
    }

    /// <summary>
    /// list內(nèi)容格式
    /// "Me|40|29|2019530" 
    /// </summary>
    /// <param name="newList"></param>
    public void WriteExcel(List<string> newList)
    {
        //自定義excel的路徑

        string path = Application.streamingAssetsPath + "/排行榜.xlsx";
        // print(Application.dataPath);

        FileInfo newFile = new FileInfo(path);

        if (newFile.Exists)
        {
            //創(chuàng)建一個新的excel文件

            newFile.Delete();

            newFile = new FileInfo(path);
        }

        //通過ExcelPackage打開文件
        using (ExcelPackage package = new ExcelPackage(newFile))
        {
            //在excel空文件添加新sheet

            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("message");
            //添加列名

            worksheet.Cells[1, 1].Value = "playerName";

            worksheet.Cells[1, 2].Value = "accuracy";

            worksheet.Cells[1, 3].Value = "usedTime";

            worksheet.Cells[1, 4].Value = "rankingTime";

            for (int i = 0; i < newList.Count; i++)
            {
                string[] messages = newList[i].Split('|'); //趙一|黨員|1年|趙一.png| 
                string itemName = messages[0];
                string itemWork = messages[1];
                string itemYear = messages[2];
                string imageName = messages[3];
                //添加一行數(shù)據(jù)

                int num = i + 2;

                worksheet.Cells["A" + num].Value = itemName;

                worksheet.Cells["B" + num].Value = itemWork;

                worksheet.Cells["C" + num].Value = itemYear;

                worksheet.Cells["D" + num].Value = imageName;
            }

            //保存excel
            package.Save();
            print("重寫完成");
        }
    }

    /// <summary>
    /// tmpSubmit - 游戲結(jié)束輸入
    /// </summary>
    public void SubmitRanking(string tmpSubmit)
    {
        startRanking = LoadData();

        Debug.Log(startRanking.Count);
        //Debug.Log("Excel:"+startRanking.Count);
        //Debug.Log("EndExcel:"+startRanking[startRanking.Count-1]);
        if (startRanking.Count == 8)
        {
            if (FloatReckonRatio(startRanking[startRanking.Count - 1]) < FloatReckonRatio(tmpSubmit))
            {
                startRanking[startRanking.Count - 1] = tmpSubmit;
                ListSort(startRanking);
                //Debug.Log(startRanking[startRanking.Count-1]);
                //把最后一面頂替掉然后進行排序
                Debug.Log("進入排行榜成功");
            }
            else
            {
                Debug.Log("進入排行榜失敗");
            }

            Debug.Log("有八位數(shù)據(jù)");
        }
        else
        {
            startRanking.Add(tmpSubmit);
            ListSort(startRanking);
            Debug.Log("未夠十位數(shù)");
        }

        startRanking = LoadData();
        for (int i = 0; i < startRanking.Count; i++)
        {
            Debug.Log(startRanking[i]);
        }
    }

    /// <summary>
    /// 計算是 占比值 玩家分數(shù)
    /// </summary>
    /// <param name="tmpFormerRan"></param>
    /// <returns></returns>
    public float FloatReckonRatio(string tmpFormerRan)
    {
        float ratioFloat = 0;

        for (int i = 0; i < tmpFormerRan.Length; i++)
        {
            string[] tmpString = tmpFormerRan.Split('|');

            float accuracy = float.Parse(tmpString[1]) / 100f;
            float usedTime = 1 / float.Parse(tmpString[2]);
            float total = accuracy + usedTime;

            ratioFloat = total;
        }
        return ratioFloat;
    }

    /// <summary>
    /// 讓List進行重新排序排序
    /// </summary>
    /// <param name="tmpList"></param>
    public List<string> ListSort(List<string> tmpList)
    {
        List<string> _tmpData = new List<string>();

        //數(shù)據(jù)至少有兩個 才開始進行排序
        if (tmpList.Count > 1)
        {
            _tmpData = tmpList;
            for (int i = 0; i < _tmpData.Count; i++)
            {
                for (int j = 0; j < _tmpData.Count - 1 - i; j++)
                {
                    if (FloatReckonRatio(_tmpData[j]) < FloatReckonRatio(_tmpData[j + 1]))
                    {
                        string data = _tmpData[j];
                        _tmpData[j] = _tmpData[j + 1];
                        _tmpData[j + 1] = data;
                    }
                }
            }
        }
        else
        {
            _tmpData = tmpList;
        }

        //讀取重新建表
        WriteExcel(_tmpData);
        return _tmpData;
    }
}

讀取Excel的百度網(wǎng)盤路徑D文件:
鏈接：https://pan.baidu.com/s/1VrBqEdUDsiP_Z3pGi0CqOg
提取碼：t2lt

為避免打包exe后讀取不到excel數(shù)據(jù)，須將
Unity\Editor\Data\Mono\lib\mono\unity目錄下的一系列i18n相關(guān)dll導(dǎo)入項目Plugins文件夾中。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容