Needless to say, the operation of Excel tables requires the introduction of third-party libraries. First, we need to introduce Excel.dll and ICSharpCode.SharpZipLib.dll, both of which can be found online; then we need to introduce System.Data.dll, which can be found under the Editor Data Mono lib mono folder under the installation path of Unity3D.

The code is as follows:

 1 using Excel;
 2 using System.Data;
 3 using System.IO;
 4 using UnityEngine;
 5 
 6 public class Test : MonoBehaviour 
 7 {
 8     #region -- Definition of variables
 9 
10     #endregion
11 
12     #region -- System function
13     private void Start()
14     {
15         DataRowCollection _dataRowCollection = ReadExcel(Application.streamingAssetsPath + "/Student Information.xlsx");
16         //Here the loop starts from 1, because the first row is occupied by the header. Therefore, the specific analysis of data needs to be determined according to the specific circumstances.
17         for (int i = 1; i < _dataRowCollection.Count; i++)
18         {
19             Debug.Log("Student number:" + _dataRowCollection[i][0] + "--" + "Full name:" + _dataRowCollection[i][1] + "--" + "Age:" + _dataRowCollection[i][2]);
20         }
21     }
22     #endregion
23 
24     #region -- Custom function
25     /// <summary>
26     /// read Excel Tables and returns one DataRowCollection object
27     /// </summary>
28     /// <param name="_path">Excel Table path</param>
29     /// <param name="_sheetIndex">Readable Sheet Indexes. Excel There are many in the table. Sheet Of</param>
30     /// <returns></returns>
31     private static DataRowCollection ReadExcel(string _path, int _sheetIndex = 0)
32     {
33         FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read, FileShare.Read);
34         //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);//read Excel 1997-2003 Edition
35         IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);//Read 2007 and later versions
36         DataSet result = excelReader.AsDataSet();
37         return result.Tables[_sheetIndex].Rows;
38     }
39     /// <summary>
40     /// read Excel Tables and returns one DataRowCollection object
41     /// </summary>
42     /// <param name="_path">Excel Table path</param>
43     /// <param name="_sheetIndex">Readable Sheet Name. Excel There are many in the table. Sheet Of</param>
44     /// <returns></returns>
45     private static DataRowCollection ReadExcel(string _path, string _sheetName)
46     {
47         FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read, FileShare.Read);
48         //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);//read Excel 1997-2003 Edition
49         IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);//Read 2007 and later versions
50         DataSet result = excelReader.AsDataSet();
51         return result.Tables[_sheetName].Rows;
52     }
53     #endregion
54 
55 }

The operation results are as follows:

It should be noted here that according to the different versions of Excel tables, the methods used are not the same. I also have comments in the code. Just take a look. And Sheet, when we read, we can read by index or name. I also wrote the overload method.

If you write like this and run after publication, you may report errors. Then we need to introduce a third-party library. Go to Editor Data Mono lib mono unity under the Unity3D installation path and find all the class libraries that start with I18N and import them into Unity. Then we will not report errors, as follows:

The reading function of Excel table is solved. How can we generate an Excel table and write data? At this time, we need to import a class library called EPPlus.dll, which is also available on the Internet. You can download it by yourself.

The code is as follows:

 1 private void Start()
 2     {
 3         string _filePath = Application.streamingAssetsPath + "/Student Information 2.xlsx";
 4         string _sheetName = "details";
 5 
 6         FileInfo _excelName = new FileInfo(_filePath);
 7         if (_excelName.Exists)
 8         {
 9             //Delete the old file and create a new one excel Documentation.
10             _excelName.Delete();
11             _excelName = new FileInfo(_filePath);
12         }
13 
14         //adopt ExcelPackage Open the file
15         using (ExcelPackage package = new ExcelPackage(_excelName))
16         {
17             //stay excel Empty File Added New sheet，And set the name.
18             ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(_sheetName);
19 
20             //Add column names
21             worksheet.Cells[1, 1].Value = "Student ID";
22             worksheet.Cells[1, 2].Value = "Full name";
23             worksheet.Cells[1, 3].Value = "Gender";
24  
25             //Add a row of data
26             worksheet.Cells[2, 1].Value = 100001;
27             worksheet.Cells[2, 2].Value = "Zhang San";
28             worksheet.Cells[2, 3].Value = "male";
29 
30             //Add a row of data
31             worksheet.Cells[3, 1].Value = 100002;
32             worksheet.Cells[3, 2].Value = "Hammer";
33             worksheet.Cells[3, 3].Value = "female";
34 
35             //Add a row of data
36             worksheet.Cells[4, 1].Value = 120033;
37             worksheet.Cells[4, 2].Value = "Saw";
38             worksheet.Cells[4, 3].Value = "male";
39 
40             //Preservation excel
41             package.Save();
42         }
43     }

The operation results are as follows:

This is roughly how Excel tables are read and written. But I do not recommend that you read the Excel table directly, because the Excel table contains too much format information, it is best to save the Excel table as a plain text CSV file to read again, about the reading of CSV files, there is a lot of online, I have time to write one myself.