Export SQL to Excel

Question

How can i export my data from SQL server 2008 into Excel 2010 or later ?

i have tried on SQL way:

sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 0;
GO
RECONFIGURE;
GO
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\testing.xls;Extended Properties=EXCEL 12.0;HDR=YES', 
'SELECT NO_ORDRE, Date FROM [Sheet1$]') 
SELECT [NO_ORDRE], GETDATE() FROM ORDRE
GO

Unfortuntely i receive error: The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' can not be used for distributed queries because the provider is configured to run in STA mode.

and then i tried on C# way:

 public class ExportToExcel
    {
        private Excel.Application app;

        private Excel.Workbook workbook;
        private Excel.Worksheet previousWorksheet;
       // private Excel.Range workSheet_range;
        private string folder;

        public ExportToExcel(string folder)
        {

            this.folder = folder;
            this.app = null;
            this.workbook = null;
            this.previousWorksheet = null;
           // this.workSheet_range = null;

            createDoc();
        }

        private void createDoc()
        {
            try
            {
                app = new Excel.Application();
                app.Visible = false;
                workbook = app.Workbooks.Add(1);
            }
            catch (Exception excThrown)
            {
                throw new Exception(excThrown.Message);
            }
            finally
            {
            }
        }

        public void shutDown()
        {
            try
            {
                workbook = null;
                app.Quit();
            }
            catch (Exception excThrown)
            {
                throw new Exception(excThrown.Message);
            }
            finally
            {
            }
        }

        public void ExportTable(string query, string sheetName)
        {
            SqlDataReader myReader = null;
            try
            {
                using (var connectionWrapper = new Connexion())
                {
                    var connectedConnection = connectionWrapper.GetConnected();
                    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.Add(Missing.Value, Missing.Value, 1, Excel.XlSheetType.xlWorksheet);

                    worksheet.Name = sheetName;
                    previousWorksheet = worksheet;

                    SqlCommand myCommand = new SqlCommand(query, connectionWrapper.conn);

                    myReader = myCommand.ExecuteReader();

                    int columnCount = myReader.FieldCount;

                    for (int n = 0; n < columnCount; n++)
                    {
                        //Console.Write(myReader.GetName(n) + "\t");
                        createHeaders(worksheet, 1, n + 1, myReader.GetName(n));
                    }

                    int rowCounter = 2;
                    while (myReader.Read())
                    {
                        for (int n = 0; n < columnCount; n++)
                        {
                            //Console.WriteLine();
                            //Console.Write(myReader[myReader.GetName(n)].ToString() + "\t");
                            addData(worksheet, rowCounter, n + 1, myReader[myReader.GetName(n)].ToString());
                        }
                        rowCounter++;
                    }

                }
            }

            catch (Exception e)
            {
                Console.WriteLine(e.ToString());

            }
            finally
            {
                if (myReader != null && !myReader.IsClosed)
                {
                    myReader.Close();
                }   
                myReader = null;
            }

        }

        public void createHeaders(Excel.Worksheet worksheet, int row, int col, string htext)
        {
            worksheet.Cells[row, col] = htext;
        }

        public void addData(Excel.Worksheet worksheet, int row, int col, string data)
        {
            worksheet.Cells[row, col] = data;
        }

        public void SaveWorkbook()
        {

            String folderPath = "C:\\My Files\\" + this.folder;

            if (!System.IO.Directory.Exists(folderPath))
            {
                System.IO.Directory.CreateDirectory(folderPath);
            }

            string fileNameBase = "db";
            String fileName = fileNameBase;
            string ext = ".xlsx";
            int counter = 1;

            while (System.IO.File.Exists(folderPath + fileName + ext))
            {
                fileName = fileNameBase + counter;
                counter++;
            }

            fileName = fileName + ext;

            string filePath = folderPath + fileName;

            try
            {
                workbook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());

            }
        }



    }

unfortunately i got error: Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).

Any idea how can i export SQL to Excel ?

Answer 1

Your best bet might be to just write it out to a CSV. Excel registers itself as the file handler for CSV files, so it will open in excel by default.

For example:

private void SQLToCSV(string query, string Filename)
{

    SqlConnection conn = new SqlConnection(connection);
    conn.Open();
    SqlCommand cmd = new SqlCommand(query, conn);
    SqlDataReader dr = cmd.ExecuteReader();

    using (System.IO.StreamWriter fs = new System.IO.StreamWriter(Filename))
    {
        // Loop through the fields and add headers
        for (int i = 0; i < dr.FieldCount; i++)
        {
            string name = dr.GetName(i);
            if (name.Contains(","))
                name = "\"" + name + "\"";

            fs.Write(name + ",");
        }
        fs.WriteLine();

        // Loop through the rows and output the data
        while (dr.Read())
        {
            for (int i = 0; i < dr.FieldCount; i++)
            {
                string value = dr[i].ToString();
                if (value.Contains(","))
                    value = "\"" + value + "\"";

                fs.Write(value + ",");
            }
            fs.WriteLine();
        }

        fs.Close();
    }
}

Answer 2

I have modified code which was given above as and is working. Edit according to your requirements

namespace ExcelExport
{

public class ExportToExcel
{
    string strCon = ConfigurationManager.ConnectionStrings["SafewayGVDemoDBContext"].ConnectionString;
    private Microsoft.Office.Interop.Excel.Application app;
    private Microsoft.Office.Interop.Excel.Workbook workbook;
    private Microsoft.Office.Interop.Excel.Worksheet previousWorksheet;
    // private Excel.Range workSheet_range;
    private string folder;

    public ExportToExcel(string folder)
    {

        this.folder = folder;
        this.app = null;
        this.workbook = null;
        this.previousWorksheet = null;
        // this.workSheet_range = null;

        createDoc();
    }

    private void createDoc()
    {
        try
        {
            app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = false;
            workbook = app.Workbooks.Add(1);
        }
        catch (Exception excThrown)
        {
            throw new Exception(excThrown.Message);
        }
        finally
        {
        }
    }

    public void shutDown()
    {
        try
        {
            workbook = null;
            app.Quit();
        }
        catch (Exception excThrown)
        {
            throw new Exception(excThrown.Message);
        }
        finally
        {
        }
    }

    public void ExportTable(string procName, string sheetName)
    {
        SqlDataReader myReader = null;
        try
        {

            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.Add(Missing.Value, Missing.Value, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
            using (SqlConnection Sqlcon = new SqlConnection(strCon))
            {
                SqlCommand cmd = new SqlCommand();
                Sqlcon.Open();
                cmd.Connection = Sqlcon;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procName;
                cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));
                cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;
                cmd.Parameters["@pvchAction"].Value = "select";
                worksheet.Name = sheetName;
                previousWorksheet = worksheet;

                myReader = cmd.ExecuteReader();

                int columnCount = myReader.FieldCount;

                for (int n = 0; n < columnCount; n++)
                {
                    //Console.Write(myReader.GetName(n) + "\t");
                    createHeaders(worksheet, 1, n + 1, myReader.GetName(n));
                }

                int rowCounter = 2;
                while (myReader.Read())
                {
                    for (int n = 0; n < columnCount; n++)
                    {
                        //Console.WriteLine();
                        //Console.Write(myReader[myReader.GetName(n)].ToString() + "\t");
                        addData(worksheet, rowCounter, n + 1, myReader[myReader.GetName(n)].ToString());
                    }
                    rowCounter++;
                }
            }

        }

        catch (Exception e)
        {
            Console.WriteLine(e.ToString());

        }
        finally
        {
            if (myReader != null && !myReader.IsClosed)
            {
                myReader.Close();
            }
            myReader = null;
        }

    }

    public void createHeaders(Microsoft.Office.Interop.Excel.Worksheet worksheet, int row, int col, string htext)
    {
        worksheet.Cells[row, col] = htext;
    }

    public void addData(Microsoft.Office.Interop.Excel.Worksheet worksheet, int row, int col, string data)
    {
        worksheet.Cells[row, col] = data;
    }

    public void SaveWorkbook()
    {

        String folderPath = @"C:\My Files\" + this.folder;

        if (!System.IO.Directory.Exists(folderPath))
        {
            System.IO.Directory.CreateDirectory(folderPath);
        }

        string fileNameBase = "db";
        String fileName = fileNameBase;
        string ext = ".xlsx";
        int counter = 1;
        //System.IO.File.Open(folderPath + fileName + ext, System.IO.FileMode.Open);
        while (System.IO.File.Exists(folderPath + @"\"+ fileName + ext))
        {
            fileName = fileNameBase + counter;
            counter++;
        }

        fileName = fileName + ext;

        string filePath = folderPath +@"\"+ fileName;

        try
        {
            workbook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

        }
        catch (Exception e)
        {
            Console.WriteLine(e.ToString());

        }
    }

}