• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 592
  • Last Modified:

How to export a datatable to excel

I have a datatable that I want to export to excel.
I do not want to use the Excel Interop dlls.

Also I do not want to bind the datatable to the datagrid as I waant to skip certtain rows from the datatable.

how can i iterate through the rows and columns and do this
0
countrymeister
Asked:
countrymeister
1 Solution
 
snurkerCommented:
If you have ODBC drivers for the database, you should be able to draw teh fields from Excel Going to Data> Get External Data> From Other Sources. Then select the appropriate source or Select from M$ Query. Connect to your database and select the fields that you need.

You can break the connection after pulling the data.
0
 
countrymeisterAuthor Commented:
Snurker
I have the datatable fetched from the database server. I have no need to make another trip to the database.
All I want is to iterate through the rows and columns and write <HTML> tages and TR and TD tags.
and then use a streamwriter to write to an excel file  .xls file
0
 
disruptCommented:
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kalpesh ChhatralaSoftware ConsultantCommented:
0
 
CodeCruiserCommented:
>Also I do not want to bind the datatable to the datagrid as I waant to skip certtain rows from the datatable.

If you are skipping based on simple conditions, you can filter the datatable before setting it as datasource to grid.
0
 
countrymeisterAuthor Commented:
I am little disappointed. I clearly mentioned that I do want to use a datagrid, but I get an example link of using grid view.
Not sure if the audience is aware that doing a render requires that paging and sorting needs to be turned off OR EnableEventValidation should be set to false at the page level.

Next I get another example of Excel Interop dll's , this is exactly soemthing I do not want to use as per my intiial question.

I have attached my solution, maybe helpful for someone else, the string returned can be written out to an .xls file using a StreamWriter.
Protected Function ConvertToHtmlFile(ByVal targetTable As DataTable) As String
        Dim strHtmlFile As String = String.Empty
        Dim strAccession As String = String.Empty
        Dim strHeader1 As String = "StartDate: " & txtStartDate.Text & "  EndDate: " & txtEndDate.Text

        Dim strBuilder As StringBuilder = New StringBuilder
        strBuilder.Append("<html xmlns='http://www.w3.org/1999/xhtml'>")
        strBuilder.Append("<head>")
        strBuilder.Append("<title>")
        strBuilder.Append(strHeader1)
        strBuilder.Append("</title>")
        strBuilder.Append("</head>")
        strBuilder.Append("<body>")
        strBuilder.Append("<table border='1px' cellpadding='5' cellspacing='0' ")
        strBuilder.Append("style='border: solid 1px Silver  font-size: x-small '>")
        strBuilder.Append("<tr align='left' valign='top'>")
        strBuilder.Append("<td align='left' valign='top'>")
        strBuilder.Append(strHeader1)
        strBuilder.Append("</td>")
        strBuilder.Append("</tr>")
        strBuilder.Append("<tr align='left' valign='top'>")
        For Each dtColumn As DataColumn In targetTable.Columns

            strBuilder.Append("<td align='left' valign='top'>")
            strBuilder.Append(dtColumn.ColumnName)
            strBuilder.Append("</td>")
        Next

        strBuilder.Append("</tr>")

        For Each drow As DataRow In targetTable.Rows
            strBuilder.Append("<tr align='left' valign='top'>")
            If strAccession <> drow(0).ToString Then
                For Each dtColumn As DataColumn In targetTable.Columns
                    strBuilder.Append("<td align='left' valign='top'>")
                    strBuilder.Append(drow(dtColumn.ColumnName).ToString())
                    strBuilder.Append("</td>")
                Next
            Else
                For Each dtColumn As DataColumn In targetTable.Columns
                    strBuilder.Append("<td align='left' valign='top'>")
                    If dtColumn.ColumnName = "Order" Or dtColumn.ColumnName = "OrderLine" Then
                        strBuilder.Append(String.Empty)
                    Else
                        strBuilder.Append(drow(dtColumn.ColumnName).ToString())
                    End If
                    strBuilder.Append("</td>")
                Next
            End If
            strAccession = drow(0).ToString
        Next

        strBuilder.Append("</table>")
        strBuilder.Append("</body>")
        strBuilder.Append("</html>")

        strHtmlFile = strBuilder.ToString()

        Return strHtmlFile
    End Function

Open in new window

0
 
countrymeisterAuthor Commented:
I pretty much found my solution, just giving points for the effort made by the expert
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now