1.读取Excel表格和保存sqlite数据库所用到的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()
{
}
}
