/// <summary>
/// 得到查询值sql语句段（未配置列情况下使用且有别名）
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="headerIndex"></param>
/// <param name="dataIndex"></param>
/// <returns></returns>
private string GetSqlFieldValueStringAccordingToAlias(AsposeCell asposeCell, int headerIndex, int dataIndex)//对于没有配置列的情况使用
{
string sql = "";
foreach (string columnName in asposeCell.GetRangeNames(headerIndex - 1))
{
if(asposeCell.IsMerged(dataIndex - 1,asposeCell.GetColumnIndexByRangeName(columnName)))
{
sql+="'"+asposeCell.GetMergedCellValue(dataIndex - 1, asposeCell.GetColumnIndexByRangeName(columnName)).Replace("'","''")+"',";
}
else
{
sql += "'" + asposeCell.GetCellValue(dataIndex - 1, asposeCell.GetColumnIndexByRangeName(columnName)).Replace("'", "''") + "',";
}
}
sql = sql.TrimEnd(',');
return sql;
}
//执行Excel导入
private void ExcuteImport()
{
string sqlDelete = "";//删除操作对应的sql语句
string sqlInsert = "";//插入操作使用的sql语句
int headerIndex = 1;//列头所在行
int dataIndex = 2;//数据起始行
string endFlag = "RowBlank";
foreach (string excelFullName in configs.Keys)//遍历所有config
{
asposeCell = new AsposeCell(excelFullName);
List<Entity> entities = configs[excelFullName].Entities;//注意每个config中不一定只有一个实体，可以配置多个，这样每个excel可以导入到多张表中
headerIndex = configs[excelFullName].HeaderIndex;
dataIndex = configs[excelFullName].DataIndex;
endFlag = configs[excelFullName].EndFlag;
while (!IsEnd(asposeCell, endFlag, dataIndex))
{
foreach (Entity entity in entities)
{
if (entity.Propertys.Count > 0)//说明配置了Column字段
{
if (entity.DeleteRepeat)
{
sqlDelete = "delete from " + entity.Name + " where " + GetSqlConditionString(asposeCell, entity, headerIndex, dataIndex);
dbHelper.ExcuteNonQuery(CommandType.Text, sqlDelete);
}
sqlInsert = "insert into " + entity.Name + "(" + GetSqlFieldString(asposeCell, entity) + ")" + " Values(" + GetSqlFieldValueString(asposeCell, entity, headerIndex, dataIndex) + ")";
dbHelper.ExcuteNonQuery(CommandType.Text, sqlInsert);
}
else
{
if (asposeCell.GetRangeNames(headerIndex - 1).Count > 0)//说明指定了单元格别名作为列名
{
sqlInsert = "insert into " + entity.Name + "(" + GetSqlFieldStringAccordingToAlias(asposeCell, headerIndex) + ") values(" + GetSqlFieldValueStringAccordingToAlias(asposeCell, headerIndex, dataIndex) + ")";
}
else if (entity.ExcludedColumns.Count > 0)//说明有排除字段
{
sqlInsert = "insert into " + entity.Name + "(" + GetSqlFieldStringAccordingToExcludedColumns(asposeCell, entity) + ") values(" + GetSqlFieldValueStringWithoutAlias(asposeCell, entity, headerIndex, dataIndex) + ")";
}
else
{
sqlInsert = "insert into " + entity.Name + " values(" + GetSqlFieldValueStringWithoutAlias(asposeCell, entity, headerIndex, dataIndex) + ")";
}
dbHelper.ExcuteNonQuery(CommandType.Text, sqlInsert);
}
}
dataIndex++;
}
}
}
/// <summary>
/// 依据别名得到查询字段sql段
/// </summary>
/// <param name="asposeCell"></param>
/// <returns></returns>
private string GetSqlFieldStringAccordingToAlias(AsposeCell asposeCell,int headerIndex)
{
string sql = "";
foreach (string columnName in asposeCell.GetRangeNames(headerIndex - 1))
{
sql += columnName + ",";
}
sql = sql.TrimEnd(',');
return sql;
}