Click here to Skip to main content
11,489,574 members (64,823 online)
Click here to Skip to main content

Converting Database records to Excel file(Very simple code)

, 21 Feb 2006 59.7K 722 16
Rate this:
Please Sign up or sign in to vote.
This is project is to know about how to convert the retrieved data from database to Excel file thro' c# code

Sample Image - DatabaseToExcel.gif

Introduction

This is project will be very easy to know about how to create a new excel file and how to insert data into excel cell.To do this we need to add reference to the Microsoft Excel.dll. To add reference right click on references in solution explorer->select add references->select com tab -> select microsoft excel.dll. And one more main thing is normally we will get one exception error call "old format or invalid type library". This code will solve that problem also. I have attached the complete code with comments of my application here.

 

The following code will come in the code view of the the Form1. This form will contain a single command button. During the click event of this button it ll retrieve the data from table and it ll insert that into Excel cells.

DatabaseToExcel.DB objDB;       // Declaring variabe of type db class 
Excel.Application application;  // Declaring variable in type of Excel <BR>                                // Application 
Excel.Workbook book;//Declaring variable in type of Excel Workbook 
Excel.Worksheet sheet;//Declaring variable in type of Excel WorkSheet 
string query = "select * from TestExcel"; 
string ConnectionString = "server=FARQUI;database=sample;uid=sa;pwd=;"; 

private void Form1_Load(object sender, System.EventArgs e) 
{ 
    //Creating instance for Excel Application Class. 
    //This will create new excel application. 
    application = new Excel.ApplicationClass(); 

    //This is to convert your sysdem data into Global language. 
    //It is necessary when your system data in some other Language. 
    System.Globalization.CultureInfo oldCI = <BR>                     System.Threading.Thread.CurrentThread.CurrentCulture ; 
    System.Threading.Thread.CurrentThread.CurrentCulture = <BR>                      new System.Globalization.CultureInfo("en-US"); 

    //This is to add new excell work book into your new application. 
    book = application.Workbooks.Add(Type.Missing); 

    //To make visible the excel application while execution. 
    application.Visible = true; 
} 

private void cmd_create_Click(object sender, System.EventArgs e) 
{ 
    try 
    { 
        DataTable dt = new DataTable(); 
        objDB = new DatabaseToExcel.DB(ConnectionString); 
        dt = objDB.runQuery(query); 
        ctl_progress.Visible = true; 
        ctl_progress.Minimum = 1; 
        ctl_progress.Maximum = dt.Rows.Count + 1; 
        ctl_progress.Value = 1; 
        //This is to access the first work sheet of your application 
        sheet = (Excel.Worksheet)book.Worksheets[1]; 
        sheet.Name = "SampleExcel"; 
        for(int i = 1 ; i <= dt.Rows.Count ; i++) 
        { 
            for(int j = 1 ; j <= dt.Columns.Count ; j++ ) 
            { 
                // This is to add the Data which retrieved from <BR>                // the database into your Excel Sheet. 
                ((Excel.Range)sheet.Cells[i,j]).Value2<BR>                                   = dt.Rows[i-1].ItemArray[j-1].ToString(); 
                //dt.Rows[i-1].ItemArray[j-1].ToString()--> This will retrieve <BR>                // data from your datatable's 
                //(i-1)th rows (j-1)st column 
            } 
            ctl_progress.Value += 1; 
        } 
        MessageBox.Show("Your Process Completed Successfully"); 
        ctl_progress.Visible = false; 
    } 
    catch(Exception e1) 
    { 
        MessageBox.Show(e1.Message); 
    } 
}

The following code is my DB.cs class. This is for handling database functions.

using System; 
using System.Data; 
using System.Data.SqlClient; 
namespace DatabaseToExcel 
{ 
    public class DB 
    { 
        string ConnectionString; 
        SqlConnection con; 
        public DB(string s) 
        { 
            ConnectionString = s; 
            con = new SqlConnection(ConnectionString); 
        } 
        public DataTable runQuery(string query) 
        { 
            DataTable dt = new DataTable(); 
            SqlDataAdapter da = new SqlDataAdapter(query,con); 
            da.Fill(dt); 
            return dt; 
        } 
    } 
}

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Raja Chandrasekaran
Web Developer
Turkey Turkey
Hai This is Raja Chandrasekaran working as a Software Engineer in Dewsoft Solution , Mumbai,India. Basically i am a BE graduate in Computer Stream done in Tamilnadu. I would like to work in C#. Creating easy codes for complex programs is my ambition.

Comments and Discussions

 
QuestionHow do i add headers Pin
javs7-Feb-07 19:49
memberjavs7-Feb-07 19:49 
AnswerRe: How do i add headers Pin
javs8-Feb-07 0:46
memberjavs8-Feb-07 0:46 
GeneralSystem.Runtime.InteropServices.COMException Pin
Fernando Velazco16-Jan-07 10:23
memberFernando Velazco16-Jan-07 10:23 
AnswerRe: System.Runtime.InteropServices.COMException Pin
haposai20078-Oct-07 8:09
memberhaposai20078-Oct-07 8:09 
GeneralWorks but, so slow Pin
Serdar YILMAZ24-Dec-06 0:42
memberSerdar YILMAZ24-Dec-06 0:42 
QuestionExcel.Application newXCL = new Excel.Application(); Pin
Crazyabtdotnet15-Sep-06 5:01
memberCrazyabtdotnet15-Sep-06 5:01 
GeneralQuestions about excel Pin
moussa_azar17-Aug-06 3:27
membermoussa_azar17-Aug-06 3:27 
QuestionThis code doesn't work in .Net 2003 Pin
bibicool1-Aug-06 22:23
memberbibicool1-Aug-06 22:23 
AnswerRe: This code doesn't work in .Net 2003 Pin
bibicool1-Aug-06 23:04
memberbibicool1-Aug-06 23:04 
GeneralRe: This code doesn't work in .Net 2003 Pin
martinkwong9-Sep-06 9:15
membermartinkwong9-Sep-06 9:15 
QuestionHow to embed SQL query in Excel sheet dynamically Pin
skumarcode7-Jul-06 6:28
memberskumarcode7-Jul-06 6:28 
Generalthere are Bugs...check this out Pin
bluexxx21-Feb-06 0:21
memberbluexxx21-Feb-06 0:21 
GeneralRe: there are Bugs...check this out Pin
Raja Chandrasekaran21-Feb-06 0:23
memberRaja Chandrasekaran21-Feb-06 0:23 
GeneralDoesnt work for big database !!! Pin
bluexxx20-Feb-06 8:07
memberbluexxx20-Feb-06 8:07 
GeneralRe: Doesnt work for big database !!! Pin
Raja Chandrasekaran20-Feb-06 20:43
memberRaja Chandrasekaran20-Feb-06 20:43 
GeneralRe: Doesnt work for big database !!! Pin
bluexxx20-Feb-06 23:32
memberbluexxx20-Feb-06 23:32 
GeneralRe: Doesnt work for big database !!! Pin
Raja Chandrasekaran21-Feb-06 0:24
memberRaja Chandrasekaran21-Feb-06 0:24 
GeneralToo big Pin
Yulianto.15-Jul-05 20:12
memberYulianto.15-Jul-05 20:12 
GeneralRe: Too big Pin
Raja Chandrasekaran15-Jul-05 20:44
memberRaja Chandrasekaran15-Jul-05 20:44 
GeneralFor Web Application Pin
gngholap9-Jun-06 1:00
membergngholap9-Jun-06 1:00 
GeneralRe: For Web Application Pin
Raja Chandrasekaran9-Jun-06 20:45
memberRaja Chandrasekaran9-Jun-06 20:45 
GeneralRe: For Web Application Pin
satheesh.713410-May-07 22:20
membersatheesh.713410-May-07 22:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 21 Feb 2006
Article Copyright 2005 by Raja Chandrasekaran
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid