excel - 解析 Excel 数据引发“文件错误”
问题描述
我在 Perl 脚本中有三个函数来解析 Excel 文件以获取所需的输出 Excel 文件。我得到了正确的输出,但是我得到了一个错误
文件错误:数据可能已丢失
可能根本原因是在文件中两次写入同一个 Excel 单元格。
如何在维护脚本中的功能的同时摆脱此错误?
输入文件
A B
Apples Carrots
Oranges Broccoli
Grapes Spinach
期望输出文件
A B
Apples Carrots
Oranges Broccoli
PEACHES ASPARAGUS
Perl 代码
use v5.10.0;
use warnings;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel;
my $parser = Spreadsheet::ParseExcel::SaveParser->new();
my $workbook_R = $parser->parse('C:\Perl\databases\Fruits_and_Veggies.xls');
my $workbook_W = Spreadsheet::WriteExcel->new('C:\Perl\databases\New_Fruits_and_Veggies.xls');
my $worksheet_W = $workbook_W->add_worksheet();
for our $worksheet_R ( $workbook_R->worksheets() ) {
my ( $row_min, $row_max ) = $worksheet_R->row_range();
my ( $col_min, $col_max ) = $worksheet_R->col_range();
for our $row ( $row_min .. $row_max ) {
for our $col ( $col_min .. $col_max ) {
FruitStand();
VeggieStand();
ComboStand();
#------------------------------------------------------------------------------
# sub FruitStand - parsing: replace Grapes with PEACHES
#------------------------------------------------------------------------------
sub FruitStand {
# if the cell contains Grapes write 'PEACHES' instead
my $cell_grapes = $worksheet_R->get_cell( $row, $col );
if ( $cell_grapes->value() =~ /Grapes/ ) {
$worksheet_W->write($row, $col,"PEACHES");
}
}
#------------------------------------------------------------------------------
# sub VeggieStand - parsing: repalce Spinach with ASPARAGUS
#------------------------------------------------------------------------------
sub VeggieStand {
# if the cell contains Spinach write 'ASPARAGUS' instead
my $cell_veggies = $worksheet_R->get_cell( $row, $col );
# my $cell = $worksheet_R->get_cell( $row, $col );
if (/ $cell_veggies->value() =~ /Spinach/ ) {
$worksheet_W->write($row, $col,"ASPARAGUS");
}
}
#------------------------------------------------------------------------------
# Writing all fruits and veggies with the 2 changes (PEACHES and ASPARAGUS)
#------------------------------------------------------------------------------
sub ComboStand {
my $cell = $worksheet_R->get_cell( $row, $col );
$worksheet_W->write($row, $col, $cell->value()) ;
}
}
}
}
解决方案
正如您所说,错误消息是由于多次写入单元格造成的。您可以通过确保每个单元格只写入一次来消除错误消息。由于您的三个子例程具有非常相似的功能,因此可以将它们组合成一组可以完成所有操作的行,并使用if
/else
级联来决定应采取的操作。
for our $row ( $row_min .. $row_max ) {
for our $col ( $col_min .. $col_max ) {
my $cell = $worksheet_R->get_cell( $row, $col );
if($cell->value() =~ /Spinach/) {
$worksheet_W->write($row, $col,"ASPARAGUS");
}
elsif($cell->value() =~ /Grapes/) {
$worksheet_W->write($row, $col,"PEACHES");
}
else {
$worksheet_W->write($row, $col, $cell->value()) ;
}
}
}
如果你必须保留这些函数,我建议像这样,你有一个函数可以获取当前单元格并对其应用任何适当的转换,并返回准备好输出的文本。这将重复读取单元格并将单元格写入子程序之外:
for our $row ( $row_min .. $row_max ) {
for our $col ( $col_min .. $col_max ) {
my $cell = $worksheet_R->get_cell( $row, $col );
$worksheet_w->write( $row, $col, produce_check($cell->value) );
}
}
执行交换的produce_check
sub 也将是进行任何文本规范化或您可能想要对输入进行的其他检查的好地方,例如删除额外的空格、将所有输出设置为标题大小写等。
sub produce_check {
my $prod = shift;
# maybe we have to make sure there's no trailing whitespace on $prod
$prod =~ s/\s*$//;
my %swaps = (
grapes => 'peaches',
spinach => 'asparagus',
tins => 'cans',
zuchini => 'zucchini'
);
# is $prod one of pieces of produce we have to swap?
# perhaps our input is in a mixture of cases, uppercase, lowercase, titlecase
# to avoid having to add all those variations to the %swaps hash, we convert
# to lowercase using `lc`
if ( $swaps{ lc($prod) } ) {
$prod = $swaps{ lc($prod) };
}
# this line uses `ucfirst($prod)` to convert all output to titlecase.
# You could also convert everything to lowercase ( `lc($prod)` ), to
# uppercase ( `uc($prod)` ), or just leave it as-is by using `return $prod;`
return ucfirst( $prod );
}
推荐阅读
- excel - 如果行中的单元格包含日期行中的“新”返回日期
- java - 系统地注释 PDF
- python - 静态文件不会在 django 中更新
- cordova - 如何在 Ionic 3 应用程序中的 Multiview 上显示启动画面
- c# - 如何创建下一个和上一个按钮来导航列表?
- firebase - 当我使用变量时,Firebase 更新查询无限运行
- asp.net-core - 400 Bad Request error with asp .net core hosted with Dokku on DigitalOcean
- gitlab - TortoiseGit 在尝试 lfs 跟踪文件的差异时响应“/usr/bin/sh: git-lfs: command not found”
- android - 如何将图像添加到 Android Studio 中的随机 ImageView?
- java - 在 Java 中实现 SHA1withRSA