CSV格式转换成XLS格式并画图(CSV2EXCEL)
返回
- 未解决问题:如何任意选择一张图贴在Sheet的任意位置
- 如何改变X轴和Y轴交叉点的位置
<![CDATA[
$t{sheet1} = $t{book1}->Worksheets(4);
Win32::OLE(0.1704) error 0x8002000b: "インデックスが無効です。"
in METHOD/PROPERTYGET "Worksheets" at get_excel.pl line 194
$t{sheet1} = $t{book1}->Worksheets->Add(4);
Win32::OLE(0.1704) error 0x800a03ec
in METHOD/PROPERTYGET "Add" at get_excel.pl line 194
$t{sheet1} = $t{book1}->Worksheets->Add();
==>OK
--------------------------------------------------------
1号问题解决,把"グラフ 1"配置到$t{left},${top}坐标上。
with ($t{sheet}->Shapes->{"グラフ 1"},
'Left' => $t{left},
'Top' => $t{top});
犯错信息==>
$t{chart}->Shapes("グラフ 4")->{IncrementLeft} = 429.75;
-----------------------------------
Win32::OLE(0.1704) error 0x80070057: "パラメータが間違っています。"
in METHOD/PROPERTYGET "Shapes" at test.pl line 112
$t{sheet0}->Shapes->AddPicture("グラフ 4",0,1,150,200,150,113);
-----------------------------------
Win32::OLE(0.1704) error 0x800a03ec
in METHOD/PROPERTYGET "AddPicture" at test.pl line 111
$t{sheet0}->Shapes->{"グラフ 2"}->{IncrementLeft} = 0;
-----------------------------------
Win32::OLE(0.1704) error 0x80020009: "例外が発生しました。"
in METHOD/PROPERTYGET "グラフ 2" at test.pl line 111
$t{sheet0}->Shapes->{"グラフ 1"}->{IncrementLeft} = 0;
-----------------------------------
Win32::OLE(0.1704) error 0x80020003: "メンバが見つかりません。"
in PROPERTYPUT "IncrementLeft" at test.pl line 111
]]>
<![CDATA[
#一些设定
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Const 'Microsoft Office .* Object Library';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG);
# set perl's OLE module to return Unicode(这个设定不能忘)
Win32::OLE->Option(CP => Win32::OLE::CP_UTF8, Warn=>3);
# 起动EXCEL
$t{ex} = Win32::OLE->new('Excel.Application') or die "oops\n";
# 显示模式(0为非显示模式)
$t{ex}->{Visible} = 1;
# 不跳出是否允许的窗口
$t{ex}->{DisplayAlerts} = 'False';
# 打开CSV文件,打开EXCEL文件时用(Open)
# DIR的设定例子(从其他TXT文件中读)=>C:\csv2excel\output\
# $$ref{outputf}=>CSV文件名
eval {
$t{ex}->Workbooks->OpenText({Filename => "$t{dir}$$ref{outputf}"});
};
if ($@) {
die "Can't open the file $t{dir}$$ref{outputf}.\n";
}
# 把CSV文件改成XLS文件并保存
$t{ex}->ActiveWorkbook->SaveAs({Filename => "$t{dir}$$ref{outputfexcel}",
Fileformat => xlWorkbookNormal});
# 选择book
$$ref{book1} = $t{ex}->Workbooks->Open({Filename => "$t{dir}$$ref{outputfexcel}"});
# 选择Sheet=>注意Sheet名
$t{sheet1} = $$ref{book1}->Worksheets("$$ref{output}");
# 选择画图的范围
$t{Range} = $t{sheet1}->Range("B1:C10,G1:G10");
# 追加Graph
$t{chart} = $$ref{book1}->Charts->Add;
$t{chart}->{ChartType} = xlXYScatterLinesNoMarkers;
$t{chart}->SetSourceData({Source => $t{Range}, PlotBy => xlColumns});
# 这张图布置在该Sheet的中间
$t{chart}->Location({where=>xlLocationAsObject,Name=>"$$ref{output}"});
# 激活该图
$t{chart} = $$y_ref{book1}->ActiveChart;
# 有显示例
$t{chart}->{HasLegend} = 1;
# 显示例放在图的下方
with ($t{chart}->{Legend},
'Position' => xlBottom);
$t{chart}->Axes(xlCategory,xlPrimary)->{HasTitle} = 'True';
$t{chart}->Axes(xlCategory,xlPrimary)->AxisTitle->Characters->{Text} = Variant(VT_BSTR, '時刻');
$t{chart}->Axes(xlValue,xlPrimary)->{HasTitle} = 'True';
$t{chart}->Axes(xlValue,xlPrimary)->AxisTitle->Characters->{Text} = Variant(VT_BSTR, 'TAP位置');
# X轴的一些设定
with ($t{chart}->Axes(xlCategory),
'MinimumScale' => '0',
'MaximumScale' => '100',
'MajorUnit' => '10',
'MinorUnit' => '5',
'Crosses' => xlAutomatic,
'HasMajorGridlines' => 'True',
'HasMinorGridlines' => 'True',
'ReversePlotOrder' => 'False',
'ScaleType' => xlLinear,
'DisplayUnit' => xlNone);
# 对一条线作设定
with ($t{chart}->SeriesCollection(1)->{Border},
'ColorIndex' => '1',
'Weight' => xlMedium,
'LineStyle' => xlContinuous);
# 对一条线作设定
with ($t{chart}->SeriesCollection(2)->{Border},
'ColorIndex' => '3',
'Weight' => xlMedium,
'LineStyle' => xlContinuous);
# 画一批图
$t{NO} = 0;
$t{NO2} = 0;
for $n ( 'L' .. 'P' ) {
# 跳过一行的处理
$t{NO}++;
$t{N1} = $t{NO} % 2;
next unless ( $t{N1} == 1 );
# 给图编号
$t{NO2}++;
$$ref{PLOT1} = $n;
$$ref{graph} = '电流' . $t{NO2};
# 用子程序画图
($ref) = plot_current($ref);
}
# 保存EXCEL文件
$t{ex}->ActiveWorkbook->SaveAs({Filename => "$t{dir}$$ref{outputfexcel}",
Fileformat => xlWorkbookNormal});
# 关闭EXCEL
$t{ex}->ActiveWorkbook->Close();
$t{ex}->Quit();
sub plot_current {
my ($ref) = @_;
my(%t);
$t{plot} = 'B1:B10,' . $$ref{PLOT1} . '1:' . $$ref{PLOT1} . '10';
$t{sheet1} = $$ref{book1}->Worksheets("$$ref{output}");
$t{Range} = $t{sheet1}->Range("$t{plot}");
$t{chart} = $$ref{book1}->Charts->Add;
$t{chart}->{ChartType} = xlXYScatterLinesNoMarkers;
$t{chart}->SetSourceData({Source => $t{Range}, PlotBy => xlColumns});
# 一张图一张Sheet的设定方法
$t{chart}->Location({where=>xlLocationAsNewSheet,Name=>"$$ref{graph}"});
$t{chart} = $$ref{book1}->ActiveChart;
$t{chart}->{HasLegend} = 0;
$t{chart}->Axes(xlCategory,xlPrimary)->{HasTitle} = 'True';
$t{chart}->Axes(xlCategory,xlPrimary)->AxisTitle->Characters->{Text} = Variant(VT_BSTR, '时间');
$t{chart}->Axes(xlValue,xlPrimary)->{HasTitle} = 'True';
$t{chart}->Axes(xlValue,xlPrimary)->AxisTitle->Characters->{Text} = Variant(VT_BSTR, '电流[A]');
with ($t{chart}->Axes(xlCategory),
'MinimumScale' => '0',
'MaximumScale' => '100',
'MajorUnit' => '10',
'MinorUnit' => '5',
'Crosses' => xlAutomatic,
'HasMajorGridlines' => 'True',
'HasMinorGridlines' => 'True',
'ReversePlotOrder' => 'False',
'ScaleType' => xlLinear,
'DisplayUnit' => xlNone);
with ($t{chart}->SeriesCollection(1)->{Border},
'ColorIndex' => '1',
'Weight' => xlMedium,
'LineStyle' => xlContinuous);
return($ref);
}
]]>
ColorIndex的定义请看==>ColorIndex值一览
<![CDATA[
# 指定任意位置的文字为图的题目(Title)
# R->Row,C=>Column,Sheet的第3行,第$$ref{PC}(变数)列的文字
$t{chart}->{HasTitle} = 'True';
$t{chart}->ChartTitle->{Text} = "=csv!R3C$$ref{PC}";
# 增加一条曲线
$t{chart}->SeriesCollection->NewSeries();
$t{chart}->SeriesCollection(2)->{XValues} = "=$$ref{output}!R1C2:R2C2";
$t{chart}->SeriesCollection(2)->{Values} = "=$$ref{output}!R1C3:R2C3";
]]>
<![CDATA[
一些错误信息
with ($t{chart}->Axes(xlValue),
'Crosses' => xlCustom,
'CrossesAt' => 'MinimumScale');
--------------------------------------------------------------
Win32::OLE(0.1704) error 0x80020009: "例外が発生しました。"
in PROPERTYPUT "CrossesAt" at C:/Perl/site/lib/Win32/OLE/Lite.pm line 201
$t{chart}->SeriesCollection(2)->{XValues} = "=$$y_ref{output}\!\$CC\$1:\$CC\$2";
--------------------------------------------------------------
Win32::OLE(0.1704) error 0x80020009: "例外が発生しました。"
in PROPERTYPUT "XValues" at VRdata1.pl line 336
グラフ 1のfontはUTF-16LE(BOM付き)である！
Can't locate object method "Worksheets" via package "ActiveWorkbook" (perhaps yo
u forgot to load "ActiveWorkbook"?) at test.pl line 63.
$t{sheet1} = $t{book1}->Worksheets($$y_ref{outputf});
--------------------------------------------------------
Win32::OLE(0.1704) error 0x8002000b: "インデックスが無効です。"
in METHOD/PROPERTYGET "Worksheets" at test.pl line 66
$t{book1} = $t{ex}->ActiveWorkbook->Select;
--------------------------------------------------------
Win32::OLE(0.1704) error 0x80020003: "メンバが見つかりません。"
in METHOD/PROPERTYGET "" at test.pl line 64
$t{book1} = $t{ex}->Select;
--------------------------------------------------------
Win32::OLE(0.1704) error 0x80020011: "コレクションをサポートしません。"
in METHOD/PROPERTYGET "" at test.pl line 64
$t{sheet1} = $t{book1}->Worksheets->Add(1);
--------------------------------------------------------
Win32::OLE(0.1704) error 0x800a03ec
in METHOD/PROPERTYGET "Add" at test.pl line 80
$t{chart} = ActiveSheet->ChartObjects("グラフ 1")->Activate;
--------------------------------------------------------
Can't locate object method "ChartObjects" via package "ActiveSheet" (perhaps you
forgot to load "ActiveSheet"?) at test.pl line 80.
$t{chart}->Paste;
--------------------------------------------------------
Can't call method "Paste" on an undefined value at test.pl line 85.
$t{ex} = ActiveWindow->{Visible} = 'False';
--------------------------------------------------------
Can't use bareword ("ActiveWindow") as a HASH ref while "strict refs" in use at
test.pl line 84.
$t{ex}->{ActiveChart}->Paste;
--------------------------------------------------------
Can't use string ("1") as a HASH ref while "strict refs" in use at test.pl lin
e 89.
$t{chart} = $t{sheet0}->ChartObjects("グラフ 1")->Activate;
--------------------------------------------------------
Win32::OLE(0.1704) error 0x800a03ec
in METHOD/PROPERTYGET "ChartObjects" at test.pl line 83
$t{chart}->SeriesCollection->NewSeries = 1;
--------------------------------------------------------
Can't modify non-lvalue subroutine call at test.pl line 172.
Win32::OLE(0.1704) error 0x80020009: "例外が発生しました。"
in PROPERTYPUT "ColorIndex" at C:/Perl/site/lib/Win32/OLE/Lite.pm line 201
]]>
<![CDATA[
# make_xls.pl
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use utf8;
# set perl's OLE module to return Unicode
Win32::OLE->Option(CP => Win32::OLE::CP_UTF8, Warn=>3);
my(%t,$n,$h_ref,@fld);
print "Please input the directory name=";
chop($t{root}=<STDIN>);
$$h_ref{dir} = 'vF' . $t{root};
$t{inputf} = $t{root} . '_vF.csv';
open(IN,"./$$h_ref{dir}/$t{inputf}") or die "Can't open the file /$$h_ref{dir}/$t{inputf}.\n";
while(<IN>){
next if ( $. == 1 );
chop;
@fld = split(/,/);
next unless $fld[1];
$t{T1} = sprintf("%10.6f",$fld[0]);
push(@{ $$h_ref{Time} },$t{T1});
push(@{ $$h_ref{k_files} },$fld[1]);
push(@{ $$h_ref{Start} },$fld[2]);
}
close(IN);
for $n ( 0 .. $#{ $$h_ref{Time} } ) {
$t{file1} = $$h_ref{k_files}[$n];
$$h_ref{csv1} = $t{file1};
$t{file1} =~ s/csv/xls/;
$$h_ref{xls1} = $t{file1};
$t{file1} =~ s/xls/gif/;
$$h_ref{gif1} = $t{file1};
($h_ref) = csv2excel($h_ref);
# last if ($n > 5);
}
sub csv2excel {
my ($h_ref) = @_;
my(%t,$n);
# EXCEL起動
$t{ex} = Win32::OLE->new('Excel.Application') or die "oops\n";
# 「xxへの変更を保存しますか？」が出ない
$t{ex}->{DisplayAlerts} = 'False';
eval {
$t{ex}->Workbooks->OpenText({Filename => "C:\\output\\$$h_ref{dir}\\$$h_ref{csv1}"});
};
if ($@) {
die "Can't open the file $$h_ref{csv1}.\n";
}
# シートを選択
$t{sheet} = $t{ex}->ActiveSheet;
$t{Range} = $t{sheet}->Range("A1:C102");
# Graphを追加
$t{chart} = $t{ex}->Charts->Add;
$t{chart}->{ChartType} = xlXYScatterLinesNoMarkers;
$t{chart}->SetSourceData({Source => $t{Range}, PlotBy => xlColumns});
$t{chart}->{HasLegend} = 1;
$t{chart}->Location(xlLocationAsNewSheet, "vF");
$t{chart}->Axes(xlCategory,xlPrimary)->{HasTitle} = 'True';
$t{chart}->Axes(xlCategory,xlPrimary)->AxisTitle->Characters->{Text} = Variant(VT_BSTR, 'k(%)');
$t{chart}->Axes(xlValue,xlPrimary)->{HasTitle} = 'True';
$t{chart}->Axes(xlValue,xlPrimary)->AxisTitle->Characters->{Text} = Variant(VT_BSTR, 'v(PU)');
with ($t{chart}->Axes(xlValue),
'HasMajorGridlines' => 'True',
'HasMinorGridlines' => 'False');
with ($t{chart}->Axes(xlCategory),
'HasMajorGridlines' => 'True',
'MaximumScale' => '100',
'MajorUnit' => '10',
'Crosses' => xlAutomatic,
'ReversePlotOrder' => 'False',
'ScaleType' => xlLinear,
'DisplayUnit' => xlNone);
with ($t{chart}->Axes(xlValue)->{MajorGridlines}->{Border},
'ColorIndex' => '57',
'Weight' => xlHairline,
'LineStyle' => xlDot);
with ($t{chart}->Axes(xlCategory)->{MajorGridlines}->{Border},
'ColorIndex' => '57',
'Weight' => xlHairline,
'LineStyle' => xlDot);
with ($t{chart}->SeriesCollection(1)->{Border},
'ColorIndex' => '3',
'Weight' => xlMedium,
'LineStyle' => xlContinuous);
with ($t{chart}->SeriesCollection(2)->{Border},
'ColorIndex' => '1',
'Weight' => xlMedium,
'LineStyle' => xlContinuous);
$t{chart}->Export("C:\\output\\$$h_ref{dir}\\$$h_ref{gif1}", 'GIF');
# XLS保存
$t{ex}->ActiveWorkbook->SaveAs({Filename => "C:\\output\\$$h_ref{dir}\\$$h_ref{xls1}",
Fileformat => xlWorkbookNormal});
$t{ex}->ActiveWorkbook->Close();
$t{ex}->Quit();
sleep(1);
return($h_ref);
}
__END__;
错误信息:
Win32::OLE(0.1704) error 0x80020003: "メンバが見つかりません。"
in PROPERTYPUT "MinimumScalelsAuto" at C:/Perl/site/lib/Win32/OLE/Lite.pm li
ne 201
Win32::OLE(0.1704) error 0x80020003: "メンバが見つかりません。"
in PROPERTYPUT "MinorUnitlsAuto" at C:/Perl/site/lib/Win32/OLE/Lite.pm line
201
Undefined subroutine &main::SeriesCollection called at make_xls.pl line 70.
with ($t{chart}->{SeriesCollection}->{1}->{Border},
OLE exception from "Microsoft Excel":
<No description provided>
Win32::OLE(0.1704) error 0x80020009: "例外が発生しました。"
in METHOD/PROPERTYGET "1" at make_xls.pl line 70
with ($t{chart}->{SeriesCollection}->{Border},
<No description provided>
Win32::OLE(0.1704) error 0x80020009: "例外が発生しました。"
in METHOD/PROPERTYGET "Border" at C:/Perl/site/lib/Win32/OLE/Lite.pm line 19
8
正确的写法是
==>with ($t{chart}->SeriesCollection(1)->{Border},
-----------------------------------------------------------
$t{chart}->{HasLegend} = 0;
------------------------------------
Win32::OLE(0.1704) error 0x800a01a8
in PROPERTYPUT "HasLegend" at xx.pl line 39
$t{sheet} = $t{books}->Worksheets("node")->Add;
------------------------------------
Win32::OLE(0.1704) error 0x80020003: "メンバが見つかりません。"
in METHOD/PROPERTYGET "" at xx.pl line 30
Win32::OLE(0.1704) error 0x800a03ec
in METHOD/PROPERTYGET "Location" at yhou130_in.pl line 103
my $sheet = $book->Worksheets("Dashboard")
or die "N'a pas pu récupérer la feuille Dashboard\n";
]]>
CSV文件画图
<![CDATA[
# EXCEL启动
$t{ex} = Win32::OLE->new('Excel.Application') or die "oops\n";
# 禁止跳出
$t{ex}->{DisplayAlerts} = 'False';
# 打开一个CSV文件
eval {
$t{ex}->Workbooks->OpenText({Filename => "$$ref{dir}$$ref{file}"});
};
if ($@) {
die "Can't open the file $$ref{dir}$$ref{file}.\n";
}
# 选择Sheet
$t{ex}->ActiveSheet->Cells->Select;
# 选择复写
$t{ex}->Selection->Copy;
# 新Book
$t{book1} = $t{ex}->Workbooks->Add;
# 新Sheet
$t{sheet1} = $t{book1}->Worksheets(1);
# Paste
$t{sheet1}->Paste;
# 改名
$t{sheet1}->{name} = 'graph';
$t{sheet} = $t{book1}->Worksheets("graph");
$t{Range} = $t{sheet}->Range("A:A,B:B");
# 追加Graph
$t{chart} = $t{book1}->Charts->Add;
$t{chart}->{ChartType} = xlXYScatterLinesNoMarkers;
$t{chart}->SetSourceData({Source => $t{Range}, PlotBy => xlColumns});
$t{chart}->Location({where=>xlLocationAsObject,Name=>'graph'});
$t{chart} = $t{book1}->ActiveChart;
$t{chart}->{HasLegend} = 0;
$t{chart}->Axes(xlCategory,xlPrimary)->{HasTitle} = 'True';
$t{chart}->Axes(xlCategory,xlPrimary)->AxisTitle->Characters->{Text} = Variant(VT_BSTR, '时间(秒)');
$t{chart}->Axes(xlValue,xlPrimary)->{HasTitle} = 'True';
$t{chart}->Axes(xlValue,xlPrimary)->AxisTitle->Characters->{Text} = Variant(VT_BSTR, '电压(V)');
with ($t{chart}->Axes(xlCategory),
'MinimumScale' => '0',
'MaximumScale' => '300',
'MajorUnit' => '10',
'Crosses' => xlAutomatic,
'ReversePlotOrder' => 'False',
'ScaleType' => xlLinear,
'DisplayUnit' => xlNone);
]]>
<![CDATA[
CSV格式转换成XLS格式并画图
删除文件的一行
open IN, '<', $filename or die;
my @contents = <IN>;
close IN;
splice(@contents, 0, 1);
open OUT, '>', $filename or die;
print OUT @contents;
close OUT;
------------------------------------------------
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Const 'Microsoft Office .* Object Library';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG);
use utf8;
use File::Copy;
# set perl's OLE module to return Unicode
Win32::OLE->Option(CP => Win32::OLE::CP_UTF8, Warn=>3);
use Encode;
my(%t,@fld,$n);
print "This is get_excel.pl\n";
# 读set.txt
open(IN,"set.txt") or die "Can't open the file set.txt\n";
while(<IN>){
last if (/^END/);
@fld = split;
if (/^INPUTDIR0/){
$t{inputdir0} = $fld[1];
} elsif ( /^INPUTDIR1/ ) {
$t{inputdir1} = $fld[1];
} elsif ( /^INPUTFILE/ ) {
$t{inputf} = $fld[1];
} elsif (/^OUTPUTDIR/){
$t{outputdir} = $fld[1];
} elsif (/^CALDIR/){
$t{caldir} = $fld[1];
}
}
close(IN);
$t{inputdir} = $t{inputdir0} . $t{inputdir1} . '/';
$t{caldir} = $t{caldir} . $t{inputdir1} . '/';
# 读复数文件指定
open(IN,"$t{inputdir}$t{inputf}") or die "Can't open the file $t{inputdir}$t{inputf}\n";
$t{NO} = 0;
while(<IN>){
if ( /^file1/ ) {
chop;
@fld = split(/=/);
push(@{ $t{list} },$fld[1]);
$t{NO}++;
$t{N2} = sprintf("%02s",$t{NO});
$t{job1} = 'work00' . $t{N2};
push(@{ $t{jobs} },$t{job1});
$t{output1} = $t{inputdir1} . '_' . $fld[1] . '.xls';
push(@{ $t{outputs} },$t{output1});
$t{output2} = $t{inputdir1} . '_' . $fld[1] . '.txt';
push(@{ $t{outputs2} },$t{output2});
}
}
close(IN);
# CSV文件项目数
$t{GAG0} = 'ga1.csv';
$t{GPG0} = 'gp1.csv';
$t{GQG0} = 'gq1.csv';
$t{GAG} = 'gag.csv';
$t{GPG} = 'gpg.csv';
$t{GQG} = 'gqg.csv';
# 复写文件
for $n ( 0 .. $#{ $t{jobs} } ) {
$t{job1} = $t{jobs}[$n];
$t{out1} = $t{outputs}[$n];
$t{out2} = $t{outputs2}[$n];
# 复写文件
copy("$t{caldir}$t{job1}/sys1/out1.lst","$t{outputdir}$t{out2}") or die "Copy failed:$!";
print "caldir=$t{caldir}$t{job1}\n";
# EXCEL的起動
$t{ex} = Win32::OLE->new('Excel.Application') or die "oops\n";
# 「xxへの変更的保存しますか？」
$t{ex}->{DisplayAlerts} = 'False';
# AG
open(IN,"$t{caldir}$t{job1}/result/$t{GAG0}") or die "Can't open $t{caldir}$t{job1}/result/$t{GAG0}\n";
my @contents = <IN>;
close IN;
splice(@contents, 0, 1);
open(OUT, ">$t{caldir}$t{job1}/result/$t{GAG}") or die;
print OUT @contents;
close OUT;
eval {
$t{ex}->Workbooks->OpenText({Filename => "$t{caldir}$t{job1}/result/$t{GAG}"});
};
if ($@) {
die "Can't open the file $t{caldir}$t{job1}/result/$t{GAG}.\n";
}
# sheet的选择
$t{ex}->ActiveSheet->Cells->Select;
# sheet的复写
$t{ex}->Selection->Copy;
# 新Book的作成
$t{book1} = $t{ex}->Workbooks->Add;
# 新しいSheet的追加
$t{sheet1} = $t{book1}->Worksheets(1);
# Paste
$t{sheet1}->Paste;
# sheet名的変更
$t{sheet1}->{name} = 'AG';
# PG
open IN, '<', "$t{caldir}$t{job1}/result/$t{GPG0}" or die;
my @contents = <IN>;
close IN;
splice(@contents, 0, 1);
open OUT, '>', "$t{caldir}$t{job1}/result/$t{GPG}" or die;
print OUT @contents;
close OUT;
eval {
$t{ex}->Workbooks->OpenText({Filename => "$t{caldir}$t{job1}/result/$t{GPG}"});
};
if ($@) {
die "Can't open the file $t{caldir}$t{job1}/result/$t{GPG}.\n";
}
# sheet的选择
$t{ex}->ActiveSheet->Cells->Select;
# sheet的复写
$t{ex}->Selection->Copy;
# 新しいSheet的追加
$t{sheet1} = $t{book1}->Worksheets(2);
# Paste
$t{sheet1}->Paste;
# sheet名的変更
$t{sheet1}->{name} = 'PG';
# QG
open IN, '<', "$t{caldir}$t{job1}/result/$t{GQG0}" or die;
my @contents = <IN>;
close IN;
splice(@contents, 0, 1);
open OUT, '>', "$t{caldir}$t{job1}/result/$t{GQG}" or die;
print OUT @contents;
close OUT;
eval {
$t{ex}->Workbooks->OpenText({Filename => "$t{caldir}$t{job1}/result/$t{GQG}"});
};
if ($@) {
die "Can't open the file $t{caldir}$t{job1}/result/$t{GQG}.\n";
}
# sheet的选择
$t{ex}->ActiveSheet->Cells->Select;
# sheet的复写
$t{ex}->Selection->Copy;
# 新しいSheet的追加
$t{sheet1} = $t{book1}->Worksheets(3);
# Paste
$t{sheet1}->Paste;
# sheet名的変更
$t{sheet1}->{name} = 'QG';
$t{book1}->SaveAs({Filename => "$t{outputdir}$t{out1}",
Fileformat => xlWorkbookNormal});
$t{book1}->Close();
$t{ex}->Quit();
print "The output files of $t{job1} are $t{outputdir}$t{out1} and $t{out2}.\n";
sleep(3);
}
__END__;
]]>
返回