如何将Excel文件保存在Win32 :: OLE中的工作目录中 [英] How to save Excel file in working directory in Win32::OLE

问题描述

我试图用Perl解析一个Excel文件。从中提取所需的信息后，关闭Excel文件。最后我试图在同一个目录中保存一个不同名称的新Excel文件。但是这个Excel已经存储在我的文档文件夹中。

  use Storable; 
使用Cwd; 
 
使用Win32 :: OLE; 
使用Win32 :: OLE qw（with with）; 
使用Win32 :: OLE; 
使用Win32 :: OLE :: Const'Microsoft Excel'; 
 
使用Excel :: Writer :: XLSX; 
 
我的$ Excel = Win32 :: OLE-> new（Excel.Application）; 
 my $ excel = $ Excel-> Workbooks-> Add（）; 
 
我的$ sheet = $ excel->工作表（1）; 
 $ sheet-> Activate（）; 
 
我的$ new_file =Temp_file.xlsm; 
我的$ new_excel = cwd.'\\'。$ new_file; 
 $ new_excel =〜s / \ // \\ / g; 
 
 $ excel-> SaveAs（$ new_excel）; 
 
 $ Excel-> {DisplayAlerts} = 0; 
 $ excel-> {Saved} = 1; 
 $ excel->关闭; 
  

解决方案

这是一个基于您的代码的更新。首先，您将让 Win32 :: OLE 错误被忽略。相反，设置： $ Win32 :: OLE :: Warn = 3 所以它会在出现问题时嘎吱作响。其次，您尝试获取 Excel.Application 实例的方式是不正确的。一方面，如果出现问题，您将有Excel的实例将保持浮动。

您还会混淆Excel实例，工作簿和工作表包含的内容。如果您确实有 $ Win32 :: OLE :: Warn = 3 ，您将收到通知，说明发生了什么问题。

您还应该始终在脚本中使用使用strict 和使用警告。如果他们知道你的问题不是由于一些琐碎的错字造成的，那么其他人会更倾向于帮助。

你也不需要三个单独的使用Win32 :: OLE 语句。

下面的代码工作。最后，如果您通过 Win32 :: OLE 来操作您的工作表，那么就没有原因在你的代码中有 Excel :: Writer :: XLSX 。

 使用功能'say'; 
使用strict; 
使用警告; 
 
使用File :: Spec :: functions qw（rel2abs）; 
使用Win32 :: OLE qw（with with）; 
使用Win32 :: OLE :: Const'Microsoft Excel'; 
 $ Win32 :: OLE :: Warn = 3; 
 
我的$ excel = eval {
 Win32 :: OLE-> GetActiveObject（'Excel.Application'）; 
} || Win32 :: OLE-> new（'Excel.Application'，sub {$ _ [0]  - > Quit}）; 
 
我的$ wb = $ excel->工作簿 - >添加; 
 my $ sheet = $ wb-> Worksheets-> Add; 
 
 $ wb-> SaveAs（rel2abs（'temp_file.xlsm'））; 
 
 $ excel-> {DisplayAlerts} = 0; 
 $ wb-> {Saved} = 1; 
 $ wb->关闭; 
  

I am trying to parse an Excel file in perl. After I extract the required info from it, I close the Excel file. At the end I am trying to save a new Excel file with a different name in the same directory. But this Excel is getting stored in 'My Documents' folder.

use Storable ;
use Cwd;

use Win32::OLE ;
use Win32::OLE qw(in with) ;
use Win32::OLE in ;
use Win32::OLE::Const 'Microsoft Excel';

use Excel::Writer::XLSX;

my $Excel = Win32::OLE->new("Excel.Application");
my $excel = $Excel->Workbooks->Add();

my $sheet = $excel->Worksheets(1);
$sheet->Activate();

my $new_file = "Temp_file.xlsm";
my $new_excel = cwd.'\\'.$new_file;
$new_excel =~ s/\//\\/g;

$excel->SaveAs($new_excel);

$Excel->{DisplayAlerts} = 0;
$excel->{Saved} = 1;
$excel->Close;

解决方案

Here is an update based on your code. First, you are letting Win32::OLE errors to be silently ignored. Instead, set: $Win32::OLE::Warn = 3 so it croaks whenever something goes wrong. Second, the way you try to obtain an Excel.Application instance is not correct. For one thing, if something goes wrong, you will have instances of Excel will remain floating around.

You are also confusing an Excel instance, a workbook, and the sheets it contains. If you did have $Win32::OLE::Warn = 3, you would have received a notification of where things are going wrong.

You should also always have use strict and use warnings in your script. Others will be more inclined to try to help if they know your problem is not caused by some trivial typo.

You also don't need three separate use Win32::OLE statements.

The code below "works". Compare it to yours.

Finally, if you are manipulating your sheet via Win32::OLE, there is no reason to have Excel::Writer::XLSX in your code.

use feature 'say';
use strict;
use warnings;

use File::Spec::Functions qw( rel2abs );
use Win32::OLE qw(in with) ;
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;

my $excel = eval {
        Win32::OLE->GetActiveObject('Excel.Application');
    } || Win32::OLE->new('Excel.Application', sub { $_[0]->Quit });

my $wb = $excel->Workbooks->Add;
my $sheet = $wb->Worksheets->Add;

$wb->SaveAs( rel2abs('temp_file.xlsm') );

$excel->{DisplayAlerts} = 0;
$wb->{Saved} = 1;
$wb->Close;

这篇关于如何将Excel文件保存在Win32 :: OLE中的工作目录中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！