///2转DB
static void Change2DB(SQLiteDBHelper sqliteDBHelper, string ExcelPath)
{
IWorkbook workbook = null;
FileStream fileStream = new FileStream(ExcelPath, FileMode.Open, FileAccess.Read);
string tableName = Path.GetFileNameWithoutExtension(ExcelPath);
if (ExcelPath.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fileStream); //xlsx数据读入workbook
}
else if (ExcelPath.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fileStream); //xls数据读入workbook
}
else
{
return;
}
ISheet sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
//创建标投
IRow rowItem1 = sheet.GetRow(2);
IRow rowItem2 = sheet.GetRow(3);
List <string> ColumnsName = new List <string>();
List <string> ColumnsType = new List <string>();
List <string> CSType = new List <string>();
int cellCount = rowItem1.LastCellNum;
for (int k = 0; k < cellCount; k++)
{
ICell cell = rowItem1.GetCell(k);
ICell cel2 = rowItem2.GetCell(k);
if (cell != null)
{
string cellValue = cell.StringCellValue;
ColumnsName.Add(cel2.StringCellValue);
ColumnsType.Add(CS2DbType(cell.StringCellValue));
CSType.Add(GetCSharpType(cell.StringCellValue));
}
}
sqliteDBHelper.CreateTable(tableName, ColumnsName.ToArray(), ColumnsType.ToArray());
using (DbTransaction transaction = sqliteDBHelper.connection.BeginTransaction())
{
using (DbCommand cmd = sqliteDBHelper.connection.CreateCommand())
{
int startRow = 4;
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; i++)
{
IRow rowItem = sheet.GetRow(i);
List <string> ContentStr = new List <string>();
bool caInsert = true;
for (int k = 0; k < rowItem.LastCellNum; k++)
{
ICell cellItem = rowItem.GetCell(k);
if (cellItem != null && !string.IsNullOrEmpty(cellItem.ToString()))
{
ContentStr.Add(cellItem.ToString());
}
else
{
caInsert = false;
break;
}
}
if (caInsert)
{
//解决SQL注入漏洞 cmd是SqlCommand对象
cmd.CommandText = sqliteDBHelper.GetInsertValueStr(tableName, ColumnsName.ToArray());// @"select count(*) from UserInfo where UserName=@UserName and UserPwd=@UserPwd";
for (int k = 0; k < ColumnsName.Count; k++)
{
DbParameter para = cmd.CreateParameter();
para.ParameterName = "@" + ColumnsName[k] + ""; //SQL参数化
para.Value = ContentStr[k]; //SQL参数化
cmd.Parameters.Add(para);
}
cmd.ExecuteScalar();
}
}
}
transaction.Commit();
}
CreatCs(Path.GetFileNameWithoutExtension(ExcelPath), Path.Combine(CsPath, "CS_" + Path.GetFileNameWithoutExtension(ExcelPath) + ".cs"), ColumnsName.ToArray(), CSType);
}
}