在Perl中，如何将XLSX工作表中的列子集复制到另一列？ [英] In Perl, how can I copy a subset of columns from an XLSX work sheet to another?

问题描述

我有一个 .xlsx 文件（只有一张），共15列。我想阅读一些具体的列，我们来看列3，5，11，14，并写入一个新的Excel表。在这种情况下，一些输入文件的单元格为空，表示没有任何值。

I have a .xlsx file (only one sheet) with 15 columns. I want to read some specific columns, let's say columns 3, 5, 11, 14 and write it to a new Excel sheet. In this case some cells of input files are empty means don't have any value.

这里我正在尝试：

use warnings;
use strict;

use Spreadsheet::ParseXLSX;
use Excel::Writer::XLSX;

my $parser = Spreadsheet::ParseXLSX->new;
my $workbook = $parser->parse("test.xlsx");

if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}

my $worksheet = $workbook->worksheet('Sheet1');

# but from here I don't know how to define row and column range to get specific column data.
# I am trying to get all data in an array, so I can write it in new .xlsx file.

# function to write data in new file
sub writetoexcel
{
    my @fields = @_;
    my $workbook = Excel::Writer::XLSX->new( 'report.xlsx' );
    $worksheet = $workbook->add_worksheet();

    my $row = 0;
    my $col = 0;
    for my $token ( @fields )
    {
        $worksheet->write( $row, $col, $token );
        $col++;
    }
    $row++;
}

我也按照这个问题，但没有运气。

I also followed this Question, but no luck.

如何从 .xlsx 文件并将其写入新的 .xlsx 文件？

How can I read specific columns from .xlsx file and write it into new .xlsx file?

推荐答案

你从来没有将一列数组从一组数组复制到另一个数组？

Have you never copied a subset of columns from an array of arrays to another?

这是我用于这个的输入表：

Here is the input sheet I used for this:

，这是在代码运行后输出文件中得到的：

and, this is what I get in the output file after the code is run:

#!/usr/bin/env perl

use strict;
use warnings;

use Excel::Writer::XLSX;
use Spreadsheet::ParseXLSX;

my @cols = (1, 3);

my $reader = Spreadsheet::ParseXLSX->new;
my $bookin = $reader->parse($ARGV[0]);
my $sheetin = $bookin->worksheet('Sheet1');

my $writer =  Excel::Writer::XLSX->new($ARGV[1]);
my $sheetout = $writer->add_worksheet('Extract');

my ($top, $bot) = $sheetin->row_range;

for my $r ($top .. $bot) {
    $sheetout->write_row(
        $r,
        0,
        # of course, you need to do more work if you want
        # to preserve formulas, formats etc. That is left
        # to you, as you left that part of the problem
        # unspecified.
        [ map $sheetin->get_cell($r, $_)->value, @cols ],
    );
}

$writer->close;

这篇关于在Perl中，如何将XLSX工作表中的列子集复制到另一列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！