从 Excel 文件导入数据

Question

在下面提到的代码中，数据是从包含MM/DD/YYYY格式Sent_date列的Excel文件中导入的。迭代每个数据行，并将sent_date和日期值存储在ex_date变量中。

protected void btnUpload_Click(object sender, EventArgs e)
{
MySqlTransaction transaction;
string ex_id = "";
var count = 0;

string file_name = Path.GetFileName(FileUpload1.FileName);
string Excel_path = Server.MapPath("~/Excel/" + file_name);
FileUpload1.SaveAs(Excel_path);
using (OleDbConnection my_con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Excel_path + ";Extended Properties=Excel 8.0;Persist Security Info=False"))
{
my_con.Open();
// get the excel file data and assign it in OleDbcoomad object(o_cmd)
using (OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", my_con))
{
// read the excel file data and assing it o_dr object
using (OleDbDataReader dr = command.ExecuteReader())
{ 
/////////////////////////////////////////////
// while (dr.Read() && count++ < 10)
while(dr.Read())
{
//get first row data and assign it ex_id variable 
ex_id = dr[0].ToString();
string ex_uid = dr[1].ToString();
//get second row data and assign it ex_name variable
string ex_date = dr[2].ToString();
//get thirdt row data and assign it ex_name variable
string ex_dir = dr[3].ToString();
//get first row data and assign it ex_location variable
string ex_email = dr[4].ToString();
string ex_email1 = dr[5].ToString();
string ex_email2 = dr[6].ToString();
//string ex_company = dr[7].ToString();
string ex_company = dr[7].ToString();
string ex_contact = dr[8].ToString();
string ex_proposal = dr[9].ToString();
string ex_reason = dr[10].ToString();
//string ex_type = dr[11].ToString();
int chk = 0;
int type = 0;
int dup = 0;
int dir = 0;
if (ddlwebsites.SelectedIndex != 0)
{
dir = Convert.ToInt32(ddlwebsites.SelectedValue);
if (dir == 8)
{
type = 1;
}
}

foreach (ListItem lstAssign in ddlevents.Items)
{
if (lstAssign.Selected == true)
{
chk = 1;
}
}
if (type == 1 && chk == 0)
{
evyerror.Text = "Please Select the Event!!";
return;
}
else
{
string querycomp = "", compID = "";
querycomp = "Select * from barter_company where website like '%' '" + ex_dir + "' '%'";
string connStr = ConfigurationManager.ConnectionStrings["BarterConnectionString"].ToString();
connect = new MySqlConnection(connStr);
connect.Open();
transaction = connect.BeginTransaction();
try
{
ClassDtBaseConnect clsDtResult = new ClassDtBaseConnect();
DataTable dt = clsDtResult.GetDataTable(querycomp);
if (dt.Rows.Count > 0)
{
compID = dt.Rows[0]["comp_id"].ToString();
ViewState["comp_id"] = compID;
if (type == 1)
{
dup = checkforDuplicates(Convert.ToInt32(compID));
if (dup == 1)
{
//Confirm_MP.Show();
// ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "confirmation();", true);
}
}
//return;
}
else
{
string queryStr = "insert into barter_company (comp_name,website) values(?comp,?website)";
MySqlCommand cmd = new MySqlCommand(queryStr, connect, transaction);
cmd.Parameters.AddWithValue("?comp", ex_company);
cmd.Parameters.AddWithValue("?website", ex_dir);
cmd.ExecuteNonQuery();
cmd.CommandText = "Select LAST_INSERT_ID()";
compID = cmd.ExecuteScalar().ToString();
transaction.Commit();
connect.Close();
}
}
catch
{
transaction.Rollback();
}


if (dup == 0)
{
// create a connection string with your sql database 
string connStr1 = ConfigurationManager.ConnectionStrings["BarterConnectionString"].ToString();
connect = new MySqlConnection(connStr1);
connect.Open();
DateTime date = new DateTime();//added by chetan
ex_date = ex_date.Split(' ')[0];//added by chetan
date = DateTime.ParseExact(ex_date, "MM/dd/yyyy", null);//added by chetan
string SentDateString = date.ToString("yyyy/MM/dd");//added by chetan
DateTime SentDate = Convert.ToDateTime(SentDateString).Date;//added by chetan
                      //DateTime SentDate = DateTime.Parse(ex_date);//added by chetan

// }
//date = DateTime.ParseExact(ex_date, "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture);//added by chetan
//date = DateTime.ParseExact(ex_date, "MM/dd/yyyy", null);
// string SentDateString = date.ToString("yyyy/MM/dd");
transaction = connect.BeginTransaction();
try
{
ex_uid = Session["session_barterUser_id"].ToString();

MySqlCommand cmd = new MySqlCommand("insert into barter_proposals(user_id, sent_date, website_a, email_id, email_id2, email_id3, company, contact_name, proposal_status, reason,type) values(?uid,?sentdate,?dir,?email,?email2,?email3,?comp,?cont_name,?pro_status,?reason,?type)", connect);
cmd.Parameters.AddWithValue("?uid", ex_uid);
cmd.Parameters.AddWithValue("?comp", compID);
cmd.Parameters.AddWithValue("?sentdate", SentDate);
// command.Parameters.AddWithValue("?event", eventname);
cmd.Parameters.AddWithValue("?dir", dir);
// command.Parameters.AddWithValue("?bar_type", ddlbartertype.SelectedValue);
// command.Parameters.AddWithValue("?website_b", txtwebsite.Text);
//cmd.Parameters.AddWithValue("?comp", ex_company);
cmd.Parameters.AddWithValue("?cont_name", ex_contact);
cmd.Parameters.AddWithValue("?email", ex_email);
cmd.Parameters.AddWithValue("?email2", ex_email1);
cmd.Parameters.AddWithValue("?email3", ex_email2);
cmd.Parameters.AddWithValue("?pro_status", ex_proposal);
cmd.Parameters.AddWithValue("?reason", ex_reason);
cmd.Parameters.AddWithValue("?type", type);
// command.Parameters.AddWithValue("?type", type);
cmd.ExecuteNonQuery();
if (type == 1)
{
cmd.CommandText = "Select LAST_INSERT_ID()";
Int64 CurrentProId = Convert.ToInt64(cmd.ExecuteScalar());
int eventAssignID;
string QueryInqEventAssign = "insert into barter_propeventassign(prop_id,event_id) values(?pro_id,?event_id)";
foreach (ListItem lstAssign in ddlevents.Items)
{
if (lstAssign.Selected == true)
{
cmd = new MySqlCommand(QueryInqEventAssign, connect, transaction);
cmd.Parameters.AddWithValue("?pro_id", CurrentProId);
eventAssignID = Convert.ToInt32(lstAssign.Value);
cmd.Parameters.AddWithValue("?event_id", eventAssignID);
cmd.ExecuteNonQuery();
}
}
}//end of if
transaction.Commit();
connect.Close();
Response.Write("<script type="text/javascript">alert('Proposal Added Successfully!!!');</script>");
}//end of try
catch (Exception ex)
{
transaction.Rollback();
Response.Write("<script>alert('There is an Error Ocurred:" + Server.HtmlEncode(ex.Message) + "')</script>");
}
finally
{
connect.Close();
}
}//ifdupzero
}//else
}//while   

//dr.Close();//commented by chetan
}//using
}//using
}//using
if (System.IO.File.Exists(Excel_path))
{
System.IO.File.Delete(Excel_path);
}

}

现在的问题是，假设当一个 excel 文件包含 3 行时

，如下所示：

sent_date
11/21/2017----1strow
11/22/2017----2ndrow
11/23/2017----3rdrow

Reader确实读取了 3 行，但之后不会停止。它再次转到 while 循环并读取日期(实际值：")并给出错误"string is not recognised as datetime"。

观察到的一件奇怪的事情是，在导入16 行时，它不会给出此错误 - 读取器停止并插入数据。不知道为什么需要 16 行才能停止reader

在读取前 3 行后应该在代码中添加什么来停止阅读器 - 即在读取第 3 行之后，它将如何dr.close()？

Answer 1

在阅读 excel 文件时，您需要考虑空/已用行的概念。如果您在 3 行后从 excel 中删除行并再次导入，则不会出现此问题。

根据您的解释，excel 总共有 16 个已用行，但数据仅填充在 3 行中。

要在 3 个数据行后循环时出来，您需要输入更详尽的 where 条件，例如"从 [Sheet1$] 中选择 * WHERE [F1] <> ''">