How to Import data from Sql Server to XLS document

1. Add Reference called Microsoft Excel 12.0 Object Library to your project.

2. Add a web page under it

using System;

using System.Configuration;

using System.Data;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

using Excel = Microsoft.Office.Interop.Excel;

public partial class XLSConvert : System.Web.UI.Page 

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            using (SqlConnection con = new SqlConnection(@"Data Source=sqlexpres;Initial Catalog=test;User ID=sa;Password=sa"))

            {

                SqlCommand cmd = new SqlCommand("select * from Country_Tbl;select * from test.dbo.State_Tbl;", con);

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                DataSet ds = new DataSet();

                da.Fill(ds);

                if (ds.Tables.Count > 0)

                {

                    PrepareExcelSheet(ds);

                    ScriptManager.RegisterStartupScript(this, GetType(), "", 

                        "alert('Excel file created, you can find the file @ c:\\DB_Tables.xls');", true);

                }

            }

        }

    }

    public void PrepareExcelSheet(DataSet ds)

    {

        object misedValue = System.Reflection.Missing.Value;

        Excel.Application xlsApp = new Excel.ApplicationClass();

        Excel.Workbook xlsWorkBook = xlsApp.Workbooks.Add(misedValue);

        for (int k = 0; k < ds.Tables.Count; k++)

        {

            Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlsWorkBook.Worksheets.get_Item(k+1);

            xlWorkSheet.Name = ds.Tables[k].TableName.ToString();

            for (int i = 1; i <= ds.Tables[k].Columns.Count; i++)

                xlWorkSheet.Cells[1, i] = ds.Tables[k].Columns[i - 1].ColumnName.ToString();

            for (int i = 1; i <= ds.Tables[k].Rows.Count; i++)

                for (int j = 1; j <= ds.Tables[k].Columns.Count; j++)

                    xlWorkSheet.Cells[i + 1, j] = ds.Tables[k].Rows[i - 1][j - 1].ToString();

            releaseObject(xlWorkSheet);

        }

        xlsWorkBook.SaveAs("DB_Tables.xls", Excel.XlFileFormat.xlWorkbookNormal, misedValue, misedValue, misedValue, misedValue,

            Excel.XlSaveAsAccessMode.xlExclusive, misedValue, misedValue, misedValue, misedValue, misedValue);

        xlsWorkBook.Close(true, misedValue, misedValue);

        xlsApp.Quit();

        releaseObject(xlsWorkBook);

        releaseObject(xlsApp);

    }

    private void releaseObject(object obj)

    {

        try

        {

            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);

            obj = null;

        }

        catch (Exception ex) { obj = null; }

        finally { GC.Collect(); }

    }

}

Out Put:

Tag: convert, import data, Sql server, xls