工作中,需按客户(医院)提供的数据重建核算科室的核算报表,
perl帮了我的大忙。Text::Table模块用于报表输出可以让代码简洁直观些。
以下代码只是示例如何从Excel中取数,并用Text::Table格式化输出报表。其中的财务数据的勾兑关系不一定正确,而且输出的报表也不全,缺少人员经费这部分的项目。
测试.rar
- #!/usr/bin/Perl
- use Cwd;
- use Win32::OLE;
- use Text::Table;
- #use Acme::PerlTidy;
- my $nll = '×';
- my $dir = getcwd();
- my $BookName = $dir . '/收入成本.xls';
- my @arrSh = qw(1月 2月 3月 4月);
- my $Excel = Win32::OLE->new("Excel.Application");
- $Excel->{Visible} = 1;
- $Book = $Excel->Workbooks->Open($BookName);
- foreach my $sheet (@arrSh) {
- $Book->Sheets($sheet)->Select;
- my $ActSheet = $Book->ActiveSheet;
- my $ks = $ActSheet->Range('A3:A20')->{'Value'};
- foreach my $i ( 0 .. 17 ) {
- my $ks_now = $ks->[$i][0];
- my $j = $i + 3;
- my $sr = $ActSheet->Range("B$j:C$j")->{'Value'};
- my $cb = $ActSheet->Range("E$j:M$j")->{'Value'};
- my $ylsr = $sr->[0][0] - $sr->[0][1];
- my $qtcb = $cb->[0][4] + $cb->[0][5];
- my $tb = Text::Table->new();
- $tb->load(
- [ "一、业务收入", $nll, "四、业务费", $nll ],
- [ "药品收入", $sr->[0][1], "1、药品成本", $cb->[0][2] ],
- [ "挂号收入", $nll, "2、高值耗材成本", $cb->[0][3] ],
- [ "门诊治疗收入", $nll, "3、氧气成本", $nll ],
- [ "心超收入(电生理)", $nll, "4、卫生材料", $cb->[0][1]],
- [ "化验收入(检验科)", $nll, "5、低值易耗品", $nll],
- [ "X、CT收入(放射科)", $nll, "6、洗涤费",$nll ],
- [ "理疗收入(理疗科)", $nll, "7、消毒费",$nll ],
- [ "检查收入",$nll, "8、器设维修", $nll ],
- [ "住院治疗收入",$ylsr, "9、保洁费",$nll ],
- [ "×××××××××××",$nll, "10、差旅费",$nll ],
- ["×××××××××××", $nll, "11、水电支出", $cb->[0][0]],
- [ "×××××××××××", $nll, "12、电话费",$nll ],
- [ "×××××××××××", $nll, "13、取暖费",$nll ],
- [ "×××××××××××", $nll, "14、其他",$qtcb ],
- [ "×××××××××××", $nll, "×××××××××××", $nll ],
- [ "×××××××××××", $nll, "×××××××××××", $nll ],
- ["×××××××××××", $nll,"五、折旧摊销费", $cb->[0][7]],
- [ "×××××××××××", $nll, "×××××××××××", $nll ],
- ["×××××××××××",$nll,"手术室费用(备查)", $cb->[0][6]],
- );
- open( FH, ">>Srcb.txt" );
- print FH "$ks_now:$sheet\n";
- print FH "$tb\n";
- close FH;
- }
- }
- $Book->Save;
- $Book->Close;
- $Excel->quit;
格式化输出至HTML向CU论坛的ttcn_cu又学了一招
- #!/usr/bin/Perl
- use Text::Table;
- my $nll = '×';
- my $tb = Text::Table->new();
- $tb->load(
- [ "一、业务收入", 44, "四、业务费", $nll ],
- [ "药品收入", 22, "1、药品成本", 22 ],
- [ "挂号收入", $nll, "2、高值耗材成本", 22 ],
- [ "门诊治疗收入", $nll, "3、氧气成本", $nll ],
- [ "心超收入(电生理)", $nll, "4、卫生材料", 22],
- [ "化验收入(检验科)", $nll, "5、低值易耗品", $nll],
- [ "X、CT收入(放射科)", $nll, "6、洗涤费",$nll ],
- [ "理疗收入(理疗科)", $nll, "7、消毒费",$nll ],
- [ "检查收入",$nll, "8、器设维修", $nll ],
- [ "住院治疗收入",22, "9、保洁费",$nll ],
- [ "×××××××××××",$nll, "10、差旅费",$nll ],
- ["×××××××××××", $nll, "11、水电支出", 22],
- [ "×××××××××××", $nll, "12、电话费",$nll ],
- [ "×××××××××××", $nll, "13、取暖费",$nll ],
- [ "×××××××××××", $nll, "14、其他",17 ],
- [ "×××××××××××", $nll, "×××××××××××", $nll ],
- [ "×××××××××××", $nll, "×××××××××××", $nll ],
- ["×××××××××××", $nll,"五、折旧摊销费", 22],
- [ "×××××××××××", $nll, "×××××××××××", $nll ],
- ["×××××××××××",$nll,"手术室费用(备查)", 22],
- );
- my $outputbuff='';
- my $line_num= $tb->body_height();
- for my $i (0..$line_num-1){
- $outputbuff.="
";
- my @row=split(/\s+/,$tb->body($i));
- for (@row){
- $outputbuff.="
$_ | ";
- }
- $outputbuff.="
";
- }
- $outputbuff.="";
- open OUTPUT,'>','out1.html';
- print OUTPUT "
";
- print OUTPUT $outputbuff;
- close OUTPUT;
- open OUTPUT,'>','out2.html';
- print OUTPUT "
";
- print OUTPUT $outputbuff;
- close OUTPUT;
- #############################################
- #另外一个使用Text::Table的原因,就是可以很容易地按行(取单行:$tb->body($i);取多行:$tb->body(0..$i)或$tb->body(0,1,$i))或按列($tb->select($i))取数据,再结合使用切片可以方便地计算出需要的数据
- my @temp=split(/\n/,$tb->select(1));
- my @col=grep(/\d+/,@temp[1..$#temp]);
- print '业务收入='.join('+',@col),"\n" ;
阅读(1907) | 评论(0) | 转发(0) |