How to import data from access to excel

Question

0.00/5 (No votes)

See more:

Hi All,

can anyone please confirm me the c# code from which i am able to import information in access table to excel.

Thanks

Posted 9-Dec-12 22:36pm

jonlink01

Add a Solution

Comments

AnkitGoel.com 10-Dec-12 4:51am

first get the data from ms-access db then write in onto excel simple.
tell me where ru stuck?

jonlink01 11-Dec-12 3:53am

Thank you for writing,

i am a MCA student and preparing a collage project, i am not shore whether the code which i am writing is correct or not.

but below is what i am trying to do.

first i have imported all the data from access to datagridview. and then i have import data from "datagridview" to excel.

to do this i am using file handling process but its not giving me any success. below is the code which i used.
OleDbConnection connect = new OleDbConnection();
connect.ConnectionString = @"Provider=Microsoft.jet.OLEDB.4.0;Data Source=f:\db1.mdb;User Id=admin;Password=;";
connect.Open();
string query = "select * from test";
OleDbCommand cmd = new OleDbCommand(query, connect);
OleDbDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
dataGridView1.DataSource = dt;
dataGridView1.Show();
connect.Close();
FileStream fs= new FileStream("f:\\test.xls",FileMode.Open, FileAccess.Write);
StreamWriter sw= new StreamWriter(fs);
string str;
str = dt.ToString();
sw.WriteLine(str);
sw.Flush();
sw.Close();
fs.Close();

2 solutions

Solution 1

The below link has couple of approaches.
http://angelwing59.blog.com/2011/01/19/export-data-in-access-to-excel-with-c/[^]

Posted 9-Dec-12 23:35pm

Kiran Susarla

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

sisir patro · Accepted Answer · 2012-12-11T00:33:00

Hi jonlink,

Try this out for importing the data to excel.

public DataSet GetDataFromExcel(string filePath)
{
    try
    {
    string strConn;
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"";
    DataTable dt = new DataTable();
    dt = null;
    using (OleDbConnection oleDB = new OleDbConnection(strConn))
    {
        oleDB.Open();
        dt = oleDB.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (dt == null)
            return null;

        ListItemCollection items = new ListItemCollection();
        int i = 0;

        //if (dt.Rows.Count > 1)
        //return null;  

        for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
        {
            string excelSheetName;
            string lastCharacter = "";

            excelSheetName = dt.Rows[rowIndex]["TABLE_NAME"].ToString();
            excelSheetName = excelSheetName.Replace("'", "");
            lastCharacter = excelSheetName.Substring(excelSheetName.Length - 1, 1);
            if (lastCharacter == "$")
            {
                items.Add(dt.Rows[rowIndex]["TABLE_NAME"].ToString());
            }
        }
        if (items.Count > 1)
            return null;

        string sName;
        string query;

        sName = items[0].ToString();
        sName = sName.Replace("'", "");
        sName = sName.Replace("$", "");

        query = "";
        query = String.Format("select * from [{0}$]", sName);
        OleDbDataAdapter da = new OleDbDataAdapter(query, strConn);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    }
    catch (Exception ex)
    {

        throw ex;
    }
}



public static DataSet ImportExceltoDataset(string file)
{
    IExcelDataReader iExcelDataReader = null;

    FileStream oStream = File.Open(file, FileMode.Open, FileAccess.Read);

    iExcelDataReader = ExcelReaderFactory.CreateBinaryReader(oStream);

    iExcelDataReader.IsFirstRowAsColumnNames = true;

    DataSet dsUnUpdated = new DataSet();

    dsUnUpdated = iExcelDataReader.AsDataSet();

    iExcelDataReader.Close();

    return dsUnUpdated;
}

Some links:
link-1 import data
link-2 Export data
link-3 Read and Write Excel Data Using C#

If you are trying to import the data to an excel sheet from a grid then try this out.

import data from Excel to Datagrid
Import excel sheet data to datagrid without using oledb

Thanks