Converting Database records to Excel file(Very simple code)

• Download demo project - 435 Kb

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 
                                // 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 = 
                     System.Threading.Thread.CurrentThread.CurrentCulture ; 
    System.Threading.Thread.CurrentThread.CurrentCulture = 
                      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 
                // the database into your Excel Sheet. 

                ((Excel.Range)sheet.Cells[i,j]).Value2
                                   = dt.Rows[i-1].ItemArray[j-1].ToString(); 
                //dt.Rows[i-1].ItemArray[j-1].ToString()--> This will retrieve 
                // 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; 
        } 
    } 
}