首先需要下载解析的库 EPPlus, Excel, ICSharpCode.SharpZipLib
下载链接:
https://download.csdn.net/download/weixin_46472622/87238048
使用方法
我的Excel 表格是这样的,每一列有一个关键词
我用一个结构体对象来表示
public struct ExaminationQuestions
{
public string equipmentType;//设备类型
public string questionType;//题目类型
public string index;//题目编号
public string questionPattern;//题目模式
public string questionDescription; //题目描述
public string questionOption;//题目选项
public string currentOption;//正确选项
public string explain;//说明
}
读取的方法
/// <summary>
/// 读取Excel 表格
/// </summary>
/// <param name="path">表格路径</param>
/// <returns></returns>
public static DataSet ReadExcel(string path)
{
IExcelDataReader excelReader = null;
FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.Read);
var str = path.Split('.');
if(str[1]=="xlsx")
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if(str[1]=="xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
// CreateOpenXmlReader用于读取Excel2007版本及以上的文件
DataSet result = excelReader.AsDataSet();
stream.Close();
excelReader.Close();
return result;
}
/// <summary>
/// 加载表格的数据并转换为对象
/// </summary>
/// <param name="path">表格路径</param>
/// <returns>返回一个结构题对象链表</returns>
public static List<ExaminationQuestions> Load(string path)
{
DataSet resultds = ReadExcel(path);//调用读取的方法
int column = resultds.Tables[0].Columns.Count;
int row = resultds.Tables[0].Rows.Count;
Debug.LogWarning(column + " " + row);//行,列
List<ExaminationQuestions> _data = new List<ExaminationQuestions>();
for (int i =1; i < row; i++)
{
ExaminationQuestions temp_data;
temp_data.equipmentType = resultds.Tables[0].Rows[i][0].ToString();
temp_data.questionType = resultds.Tables[0].Rows[i][1].ToString();
temp_data.index = resultds.Tables[0].Rows[i][2].ToString();
temp_data.questionPattern = resultds.Tables[0].Rows[i][3].ToString();
temp_data.questionDescription = resultds.Tables[0].Rows[i][4].ToString();
temp_data.questionOption = resultds.Tables[0].Rows[i][5].ToString();
temp_data.currentOption = resultds.Tables[0].Rows[i][6].ToString();
temp_data.explain = resultds.Tables[0].Rows[i][7].ToString();
Debug.Log(temp_data.equipmentType + " " + temp_data.questionType+ " "+ temp_data.index + " " + temp_data.questionPattern + " " + temp_data.questionDescription + " " + temp_data.questionOption + " " + temp_data.currentOption + " " + temp_data.explain);
_data.Add(temp_data);
}
return _data;
}
}
全部代码,以及调用:
using Excel;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using UnityEngine;
public class XLSXConvert
{
void Start()
{
// 这里设置需要读取的文件的路径
string FilePath = Application.streamingAssetsPath + "/ExaminationQuestions.xlsx";
Load(FilePath);
}
/// <summary>
/// 读取Excel 表格
/// </summary>
/// <param name="path">表格路径</param>
/// <returns></returns>
public static DataSet ReadExcel(string path)
{
IExcelDataReader excelReader = null;
FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.Read);
var str = path.Split('.');
if(str[1]=="xlsx")
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if(str[1]=="xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
// CreateOpenXmlReader用于读取Excel2007版本及以上的文件
DataSet result = excelReader.AsDataSet();
stream.Close();
excelReader.Close();
return result;
}
/// <summary>
/// 加载表格的数据并转换为对象
/// </summary>
/// <param name="path">表格路径</param>
/// <returns>返回一个结构题对象链表</returns>
public static List<ExaminationQuestions> Load(string path)
{
DataSet resultds = ReadExcel(path);//调用读取的方法
int column = resultds.Tables[0].Columns.Count;
int row = resultds.Tables[0].Rows.Count;
Debug.LogWarning(column + " " + row);//行,列
List<ExaminationQuestions> _data = new List<ExaminationQuestions>();
for (int i =1; i < row; i++)
{
ExaminationQuestions temp_data;
temp_data.equipmentType = resultds.Tables[0].Rows[i][0].ToString();
temp_data.questionType = resultds.Tables[0].Rows[i][1].ToString();
temp_data.index = resultds.Tables[0].Rows[i][2].ToString();
temp_data.questionPattern = resultds.Tables[0].Rows[i][3].ToString();
temp_data.questionDescription = resultds.Tables[0].Rows[i][4].ToString();
temp_data.questionOption = resultds.Tables[0].Rows[i][5].ToString();
temp_data.currentOption = resultds.Tables[0].Rows[i][6].ToString();
temp_data.explain = resultds.Tables[0].Rows[i][7].ToString();
Debug.Log(temp_data.equipmentType + " " + temp_data.questionType+ " "+ temp_data.index + " " + temp_data.questionPattern + " " + temp_data.questionDescription + " " + temp_data.questionOption + " " + temp_data.currentOption + " " + temp_data.explain);
_data.Add(temp_data);
}
return _data;
}
}
public struct ExaminationQuestions
{
public string equipmentType;//设备类型
public string questionType;//题目类型
public string index;//题目编号
public string questionPattern;//题目模式
public string questionDescription; //题目描述
public string questionOption;//题目选项
public string currentOption;//正确选项
public string explain;//说明
}
如果是打包PC端的exe,需要将编辑器下的I18N导入工程,打包就可以成功解析了。
(打包方式为mono的可以,为IL2Cpp的不行)2019.4.25f1c1\Editor\Data\MonoBleedingEdge\lib\mono\unity
这样就可以成功解析,如果是IL2cpp要解析Excel表格,可以参考这篇文章来源:https://www.toymoban.com/news/detail-520888.html
[Unity]FlexReader插件读取excel解决IL2Cpp与I18N打包冲突的问题_DAGUNIANGZHOU的博客-CSDN博客文章来源地址https://www.toymoban.com/news/detail-520888.html
到了这里,关于【Unity】用Excel库读取Excel表格(.xlsx或者.xls)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!