使用C#将Excel空行替换为值 [英] Replace Excel Null Row with value using c#
本文介绍了使用C#将Excel空行替换为值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在excel中有一个数据库，我想从该excel表中删除一行.
在这里，我提到的是用C#编写的代码
i have a database in excel and i want to delete a row from that excel sheet.
Here i am mentioning my code written in c#
private void pbDelete_Click(object sender, EventArgs e)
{
if (MobileNumber == "")
{
MessageBox.Show("Please select a Name !..");
}
else
{
if (MessageBox.Show("Do you realy want to delete this data?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (index != -1)
{
string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.GetDirectoryName(Application.ExecutablePath) + "\\Taas SMS.xls;Extended Properties=Excel 8.0;";
//oledbConn.Open();
//cmd1 = new OleDbCommand();
//cmd.Connection = oledbConn;
//cmd.CommandText = "INSERT INTO ExecutiveMember (Name,Designation,TravelAgent, MobileNumber) values (''" + dt.Rows[i][0] + "'', ''" + dt.Rows[i][1] + "'',''" + dt.Rows[i][2] + "'', ''" + dt.Rows[i][3] + "'')";
//cmd.ExecuteNonQuery();
//oledbConn.Close();
OleDbConnection oledbConn1 = new OleDbConnection(conn);
oledbConn1.Open();
OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = oledbConn1;
cmd1.CommandText = "select * from ExecutiveMember";
OleDbDataAdapter da = new OleDbDataAdapter(cmd1);
DataTable dt1 = new DataTable();
//dt1.Clear();
da.Fill(dt1);
OleDbConnection oledbConn = new OleDbConnection(conn);
oledbConn.Open();
OleDbCommand cmd2 = new OleDbCommand();
cmd2.Connection = oledbConn;
cmd2.CommandText = "update ExecutiveMember set Name='''',Designation='''',TravelAgent='''',MobileNumber='''' where MobileNumber=''" + dt1.Rows[index][3] + "''";
cmd2.ExecuteNonQuery();
oledbConn.Close();
//DataRow dtRowDelete = dt1.Rows[index];
//dtRowDelete.Delete();
//dt1.AcceptChanges();
da.Update(dt1);
dgvExecutiveMember.DataSource = dt1;
//conn = new OleDbConnection();
//conn.ConnectionString = gm.ConnectionString;
//conn.Open();
////cmd = new OleDbCommand("select * from ExecutiveMember where MobileNumber=''" + MobileNumber + "''", conn);
//cmd = new OleDbCommand("select * from ExecutiveMember", conn);
//da = new OleDbDataAdapter(cmd);
//dt.Clear();
//da.Fill(dt);
//DataRow dtRowDelete = dt.Rows[index];
//dtRowDelete.Delete();
//dt.AcceptChanges();
//da.Update(dt);
OleDbConnection oledbConn2 = new OleDbConnection(conn);
oledbConn.Open();
//conn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [ExecutiveMember]", oledbConn2);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
DataSet ds = new DataSet();
oleda.Fill(ds, "ExecutiveMember");
int rowcount = ds.Tables[0].Rows.Count;
oledbConn.Close();
for (int i = 0; i < rowcount-2; i++)
{
oledbConn.Open();
cmd = new OleDbCommand();
cmd.Connection = oledbConn;
cmd.CommandText = "update ExecutiveMember set Name=''" + dt1.Rows[i][0] + "'',Designation=''" + dt1.Rows[i][1] + "'',TravelAgent=''" + dt1.Rows[i][2] + "'',MobileNumber=''" + dt1.Rows[i][3] + "'' where MobileNumber=''" + dt1.Rows[i][3] + "''";
//cmd.CommandText = "INSERT INTO ExecutiveMember (Name,Designation,TravelAgent, MobileNumber) values (''" + dt1.Rows[i][0] + "'', ''" + dt1.Rows[i][1] + "'',''" + dt1.Rows[i][2] + "'', ''" + dt1.Rows[i][3] + "'')";
cmd.ExecuteNonQuery();
oledbConn.Close();
Clear();
}
// conn = new OleDbConnection();
// conn.ConnectionString = gm.ConnectionString;
// conn.Open();
// cmd = new OleDbCommand();
// cmd.Connection = conn;
// cmd.CommandText = "INSERT INTO ExecutiveMember (Name,Designation,TravelAgent, MobileNumber) values (''" + dt.Rows[i][0] + "'', ''" + dt.Rows[i][1] + "'',''" + dt.Rows[i][2] + "'', ''" + dt.Rows[i][3] + "'')";
// cmd.ExecuteNonQuery();
// conn.Close();
// Clear();
// FillGrid();
//}
//conn.Close();
Clear();
MobileNumber = "";
//FillGrid();
index = -1;
MessageBox.Show("Data Deleted!...");
}
}
else
{
}
}
}
这些代码可以从该Excel文件中删除行，但是我想用该Excel工作表的下一行值替换空字段.
请帮我解决这个问题.在此先感谢您.
These code can delete row from that Excel file, but i want to replace the null field with the next row value of that Excel sheet.
Please help me to solve this. Thanks in advance.
推荐答案
出现诸如
之类的错误时
When you get an error such as
Object reference not set to an instance of an object
这意味着您的对象之一未初始化.附加调试器后，您应该在IDE中直接获得行号和异常详细信息.您可以使用它来解决以上错误.
在此处 [
It means that one of your objects is not initialized. With your debugger attached, you should get a line number and exception details right within the IDE. You can use that to work through the error above.
There are several samples and troubleshooting efforts here[^] for a range of problems.
Cheers.
您正在使用dt.Clear();
我看不到定义dt.rows的地方.
You are usingdt.Clear();
I don''t see a place where you have defineddt.rows.
这篇关于使用C#将Excel空行替换为值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
