/// <summary> /// 将Excel数据导入到List表链中 /// </summary> /// <param name="xlstable">Excel文件名</param> /// <param name="xlssheet">工作簿</param> /// <returns></returns> protected static bool ImportExcelToList(string ExcelFileName, string xlssheet) { try { //打开Excel表,并写入DataSet中。 //string xlsfile = ExcelFileName; //string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "data source=" + xlsfile // + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + ExcelFileName + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataSet ds = new DataSet(); OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + xlssheet + "$]", conn); odda.Fill(ds, "table1"); conn.Close(); //读取DataSet数据集 ltExcelTags.Clear(); //////////////////////////////////////////////////////////////// System.Data.DataTable dt = ds.Tables[0]; int nAll = dt.Rows.Count; int nValid = 0; foreach (System.Data.DataRow dr in dt.Rows) { if (dr["TagID"].ToString() != "") { WS_ExcelTag exlstag = new WS_ExcelTag(); exlstag.TagID = dr["TagID"].ToString().Trim(); exlstag.UnitCode = dr["UnitCode"].ToString().Trim(); exlstag.TagCode = dr["TagCode"].ToString().Trim(); exlstag.TagDesc = dr["TagDesc"].ToString().Trim(); exlstag.TagType = dr["TagType"].ToString().Trim(); exlstag.TagEngunit = dr["TagEngunit"].ToString().Trim(); exlstag.TagIsValid = dr["TagIsValid"].ToString().Trim(); exlstag.TagRunExp = dr["TagRunExp"].ToString().ToUpper().Trim(); exlstag.TagShiftExp = dr["TagShiftExp"].ToString().ToUpper().Trim(); exlstag.TagPeriodExp = dr["TagPeriodExp"].ToString().ToUpper().Trim(); exlstag.TagMWExp = dr["TagMWExp"].ToString().ToUpper().Trim(); exlstag.TagSnapExp = dr["TagSnapExp"].ToString().ToUpper().Trim(); exlstag.TagTargetExp = dr["TagTargetExp"].ToString().ToUpper().Trim(); exlstag.TagScoreExp = dr["TagScoreExp"].ToString().ToUpper().Trim(); exlstag.TagPeriodExp = dr["TagPeriodExp"].ToString().ToUpper().Trim(); //sub tag ltExcelTags.Add(exlstag); nValid += 1; } } string strInfor = "Excel表总数为:{0}个, 【ID不为空】有效点为:{1}个。"; strInfor = string.Format(strInfor, nAll, nValid); LogUtil.LogMessage(strInfor); return(true); } catch (Exception ex) { LogUtil.LogMessage("Excel表读取失败:" + ex.ToString()); return(false); } }
/// <summary> /// 将Excel数据导入到List表链中 /// </summary> /// <param name="xlstable">Excel文件名</param> /// <param name="xlssheet">工作簿</param> /// <returns></returns> protected static bool ImportExcelToList(string ExcelFileName, string xlssheet) { try { //打开Excel表,并写入DataSet中。 //string xlsfile = ExcelFileName; //string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "data source=" + xlsfile // + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + ExcelFileName + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataSet ds = new DataSet(); OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + xlssheet + "$]", conn); odda.Fill(ds, "table1"); conn.Close(); //读取DataSet数据集 ltExcelTags.Clear(); //////////////////////////////////////////////////////////////// System.Data.DataTable dt = ds.Tables[0]; int nAll = dt.Rows.Count; int nValid = 0; foreach (System.Data.DataRow dr in dt.Rows) { if (dr["TagID"].ToString() != "") { WS_ExcelTag exlstag = new WS_ExcelTag(); exlstag.TagID = dr["TagID"].ToString().Trim(); exlstag.UnitCode = dr["UnitCode"].ToString().Trim(); exlstag.TagCode = dr["TagCode"].ToString().Trim(); exlstag.TagDesc = dr["TagDesc"].ToString().Trim(); exlstag.TagType = dr["TagType"].ToString().Trim(); exlstag.TagEngunit = dr["TagEngunit"].ToString().Trim(); exlstag.TagIsValid = dr["TagIsValid"].ToString().Trim(); exlstag.TagRunExp = dr["TagRunExp"].ToString().ToUpper().Trim(); exlstag.TagShiftExp = dr["TagShiftExp"].ToString().ToUpper().Trim(); exlstag.TagPeriodExp = dr["TagPeriodExp"].ToString().ToUpper().Trim(); exlstag.TagMWExp = dr["TagMWExp"].ToString().ToUpper().Trim(); exlstag.TagSnapExp = dr["TagSnapExp"].ToString().ToUpper().Trim(); exlstag.TagTargetExp = dr["TagTargetExp"].ToString().ToUpper().Trim(); exlstag.TagScoreExp = dr["TagScoreExp"].ToString().ToUpper().Trim(); exlstag.TagPeriodExp = dr["TagPeriodExp"].ToString().ToUpper().Trim(); //sub tag ltExcelTags.Add(exlstag); nValid += 1; } } string strInfor = "Excel表总数为:{0}个, 【ID不为空】有效点为:{1}个。"; strInfor = string.Format(strInfor, nAll, nValid); LogUtil.LogMessage(strInfor); return true; } catch (Exception ex) { LogUtil.LogMessage("Excel表读取失败:"+ex.ToString()); return false; } }