使用asp.net和vb.net从sql server导出到excel文件？

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?

有没有办法直接使用asp.net和vb.net从sql server 2008导出一个表中的整个数据而不使用datagridview到EXCEL FILE?

6 个解决方案

#1

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.

基本上,您只需要遍历DataTable的列和行,以便将它们输出到响应中。此链接向您展示如何。

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()

#2

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

将页面的contenttype设置为“ContentType =”application / vnd.ms-excel“”

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

并且响应。将所有列标题写入“th”,将所有数据写入“tr”的“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

在页面加载时,将要求您保存文件。将其保存在桌面上并使用Excel打开它

#3

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

是什么代码使它以pdf显示表格记录而没有数据网格。输入代码这是我使用的代码。 Private Sub Command1_Click(ByVal sender As System.Object,ByVal e As System.EventArgs)处理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

#4

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.

您可以尝试Office Interop,它可以创建和操作Office格式。但请注意,它具有可怕的性能问题,并且MS官方建议不要在生产服务器上使用它,而是在客户端计算机上使用它。 (虽然它可能已经改变了2007年及以上的办公室)。如果您需要替代品 - 例如,有大量插件可用于创建PDF文档。还 - 看到这个问题。

#5

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:

我过去的做法是从DataTable创建一个CSV文件。这是使用DataTable类的扩展方法的一个很好的例子:

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:

将该扩展方法添加到项目后,可以将CSV输出到响应流,如下所示:

        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();

#6

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 !!!

非常感谢每一个人!

#1