Unity读写Excel表格数据

在项目研发时需要加载excel表来进行数据读取，将excel的动态库文件导入unity plugins文件夹中。excel动态库文件下载链接：https://download.csdn.net/download/qq_34818497/13457567

具体代码如下：

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

public class ExcelManager
{
    /// <summary>
    /// 读取CSV文件中的所有数据
    /// </summary>
    private Dictionary<int, List<string>> ReadCSV(string _path, int _titleIndex)
    {
        Debug.Log("load csv file from " + _path);

        Dictionary<int, List<string>> csvData = new Dictionary<int, List<string>>();

        StreamReader sr = null;
        try
        {
            sr = File.OpenText(_path);
            //Debug.Log("File Find in " + _path);

            string line = null;
            int index = 0;
            while ((line = sr.ReadLine()) != null)   //按行读取
            {
                List<string> strs = new List<string>();
                //Debug.Log(line.Split(',')[0]);
                for (int i = 0; i < line.Split(',').Length; i++)
                {
                    strs.Add(line.Split(',')[i]);
                }
                csvData.Add(index, strs);

                index++;
            }
            sr.Close();
            sr.Dispose();
        }
        catch
        {
            Debug.LogError("File cannot find file " + _path);
        }

        return ColToRow(csvData, _titleIndex);
    }

    /// <summary>
    /// 将行列方式存储的数据转化为列行存储的数据(行列互换),方便取数据
    /// </summary>
    /// <param name="datas">通过StreamReader读取到的excel的行列存储的数据</param>
    /// <param name="_titleIndex">excel中第几行可以表示其列数</param>
    /// <returns></returns>
    private Dictionary<int, List<string>> ColToRow(Dictionary<int, List<string>> datas, int _titleIndex)
    {
        Dictionary<int, List<string>> temp = new Dictionary<int, List<string>>();
        for (int col = 0; col < datas[_titleIndex].Count; col++)
        {
            List<string> colData = new List<string>();
            for (int row = 0; row < datas.Count; row++)
            {
                colData.Add(datas[row][col]);
            }
            temp.Add(col, colData);
        }

        return temp;
    }

    /// <summary>
    /// 读取 Excel 表并返回一个 DataRowCollection 对象
    /// </summary>
    /// <param name="_path">Excel 表路径</param>
    /// <param name="_sheetIndex">读取的 Sheet 索引。Excel 表中是有多个 Sheet 的</param>
    /// <returns></returns>
    public DataRowCollection ReadExcel(string _path, int _sheetIndex = 0)
    {
        try
        {
            FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read, FileShare.Read);
            //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            DataSet result = excelReader.AsDataSet();

            return result.Tables[_sheetIndex].Rows;
        }
        catch (Exception e)
        {
            Debug.LogError("读取Excel文件出错：" + e);

            return null;
        }
    }

    /// <summary>
    /// 读取 Excel 表并返回一个 DataRowCollection 对象
    /// </summary>
    /// <param name="_path">Excel 表路径</param>
    /// <param name="_sheetIndex">读取的 Sheet 名称。Excel 表中是有多个 Sheet 的</param>
    /// <returns></returns>
    public DataRowCollection ReadExcel(string _path, string _sheetName)
    {
        try
        {
            FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read, FileShare.Read);
            //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);//读取 Excel 1997-2003版本
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);//读取 2007及以后的版本
            DataSet result = excelReader.AsDataSet();
            return result.Tables[_sheetName].Rows;
        }
        catch (Exception e)
        {
            Debug.LogError("读取Excel文件出错：" + e);

            return null;
        }
    }
}

注意：有些excel表格数据因为格式问题读取不到，函数报错。此时需要用到上面的ReadCSV函数，再手动将xls或者xlsx格式的excel表转化为csv格式的表，最后将csv格式的表格的编码格式转化为utf8，即可读取。