All geek questions in one place

Export from sql server to excel file using asp.net and vb.net?

Is there a way to export all the data into a single table from SQL Server 2008 directly using asp.net and vb.net without using datagridview for EXCEL FILE?

+6
source share
6 answers

Basically, you just need to iterate over the columns and rows of your DataTable in order to output them in 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()
+13
source

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

and response.write the entire column header to "th" and all the data in "tr" 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());

When the page loads, you will be prompted to save the file. Save it to your desktop and open it with Excel

+1
source

what is the code so that it displays records in a table in pdf format with no data grid. In the code itself, it uses the im code. Private Sub Command1_Click (sender ByVal 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
+1
source

you can try Office Interop , which can create and process Office formats.
beware, however, it has TERRIBLE performance issues, and MS does not officially recommend using it on production servers, but rather on client machines. (although it may have changed for office 2007 and above).
If you need alternatives, there are many plugins for creating PDF documents, for example.
also see this question .

0
source

The way I did this in the past is to create a CSV file from a DataTable. Here is one good example of using extension methods for the DataTable class:

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

After you add this extension method to your project, you can output the CSV to the response stream as follows:

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

it was the right answer for me

  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

Many thanks to everyone!

0
source

Source: https://habr.com/ru/post/885656/

More articles:


All Articles