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