/// <summary> /// 生成字段和列,并绑定数据源 /// </summary> /// <param name="sourceFileName">文件名</param> /// <param name="targTableName">目标表名选中的表</param> /// <param name="_gp"></param> /// <param name="_store"></param> public JsonResult BindData() { string StrError = null;//返回错误原因 //清除旧数据与记录集 //(1)读取目标表的所有列数据Session["uploadPath"] string uploadPath = Session["uploadPath"].ToString(); string targTableName = Session["TargetTable"].ToString();//获取目标表 string myFiledsSql = @"SELECT syscolumns.name,systypes.name type,syscolumns.isnullable, syscolumns.length FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = object_id('" + targTableName + "')"; IList<ImportTableFieldStruct> structList = new List<ImportTableFieldStruct>(); IList<ColumeStructs> colsList = new List<ColumeStructs>(); DataTable targetTableStru = null;//目标表表结构数据 //示例: //name type isnullable length // ID int 0 4 //S_SubName nvarchar 1 40 //I_ParentID int 1 4 //S_Memo nvarchar 1 40 //I_Level int 1 4 //I_Sort int 1 4 try { Database db = DBHelper.CreateDataBase("PlatformData"); DbCommand cmd = db.GetSqlStringCommand(myFiledsSql); using (DataSet myDs = db.ExecuteDataSet(cmd)) { targetTableStru = myDs.Tables[0]; for (int i2 = 0; i2 < targetTableStru.Rows.Count; i2++) { ColumeStructs colsItem = new ColumeStructs(); colsItem.ColName = targetTableStru.Rows[i2][0].ToString(); colsItem.ColType = targetTableStru.Rows[i2][1].ToString(); colsItem.ColNullable = targetTableStru.Rows[i2][2].ToString(); colsItem.ColLength = targetTableStru.Rows[i2][3].ToString(); colsList.Add(colsItem);//DataTable属性 } } } catch (Exception e2) { StrError = "出错了!" + e2.Message.ToString(); return Json(new { structList, StrError }, JsonRequestBehavior.AllowGet); } //(3)读入源数据的Excel文件; string excelPath = Server.MapPath(uploadPath); DataTable dt = null; if (!System.IO.File.Exists(excelPath)) { return null; } try { dt = new RSExcelReader().getTableByExcelpath(excelPath); //读取数据到表中,第一行为列的说明名,第二行为列名 } catch (Exception e3) { StrError = "出错了,读取Excel错误:" + e3.Message.ToString(); return Json(new { structList, StrError }, JsonRequestBehavior.AllowGet); } //(4)判断此数据是否合格; DataTable dt_Error = dt.Clone(); //添加一个一样结构的table dt_Error.Columns.Add("异常原因"); int I_TeaDetailsCount = dt.Rows.Count; if (I_TeaDetailsCount < 1) { StrError = "出错了,读取Excel格式错误,行数太少!"; return Json(new { structList, StrError }, JsonRequestBehavior.AllowGet); } //(5)读取有效行,并写入临时类 //只处理第二列 DataRow sourcRow = dt.Rows[0]; int colCount = dt.Columns.Count; for (int j = 0; j < colCount; j++) { ImportTableFieldStruct strutItem = new ImportTableFieldStruct(); //SourceFieldMem表字段说明 strutItem.SourceFieldMem = dt.Columns[j].ColumnName;//源表说明 string tmpField = sourcRow[j].ToString();//源表字段名 string[] myFields = tmpField.Split(';');//源表字段可以指定目标字段,以;号分隔 //查询与源表字段名相同的名称作为匹配字段,如果找不到,返回为空 strutItem.SoureFieldName = myFields[0];// int taregIndex = CheckTareget(strutItem.SoureFieldName, targetTableStru);//taregIndex为对应的字段的所在行targetTableStru从数据库里面获取的字段 if (taregIndex > -1) { //找到了同名列,需要处理 strutItem.TargetFieldName = targetTableStru.Rows[taregIndex][0].ToString(); strutItem.TargetFieldType = targetTableStru.Rows[taregIndex][1].ToString(); strutItem.TargetFieldLength = targetTableStru.Rows[taregIndex][3].ToString(); strutItem.TargetFieldNullable = targetTableStru.Rows[taregIndex][2].ToString(); } if (myFields.Length == 6) { //有第三方列 strutItem.TargetFieldName = myFields[1]; //返回的对应目标字段 strutItem.SecTableName = myFields[2]; strutItem.SecFieldName = myFields[3]; strutItem.SecFieldType = myFields[4].ToLower();//字段类型,小写 strutItem.SecFieldLength = myFields[5]; } strutItem.GenID = (j + 1); structList.Add(strutItem); } Session["ImportTbStructs"] = structList;//临时表 return Json(new { structList, StrError }, JsonRequestBehavior.AllowGet); }
/// <summary> /// 根据提交的数据进行导入 /// </summary>//返回两个数据, 一个表示状态 出错地方,,仅仅在成功时表现插入的条数 /// <param name="sender"></param> /// <param name="e"></param> /// protected void btnImportDataClick(object sender, DirectEventArgs e) public JsonResult btnImportDataClick(String tableName, String cityCode, String attrArr) { Database db = DBHelper.CreateDataBase("PlatformData"); //接受并保存excel string path = Server.MapPath("/uploadExcel"); HttpFileCollection files = System.Web.HttpContext.Current.Request.Files; if (files.Count <= 0) { return Json("没有接受到文件", JsonRequestBehavior.AllowGet); } if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } String name = files[0].FileName; string savePaht = Server.MapPath("/uploadExcel/" + name); files[0].SaveAs(savePaht); String StrErrorOrNot = "";//表示错误原因或者正确导入时返回的值的相关信息 //(1)取导入表结构 //(2)读到数据,准备循环处理; string targTableName = tableName;//目标表名 string excelPath = savePaht; DataTable dt = null, dtSec = null; if (!System.IO.File.Exists(excelPath)) { return null; } try { dt = new RSExcelReader().getTableByExcelpath(excelPath); //读取数据到表中,第一行为列的说明名,第二行为列名 } catch (Exception e3) { StrErrorOrNot = "出错了,读取Excel错误:" + e3.Message.ToString(); return Json(StrErrorOrNot, JsonRequestBehavior.AllowGet); } //(4)判断此数据是否合格; int I_TeaDetailsCount = dt.Rows.Count; if (I_TeaDetailsCount < 2) { StrErrorOrNot = "出错了,读取Excel格式错误,行数太少!"; return Json(StrErrorOrNot, JsonRequestBehavior.AllowGet); } int dtCount = dt.Rows.Count;//总行数 int dtColCount = dt.Columns.Count;//总列数 DbCommand cmd; StringBuilder insertSQL = new StringBuilder(), fieldSQL = new StringBuilder(), valueSQL = new StringBuilder(); //取已有的指标和指标单位 List<String> indCodeList = new List<string>(); List<String> unitList = new List<string>(); string insertSecSQL = ""; List<string> filedName = new List<string>(); for (int i = 1; i < dtColCount; i++) { String unit = dt.Rows[0][i] == null ? "null" : dt.Rows[0][i].ToString(); unitList.Add(unit); indCodeList.Add(dt.Rows[1][i].ToString()); } for (int i = 2; i < dtCount; i++) { for (int j = 1; j < dtColCount; j++) { String value = dt.Rows[i][j].ToString(); if (value.Equals("-")) { continue; } insertSQL.Append("INSERT INTO [HYCtbuEco].[dbo].[" + tableName + "]( "); insertSQL.Append("[STimeCode],[IAreaID],[SIndCode],[FValue],[SUnit],[SMemo],[ITimeID],[IIndID]"); insertSQL.Append(" ) VALUES "); valueSQL.Append("("); String timeCode = dt.Rows[i][0].ToString(); valueSQL.Append("'" + timeCode + "',"); valueSQL.Append("'" + cityCode + "',"); String indCode = indCodeList[j - 1]; valueSQL.Append("'" + indCode + "',"); valueSQL.Append(value + ","); String unit = unitList[j - 1].Equals("null") ? "null" : "'" + unitList[j - 1] + "'"; valueSQL.Append(unit + ","); valueSQL.Append("null,");//对应SMemo IEnumerable<TbDimTime> timelist = MyCache.GetTimeId().Where(a => a.STimeCode == timeCode); TbDimTime time = timelist.ToList().Count > 0 ? timelist.First() : null; if (time == null) { valueSQL.Append("null,");//对应ITimeID } else { valueSQL.Append("'" + time.Id + "',"); } IEnumerable<TbDimIndicator> indicatorlist = MyCache.GetIndId().Where(a => a.SIndCode == indCode); TbDimIndicator indicator = indicatorlist.ToList().Count > 0 ? indicatorlist.First() : null; if (indicator == null) { valueSQL.Append("null");//对应IIndID } else { valueSQL.Append("'" + indicator.Id + "'"); } valueSQL.Append(");"); insertSQL.Append(valueSQL); insertSecSQL += insertSQL; insertSQL.Clear(); valueSQL.Clear(); } } try { cmd = db.GetSqlStringCommand(insertSecSQL); db.ExecuteNonQuery(cmd); } catch (Exception e) { return Json("数据库错误,上传失败", JsonRequestBehavior.AllowGet); } return Json("上传成功", JsonRequestBehavior.AllowGet); }