Click here to Skip to main content
15,887,267 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to Copy Data from Access database File to Excel worksheet. I use OLEDBConnection but I don't know how to copy all data from Access table to paste to cells A2 on excel.

My table's name is Beam Forces, It has 10 columns: Story,Beam, CaseCombo, Station, P, V2, V3, T, M2, M3.

I do not know where it is wrong. Here is my code:

C#
private void btnInput_Click(object sender, RibbonControlEventArgs e)
       {
           OpenFileDialog MDB = new OpenFileDialog();
           string sFile;
           MDB.Filter = "Access files|*.mdb";
           MDB.AddExtension = true;
           MDB.CheckPathExists = true;
           MDB.Title = "Choose Your Input File";


           if (MDB.ShowDialog() == DialogResult.OK)
           {
               sFile = MDB.FileName;
               //MessageBox.Show(sFile);

               try
               {
                   OleDbConnection MyConnection  = new OleDbConnection();
                   OleDbCommand MyCommand = new OleDbCommand();
                   MyConnection = new  OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sFile);
                   MyConnection.Open();
                   MessageBox.Show("Mở kết nối thành công");

                   string mySQL = "SELECT [Story],[Beam], [CaseCombo], [Station], P, V2, V3, T, M2, M3 " + "From [Beam Forces]";
                   OleDbCommand cmd = new OleDbCommand(mySQL, MyConnection);
                   MessageBox.Show(mySQL);
                   OleDbDataReader dr = cmd.ExecuteReader();
                   DataTable dtb = new DataTable();
                   WorkSheet curSheet = Globals.ThisAddIn.Application.ActiveSheet;


                   ????????????????????????

                   MyConnection.Close();
               }
               catch (Exception ex)
               {
                   MessageBox.Show("Error: "+ ex);
               }

           }


What I have tried:

I tried to search some help from MSDN but I can't.
Posted
Updated 10-Sep-17 13:00pm
Comments
Richard MacCutchan 9-Sep-17 12:36pm    
Try searchingg for "excel c#" and you should find many samples.

You can do it directly in Excel. What is the need for an external program? Much more efficient.

A quick Google Search: query access database from excel vba[^] turns up almost 1/2 million results on how to do this with sample code like:

* Running Access Queries From Excel Using VBA ~ My Engineering World[^]
* VBA Express : Excel - Query Access database using ADODB and write results to spreadsheet[^]
* http://www.globaliconnect.com/excel_new/[^]
* Excel VBA to Extract Data from an Access Database - YouTube[^]
* Excel VBA to get data from Access DataBase[^]
* and much much more![^]

However if you must do it all in your app, then: how to write data from dataset to excel in c#[^]
 
Share this answer
 
v2
You can also use EPPlus. Is very easy to use and it free. EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx). Here is a sample project on how to do so.

[EPPlus.DataTableToExcel]
 
Share this answer
 

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