这是一个将Excel数据导入SQLite数据库的C#程序。它读取Excel文件，解析内容并根据预配置的数据类型检查数据格式，然后创建表格并插入数据。程序使用NPOI库读取Excel，SQLite.NET库操作数据库，并依赖于应用程序配置文件来定义数据类型。
Excel配置数据导入sqlite
读取excel数据导入到sqlite
很多游戏都需要配置数据的，有的项目实现一个自己的data编辑器，我只是想让策划在excel里面配置，然后导入到sqlite里面。
原理很简单，读取excel然后解析存入sqlite
代码
using System;
using System.Collections.Generic;
using System.Linq;
using NPOI.SS.UserModel;
using System.IO;
using System.Collections;
using System.Configuration;
using System.Data.SQLite;
/**
* 不在生成代码，感觉没用
* */
namespace ExcelTosqlite
{
class Program
{
static string dataFileName = ConfigurationManager.AppSettings["dataFolder"] + "\\"+"data.db";
static string connDataString = "data source = " + dataFileName;
static string inFolder = ConfigurationManager.AppSettings["dataFolder"];
static IDictionary dataTypeDict = ConfigurationManager.GetSection("dataType") as IDictionary;
static List<DirectoryInfo> folders = new List<DirectoryInfo>();
static List<string> tableNames = new List<string>();
static DirectoryInfo root = new DirectoryInfo(inFolder);
static SQLiteConnection conn = new SQLiteConnection(connDataString);
static string GetValueByType(string typeInfo, ICell cellInfo)
{
if (cellInfo != null)
{
switch (cellInfo.CellType)
{
case CellType.Unknown:
return "''";
case CellType.Numeric:
return "'" + cellInfo.NumericCellValue.ToString() + "'";
case CellType.String:
return "'" + cellInfo.StringCellValue + "'";
case CellType.Formula:
return "''";
case CellType.Blank:
return "''";
case CellType.Boolean:
return cellInfo.BooleanCellValue ? "'1'" : "'0'";
case CellType.Error:
return "''";
default:
return "''";
}
}
else
{
return "''";
}
}
static string CreateInsertTableCommand(string tableName, List<string> headerList, List<string> valueList)
{
string cmdStr = "INSERT INTO " + tableName + "(";
for (int headIdx = 0; headIdx < headerList.Count; headIdx++)
{
if (headIdx < headerList.Count - 1)
{
cmdStr += headerList[headIdx] + ",";
}
else
{
cmdStr += headerList[headIdx] + ")";
}
}
cmdStr += " VALUES (";
for (int colIdx = 0; colIdx < valueList.Count; colIdx++)
{
if (colIdx < valueList.Count - 1)
{
cmdStr += valueList[colIdx] + ",";
}
else
{
cmdStr += valueList[colIdx] + ")";
}
}
return cmdStr;
}
static string CreateCreateTableCommand(string tableName, List<string> headerList)
{
string cmdStr = "CREATE TABLE " + tableName + "(";
for (int headIdx = 0; headIdx < headerList.Count; headIdx++)
{
if (headIdx < headerList.Count - 1)
{
if (headerList[headIdx] == "id")
{
cmdStr += headerList[headIdx] + " INT PRIMARY KEY NOT NULL,";
}
else
{
cmdStr += headerList[headIdx] + " TEXT,";
}
}
else
{
if (headerList[headIdx] == "id")
{
cmdStr += headerList[headIdx] + " INT PRIMARY KEY NOT NULL)";
}
else
{
cmdStr += headerList[headIdx] + " TEXT)";
}
}
}
return cmdStr;
}
static bool CheckSheetHeaderConfiged(FileInfo item, List<string> headerList)
{
foreach (var head in headerList)
{
if (!dataTypeDict.Contains(head))
{
Console.WriteLine("错误：[" + item.FullName + "]表头["+head+"]没有配置数据类型，请配置后检查数据重新导出");
return false;
}
}
return true;
}
static bool CheckIntData(string data)
{
try
{
int value = int.Parse(data);
return true;
}
catch (Exception)
{
return false;
}
}
static bool CheckFloatData(string data)
{
try
{
float value = float.Parse(data);
return true;
}
catch (Exception)
{
return false;
}
}
static bool CheckVecIntData(string data)
{
try
{
string theData = data.Trim();
string[] subs = theData.Split(';');
foreach (var item in subs)
{
string theItem = item.Trim();
if (string.IsNullOrEmpty(theItem))
{
continue;
}
int value = int.Parse(theItem);
}
return true;
}
catch (Exception)
{
return false;
}
}
static bool CheckVecFloatData(string data)
{
try
{
string theData =data.Trim();
string[] subs = theData.Split(';');
foreach (var item in subs)
{
string theItem = item.Trim();
if (string.IsNullOrEmpty(theItem))
{
continue;
}
float value = float.Parse(theItem);
}
return true;
}
catch (Exception)
{
return false;
}
}
//格式正确
//没有重复
static bool CheckMapIntIntData(string data)
{
try
{
string theData =data.Trim();
string[] subs = theData.Split(';');
List<int> keys = new List<int>();
foreach (var item in subs)
{
string theItem = item.Trim();
if (string.IsNullOrEmpty(theItem))
{
continue;
}
if (!theItem.StartsWith("<") || !theItem.EndsWith(">"))
{
return false;
}
string[] subints = theItem.Split(',');
if (subints.Length != 2)
{
return false;
}
else
{
//检查第一项
string keyIntStr = subints[0].Substring(1);
int keyIntValue = int.Parse(keyIntStr);
if (keys.Contains(keyIntValue))
{
return false;
}
else
{
keys.Add(keyIntValue);
}
//检查第二项
string valueIntStr = subints[1].Substring(0, subints[1].Length - 1);
int valueInt = int.Parse(valueIntStr);
}
}
return true;
}
catch (Exception)
{
return false;
}
}
static bool CheckMapIntFloatData(string data)
{
try
{
string theData = data.Trim();
string[] subs = theData.Split(';');
List<int> keys = new List<int>();
foreach (var item in subs)
{
string theItem = item.Trim();
if (string.IsNullOrEmpty(theItem))
{
continue;
}
if (!theItem.StartsWith("<") || !theItem.EndsWith(">"))
{
return false;
}
string[] subDatas = theItem.Split(',');
if (subDatas.Length != 2)
{
return false;
}
else
{
string intKeyStr = subDatas[0].Substring(1);
int intKey = int.Parse(intKeyStr);
if (keys.Contains(intKey))
{
return false;
}
else
{
keys.Add(intKey);
}
string valueFloatStr = subDatas[1].Substring(0, subDatas[1].Length - 1);
float floatValue = float.Parse(valueFloatStr);
}
}
return true;
}
catch (Exception)
{
return false;
}
}
static bool CheckMapIntStringData(string data)
{
try
{
string theData = data.Trim();
string[] subs = theData.Split(';');
List<int> keys = new List<int>();
foreach (var item in subs)
{
string theItem = item.Trim();
if (string.IsNullOrEmpty(theItem))
{
continue;
}
if (!theItem.StartsWith("<") || !theItem.EndsWith(">"))
{
return false;
}
string[] subDatas = theItem.Split(',');
if (subDatas.Length != 2)
{
return false;
}
else
{
string intKeyStr = subDatas[0].Substring(1);
int intKey = int.Parse(intKeyStr);
if (keys.Contains(intKey))
{
return false;
}
else
{
keys.Add(intKey);
}
//第二项就是一个字符串 不用验证
}
}
return true;
}
catch (Exception)
{
return false;
}
}
static bool CheckMapFloatIntData(string data)
{
try
{
string theData = data.Trim();
string[] subs = theData.Split(';');
List<float> keys = new List<float>();
foreach (var item in subs)
{
string theItem = item.Trim();
if (string.IsNullOrEmpty(theItem))
{
continue;
}
if (!theItem.StartsWith("<") || !theItem.EndsWith(">"))
{
return false;
}
string[] subints = theItem.Split(',');
if (subints.Length != 2)
{
return false;
}
else
{
//检查第一项
string keyFloatStr = subints[0].Substring(1);
float keyFloatValue = float.Parse(keyFloatStr);
if (keys.Contains(keyFloatValue))
{
return false;
}
else
{
keys.Add(keyFloatValue);
}
//检查第二项
string valueIntStr = subints[1].Substring(0, subints[1].Length - 1);
int valueInt = int.Parse(valueIntStr);
}
}
return true;
}
catch (Exception)
{
return false;
}
}
static bool CheckMapFloatFloatData(string data)
{
try
{
string theData = data.Trim();
string[] subs = theData.Split(';');
List<float> keys = new List<float>();
foreach (var item in subs)
{
string theItem = item.Trim();
if (string.IsNullOrEmpty(theItem))
{
continue;
}
if (!theItem.StartsWith("<") || !theItem.EndsWith(">"))
{
return false;
}
string[] subints = theItem.Split(',');
if (subints.Length != 2)
{
return false;
}
else
{
//检查第一项
string keyFloatStr = subints[0].Substring(1);
float keyFloatValue = float.Parse(keyFloatStr);
if (keys.Contains(keyFloatValue))
{
return false;
}
else
{
keys.Add(keyFloatValue);
}
//检查第二项
string valueFloatStr = subints[1].Substring(0, subints[1].Length - 1);
float valueInt = float.Parse(valueFloatStr);
}
}
return true;
}
catch (Exception)
{
return false;
}
}
static bool CheckMapFloatStringData(string data)
{
try
{
string theData = data.Trim();
string[] subs = theData.Split(';');
List<float> keys = new List<float>();
foreach (var item in subs)
{
string theItem = item.Trim();
if (string.IsNullOrEmpty(theItem))
{
continue;
}
if (!theItem.StartsWith("<") || !theItem.EndsWith(">"))
{
return false;
}
string[] subints = theItem.Split(',');
if (subints.Length != 2)
{
return false;
}
else
{
//检查第一项
string keyFloatStr = subints[0].Substring(1);
float keyFloatValue = float.Parse(keyFloatStr);
if (keys.Contains(keyFloatValue))
{
return false;
}
else
{
keys.Add(keyFloatValue);
}
//第二项是字符串 不用检查
}
}
return true;
}
catch (Exception)
{
return false;
}
}
static bool CheckMapStringIntData(string data)
{
try
{
string theData = data.Trim();
string[] subs = theData.Split(';');
List<string> keys = new List<string>();
foreach (var item in subs)
{
string theItem = item.Trim();
if (string.IsNullOrEmpty(theItem))
{
continue;
}
if (!theItem.StartsWith("<") || !theItem.EndsWith(">"))
{
return false;
}
string[] subints = theItem.Split(',');
if (subints.Length != 2)
{
return false;
}
else
{
//检查第一项
string keyStr = subints[0].Substring(1);
if (keys.Contains(keyStr))
{
return false;
}
else
{
keys.Add(keyStr);
}
//检查第二项
string valueIntStr = subints[1].Substring(0, subints[1].Length - 1);
int valueInt = int.Parse(valueIntStr);
}
}
return true;
}
catch (Exception)
{
return false;
}
}
static bool CheckMapStringFloatData(string data)
{
try
{
string theData = data.Trim();
string[] subs = theData.Split(';');
List<string> keys = new List<string>();
foreach (var item in subs)
{
string theItem = item.Trim();
if (string.IsNullOrEmpty(theItem))
{
continue;
}
if (!theItem.StartsWith("<") || !theItem.EndsWith(">"))
{
return false;
}
string[] subints = theItem.Split(',');
if (subints.Length != 2)
{
return false;
}
else
{
//检查第一项
string keyStr = subints[0].Substring(1);
if (keys.Contains(keyStr))
{
return false;
}
else
{
keys.Add(keyStr);
}
//检查第二项
string valueFloatStr = subints[1].Substring(0, subints[1].Length - 1);
float valueInt = float.Parse(valueFloatStr);
}
}
return true;
}
catch (Exception)
{
return false;
}
}
static bool CheckMapStringStringData(string data)
{
try
{
string theData = data.Trim();
string[] subs = theData.Split(';');
List<string> keys = new List<string>();
foreach (var item in subs)
{
string theItem = item.Trim();
if (string.IsNullOrEmpty(theItem))
{
continue;
}
if (!theItem.StartsWith("<") || !theItem.EndsWith(">"))
{
return false;
}
string[] subints = theItem.Split(',');
if (subints.Length != 2)
{
return false;
}
else
{
//检查第一项
string keyStr = subints[0].Substring(1);
if (keys.Contains(keyStr))
{
return false;
}
else
{
keys.Add(keyStr);
}
//第二项就是字符串 不用检查
}
}
return true;
}
catch (Exception)
{
return false;
}
}
//-1没有任何问题 其他:对应列数据有问题 不对配置数据进行整理
static int CheckDataValue(List<string> headerList, List<string> dataList)
{
for (int idx = 0; idx < dataList.Count; idx++)
{
switch (dataTypeDict[headerList[idx]])
{
case "int":
if (!CheckIntData(dataList[idx]))
{
return idx;
}
break;
case "float":
if (!CheckFloatData(dataList[idx]))
{
return idx;
}
break;
case "string"://原数据就是string 不用检查
break;
case "vec_int"://用;分割 检查每一项
if (!CheckVecIntData(dataList[idx]))
{
return idx;
}
break;
case "vec_float"://用;分割 检查每一项
if (!CheckVecFloatData(dataList[idx]))
{
return idx;
}
break;
case "vec_string"://原数据就是string 不用检查
break;
case "map_int_int":
if (!CheckMapIntIntData(dataList[idx]))
{
return idx;
}
break;
case "map_int_float":
if (!CheckMapIntFloatData(dataList[idx]))
{
return idx;
}
break;
case "map_int_string":
if (!CheckMapIntStringData(dataList[idx]))
{
return idx;
}
break;
case "map_float_int":
if (!CheckMapFloatIntData(dataList[idx]))
{
return idx;
}
break;
case "map_float_float":
if (!CheckMapFloatFloatData(dataList[idx]))
{
return idx;
}
break;
case "map_float_string":
if (!CheckMapFloatStringData(dataList[idx]))
{
return idx;
}
break;
case "map_string_int":
if (!CheckMapStringIntData(dataList[idx]))
{
return idx;
}
break;
case "map_string_float":
if (!CheckMapStringFloatData(dataList[idx]))
{
return idx;
}
break;
case "map_string_string":
if (!CheckMapStringStringData(dataList[idx]))
{
return idx;
}
break;
default:
return -1;
}
}
return -1;
}
static string GetCellStringValue(ICell cell)
{
switch (cell.CellType)
{
case CellType.Unknown:
return "";
case CellType.Numeric:
return cell.NumericCellValue.ToString();
case CellType.String:
return cell.StringCellValue;
case CellType.Formula:
return "";
case CellType.Blank:
return "";
case CellType.Boolean:
return cell.BooleanCellValue ? "1" : "0";
case CellType.Error:
return "";
default:
return "";
}
}
static string GetColStr(int col)
{//我们断定超不出26进制二位数
int pre = col / 26;
if (pre > 0)
{
int thePreCharValue = pre + 'A' - 1;
char thePreChar = (char)thePreCharValue;
int theCharValue = col%26 + 'A';
char theChar = (char)theCharValue;
return thePreChar.ToString() + theChar.ToString();
}
else
{
int theCharValue = col + 'A';
char theChar = (char)theCharValue;
return theChar.ToString();
}
}
static bool ExportOneSheet(FileInfo item, ISheet sheet)
{
if (sheet != null && sheet.PhysicalNumberOfRows > 0 && ((sheet.LastRowNum - sheet.FirstRowNum + 1) != sheet.PhysicalNumberOfRows))
{
Console.WriteLine("警告：[" + item.FullName + "]数据不紧凑，请确认配置数据正确性");
}
if (sheet != null && sheet.PhysicalNumberOfRows > 0)
{
bool checkHeader = false;
List<string> headerList = new List<string>();
//构建表名
string tableName = item.Name.Substring(0, item.Name.Length - ".xlsx".Length) + "_" + sheet.SheetName;
for (int rowIdx = sheet.FirstRowNum; rowIdx <= sheet.LastRowNum; rowIdx++)
{
IRow row = sheet.GetRow(rowIdx);
if (row != null && row.FirstCellNum < row.LastCellNum)
{
if (checkHeader)
{
//添加表数据
//整理数据
if (row.FirstCellNum != 0 || row.LastCellNum - row.FirstCellNum > headerList.Count)
{
Console.WriteLine("错误：[" + item.FullName + "]数据不整齐，请确认配置数据正确性");
return false;
}
List<string> valueList = new List<string>();
List<string> dataList = new List<string>();
for (int colIdx = row.FirstCellNum; colIdx < row.LastCellNum; colIdx++)
{
ICell cell = row.GetCell(colIdx);
valueList.Add(GetValueByType(headerList[colIdx], cell));
dataList.Add(GetCellStringValue(cell));
}
int checkRetCol = CheckDataValue(headerList, dataList);
if (checkRetCol >= 0)
{
Console.WriteLine("错误：[" + item.FullName + "]数据错误，行:["+ (rowIdx + 1) + "]列:["+ GetColStr(checkRetCol) + "]，请检查数据和数据类型是否对应");
return false;
}
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = CreateInsertTableCommand(tableName, headerList, valueList);
cmd.ExecuteReader();
}
else
{
//整理表头
for (int colIdx = row.FirstCellNum; colIdx < row.LastCellNum; colIdx++)
{
ICell cell = row.GetCell(colIdx);
if (cell != null)
{
string cellValue = cell.StringCellValue;
headerList.Add(cellValue);
}
else
{
Console.WriteLine("错误：[" + item.FullName + "]表头有空单元格");
return false;
}
}
if (!CheckSheetHeaderConfiged(item, headerList))
{
return false;
}
//检查重复
if (tableNames.Contains(tableName))
{
Console.WriteLine("错误：[" + tableName + "]表名重复，路径[" + item.FullName + "]");
return false;
}
else
{
tableNames.Add(tableName);
}
//创建表 sqlite
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = CreateCreateTableCommand(tableName, headerList);
cmd.ExecuteNonQuery();
checkHeader = true;
}
}
}
}
return true;
}
static bool ExportOneFile(FileInfo item)
{
if (item.Name.EndsWith(".xlsx"))
{
IWorkbook workbook = WorkbookFactory.Create(item.FullName);
for (int idx = 0; idx < workbook.NumberOfSheets; idx++)
{
ISheet sheet = workbook.GetSheetAt(idx);
if (!ExportOneSheet(item, sheet))
{
return false;
}
}
Console.WriteLine("[" + item.FullName + "]成功生成数据...");
return true;
}
return true;
}
static bool CreateWriteDataVersionTable()
{
List<string> headerList = new List<string>() { "id", "version"};
List<string> valueList = new List<string>() { "'1'", "'" + DateTime.Now.ToString() + "'"};
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = CreateCreateTableCommand("DataVersion", headerList);
cmd.ExecuteNonQuery();
cmd = conn.CreateCommand();
cmd.CommandText = CreateInsertTableCommand("DataVersion", headerList, valueList);
cmd.ExecuteReader();
return true;
}
static void Main(string[] args)
{
try
{
if (!Directory.Exists(inFolder))
{
Console.WriteLine("错误：数据配置文件夹路径[dataFolder]配置错误!");
return;
}
SQLiteConnection.CreateFile(dataFileName);
folders.Add(root);
Console.WriteLine("========开始导出数据========\n");
while (folders.Count > 0)
{
foreach (var item in folders.First().GetFiles())
{
if (!ExportOneFile(item))
{
return;
}
}
foreach (var item in folders.First().GetDirectories())
{
folders.Add(item);
}
folders.RemoveAt(0);
}
/* if (!CreateWriteDataVersionTable())
{
Console.WriteLine("\n========数据版本表创建失败========");
}*/
Console.WriteLine("\n========数据导出完成========");
Console.ReadLine();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
Console.ReadLine();
}
}
}
}
配置
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
<section name="dataType" type="System.Configuration.DictionarySectionHandler"/>
</configSections>
<!--
数据类型支持15种,数据类型和内容不匹配会导致数据无法导出
基础数据类型
int float string
线性表，例:1;2;3;4;5;
vec_int vec_float vec_string
映射，例:<1,2>;<2,2>;<3,3>;
map_int_int map_int_float map_int_string
map_float_int map_float_float map_float_string
map_string_int map_string_float map_string_string
在工具内只是验证表内容是否能解释成指定的类型
在代码生成的时候会利用指定的数据类型生成代码
-->
<dataType>
<add key="id" value="int"/>
<add key="grow" value="float"/>
<add key="name" value="string"/>
<add key="talkid" value="vec_int"/>
<add key="talkgrow" value="vec_float"/>
<add key="talk" value="vec_string"/>
<add key="npctalkid1" value="map_int_int"/>
<add key="npctalkid2" value="map_int_float"/>
<add key="npctalkid3" value="map_int_string"/>
<add key="npctalkid4" value="map_float_int"/>
<add key="npctalkid5" value="map_float_float"/>
<add key="npctalkid6" value="map_float_string"/>
<add key="npctalkid7" value="map_string_int"/>
<add key="npctalkid8" value="map_string_float"/>
<add key="npctalkid9" value="map_string_string"/>
<add key="icon" value="string"/>
<add key="model" value="string"/>
<add key="desc" value="string"/>
<add key="sex" value="int"/>
</dataType>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
</startup>
<entityFramework>
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>
<system.data>
<DbProviderFactories>
<remove invariant="System.Data.SQLite.EF6" />
<add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
<remove invariant="System.Data.SQLite" /><add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" /></DbProviderFactories>
</system.data>
<appSettings>
<add key="dataFolder" value="E:\GameProject\Doc"/>
<add key="exportFolder" value ="E:\GameProject\Doc"/>
<add key="codeFolder" value="E:\GameProject\Doc"/>
</appSettings>
</configuration>
扫一扫
570
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
