How to export to excel on client system in vb.net 2010.

Question

1.00/5 (2 votes)

See more:

SQL-Server-2012

Dear,

I have a web application i need to export the data to the excel on the client system. I tried but the file gets created on the server instead of client system. Please help me.
The application is in vb.net 2010 and sql server 2012. office 2007.

Regards,
Mohinder Singh

Posted 15-Jul-14 19:37pm

mvengaqua

Add a Solution

Comments

Maciej Los 16-Jul-14 1:47am

What have you tried?

Sergey Alexandrovich Kryukov 16-Jul-14 2:20am

First of all, please stop posting your fake "answers". Out of 7 "answers", only one was the attempt to really answer someone. Such posts are considered abusive. Instead, use comments and/or "Improve question".
—SA

3 solutions

Solution 1

Of course it happens on server system, as everything in ASP.NET. How else? And you really should better do it on the server side. The client will be able to download the file created, or the resource directly from the HTTP response, if you provide the URL and the resource for that. (Again, how else?)

In file approach, you just create a file on the server and generate the line with URL pointing to the file on some HTML page:

HTML

<a href="myDocument.xlsx">Download the document</a>

If you want to avoid creation of a file, you should be able to write the document directly to the network stream of your HTTP request. First of all, you need to generate appropriate content-type header:
http://msdn.microsoft.com/en-us/library/system.web.httpresponse.headers%28v=vs.110%29.aspx[^].

The headers should be ""application/vnd.ms-excel". You can find most types here:
http://en.wikipedia.org/wiki/Content-type[^].

Those content types are standardized here: http://www.iana.org/assignments/media-types[^].

In all cases, the user will be given and option to open the file by a default application (it will work if such other application is installed, which is possible on many platforms, not only Windows, say, thanks to Libre Office) or save it (it will be the only version if none of the installed applications can work with excel files).

—SA

Posted 15-Jul-14 20:15pm

Sergey Alexandrovich Kryukov

Solution 2

You can create excel file at system from ASP.net application. you need to add some simple steps of code to create an excel file.

for more details kindly refer this link

http://sharmanuj.blogspot.in/2010/06/create-excel-file-with-save-dialog-in.html

Posted 15-Jul-14 21:06pm

Sharmanuj

Comments

mvengaqua 16-Jul-14 7:34am

Dear Anuj,
The output in the excel file is comin in one row only.

like this"BillNo\tDate\tTableName\tPaxNo\tTotalAmount\tPayMode\tCompanyName\tWaiterName\t\n65\t7/8/2014 12:00:00 AM\t3\t1\t580.37\tCASH\tTrucount\tRajesh\t\n66\t7/8/2014 12:00:00 AM\t1\t1\t330.68\tCASH\tTrucount\tSuresh\t\n67\t7/8/2014 12:00:00 AM\t2\t1\t169.08\tCASH\tTrucount\tAmit\t\n68\t7/8/2014 12:00:00 AM\t12\t1\t330.68\tCASH\tTrucount\tRajesh\t\n69\t7/16/2014 12:00:00 AM\t0\t0\t443.84\tUPD\tTrucount\t0\t\n70\t7/16/2014 12:00:00 AM\t2\t1\t718.05\tUPD\tTrucount\tRajesh\t"

Please let me know how to get this like table in excel.

Regards,
Venga

Sharmanuj 17-Jul-14 1:10am

Hi Venga,

The data will be palced in different rows and columns according to your data table values,
to separate data in column we use
HttpContext.Current.Response.Write("\t");
and to put data in next row
HttpContext.Current.Response.Write("\n");

could you please share your data result so that i can check in that.

Regards
Anuj Sharma

Sharmanuj 17-Jul-14 1:12am

Hi Venga,

As you want to achieve this in VB.net and the solution is in C#.Net. could you please share your piece of vb.net code so that i can check the same.

Regards,

Anuj Sharma

mvengaqua 17-Jul-14 4:30am

Dear Anuj,

Please find the Code in vb.net:

Public Sub SaveExcelFile(ByVal fileName As String, ByVal dtData As DataTable)

HttpContext.Current.Response.Clear()
HttpContext.Current.Response.ContentType = "application/ms-excel"
HttpContext.Current.Response.AddHeader("Content-Disposition", "inline; filename=" + fileName + "")
HttpContext.Current.Response.Flush()
Dim colCount, rowCount As Integer
For colCount = 0 To dtData.Columns.Count - 1
HttpContext.Current.Response.Write(dtData.Columns(colCount).ColumnName.ToString())
HttpContext.Current.Response.Write("\t")
Next
For rowCount = 0 To dtData.Rows.Count - 1
For colCount = 0 To dtData.Columns.Count - 1
HttpContext.Current.Response.Write("\n")
HttpContext.Current.Response.Write(dtData.Rows(rowCount)(colCount).ToString())
HttpContext.Current.Response.Write("\t")
Next
Next
HttpContext.Current.Response.End()
End Sub

Sharmanuj 17-Jul-14 6:41am

Hi Venga,

I could see there is a line mistake in the for loop. kindly use the below updated code and that will work to get all the data in separate columns

Public Sub SaveExcelFile(ByVal fileName As String, ByVal dtData As DataTable)

HttpContext.Current.Response.Clear()
HttpContext.Current.Response.ContentType = "application/ms-excel"
HttpContext.Current.Response.AddHeader("Content-Disposition", "inline; filename=" + fileName + "")
HttpContext.Current.Response.Flush()
Dim colCount, rowCount As Integer
For colCount = 0 To dtData.Columns.Count - 1
HttpContext.Current.Response.Write(dtData.Columns(colCount).ColumnName.ToString())
HttpContext.Current.Response.Write("\t")
Next
For rowCount = 0 To dtData.Rows.Count - 1
HttpContext.Current.Response.Write("\n")
For colCount = 0 To dtData.Columns.Count - 1
HttpContext.Current.Response.Write(dtData.Rows(rowCount)(colCount).ToString())
HttpContext.Current.Response.Write("\t")
Next
Next
HttpContext.Current.Response.End()
End Sub

mvengaqua 18-Jul-14 0:49am

Dear Anuj,

Its comin in one cell only. Please help me out

Regards,
Venga

Sharmanuj 18-Jul-14 1:32am

Hi Venga,

this would come in the different cell as required. I did some modification in code. use the html table control to create rows and cells.
Kindly see the below code

Protected Sub GenerateXls(ByVal fileName As String, ByVal dtData As DataTable)
Try
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.ContentType = "application/ms-excel"
HttpContext.Current.Response.AddHeader("Content-Disposition", "inline; filename=" + fileName + "")
HttpContext.Current.Response.Flush()
Dim colCount, rowCount As Integer
HttpContext.Current.Response.Write("<Table>")
HttpContext.Current.Response.Write("<TR>")

For colCount = 0 To dtData.Columns.Count - 1
HttpContext.Current.Response.Write("<TD>")
HttpContext.Current.Response.Write(dtData.Columns(colCount).ColumnName.ToString())
'HttpContext.Current.Response.Write("\t")
HttpContext.Current.Response.Write("</TD>")
Next
HttpContext.Current.Response.Write("</TR>")

For rowCount = 0 To dtData.Rows.Count - 1
'HttpContext.Current.Response.Write("\n")
HttpContext.Current.Response.Write("<TR>")
For colCount = 0 To dtData.Columns.Count - 1
HttpContext.Current.Response.Write("<TD>")
HttpContext.Current.Response.Write(dtData.Rows(rowCount)(colCount).ToString())
'HttpContext.Current.Response.Write("\t")
HttpContext.Current.Response.Write("</TD>")
Next
HttpContext.Current.Response.Write("</TR>")
Next
HttpContext.Current.Response.Write("</Table>")
HttpContext.Current.Response.End()

Catch ex As Exception

End Try

End Sub

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sharmanuj · Accepted Answer · 2014-07-17T19:33:00

Here is the solution to get all the values in different rows and cells.

XML

Protected Sub GenerateXls(ByVal fileName As String, ByVal dtData As DataTable)
        Try
            HttpContext.Current.Response.Clear()
            HttpContext.Current.Response.ContentType = "application/ms-excel"
            HttpContext.Current.Response.AddHeader("Content-Disposition", "inline; filename=" + fileName + "")
            HttpContext.Current.Response.Flush()
            Dim colCount, rowCount As Integer
            HttpContext.Current.Response.Write("<Table>")
            HttpContext.Current.Response.Write("<TR>")

            For colCount = 0 To dtData.Columns.Count - 1
                HttpContext.Current.Response.Write("<TD>")
                HttpContext.Current.Response.Write(dtData.Columns(colCount).ColumnName.ToString())
                'HttpContext.Current.Response.Write("\t")
                HttpContext.Current.Response.Write("</TD>")
            Next
            HttpContext.Current.Response.Write("</TR>")

            For rowCount = 0 To dtData.Rows.Count - 1
                'HttpContext.Current.Response.Write("\n")
                HttpContext.Current.Response.Write("<TR>")
                For colCount = 0 To dtData.Columns.Count - 1
                    HttpContext.Current.Response.Write("<TD>")
                    HttpContext.Current.Response.Write(dtData.Rows(rowCount)(colCount).ToString())
                    'HttpContext.Current.Response.Write("\t")
                    HttpContext.Current.Response.Write("</TD>")
                Next
                HttpContext.Current.Response.Write("</TR>")
            Next
            HttpContext.Current.Response.Write("</Table>")
            HttpContext.Current.Response.End()

        Catch ex As Exception

        End Try

    End Sub