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;
}