export datareader to excel

Question

_{Thursday, October 8, 2009 2:38 PM}

Hi,

Ever wonder if I can do it. I would like ask the forum. Is there a way I can export data from my SqlDataReader to Excel???

Please help with the algorithm , links, or code. I googled but all it got me was from asp.net to Excel. I have a window forms application.

Thank You Guys,

Suman

All replies (3)

_{Thursday, October 8, 2009 2:59 PM ✅Answered}

Could you get your data as a datatable instead of as a datareader?

If so, I have code to display a DataTable in Excel here:

http://msmvps.com/blogs/deborahk/archive/2009/07/23/writing-data-from-a-datatable-to-excel.aspx

Otherwise, you could tailor this to use a datareader instead.

Hope this helps.www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

_{Wednesday, November 30, 2011 9:04 PM}

install PIA's for microsoft office , add the reference in com for excel, 

Imports Excel = Microsoft.Office.Interop.Excel

Imports System.Net.Mime.MediaTypeNames

SQLConn.ConnectionString = ConnString 'Set the Connection String

        SQLConn.Open() 'Open the connection

        SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command

        SQLCmd.CommandText = SQLStr 'Sets the SQL String

        Dim xl As New Excel.Application

        Dim wSheet As Excel.Worksheet

        Try

            xl.Workbooks.Open("path\abcde.xlsx")

            wSheet = xl.Workbooks("abcde.xlsx").Sheets("Sheet1")

            'all your data objects here like connection and command

            reader = SQLCmd.ExecuteReader() 'Executes SQL Commands Non-Querys only

            Dim i As Integer = 1

            If reader.HasRows Then

                While reader.Read()

                    wSheet.Cells(i, 1) = reader.Item("colname1")

                    wSheet.Cells(i, 2) = reader.Item("colname2")

                    wSheet.Cells(i, 3) = reader.Item("colname3")

                    wSheet.Cells(i, 4) = reader.Item("colname4")

                    i = i + 1

                End While

            End If

        Catch ex As Exception

            MessageBox.Show(ex.Message)

            xl.Workbooks.Close()

        End Try

        xl.Workbooks.Close()

_{Wednesday, October 17, 2012 3:02 PM}

Hi,

You can use DataTable.Load method to convert IDataReader to DataTable and then you can easily export DataTable to Excel with this Excel C# library:

// Create new Excel file.
var excelFile = new ExcelFile();

// Add new worksheet and export data from IDataReader into it.
var dataTable = new DataTable("MyTable").Load(dataReader);
excelFile.Worksheets.Add(dataTable.TableName).InsertDataTable(dataTable, 0, 0, true);

// Export to XLS format.
excelFile.SaveXls(dataTable.TableName + ".xls");