EXCEL导入解析

最新推荐文章于 2022-07-13 21:09:50 发布

wyz_199102

最新推荐文章于 2022-07-13 21:09:50 发布

阅读量260

点赞数

本文链接：https://blog.csdn.net/wyz_199102/article/details/51424924

版权

#region 出让计划导入
if (!String.IsNullOrEmpty(strPath))
{
//解析EXCLE文件
//Excel 中的数据集
DataSet ds = null;
//数据表
DataTable dt = new DataTable();
//连接字符串
string strCon = string.Empty;
string strExt = strPath.Substring(strPath.LastIndexOf(".") + 1).ToLower();
//判断文件后缀名
if (strExt == "xls")// 2003格式
{
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties='Excel 8.0;IMEX=1';Persist Security Info=True";
}
else if (strExt == "xlsx") //2007或以上格式
{
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";Extended Properties='Excel 12.0;IMEX=1';Persist Security Info=True";
}
//Excel 连接实例化
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
try
{
//打开Excel的连接
Conn.Open();
//查询SQL语句默认查询第一个工作表（默认总共三个）
string strCom = "SELECT * FROM [Sheet1$]";
//执行查询
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
ds = new DataSet();
//将查询到的数据填充到DataSet中，并设置表名为第一个表单的名称“[Sheet1$]”
myCommand.Fill(ds, "[Sheet1$]");
dt = ds.Tables[0];
string nf =dt.Columns[0].ToString().Substring(0,4);
//添加数据
int MAXCBBP_CRJHOID = 0;
string sqlString = "SELECT max(oid) FROM CBBP_CRJH";
string dt_maxoid = BP.DA.DBAccess.RunSQLReturnString(sqlString);
if (!String.IsNullOrEmpty(dt_maxoid))
{
MAXCBBP_CRJHOID = Convert.ToInt32(dt_maxoid) + 1;
}
string XSQ = "01";
for (int i = 2; i < dt.Rows.Count; i++)
{
string oneLineValue = dt.Rows[i][0].ToString().Trim();
string twoTitle = dt.Rows[i][1].ToString().Trim();
if (String.IsNullOrEmpty(oneLineValue))
{
continue;
}
else if (String.IsNullOrEmpty(twoTitle))
{
XSQ = GetXSQIDforXSQName(oneLineValue.Trim().Replace("\n", ""), "SZXZQ");
continue;
}
else
{
try
{
//添加表数据
string TDYT = GetXSQIDforXSQName(dt.Rows[i][4].ToString().Trim().Replace("\n",""), "SF_TDYT");
string ZDBM = dt.Rows[i][0].ToString().Trim().Replace("\n","");
string ZDMC = dt.Rows[i][1].ToString().Trim().Replace("\n", "");
string DKZL = dt.Rows[i][2].ToString().Trim().Replace("\n", "");
string ZDMJ = dt.Rows[i][3].ToString().Trim().Replace("\n", "");
string BZ = dt.Rows[i][5].ToString().Trim().Replace("\n", "");
string inSql = string.Format("insert into CBBP_CRJH (OID,SZXZQ2,ZDBM,ZDMC,DKZL,ZDMJ,TDYT,BZ,XZSJ) VALUES ({0},{1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}')", MAXCBBP_CRJHOID, XSQ, ZDBM, ZDMC, DKZL, ZDMJ, TDYT, BZ,nf);
if (BP.DA.DBAccess.RunSQL(inSql) > 0)
{
MAXCBBP_CRJHOID++;
}
}
catch (Exception)
{
strData += dt.Rows[i][1].ToString() + "添加失败；";
}

}
}
}
catch (Exception)
{
}
finally
{
//关闭Excel的链接
Conn.Close();
if (!File.Exists(strPath))
{
File.Delete(strPath);
}

}

}
#endregion