//执行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>
/// <param name="headerIndex"></param>
/// <param name="dataIndex"></param>
/// <returns></returns>
private string GetSqlFieldValueStringWithoutAlias(AsposeCell asposeCell,Entity entity, int headerIndex, int dataIndex)//对于没有配置列的情况使用
{
string sql = "";
int column = 0;
while (asposeCell.GetCellValue(headerIndex-1, column) != "")
{
sql += "'" + (asposeCell.IsMerged(dataIndex - 1, column) ? asposeCell.GetMergedCellValue(dataIndex - 1, column) : asposeCell.GetCellValue(dataIndex - 1, column)).Replace("'","''") + "',";
column++;
}
sql = sql.TrimEnd(',');
return sql;
}
/// <summary>
/// 得到代码表的对应值
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="property"></param>
/// <param name="headerIndex"></param>
/// <param name="dataIndex"></param>
/// <returns></returns>
private string GetCodeTableValue(AsposeCell asposeCell,Property property,int headerIndex,int dataIndex)
{
string value=asposeCell.IsMerged(dataIndex-1, GetColumnIndexByHeaderText(asposeCell, headerIndex, property.HeaderText))?asposeCell.GetMergedCellValue(dataIndex-1, GetColumnIndexByHeaderText(asposeCell, headerIndex, property.HeaderText)):asposeCell.GetCellValue(dataIndex-1, GetColumnIndexByHeaderText(asposeCell, headerIndex, property.HeaderText)).Replace("'","''");
StringBuilder sb = new StringBuilder("select distinct ");
sb.Append(property.CodeTable.PrimaryKey);
sb.Append(" from ");
sb.Append(property.CodeTable.Name);
sb.Append(" where ");
sb.Append(property.CodeTable.ReferenceColumn);
sb.Append("='");
sb.Append(value);
sb.Append("'");
if (property.DataType != "" && property.DataType != "string")
{
if (!ValidateDataType(value, property.DataType))
{
throw new Exception("列\"" + property.HeaderText + "\"中存在非\"" + property.DataType + "\"类型数据！");
}
}
if (property.DataLength != 0 && property.DataLength != 5000)
{
if (!ValidateDataLength(value, property.DataLength))
{
throw new Exception("列\"" + property.HeaderText + "\"中存长度超过\"" + property.DataLength.ToString() + "\"的数据！");
}
}
if (dbHelper.ExecuteScalar(CommandType.Text, sb.ToString()) != null)
{
return dbHelper.ExecuteScalar(CommandType.Text, sb.ToString()).ToString();
}
else
{
throw new Exception("没有对应的代码表值！");
}
}
/// <summary>
/// 得到查询值sql语句段
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="entity"></param>
/// <param name="headerIndex"></param>
/// <param name="dataIndex"></param>
/// <returns></returns>
private string GetSqlFieldValueString(AsposeCell asposeCell, Entity entity,int headerIndex, int dataIndex)
{
string sql = "";
string v = "";
foreach (Property p in entity.Propertys)//遍历实体的所有属性
{
if (p.CodeTable != null)
{
sql += "'" + GetCodeTableValue(asposeCell,p,headerIndex,dataIndex).Replace("'","''") + "',";//注意如果单元格本身的值就有“'”的情况
}
else//说明此属性是一个代码表字段
{
if(asposeCell.IsMerged(dataIndex-1, GetColumnIndexByHeaderText(asposeCell, headerIndex, p.HeaderText)))//是否为合并单元格(对于合并单元格取此合并单元格的第一个值)
{
v=asposeCell.GetMergedCellValue(dataIndex-1, GetColumnIndexByHeaderText(asposeCell, headerIndex, p.HeaderText));
}
else
{
v=asposeCell.GetCellValue(dataIndex-1, GetColumnIndexByHeaderText(asposeCell, headerIndex, p.HeaderText));
}
if (v == "")//说明单元格中没有任何值，就要考虑“默认值”和“必须”属性
{
if (GetDefaultValue(asposeCell, entity.Name, p.ColumnName, p.DefaultValue) != "")//说明有默认值
{
v = GetDefaultValue(asposeCell, entity.Name, p.ColumnName, p.DefaultValue);
}
else//如果单元格没有值并且无默认值，则检查此属性是否是必须的
{
if (!p.Required)
{
v = "";
}
else
{
throw new Exception("列\""+p.HeaderText+"\""+"不能为空！");
}
}
}
//检查类型
if (p.DataType != "" && p.DataType != "string")
{
if (!ValidateDataType(v, p.DataType))
{
throw new Exception("列\"" + p.HeaderText + "\"中存在非\"" + p.DataType + "\"类型数据！");
}
}
//属性长度检查
if (p.DataLength != 0 && p.DataLength != 5000)
{
if(!ValidateDataLength(v,p.DataLength))
{
throw new Exception("列\"" + p.HeaderText + "\"中存长度超过\"" + p.DataLength.ToString() + "\"的数据！");
}
}
sql += "'"+v.Replace("'", "''")+"',";
}
}
sql = sql.TrimEnd(',');
return sql;
}
/// <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;
}
/// <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;
}
/// <summary>
/// 依据排除列得到查询字段sql语句段
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="headerIndex"></param>
/// <returns></returns>
private string GetSqlFieldStringAccordingToExcludedColumns(AsposeCell asposeCell, Entity entity)
{
string sql = "";
foreach(DataColumn column in dbHelper.GetDataTable(CommandType.Text,"select top 0 * from "+entity.Name).Columns)
{
if (!entity.ExcludedColumns.Contains(column.ColumnName))
{
sql += column.ColumnName + ",";
}
}
sql = sql.TrimEnd(',');
return sql;
}
/// <summary>
/// 得到查询字段sql语句段
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="entity"></param>
/// <returns></returns>
private string GetSqlFieldString(AsposeCell asposeCell,Entity entity)
{
string sql = "";
foreach (Property p in entity.Propertys)
{
sql += p.ColumnName+",";
}
sql = sql.TrimEnd(',');
return sql;
}
/// <summary>
/// 得到查询条件sql语句段
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="entity"></param>
/// <param name="headerIndex"></param>
/// <param name="dataIndex"></param>
/// <returns></returns>
private string GetSqlConditionString(AsposeCell asposeCell, Entity entity,int headerIndex,int dataIndex)
{
string sql="";
foreach (Property p in entity.Propertys)
{
if(p.IsPrimaryKey)
{
sql+=p.ColumnName+"=";
if (p.HeaderText!="")
{
sql+="'"+asposeCell.GetCellValue(dataIndex-1,GetColumnIndexByHeaderText(asposeCell,headerIndex,p.HeaderText))+"'";
}
else
{
sql += "'"+GetDefaultValue(asposeCell, entity.Name, p.ColumnName, p.DefaultValue)+"'";
}
sql += " and";
}
}
sql = sql.Substring(0, sql.Length - 4);
return sql;
}
/// <summary>
/// 根据DefaultValue配置节确定默认值
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="entityName"></param>
/// <param name="columnName"></param>
/// <param name="defaultValue"></param>
/// <returns></returns>
private string GetDefaultValue(AsposeCell asposeCell,string entityName,string columnName, string defaultValue)
{
string r = "";
switch (defaultValue)
{
case "NewID": r = Guid.NewGuid().ToString(); break;
case "Max": r = dbHelper.ExecuteScalar(CommandType.Text, "select max(" + columnName + ") from " + entityName + "") != null ?((Convert.ToInt32(dbHelper.ExecuteScalar(CommandType.Text,"select max(" + columnName + ") from " + entityName + "").ToString())+1).ToString()) : "1"; break;
default: r = defaultValue; break;
}
return r;
}
/// <summary>
/// 根据HeaderText配置节确定列索引（从0开始）
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="headerIndex"></param>
/// <param name="headerText"></param>
/// <returns></returns>
private int GetColumnIndexByHeaderText(AsposeCell asposeCell,int headerIndex,string headerText)
{
int columnIndex=0;
int r=0;
while (asposeCell.GetCellValue(headerIndex-1, columnIndex) != "")
{
if (asposeCell.GetCellValue(headerIndex-1, columnIndex) == headerText)
{
r = columnIndex;
}
columnIndex++;
}
return r;
}
/// <summary>
/// 根据EndFlag标记判断当前数据行是否结束
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="endFlag"></param>
/// <param name="row"></param>
/// <returns></returns>
private bool IsEnd(AsposeCell asposeCell,string endFlag,int row)
{
bool r = false;
switch (endFlag)
{
case "RowBlank":
if (!asposeCell.RowHasValue(row))
{
r = true;
}
break;
default :
if (asposeCell.GetCellValue(endFlag+row.ToString()) == "")
{
r = true;
}
break;
}
return r;
}