自动化perl中的Excel文件处理并避免对话/ UI交互

How can a I guarantee that no pop-up dialogs will appear when I automate Microsoft Excel through OLE? I'm using a Perl module (Win32::OLE). I can avoid most dialog pop-ups using the following code:

当我通过OLE自动化Microsoft Excel时，如何保证不会出现弹出对话框？我正在使用Perl模块（Win32 :: OLE）。我可以使用以下代码避免大多数对话框弹出窗口：

use Win32::OLE;
use Win32::OLE::Variant;
use Win32::OLE::Const;

my $excel_symbols = Win32::OLE::Const->Load('Microsoft Excel');
my $excel = Win32::OLE->new('Excel.Application', sub { $_[0]->Quit();} );
$excel->{'Visible'} = 0;
$excel->{'DisplayAlerts'} = 0;
$excel->Workbooks->Open('c:\some_excel_file.xls',
  { 'UpdateLinks' => $excel_symbols->{'xlUpdateLinksNever'},
    'ReadOnly' => 1,
    'IgnoreReadOnlyRecommended' => 1
  });

However for some files, I continue to get a dialog with the following text:

但是对于某些文件，我继续使用以下文本进行对话：

This file is not a recognizable format.

此文件不是可识别的格式。

If you know the file is from another program which is incompatible with Microsoft Excel, click Cancel, then open this file in its original application. If you want to open the file later in Microsoft Excel, save it in a format that is compatible, such as text format.

如果您知道该文件来自与Microsoft Excel不兼容的其他程序，请单击“取消”，然后在其原始应用程序中打开此文件。如果要稍后在Microsoft Excel中打开该文件，请将其保存为兼容的格式，例如文本格式。

If you suspect the file is damaged, click Help for more information about solving the problem.

如果您怀疑文件已损坏，请单击“帮助”以获取有关解决问题的详细信息。

If you still want to see what text is contained in the file, Click OK. Then click Finish in the Text Import Wizard.

如果您仍想查看文件中包含的文本，请单击“确定”。然后在文本导入向导中单击“完成”。

OK Cancel

确定取消

Sometimes a similar dialog appears that contains 'OK', 'Cancel' and 'Help' buttons.

有时会出现类似的对话框，其中包含“确定”，“取消”和“帮助”按钮。

I cannot control the quality of files that are provided to the scripts.

我无法控制提供给脚本的文件的质量。

4 个解决方案

#1

You could consider using Spreadsheet::ParseExcel (albeit it may lack features you need) or Apache POI (albeit it will need some wrapping to use in a Perl script) instead of calling the Excel engine over OLE. That way you won't get any Excel-generated dialogs.

您可以考虑使用Spreadsheet :: ParseExcel（尽管它可能缺少您需要的功能）或Apache POI（虽然它需要在Perl脚本中使用一些包装）而不是通过OLE调用Excel引擎。这样你就不会得到任何Excel生成的对话框。

#2

I revisited this issue and found a solution.

我重新审视了这个问题并找到了解决方案。

Copy the file before processing to a temporary location. Then save the file before closing it in Excel:

在处理之前将文件复制到临时位置。然后在Excel中关闭文件之前保存文件：

File::Copy::copy('c:\some_excel_file.xls', 'c:\temp\SFO3jfd.xls');
my $book = $excel->Workbooks->Open('c:\temp\SFO3jfd.xls',
  { 'UpdateLinks' => $excel_symbols->{'xlUpdateLinksNever'},
    'ReadOnly' => 1,
    'IgnoreReadOnlyRecommended' => 1
  });
$book->Save();
$book->Close();

Why this works:

为何如此有效：

Excel 2003 automatically recalculates the formulas in documents that were created in an older version of Excel. Furthermore, macros could be invoked when the document is opened. All of this means that there could be changes made on a document, even though your script doesn't perform any such operations.

Excel 2003自动重新计算在旧版Excel中创建的文档中的公式。此外，可以在打开文档时调用宏。所有这些意味着即使您的脚本不执行任何此类操作，也可能对文档进行更改。

By saving the document before closing, you avoid the dialog requesting that you save the file. Using a temporary file ensures that the original file does not get changed during the validation operation. If you aren't concerned about this, you might consider validating in-place.

通过在关闭之前保存文档，可以避免请求保存文件的对话框。使用临时文件可确保在验证操作期间不会更改原始文件。如果您不关心这一点，您可以考虑就地验证。

#3

Here is full documentation for Open method. I wonder if CorruptLoad parameter is what you need.

这是Open方法的完整文档。我想知道CorruptLoad参数是否是你需要的。

#4

-2

If you are trying of process all xl files in the tree, some of them may be open by other users and have the ~ prefix.

如果您正在尝试处理树中的所有xl文件，其中一些可能由其他用户打开并具有〜前缀。

#1

#2