protected void btnImport_Click(object sender, EventArgs e) { if (fileUp.HasFile == false)//HasFile用来检查FileUpload是否有指定文件 { JScript.Alert("请您选择Excel文件"); return; } string IsXls = System.IO.Path.GetExtension(fileUp.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名 if ((IsXls != ".xls")&&(IsXls!=".xlsx")) { JScript.Alert("只可以选择Excel文件"); return; } //获取Execle文件名 DateTime日期函数 string filename = fileUp.FileName; //Server.MapPath 获得虚拟服务器相对路径 string savePath = Server.MapPath(("upfiles\\") + filename); //SaveAs 将上传的文件内容保存在服务器上 fileUp.SaveAs(savePath); //连接Excel 读取Excel数据 并返回DataSet数据集合 DataSet ds = GridViewExportUtil.ExcelSqlConnection(savePath, filename, "五大集团"); //定义一个DataRow数组 DataRow[] dr = ds.Tables[0].Select(); int rowsnum = ds.Tables[0].Rows.Count; if (rowsnum == 0) { JScript.Alert("Excel表为空表,无数据!"); } else { List<DataInfo> infoList = new List<DataInfo>(); //时间 //string time = dr[1][1].ToString(); //for (int i = 2; i < dr.Length - 1; ) //{ // int j; // //合并五行 // for (j = 0; j < 5; j++) // { // DataInfo info = new DataInfo(); // if (String.IsNullOrEmpty(dr[i][0].ToString())) // { // break; // } // //防止超出excel行数 // if (i + j > dr.Length-1) // { // break; // } // info.T_COMPANY = dr[i][0].ToString(); // info.T_DATATYPE = dr[i + j][1].ToString(); // info.T_900_SL = dr[i + j][2].ToString(); // info.T_900_KL = dr[i + j][3].ToString(); // info.T_600_HJ = dr[i + j][4].ToString(); // info.T_600_CCL = dr[i + j][5].ToString(); // info.T_600_CLS = dr[i + j][6].ToString(); // info.T_600_CLK = dr[i + j][7].ToString(); // info.T_600_YLS = dr[i + j][8].ToString(); // info.T_600_YLK = dr[i + j][9].ToString(); // info.T_600_J = dr[i + j][10].ToString(); // info.T_350_HJ = dr[i + j][11].ToString(); // info.T_350_CNHJ = dr[i + j][12].ToString(); // info.T_350_CLJ = dr[i + j][13].ToString(); // info.T_350_YLJ = dr[i + j][14].ToString(); // info.T_350_GRHJ = dr[i + j][15].ToString(); // info.T_350_RCLJ = dr[i + j][16].ToString(); // info.T_350_RYLJ = dr[i + j][17].ToString(); // info.T_300_HJ = dr[i + j][18].ToString(); // info.T_300_CGSL = dr[i + j][19].ToString(); // info.T_300_CGKL = dr[i + j][20].ToString(); // info.T_300_LHC = dr[i + j][21].ToString(); // info.T_300_SL = dr[i + j][22].ToString(); // info.T_300_KL = dr[i + j][23].ToString(); // info.T_200_HJ = dr[i + j][24].ToString(); // info.T_200_SL = dr[i + j][25].ToString(); // info.T_200_KL = dr[i + j][26].ToString(); // info.T_200_LHC = dr[i + j][27].ToString(); // info.T_200_J = dr[i + j][28].ToString(); // info.T_120_HJ = dr[i + j][29].ToString(); // info.T_120_J = dr[i + j][30].ToString(); // info.T_120_LHC = dr[i + j][31].ToString(); // info.T_120_KL = dr[i + j][32].ToString(); // info.T_120_JC = dr[i + j][33].ToString(); // info.T_135_LHC = dr[i + j][34].ToString(); // info.T_100_HJ = dr[i + j][35].ToString(); // info.T_100_CG = dr[i + j][36].ToString(); // info.T_100_LHC = dr[i + j][37].ToString(); // info.T_100_J = dr[i + j][38].ToString(); // info.T_90_HJ = dr[i + j][39].ToString(); // info.T_90_NJ = dr[i + j][40].ToString(); // info.T_90_LHC = dr[i + j][41].ToString(); // info.T_90_RJ = dr[i + j][42].ToString(); // infoList.Add(info); // } // i = i + j; //} string time = DateTime.Now.Year.ToString(); int lee=dr[0][0].ToString().Length; string month = dr[0][0].ToString().TrimStart().Substring(3,1); int resul = 0; if (int.TryParse(month, out resul)) { month = dr[0][0].ToString().Substring(2, 2); } else { month ="0"+dr[0][0].ToString().Substring(2, 1); } time += "-" + month; //dateTime = DateTime.Parse(time); for (int i = 3; i < dr.Length - 1; ) { if (String.IsNullOrEmpty(dr[i][0].ToString())) { break; } int j; string company = dr[i][0].ToString(); //合并八行 for (j = 0; j < 8; j++) { DataInfo info = new DataInfo(); //防止超出excel行数 if (i + j > dr.Length - 1) { break; } if ((dr[i + j][1].ToString().Trim() != "供电煤耗") && (dr[i + j][1].ToString().Trim() != "供电煤耗最优值") && (dr[i + j][1].ToString().Trim() != "供电煤耗最优机组")) { continue; } switch (dr[i + j][1].ToString().Trim()) { case "供电煤耗": info.T_DATATYPE = "平均值"; break; case "供电煤耗最优值": info.T_DATATYPE = "最优值"; break; case "供电煤耗最优机组": info.T_DATATYPE = "最优机组"; break; } info.T_COMPANY = company; info.T_TIME = time; info.T_900_SL = dr[i + j][2].ToString(); info.T_900_KL = dr[i + j][3].ToString(); info.T_600_HJ = dr[i + j][4].ToString(); info.T_600_CCL = dr[i + j][5].ToString(); info.T_600_CLS = dr[i + j][6].ToString(); info.T_600_CLK = dr[i + j][7].ToString(); info.T_600_YLS = dr[i + j][8].ToString(); info.T_600_YLK = dr[i + j][9].ToString(); info.T_600_J = dr[i + j][10].ToString(); info.T_350_HJ = dr[i + j][11].ToString(); info.T_350_CNHJ = dr[i + j][12].ToString(); info.T_350_CLJ = dr[i + j][13].ToString(); info.T_350_YLJ = dr[i + j][14].ToString(); info.T_350_GRHJ = dr[i + j][15].ToString(); info.T_350_RCLJ = dr[i + j][16].ToString(); info.T_350_RYLJ = dr[i + j][17].ToString(); info.T_300_HJ = dr[i + j][18].ToString(); info.T_300_CGSL = dr[i + j][19].ToString(); info.T_300_CGKL = dr[i + j][20].ToString(); info.T_300_LHC = dr[i + j][21].ToString(); info.T_300_SL = dr[i + j][22].ToString(); info.T_300_KL = dr[i + j][23].ToString(); info.T_200_HJ = dr[i + j][24].ToString(); info.T_200_SL = dr[i + j][25].ToString(); info.T_200_KL = dr[i + j][26].ToString(); info.T_200_LHC = dr[i + j][27].ToString(); info.T_200_J = dr[i + j][28].ToString(); info.T_120_HJ = dr[i + j][29].ToString(); info.T_120_J = dr[i + j][30].ToString(); info.T_120_LHC = dr[i + j][31].ToString(); info.T_120_KL = dr[i + j][32].ToString(); info.T_120_JC = dr[i + j][33].ToString(); info.T_135_LHC = dr[i + j][34].ToString(); info.T_100_HJ = dr[i + j][35].ToString(); info.T_100_CG = dr[i + j][36].ToString(); info.T_100_LHC = dr[i + j][37].ToString(); info.T_100_J = dr[i + j][38].ToString(); info.T_90_HJ = dr[i + j][39].ToString(); info.T_90_NJ = dr[i + j][40].ToString(); info.T_90_LHC = dr[i + j][41].ToString(); info.T_90_RJ = dr[i + j][42].ToString(); infoList.Add(info); } i=i+8; //再增加最差机组和最差值 DataInfo infos = new DataInfo(); infos.T_COMPANY = company; infos.T_TIME = time; infos.T_DATATYPE = "最差值"; infoList.Add(infos); DataInfo infoss = new DataInfo(); infoss.T_COMPANY = company; infoss.T_TIME = time; infoss.T_DATATYPE = "最差机组"; infoList.Add(infoss); } try { if (bc.InsertData(infoList, out errMsg)) { JScript.Alert("Excle表导入成功"); } else { JScript.Alert("Excle表导入失败"); } } catch { JScript.Alert("Excle表导入失败"); } } }
/// <summary> /// 获取数据对标信息 /// </summary> /// <param name="errMsg"></param> /// <param name="time">查询时间</param> /// <returns></returns> public List<DataInfo> GetInfo(string time, out string errMsg) { this.init(); errMsg = ""; List<DataInfo> infoList = new List<DataInfo>(); string sql = "select * FROM T_INFO_DATASTANDARD"; if (!string.IsNullOrEmpty(time)) { sql += " WHERE T_TIME='" + time + "'"; } DataTable dt = null; if (rlDBType == "SQL") { // sql = "select ID_KEY,班次名,起始时间,结束时间 from 班次时间表 order by ID_KEY"; //dt = DBsql.RunDataTable(sql, out errMsg); } else { dt = DBdb2.RunDataTable(sql, out errMsg); } if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { DataInfo info = new DataInfo(); //info.ParaId = String.IsNullOrEmpty(dt.Rows[i]["T_PARAID"].ToString()) ? String.Empty : dt.Rows[i]["T_PARAID"].ToString(); info.T_COMPANY = dt.Rows[i]["T_COMPANY"].ToString(); info.T_DATATYPE = dt.Rows[i]["T_DATATYPE"].ToString(); info.T_900_SL = dt.Rows[i]["T_900_SL"].ToString(); info.T_900_KL = dt.Rows[i]["T_900_KL"].ToString(); info.T_600_HJ = dt.Rows[i]["T_600_HJ"].ToString(); info.T_600_CCL = dt.Rows[i]["T_600_CCL"].ToString(); info.T_600_CLS = dt.Rows[i]["T_600_CLS"].ToString(); info.T_600_CLK = dt.Rows[i]["T_600_CLK"].ToString(); info.T_600_YLS = dt.Rows[i]["T_600_YLS"].ToString(); info.T_600_YLK = dt.Rows[i]["T_600_YLK"].ToString(); info.T_600_J = dt.Rows[i]["T_600_J"].ToString(); info.T_350_HJ = dt.Rows[i]["T_350_HJ"].ToString(); info.T_350_CNHJ = dt.Rows[i]["T_350_CNHJ"].ToString(); info.T_350_CLJ = dt.Rows[i]["T_350_CLJ"].ToString(); info.T_350_YLJ = dt.Rows[i]["T_350_YLJ"].ToString(); info.T_350_GRHJ = dt.Rows[i]["T_350_GRHJ"].ToString(); info.T_350_RCLJ = dt.Rows[i]["T_350_RCLJ"].ToString(); info.T_350_RYLJ = dt.Rows[i]["T_350_RYLJ"].ToString(); info.T_300_HJ = dt.Rows[i]["T_300_HJ"].ToString(); info.T_300_CGSL = dt.Rows[i]["T_300_CGSL"].ToString(); info.T_300_CGKL = dt.Rows[i]["T_300_CGKL"].ToString(); info.T_300_LHC = dt.Rows[i]["T_300_LHC"].ToString(); info.T_300_SL = dt.Rows[i]["T_300_SL"].ToString(); info.T_300_KL = dt.Rows[i]["T_300_KL"].ToString(); info.T_200_HJ = dt.Rows[i]["T_200_HJ"].ToString(); info.T_200_SL = dt.Rows[i]["T_200_SL"].ToString(); info.T_200_KL = dt.Rows[i]["T_200_KL"].ToString(); info.T_200_LHC = dt.Rows[i]["T_200_LHC"].ToString(); info.T_200_J = dt.Rows[i]["T_200_J"].ToString(); info.T_120_HJ = dt.Rows[i]["T_120_HJ"].ToString(); info.T_120_J = dt.Rows[i]["T_120_J"].ToString(); info.T_120_LHC = dt.Rows[i]["T_120_LHC"].ToString(); info.T_120_KL = dt.Rows[i]["T_120_KL"].ToString(); info.T_120_JC = dt.Rows[i]["T_120_JC"].ToString(); info.T_135_LHC = dt.Rows[i]["T_135_LHC"].ToString(); info.T_100_HJ = dt.Rows[i]["T_100_HJ"].ToString(); info.T_100_CG = dt.Rows[i]["T_100_CG"].ToString(); info.T_100_LHC = dt.Rows[i]["T_100_LHC"].ToString(); info.T_100_J = dt.Rows[i]["T_100_J"].ToString(); info.T_90_HJ = dt.Rows[i]["T_90_HJ"].ToString(); info.T_90_NJ = dt.Rows[i]["T_90_NJ"].ToString(); info.T_90_LHC = dt.Rows[i]["T_90_LHC"].ToString(); info.T_90_RJ = dt.Rows[i]["T_90_RJ"].ToString(); infoList.Add(info); } } //去掉重复。 //return infoList.Distinct(new EqualCompare<ParaTableInfo>((x, y) => (x != null && y != null) &&(x.OutTableName == y.OutTableName))).ToList(); //不能过滤,因为会把ParaId过滤掉。以ParaId来取值的。 return infoList; }