unity读取Excel表格保存到Sqlite数据库

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()
    {
        
    }
}