How To Export Data That Coming From DataBase Via Function to Excel

Question

I have a question I didnt found appropriate answer yet....I have a function that retrieving data

From the DB and I want to by click on button to export all the data to excel File how can I do this??

Here is my function:

******************


Protected Sub btnExport_Click(ByVal sender as Object,ByVal e as EventArgs)

Try

Dim tmp as String = ""

Dim dba as New DBAccess
Dim ds as DataSet = dba.GetGlobalAdminUser(m_user.UserID)

If Not ds Is Nothing Then

Dim dt as DataTable = ds.Tables(0)

????

End IF



End Sub

score 0 · Accepted Answer · edited May 23 '17 at 12:13

0

You need to create the file yourself then, you should use the OpenXml library. You should avoid using OLE/COM

Here is one way that won't require a lot of coding:

How to Create/Open Excel files using OpenXml with C#

Here is another way of writing a dataset:

http://www.codeproject.com/Articles/692121/Csharp-Export-data-to-Excel-using-OpenXML-librarie

edited May 23 '17 at 12:13

Community

1
1

answered Jan 13 '14 at 03:56

T McKeown

12,971
1
25
32

it will require you to download some new GAC assemblies. – T McKeown Jan 13 '14 at 04:15
there is any way when its save to open that in a new window? – user3188868 Jan 13 '14 at 19:18
do you might know how to open it in a new window of make the window popup with the XLS File?? cus I dont want it to been save on my computer. – user3188868 Jan 13 '14 at 19:28
i don't understand what you are asking. a new window of what? excel? – T McKeown Jan 13 '14 at 19:34
yes because its saving it to my computer I want to show it on a new window there is any way to do it? – user3188868 Jan 13 '14 at 19:40
if you have excel open and you start a new workbook there is still only 1 instance of Excel. I still have no idea what you want. – T McKeown Jan 13 '14 at 19:42
okay after this rows:wSheet.Columns.AutoFit() Dim strFileName As String = "C:\datatable.xlsx" If System.IO.File.Exists(strFileName) Then System.IO.File.Delete(strFileName) End If wBook.SaveAs(strFileName) wBook.Close() _excel.Quit() its saving it to my computer I just want it to be more comfortable to the user that the Excel file will be open in a new window instead of telling him to go to my computer and see the file there. the user need to go the my computer drive C:\\ and find the file there I'm trying to make it more easier for him and open it in a new file. – user3188868 Jan 13 '14 at 19:45
Are you wanting a Dialog to popup and allow the user to select where the file should go???????? – T McKeown Jan 13 '14 at 19:47
Use SaveFileDialog, it will allow you to pop up a "Save As" looking dialog, look here http://stackoverflow.com/questions/11055258/how-to-use-savefiledialog-for-saving-images-in-c – T McKeown Jan 13 '14 at 19:57

score 0 · Answer 2 · answered Jan 13 '14 at 04:30

Try this method. You have to use like

Protected Sub btnExport_Click(ByVal sender as Object,ByVal e as EventArgs)

Try

Dim tmp as String = ""

Dim dba as New DBAccess
Dim ds as DataSet = dba.GetGlobalAdminUser(m_user.UserID)

If Not ds Is Nothing Then

Dim dt as DataTable = ds.Tables(0)

DatatableToExcel(dt)

End IF
...
...
End Sub

Private Sub DatatableToExcel(ByVal dtTemp As DataTable)
    Dim _excel As New Microsoft.Office.Interop.Excel.Application
    Dim wBook As Microsoft.Office.Interop.Excel.Workbook
    Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

    wBook = _excel.Workbooks.Add()
    wSheet = wBook.ActiveSheet()


    Dim dt As System.Data.DataTable = dtTemp
    Dim dc As System.Data.DataColumn
    Dim dr As System.Data.DataRow
    Dim colIndex As Integer = 0
    Dim rowIndex As Integer = 0

    For Each dc In dt.Columns
        colIndex = colIndex + 1
        _excel.Cells(1, colIndex) = dc.ColumnName
    Next

    For Each dr In dt.Rows
        rowIndex = rowIndex + 1
        colIndex = 0
        For Each dc In dt.Columns
            colIndex = colIndex + 1
            _excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
        Next
    Next

    wSheet.Columns.AutoFit()
    Dim strFileName As String = "C:\datatable.xlsx"
    If System.IO.File.Exists(strFileName) Then
        System.IO.File.Delete(strFileName)
    End If

    wBook.SaveAs(strFileName)
    wBook.Close()
    _excel.Quit()
End Sub

is there any way to open the XLS on an new window? – user3188868 Jan 13 '14 at 19:14 — user3188868, Jan 13 '14 at 19:14

How To Export Data That Coming From DataBase Via Function to Excel

2 Answers2