SQLSERVER是这么写的:count = 0;//计数
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelFileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
string strExcel = string.Format("select * from [{0}$]", strSheetName);//test
StringBuilder sb = new StringBuilder();
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName);
conn.Close();
}
DataTable dt = ds.Tables[0];
MCT_YYEQKB mCT_YYEQKB = new MCT_YYEQKB();
foreach (DataRow dr in dt.Rows)
{
//避免空数据干扰
if (dr["妇女ID"].ToString() == "")
{
break;
}
try
{
mCT_YYEQKB.MCC_C0_01 = dr[0].ToString().Trim();
mCT_YYEQKB.MCC_C0_02 = dr[1].ToString().Trim();
mCT_YYEQKB.MCC_C2_01 = dr[2].ToString().Trim();
mCT_YYEQKB.MCC_C2_02 = dr[3].ToString().Trim();
mCT_YYEQKB.MCC_C2_03 = dr[4].ToString().Trim();
mCT_YYEQKB.MCC_C2_04 = dr[5].ToString().Trim();
mCT_YYEQKB.MCC_C2_05 = dr[6].ToString().Trim();
mCT_YYEQKB.MCC_C2_06 = dr[7].ToString().Trim();
mCT_YYEQKB.MCC_C2_07 = dr[8].ToString().Trim();
mCT_YYEQKB.MCC_C2_08 = dr[9].ToString().Trim();
mCT_YYEQKB.MCC_C2_09 = dr[10].ToString().Trim();
mCT_YYEQKB.MCC_C2_10 = dr[11].ToString().Trim();
mCT_YYEQKB.MCC_C2_11 = dr[12].ToString().Trim();
mCT_YYEQKB.MCC_C2_12 = dr[13].ToString().Trim();
mCT_YYEQKB.MCC_C2_13 = dr[14].ToString().Trim();
mCT_YYEQKB.MCC_C2_14 = dr[15].ToString().Trim();
mCT_YYEQKB.MCC_C2_15 = dr[16].ToString().Trim();
mCT_YYEQKB.MCC_C2_16 = dr[17].ToString().Trim();
mCT_YYEQKB.MCC_C2_17 = dr[18].ToString().Trim();
mCT_YYEQKB.MCC_C2_18 = dr[19].ToString().Trim();
mCT_YYEQKB.MCC_C2_19 = dr[20].ToString().Trim();
mCT_YYEQKB.MCC_C2_20 = dr[21].ToString().Trim();
mCT_YYEQKB.MCC_C2_21 = dr[22].ToString().Trim();
mCT_YYEQKB.MCC_C2_22 = dr[23].ToString().Trim();
mCT_YYEQKB.MCC_C2_23 = dr[24].ToString().Trim();
mCT_YYEQKB.MCC_C2_24 = dr[25].ToString().Trim();
mCT_YYEQKB.MCC_C2_25 = dr[26].ToString().Trim();
mCT_YYEQKB.MCC_C2_26 = dr[27].ToString().Trim();
mCT_YYEQKB.MCC_C2_27 = dr[28].ToString().Trim();
mCT_YYEQKB.MCC_C2_28 = dr[29].ToString().Trim();
mCT_YYEQKB.MCC_C2_29 = dr[30].ToString().Trim();
mCT_YYEQKB.MCC_C2_30 = dr[31].ToString().Trim();
mCT_YYEQKB.MCC_C2_31 = dr[32].ToString().Trim();
mCT_YYEQKB.MCC_C2_32 = dr[33].ToString().Trim();
mCT_YYEQKB.MCC_C2_33 = dr[34].ToString().Trim();
mCT_YYEQKB.MCC_C2_34 = dr[35].ToString().Trim();
mCT_YYEQKB.MCC_C2_35 = dr[36].ToString().Trim();
mCT_YYEQKB.MCC_C2_36 = dr[37].ToString().Trim();
mCT_YYEQKB.MCC_C2_37 = dr[38].ToString().Trim();
mCT_YYEQKB.MCC_C2_38 = dr[39].ToString().Trim();
mCT_YYEQKB.MCC_C2_39 = dr[40].ToString().Trim();
mCT_YYEQKB.MCC_C2_40 = dr[41].ToString().Trim();
mCT_YYEQKB.MCC_C2_41 = dr[42].ToString().Trim();
mCT_YYEQKB.MCC_C2_42 = dr[43].ToString().Trim();
mCT_YYEQKB.MCC_C2_43 = dr[44].ToString().Trim();
string s = "insert into MCT_YYEQKB values('" + mCT_YYEQKB.MCC_C0_01 + "','" + mCT_YYEQKB.MCC_C0_02 + "','" + mCT_YYEQKB.MCC_C2_01 + "','" + mCT_YYEQKB.MCC_C2_02 + "','" + mCT_YYEQKB.MCC_C2_03 + "','" + mCT_YYEQKB.MCC_C2_04 + "','" + mCT_YYEQKB.MCC_C2_05 + "','" + mCT_YYEQKB.MCC_C2_06 + "','" + mCT_YYEQKB.MCC_C2_07 + "','" + mCT_YYEQKB.MCC_C2_08 + "','" + mCT_YYEQKB.MCC_C2_09 + "','" + mCT_YYEQKB.MCC_C2_10 + "','" + mCT_YYEQKB.MCC_C2_11 + "','" + mCT_YYEQKB.MCC_C2_12 + "','" + mCT_YYEQKB.MCC_C2_13 + "','" + mCT_YYEQKB.MCC_C2_14 + "','" + mCT_YYEQKB.MCC_C2_15 + "','" + mCT_YYEQKB.MCC_C2_16 + "','" + mCT_YYEQKB.MCC_C2_17 + "','" + mCT_YYEQKB.MCC_C2_18 + "','" + mCT_YYEQKB.MCC_C2_19 + "','" + mCT_YYEQKB.MCC_C2_20 + "','" + mCT_YYEQKB.MCC_C2_21 + "','" + mCT_YYEQKB.MCC_C2_22 + "','" + mCT_YYEQKB.MCC_C2_23 + "','" + mCT_YYEQKB.MCC_C2_24 + "','" + mCT_YYEQKB.MCC_C2_25 + "','" + mCT_YYEQKB.MCC_C2_26 + "','" + mCT_YYEQKB.MCC_C2_27 + "','" + mCT_YYEQKB.MCC_C2_28 + "','" + mCT_YYEQKB.MCC_C2_29 + "','" + mCT_YYEQKB.MCC_C2_30 + "','" + mCT_YYEQKB.MCC_C2_31 + "','" + mCT_YYEQKB.MCC_C2_32 + "','" + mCT_YYEQKB.MCC_C2_33 + "','" + mCT_YYEQKB.MCC_C2_34 + "','" + mCT_YYEQKB.MCC_C2_35 + "','" + mCT_YYEQKB.MCC_C2_36 + "','" + mCT_YYEQKB.MCC_C2_37 + "','" + mCT_YYEQKB.MCC_C2_38 + "','" + mCT_YYEQKB.MCC_C2_39 + "','" + mCT_YYEQKB.MCC_C2_40 + "','" + mCT_YYEQKB.MCC_C2_41 + "','" + mCT_YYEQKB.MCC_C2_42 + "','" + mCT_YYEQKB.MCC_C2_43 + "')"; sb.Append(s);
count++;//计数
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return sb.ToString();
}不知道导入ORACLE是不是也可以同样操作?还有个问题就是我的Excel数据源前4行都是标题,我想从第5行开始导入,不知道如何操作比较好?或者说是从指定行开始导入?
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelFileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
string strExcel = string.Format("select * from [{0}$]", strSheetName);//test
StringBuilder sb = new StringBuilder();
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName);
conn.Close();
}
DataTable dt = ds.Tables[0];
MCT_YYEQKB mCT_YYEQKB = new MCT_YYEQKB();
foreach (DataRow dr in dt.Rows)
{
//避免空数据干扰
if (dr["妇女ID"].ToString() == "")
{
break;
}
try
{
mCT_YYEQKB.MCC_C0_01 = dr[0].ToString().Trim();
mCT_YYEQKB.MCC_C0_02 = dr[1].ToString().Trim();
mCT_YYEQKB.MCC_C2_01 = dr[2].ToString().Trim();
mCT_YYEQKB.MCC_C2_02 = dr[3].ToString().Trim();
mCT_YYEQKB.MCC_C2_03 = dr[4].ToString().Trim();
mCT_YYEQKB.MCC_C2_04 = dr[5].ToString().Trim();
mCT_YYEQKB.MCC_C2_05 = dr[6].ToString().Trim();
mCT_YYEQKB.MCC_C2_06 = dr[7].ToString().Trim();
mCT_YYEQKB.MCC_C2_07 = dr[8].ToString().Trim();
mCT_YYEQKB.MCC_C2_08 = dr[9].ToString().Trim();
mCT_YYEQKB.MCC_C2_09 = dr[10].ToString().Trim();
mCT_YYEQKB.MCC_C2_10 = dr[11].ToString().Trim();
mCT_YYEQKB.MCC_C2_11 = dr[12].ToString().Trim();
mCT_YYEQKB.MCC_C2_12 = dr[13].ToString().Trim();
mCT_YYEQKB.MCC_C2_13 = dr[14].ToString().Trim();
mCT_YYEQKB.MCC_C2_14 = dr[15].ToString().Trim();
mCT_YYEQKB.MCC_C2_15 = dr[16].ToString().Trim();
mCT_YYEQKB.MCC_C2_16 = dr[17].ToString().Trim();
mCT_YYEQKB.MCC_C2_17 = dr[18].ToString().Trim();
mCT_YYEQKB.MCC_C2_18 = dr[19].ToString().Trim();
mCT_YYEQKB.MCC_C2_19 = dr[20].ToString().Trim();
mCT_YYEQKB.MCC_C2_20 = dr[21].ToString().Trim();
mCT_YYEQKB.MCC_C2_21 = dr[22].ToString().Trim();
mCT_YYEQKB.MCC_C2_22 = dr[23].ToString().Trim();
mCT_YYEQKB.MCC_C2_23 = dr[24].ToString().Trim();
mCT_YYEQKB.MCC_C2_24 = dr[25].ToString().Trim();
mCT_YYEQKB.MCC_C2_25 = dr[26].ToString().Trim();
mCT_YYEQKB.MCC_C2_26 = dr[27].ToString().Trim();
mCT_YYEQKB.MCC_C2_27 = dr[28].ToString().Trim();
mCT_YYEQKB.MCC_C2_28 = dr[29].ToString().Trim();
mCT_YYEQKB.MCC_C2_29 = dr[30].ToString().Trim();
mCT_YYEQKB.MCC_C2_30 = dr[31].ToString().Trim();
mCT_YYEQKB.MCC_C2_31 = dr[32].ToString().Trim();
mCT_YYEQKB.MCC_C2_32 = dr[33].ToString().Trim();
mCT_YYEQKB.MCC_C2_33 = dr[34].ToString().Trim();
mCT_YYEQKB.MCC_C2_34 = dr[35].ToString().Trim();
mCT_YYEQKB.MCC_C2_35 = dr[36].ToString().Trim();
mCT_YYEQKB.MCC_C2_36 = dr[37].ToString().Trim();
mCT_YYEQKB.MCC_C2_37 = dr[38].ToString().Trim();
mCT_YYEQKB.MCC_C2_38 = dr[39].ToString().Trim();
mCT_YYEQKB.MCC_C2_39 = dr[40].ToString().Trim();
mCT_YYEQKB.MCC_C2_40 = dr[41].ToString().Trim();
mCT_YYEQKB.MCC_C2_41 = dr[42].ToString().Trim();
mCT_YYEQKB.MCC_C2_42 = dr[43].ToString().Trim();
mCT_YYEQKB.MCC_C2_43 = dr[44].ToString().Trim();
string s = "insert into MCT_YYEQKB values('" + mCT_YYEQKB.MCC_C0_01 + "','" + mCT_YYEQKB.MCC_C0_02 + "','" + mCT_YYEQKB.MCC_C2_01 + "','" + mCT_YYEQKB.MCC_C2_02 + "','" + mCT_YYEQKB.MCC_C2_03 + "','" + mCT_YYEQKB.MCC_C2_04 + "','" + mCT_YYEQKB.MCC_C2_05 + "','" + mCT_YYEQKB.MCC_C2_06 + "','" + mCT_YYEQKB.MCC_C2_07 + "','" + mCT_YYEQKB.MCC_C2_08 + "','" + mCT_YYEQKB.MCC_C2_09 + "','" + mCT_YYEQKB.MCC_C2_10 + "','" + mCT_YYEQKB.MCC_C2_11 + "','" + mCT_YYEQKB.MCC_C2_12 + "','" + mCT_YYEQKB.MCC_C2_13 + "','" + mCT_YYEQKB.MCC_C2_14 + "','" + mCT_YYEQKB.MCC_C2_15 + "','" + mCT_YYEQKB.MCC_C2_16 + "','" + mCT_YYEQKB.MCC_C2_17 + "','" + mCT_YYEQKB.MCC_C2_18 + "','" + mCT_YYEQKB.MCC_C2_19 + "','" + mCT_YYEQKB.MCC_C2_20 + "','" + mCT_YYEQKB.MCC_C2_21 + "','" + mCT_YYEQKB.MCC_C2_22 + "','" + mCT_YYEQKB.MCC_C2_23 + "','" + mCT_YYEQKB.MCC_C2_24 + "','" + mCT_YYEQKB.MCC_C2_25 + "','" + mCT_YYEQKB.MCC_C2_26 + "','" + mCT_YYEQKB.MCC_C2_27 + "','" + mCT_YYEQKB.MCC_C2_28 + "','" + mCT_YYEQKB.MCC_C2_29 + "','" + mCT_YYEQKB.MCC_C2_30 + "','" + mCT_YYEQKB.MCC_C2_31 + "','" + mCT_YYEQKB.MCC_C2_32 + "','" + mCT_YYEQKB.MCC_C2_33 + "','" + mCT_YYEQKB.MCC_C2_34 + "','" + mCT_YYEQKB.MCC_C2_35 + "','" + mCT_YYEQKB.MCC_C2_36 + "','" + mCT_YYEQKB.MCC_C2_37 + "','" + mCT_YYEQKB.MCC_C2_38 + "','" + mCT_YYEQKB.MCC_C2_39 + "','" + mCT_YYEQKB.MCC_C2_40 + "','" + mCT_YYEQKB.MCC_C2_41 + "','" + mCT_YYEQKB.MCC_C2_42 + "','" + mCT_YYEQKB.MCC_C2_43 + "')"; sb.Append(s);
count++;//计数
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return sb.ToString();
}不知道导入ORACLE是不是也可以同样操作?还有个问题就是我的Excel数据源前4行都是标题,我想从第5行开始导入,不知道如何操作比较好?或者说是从指定行开始导入?
原来用sqlserver数据库的时候是用的OleDb来操作excel导入的sqlserver,是没问题的。现在不知道是不是可以同样操作oracle?因为我操作sqlserver的时候是拼接成一个长sql语句,执行sql语句。我想oracle也可以同样操作吧。还有第二个问题,不知道怎么从指定行开始读取excel?
从E开始 读取到第M列 具体要从那一列开始 那一列结尾 看一下EXCEL文档的头部就可以了或者
如你上面的代码 都已经得到datatable了 从第五列开始读 不会么??
mCT_YYEQKB.MCC_C2_04 = dr[5].ToString().Trim(); 这不就是第五列么?
笨一点的方法,是读取记录个数,然后 循环从 i=5开始。