利用Win32::OLE和EXCEL画图
返回
<![CDATA[
VBA to PERL
ActiveSheet.Shapes("グラフ 1").ScaleHeight 0.66, msoFalse, msoScaleFromTopLeft
-------------------------------------------------------
$t{sheet}->Shapes("グラフ 1")->ScaleHeight(0.66);
Win32::OLE(0.1704) error 0x8002000e: "パラメータの数が無効です。"
in METHOD/PROPERTYGET "ScaleHeight" at ../perl/yhou130.pl line 179
-------------------------------------------------------
$t{sheet}->Shapes("グラフ 1")->ScaleHeight(0.66,msoFalse,msoScaleFromTopLeft);
Win32::OLE(0.1704) error 0x80020005: "種類が一致しません。"
in METHOD/PROPERTYGET "ScaleHeight" argument 2 at ../perl/yhou130.pl line 17
9
-------------------------------------------------------
$t{sheet}->Shapes("グラフ 1")->ScaleHeight({Factor=>0.66,msoFalse=>0,msoScaleFromTopLeft=>0});
Win32::OLE(0.1704) error 0x800a03ec
"msoFalse", "msoScaleFromTopLeft" and "Factor" in GetIDsOfNames for
"ScaleHeight" at ../perl/yhou130.pl line 179
-------------------------------------------------------
$t{sheet}->Shapes("グラフ 1")->ScaleHeight = "0.66,msoFalse,msoScaleFromTopLeft";
Can't modify non-lvalue subroutine call at ../perl/yhou130.pl line 179.
-------------------------------------------------------
$t{sheet}->Shapes("グラフ 1")->ScaleHeight->(0.66,msoFalse,msoScaleFromTopLeft);
Win32::OLE(0.1704) error 0x8002000e: "パラメータの数が無効です。"
in METHOD/PROPERTYGET "ScaleHeight" at ../perl/yhou130.pl line 179
-------------------------------------------------------
==>最后成功!
-------------------------------------------------------
$t{sheet}->Shapes("グラフ 1")->ScaleHeight(0.66,0,0);
给chart取名
-------------------------------------
$t{chart}->{name} = 'sor2';
Win32::OLE(0.1704) error 0x800a01a8
in PROPERTYPUT "name" at ../perl/yhou130.pl line 152
]]>
<![CDATA[
# vba_perl_test.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);
$t{ChartFilePath} = 'C:\\temp\\Chart.gif'; # チャートの出力先
$t{PI} = 4*atan2(1,1);
$t{f0} = 50;
# EXCEL起動
$t{ex} = Win32::OLE->new('Excel.Application') or die "oops\n";
# 「xxへの変更を保存しますか？」が出ない
$t{ex}->{DisplayAlerts} = 'False';
$t{w0} = 2*$t{PI}*$t{f0};
$t{step} = 1/50/24;
# 计算COS曲线并写入CSV文件
open(OUT,">test.csv");
print OUT 'Time,v1',"\n";
for $n ( 1 .. 100 ) {
$t{T} = $n*$t{step};
$t{wt} = $t{w0}*$t{T};
$t{v1} = 100*cos($t{wt});
print OUT $t{T},',';
print OUT $t{v1},"\n";
}
close(OUT);
# 把CSV变换成XLS文件
eval {
$t{ex}->Workbooks->OpenText({Filename => "C:\\test\\test.csv"});
};
if ($@) {
die "Can't open the file test.csv.\n";
}
# 实施画图
# シートを選択
$t{sheet} = $t{ex}->ActiveSheet;
$t{sheet}->Columns("A:B")->Select;
$t{chart} = $t{sheet}->ChartObjects->Add(1, 175, 457, 300);
$t{chart}->Chart->{ChartType} = xlXYScatterLines;
$t{chart}->Chart->ChartWizard({Source => $t{sheet}->Range("A:B"),
PlotBy => xlColumns});
$t{chart}->Chart->{HasLegend} = 1; # 1なら凡例を表示する
$t{chart}->Chart->Location(xlLocationAsObject, "test");
$t{chart}->Chart->{HasTitle} = 'True';
$t{chart}->Chart->ChartTitle->Characters->{Text} = 'v1';
$t{chart}->Chart->Axes(xlCategory,xlPrimary)->{HasTitle} = 'False';
$t{chart}->Chart->Axes(xlValue,xlPrimary)->{HasTitle} = 'False';
# 注意要用Unicode写入
$t{chart}->Chart->Axes(xlCategory,xlPrimary)->{HasTitle} = 'True';
$t{chart}->Chart->Axes(xlCategory,xlPrimary)->AxisTitle->Characters->{Text} = Variant(VT_BSTR, 't(秒)');
$t{chart}->Chart->Axes(xlValue,xlPrimary)->{HasTitle} = 'True';
$t{chart}->Chart->Axes(xlValue,xlPrimary)->AxisTitle->Characters->{Text} = Variant(VT_BSTR, 'v(V)');
with ($t{chart}->Chart->Axes(xlValue),
'CrossesAt' => -150,
'HasMajorGridlines' => 'True',
'HasMinorGridlines' => 'False',
'ScaleType' => xlLinear);
# 形成GIF文件
$t{chart}->Chart->Export($t{ChartFilePath}, 'GIF');
# XLS保存
$t{ex}->ActiveWorkbook->SaveAs({Filename => "C:\\test\\test.xls",
Fileformat => xlWorkbookNormal});
$t{ex}->ActiveWorkbook->Close();
$t{ex}->Quit();
sleep(1);
system("del test.csv");
print "The output file is test.xls.\n";
该程序还有如下问题:
如何把第一列的数值作为x轴?(现在x轴是顺序号)
]]>
下面是编程时的错误信息,留个底稿。
<![CDATA[
Win32::OLE(0.1704) error 0x80020005: "種類が一致しません。"
in PROPERTYPUT "ChartType" at vba_perl_test.pl line 46
$t{chart} = $t{ex}->ChartObjects->Add(1, 175, 457, 300);
---------------------------------------------------------------
Win32::OLE(0.1704) error 0x80020011: "コレクションをサポートしません。"
in METHOD/PROPERTYGET "" at vba_perl_test.pl line 45
$t{chart}->Chart->{ChartType} = 'xlXYScatterLinesNoMarkers';
---------------------------------------------------------------
Win32::OLE(0.1704) error 0x80020005: "種類が一致しません。"
in PROPERTYPUT "ChartType" at vba_perl_test.pl line 45
$t{chart}->Chart->ChartWizard({Source => $t{sheet}->Range("A:B"),
PlotBy => '_xlColumns'});
---------------------------------------------------------------
OLE exception from "Microsoft Excel":
Chart クラスの ChartWizard プロパティを取得できません。
Win32::OLE(0.1704) error 0x800a03ec
in METHOD/PROPERTYGET "ChartWizard" at vba_perl_test.pl line 48
with ($t{chart}->Chart->Axes(xlCategory,xlPrimary)->AxisTitle->Characters,
'Text' => 't(秒)');
---------------------------------------------------------------
OLE exception from "Microsoft Excel":
AxisTitle クラスの Characters プロパティを取得できません。
Win32::OLE(0.1704) error 0x800a03ec
in METHOD/PROPERTYGET "Characters" at vba_perl_test.pl line 51
with ($t{chart}->Chart,
Axes(xlCategory,xlPrimary)->AxisTitle->Characters->Text => 't(秒)');
---------------------------------------------------------------
Undefined subroutine &main::Axes called at vba_perl_test.pl line 51.
$t{chart}->Chart->Axes(xlCategory,xlPrimary)->AxisTitle->Characters->{Text} = 't(秒)';
---------------------------------------------------------------
OLE exception from "Microsoft Excel":
AxisTitle クラスの Characters プロパティを取得できません。
Win32::OLE(0.1704) error 0x800a03ec
in METHOD/PROPERTYGET "Characters" at vba_perl_test.pl line 51
$t{chart}->Chart->Location({xlLocationAsObject,Name=>"text"});
---------------------------------------------------------------
Win32::OLE(0.1704) error 0x80020006: "名前が不明です。"
"text" and "2" in GetIDsOfNames for "Location" at vba_perl_test.pl line 52
$t{chart}->Chart->Location(xlLocationAsObject,text);
---------------------------------------------------------------
Bareword "text" not allowed while "strict subs" in use at vba_perl_test.pl line
52.
Execution of vba_perl_test.pl aborted due to compilation errors.
$t{chart}->Chart->Location(xlLocationAsObject,"text");
---------------------------------------------------------------
Win32::OLE(0.1704) error 0x800a03ec
in METHOD/PROPERTYGET "Location" at vba_perl_test.pl line 52
$t{chart}->Chart->Location(xlLocationAsObject);
---------------------------------------------------------------
Win32::OLE(0.1704) error 0x80070057: "パラメータが間違っています。"
in METHOD/PROPERTYGET "Location" at vba_perl_test.pl line 52
$t{chart} = $t{sheet}->ChartsObjects->Add;
---------------------------------------------------------------
Win32::OLE(0.1704) error 0x80020003: "メンバが見つかりません。"
in METHOD/PROPERTYGET "" at vba_perl_test.pl line 49
$t{chart} = $t{sheet}->ChartObjects->Add;
$t{chart} = $t{sheet}->ChartObjects->Add();
---------------------------------------------------------------------
Win32::OLE(0.1704) error 0x8002000f: "パラメータはオプションではありません。"
in METHOD/PROPERTYGET "Add" at vba_perl_test.pl line 50
$t{chart}->Chart->ChartWizard({PlotBy => xlColumns});
---------------------------------------------------------------------
Win32::OLE(0.1704) error 0x800a03ec
in METHOD/PROPERTYGET "ChartWizard" at vba_perl_test.pl line 54
$t{chart}->SetSourceData({Source => Range("A:B"), PlotBy => xlColumns});
-----------------------------------------------------
Undefined subroutine &main::Range called at vba_perl_test.pl line 56.
$t{sheet} = $t{ex}->Worksheets("text");
-----------------------------------------------------
Win32::OLE(0.1704) error 0x8002000b: "インデックスが無効です。"
in METHOD/PROPERTYGET "Worksheets" at vba_perl_test.pl line 54
$t{chart}->{HasTitle} = 'True';
-----------------------------------------------------
Win32::OLE(0.1704) error 0x800a01a8
in PROPERTYPUT "HasTitle" at vba_perl_test.pl line 74
$t{chart}->Chart->{HasTitle} = 'True';
-----------------------------------------------------
Win32::OLE(0.1704) error 0x800a01a8
in METHOD/PROPERTYGET "" at vba_perl_test.pl line 66
-----------------------------------------------------
最后终于成功地完成了预定目标
感谢下面这个网页提供的参考
http://www.hirax.net/misc/amet/PiChartExcel.pl
# vba_perl_test.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);
$t{ChartFilePath} = 'C:\\temp\\Chart.gif'; # チャートの出力先
$t{PI} = 4*atan2(1,1);
$t{f0} = 50;
# EXCEL起動
$t{ex} = Win32::OLE->new('Excel.Application') or die "oops\n";
# 「xxへの変更を保存しますか？」が出ない
$t{ex}->{DisplayAlerts} = 'False';
$t{w0} = 2*$t{PI}*$t{f0};
$t{step} = 1/50/24;
# 计算COS曲线并写入CSV文件
open(OUT,">test.csv");
print OUT 'Time,v1',"\n";
for $n ( 1 .. 100 ) {
$t{T} = $n*$t{step};
$t{wt} = $t{w0}*$t{T};
$t{v1} = 100*cos($t{wt});
print OUT $t{T},',';
print OUT $t{v1},"\n";
}
close(OUT);
# 把CSV变换成XLS文件
eval {
$t{ex}->Workbooks->OpenText({Filename => "C:\\test\\test.csv"});
};
if ($@) {
die "Can't open the file test.csv.\n";
}
# 实施画图
# シートを選択
$t{sheet} = $t{ex}->ActiveSheet;
$t{Range} = $t{sheet}->Range("A1:B101");
# 增加一张图
$t{chart} = $t{ex}->Charts->Add;
$t{chart}->{ChartType} = xlXYScatterLinesNoMarkers;
$t{chart}->SetSourceData({Source => $t{Range}, PlotBy => xlColumns});
$t{chart}->{HasLegend} = 1; # 1なら凡例を表示する
$t{chart}->Location(xlLocationAsObject, "test");
$t{chart} = $t{ex}->ActiveChart; # 非常重要!!!不能漏掉这句!
$t{chart}->{HasTitle} = 'True';
$t{chart}->ChartTitle->Characters->{Text} = 'v1';
$t{chart}->Axes(xlCategory,xlPrimary)->{HasTitle} = 'False';
$t{chart}->Axes(xlValue,xlPrimary)->{HasTitle} = 'False';
$t{chart}->Axes(xlCategory,xlPrimary)->{HasTitle} = 'True';
$t{chart}->Axes(xlCategory,xlPrimary)->AxisTitle->Characters->{Text} = Variant(VT_BSTR, 't(秒)');
$t{chart}->Axes(xlValue,xlPrimary)->{HasTitle} = 'True';
$t{chart}->Axes(xlValue,xlPrimary)->AxisTitle->Characters->{Text} = Variant(VT_BSTR, 'v(V)');
with ($t{chart}->Axes(xlValue),
'CrossesAt' => -150,
'HasMajorGridlines' => 'True',
'HasMinorGridlines' => 'False',
'ScaleType' => xlLinear);
# 形成GIF文件
$t{chart}->Export($t{ChartFilePath}, 'GIF');
# XLS保存
$t{ex}->ActiveWorkbook->SaveAs({Filename => "C:\\test\\test.xls",
Fileformat => xlWorkbookNormal});
$t{ex}->ActiveWorkbook->Close();
$t{ex}->Quit();
sleep(1);
system("del test.csv");
print "The output file is test.xls.\n";
]]>
最新的程序参见这儿==>CSV格式转换成XLS格式并画图(CSV2EXCEL)
返回