unity读取Excel表格保存到Sqlite数据库
1.读取Excel表格和保存sqlite数据库所用到的dll文件 下载链接:dll文件
最后如下图所示
废话不多说了,直接上代码吧
因为太长就折叠了
using System.Collections; using System.Collections.Generic; using UnityEngine; using Mono.Data.Sqlite; using System; using System.IO; using System.Data; using Excel; public class SQLiteDataBase { private SqliteConnection conn; // SQLite连接 private SqliteDataReader reader; private SqliteCommand command;// SQLite命令 private float timespeed = 0.001f; /// <summary> /// 执行SQL语句 公共方法 /// </summary> /// <param name="sqlQuery"></param> /// <returns></returns> public SqliteDataReader ExecuteQuery(string sqlQuery) { command = conn.CreateCommand(); command.CommandText = sqlQuery; reader = command.ExecuteReader(); return reader; } #region 打开/关闭数据库 /// <summary> /// 打开数据库 /// </summary> /// <param name="connectionString">@"Data Source = " + path</param> public SQLiteDataBase(string connectionString) { try { //构造数据库连接 conn = new SqliteConnection(connectionString); //打开数据库 conn.Open(); Debug.Log("打开数据库"); } catch (Exception e) { Debug.Log(e.Message); } } /// <summary> /// 关闭数据库连接 /// </summary> public void CloseSqlConnection() { if (command != null) { command.Dispose(); command = null; } if (reader != null) { reader.Dispose(); reader = null; } if (conn != null) { conn.Close(); conn = null; } Debug.Log("关闭数据库！"); } #endregion; #region 创建表单 /// <summary> /// 创建表单 第一种 /// </summary> /// <param name="name">表单名</param> /// <param name="col">字段</param> /// <param name="colType">类型</param> public void CreationMenu(string name, string[] col, string[] colType) { string query = "create table " + name + " (" + col[0] + " " + colType[0]; for (int i = 1; i < col.Length; ++i) { query += ", " + col[i] + " " + colType[i]; } query += ")"; command = new SqliteCommand(query, conn); command.ExecuteNonQuery(); } /// <summary> 第二种 区别第一种用了公共方法 原理应该是一样的 经测试都可以使用 /// 创建表 param name=表名 col=字段名 colType=字段类型 /// </summary> public SqliteDataReader CreateTable(string name, string[] col, string[] colType) { if (col.Length != colType.Length) { throw new SqliteException("columns.Length != colType.Length"); } string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0]; for (int i = 1; i < col.Length; ++i) { query += ", " + col[i] + " " + colType[i]; } query += ")"; return ExecuteQuery(query); } #endregion; #region 查询数据 /// <summary> /// 查询表中全部数据 param tableName=表名 /// </summary> public SqliteDataReader ReadFullTable(string tableName) { string query = "SELECT * FROM " + tableName; return ExecuteQuery(query); } /// <summary> /// 按条件查询数据 param tableName=表名 items=查询字段 col=查找字段 operation=运算符 values=内容 /// </summary> public SqliteDataReader SelectWhere(string tableName, string[] items, string[] col, string[] operation, string[] values) { if (col.Length != operation.Length || operation.Length != values.Length) { throw new SqliteException("col.Length != operation.Length != values.Length"); } string query = "SELECT " + items[0]; for (int i = 1; i < items.Length; ++i) { query += ", " + items[i]; } query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' "; for (int i = 1; i < col.Length; ++i) { query += " AND " + col[i] + operation[i] + "'" + values[i] + "' "; } return ExecuteQuery(query); } /// <summary> /// 查询表 /// </summary> public SqliteDataReader Select(string tableName, string col, string values) { string query = "SELECT * FROM " + tableName + " WHERE " + col + " = " + values; return ExecuteQuery(query); } public SqliteDataReader Select(string tableName, string col, string operation, string values) { string query = "SELECT * FROM " + tableName + " WHERE " + col + operation + values; return ExecuteQuery(query); } /// <summary> /// 升序查询 /// </summary> public SqliteDataReader SelectOrderASC(string tableName, string col) { string query = "SELECT * FROM " + tableName + " ORDER BY " + col + " ASC"; return ExecuteQuery(query); } /// <summary> /// 降序查询 /// </summary> public SqliteDataReader SelectOrderDESC(string tableName, string col) { string query = "SELECT * FROM " + tableName + " ORDER BY " + col + " DESC"; return ExecuteQuery(query); } /// <summary> /// 查询表行数 /// </summary> public SqliteDataReader SelectCount(string tableName) { string query = "SELECT COUNT(*) FROM " + tableName; return ExecuteQuery(query); } #endregion #region 插入数据 /// <summary> /// 插入数据 param tableName=表名 values=插入数据内容 /// 插入一条数据 /// </summary> public SqliteDataReader InsertInto(string tableName, string[] values) { string query = "INSERT INTO " + tableName + " VALUES ('" + values[0]; for (int i = 1; i < values.Length; ++i) { query += "', '" + values[i]; } query += "')"; return ExecuteQuery(query); } /// <summary> /// 插入数据 插入多条数据 /// </summary> 经测试这个方法是可用的 /// 因为我的数据有两万条运行卡着不动了,所以用协程时间控制一下 虽然慢但是不卡死,写到数据库中之后用数据库就好了 /// <param name="tableName">表名字</param> /// <param name="values">字典</param> /// <returns></returns> public IEnumerator InsertInto(string tableName, Dictionary<string, List<string>> values) { int ii = 0; foreach (var item in values) { string query = ""; string value = ""; foreach (var ite in item.Value) { value += "','" + ite; } query = "INSERT INTO " + tableName + " VALUES ('" + item.Key + value + "')"; //Debug.Log(query); command = conn.CreateCommand(); command.CommandText = query; command.ExecuteNonQuery(); Debug.Log("写入成功" + ii++); yield return new WaitForSeconds(timespeed); } Debug.Log("写入成功"); } #region 没测试过的 /// <summary> /// 插入数据 param tableName=表名 cols=插入字段 value=插入内容 /// </summary> public SqliteDataReader InsertIntoSpecific(string tableName, string[] cols, string[] values) { if (cols.Length != values.Length) { throw new SqliteException("columns.Length != values.Length"); } string query = "INSERT INTO " + tableName + "('" + cols[0]; for (int i = 1; i < cols.Length; ++i) { query += "', '" + cols[i]; } query += "') VALUES ('" + values[0]; for (int i = 1; i < values.Length; ++i) { query += "', '" + values[i]; } query += "')"; return ExecuteQuery(query); } /// <summary> /// 更新数据 param tableName=表名 cols=更新字段 colsvalues=更新内容 selectkey=查找字段（主键) selectvalue=查找内容 /// </summary> public SqliteDataReader UpdateInto(string tableName, string[] cols, string[] colsvalues, string selectkey, string selectvalue) { string query = "UPDATE " + tableName + " SET " + cols[0] + " = " + colsvalues[0]; for (int i = 1; i < colsvalues.Length; ++i) { query += ", " + cols[i] + " =" + colsvalues[i]; } query += " WHERE " + selectkey + " = " + selectvalue + " "; return ExecuteQuery(query); } #endregion #endregion #region 删除 /// <summary> /// 删除表 IF EXISTS判断表存不存在防止出错 已测试 /// </summary> public SqliteDataReader DeleteContents(string tableName) { string query = "DROP TABLE IF EXISTS " + tableName; Debug.Log("删除表成功"); return ExecuteQuery(query); } /// <summary> /// 删除数据 param tableName=表名 cols=字段 colsvalues=内容 /// </summary> public SqliteDataReader Delete(string tableName, string[] cols, string[] colsvalues) { string query = "DELETE FROM " + tableName + " WHERE " + cols[0] + " = " + colsvalues[0]; for (int i = 1; i < colsvalues.Length; ++i) { query += " or " + cols[i] + " = " + colsvalues[i]; } return ExecuteQuery(query); } #endregion } public class SQLiteDataBaseTion : MonoBehaviour { [Header("Excel表数据长度")] //表格一共有多少列数 最长的一个 public int tableint; public string[] fields, type; //字段\类型 [Header("数据库名字")] public string dbname; private SQLiteDataBase _SQLiteData; private SqliteDataReader reader; private string path; private string connectionString; public static Dictionary<string, List<string>> JDDateDic = new Dictionary<string, List<string>>();//机电数据 public static Dictionary<string, List<string>> OneCDateDic = new Dictionary<string, List<string>>();//一层数据 private void Awake() { fields = new string[tableint]; type = new string[tableint]; for (int i = 0; i < tableint; i++) { fields[i] = "sql" + i.ToString(); type[i] = "varchar"; } } // Start is called before the first frame update void Start() { //读取excel表格数据 ReadExcelClick("jiegou.xlsx", 0, OneCDateDic); path = Application.streamingAssetsPath + "/"+ dbname + ".db"; connectionString = @"Data Source = " + path; //创建数据库文件 存在就打开 CreateSQL(dbname); //创建表 //_SQLiteData.CreationMenu("jiegou", fields, type); //将数据插入数据库 //StartCoroutine(_SQLiteData.InsertInto("jiegou", OneCDateDic)); //删除表 //_SQLiteData.DeleteContents("jiegou"); } /// <summary> /// 创建数据库文件 /// </summary> /// <param name="sqlname">文件名字</param> public void CreateSQL(string sqlname) { if (!File.Exists(Application.streamingAssetsPath + "/" + sqlname + ".db")) { //不存在就创建 File.Create(Application.streamingAssetsPath + "/" + sqlname + ".db"); //创建之后再打开 _SQLiteData = new SQLiteDataBase(connectionString); } else { Debug.Log("已存在"); //打开数据库 _SQLiteData = new SQLiteDataBase(connectionString); } } /// 读取数据库某一行数据 "646c173c-7d14-47b0-80fe-53c1c8ce2b0e-0037044a" public List<string> SomeLine(string idname,out List <string >listidnames) { reader = _SQLiteData.ReadFullTable("jidian"); List<string> idname_ = new List<string>(); while (reader.Read()) { //Debug.Log(reader.GetString(reader.GetOrdinal("idname")));// reader.ToString(); if (reader.GetString(0).ToString() == idname) { for (int i = 0; i < reader.FieldCount; i++) { try { if (reader.GetString(i) != null) { Debug.Log(reader.GetString(i)); idname_.Add(reader.GetString(i)); } } catch (Exception e) { Debug.Log(e.Message); break; } } listidnames = idname_; return listidnames; } } listidnames = idname_; return listidnames; } //读取 Excel表格 void ReadExcelClick(string _name, int _num, Dictionary<string, List<string>> _Dic) { //1.打开文件，创建一个文件流操作对象 //FileStream fileStream = new FileStream(Application.streamingAssetsPath + "/" + "机电.xlsx", FileMode.Open, FileAccess.Read); FileStream fileStream = new FileStream(Application.streamingAssetsPath + "/" + _name, FileMode.Open, FileAccess.Read); //2.创建一个excel读取类 IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(fileStream); //方法1：读取 //while (reader.Read()) //{ // string name = reader.GetString(0); // string birth = reader.GetString(1); // string brief = reader.GetString(2); // Debug.Log("姓名:" + name + " --- " + "生卒:" + birth + " --- " + "简介：" + brief); //} //方法2：读取 DataSet result = reader.AsDataSet(); //获取行数 int rows = result.Tables[_num].Rows.Count; Debug.Log(rows); //获取列数 int column = result.Tables[_num].Columns.Count; for (int i = 0; i < rows; i++) { //获取i行的第一列数据 string name = result.Tables[_num].Rows[i][0].ToString(); List<string> _S = new List<string>(); for (int j = 1; j < column; j++) { string birth = result.Tables[_num].Rows[i][j].ToString(); _S.Add(birth); } if (_Dic.ContainsKey(name)) { continue; } _Dic.Add(name, _S); } Debug.Log(_Dic.Count); } private void OnDisable() { _SQLiteData.CloseSqlConnection(); } // Update is called once per frame void Update() { } }
