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?
6 Answers
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()
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
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
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.
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();
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 !!!