需要更多有关如何使用Spreadsheet :: ParseExcel的示例 [英] Need more examples on how to use Spreadsheet::ParseExcel

问题描述

我一直在使用 Spreadsheet :: ParseExcel 列出其中的内容电子表格.我已经看到了几个有关如何转储整个电子表格的示例.我真的很想看看如何更选择性地使用此脚本.

I have been using the Spreadsheet::ParseExcel to list the contents of spreadsheet. I've seen several examples on how to dump the entire spreadsheet. I really would like to see how to use this script more selectively.

下面来自IBM的示例基本上转储了所有具有数据的单元格的内容.

The example below from IBM basically dumps the content of all cells that have data.

#!/usr/bin/perl -w

use strict;
use Spreadsheet::ParseExcel;

my $oExcel = new Spreadsheet::ParseExcel;

die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV;

my $oBook = $oExcel->Parse($ARGV[0]);
my($iR, $iC, $oWkS, $oWkC);
print "FILE  :", $oBook->{File} , "\n";
print "COUNT :", $oBook->{SheetCount} , "\n";

print "AUTHOR:", $oBook->{Author} , "\n"
 if defined $oBook->{Author};

for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
{
 $oWkS = $oBook->{Worksheet}[$iSheet];
 print "--------- SHEET:", $oWkS->{Name}, "\n";
 for(my $iR = $oWkS->{MinRow} ;
     defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;
     $iR++)
 {
  for(my $iC = $oWkS->{MinCol} ;
      defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ;
      $iC++)
  {
   $oWkC = $oWkS->{Cells}[$iR][$iC];
   print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);
  }
 }
}

有人可以给我一个例子，说明如何为每一行指定对某列采取的操作吗?

Can someone give me an example of how I can specify some action to take for a certain column for every row?

例如，我有一个包含7列n行的电子表格.我想以某种方式重新格式化每个列中的数据.也许我想为每行取第6列，并将一些文本附加到存储在单元格中的字符串的末尾.怎么设置?

For example, I have a spreadsheet with 7 columns and n rows. I want to reformat the data in each of the columns in a certain way. Perhaps I want take column 6 for every row and append some text to the end of the string stored in the cell. How would that be set up?

推荐答案

要修改Excel文件， Spreadsheet :: ParseExcel :: SaveParser 模块非常有用.它允许您读取文件，进行修改，然后将更改后的内容写入新文件.这是一个简单的示例:

To modify an Excel file, the Spreadsheet::ParseExcel::SaveParser module is very useful. It allows you to read a file, make modifications, and then write the altered content to a new file. Here's a simple example:

use strict;
use warnings;

use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;

my $excel_file_name = $ARGV[0];
my $parser          = Spreadsheet::ParseExcel::SaveParser->new();
my $workbook_orig   = $parser->Parse($excel_file_name);

# We will edit column 7 of the first worksheet.
my $worksheet = $workbook_orig->worksheet(0);
my $EDIT_COL = 6;

my ($row_min, $row_max) = $worksheet->row_range();
for my $r ($row_min .. $row_max){
    my $cell = $worksheet->get_cell($r, $EDIT_COL);
    unless (defined $cell){
        next; # Modify as needed to handle blank cells.
    }
    my $val = $cell->value . '_append_text';
    $worksheet->AddCell( $r, $EDIT_COL, $val, $cell->{FormatNo} );
}

# You can save the modifications to the same file, but when
# you are learning, it's safer to write to a different file.
$excel_file_name =~ s/\.xls$/_new.xls/;
$workbook_orig->SaveAs($excel_file_name);

有关其他许多示例，请参见出色的文档:

For many other examples, see the excellent documentation:

• 电子表格:: ParseExcel :: SaveParser
• 电子表格:: WriteExcel
• 电子表格:: WriteExcel :: Examples
• 电子表格:: ParseExcel

• Spreadsheet::ParseExcel::SaveParser
• Spreadsheet::WriteExcel
• Spreadsheet::WriteExcel::Examples
• Spreadsheet::ParseExcel

这篇关于需要更多有关如何使用Spreadsheet :: ParseExcel的示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！