在C＃.net中导出和导入Excel文件 [英] Export and Import Excel File in C#.net

问题描述

如何在C＃.net中导出和导入Excel文件。如果我们点击导出选项并给出正常意味着数据库表应该作为Excel文件保存在系统中。我'使用Access 2007进行后端。对于导入Excel文件，系统中的excel文件应该导入并保存在数据库中，即访问2007.



问候

Balamurugan

Hi,
How to Export and Import Excel File in C#.net.If we are clicking Export option and giving ok means the database table should save in the system as Excel file.I''m using Access 2007 for backend. For Importing a Excel file the excel file from the system should imported and should save in the database i.e access 2007.

Regards
Balamurugan

推荐答案

尝试

http://www.c-sharpcorner.com/UploadFile/vandita/exceltoaccessinterop02242007093426AM/exceltoaccessinterop.aspx [ ^ ]

http://angelwing59.blog.com/2011/01/19/export-data-in-access-to-excel-with -c / [ ^ ]

http://www.gemboxsoftware.com/support/articles/import-export-xls-xlsx-ods-csv-html -net [ ^ ]



这是一段视频 - http://www.youtube.com/watch?v=7Gq5Y3UeYbA [ ^ ]。

Try
http://www.c-sharpcorner.com/UploadFile/vandita/exceltoaccessinterop02242007093426AM/exceltoaccessinterop.aspx[^]
http://angelwing59.blog.com/2011/01/19/export-data-in-access-to-excel-with-c/[^]
http://www.gemboxsoftware.com/support/articles/import-export-xls-xlsx-ods-csv-html-net[^]

Here is a video - http://www.youtube.com/watch?v=7Gq5Y3UeYbA[^].

我们需要添加用于导出和导入Excel文件的dll .dll文件名是ExcelLibrary.dll。添加此dll后，我们需要添加引用即ExcelLibrary。

从工具框中我们需要以设计形式拖动openfiledialog和savedialog。双击savedialog并需要编写以下编码用于导出数据库文件到系统，

private void saveFileDialog1_FileOk（object sender，CancelEventArgs e）

{

DataSet dds = new DataSet（）;

OleDbConnection con = new OleDbConnection（Provider = Microsoft.ACE.OLEDB.12.0; Data Source = | DataDirectory | \\ xxx.accdb）;

OleDbDataAdapter da =新的OleDbDataAdapter（Select * from yyy，con）;

con.Open（）;

if（radioButton3.Checked == true）{da = new OleDbDataAdapter（ 从yyy中选择*，con）; }

else if（radioButton4.Checked == true）

{da = new OleDbDataAdapter（Select * from aaa，con）; }

else if（radioButton5.Checked == true）

{da = new OleDbDataAdapter（Select * from bbb，con）; }

else if（radioButton6.Checked == true）

{da = new OleDbDataAdapter（Select * from ccc，con）; }

else if（radioButton7.Checked == true）

{da = new OleDbDataAdapter（Select * from yyy，con）; }

da.Fill（dds）;

string nm =;

if（saveFileDialog1.FileName.IndexOf（xls） == -1）

{

nm = saveFileDialog1.FileName +。xls;

}

ExcelLibrary.DataSetHelper.CreateWorkbook（nm，dds）;

}

要将Excel文件从系统导入数据库，我们需要使用下面的代码，

首先我们需要从工具箱添加openfiledialog并需要双击并编写代码，

private void openFileDialog1_FileOk（object sender，CancelEventArgs e）

{

尝试

{

DataSet dds = new DataSet（）;

dds = ExcelLibrary.DataSetHelper.CreateDataSet（openFileDialog1。 FileName）;

DataTable dt = dds.Tables [0];

OleDbConnection con = new OleDbConn ection（Provider = Microsoft.ACE.OLEDB.12.0; Data Source = d：\\ xxx.accdb）;

OleDbCommand da = new OleDbCommand（Select * from aaa，con ）;

con.Open（）;

if（radioButton3.Checked == true）{

da = new OleDbCommand（ aaa，con）;

da.ExecuteNonQuery（）;

foreach（dt.Rows中的DataRow dr）

{

OleDbCommand da1 = new OleDbCommand（插入aaa值（+ dr [0] .ToString（）+，''+ dr [1] .ToString（）+''），con） ;

da1.ExecuteNonQuery（）;

}

}

else if（radioButton4.Checked == true） ）

{

da = new OleDbCommand（从bbb删除，con）;

da.ExecuteNonQuery（）;

foreach（dt.Rows中的DataRow博士）

{

OleDbCommand da1 =新的OleDbCommand（插入bbb值（+ dr [ 0] .ToString（）+，''+ dr [1] .ToString（）+''），con）;

da1.ExecuteNonQuery（）;

}

}

else if（radioButton5.Checked == true）

{

da =新的OleDbCommand（从ccc中删除，con）;

da.ExecuteNonQuery（）;

foreach（dt.Rows中的DataRow dr）

{

OleDbCommand da1 = new OleDbCommand（插入ccc值（+ dr [0] .ToString（）+，''+ dr [1] .ToString（）+' '，+ dr [2] .ToString（）+），con）;

da1.ExecuteNonQuery（）;

}

}

否则if（radioButton6.Checked == true）

{

da = new OleDbCommand（从ddd删除，con）;

da .ExecuteNonQuery（）;

foreach（dt.Rows中的DataRow dr）

{

OleDbCommand da1 = new OleDbCommand（插入ddd值 （+ dr [0] .ToString（）+，+ dr [1] .ToString（）+），con）;

da1.ExecuteNonQuery（）;

}

}

else if（radioButton7.Checked == true）

{

da = new OleDbCommand（从eee删除，con）;

da.ExecuteNonQuery（）;

foreach（dt.Rows中的DataRow dr）

{

OleDbCommand da1 =新的OleDbCommand（插入eee值（+ dr [0] .ToString（）+，''+ dr [1] .ToString（） +''，''+ dr [2] .ToString（）+''，''+ dr [3] .ToString（）+''，+ dr [4] .ToString（）+ ，+ dr [5] .ToString（）+，''+ dr [6] .ToString（）+''，''+ dr [7] .ToString（）+''， + dr [8] .ToString（）+，''+ dr [9] .ToString（）+''，''+ dr [10] .ToString（）+''，+ dr [ 11] .ToString（）+，''+ dr [12] .ToString（）+''，''+ dr [13] .ToString（）+''，''+ dr [14 ] .ToString（）+''，''+ dr [15] .ToString（）+''，+ dr [16] .ToString（）+，''+ dr [17] .ToString （）+''），con）;

da1.ExecuteNonQuery（）;

}

}

MessageBox.Show（数据导入成功）;

}

catch（例外情况）

{

MessageBox.Show（导入错误：（+ ex.ToString（）+））;

}

}

We need to add the dll for Exporting and importing Excel file .The dll file name is ExcelLibrary.dll. After adding this dll we need to add the refernce i.e ExcelLibrary.
From tool box we need to drag the openfiledialog and savedialog in design form.Double click savedialog and need to write the below coding for Exporting the database file to the system,
private void saveFileDialog1_FileOk(object sender, CancelEventArgs e)
{
DataSet dds=new DataSet();
OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\xxx.accdb");
OleDbDataAdapter da= new OleDbDataAdapter("Select * from yyy", con);
con.Open();
if (radioButton3.Checked == true) { da = new OleDbDataAdapter("Select * from yyy", con); }
else if (radioButton4.Checked == true)
{ da = new OleDbDataAdapter("Select * from aaa", con); }
else if (radioButton5.Checked == true)
{ da = new OleDbDataAdapter("Select * from bbb", con); }
else if (radioButton6.Checked == true)
{ da = new OleDbDataAdapter("Select * from ccc", con); }
else if (radioButton7.Checked == true)
{ da = new OleDbDataAdapter("Select * from yyy", con); }
da.Fill(dds);
string nm = "";
if (saveFileDialog1.FileName.IndexOf("xls") == -1)
{
nm = saveFileDialog1.FileName + ".xls";
}
ExcelLibrary.DataSetHelper.CreateWorkbook(nm,dds);
}
For Importing the Excel file from system to database we need to folllow the below code,
First we need to add openfiledialog from toolbox and need to double click and write the coding,
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
try
{
DataSet dds = new DataSet();
dds = ExcelLibrary.DataSetHelper.CreateDataSet(openFileDialog1.FileName);
DataTable dt = dds.Tables[0];
OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\\xxx.accdb");
OleDbCommand da = new OleDbCommand("Select * from aaa", con);
con.Open();
if (radioButton3.Checked == true) {
da = new OleDbCommand("Delete from aaa", con);
da.ExecuteNonQuery();
foreach (DataRow dr in dt.Rows)
{
OleDbCommand da1 = new OleDbCommand("insert into aaa values("+dr[0].ToString()+",''"+dr[1].ToString()+"'')", con);
da1.ExecuteNonQuery();
}
}
else if (radioButton4.Checked == true)
{
da = new OleDbCommand("Delete from bbb", con);
da.ExecuteNonQuery();
foreach (DataRow dr in dt.Rows)
{
OleDbCommand da1 = new OleDbCommand("insert into bbb values(" + dr[0].ToString() + ",''" + dr[1].ToString() + "'')", con);
da1.ExecuteNonQuery();
}
}
else if (radioButton5.Checked == true)
{
da = new OleDbCommand("Delete from ccc", con);
da.ExecuteNonQuery();
foreach (DataRow dr in dt.Rows)
{
OleDbCommand da1 = new OleDbCommand("insert into ccc values(" + dr[0].ToString() + ",''" + dr[1].ToString() + "''," + dr[2].ToString() + ")", con);
da1.ExecuteNonQuery();
}
}
else if (radioButton6.Checked == true)
{
da = new OleDbCommand("Delete from ddd", con);
da.ExecuteNonQuery();
foreach (DataRow dr in dt.Rows)
{
OleDbCommand da1 = new OleDbCommand("insert into ddd values(" + dr[0].ToString() + "," + dr[1].ToString() + ")", con);
da1.ExecuteNonQuery();
}
}
else if (radioButton7.Checked == true)
{
da = new OleDbCommand("Delete from eee", con);
da.ExecuteNonQuery();
foreach (DataRow dr in dt.Rows)
{
OleDbCommand da1 = new OleDbCommand("insert into eee values(" + dr[0].ToString() + ",''" + dr[1].ToString() + "'',''" + dr[2].ToString() + "'',''" + dr[3].ToString() + "''," + dr[4].ToString() + "," + dr[5].ToString() + ",''" + dr[6].ToString() + "'',''" + dr[7].ToString() + "''," + dr[8].ToString() + ",''" + dr[9].ToString() + "'',''" + dr[10].ToString() + "''," + dr[11].ToString() + ",''" + dr[12].ToString() + "'',''" + dr[13].ToString() + "'',''" + dr[14].ToString() + "'',''" + dr[15].ToString() + "''," + dr[16].ToString() + ",''" + dr[17].ToString() + "'')", con);
da1.ExecuteNonQuery();
}
}
MessageBox.Show("Data Imported Successfully");
}
catch(Exception ex)
{
MessageBox.Show("Error In Import : ("+ex.ToString()+")");
}
}

这篇关于在C＃.net中导出和导入Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！