Click here to Skip to main content
14,695,620 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted

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()+")");
}
}
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900