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 5: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 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  
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  

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



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