CODEWITHSUNDEEP
asp.netvb.netexport-to-excel
Adolfo Javier Torres
Adolfo Javier Torres

Reputation: 11

Using ASP.NET with Excel Files

I want to create an excel file from a web page in ASP.NET, I'm using the Microsoft.Office.Interop.Excel library, but I can't delete the file create on the side of the server, because I get in trouble with the times. I want to know if there is a way to create an Excel file with data of a database for example, and then that the client can download that file without saturate the server.

Actually I'm using this code in VB after the file is create in the server, but it doesn't work like I want:

Dim p As Process = New Process()
    p.StartInfo = New ProcessStartInfo()
    p.StartInfo.UseShellExecute = True
    p.StartInfo.FileName = "taskkill.exe"
    p.StartInfo.Arguments = "/F /IM EXCEL.exe"
    p.Start()
    p.WaitForExit()
    Thread.Sleep(24)
    Response.ContentType = "application/vnd.ms-excel"
    Response.AppendHeader("Content-Disposition", "attachment; filename=Reporte.xlsx")
    Response.TransmitFile(Server.MapPath("~/Resources/" & HiddenField1.Value.ToString() & ".xlsx"))
    'Thread.Sleep(10000)
    'Dim fso, f2
    'fso = CreateObject("Scripting.FileSystemObject")
    'f2 = fso.GetFile(Server.MapPath("~/Resources/" & HiddenField1.Value.ToString() & ".xlsx"))
    'f2.Delete()
    'Thread.Sleep(24)
    Response.End()

Thanks

Upvotes: 1

Views: 880

Answers (3)

ChrisLively
ChrisLively

Reputation: 88044

We use EPPlus to generate our excel documents.

It gives you quite a bit of control over the output and it's very fast. As John said, automating Excel on the server through a web site is completely unsupported by MS and they suggest you use alternatives.

Upvotes: 0

John Koerner
John Koerner

Reputation: 38079

Automating excel on a server is not officially supported by Microsoft and I suggest you find an alternative. Microsoft has a lot of good information on this topic here: http://support.microsoft.com/kb/257757.

All current versions of Microsoft Office were designed, tested, and configured to run as end-user products on a client workstation. They assume an interactive desktop and user profile. They do not provide the level of reentrancy or security that is necessary to meet the needs of server-side components that are designed to run unattended.

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

If you are building a solution that runs in a server-side context, you should try to use components that have been made safe for unattended execution. Or, you should try to find alternatives that allow at least part of the code to run client-side. If you use an Office application from a server-side solution, the application will lack many of the necessary capabilities to run successfully. Additionally, you will be taking risks with the stability of your overall solution.

They even offer suggestions on how to avoid using them:

Most server-side Automation tasks involve document creation or editing. Office 2007 supports new Open XML file formats that let developers create, edit, read, and transform file content on the server side. These file formats use the System.IO.Package.IO namespace in the Microsoft .NET 3.x Framework to edit Office files without using the Office client applications themselves. This is the recommended and supported method for handling changes to Office files from a service.

They also have tutorials on using the APIs:

http://msdn.microsoft.com/en-us/library/bb739834.aspx

http://msdn.microsoft.com/en-us/library/bb727373.aspx

Upvotes: 1

Ann L.
Ann L.

Reputation: 13965

You might consider using something other than Excel to create the file you want to send.

For example, there is Carlos Aguilar Mares's free Excel XML library: http://www.carlosag.net/tools/excelxmlwriter/.

Also, some reporting tools like SQL Server Reporting Services have the option of exporting native Excel files. That's one way to generate an Excel file, complete with formatting.

Finally, Excel can read other types of files, such as CSV and HTML files. Those are a lot easier to produce and don't require Excel itself to generate.

A good run-through of the options: Generating an Excel file in ASP.NET

Upvotes: 1

Related Questions