Perl 5.6处理CSV独立零值并写入Excel的技术难题求助

Let's break down fixes for your issues and build a reliable script that works within your constraints:

1. Fix Regex Replacement (Avoid system/exec Calls) #

The main reason your command-line regex fails in system/exec is shell escaping hell—special regex characters like \b, $, or * get mangled when passed through the shell. Instead, handle regex directly in Perl, which is cleaner and avoids external process overhead.

Bad (External Call): #

# This will fail due to shell escaping
system("echo '$field' | sed 's/\\b0\\b/0/g'");

Good (Inline Perl Regex): #

# Matches standalone zeros and preserves them exactly
$field =~ s/\b0\b/0/g;

This works exactly as your command-line regex but without shell interference. If your goal is to ensure standalone zeros are retained as strings for Excel, we’ll handle that in the Excel writing step below.

2. Fix Text::CSV "Swallowing" Zeros #

The issue here is almost certainly a boolean check mistake, not Text::CSV itself. In Perl, the string "0" evaluates to false in a boolean context, so code like if ($field) { ... } will skip it. Here’s how to use Text::CSV correctly to preserve all fields:

Correct Text::CSV Usage: #

use Text::CSV;

my $csv = Text::CSV->new({ 
    binary => 1,          # Handle non-ASCII characters
    allow_whitespace => 1 # Trim leading/trailing spaces in fields
});

open my $fh, '<', 'input.csv' or die "Can't open CSV: $!";
while (my $row = $csv->getline($fh)) {
    foreach my $field (@$row) {
        # Check if field exists (including "0")
        if (defined $field && $field ne '') {
            # Process the field
        } else {
            # Handle empty fields if needed
        }
    }
}
close $fh;

This ensures "0" is never skipped, as we explicitly check for definedness and non-empty strings instead of relying on boolean truthiness.

3. Dynamic Column Handling (String vs Numeric) #

Since your reports have M leading string columns followed by N numeric columns (unknown counts), we’ll use the most reliable approach: let you pass M as a command-line argument (you likely know this per report, even if it varies between reports).

Pass M as an Argument (Recommended) #

Run your script like perl csv_to_excel.pl 3 input.csv output.xlsx where 3 is the number of string columns. This avoids guesswork if string columns have numeric-looking values.

4. Full Working Script #

Here’s a complete script that ties everything together, uses Excel::Writer::XLSX, preserves zeros, and handles dynamic columns:

use strict;
use Excel::Writer::XLSX;
use Text::CSV;

# Get number of string columns from command line
my $M = shift @ARGV;
die "Usage: $0 <num_string_columns> <input.csv> <output.xlsx>" 
    unless defined $M && $M =~ /^\d+$/ && @ARGV == 2;

my ($input_csv, $output_xlsx) = @ARGV;

# Create Excel workbook
my $workbook = Excel::Writer::XLSX->new($output_xlsx);
my $worksheet = $workbook->add_worksheet();

# Initialize CSV parser
my $csv = Text::CSV->new({ binary => 1, allow_whitespace => 1 });

open my $fh, '<', $input_csv or die "Can't open $input_csv: $!";

my $row_num = 0;
while (my $row = $csv->getline($fh)) {
    my $col_num = 0;
    foreach my $field (@$row) {
        if ($col_num < $M) {
            # String column: write as string to preserve "0" and formatting
            $worksheet->write_string($row_num, $col_num, $field) 
                if defined $field;
        } else {
            # Numeric column: convert to number, preserve 0
            if (defined $field && $field ne '') {
                # Convert to numeric (handles "0" -> 0, "123" -> 123, etc.)
                my $num = $field + 0;
                $worksheet->write_number($row_num, $col_num, $num);
            } else {
                # Write blank for empty fields
                $worksheet->write_blank($row_num, $col_num);
            }
        }
        $col_num++;
    }
    $row_num++;
}

close $fh;
$workbook->close();

print "Successfully generated $output_xlsx\n";

Key Notes: #

• Zero Preservation: String columns use write_string to ensure "0" is stored as a string in Excel. Numeric columns convert "0" to the number 0, which Excel displays correctly.
• No External Calls: All processing is done inline in Perl, avoiding shell regex issues.
• CSV Safety: Uses Text::CSV to handle quoted fields, commas inside fields, and escaped quotes—something manual line splitting can’t reliably do.

If you must use manual line splitting (e.g., no Text::CSV), replace the CSV parsing part with this regex-based splitter (handles quoted fields):

sub split_csv {
    my ($line) = @_;
    chomp $line;
    my @fields;
    while ($line =~ /(?:^|,)(?:"([^"]*(?:""[^"]*)*)"|([^,"]*))/g) {
        my $field = defined $1 ? $1 : $2;
        $field =~ s/""/"/g if defined $field; # Unescape quotes
        push @fields, $field;
    }
    return \@fields;
}

# Usage:
while (my $line = <$fh>) {
    my $row = split_csv($line);
    # Process row...
}

内容的提问来源于stack exchange，提问作者alejandrocerrano