Excel表格转成sqlite数据库导入unity发布多平台的操作流程
前言
好久没用sqlite了，今天重新写了下，顺便记录下整个解决流程
环境准备
1.我用的unity版本是2019.4.10f1
2.需要装一下sqlite的可视化工具：使用SQLiteExpertPro专业版实用30天，才可以导入excel文件，不用担心，点这里下载自用（pojie）专业版
3.unity需要安装相应的plugins文件，我也整理好了，点这里直接下载。
步骤过程
一、excel转成.csv格式
1.创建excel表格，填写数据
2.excel表格要另存为.csv格式
3.这个.csv格式的文件需要 右键-打开方式-记事本 打开，然后另存为编码格式改为UTF-8
</font>
二、.csv文件导入sqlite数据库的步骤
在SQLite Expert Professional 中新建一个database
右键这个新建的database选择导入.csv文件
设置编码格式为UTF—8，如图所示，点击start
然后可以看到导入成功
</font>
三、将.db文件放入unity进行操作
把刚才的test2.db文件放到unity目录下
先写个sqlite的帮助类，话不多说上代码
using UnityEngine;
using Mono.Data.Sqlite;
using System;
public class SQLiteController
{
/// <summary>
/// 数据库连接定义
/// </summary>
private SqliteConnection dbConnection;
/// <summary>
/// SQL命令定义
/// </summary>
private SqliteCommand dbCommand;
/// <summary>
/// 数据读取定义
/// </summary>
private SqliteDataReader dataReader;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
public SQLiteController(string connectionString)
{
try{
//构造数据库连接
dbConnection=new SqliteConnection(connectionString);
//打开数据库
dbConnection.Open();
}catch(Exception e)
{
Debug.Log(e.Message);
}
}
/// <summary>
/// 执行SQL命令
/// </summary>
/// <returns>The query.</returns>
/// <param name="queryString">SQL命令字符串</param>
public SqliteDataReader ExecuteQuery(string queryString)
{
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = queryString;
dataReader = dbCommand.ExecuteReader();
return dataReader;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void CloseConnection()
{
//销毁Command
if(dbCommand != null){
dbCommand.Cancel();
}
dbCommand = null;
//销毁Reader
if(dataReader != null){
dataReader.Close();
}
dataReader = null;
//销毁Connection
if(dbConnection != null){
dbConnection.Close();
}
dbConnection = null;
}
/// <summary>
/// 读取整张数据表
/// </summary>
/// <returns>The full table.</returns>
/// <param name="tableName">数据表名称</param>
public SqliteDataReader ReadFullTable(string tableName)
{
string queryString = "SELECT * FROM " + tableName;
return ExecuteQuery (queryString);
}
/// <summary>
/// 向指定数据表中插入数据
/// </summary>
/// <returns>The values.</returns>
/// <param name="tableName">数据表名称</param>
/// <param name="values">插入的数值</param>
public SqliteDataReader InsertValues(string tableName,string[] values)
{
//获取数据表中字段数目
int fieldCount=ReadFullTable(tableName).FieldCount;
//当插入的数据长度不等于字段数目时引发异常
if(values.Length!=fieldCount){
throw new SqliteException("values.Length!=fieldCount");
}
string queryString = "INSERT INTO " + tableName + " VALUES (" + values[0];
for(int i=1; i<values.Length; i++)
{
queryString+=", " + values[i];
}
queryString += " )";
return ExecuteQuery(queryString);
}
/// <summary>
/// 更新指定数据表内的数据
/// </summary>
/// <returns>The values.</returns>
/// <param name="tableName">数据表名称</param>
/// <param name="colNames">字段名</param>
/// <param name="colValues">字段名对应的数据</param>
/// <param name="key">关键字</param>
/// <param name="value">关键字对应的值</param>
public SqliteDataReader UpdateValues(string tableName,string[] colNames,string[] colValues,string key,string operation,string value)
{
//当字段名称和字段数值不对应时引发异常
if(colNames.Length!=colValues.Length) {
throw new SqliteException("colNames.Length!=colValues.Length");
}
string queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + colValues[0];
for(int i=1; i<colValues.Length; i++)
{
queryString+=", " + colNames[i] + "=" + colValues[i];
}
queryString += " WHERE " + key + operation + value;
return ExecuteQuery(queryString);
}
/// <summary>
/// 删除指定数据表内的数据
/// </summary>
/// <returns>The values.</returns>
/// <param name="tableName">数据表名称</param>
/// <param name="colNames">字段名</param>
/// <param name="colValues">字段名对应的数据</param>
public SqliteDataReader DeleteValuesOR(string tableName,string[] colNames,string[] operations,string[] colValues)
{
//当字段名称和字段数值不对应时引发异常
if(colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length) {
throw new SqliteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
}
string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + colValues[0];
for(int i=1; i<colValues.Length; i++)
{
queryString+="OR " + colNames[i] + operations[0] + colValues[i];
}
return ExecuteQuery(queryString);
}
/// <summary>
/// 删除指定数据表内的数据
/// </summary>
/// <returns>The values.</returns>
/// <param name="tableName">数据表名称</param>
/// <param name="colNames">字段名</param>
/// <param name="colValues">字段名对应的数据</param>
public SqliteDataReader DeleteValuesAND(string tableName,string[] colNames,string[] operations,string[] colValues)
{
//当字段名称和字段数值不对应时引发异常
if(colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length) {
throw new SqliteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
}
string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + colValues[0];
for(int i=1; i<colValues.Length; i++)
{
queryString+=" AND " + colNames[i] + operations[i] + colValues[i];
}
return ExecuteQuery(queryString);
}
/// <summary>
/// 创建数据表
/// </summary> +
/// <returns>The table.</returns>
/// <param name="tableName">数据表名</param>
/// <param name="colNames">字段名</param>
/// <param name="colTypes">字段名类型</param>
public SqliteDataReader CreateTable(string tableName,string[] colNames,string[] colTypes)
{
string queryString = "CREATE TABLE " + tableName + "( " + colNames [0] + " " + colTypes [0];
for (int i=1; i<colNames.Length; i++)
{
queryString+=", " + colNames[i] + " " + colTypes[i];
}
queryString+= " ) ";
return ExecuteQuery(queryString);
}
/// <summary>
/// Reads the table.
/// </summary>
/// <returns>The table.</returns>
/// <param name="tableName">Table name.</param>
/// <param name="items">Items.</param>
/// <param name="colNames">Col names.</param>
/// <param name="operations">Operations.</param>
/// <param name="colValues">Col values.</param>
public SqliteDataReader ReadTable(string tableName,string[] items,string[] colNames,string[] operations, string[] colValues)
{
string queryString = "SELECT " + items [0];
for (int i=1; i<items.Length; i++)
{
queryString+=", " + items[i];
}
queryString += " FROM " + tableName + " WHERE " + colNames[0] + " " + operations[0] + " " + colValues[0];
for (int i=0; i<colNames.Length; i++)
{
queryString+=" AND " + colNames[i] + " " + operations[i] + " " + colValues[0] + " ";
}
return ExecuteQuery(queryString);
}
}
然后是测试代码，读取所有的数据，里面包括了各个平台的路径是不一样的，可以仔细看下
using System.Collections;
using System.Collections.Generic;
using System.IO;
using Mono.Data.Sqlite;
using UnityEngine;
public class ReadAllData : MonoBehaviour
{
private SQLiteController m_sql;
private string m_path;
void Start()
{
#if UNITY_ANDROID
m_path = "URL=file:" + Application.persistentDataPath + "/" + "test.db";
#endif
#if UNITY_IOS
m_path = "Data Source=" + Application.persistentDataPath + "/" + "test.db";
#endif
#if UNITY_STANDALONE_WIN
m_path = "Data Source=" + Application.dataPath + "/" + "test2.db";
#endif
Debug.Log(m_path);
m_sql=new SQLiteController(m_path);
SqliteDataReader reader = m_sql.ReadFullTable ("playerInfo");
while(reader.Read())
{
//读取ID+Name+性别+分数
Debug.Log(reader.GetInt32(reader.GetOrdinal("ID"))+reader.GetString(reader.GetOrdinal("name"))
+reader.GetString(reader.GetOrdinal("gender"))+
reader.GetInt32(reader.GetOrdinal("score")));
}
}
void Update()
{
}
}
其他的数据库操作可以自己去试下，直接用帮助类里面的方法就可以了。
注意事项
各个平台之间的路径问题
#if UNITY_ANDROID
m_path = "URL=file:" + Application.persistentDataPath + "/" + "test.db";
#endif
#if UNITY_IOS
m_path = "Data Source=" + Application.persistentDataPath + "/" + "test.db";
#endif
#if UNITY_STANDALONE_WIN
m_path = "Data Source=" + Application.dataPath + "/" + "test2.db";
#endif
总结
先写这些，欢迎大佬多多来给萌新指正，欢迎大家来共同探讨。
如果各位看官觉得文章有点点帮助，跪求各位给点个“一键三连”，谢啦~
声明一下：本博文章若非特殊注明皆为原创原文链接
https://blog.csdn.net/Wrinkle2017/article/details/113745415
————————————————————————————————
版权声明
版权声明：本博客为非营利性个人原创
所刊登的所有作品的著作权均为本人所拥有
本人保留所有法定权利,违者必究!
对于需要复制、转载、链接和传播博客文章或内容的
请及时和本博主进行联系
对于经本博主明确授权和许可使用文章及内容的
使用时请注明文章或内容出处并注明网址
转载请附上原文出处链接及本声明
扫一扫
7266
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
