Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

xlsmerge - Merge worksheets from different Excel files into one Workbook

by Corion (Pope)
on Feb 13, 2009 at 09:58 UTC ( #743574=sourcecode: print w/replies, xml ) Need Help??
Category: Utillity Scripts
Author/Contact Info /msg Corion
Description:

This script is a raw utility that I use to merge "information" and "data" sheets from different Excel workbooks. The basic syntax is as follows:

xlsmerge -s -o results-%Y%m%d.xls info.xls:Information query_results.x +ls:Sheet1

The above command line will create results-20091302.xls containing a worksheet "Information" and one worksheet "Sheet1" from the respective Excel files. There is no renaming functionality and duplicate sheet names are fatal.

#!/usr/bin/perl -w
use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use File::Glob qw(bsd_glob);
use Getopt::Long;
use POSIX qw(strftime);

GetOptions(
    'output|o=s' => \my $outfile,
    'strftime|t' => \my $do_strftime,
) or die;

if ($do_strftime) {
    $outfile = strftime $outfile, localtime;
};

my $output = Spreadsheet::WriteExcel->new($outfile)
    or die "Couldn't create '$outfile': $!";

for (@ARGV) {
    my ($filename,$sheetname,$targetname);
    my @files;
    if (m!^(.*\.xls):(.*?)(?::([\w ]+))$!) {
        ($filename,$sheetname,$targetname) = ($1,qr($2),$3);
        warn $filename;
        if ($do_strftime) {
            $filename = strftime $filename, localtime;
        };
        @files = glob $filename;
    } else {
        ($filename,$sheetname,$targetname) = ($_,qr(.*),undef);
        if ($do_strftime) {
            $filename = strftime $filename, localtime;
        };
        push @files, glob $filename;
    };

    for my $f (@files) {
        my $excel = Spreadsheet::ParseExcel::Workbook->Parse($f);
        foreach my $sheet (@{$excel->{Worksheet}}) {
            if ($sheet->{Name} !~ /$sheetname/) {
                warn "Skipping '" . $sheet->{Name} . "' (/$sheetname/)
+";
                next;
            };
            $targetname ||= $sheet->{Name};
            #warn sprintf "Copying %s to %s\n", $sheet->{Name}, $targe
+tname;

            my $s = $output->add_worksheet($targetname);
            $sheet->{MaxRow} ||= $sheet->{MinRow};
            foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
                my @rowdata = map {
                    $sheet->{Cells}->[$row]->[$_]->{Val};
                } $sheet->{MinCol} ..  $sheet->{MaxCol};
                $s->write($row,0,\@rowdata);
            }
        }
    };
};

$output->close;
  • Comment on xlsmerge - Merge worksheets from different Excel files into one Workbook
  • Download Code
Replies are listed 'Best First'.
Re: xlsmerge - Merge worksheets from different Excel files into one Workbook
by ambrus (Abbot) on Jun 15, 2010 at 13:03 UTC

    You know, if you open two workbooks in Excel and right-click on the sheet selection tab, you get a shortcut menu with a "Move/copy sheet..." entry that lets you copy sheets even among workbooks. What advantage does this script have over that method?

      The advantage of all my scripts. I don't have to do menial tasks manually, like merging boilerplate sheets into other workbooks before mailing them.

Re: xlsmerge - Merge worksheets from different Excel files into one Workbook
by Anonymous Monk on Nov 19, 2013 at 20:29 UTC
    My formulas are not coming over with the data, it is just showing 0 instead. Any ideas?

      The script only copies values, not formulas. For copying formulas, you'll have to look at how Spreadsheet::ParseExcel offers the formulas, and then copy those instead of the values.

Re: xlsmerge - Merge worksheets from different Excel files into one Workbook
by Anonymous Monk on Jun 15, 2010 at 08:43 UTC
    how to call this file

      Maybe you can suggest to me how I would write the description better:

      This script is a raw utility that I use to merge "information" and "data" sheets from different Excel workbooks. The basic syntax is as follows:
      xlsmerge -s -o results-%Y%m%d.xls info.xls:Information query_results.x +ls:Sheet1

      Maybe, if your operating system does not execute Perl programs directly, you want to use the following line:

      perl -w xlsmerge -s -o results-%Y%m%d.xls info.xls:Information query_r +esults.xls:Sheet1

      You don't tell us what you've tried and what problems you encountered, so it's hard to advise you further.

        Old thread, but wanted to share with other readers. Running command exactly as suggested created an empty results file. There is likely a bug in the script on line 24 and believe it's expecting a different format compared to what's suggested in the example by author.

        Solution is to NOT specify the sheet name after the semicolon.

        Simply run it as
        perl xlsmerge.pl -s -o results-%Y%m%d.xls File1.xls File2.xls

        I have a different question though. Is it possible to simply change the extension to csv in the source code and run this on csv files?

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: sourcecode [id://743574]
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (8)
As of 2017-04-24 03:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    I'm a fool:











    Results (433 votes). Check out past polls.