MySQL操作程序二十一(mscwrite_excel.pl)
返回
<![CDATA[
COMMERCIAL INVOICE的DISC没有计算!
SHIPPING INV
$t{excel_pat} eq 'shipping'
if ($t{excel_pat} eq 'invoice') {
的$t{disc}部分复写过来!
增加如下段落:(注意,不能对应复数shipping的情况!)
if ( $t{disc} ) {
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = 'Disc';
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{XY1} = "G" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
$$moneyref{one} = $t{disc};
$t{total1} = $t{total1} + $t{disc};
($moneyref)=money_show($moneyref);
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $$moneyref{one};
$t{Y1}++;
$t{Y1}++;
}
]]>
<![CDATA[
INVOICE输出时,增加消费税
if ( $t{tax1} != 1 ) {
$t{total} = $t{total} . '(+TAX)';
}
if ( $t{tax1} != 1 ) {
$t{addtax1} = $self->dbh->selectrow_array("SELECT tax1 FROM tax_ok WHERE id = $t{tax1}");
$t{total1} = $t{total1}*(1+$t{addtax1}/100);
}
]]>
<![CDATA[
QTN文件输出时,
TOTAL EUR 153.35
变成
TOTAL EUR 153
发现"セルの書式設定"的种类时"通货",要改为"文字列"
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@"; # 设为文字列
$t{XY1} = "G" . $t{Y1}; # 放在右边
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
]]>
<![CDATA[
几个不同的DISC输出时,EXCEL按disc0处的数字显示,但是总价需要按照各个不同的disc来计算!
Bug!删除这些if文。
程序似乎没有问题,就是在EXCEL显示时,按照dis0处的数据显示!
]]>
<![CDATA[
增加ti_ok輸出
($t{memo},$t{pi_ok}) = $self->dbh->selectrow_array("select memo,pi_ok from quo2 where id = $t{enq1_id} and memo is not NULL");
if ( $t{pi_ok} == 1 ) {
$t{Y1} = $t{Y1} + 1;
$t{sheet}->Cells($t{Y1},"C")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"C")->{Font}->{Bold} = "False";
# $t{sheet}->Cells($t{Y1},"E")->{WrapText} = "False";
$t{XY1} = 'C' . $t{Y1};
$t{XY2} = 'G' . $t{Y1};
$t{sheet}->Range("$t{XY1}:$t{XY2}")->{MergeCells} = "True";
$t{sheet}->Cells($t{Y1},"C")->{'Value'} = 'THE PRICE IS NOT INCLUDING PACKING & HANDING CHARGE.';
}
====>
($t{memo},$t{pi_ok},$t{ti_ok}) = $self->dbh->selectrow_array("select memo,pi_ok,ti_ok from quo2 where id = $t{enq1_id}");
} elsif ( $t{ti_ok} == 1 ) {
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
# $t{sheet}->Cells($t{Y1},"E")->{WrapText} = "False";
$t{XY1} = 'E' . $t{Y1};
$t{XY2} = 'G' . $t{Y1};
$t{sheet}->Range("$t{XY1}:$t{XY2}")->{MergeCells} = "True";
$t{word1} = '已提供業界最低價,無法再降.';
$t{word1} = decode("utf8",$t{word1});
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = Variant(VT_BSTR, $t{word1});
}
]]>
<![CDATA[
Argument "C" isn't numeric in numeric ne (!=) at ./pro/mscwrite_excel.pl line 434
434: if ( $t{seri2} != 1 ) {
要把所有series是'C'的enq1全部置换成'1'
mysql> update enq1 set seriesid = '1' where seriesid ='C';
Query OK, 9625 rows affected (0.34 sec)
Rows matched: 9625 Changed: 9625 Warnings: 0
mysql> select id,seriesid from enq1 where seriesid ='C';
Empty set (0.03 sec)
mysql> select id,seriesid from enq1 where id = 12395;
+-------+----------+
| id | seriesid |
+-------+----------+
| 12395 | 1 |
+-------+----------+
1 row in set (0.00 sec)
]]>
excel输出时的seriesid的正确输出
- "if ( $t{seri2} ne 'C' ) {"的修改
==>"if ( $t{seri2} != 1 ) {"
- enq1项目生成时的修改(把输入'C'改为输入'1')
==>mysql_test45.htm(mscenq1_start.pl)
- 2个以上主机输入情况的确认。
<![CDATA[
# $t{sheet}->Cells($t{Y1},"C")->{'Value'} = 'THE PRICE IS NOT INCLUDING PACKING & HANDLING CHARGE.';
$t{sheet}->Cells($t{Y1},"C")->{'Value'} = 'THE PRICE IS NOT INCLUDING PACKING & BANK CHARGE.';
点击选择后seriesid变成了1
-----------------------------------------------------------
mysql> select * from enq1 where id = 12399\G
*************************** 1. row ***************************
id: 12399
time: 2012-01-19
ourref: C011901
owner: 171
ownerno: NULL
hullnoid: 1
type1id: 11
partsid: 179
QTY: 2
memo: NULL
LANGUAGEid: 1
makerid: 1
enq2s: 13949
seriesid: 1
tmp_pname:
tmp_pcode:
tmp_pqty:
1 row in set (0.00 sec)
一个为什么会是4?
-----------------------------------------------------------
mysql> select * from enq1 where id = 12399\G
*************************** 1. row ***************************
id: 12399
time: 2012-01-19
ourref: C011901
owner: 171
ownerno: NULL
hullnoid: 1
type1id: 11
partsid: 179
QTY: 2
memo: NULL
LANGUAGEid: 1
makerid: 1
enq2s: 13949
seriesid: 4
tmp_pname:
tmp_pcode:
tmp_pqty:
1 row in set (0.03 sec)
]]>
<![CDATA[
[client 127.0.0.1] Argument "1856,400" isn't numeric in multiplication (*) at ./pro/mscwrite_excel.pl line 666,
665: $$moneyref{one} =~ s/\,//;
666: $t{pris} = $t{Q1}*$$moneyref{one};
修改如下:
665: $$moneyref{one} =~ s/\,//g; # 增加g
666: $t{pris} = $t{Q1}*$$moneyref{one};
]]>
- 增加"THE PRICE IS NOT INCLUDING PACKING & HANDING CHARGE."按钮选择(EXCEL自动写入)
- TOTAL 增加RMB(人民币)==>已有
- 点击EXCEL生成时写入invoicetime
mscorder.pl,pat==>invoiceexcel,$a_ref = obtain_time($a_ref);
- ship price的总数(total不对)?
出货发票上的价格还是有误
原因:SHIPPING INV複数ORDER検索追加部分の計算?=>问题解决!
- ship price的乘的问题
- ORDER_CONFIRMATION,TOTAL的USD显示
有"Packing Charge"时,TOTAL USD的显示没有问题。
サンプルファイルセルの書式は小数点后面为零!(和下面一样)
- ORDER,EXPENSE,1000=>100000%?=>サンプルファイルセルの書式はパーセンテージとなっている。
- 9102723,ID=6053,SERIES的印刷问题
<![CDATA[
[error] [client 127.0.0.1] Use of uninitialized value in multiplication (*) at ./pro/mscwrite_excel.pl line 637,
[error] [client 127.0.0.1] Use of uninitialized value in numeric eq (==) at ./pro/mscwrite_excel.pl line 1289,
637: $t{pris} = $t{Q1}*$t{pri1};
1264: ($t{memo},$t{pi_ok}) = $self->dbh->selectrow_array("select memo,pi_ok from quo2 where id = $t{enq1_id} and memo is not NULL");
1289: if ( $t{pi_ok} == 1 ) {
==>修改如下
($t{memo},$t{pi_ok}) = $self->dbh->selectrow_array("select memo,pi_ok from quo2 where id = $t{enq1_id}");
]]>
<![CDATA[
[error] [client 127.0.0.1] Use of uninitialized value in addition (+) at ./pro/mscwrite_excel.pl line 695,
[error] [client 127.0.0.1] Use of uninitialized value in concatenation (.) or string at ./pro/mscwrite_excel.pl line 777,
695: $t{total1} = $t{total1} + $t{disc} + $t{packing_charge} + $t{freight_charges} + $t{delivery_charge};
777: $t{delivery_place} = 'TO ' . $t{delivery_place};
==>修改如下
$t{total1} = $t{total1} + $t{pcharges}; # 总和
if ( $t{delivery_place} ) {
$t{delivery_place} = 'TO ' . $t{delivery_place};
} else {
$t{delivery_place} = 'TO ';
}
[error] [client 127.0.0.1] Use of uninitialized value in concatenation (.) or string at ./pro/mscwrite_excel.pl line 337,
337: $t{ownerno} = 'ENQ: ' . $t{ownerno};
==>修改如下
if ( $t{ownerno} ) {
$t{ownerno} = 'ENQ: ' . $t{ownerno};
} else {
$t{ownerno} = 'ENQ: ';
}
]]>
<![CDATA[
[error] [client 127.0.0.1] Use of uninitialized value in concatenation (.) or string at ./pro/mscwrite_excel.pl line 260,
[error] [client 127.0.0.1] Use of uninitialized value in addition (+) at ./pro/mscwrite_excel.pl line 805,
[error] [client 127.0.0.1] Use of uninitialized value in addition (+) at ./pro/mscwrite_excel.pl line 807,
260: $t{word3} = 'YOUR REF. ' . $t{makerref};
805: $t{disc} = $t{disc1} + $t{disc2};
807: $t{total1} = $t{total1} + $t{disc} + $t{charges};
==>修改如下
if ( $t{makerref} ) {
$t{word3} = 'YOUR REF. ' . $t{makerref};
} else {
$t{word3} = 'YOUR REF. ';
}
if ( $t{disc2} ) {
$t{disc} = $t{disc1} + $t{disc2};
} else {
$t{disc} = $t{disc1};
}
if ( $t{charges} ) {
$t{total1} = $t{total1} + $t{disc} + $t{charges};
} else {
$t{total1} = $t{total1} + $t{disc};
}
]]>
<![CDATA[
[error] [client 127.0.0.1] Use of uninitialized value in numeric gt (>) at
./pro/mscwrite_excel.pl line 1577,
1577: if ( $$moneyref{one} > 0 ) {
==>修改如下
sub money_show {
my($moneyref) = @_;
if ( $$moneyref{one} ) { # 增加判断语句
if ( $$moneyref{moneyid} == 1 ) {
if ( $$moneyref{one} > 0 ) {
$$moneyref{one} = int($$moneyref{one}+0.5);
} else {
$$moneyref{one} = int($$moneyref{one}-0.5);
}
} else {
if ( $$moneyref{one} > 0 ) {
$$moneyref{one} = int(($$moneyref{one}*100+0.5))/100;
} else {
$$moneyref{one} = int(($$moneyref{one}*100-0.5))/100;
}
$$moneyref{one} = sprintf("%.02f",$$moneyref{one});
}
$$moneyref{one} = commify($$moneyref{one});
} else {
$$moneyref{one} = 0;
}
return($moneyref);
}
[error] [client 127.0.0.1] Argument "P2" isn't numeric in numeric gt (>) at
./pro/mscwrite_excel.pl line 1574,
1574: if ( $$moneyref{one} > 0 ) {
原因:price还没有任何输入,是P2!
mysql> select * from quo2 where id = 12396\G
*************************** 1. row ***************************
id: 12396
time: 2011-01-03
ORIGINid: 1
PRICEid: 2
PAYMENTid: 5
DELIVERY:
money: 1
percent0: 115
discount0: 100
percent: P1
discount: D
price: P2
D_FEE: NULL
total: NULL
memo:
disc0: 100
disc: D0
price0: P0
pi_ok: 2
1 row in set (0.00 sec)
$t{pp1} = $self->dbh->selectrow_array("SELECT price FROM quo2 WHERE id = $t{enq1_id}");
==>修改如下
$t{pp1} = $self->dbh->selectrow_array("SELECT price FROM quo2 WHERE id = $t{enq1_id}");
if ( $t{pp1} eq 'P2' ) {
$t{pp1} = 0;
}
[error] [client 127.0.0.1] Argument "C" isn't numeric in numeric gt (>) at
./pro/mscwrite_excel.pl line 419
419: if ( $t{seri2} > 1 ) {
==>修改如下
if ( $t{seri2} ne 'C' ) {
[error] [client 127.0.0.1] Use of uninitialized value in concatenation (.) or string at ./pro/mscwrite_excel.pl line 288,
288: $t{DELIVERY} = 'DELIVERY TIME: ' . $t{DELIVERY};
==>修改如下
if ( $t{DELIVERY} ) {
$t{DELIVERY} = 'DELIVERY TIME: ' . $t{DELIVERY};
} else {
$t{DELIVERY} = 'DELIVERY TIME: ';
}
[error] [client 127.0.0.1] Useless use of hash element in void context at ./pro/mscwrite_excel.pl line 502.
502 $t{sheet}->Rows($t{Y1})->{AutoFit};
==>AutoFit的作用不清楚。加#暂时退出
# $t{sheet}->Rows($t{Y1})->{AutoFit};
]]>
<![CDATA[
# QTNにNOTEを出力
if ( $t{excel_pat} eq 'quo2' ) {
$t{memo} = $self->dbh->selectrow_array("select memo from quo2 where id = $t{enq1_id} and memo is not NULL");
if ( $t{memo} ) {
if ( $t{memo} =~ /==/ ) {
@{ $t{memos} } = split(/==/,$t{memo});
$t{Y1} = $t{Y1} + 1;
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{memos}[0];
for $n ( 1 .. $#{ $t{memos} } ) {
$t{Y1} = $t{Y1} + 1;
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{memos}[$n];
}
} else {
$t{Y1} = $t{Y1} + 1;
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{memo};
}
}
====>
($t{memo},$t{pi_ok}) = $self->dbh->selectrow_array("select memo,pi_ok from quo2 where id = $t{enq1_id} and memo is not NULL");
if ( $t{pi_ok} == 1 ) {
$t{Y1} = $t{Y1} + 1;
$t{sheet}->Cells($t{Y1},"C")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"C")->{Font}->{Bold} = "False";
# $t{sheet}->Cells($t{Y1},"E")->{WrapText} = "False";
$t{XY1} = 'C' . $t{Y1};
$t{XY2} = 'G' . $t{Y1};
$t{sheet}->Range("$t{XY1}:$t{XY2}")->{MergeCells} = "True";
$t{sheet}->Cells($t{Y1},"C")->{'Value'} = 'THE PRICE IS NOT INCLUDING PACKING & HANDING CHARGE.';
}
##------写入excel,生成给船东的QTN文件
} elsif ( $t{pat} eq 'excel' ) {
$$e_ref{excel_pat} = 'quo2';
$$e_ref{excel} = $t{q}->param("excel");
$$e_ref{enq1_id} = $t{quo2_id};
($e_ref,$self) = write_excel($e_ref,$self);
]]>
<![CDATA[
恢复DWG.的写入
--------------------------------------------------------------
if ( $t{DWG1} eq 'XXXDWG' ) { # 没有图纸号的情况
# $t{line1} = 'DWG. NO. ';
$t{line1} = 'DWG. ';
$t{line1} = '';
} else {
# $t{line1} = 'DWG. NO. ' . $t{DWG1};
$t{line1} = 'DWG. ' . $t{DWG1};
$t{line1} = $t{DWG1};
}
取消DWG.NO.的写入
--------------------------------------------------------------
if ( $t{DWG1} eq 'XXXDWG' ) { # 没有图纸号的情况
# $t{line1} = 'DWG. NO. ';
$t{line1} = '';
} else {
# $t{line1} = 'DWG. NO. ' . $t{DWG1};
$t{line1} = $t{DWG1};
}
--------------------------------------------------------------
# $t{pris} = $t{Q1}*$t{pri1};
$$moneyref{one} =~ s/\,//;
$t{pris} = $t{Q1}*$$moneyref{one};
--------------------------------------------------------------
格式问题
Excelの「折り返して全体を表示」と印刷レイアウト
折り返し位置を編集画面と印刷レイアウトで一致させる
MSゴシックは等幅フォント・MSPゴシックはプロポーショナルフォント
デフォルトのフォントはMSPゴシック。
MSゴシックにすると編集画面と印刷レイアウトはほぼ一致
==>我的情况是数字!
英語等幅系 Courier New
英語セリフ系 Times New Roman
--------------------------------------------------------------
ship price的乘的问题
# $t{pris} = $t{Q1}*$t{pri1};
$$moneyref{one} =~ s/\,//;
$t{pris} = $t{Q1}*$$moneyref{one};
需要用出现在表格上的数字,注意要把逗号去掉。
--------------------------------------------------------------
$t{sheet}->Cells($t{Y1},"F")->{NumberFormatLocal} = "@";
3420=>3,420
--------------------------------------------------------------
把money的显示子程序化
$$moneyref{one} = $t{packing_charge};
($moneyref)=money_show($moneyref);
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $$moneyref{one};
sub money_show {
my($moneyref) = @_;
if ( $$moneyref{moneyid} == 1 ) {
if ( $$moneyref{one} > 0 ) {
$$moneyref{one} = int($$moneyref{one}+0.5);
} else {
$$moneyref{one} = int($$moneyref{one}-0.5);
}
} else {
if ( $$moneyref{one} > 0 ) {
$$moneyref{one} = int(($$moneyref{one}*100+0.5))/100;
} else {
$$moneyref{one} = int(($$moneyref{one}*100-0.5))/100;
}
$$moneyref{one} = sprintf("%.02f",$$moneyref{one});
}
$$moneyref{one} = commify($$moneyref{one});
return($moneyref);
}
---------------------------------------------------------------
小于0的整数处理!
if ($t{disc} > 0 ) {
$t{disc} = int($t{disc}+0.5);
} else {
$t{disc} = int($t{disc}-0.5);
}
---------------------------------------------------------------
# order1, 2010/08/16追加
if ( $t{moneyid} == 1 ) {
# $t{total1} = int($t{total1}+0.5); # 2010/08/21修正
} else {
$t{total1} = sprintf("%.02f",$t{total1});
}
---------------------------------------------------------------
if ( $t{moneyid} == 1 ) {
# $t{disc} = int($t{disc}+0.5);
$t{disc} = int($t{disc}+0.5); # 2010/08/16恢复
} else {
$t{disc} = sprintf("%.02f",$t{disc});
}
# order1, 2010/08/16追加
if ( $t{moneyid} == 1 ) {
$t{total1} = int($t{total1}+0.5);
} else {
$t{total1} = sprintf("%.02f",$t{total1});
}
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
$t{XY1} = "G" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
---------------------------------------------------
删除双引号
$t{name1} =~ s/^\"//; #削除2010/08/12
$t{name1} =~ s/\"$//;
----------------------------------------------------------------
EXCEL文件的逗号问题!!!
该子程序在mscqtn_input.pl
sub commify {
my $text = reverse $_[0];
$text =~ s/(\d\d\d)(?=\d)(?!\d*\.)/$1,/g;
return scalar reverse $text;
}
输入指定数值单元
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
输入字体加粗
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "True";
输入字体不加粗
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
]]>
<![CDATA[
插入图像文件
# MSC mark
$t{Y1} = $t{Y1} - 7;
$t{XY1} = 'C' . $t{Y1};
$t{sheet}->Range("$t{XY1}")->Select;
$t{sheet}->Pictures->Insert("E:\\wwwexcel\\samples\\msc_mark.jpg")->Select;
$t{ex}->Selection->ShapeRange->IncrementLeft("10.5");
$t{ex}->Selection->ShapeRange->IncrementTop("6.75");
# SIGN
$t{XY1} = 'E' . $t{Y1};
$t{sheet}->Range("$t{XY1}")->Select;
$t{sheet}->Pictures->Insert("E:\\wwwexcel\\samples\\sign.jpg")->Select;
EXCEL 的Micro登记
Sub Macro2()
ActiveSheet.Pictures.Insert("C:\wwwexcel\samples\sign.GIF").Select
Selection.ShapeRange.IncrementLeft -125.25
Selection.ShapeRange.IncrementTop 327.75
Selection.ShapeRange.IncrementLeft -59.25
End Sub
Sub Macro4()
Range("G40").Select
ActiveSheet.Pictures.Insert("C:\wwwexcel\samples\sign.GIF").Select
End Sub
PERL sample
use strict;
use warnings;
use Win32::OLE;
my $excel = Win32::OLE->CreateObject('Excel.Application');
$excel->{Visible} = 1;
my $wb = $excel->Workbooks->Add;
$wb->ActiveSheet->Pictures->Insert( "C:\\s\\x.png" )->Select;
$excel->Selection->ShapeRange->IncrementLeft( 40 );
$excel->Selection->ShapeRange->IncrementTop( 40 );
PERL实现
$t{excel_pat} eq 'invoice'
]]>
<![CDATA[
问题解决,把'置换掉!
$t{name1} =~ s/^\'//; #削除2009/08/14
$t{name1} =~ s/\'$//;
Parts的引用符号问题?印刷时有吗?
excel 文字 引用符
]]>
返回