使用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的公司使用这种方法，他们似乎并不在乎。 Go图。

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.

这个解决方案是有效的，但它有一些局限性，需要对它运行的服务器进行相当大的控制。服务器还需要大量的内存。

要开始，请确保在服务器上执行每个Office功能的完全安装，以便如果您尝试使用不存在的功能，Excel将不会尝试安装某些功能。

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（最好是所有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 Config，并为 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应用程序时，它将在服务器上运行，以便人类用户不会在要解除这些消息 - 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，可能会有更多，但我不熟悉。

to disable UI messages from Excel. If you use Excel 2010, there may be more, but I'm not familiar with that.

如果您的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对象，因为您的页面代码将变得非常复杂，您可能无法正确清理。

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实例。这不是我的问题，因为我们控制了服务器。

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.

如果可以，尝试创建一个out-of-proc（。 exe）COM服务器，因为这样你可以在一个单独的进程中保存Excel引用。可以使用in-proc（.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. 让您的经理


2. 启动时将参数作为命令行参数传递给.exe。


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

我使用所有这些，发现COM对象选项最干净

在您的经理对象中，请遵循以下准则：

In your manager object, follow these guidelines:

• 使用 try..catch 块中的每个使用Excel的单个函数来捕获任何可能的异常。


• 始终显式释放所有Excel通过调用 Marshal.ReleaseComObject（）然后在不需要它们的情况下将变量设置为 null 。始终在终止块中释放这些对象，以确保失败的Excel方法调用不会导致悬挂的COM对象。


• 如果您尝试在Excel中使用任何格式化功能（页眉，页边距等），则必须安装打印机并且可以访问用于运行Excel的用户帐户。如果您没有活动的打印机（最好连接到服务器），与格式相关的功能可能无法正常工作。


• 发生错误时，关闭您使用的Excel实例。您可能无法从与Excel相关的错误中恢复，并且保留实例的时间越长，资源使用的时间越长。


• 当您退出Excel时，请确保您保护该代码递归调用 - 如果您的异常处理程序尝试在代码已关闭Excel的过程中关闭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.

编辑：约翰·桑德斯（John Saunders）可能对许可协议有所了解 - 我无法就此提出建议。我使用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问题


• 可能的替代产品

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

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