任何人都可以帮助从xlsx格式文件中解析特定的行和列，并在另一个xlsx文件中重新返回

Question

我正在尝试从xlsx文件中解析1st和7th列，我想将解析后的列重写为另一个新的xlsx格式文件作为输出。

use strict;
use warnings;
use Spreadsheet::WriteExcel;
use Spreadsheet::Read;
use constant FILE_NAME => 'file1.xls';
my $workbook = ReadData ("Next seq_Run039_ Data Transfer     sheet_22.1.2015.xlsx");
$workbook = Spreadsheet::WriteExcel->new(FILE_NAME)
|| die "could not create $!";
$workbook->compatibility_mode();
my $worksheet = $workbook->add_worksheet();
#my $parser = $worksheet->"Next seq_Run039_ Data Transfer sheet_22.1.2015.xlsx";
$worksheet->write_col(0, 0,[[qw(Header)],[ qw(File_Version), 1 ],[], [qw(Data)], [qw(SampleID SampleName Index Index2)]]);
$worksheet->write_col( 5, 0,  [1 .. 100] );

$workbook->close() or die "Error closing file: $!";

我的问题是如何解析数据并将其打印到另一个文件中？

Answer 1

我不确定你想对这些列做什么，以及你想把它放在哪里，但这段代码可以给你提供线索。

use strict;
use warnings;
use Spreadsheet::Read;
use Excel::Writer::XLSX;
use List::Util qw(max);
use constant FILE_IN   => 'Next seq_Run039_ Data Transfer     sheet_22.1.2015.xlsx';
use constant FILE_OUT  => 'file.xlsx';
use constant SHEET     => 1;
use constant COLS      => ( 1, 7 );
use constant SKIP_ROWS => 3;
use constant COL_SPACE => 0;
my $book = ReadData(FILE_IN);
my @cols = @{ $book->[SHEET]{cell} }[COLS];
# undef $book; # you can release rest of $book
# there is filler as 0. row so +1
# note splice is destructive to $book->[SHEET]{cell} }[COLS] content but not to FILE_IN
splice @$_, 0, SKIP_ROWS + 1 for @cols;
my $max_row = max map scalar @$_, @cols;
# replace any space in @cols content by single '_'
map s/s+/_/g, @$_ for @cols;
# Create new Spreadsheet
my $workbook  = Excel::Writer::XLSX->new(FILE_OUT);
my $worksheet = $workbook->add_worksheet();
# add header
my @header = (
    [qw(Header)], [ qw(File_Version), 1 ],
    [], [qw(Data)], [qw(SampleID SampleName Index Index2)]
);
$worksheet->write_col( 0, 0, @header );
# insert columns from 3rd column
$worksheet->write( scalar @header,
    0, [ [ 1 .. $max_row ], ( [] ) x COL_SPACE, @cols ] );
$workbook->close();