基于Unity实现Excel解析的学生登陆系统

基于Unity实现Excel解析的学生登陆系统

工程源文件.git

!：

之前曾经尝试使用Unity2019.4.8.f1开发发现其无法正确解析Excel，后又换成2017.2.0f3即可正常解析
关于Excel解析部分我稍后再说。

Part1
既然是学生系统所以我们需要先想好怎么存数据。
其实很简单
一个序列化student类就行

[System.Serializable]
public class Student
{public string username;//用户名public string password;//密码public string name;//姓名
}

我这里是用一个字典存学生信息
Dic<用户名,Pair<密码，姓名>> StudentInfoDic;

public static Dictionary<string, KeyValuePair<string, string>> StudentInfoDic = new Dictionary<string, KeyValuePair<string, string>>();

使用字典存这种结构会使得读写的达到O(1),(虽然说不太确定c#的dictinary是hash还是红黑树实现)

Part2
如果载入文件

使用OpenFile类

using UnityEngine;
using System.Collections;
using System;
using System.Runtime.InteropServices;[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Auto)]
public class OpenFileName
{public int structSize = 0;public IntPtr dlgOwner = IntPtr.Zero;public IntPtr instance = IntPtr.Zero;public String filter = null;public String customFilter = null;public int maxCustFilter = 0;public int filterIndex = 0;public String file = null;public int maxFile = 0;public String fileTitle = null;public int maxFileTitle = 0;public String initialDir = null;public String title = null;public int flags = 0;public short fileOffset = 0;public short fileExtension = 0;public String defExt = null;public IntPtr custData = IntPtr.Zero;public IntPtr hook = IntPtr.Zero;public String templateName = null;public IntPtr reservedPtr = IntPtr.Zero;public int reservedInt = 0;public int flagsEx = 0;
}
public class LocalDialog
{//链接指定系统函数       打开文件对话框[DllImport("Comdlg32.dll", SetLastError = true, ThrowOnUnmappableChar = true, CharSet = CharSet.Auto)]public static extern bool GetOpenFileName([In, Out] OpenFileName ofn);public static bool GetOFN([In, Out] OpenFileName ofn){return GetOpenFileName(ofn);}//链接指定系统函数        另存为对话框[DllImport("Comdlg32.dll", SetLastError = true, ThrowOnUnmappableChar = true, CharSet = CharSet.Auto)]public static extern bool GetSaveFileName([In, Out] OpenFileName ofn);public static bool GetSFN([In, Out] OpenFileName ofn){return GetSaveFileName(ofn);}
}

客户端只需要简单的调用

openFileName.structSize = Marshal.SizeOf(openFileName);// 图片文件(*.jpg *.png) *.jpg; *.pngopenFileName.filter = "Excel文件(*.xls *.xlsx)*.xls; *.xlsx;";openFileName.file = new string(new char[256]);openFileName.maxFile = openFileName.file.Length;openFileName.fileTitle = new string(new char[64]);openFileName.maxFileTitle = openFileName.fileTitle.Length;openFileName.initialDir = Application.streamingAssetsPath.Replace('/', '\');//默认路径openFileName.title = "窗口标题";openFileName.flags = 0x00080000 | 0x00001000 | 0x00000800 | 0x00000008;if (LocalDialog.GetSaveFileName(openFileName)){text导入文件.text = openFileName.file;//string Savepath = Path.GetDirectoryName(openFileName.file);//Process.Start(openFileName.file);}

就能获取文件了
其实 openFileName.filter 是文件类型可选项
*.xls; *.xlsx; 这里是可以用xls和xlsx类型

Part3
UI适配
大概设计是用一个UIManger管理所有UI以及他们的事件
这里使用unity的UGUI系统
用到Button，Text，InputField，Image

Button用来监听点击事件：犹如选择文件，导入文件，录入，删除等
Text 是所有非输入类型文本
InputField 是输入类型文本 例如输入密码，输入学号
Image是背景图片文件
这些比较基础不多做赘述

然后稍微聊一下输入格式问题，虽然说正则表达式应该会简单很多【但是我是菜狗不会】
由于某些方法经常使用，故封装成方法代码如下：

private bool CheckPassword(string password, bool isTiqu = false){//AB1234a@1,aF1#,2w3E*,2Ee345676//UnityEngine.Debug.LogError(password);string[] passwords = password.Split(',');foreach (string ps in passwords){if (ps.Length >= 6 && ps.Length <= 12){bool hasa = false, hasnu = false, hasA = false, hasTx = false;foreach (char c in ps){if (c >= 'a' && c <= 'z') hasa = true;else if (c >= 'A' && c <= 'Z') hasA = true;else if (c >= '0' && c <= '9') hasnu = true;else if (c == '$' || c == '#' || c == '@') hasTx = true;}if (hasa && hasnu && hasA && hasTx){if (isTiqu){_提取密码.text = ps;}else _密码.text = ps;return false;}}}return true;}

只需要获取InputField的text就行了，然后和静态字典去对接核对就能保证录入信息是否正确

Part4
Excel如何处理

首先确保在版本大概在Unity2017的情况下【我之前试过2019，完全用不了甚至会项目暴死】，导入这些库

需要加入库文件 Excel.dll 和ICSharpCode.SharpZipLib库文件，官方链接 /
备注：
1.你的unity版本是多少，去对应的安装目录中取dll
2.System.Data.dll 在D:Program FilesUnity2017.2EditorDataMonolibmono2.0
3.I18N开头的dll 在 D:Program FilesUnity2017.2EditorDataMonolibmonounity

先读取Excel

 FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);//读取Excel 2007版本及以上的文件 xlsx
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);//读取 Excel 1997-2003版本 xls

使用DataSet来处理Excel文件里的内容

DataSet result = excelReader.AsDataSet();

用完关闭 excelReader.Close();

然后将DataSet映射到Dic

 int columnNum = resultds.Tables[0].Columns.Count;int rowNum = resultds.Tables[0].Rows.Count;DataRowCollection collect = resultds.Tables[0].Rows;UIManger.StudentInfoDic.Clear();for (int i = 1; i < rowNum; i++){string s = "";for (int j = 1; j < columnNum; j++){s += collect[i][j].ToString() + "  ";}//UnityEngine.Debug.LogError( s);if (CheckPassword(collect[i][3].ToString())) continue;if (collect[i][1].ToString().Length != 10) continue;if (collect[i][2].ToString().Length < 2 || collect[i][2].ToString().Length > 4)continue;if (!UIManger.StudentInfoDic.ContainsKey(collect[i][1].ToString()))UIManger.StudentInfoDic.Add(collect[i][1].ToString(), new KeyValuePair<string, string>(collect[i][3].ToString(), collect[i][2].ToString()));//UnityEngine.Debug.LogError("ok  "+s);}

写的话

FileInfo newFile = new FileInfo(path);if (newFile.Exists){//创建一个新的excel文件newFile.Delete();newFile = new FileInfo(path);}//通过ExcelPackage打开文件using (ExcelPackage package = new ExcelPackage(newFile)){//在excel空文件添加新sheetExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetName);//添加列名worksheet.Cells[1, 1].Value = "";worksheet.Cells[1, 2].Value = "学号";worksheet.Cells[1, 3].Value = "姓名";worksheet.Cells[1, 4].Value = "密码";//添加一行数据int index = 2;foreach (var stu in UIManger.StudentInfoDic){worksheet.Cells[index, 1].Value = index - 1;worksheet.Cells[index, 2].Value = stu.Key;worksheet.Cells[index, 3].Value = stu.Value.Value;worksheet.Cells[index, 4].Value = stu.Value.Key;index++;}//保存excel*/package.Save();}

额虽然说我的代码很垃圾，不过我把我代码都发出来吧
OpenFile.cs

using Excel;
using OfficeOpenXml;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using UnityEngine;[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Auto)]
public class OpenFileName
{public int structSize = 0;public IntPtr dlgOwner = IntPtr.Zero;public IntPtr instance = IntPtr.Zero;public String filter = null;public String customFilter = null;public int maxCustFilter = 0;public int filterIndex = 0;public String file = null;public int maxFile = 0;public String fileTitle = null;public int maxFileTitle = 0;public String initialDir = null;public String title = null;public int flags = 0;public short fileOffset = 0;public short fileExtension = 0;public String defExt = null;public IntPtr custData = IntPtr.Zero;public IntPtr hook = IntPtr.Zero;public String templateName = null;public IntPtr reservedPtr = IntPtr.Zero;public int reservedInt = 0;public int flagsEx = 0;
}
public class LocalDialog
{//链接指定系统函数       打开文件对话框[DllImport("Comdlg32.dll", SetLastError = true, ThrowOnUnmappableChar = true, CharSet = CharSet.Auto)]public static extern bool GetOpenFileName([In, Out] OpenFileName ofn);public static bool GetOFN([In, Out] OpenFileName ofn){return GetOpenFileName(ofn);}//链接指定系统函数        另存为对话框[DllImport("Comdlg32.dll", SetLastError = true, ThrowOnUnmappableChar = true, CharSet = CharSet.Auto)]public static extern bool GetSaveFileName([In, Out] OpenFileName ofn);public static bool GetSFN([In, Out] OpenFileName ofn){return GetSaveFileName(ofn);}
}[System.Serializable]
public class Student
{public string username;public string password;public string name;
}public class DoExcel
{private static DataSet ReadExcel(string path){FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.Read);IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);//读取Excel 2007版本及以上的文件 xlsx// MyDebug.Log(excelReader.ToString());//IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);//读取 Excel 1997-2003版本 xlsif (excelReader == null){//  stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.Read);//  excelReader = ExcelReaderFactory.CreateBinaryReader(stream);//读取 Excel 1997-2003版本 xls}//    MyDebug.Log(excelReader.ToString());DataSet result = excelReader.AsDataSet();//  if (result == null)//  {//     Debug.Log("??");//      //  stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.Read);//  excelReader = ExcelReaderFactory.CreateBinaryReader(stream);//读取 Excel 1997-2003版本 xls//      Debug.LogError(excelReader);//        result = excelReader.AsDataSet();//     }//Debug.LogError(excelReader);//Debug.Log(result);excelReader.Close();return result;}private static bool CheckPassword(string password){//AB1234a@1,aF1#,2w3E*,2Ee345676//UnityEngine.Debug.LogError(password);string[] passwords = password.Split(',');foreach (string ps in passwords){if (ps.Length >= 6 && ps.Length <= 12){bool hasa = false, hasnu = false, hasA = false, hasTx = false;foreach (char c in ps){if (c >= 'a' && c <= 'z') hasa = true;else if (c >= 'A' && c <= 'Z') hasA = true;else if (c >= '0' && c <= '9') hasnu = true;else if (c == '$' || c == '#' || c == '@') hasTx = true;}if (hasa && hasnu && hasA && hasTx){return false;}}}return true;}public static bool Load(string path){//List<DepenceTableData> _data = new List<DepenceTableData>();DataSet resultds = ReadExcel(path);//Debug.LogError(resultds);if (resultds == null){return false;}if (resultds.Tables.Count == 0) return true;int columnNum = resultds.Tables[0].Columns.Count;int rowNum = resultds.Tables[0].Rows.Count;DataRowCollection collect = resultds.Tables[0].Rows;UIManger.StudentInfoDic.Clear();for (int i = 1; i < rowNum; i++){string s = "";for (int j = 1; j < columnNum; j++){s += collect[i][j].ToString() + "  ";}//UnityEngine.Debug.LogError( s);if (CheckPassword(collect[i][3].ToString())) continue;if (collect[i][1].ToString().Length != 10) continue;if (collect[i][2].ToString().Length < 2 || collect[i][2].ToString().Length > 4)continue;if (!UIManger.StudentInfoDic.ContainsKey(collect[i][1].ToString()))UIManger.StudentInfoDic.Add(collect[i][1].ToString(), new KeyValuePair<string, string>(collect[i][3].ToString(), collect[i][2].ToString()));//UnityEngine.Debug.LogError("ok  "+s);}return true;}/// <summary>/// 写入 Excel ; 需要添加 OfficeOpenXml.dll;/// </summary>/// <param name="excelName">excel文件名</param>/// <param name="sheetName">sheet名称</param>public static void WriteExcel(string path, string sheetName){//通过面板设置excel路径//string outputDir = EditorUtility.SaveFilePanel("Save Excel", "", "New Resource", "xlsx");//自定义excel的路径FileInfo newFile = new FileInfo(path);if (newFile.Exists){//创建一个新的excel文件newFile.Delete();newFile = new FileInfo(path);}//通过ExcelPackage打开文件using (ExcelPackage package = new ExcelPackage(newFile)){//在excel空文件添加新sheetExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetName);//添加列名worksheet.Cells[1, 1].Value = "";worksheet.Cells[1, 2].Value = "学号";worksheet.Cells[1, 3].Value = "姓名";worksheet.Cells[1, 4].Value = "密码";//添加一行数据int index = 2;foreach (var stu in UIManger.StudentInfoDic){worksheet.Cells[index, 1].Value = index - 1;worksheet.Cells[index, 2].Value = stu.Key;worksheet.Cells[index, 3].Value = stu.Value.Value;worksheet.Cells[index, 4].Value = stu.Value.Key;index++;}//保存excel*/package.Save();}}}

UIManger

/****************************************************Author:            龙之介CreatTime:    #CreateTime#Description:     Nothing
*****************************************************/using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using System;
using UnityEngine.UI;
using System.Linq;
using System.Runtime.InteropServices;
using System.IO;
using System.Diagnostics;
using Excel;
using System.Data;public class UIManger : MonoBehaviour
{public OpenFileName openFileName = new OpenFileName();public static Dictionary<string, KeyValuePair<string, string>> StudentInfoDic = new Dictionary<string, KeyValuePair<string, string>>();private Text text导入文件;private GameObject 请先导入csv;private GameObject 请先选择csv;private GameObject 导入成功;private GameObject 请关闭欲导入文件;private InputField _学号;private InputField _姓名;private InputField _密码;private Text _录入Tip;private InputField _提取学号;private InputField _提取密码;private Text _提取Tip;private bool IS导入 = false;private void Start(){导入成功 = transform.Find("导入成功").gameObject;请先导入csv = transform.Find("请先导入csv").gameObject;请先选择csv = transform.Find("请先选择csv").gameObject;请关闭欲导入文件 = transform.Find("请关闭欲导入文件").gameObject;导入成功.transform.Find("Close").GetComponent<UnityEngine.UI.Button>().onClick.AddListener(() => { Close(导入成功); });请关闭欲导入文件.transform.Find("Close").GetComponent<UnityEngine.UI.Button>().onClick.AddListener(() => { Close(请关闭欲导入文件); });请先导入ansform.Find("Close").GetComponent<UnityEngine.UI.Button>().onClick.AddListener(() => { Close(请先导入csv); });请先选择ansform.Find("Close").GetComponent<UnityEngine.UI.Button>().onClick.AddListener(() => { Close(请先选择csv); });Close(请先导入csv);Close(请先选择csv);transform.Find("导入").Find("btn导入文件").gameObject.GetComponent<UnityEngine.UI.Button>().onClick.AddListener(LoadStudentInfo);transform.Find("导入").Find("btn选择文件").gameObject.GetComponent<UnityEngine.UI.Button>().onClick.AddListener(SelectFile);transform.Find("btn查看所有学生信息").gameObject.GetComponent<UnityEngine.UI.Button>().onClick.AddListener(LookAtAllStudent);text导入文件 = transform.Find("导入").Find("text导入文件").gameObject.GetComponent<Text>();Transform _录入 = transform.Find("录入");_学号 = _录入.Find("InputField").GetComponent<InputField>();//_学号.text = "1901420132";_姓名 = _录入.Find("InputField1").GetComponent<InputField>();//_姓名.text = "找卡的";_密码 = _录入.Find("InputField2").GetComponent<InputField>();// _密码.text= "AB1234a@1,aF1#,2w3E*,2Ee345676";_录入Tip = _录入.Find("tip").GetComponent<Text>();_录入.Find("btn录入").gameObject.GetComponent<UnityEngine.UI.Button>().onClick.AddListener(LogIn);_录入.Find("btn删除").gameObject.GetComponent<UnityEngine.UI.Button>().onClick.AddListener(Delete);Transform _提取 = transform.Find("提取");_提取学号 = _提取.Find("InputField").GetComponent<InputField>();// _提取学号.text = "1901420132";_提取密码 = _提取.Find("InputField1").GetComponent<InputField>();// _提取密码.text = "AB1234a@1,aF1#,2w3E*,2Ee345676";_提取Tip = _提取.Find("tip").GetComponent<Text>();_提取.Find("btn显示姓名").gameObject.GetComponent<UnityEngine.UI.Button>().onClick.AddListener(LookAt);}private void LookAt(){if (!IS导入){Open(请先导入csv);return;}if (_提取学号.text.Length != 10){_提取 = "学号错误";return;}if (_提取密码.text.Length == 0){_提取 = "密码不能为空";return;}//UnityEngine.Debug.LogError(_提取学号.text +  "  " + _提取密码.text);if (CheckPassword(_提取密码.text, true)){_提取 = "密码错误";return;}if (StudentInfoDic.ContainsKey(_提取学号.text)){if (StudentInfoDic[_提取学号.text].Key != _提取密码.text){_提取 = "密码错误";}else{_提取 = StudentInfoDic[_提取学号.text].Value;}return;}_提取 = "学号未录入";}private bool CheckPassword(string password, bool isTiqu = false){//AB1234a@1,aF1#,2w3E*,2Ee345676//UnityEngine.Debug.LogError(password);string[] passwords = password.Split(',');foreach (string ps in passwords){if (ps.Length >= 6 && ps.Length <= 12){bool hasa = false, hasnu = false, hasA = false, hasTx = false;foreach (char c in ps){if (c >= 'a' && c <= 'z') hasa = true;else if (c >= 'A' && c <= 'Z') hasA = true;else if (c >= '0' && c <= '9') hasnu = true;else if (c == '$' || c == '#' || c == '@') hasTx = true;}if (hasa && hasnu && hasA && hasTx){if (isTiqu){_提取密码.text = ps;}else _密码.text = ps;return false;}}}return true;}private void Delete(){if (!IS导入){Open(请先导入csv);return;}if (_学号.text.Length != 10){_录入 = "学号错误";return;}if (_姓名.text.Length < 2){_录入 = "姓名不能为空";return;}if (_密码.text.Length == 0){_录入 = "密码不能为空";return;}//UnityEngine.Debug.LogError(_学号.text + "  " + _姓名.text + "  " + _密码.text);if (CheckPassword(_密码.text)){_录入 = "密码格式错误";return;}if (StudentInfoDic.ContainsKey(_学号.text)){if (StudentInfoDic[_学号.text].Key == _密码.text){if (StudentInfoDic[_学号.text].Value == _姓名.text){StudentInfoDic.Remove(_学号.text);DoExcel.WriteExcel(openFileName.file, "StudentInfo");_录入 = "删除成功";}else{_录入 = "姓名错误";}}else_录入 = "密码错误";}else{_录入 = "学号不存在";}}private void LogIn(){if (!IS导入){Open(请先导入csv);return;}if (_学号.text.Length != 10){_录入 = "学号错误";return;}if (_姓名.text.Length < 2){_录入 = "姓名不能为空";return;}if (_密码.text.Length == 0){_录入 = "密码不能为空";return;}//UnityEngine.Debug.LogError(_学号.text + "  " + _姓名.text + "  " + _密码.text);if (CheckPassword(_密码.text)){_录入 = "密码格式错误";return;}if (StudentInfoDic.ContainsKey(_学号.text)){_录入 = "学号已存在";return;}StudentInfoDic.Add(_学号.text, new KeyValuePair<string, string>(_密码.text, _姓名.text));_录入 = "录入成功";DoExcel.WriteExcel(openFileName.file, "StudentInfo");}private void Close(GameObject window){window.SetActive(false);}private void Open(GameObject window){window.SetActive(true);}private void SelectFile(){openFileName.structSize = Marshal.SizeOf(openFileName);// 图片文件(*.jpg *.png) *.jpg; *.pngopenFileName.filter = "Excel文件(*.xls *.xlsx)*.xls; *.xlsx;";openFileName.file = new string(new char[256]);openFileName.maxFile = openFileName.file.Length;openFileName.fileTitle = new string(new char[64]);openFileName.maxFileTitle = openFileName.fileTitle.Length;openFileName.initialDir = Application.streamingAssetsPath.Replace('/', '\');//默认路径openFileName.title = "窗口标题";openFileName.flags = 0x00080000 | 0x00001000 | 0x00000800 | 0x00000008;if (LocalDialog.GetSaveFileName(openFileName)){text导入文件.text = openFileName.file;//string Savepath = Path.GetDirectoryName(openFileName.file);//Process.Start(openFileName.file);}}private void LoadStudentInfo(){if (openFileName.file == null || openFileName.file.Length == 0){Open(请先选择csv);return;}string file = openFileName.file.Split('.')[1];if (file == "csv"){DataTable dataTable = DoSVC.ReadSVC(openFileName.file);DataRowCollection collect = dataTable.Rows;int rowNum = dataTable.Rows.Count;int columnNum = dataTable.Columns.Count;for (int i = 1; i < rowNum; i++){string s = "";for (int j = 1; j < columnNum; j++){s += collect[i][j].ToString() + "  ";}UnityEngine.Debug.LogError(s);if (CheckPassword(collect[i][3].ToString())) continue;if (collect[i][1].ToString().Length != 10) continue;if (collect[i][2].ToString().Length < 2 || collect[i][2].ToString().Length > 4)continue;if (!UIManger.StudentInfoDic.ContainsKey(collect[i][1].ToString()))UIManger.StudentInfoDic.Add(collect[i][1].ToString(), new KeyValuePair<string, string>(collect[i][3].ToString(), collect[i][2].ToString()));//UnityEngine.Debug.LogError("ok  "+s);}}else{if (!DoExcel.Load(openFileName.file)){Open(请关闭欲导入文件);return;}}Open(导入成功);IS导入 = true;DoExcel.WriteExcel(openFileName.file, "StudentInfo");}private void LookAtAllStudent(){if (StudentInfoDic.Count == 0){Open(请先导入csv);return;}foreach (var student in StudentInfoDic){UnityEngine.Debug.LogError("姓名: " + student.Value.Value + "  学号: " + student.Key + "  密码: " + student.Value.Key);}}/*private static void ReadExecl(string filePath){print(filePath);FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);DataSet result = excelReader.AsDataSet();//Tables[0] 下标0表示excel文件中第一张表的数据int columnNum = result.Tables[0].Columns.Count;int rowNum = result.Tables[0].Rows.Count;DataRowCollection collect = result.Tables[0].Rows;//根据excel的定义，第二行开始才是数据for (int i = 0; i < rowNum; i++){for (int j = 0; j < columnNum; j++){UnityEngine.Debug.LogError(collect[i][0].ToString());}//解析每列的数据/*item.itemId = uint.Parse(collect[i][0].ToString());item.itemName = collect[i][1].ToString();item.itemPrice = uint.Parse(collect[i][2].ToString());array[i - 1] = item;*///   }//  return array;//return ;// }
}