Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 5 down vote favorite
2

is there a way to export the whole data in one table from sql server 2008 directly using asp.net and vb.net without using datagridview to EXCEL FILE?

share|improve this question

6 Answers

up vote 12 down vote accepted

Basically, you just need to loop over the columns and rows of your DataTable in order to output them to the response. This link shows you how.

In C#:

        DataTable dt = GetData();
        string attachment = "attachment; filename=Employee.xls";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/vnd.ms-excel";
        string tab = "";
        foreach (DataColumn dc in dt.Columns)
        {
            Response.Write(tab + dc.ColumnName);
            tab = "\t";
        }
        Response.Write("\n");

        int i;
        foreach (DataRow dr in dt.Rows)
        {
            tab = "";
            for (i = 0; i < dt.Columns.Count; i++)
            {
                Response.Write(tab + dr[i].ToString());
                tab = "\t";
            }
            Response.Write("\n");
        }
        Response.End();

In VB.NET

    Dim dt As DataTable = GetData()
    Dim attachment As String = "attachment; filename=Employee.xls"
    Response.ClearContent()
    Response.AddHeader("content-disposition", attachment)
    Response.ContentType = "application/vnd.ms-excel"
    Dim tab As String = ""
    For Each dc As DataColumn In dt.Columns
        Response.Write(tab + dc.ColumnName)
        tab = vbTab
    Next
    Response.Write(vbLf)

    Dim i As Integer
    For Each dr As DataRow In dt.Rows
        tab = ""
        For i = 0 To dt.Columns.Count - 1
            Response.Write(tab & dr(i).ToString())
            tab = vbTab
        Next
        Response.Write(vbLf)
    Next
    Response.End()
share|improve this answer
yes this the what i want exactly!!! – HAJJAJ Apr 12 '11 at 8:23
Excellent @Town, its working very fine in asp.net – Sai Kalyan Akshinthala Jul 19 '12 at 4:33
up vote 1 down vote

Set the contenttype of your page to "ContentType="application/vnd.ms-excel""

and the response.write all you column header to "th" and all data to "tr"'s with "td"

 var exceltable = new StringBuilder();
            exceltable.Append("<HTML><BODY><TABLE Border=0>");
            exceltable.AppendFormat("<TR>");

            exceltable.AppendFormat(string.Concat("<TD>Merchantname</TD>"));
            exceltable.AppendFormat(string.Concat("<TD>Pendingstatus</TD>"));
            exceltable.AppendFormat(string.Concat("<TD>Date</TD>"));
            exceltable.AppendFormat(string.Concat("<TD>Ordervalue</TD>"));
            exceltable.AppendFormat(string.Concat("<TD>Customer commision</TD>"));
            exceltable.AppendFormat(string.Concat("<TD>Affiliate commision</TD>"));
            exceltable.AppendFormat(string.Concat("<TD>Customerid</TD>"));
            exceltable.AppendFormat(string.Concat("<TD>Paid</TD>"));
            exceltable.AppendFormat(string.Concat("<TD>Paid date</TD>"));

            exceltable.AppendFormat("</TR>");
            foreach (DataRow row in dt.Rows)
            {
                exceltable.AppendFormat("<TR>");

                exceltable.AppendFormat(string.Concat("<TD>", row["NAME"].ToString(), "</TD>"));
                exceltable.AppendFormat(string.Concat("<TD>", row["pendingstatus"].ToString(), "</TD>"));
                exceltable.AppendFormat(string.Concat("<TD>", row["datetimeclickout"].ToString(), "</TD>"));
                exceltable.AppendFormat(string.Concat("<TD>", row["ordervalue"].ToString(), "</TD>"));
                exceltable.AppendFormat(string.Concat("<TD>", row["customercommision"].ToString(), "</TD>"));
                exceltable.AppendFormat(string.Concat("<TD>", row["affiliatecommision"].ToString(), "</TD>"));
                exceltable.AppendFormat(string.Concat("<TD>", row["user_id"].ToString(), "</TD>"));
                exceltable.AppendFormat(string.Concat("<TD>", row["paid"].ToString(), "</TD>"));
                exceltable.AppendFormat(string.Concat("<TD>", row["paiddate"].ToString(), "</TD>"));

                exceltable.AppendFormat("</TR>");
            }
            exceltable.Append("</TABLE></BODY></HTML>");
            Response.Write(exceltable.ToString());

On page load you will be asked to save the file. Save it on your desktop and open it with Excel

share|improve this answer
up vote 1 down vote

what is the code to make it display table records in pdf with out a data grid.`enter code he this is the code im using. Private Sub Command1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Command1.Click Dim startTime As Date

    Command1.Enabled = False

    startTime = Now()
    lblEnd.Text = ""

    Dim clPDF As New clsPDFCreator
    Dim strFile As String
    Dim i As Integer

    '  output NAME
    strFile = App_Path & "\Demo.pdf"

    With clPDF
        .Title = "Pay Day Report"           ' TITLE
        .ScaleMode = clsPDFCreator.pdfScaleMode.pdfCentimeter
        .PaperSize = clsPDFCreator.pdfPaperSize.pdfA4                     ' PAGE FORMAT
        .Margin = 0                                 ' Margin
        .Orientation = clsPDFCreator.pdfPageOrientation.pdfPortrait               ' ORIENTATION

        .EncodeASCII85 = chkASCII85.Checked

        .InitPDFFile(strFile)

        ' DEFINING FONT
        .LoadFont("Fnt1", "Times New Roman")
        .LoadFont("Fnt2", "Arial", clsPDFCreator.pdfFontStyle.pdfItalic)
        .LoadFont("Fnt3", "Courier New")
        .LoadFontStandard("Fnt4", "Courier New", clsPDFCreator.pdfFontStyle.pdfBoldItalic)


        .LoadImgFromBMPFile("Img1", App_Path & "\img\20x20x24.bmp")
        .LoadImgFromBMPFile("Img2", App_Path & "\img\200x200x24.bmp")


        For i = 0 To 5
            '     open a page
            .BeginPage()


            .DrawText(19, 1.5, "page " & Trim(CStr(.Pages)), "Fnt1", 12, clsPDFCreator.pdfTextAlign.pdfAlignRight)
            .DrawObject("Footers")
            .DrawText(10.5, 27, "Unifrieght Sage", "Fnt1", 18, clsPDFCreator.pdfTextAlign.pdfCenter)

            .SetTextHorizontalScaling(70)
            .DrawText(20, 25, "Regnumber", "Fnt2", 14, clsPDFCreator.pdfTextAlign.pdfAlignRight)
            .DrawText(1, 25, "Name", "Fnt2", 14, clsPDFCreator.pdfTextAlign.pdfAlignLeft)
            .DrawText(10.5, 25, "Surname", "Fnt2", 14, clsPDFCreator.pdfTextAlign.pdfCenter)

            .SetTextHorizontalScaling(100)

            Dim Name1 As String
            Dim Surname As String
            Dim Regnumber As String
            Dim dt As DataTable
            Dim tab As String = ""

            Dim a As Integer
            Dim cmd As OdbcCommand = New OdbcCommand("Select *from tblMain ", cn)
            cmd.CommandType = CommandType.Text
            Dim DR As OdbcDataReader = cmd.ExecuteReader
            For Each dc As DataColumn In dt.Columns
                '.DrawText(tab + dc.ColumnName)
                tab = vbTab

            Next

            While DR.Read
                Name1 = DR("name")
                Surname = DR("surname")
                Regnumber = ("regnumber")

                Dim i As Integer
                For Each drk As DataRow In dt.Rows
                    tab = ""
                    For i = 0 To dt.Columns.Count - 1


                        .SetTextHorizontalScaling(70)
                        .DrawText(20, 23 - a, Regnumber, "Fnt2", 14, clsPDFCreator.pdfTextAlign.pdfAlignRight)
                        .DrawText(1, 23 - a, Name1, "Fnt2", 14, clsPDFCreator.pdfTextAlign.pdfAlignLeft)
                        .DrawText(10.5, 23 - a, Surname, "Fnt2", 14, clsPDFCreator.pdfTextAlign.pdfCenter)
                        .SetTextHorizontalScaling(100)
                        tab = vbTab
                    Next
                Next
                .SetCharSpacing(3)

            End While
            .EndPage()



            ' this is for the footers
            .StartObject("Footers", clsPDFCreator.pdfObjectType.pdfAllPages)
            .DrawText(10, 1.5, "Designed by Renegate", "Fnt3", 8, clsPDFCreator.pdfTextAlign.pdfCenter)
            .DrawText(20, 1.5, " of " & Trim(CStr(.Pages)), "Fnt1", 12, clsPDFCreator.pdfTextAlign.pdfAlignRight)
            .EndObject()

        Next
        ' closing the document
        .ClosePDFFile()

    End With

    Dim Elapsed As TimeSpan = Now().Subtract(startTime)
    lblEnd.Text = Elapsed.ToString()

    Command1.Enabled = True

    Call Shell("rundll32.exe url.dll,FileProtocolHandler " & (strFile), vbMaximizedFocus)
End Sub
share|improve this answer
up vote 0 down vote

you can try Office Interop which can create and manipulate Office formats.
beware, however- it has TERRIBLE performance issues, and MS officialy recommend NOT to use it on production servers, but rather- on client machines. (although it may have changed for office 2007 and upwards).
If you need alternatives- there are plenty of plugins for creating PDF documents, for example.
also- see this question.

share|improve this answer
up vote 0 down vote

The way I've done it in the past is to create a CSV file from a DataTable. Here's one nice example using extension methods to the DataTable class:

http://blog.runxc.com/post/2009/06/24/Exporting-a-DataTable-to-Excel-(DataTable-to-CSV).aspx

After you add that extension method to your project, you could output the CSV to the response stream like this:

        Response.ContentType = "application/vnd.ms-excel";
        Response.ContentEncoding = new System.Text.UTF8Encoding();
        Response.AddHeader("content-disposition", "attachment; filename=report.xls");
        Response.Write(myDataTable.toCSV());
        Response.End();
share|improve this answer
up vote 0 down vote

for me this is was the right answer 

 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim ad As New results()
    Dim dt As results.ResultsDataTable
    dt = ad.Read()

    Dim attachment As String = "attachment; filename=USurvey.xls"
    Response.ClearContent()
    Response.AddHeader("content-disposition", attachment)
    Response.ContentType = "application/vnd.ms-excel"
    Dim tab As String = ""
    For Each dc As DataColumn In dt.Columns
        Response.Write(tab + dc.ColumnName)
        tab = vbTab
    Next
    Response.Write(vbLf)

    Dim i As Integer
    For Each dr As DataRow In dt.Rows
        tab = ""
        For i = 0 To dt.Columns.Count - 1
            Response.Write(tab & dr(i).ToString())
            tab = vbTab
        Next
        Response.Write(vbLf)
    Next
    Response.[End]()
    'export to excel
End Sub

thanks a lot every one !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.