对 excel 进行归档的 perl 脚本

archive.pl

#!/usr/bin/env perl

use strict;
use warnings;
use utf8;
use autodie;
use 5.010;

#use Data::Dumper;
use Encode;
use Excel::Writer::XLSX;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::FmtUnicode;

my $map = Unicode::Map->new("GB2312");

my $parser   = Spreadsheet::ParseExcel->new();
my $formatter = Spreadsheet::ParseExcel::FmtUnicode->new(Unicode_Map=>"CP936");
my $workbook = $parser->parse('金山区和石化使用汇总.xls', $formatter);

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

my $company_worksheet = $workbook->worksheet(encode('cp936', '企业信息总表'));
my $chemicals_worksheet = $workbook->worksheet(encode('cp936', '使用企业_危化品使用'));

my $output_workbook  = Excel::Writer::XLSX->new( 'a.xls' );
my $output_worksheet = $output_workbook->add_worksheet();
die "Problems creating new Excel file: $!" if !defined $workbook;

my ( $row_min_of_company_worksheet, $row_max_of_company_worksheet ) = $company_worksheet->row_range();
my ( $row_min_of_chemicals_worksheet, $row_max_of_chemicals_worksheet ) = $chemicals_worksheet->row_range();

#say $row_max_of_chemicals_worksheet;

my $row_in_chemicals_worksheet = 1;
my $row_in_output_worksheet = 1;
for my $row_in_company_worksheet (1..$row_max_of_company_worksheet) {
    #say $row_in_company_worksheet;
    my $company_id = 0 + encode('utf-8', decode('cp936', $company_worksheet->get_cell($row_in_company_worksheet, 0)->value));
    my $company_name = encode('utf-8', decode('cp936', $company_worksheet->get_cell($row_in_company_worksheet, 3)->value));
    my @company_informations = ( $company_name );
    for my $col (4..9) {
        push @company_informations, encode('utf-8', decode('cp936', $company_worksheet->get_cell($row_in_company_worksheet, $col)->value));
    }
    push @company_informations, [];
    #say $company_name;
    while (1) {
        if ($row_in_chemicals_worksheet > $row_max_of_chemicals_worksheet) {
            last;
        }
        #say '$row_in_chemicals_worksheet = ', $row_in_chemicals_worksheet;
        my $current_company_id_in_chemicals_worksheet = 0 + encode('utf-8', decode('cp936', $chemicals_worksheet->get_cell($row_in_chemicals_worksheet, 0)->value ));
        if ($company_id > $current_company_id_in_chemicals_worksheet ) {
            next;
        }
        if ($company_id == $current_company_id_in_chemicals_worksheet ) {
            my @chemical_informations;
            for my $col (2..7) {
                push @chemical_informations, encode('utf-8', decode('cp936', $chemicals_worksheet->get_cell($row_in_chemicals_worksheet, $col)->value));
            }
            push @{$company_informations[7]}, \@chemical_informations;
            next;
        }
        last;
    }
    continue {
        $row_in_chemicals_worksheet += 1;
    }
    #print Dumper(@company_informations);
    for my $i (0..6) {
        #say $row_in_output_worksheet;
        $output_worksheet->write($row_in_output_worksheet, $i, decode('utf-8', $company_informations[$i]));
        #say $company_informations[$i];
    }
    for my $array_ref (@{$company_informations[7]}) {
        for my $i (0..5) {
            $output_worksheet->write($row_in_output_worksheet, $i+7, decode('utf-8', $array_ref->[$i]));
            #say $array_ref->[$i];
        }
        $row_in_output_worksheet += 1;
    }
}

$output_workbook->close;