Click here to Skip to main content
15,845,469 members
Please Sign up or sign in to vote.
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
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();

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
 
Share this answer
 
Comments
jonlink01 12-Dec-12 5:18am    
Hi Master Black,

Thank you for your help....your code works as per my expectations.
[no name] 12-Dec-12 5:52am    
Yours welcome dear...
 
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