locked
Help converting sample c# to vb.net RRS feed

  • Question

  • User1058623934 posted
    Have tried to convert a "Export to Word" sample from c# to vb.net so I can try to adapt it but the vb version doesn't launch Word or give any error. Below are both versions of this test page. Please help and let me know what is wrong with the VB version. I've tried a couple online conversion tools with no luck. <%@ Page Language="C#" %>
    <script runat="server">
    void Page_Load(Object Src, EventArgs E)

    {

    try

    {

    System.Data.
    DataTable workTable = new System.Data.DataTable();

    workTable.TableName = "Customers";

    workTable.Columns.Add("Id");

    workTable.Columns.Add("Name");

    System.Data.DataRow workRow;

     

    for (int i = 0; i <= 9; i++)

    {

    workRow = workTable.NewRow();

    workRow[0] = i;

    workRow[1] =
    "CustName" + i.ToString();

    workTable.Rows.Add(workRow);

    }

    string strBody = DataTable2ExcelString(workTable);

    Response.AppendHeader("Content-Type", "application/vnd.ms-excel");

    Response.AppendHeader("Content-disposition", "attachment; filename=my.xls");

    Response.Write(strBody);

    }

    catch(Exception ex)

    {

    Response.Write(ex.ToString());

    }

    }

     

    public string DataTable2ExcelString(System.Data.DataTable dt)

    {

    StringBuilder sbTop = new StringBuilder();

    sbTop.Append("<html xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");

    sbTop.Append("xmlns=\"http://www.w3.org/TR/REC-html40\"><head><meta http-equiv=Content-Type content=\"text/html; charset=windows-1252\">");

    sbTop.Append("<meta name=ProgId content=Excel.Sheet><meta name=Generator content=\"Microsoft Excel 9\"><!--[if gte mso 9]>");

    sbTop.Append("<xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>" + dt.TableName + "</x:Name><x:WorksheetOptions>");

    sbTop.Append("<x:Selected/><x:ProtectContents>False</x:ProtectContents><x:ProtectObjects>False</x:ProtectObjects>");

    sbTop.Append("<x:ProtectScenarios>False</x:ProtectScenarios></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets>");

    sbTop.Append("<x:ProtectStructure>False</x:ProtectStructure><x:ProtectWindows>False</x:ProtectWindows></x:ExcelWorkbook></xml>");

    sbTop.Append("<![endif]-->");

    //@page definition is used to store document layout settings for the entire document.

    //The line below will add a header & footer to the downloaded Excel sheet.

    sbTop.Append(@"<style>

    @page

    {

    mso-header-data:'&R Date: &D Time: &T';

    mso-footer-data:'&L Proprietary & Confidential &R Page &P of &N';

    }

    </style>"

    );

    sbTop.Append(
    "</head><body><table>");

    string bottom = "</table></body></html>";

    StringBuilder sb = new StringBuilder();

    //Build the body

    sb.Append("<tr>");for (int i = 0; i < dt.Columns.Count; i++)

    {

    sb.Append(
    "<td>" + dt.Columns[i].ColumnName + "</td>");

    }

    sb.Append(
    "</tr>");

     

    //Items

    for (int x = 0; x < dt.Rows.Count; x++)

    {

    sb.Append(
    "<tr>");for (int i = 0; i < dt.Columns.Count; i++)

    {

    sb.Append(
    "<td>" + dt.Rows[x][i] + "</td>");

    }

    sb.Append(
    "</tr>");

    }

     

    string SSxml = sbTop.ToString() + sb.ToString() + bottom;

     

    return SSxml;

    }

    </script>

     

    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default5.aspx.vb" Inherits="Default5" %>

    <script runat="server">

    Private Sub Page_Load(ByVal Src As Object, ByVal E As EventArgs)

    Try

    Dim workTable As New System.Data.DataTable()

    workTable.Columns.Add("Id")

    workTable.Columns.Add("Name")Dim workRow As System.Data.DataRow

     

    For i As Integer = 0 To 9

    workRow = workTable.NewRow()

    workRow(0) = i

    workRow(1) =
    "CustName" + i.ToString()

    workTable.Rows.Add(workRow)

    Next

    Dim strBody As String = DataTable2WordString(workTable)

    Response.AppendHeader("Content-Type", "application/msword")

    Response.AppendHeader("Content-disposition", "attachment; filename=my.doc")

    Response.Write(strBody)

    Catch ex As Exception

    Response.Write(ex.ToString())

    End Try

    End Sub

    'the style of this method was adapted from Daniel Olson's DataTable2ExcelString method

    'at http://authors.aspalliance.com/olson/methods/DataTable2ExcelString.aspx

    Public Function DataTable2WordString(ByVal dt As System.Data.DataTable) As String

    Dim sbTop As New StringBuilder()

     

    sbTop.Append(
    "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:w='urn:schemas-microsoft-com:office:word' xmlns='http://www.w3.org/TR/REC-html40'> <head><title>Time</title>")

     

    sbTop.Append(
    "<!--[if gte mso 9]> <xml> <w:WordDocument> <w:View>Print</w:View> <w:Zoom>90</w:Zoom> </w:WordDocument> </xml> <![endif]-->")

     

    sbTop.Append(
    "<style> <!-- /* Style Definitions */ @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in ; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style></head>")

     

    sbTop.Append(
    "<body lang=EN-US style='tab-interval:.5in'> <div class=Section1> <h1>Time and tide wait for none</h1> <p style='color:red'><I>")

    sbTop.Append(DateTime.Now)

    sbTop.Append(
    "</I></p></div><table border='1'>")

     

    Dim bottom As String = "</table></body></html>"

     

    Dim sb As New StringBuilder()

    'Header

    sb.Append("<tr>")

    For i As Integer = 0 To dt.Columns.Count - 1

    sb.Append("<td>" + dt.Columns(i).ColumnName + "</td>")

    Next

    sb.Append("</tr>")

     

    'Items

    For x As Integer = 0 To dt.Rows.Count - 1

    sb.Append("<tr>")

    For i As Integer = 0 To dt.Columns.Count - 1

    sb.Append("<td>" + dt.Rows(x)(i) + "</td>")

    Next

    sb.Append("</tr>")

    Next

     

    Dim SSxml As String = sbTop.ToString() + sb.ToString() + bottom

     

    Return SSxml

    End Function

    </script>

    Wednesday, October 15, 2008 11:45 PM

Answers

  • User-1509636757 posted

      

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Try
                Dim workTable As System.Data.DataTable = New System.Data.DataTable()
                workTable.TableName = "Customers"
                workTable.Columns.Add("Id")
                workTable.Columns.Add("Name")
                Dim workRow As System.Data.DataRow
                For i As Integer = 0 To 9
                    workRow = workTable.NewRow()
                    workRow(0) = i
                    workRow(1) = "CustName" & i.ToString()
                    workTable.Rows.Add(workRow)
                Next i
                Dim strBody As String = DataTable2ExcelString(workTable)
                Response.AppendHeader("Content-Type", "application/vnd.ms-excel")
                Response.AppendHeader("Content-disposition", "attachment; filename=my.xls")
                Response.Write(strBody)
            Catch ex As Exception
                Response.Write(ex.ToString())
            End Try
        End Sub
    
        Public Function DataTable2ExcelString(ByVal dt As System.Data.DataTable) As String
            Dim sbTop As StringBuilder = New StringBuilder()
            sbTop.Append("<html xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel"" ")
            sbTop.Append("xmlns=""http://www.w3.org/TR/REC-html40""><head><meta http-equiv=Content-Type content=""text/html; charset=windows-1252"">")
            sbTop.Append("<meta name=ProgId content=Excel.Sheet><meta name=Generator content=""Microsoft Excel 9""><!--[if gte mso 9]>")
            sbTop.Append("<xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>" & dt.TableName & "</x:Name><x:WorksheetOptions>")
            sbTop.Append("<x:Selected/><x:ProtectContents>False</x:ProtectContents><x:ProtectObjects>False</x:ProtectObjects>")
            sbTop.Append("<x:ProtectScenarios>False</x:ProtectScenarios></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets>")
            sbTop.Append("<x:ProtectStructure>False</x:ProtectStructure><x:ProtectWindows>False</x:ProtectWindows></x:ExcelWorkbook></xml>")
            sbTop.Append("<![endif]-->")
            '@page definition is used to store document layout settings for the entire document.
            'The line below will add a header & footer to the downloaded Excel sheet.
            sbTop.Append("<style>" & ControlChars.CrLf & "@page" & ControlChars.CrLf & "{" & ControlChars.CrLf & "mso-header-data:'&R Date: &D Time: &T';" & ControlChars.CrLf & "mso-footer-data:'&L Proprietary & Confidential &R Page &P of &N';" & ControlChars.CrLf & "}" & ControlChars.CrLf & "</style>")
            sbTop.Append("</head><body><table>")
            Dim bottom As String = "</table></body></html>"
            Dim sb As StringBuilder = New StringBuilder()
            'Build the body
            sb.Append("<tr>")
            For i As Integer = 0 To dt.Columns.Count - 1
                sb.Append("<td>" & dt.Columns(i).ColumnName & "</td>")
            Next i
            sb.Append("</tr>")
            'Items
            For x As Integer = 0 To dt.Rows.Count - 1
                sb.Append("<tr>")
                For i As Integer = 0 To dt.Columns.Count - 1
                    sb.Append("<td>" & dt.Rows(x)(i) & "</td>")
                Next i
                sb.Append("</tr>")
            Next x
            Dim SSxml As String = sbTop.ToString() & sb.ToString() & bottom
            Return SSxml
        End Function
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 16, 2008 12:10 AM
  • User-1509636757 posted

     

    Perfect - thanks a lot!

    pleasure to help you.. and dont forget to mark answer and resolve the thread. thanx./.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 16, 2008 6:15 AM
  • User-1509636757 posted

    I was trying to make work was Word - any chance someone can help with the Word version?

    you just need to change the response header:

     

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Try
                Dim workTable As System.Data.DataTable = New System.Data.DataTable()
                workTable.TableName = "Customers"
                workTable.Columns.Add("Id")
                workTable.Columns.Add("Name")
                Dim workRow As System.Data.DataRow
                For i As Integer = 0 To 9
                    workRow = workTable.NewRow()
                    workRow(0) = i
                    workRow(1) = "CustName" & i.ToString()
                    workTable.Rows.Add(workRow)
                Next i
                Dim strBody As String = DataTable2ExcelString(workTable)
                Response.AppendHeader("Content-Type", "application/vnd.ms-word")
                Response.AppendHeader("Content-disposition", "attachment; filename=my.doc")
                Response.Write(strBody)
            Catch ex As Exception
                Response.Write(ex.ToString())
            End Try
        End Sub

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 16, 2008 10:07 AM

All replies

  • User-1509636757 posted

      

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Try
                Dim workTable As System.Data.DataTable = New System.Data.DataTable()
                workTable.TableName = "Customers"
                workTable.Columns.Add("Id")
                workTable.Columns.Add("Name")
                Dim workRow As System.Data.DataRow
                For i As Integer = 0 To 9
                    workRow = workTable.NewRow()
                    workRow(0) = i
                    workRow(1) = "CustName" & i.ToString()
                    workTable.Rows.Add(workRow)
                Next i
                Dim strBody As String = DataTable2ExcelString(workTable)
                Response.AppendHeader("Content-Type", "application/vnd.ms-excel")
                Response.AppendHeader("Content-disposition", "attachment; filename=my.xls")
                Response.Write(strBody)
            Catch ex As Exception
                Response.Write(ex.ToString())
            End Try
        End Sub
    
        Public Function DataTable2ExcelString(ByVal dt As System.Data.DataTable) As String
            Dim sbTop As StringBuilder = New StringBuilder()
            sbTop.Append("<html xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel"" ")
            sbTop.Append("xmlns=""http://www.w3.org/TR/REC-html40""><head><meta http-equiv=Content-Type content=""text/html; charset=windows-1252"">")
            sbTop.Append("<meta name=ProgId content=Excel.Sheet><meta name=Generator content=""Microsoft Excel 9""><!--[if gte mso 9]>")
            sbTop.Append("<xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>" & dt.TableName & "</x:Name><x:WorksheetOptions>")
            sbTop.Append("<x:Selected/><x:ProtectContents>False</x:ProtectContents><x:ProtectObjects>False</x:ProtectObjects>")
            sbTop.Append("<x:ProtectScenarios>False</x:ProtectScenarios></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets>")
            sbTop.Append("<x:ProtectStructure>False</x:ProtectStructure><x:ProtectWindows>False</x:ProtectWindows></x:ExcelWorkbook></xml>")
            sbTop.Append("<![endif]-->")
            '@page definition is used to store document layout settings for the entire document.
            'The line below will add a header & footer to the downloaded Excel sheet.
            sbTop.Append("<style>" & ControlChars.CrLf & "@page" & ControlChars.CrLf & "{" & ControlChars.CrLf & "mso-header-data:'&R Date: &D Time: &T';" & ControlChars.CrLf & "mso-footer-data:'&L Proprietary & Confidential &R Page &P of &N';" & ControlChars.CrLf & "}" & ControlChars.CrLf & "</style>")
            sbTop.Append("</head><body><table>")
            Dim bottom As String = "</table></body></html>"
            Dim sb As StringBuilder = New StringBuilder()
            'Build the body
            sb.Append("<tr>")
            For i As Integer = 0 To dt.Columns.Count - 1
                sb.Append("<td>" & dt.Columns(i).ColumnName & "</td>")
            Next i
            sb.Append("</tr>")
            'Items
            For x As Integer = 0 To dt.Rows.Count - 1
                sb.Append("<tr>")
                For i As Integer = 0 To dt.Columns.Count - 1
                    sb.Append("<td>" & dt.Rows(x)(i) & "</td>")
                Next i
                sb.Append("</tr>")
            Next x
            Dim SSxml As String = sbTop.ToString() & sb.ToString() & bottom
            Return SSxml
        End Function
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 16, 2008 12:10 AM
  • User1058623934 posted

    Perfect - thanks a lot!

    Thursday, October 16, 2008 5:50 AM
  • User-1509636757 posted

     

    Perfect - thanks a lot!

    pleasure to help you.. and dont forget to mark answer and resolve the thread. thanx./.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 16, 2008 6:15 AM
  • User1058623934 posted

    The provided Excel solution works perfectly and I'll need that one too but the example I was trying to make work was Word - any chance someone can help with the Word version? I've tried using the Excel but the page runs with no error and doesn't generate the Word doc even though C# version does.

     

    Thanks

    Thursday, October 16, 2008 9:57 AM
  • User-1509636757 posted

    I was trying to make work was Word - any chance someone can help with the Word version?

    you just need to change the response header:

     

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Try
                Dim workTable As System.Data.DataTable = New System.Data.DataTable()
                workTable.TableName = "Customers"
                workTable.Columns.Add("Id")
                workTable.Columns.Add("Name")
                Dim workRow As System.Data.DataRow
                For i As Integer = 0 To 9
                    workRow = workTable.NewRow()
                    workRow(0) = i
                    workRow(1) = "CustName" & i.ToString()
                    workTable.Rows.Add(workRow)
                Next i
                Dim strBody As String = DataTable2ExcelString(workTable)
                Response.AppendHeader("Content-Type", "application/vnd.ms-word")
                Response.AppendHeader("Content-disposition", "attachment; filename=my.doc")
                Response.Write(strBody)
            Catch ex As Exception
                Response.Write(ex.ToString())
            End Try
        End Sub

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 16, 2008 10:07 AM