/// <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="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>
/// <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="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>
/// 根据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;
}