Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#4.0
Hi
I want to import excel file which want to export access database file.
Posted 16-Jan-13 6:39am
Comments
BC @ CV at 16-Jan-13 12:19pm
   
You question isn't clear. Are you asking how to import an Excel file into an Access database?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I think you are asking how to import an excel file into an access DB,
 
if so this is a link to a walk through from microsoft,
 
http://support.microsoft.com/kb/141228[^]
 
In the future if you use google you will be able to find the answers yourself
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

There are two solutions that I will provide:
Solution 1:
See belwo code, it can help you export excel to both ms access and sql datatable:
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\sample.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            DataTable data= sheet.ExportDataTable();
            //connect with ms database table
            string conn = "Provider=Microsoft.ACE.OLEDB.12.0;data Source=dataBase.accdb;Persist Security Info=False;";
            //if you need to connect to sql datatable, you can change above code by 
            //string connectionStr = @"Data Source=DEV05\SQLEXPRESS;Initial Catalog=Sample;User Id=amy;Password=123";
            SqlConnection conn= new SqlConnection(connectionStr);
            SqlCommand command = new SqlCommand();
            conn.Open();
            command.Connection = conn;
            for (int i=1;i<data.rows.count;i++)>
            {
                DataRow row=data.Rows[i];
 
                string commd = "insert into [tableSample](Name,Capital,Continent,Area) values('" + row[0].ToString() + "','" + row[1].ToString() +
               " ','" + row[2].ToString() + "','" + row[3].ToString()+ "')";
                command.CommandText = commd;
                command.ExecuteNonQuery();
            }  
            conn.Close();
In performing above code, you need a excel library: http://spreadsheet.codeplex.com/[^]Please note that this is a commercial library. anyhow, it can have a free trial.
 
Solution two:
static void ReadExcel(String strFileName)
{
  Object Opt = Missing.Value;
  Microsoft.Office.Interop.Excel.Application app = new Application(); //= new Excel.Application();
  //Declare Workbook
  Microsoft.Office.Interop.Excel.Workbook book;
  //Excel.Worksheet sheet;
  Microsoft.Office.Interop.Excel.Worksheet xlsSheet;
  Microsoft.Office.Interop.Excel.Range oRng;
  //Open Spreadsheet
  book = app.Workbooks.Open(“”, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt);
  //Take the first sheet
  xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets[1];
  //Read the first cell
  oRng = (Microsoft.Office.Interop.Excel.Range)xlsSheet.Cells[1, 1];
  String StrCell = oRng.Text.ToString();
  Console.WriteLine(StrCell);
  book.Close(false, false, Missing.Value);
}
in using code in solution two, you need to install Microsoft.Office.Interop: http://www.microsoft.com/en-us/download/details.aspx?id=3508[^]
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 8,963
1 OriginalGriff 6,726
2 Peter Leow 4,232
3 Zoltán Zörgő 3,769
4 Richard MacCutchan 2,558


Advertise | Privacy | Mobile
Web03 | 2.8.150123.1 | Last Updated 17 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100