使用Spreadsheet :: WriteExcel [英] Using Spreadsheet::WriteExcel
本文介绍了使用Spreadsheet :: WriteExcel的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想从一张Excel工作表中提取一些信息,然后将其重新格式化为另一张.数据结构类似于:
I want to extract some information from one Excel sheet and re-format it into another one. The data structure looks something like:
col1 col2
row1 School 1
row2 Dean John
row3 No.stu. 55
row4 some irrelevant stuff
row5 School2 2
row6 Dean Tony
row7 No. stu. 60
row8 some irrelevant stuff
row9 School 3
row10 Dean James
row11 No.stu. 56
row12 No. teacher 20
我想要实现的输出是:
col1 col2 col3
row1 School Dean No.stu. No. teacher
row2 1 John 55
row3 2 Tony 60
row4 3 James 56 20
And the code I have been advised to use to extract information from Excel is the following (thanks to hdb from PerlMonks).
use strict; use warnings;
use Spreadsheet::ParseExcel;
my ($infile) = @ARGV;
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse($infile);
die $parser->error unless defined $workbook;
my ($worksheet) = $workbook->worksheets();
my %data; # Accumulate data here
my $row = 0;
my $school = 0;
while (1) {
my $cell = $worksheet->get_cell($row, 0);
last unless defined($cell);
my $key = $cell->value();
my $data = $worksheet->get_cell($row++, 1)->value();
if( $key eq "School" ) {
$school = $data;
}
else {
$data{$school}{$key} = $data;
}
}
现在,下一步是将所有内容重新写入新的Excel工作表中.我该怎么办?
Now the next step is to re-write everything into a new Excel sheet. How could I do that?
推荐答案
您的代码有点破损.这是我的:
Your code is a bit broken. Here is mine:
use strict;
use warnings;
use Spreadsheet::ParseExcel;
use FindBin qw($Bin);
my ($infile) = @ARGV;
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse("$Bin/Test.xls");
die $parser->error unless defined $workbook;
my ($worksheet) = $workbook->worksheets();
my %data;
my $row = 0;
my $school = "";
while (1) {
my $cell = $worksheet->get_cell( $row, 0 );
last unless defined($cell);
my $key = $cell->value();
my $value = $worksheet->get_cell( $row++, 1 )->value();
if ( $key eq "School" ) {
$school = $value;
next;
}
$data{$school}->{$key} = $value;
}
sleep 1;
use Spreadsheet::WriteExcel;
$workbook = Spreadsheet::WriteExcel->new('Result.xls');
$worksheet = $workbook->add_worksheet();
my $col = 0;
$row = 0;
$worksheet->write( $row++, $col,
[ "School", "Dean", "No.stu.", "No. teacher" ] );
foreach my $school ( sort keys %data ) {
$worksheet->write( $row++, $col,
[ $school, @{ $data{$school} }{ "Dean", "No.stu.", "No. teacher" } ] );
}
$workbook->close();
这篇关于使用Spreadsheet :: WriteExcel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文