Unity 导入读取Excel表数据

先给出给出Excel DLL链接：https://download.csdn.net/download/qq_40120946/11827526

先把dell导进去

导出Excel为了打开自定义文件夹，我使用standaloneFileBrowser插件：https://download.csdn.net/download/qq_40120946/11827535

读取Excel核心代码部分：

  /// <summary>
    /// 读取EXCEL配置表
    /// </summary>
    /// <returns></returns>
    private void ReadExcelConfig(string outPutDir, out object[,] excelArray, out Dictionary<string, int> keyValues)
    {
        excelArray = new object[1, 1];
        //保存表第一行名字的字典
        keyValues = new Dictionary<string, int>();
        try
        {
            FileStream stream = File.Open(outPutDir, FileMode.Open, FileAccess.Read);
            IExcelDataReader excelDataReader = ExcelReaderFactory.CreateReader(stream);
            DataSet result = excelDataReader.AsDataSet();
            //获取第几个表
            DataTable dataTable = result.Tables[0];
            int columns = dataTable.Columns.Count;
            int rows = dataTable.Rows.Count;

            //二维数组存放数据
            excelArray = new object[rows, columns];
            for (int i = 0; i < rows; i++)
            {
                for (int j = 0; j < columns; j++)
                {
                    object value = dataTable.Rows[i][j];
                    if (value != null)
                    {
                        if (i == 0 && !string.IsNullOrEmpty(value.ToString()) && !keyValues.ContainsKey(value.ToString()))
                        {
                            keyValues.Add(value.ToString(), j);
                        }
                        else
                            excelArray[i, j] = value;
                    }
                    else
                    {
                        excelArray[i, j] = "";
                    }
                }
            }
            result.Clear();
            // excelDataReader.Close();
            stream.Close();
        }
        catch (Exception e)
        {
            Debug.Log(e.Message);
        }
    }

写入Excel表核心部分：

  private static void SaveStudentExcel(string path)
    {
        if (path != null && path.Length > 1)
        {
            FileInfo Info = new FileInfo(path);
            if (Info.Exists)
            {
                Info.Delete();
                Info = new FileInfo(path);
            }
            using (ExcelPackage pakeage = new ExcelPackage(Info))
            {
                ExcelWorksheet work = pakeage.Workbook.Worksheets.Add("Sheet1");
                for (int i = 0; i < TitleName.Count; i++)
                {
                    work.Cells[1, i + 1].Value = TitleName[i];
                }
                for (int i = 0; i < StudentShow.Count; i++)
                {
                    work.Cells["A" + (i + 2)].Value = i + 1;
                    work.Cells["B" + (i + 2)].Value = StudentShow[i].id;
                    work.Cells["C" + (i + 2)].Value = StudentShow[i].name;
                    work.Cells["D" + (i + 2)].Value = StudentShow[i].age;
                    work.Cells["E" + (i + 2)].Value = StudentShow[i].grade;
                    work.Cells["F" + (i + 2)].Value = StudentShow[i].score;
                }
                pakeage.Save();
            }
        }
    }

 

工具类（读取Excel表）ReadExcelTool：

public class ReadExcelTool {

    public ReadExcelTool()
    {

    }

    /// <summary>
    /// 学生信息
    /// </summary>
    public List<Student> ReadStudentInfoConfig
    {
        get
        {
            string path = Application.streamingAssetsPath + "/ExcelRead/学生信息表.xlsx";

            return GetStudentInfos(path);
        }
    }

    /// <summary>
    /// 读取学生信息excel配置表
    /// </summary>
    /// <returns></returns>
    private List<Student> GetStudentInfos(string outPutDir)
    {
        List<Student> students = new List<Student>();
        try
        {
            object[,] excelArray = new object[1, 1];
            Dictionary<string, int> keyValues = new Dictionary<string, int>();
            ReadExcelConfig(outPutDir, out excelArray, out keyValues);

            int rows = excelArray.GetLength(0);
            for (int i = 1; i < rows; i++)
            {
                Student student = new Student();
                student.id = excelArray[i, keyValues["编号"]].ToString();
                student.name = excelArray[i, keyValues["姓名"]].ToString();
                student.age = excelArray[i, keyValues["年龄"]].ToString();
                student.grade = excelArray[i, keyValues["班级"]].ToString();
                student.score = excelArray[i, keyValues["分数"]].ToString();

                students.Add(student);
            }
        }
        catch (Exception e)
        {
            Debug.Log(e.Message);
        }

        return students;
    }

    /// <summary>
    /// 读取EXCEL配置表
    /// </summary>
    /// <returns></returns>
    private void ReadExcelConfig(string outPutDir, out object[,] excelArray, out Dictionary<string, int> keyValues)
    {
        excelArray = new object[1, 1];
        //保存表第一行名字的字典
        keyValues = new Dictionary<string, int>();
        try
        {
            FileStream stream = File.Open(outPutDir, FileMode.Open, FileAccess.Read);
            IExcelDataReader excelDataReader = ExcelReaderFactory.CreateReader(stream);
            DataSet result = excelDataReader.AsDataSet();
            //获取第几个表
            DataTable dataTable = result.Tables[0];
            int columns = dataTable.Columns.Count;
            int rows = dataTable.Rows.Count;

            //二维数组存放数据
            excelArray = new object[rows, columns];
            for (int i = 0; i < rows; i++)
            {
                for (int j = 0; j < columns; j++)
                {
                    object value = dataTable.Rows[i][j];
                    if (value != null)
                    {
                        if (i == 0 && !string.IsNullOrEmpty(value.ToString()) && !keyValues.ContainsKey(value.ToString()))
                        {
                            keyValues.Add(value.ToString(), j);
                        }
                        else
                            excelArray[i, j] = value;
                    }
                    else
                    {
                        excelArray[i, j] = "";
                    }
                }
            }
            result.Clear();
            // excelDataReader.Close();
            stream.Close();
        }
        catch (Exception e)
        {
            Debug.Log(e.Message);
        }
    }
}

工具类（导出Excel表）WriteExcelTool：
 

public static class WriteExcelTool
{
    public static List<string> TitleName = new List<string>();

    public static List<Student> StudentShow = new List<Student>();


    /// <summary>
    /// 协同单位信息表      maki      
    /// </summary>
    public static void WriteStudentExcel(List<string> Tile, List<Student> showContent)
    {
        StudentShow = showContent;
        TitleName = Tile;
        var extensions = new[] {
                new ExtensionFilter("xlsx", "xls"),
            };
        StandaloneFileBrowser.SaveFilePanelAsync("学生信息表", "", "学生信息表", extensions, SaveStudentExcel);
    }

    private static void SaveStudentExcel(string path)
    {
        if (path != null && path.Length > 1)
        {
            FileInfo Info = new FileInfo(path);
            if (Info.Exists)
            {
                Info.Delete();
                Info = new FileInfo(path);
            }
            using (ExcelPackage pakeage = new ExcelPackage(Info))
            {
                ExcelWorksheet work = pakeage.Workbook.Worksheets.Add("Sheet1");
                for (int i = 0; i < TitleName.Count; i++)
                {
                    work.Cells[1, i + 1].Value = TitleName[i];
                }
                for (int i = 0; i < StudentShow.Count; i++)
                {
                    work.Cells["A" + (i + 2)].Value = i + 1;
                    work.Cells["B" + (i + 2)].Value = StudentShow[i].id;
                    work.Cells["C" + (i + 2)].Value = StudentShow[i].name;
                    work.Cells["D" + (i + 2)].Value = StudentShow[i].age;
                    work.Cells["E" + (i + 2)].Value = StudentShow[i].grade;
                    work.Cells["F" + (i + 2)].Value = StudentShow[i].score;
                }
                pakeage.Save();
            }
        }
    }
}

 

测试代码：

public class Student
{
    public string id { get; set; }
    public string name { get; set; }
    public string age { get; set; }
    public string grade { get; set; }
    public string score { get; set; }
}
public class ExcelTest : MonoBehaviour {

    public Transform item;
    public RectTransform parent;

    private List<Student> students;

	// Use this for initialization
	void Start () {
        students = new List<Student>();
        ReadExcelTool excelReadAnd = new ReadExcelTool();
        students.AddRange(excelReadAnd.ReadStudentInfoConfig);
        Debug.Log(students.Count);
        if (students.Count <= 0) return;
        foreach (var s in students)
        {
            var obj = Instantiate(item, parent);
            obj.gameObject.SetActive(true);
            obj.GetChild(0).GetComponent<Text>().text = s.id;
            obj.GetChild(1).GetComponent<Text>().text = s.name;
            obj.GetChild(2).GetComponent<Text>().text = s.age;
            obj.GetChild(3).GetComponent<Text>().text = s.grade;
            obj.GetChild(4).GetComponent<Text>().text = s.score;
        }
        //导出EXcel表
        WriteExcelTool.WriteStudentExcel(new List<string>() { "序号", "编号", "姓名", "年龄", "班级","分数" }, students);
    }
	
}

 

显示效果如下：

 

注意：1、unity运行时不要打开excel表否则会读取不到

2、IExcelDataReader 是否要关闭问题，这个原因我没找到，在给定的项目中，容易闪退，所以我屏蔽了但没影响