当我们用OleBb读取Excel的时候,如果没有配置IMEX=1的属性,微软的处理机制是将列转换为同一类型来读取的.例如你在第一行写的数字格式,而第二行写的字符格式,就会出现某些列有值却读不出来.其实问题也很简单,如果知道问题所在的话.属性设置为"IMEX=1"即可
string filename = string.Empty;
DataSet ds = new DataSet();
try
{
filename = context.Server.MapPath(context.Request.Form["excel"]);
try
{
if (filename == string.Empty)
{
context.Response.Write(ExtensionType.JsonError("操作失败:Excel文件不能为空!"));
}
string oleDBConnString = string.Empty;
if (filename.Contains(".xlsx"))
{
oleDBConnString = "Provider=Microsoft.ACE.OLEDB.12.0;";
oleDBConnString += "Data Source=";
oleDBConnString += filename;
oleDBConnString += ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
}
else
{
oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";
oleDBConnString += "Data Source=";
oleDBConnString += filename;
oleDBConnString += ";Extended Properties='Excel 8.0; HDR=Yes;IMEX=1'";
}
OleDbConnection oleDBConn = null;
OleDbDataAdapter oleAdMaster = null;
DataTable m_tableName = new System.Data.DataTable();
oleDBConn = new OleDbConnection(oleDBConnString);
oleDBConn.Open();
m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (m_tableName != null && m_tableName.Rows.Count > 0)
{
m_tableName.TableName = m_tableName.Rows[0]["TABLE_NAME"].ToString();
}
string sqlMaster;
sqlMaster = " SELECT * FROM [" + m_tableName.TableName + "]";
oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
oleAdMaster.Fill(ds, "m_tableName");
oleAdMaster.Dispose();
oleDBConn.Close();
oleDBConn.Dispose();
List<string> SqlList = new List<string>();
List<string> SqlList2 = new List<string>();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (string.IsNullOrEmpty(ds.Tables[0].Rows[i][0].ToString()))
{
continue;
}
string UserName = ds.Tables[0].Rows[i][0].ToString();
string UserPwd = ds.Tables[0].Rows[i][1].ToString();
string sqlUser = "select count(*) from HT_User where UserName='" + UserName + "' and [Password]='" + Security.EncryptPassword(UserPwd, "MD5") + "' ";
int num = Convert.ToInt32(HtCRM.DbUtility.AosySql.ExecuteScalar(sqlUser));
if (num > 0)
{
strSql.Append(" update HT_User set Mobile='");
strSql.Append(ds.Tables[0].Rows[i][2].ToString().Trim() + "',Email='");
strSql.Append(ds.Tables[0].Rows[i][3].ToString().Trim() + "',Sex='");
strSql.Append(ds.Tables[0].Rows[i][4].ToString().Trim().GetSexIDBySexName() + "',CityID=");
strSql.Append(ds.Tables[0].Rows[i][5].ToString().Trim().GetCityIDByCityName() + ",DeptID=");
strSql.Append(ds.Tables[0].Rows[i][6].ToString().Trim().GetDeptIDByDeptName() + ",JobsID=");
strSql.Append(ds.Tables[0].Rows[i][7].ToString().Trim().GetJobIDByJobName() + ",additionalRole='");
strSql.Append(ds.Tables[0].Rows[i][8].ToString().Trim() + "' where UserName='");
strSql.Append(ds.Tables[0].Rows[i][0].ToString().Trim() + "' ");
}
else
{
strSql.Append(" insert into HT_User(");
strSql.Append(" UserName, [Password], Mobile, Email, Sex, CityID, DeptID, JobsID, ParentId, additionalRole) values ('");
try
{
strSql.Append(ds.Tables[0].Rows[i][0].ToString().Trim() + "','" +
//导入的密码进行加密
Security.EncryptPassword(ds.Tables[0].Rows[i][1].ToString().Trim(), "MD5") + "','" +
ds.Tables[0].Rows[i][2].ToString().Trim() + "','" +
ds.Tables[0].Rows[i][3].ToString().Trim() + "','" +
ds.Tables[0].Rows[i][4].ToString().Trim().GetSexIDBySexName() + "'," +
ds.Tables[0].Rows[i][5].ToString().Trim().GetCityIDByCityName() + "," +
ds.Tables[0].Rows[i][6].ToString().Trim().GetDeptIDByDeptName() + "," +
ds.Tables[0].Rows[i][7].ToString().Trim().GetJobIDByJobName() + "," +
1 + ",'" +
ds.Tables[0].Rows[i][8].ToString() + "') ");
}
catch (Exception)
{
context.Response.Write(ExtensionType.JsonError("操作失败:数据有问题,请核对数据!"));
return;
}
}
}
SqlList.Add(strSql.ToString());
int count = HtCRM.DbUtility.AosySql.ExecuteListSQL(SqlList);
if (count == SqlList.Count)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
try
{
string sql = "select ID from HT_User where DeptID=" + ds.Tables[0].Rows[i][10].ToString().Trim().GetDeptIDByDeptName() + " and UserName='" + ds.Tables[0].Rows[i][9].ToString().Trim() + "'";
object id = HtCRM.DbUtility.AosySql.ExecuteScalar(sql);
if (id != null)
{
strSql2.Append(" update HT_User set ParentId=(select ID from HT_User where DeptID=");
strSql2.Append(ds.Tables[0].Rows[i][10].ToString().Trim().GetDeptIDByDeptName());
strSql2.Append(" and UserName='");
strSql2.Append(ds.Tables[0].Rows[i][9].ToString().Trim() + "')");
strSql2.Append(" where UserName='");
strSql2.Append(ds.Tables[0].Rows[i][0].ToString().Trim() + "'");
strSql2.Append(" and DeptID=");
strSql2.Append(ds.Tables[0].Rows[i][6].ToString().Trim().GetDeptIDByDeptName() + " ");
}
else
{
//如果没有查到上级的ID,默认为Admin的ID 1
strSql2.Append(" update HT_User set ParentId=1 ");
strSql2.Append(" where UserName='");
strSql2.Append(ds.Tables[0].Rows[i][0].ToString().Trim() + "'");
strSql2.Append(" and DeptID=");
strSql2.Append(ds.Tables[0].Rows[i][6].ToString().Trim().GetDeptIDByDeptName() + " ");
}
}
catch (Exception)
{
context.Response.Write(ExtensionType.JsonError("操作失败:数据有问题,请核对数据!"));
return;
}
}
SqlList2.Add(strSql2.ToString());
int count2 = HtCRM.DbUtility.AosySql.ExecuteListSQL(SqlList2);
if (count2 == SqlList2.Count)
{
string str = "导入员工成功!";
str.AddToLog();
context.Response.Write(ExtensionType.JsonOk("导入成功!", "14"));
}
else
{
context.Response.Write(ExtensionType.JsonError("操作失败:数据有问题,请核对数据!"));
}
}
}
else
{
context.Response.Write(ExtensionType.JsonError("操作失败:导入数据为空!"));
}
}
catch (Exception)
{
context.Response.Write(ExtensionType.JsonError("操作失败."));
}
}
catch (Exception)
{
context.Response.Write(ExtensionType.JsonError("操作失败."));
}