Click here to Skip to main content
15,889,909 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication9
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cnn;
            string connectionString = null;
            string sql = null;
            string data = null;
            int i = 0;
            int j = 0;

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            connectionString = "Data Source=VISHAL-PC; Initial Catalog=arif; Integrated security=true";
           
            cnn = new SqlConnection(connectionString);
            cnn.Open();
            sql = "SELECT * FROM khan1";
            SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
            DataSet ds = new DataSet();
            dscmd.Fill(ds);


            for (i = 0; i <= ds.Tables[0].Rows.Count -1; i++)
            {
                for (j = 0; j <= ds.Tables[0].Columns.Count -1; j++)
                {
                    

                    data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                   
                    
                        xlWorkSheet.Cells[i + 2, j + 1] = data;
                        
                }
            }
            
            xlWorkBook.SaveAs("E:\\Arif khan\\ted.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
           
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Excel file created , you can find the file  E:\\Arif khan\\ted.xls");


        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }

        }
    }
}


What I have tried:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication9
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cnn;
            string connectionString = null;
            string sql = null;
            string data = null;
            int i = 0;
            int j = 0;

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            connectionString = "Data Source=VISHAL-PC; Initial Catalog=arif; Integrated security=true";
           
            cnn = new SqlConnection(connectionString);
            cnn.Open();
            sql = "SELECT * FROM khan1";
            SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
            DataSet ds = new DataSet();
            dscmd.Fill(ds);


            for (i = 0; i <= ds.Tables[0].Rows.Count -1; i++)
            {
                for (j = 0; j <= ds.Tables[0].Columns.Count -1; j++)
                {
                    

                    data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                   
                    
                        xlWorkSheet.Cells[i + 2, j + 1] = data;
                        
                }
            }
            
            xlWorkBook.SaveAs("E:\\Arif khan\\ted.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
           
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Excel file created , you can find the file  E:\\Arif khan\\ted.xls");


        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }

        }
    }
}
Posted
Updated 18-Sep-17 22:36pm
Comments
Richard MacCutchan 19-Sep-17 4:14am    
What is the question?
Member 13403292 19-Sep-17 4:17am    
question is how can i export execl from sql database with same coloumn name, and excel sheet save at runtime and open also.
Richard MacCutchan 19-Sep-17 4:35am    
Use the Excel Interop namespace: Microsoft.Office.Interop.Excel namespace ()[^]
Member 13403292 19-Sep-17 4:39am    
can u plz me send the entire code?
Richard MacCutchan 19-Sep-17 5:15am    
No of course not. We are not here to do your work for you.

1 solution

You can do this directly in Excel itself with the included DBQuery tool. Here are instructions on how:
* Use Microsoft Query to retrieve external data - Excel[^]
* How-To Create An Excel Query! - YouTube[^]

Or a more advanced VBA approach:
* Querying a SQL Server Database From an Excel Formula with VBA - YouTube[^]
 
Share this answer
 
Comments
Member 13403292 19-Sep-17 5:57am    
no this is not helpful
Graeme_Grant 19-Sep-17 6:06am    
It is an alternative & efficient method that will give your app a performance boost using minimal code and leveraging the power of Excel.

Create the dbQuery in Excel, save it. record a macro running it and you now have the excel vba commands to add to your C# code. Now you app can open Excel, load & execute the query, and excel does all the heavy lifting for you. far easier than doing the query in your app, then push all the data out to excel.

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