在ASP.net与宏编辑Excel文档 [英] Editing an Excel document with Macros in ASP.net

问题描述

是否有可能以任何方式通过包含宏的ASP.net页面编辑一个Excel工作表。我试图打开Excel工作表，它似乎只是挂，而不是加载的Excel。页面上的测试，而不宏的工作完全正常？

Is it possible in any way to edit an excel sheet through an ASP.net page that contains macro. I have tried to open the Excel sheet and it seems to just hang rather than load the excel. Testing on a page without macros works perfectly fine?

推荐答案

的 免责声明：我不知道Excel的许可协议，如果在服务器中，使用Excel的我不知道过程违反与否。这纯粹是如何得到它的工作的技术说明。建议读者检查许可协议，看它是否允许这样做或没有。不同的Office版本可能有不同的许可协议。我在一些财富100/500公司使用这种方法，他们似乎并不在乎。进入数字。的

Disclaimer: I don't know the Excel license agreement and I don't know if utilizing Excel in a server process violates it or not. This is purely a technical description of how to get it working. The reader is advised to check the license agreement to see if it's allowed to do so or not. Different Office versions may have different license agreements. I used this method at several Fortune 100/500 companies and they didn't seem to care. Go figure.

的此解决方案，但它有一些限制和要求在它运行在服务器控制了相当数量。该服务器还需要有大量的内存。的

要开始，请确保您在服务器上执行的每一个办公室功能的完整安装，以使Excel不会尝试安装的东西，如果你尝试使用一个功能，不是present。

To start, make sure that you perform a complete installation of every single Office feature on the server so that Excel won't try to install something if you attempt to use a feature that's not present.

您还需要有正确的权限在服务器上创建一个专用的用户帐户。的我不能告诉你究竟是因为在我的情况下，我们控制的服务器，我们给了管理员权限给该用户。的

You also need to create a dedicated user account on the server that has the right privileges. I can't tell you what exactly because in my case we controlled the server and we gave admin rights to this user.

当你有用户帐号，您至少需要登录一次作为用户和运行Excel（preferably所有Office应用程序），以便它可以创建它的设置。

When you have the user account, you need to log in as that user and run Excel (preferably all Office applications) at least once so that it can create its settings.

您还需要配置Excel时，它作为一个COM对象创建此用户帐户运行。对于这一点，你需要进入DCOM配置在服务器上，并配置启动和激活权限为 Excel.Application 反对使用新的用户帐户。的我不知道如果我没有记错，但我觉得这一步之后，运行Excel作为一个交互式用户稍问题。的

You also need to configure Excel to run under this user account when it's created as a COM object. For this, you need to go into DCOM Config on the server and configure Launch and Activation Permissions for the Excel.Application object to use your new user account. I'm not sure if I remember correctly, but I think after this step, running Excel as an interactive user was slightly problematic.

在默认情况下，Office应用程序试图在屏幕上显示各种信息：警告，问题等，因为当你使用从Web应用程序的Office应用程序，这些必须关闭，它运行的服务器，使人类用户韩元'T围绕解雇这些消息 - Office程序将只是坐在那里无限期等待着被解雇消息

By default, Office applications try to display various messages on the screen: warnings, questions, etc. These must be turned off because when you utilize an Office application from a web application, it runs on the server so a human user won't be around to dismiss these messages - the Office program will just sit around indefinitely, waiting for the message to be dismissed.

您需要设置（在最小）这些属性：

You need to set (at the minimum) these properties:

DisplayAlerts = false
AskToUpdateLinks = false
AlertBeforeOverwriting = false
Interactive = false
Visible = false
FeatureInstall = 0 'msoFeatureInstallNone

从Excel禁用UI消息。的如果您使用Excel 2010，可能有更多的，但我不熟悉。的

如果你有在其中宏的Excel文件，你可能要禁用Excel宏安全性 - 不能编程方式，由于显而易见的原因

If you have Excel files with macros in them, you may have to disable macro security in Excel - that can't be done programmatically, for obvious reasons.

要访问Excel服务，实现一个管理器对象，将实际持有的Excel引用 - 不要试图保持在页面Excel.Application对象，因为你的页面code就会变得非常复杂，你可能不能够正确地干净的东西了。

To access Excel services, implement a manager object that will actually hold the Excel reference - don't try to hold the Excel.Application object in the page because your page code will become very complicated and you may not be able to properly clean things up.

这是保存Excel引用对象可以是一个单独的DLL或进程外的一个服务器。您必须确保，但是，当你你的给定的线程上获得的Excel实例总是创建一个新的Excel实例的。默认值是一个已经运行Excel的实例也将成为其他请求，但不会为你工作，因为同一个Excel实例不能多个线程之间共享。在IIS中的每个请求处理线程都必须有它自己的Excel实例的 - 如果你共享实例，你就会有各种各样的问题。这意味着，你的服务器需要有相当多的内存让Excel运行多个实例。的这不是我的问题becasue我们控制服务器。的

The object that holds the Excel reference may be a separate DLL or an out-of-process server. You must make sure, however, that when you acquire an instance of Excel on a given thread you always create a new Excel instance. The default is that an already running Excel instance will also serve other requests but that won't work for you because the same Excel instance cannot be shared among multiple threads. Each request-processing thread in IIS must have its own Excel instance - if you share instances, you'll have all kinds of problems. This means that your server will need to have quite a bit of memory to have many instances of Excel running. This was not an issue for me becasue we controlled the server.

如果可以的话，尝试创建一个彻头彻尾的进程内文件（.exe）COM服务器，因为这样你可以保持在一个单独的进程中Excel引用。这是可能得到它的工作使用进程内文件（.dll）COM对象，但它会更危险到你的应用程序池 - 如果Excel的崩溃，它会崩溃您的应用程序池以及

If you can, try to create an out-of-proc (.exe) COM server because this way you can hold the Excel reference in a separate process. It's possible to get it working using an in-proc (.dll) COM object but it'll be more risky to your application pool - if Excel crashes, it'll crash your app pool as well.

当你有一个.exe文件服务器，你可以通过几种可能的方式参数：

When you have an .exe server, you can pass parameters in several possible ways:

1. 请你的经理客体的COM对象，并传递参数属性。


2. 传递参数作为命令行parameteres的.exe文件，因为它启动。


3. 在一个文本/二进制文件传递参数;传递命令行的文件的名称。

的我用所有这些，发现COM对象选择最干净的。的

在你的经理对象，遵循以下原则：

In your manager object, follow these guidelines:

• 裹使用Excel中的在try..catch 块每一个功能来捕捉任何可能的例外。


• 始终通过调用明确释放所有Excel对象对Marshal.ReleaseComObject（），然后你的变量设置为空作为一旦你不需要他们。在最后块始终释放这些对象，以确保发生故障的Excel的方法调用不会导致悬空COM​​对象。


• 如果您尝试在Excel中使用任何格式的功能（页头，页边距等），您必须安装一台打印机和访问您用来运行Excel的用户帐户。如果你没有一个活动打印机（preferably连接到服务器），格式化相关的功能可能无法正常工作。


• 当发生错误的时候，请关闭您使用Excel的实例。这不太可能，你可以从Excel相关的错误中恢复的时间越长你保持实例，时间越长，它使用的资源。


• 当您退出Excel，请务必警惕递归调用，code - 如果您的异常处理程序尝试关闭Excel的，而你的code已经关停的​​Excel的过程中，你会结束了一个死Excel的实例。


• 呼叫 GC.Collect的（）和 GC.WaitForPendingFinalizers（）调用 Application.Quit（）的方法，以确保在.NET Framework释放所有的Excel COM对象立即

• Wrap every single function that uses Excel in a try..catch block to capture any possible exception.
• Always explicitly release all Excel objects by calling Marshal.ReleaseComObject() and then setting your variables to null as soon as you don't need them. Always release these objects in a finally block to make sure that a failed Excel method call won't result in a dangling COM object.
• If you try to use any formatting features in Excel (page header, margins, etc.) you must have a printer installed and accessible to the user account that you use to run Excel. If you don't have an active printer (preferably attached to the server), formatting-related features may not work.
• When an error happens, close the Excel instance that you're using. It's not likely that you can recover from Excel-related errors and the longer you keep the instance, the longer it uses resources.
• When you quit Excel, make sure that you guard that code against recursive calls - if your exception handlers try to shut down Excel while your code is already in the process of shutting down Excel, you'll end up with a dead Excel instance.
• Call GC.Collect() and GC.WaitForPendingFinalizers() right after calling the Application.Quit() method to make sure that the .NET Framework releases all Excel COM objects immediately.

修改：约翰·桑德斯可能有关于许可协议的一个点 - 我不能建议有关。我确实使用Word / Excel的人在大客户所有Intranet应用程序和要求使用Word / Excel中的项目是给定的。

Edit: John Saunders may have a point regarding the license agreement - I can't advise about that. The projects that I did using Word/Excel were all intranet applications at large clients and the requirement to use Word/Excel was a given.

他还提供了链接有一些工具，可能是有用的，尽管这些图书馆将无法获得完整的Excel功能，如果这是你需要什么，你没有选择。如果你不需要完整的Excel功能，检查出这些库 - 他们可能更易于使用

The link he provided also has some tools that may be useful, although those libraries won't have full Excel functionality and if that's what you need, you don't have a choice. If you don't need full Excel functionality, check out those libraries - they may be much simpler to use.

几个环节，以人试图实现这种方法可能是有用的：

A few links that may be useful to people trying to implement this approach:

• StackOverflow问题


• 可能的替代产品


• COM服务器激活和窗口站

• StackOverflow question
• Possible alternate products
• COM server activation and window stations

这篇关于在ASP.net与宏编辑Excel文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！