如何打开现有工作表 [英] how to open existing worksheet
问题描述
正在开发考勤系统，当我刷卡时，我会将卡ID输入文本框，然后我需要插入excel按钮点击。
这里是代码：
private void button1_Click（ object sender，EventArgs e）
{
if （txtCardID.Text！= ）
{
newString = ;
string connectionString = GetConnectionString（）;
string s = Convert.ToString（Program.ConnectObj.Output）;
if （s.Length == 7 ）
{
newString = s.PadLeft（ 10 ，' 0' ）;
}
else if （s.Length == 8 ）
{
newString = s.PadLeft（ 10 ，' 0'）;
}
else if （s.Length == 9 ）
{
newString = s.PadLeft（ 10 ，' 0'）;
}
使用（OleDbConnection conn = new OleDbConnection（connectionString ））
{
conn.Open（）;
OleDbCommand cmd = new OleDbCommand（）;
cmd.Connection = conn;
cmd.CommandText = CREATE TABLE [sheet1]（id INT）;;
cmd.ExecuteNonQuery（）;
cmd.CommandText = INSERT INTO [sheet1 $]（id）VALUES（' + newString + '）;;
cmd.ExecuteNonQuery（）;
MessageBox.Show（ 已成功插入）;
conn.Close（）;
}
txtCardID.Text = ;
}
else
MessageBox.Show（ 请刷卡）;
}
第一次创建sheet1时会将卡片ID写入其中，但下次会是给出Exception为sheet1已经存在如何解决它。
我正在使用oledb连接。
private string GetConnectionString（）
{
Dictionary< string，string> props = new Dictionary< string，string>（）;
// XLSX - Excel 2007,2010,2012,2013
props [ Provider] = Microsoft.ACE.OLEDB.12.0;;
props [ 扩展属性] = Excel 12.0 XML;
string appPath = Path.GetDirectoryName（Application.ExecutablePath）;
string fName = @appPath + \\ \\\ MyExcel.xlsx;
props [ 数据源] = fName;
// props [Data Source] =C：\\MyExcel.xlsx;
StringBuilder sb = new StringBuilder（）;
foreach （KeyValuePair< string，string> prop in props）
{
sb.Append（prop.Key）;
sb.Append（' ='）;
sb.Append（prop.Value）;
sb.Append（' ;'）;
}
return sb.ToString（）;
}
（id）VALUES（' + newString + '）;;
cmd.ExecuteNonQuery（）;
MessageBox.Show（< span class =code-string> 已成功插入）;
conn.Close（）;
}
txtCardID.Text = ;
}
else
MessageBox.Show（ 请刷卡卡）;
}
第一次创建sheet1写卡id到那个，但下次它给出Exception为sheet1已经存在如何解决它。
我正在使用oledb连接。
< pre lang =c＃> private string GetConnectionString（）
{
Dictionary< ; string，string> props = new Dictionary< string，string>（）;
// XLSX - Excel 2007,2010,2012,2013
props [ Provider] = Microsoft.ACE.OLEDB.12.0;;
props [ 扩展属性] = Excel 12.0 XML;
string appPath = Path.GetDirectoryName（Application.ExecutablePath）;
string fName = @appPath + \\ \\\ MyExcel.xlsx;
props [ 数据源] = fName;
// props [Data Source] =C：\\MyExcel.xlsx;
StringBuilder sb = new StringBuilder（）;
foreach （KeyValuePair< string，string> prop in props）
{
sb.Append（prop.Key）;
sb.Append（' ='）;
sb.Append（prop.Value）;
sb.Append（' ;'）;
}
return sb.ToString（）;
}
每次点击时你都在创建表'sheet1' button1，
您可以验证表是否存在以解决您的问题。
IF NOT EXISTS（SELECT * FROM sys.objects WHERE object_id = OBJECT_ID（N'[Sheet1]'）并键入（N'U'））
[您的SQL查询]
请检查解决方案并让我知道它是否有帮助。
谢谢，
Hitesh Varde
您应该检查该特定工作表是否已经存在，如果没有创建其他跳过：
如何检查工作表是否已经存在于互操作中 [ ^ ]
hi,
am developing attendance system, when i swipe the card , i will get card Id into text box, then i need to insert into excel in button click.
here is the code:
private void button1_Click(object sender, EventArgs e)
{
if(txtCardID.Text !="")
{
newString = "";
string connectionString = GetConnectionString();
string s = Convert.ToString(Program.ConnectObj.Output);
if (s.Length == 7)
{
newString = s.PadLeft(10, '0');
}
else if (s.Length == 8)
{
newString = s.PadLeft(10, '0');
}
else if (s.Length == 9)
{
newString = s.PadLeft(10, '0');
}
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "CREATE TABLE [sheet1] (id INT);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO [sheet1$](id) VALUES('" + newString + "');";
cmd.ExecuteNonQuery();
MessageBox.Show("Successfully Inserted");
conn.Close();
}
txtCardID.Text = "";
}
else
MessageBox.Show("Please Swipe the Card");
}
first time it's creating "sheet1" writes card id into that, but for next time it's giving Exception as "sheet1 already exists" how to solve it.
am using oledb connection.
private string GetConnectionString()
{
Dictionary<string, string> props = new Dictionary<string, string>();
// XLSX - Excel 2007, 2010, 2012, 2013
props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
props["Extended Properties"] = "Excel 12.0 XML";
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
string fName = @appPath + "\\MyExcel.xlsx ";
props["Data Source"] = fName;
// props["Data Source"] = "C:\\MyExcel.xlsx";
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair<string, string> prop in props)
{
sb.Append(prop.Key);
sb.Append('=');
sb.Append(prop.Value);
sb.Append(';');
}
return sb.ToString();
}
(id) VALUES('" + newString + "');"; cmd.ExecuteNonQuery(); MessageBox.Show("Successfully Inserted"); conn.Close(); } txtCardID.Text = ""; } else MessageBox.Show("Please Swipe the Card"); }
first time it's creating "sheet1" writes card id into that, but for next time it's giving Exception as "sheet1 already exists" how to solve it.
am using oledb connection.
private string GetConnectionString() { Dictionary<string, string> props = new Dictionary<string, string>(); // XLSX - Excel 2007, 2010, 2012, 2013 props["Provider"] = "Microsoft.ACE.OLEDB.12.0;"; props["Extended Properties"] = "Excel 12.0 XML"; string appPath = Path.GetDirectoryName(Application.ExecutablePath); string fName = @appPath + "\\MyExcel.xlsx "; props["Data Source"] = fName; // props["Data Source"] = "C:\\MyExcel.xlsx"; StringBuilder sb = new StringBuilder(); foreach (KeyValuePair<string, string> prop in props) { sb.Append(prop.Key); sb.Append('='); sb.Append(prop.Value); sb.Append(';'); } return sb.ToString(); }
Hi,
Here you are creating table 'sheet1' every time you click on the button1,
you can validate if table exists or not to solve your problem.
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Sheet1]') AND type in (N'U')) [YOUR SQL QUERY]
Please check the solutions and let me know if it helps.
Thanks,
Hitesh Varde
You should check if that particular worksheet already exists, if not then create else skip:
how-to-check-if-the-worksheet-already-exist-in-interop[^]
这篇关于如何打开现有工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
