Ashwitha has asked for the wisdom of the Perl Monks concerning the following question:

Hi people. I have 2 excel files with data in it. I should make a consolidated excel file from the 2 files. Im able to parse the file but donot how to write back in another excel file. can u please help. The code I used for parsing is shown bellow

#!/usr/bin/perl -w
# For Spresdsheet Read/Write Operations

use Spreadsheet::ParseExcel;


my $excel_filename = $ARGV[0] or die "Must specify filename to parse.\
+n";

#$excel_filename = "sample.xls";

sub Workbook_open{
    # Excel file access in universal
    $u = new Spreadsheet::ParseExcel;
    $ubook = $u->Parse($excel_filename);
    $ubook_max_sheets = $ubook->{SheetCount};
    print "\nSheet Count = $ubook_max_sheets\n";

    foreach $index (0 .. $ubook_max_sheets - 1){
        $eSheet = $ubook->{Worksheet}[$index];
        $sheetName = $eSheet->{Name};
        print"\n\nSheet name : $sheetName\n\n";

        if(defined($eSheet->{MaxRow}) and defined($eSheet->{MaxCol})){
            $Row_max = $eSheet->{MaxRow};
            $Col_max = $eSheet->{MaxCol};
            $Row_min = $eSheet->{MinRow};
            $Col_min = $eSheet->{MinCol};
            foreach $row_num ($Row_min .. $Row_max){
                foreach $col_num ($Col_min .. $Col_max){
                    if(defined($eSheet->{Cells}[$row_num][$col_num]->V
+alue)){
                        $temp = $eSheet->{Cells}[$row_num][$col_num]->
+Value;    
                        print "$temp\t";
                    }
                }
                print "\n";
            }
        }
    }
}

&Workbook_open();

[download]

Replies are listed 'Best First'.
Re: writing after parsing
by CountZero (Bishop) on Aug 05, 2014 at 10:15 UTC
    have a look at Spreadsheet::ParseExcel::Simple and Spreadsheet::WriteExcel::Simple.

    In many cases I have found these "simple" modules to be sufficient for most (but not all) of my spreadsheet parsing and writing needs.

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

    My blog: Imperial Deltronics
[reply]
Re: writing after parsing
by Anonymous Monk on Aug 05, 2014 at 09:00 UTC
[reply]
      Thanks a lot for ur reply. Il go through the link u sent.
[reply]
Re: writing after parsing
by thanos1983 (Parson) on Aug 05, 2014 at 10:11 UTC

    Hello Ashwitha,

    As the Anonymous monk suggest it is better to go online read and find what exactly you are looking for. There are so many options to choose that can not be included in one answer.

    Although that I have never worked with spreadsheets in Perl I found Spreadsheet::ParseExcel::SaveParser from CPAN written by Douglas Wilson.

    I just created a sample.xls file exactly as the example in the tutorial shows which is able to duplicate your file and produce a new one with a new name.

    Sample of the code provided in the tutorial:

    #!/usr/bin/perl

use strict;
use warnings;

use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;


# Open an existing file with SaveParser
my $parser   = Spreadsheet::ParseExcel::SaveParser->new();
my $template = $parser->Parse('template.xls');


# Get the first worksheet.
my $worksheet = $template->worksheet(0);
my $row  = 0;
my $col  = 0;


# Overwrite the string in cell A1
$worksheet->AddCell( $row, $col, 'New string' );


# Add a new string in cell B1
$worksheet->AddCell( $row, $col + 1, 'Newer' );


# Add a new string in cell C1 with the format from cell A3.
my $cell = $worksheet->get_cell( $row + 2, $col );
my $format_number = $cell->{FormatNo};

$worksheet->AddCell( $row, $col + 2, 'Newest', $format_number );


# Write over the existing file or write a new file.
$template->SaveAs('newfile.xls');

    [download]

    There are analytic instructions in the tutorial on how the code operates.

    To make it work I had to also install Spreadsheet::WriteExcel module.

    I hope this is close to what you where looking for.

    Seeking for Perl wisdom...on the process...not there...yet!
[reply]
[d/l]
      Hello thanos1983 Thanks a lot for ur kind reply. When I try to run the above code u sent, im getting the following error. -cant call method "worksheet" on an undefined value at line 16.
[reply]

        Hello Ashwitha,

        I spend some time today trying to figure out how to combine two excel files. To be honest since I am not an expert I figure it that maybe through 6-7 modules that I tried is was not possible. Again do not take my word or experience as granted because this was my first attempt on this area.

        But yesterday I was reading about "CSV files" take a look on those. I started working with them, and I think it would be easier to solve your problem like this.

        I mean read and export your excel files into a CSV file, then combine them in one common and regenerate your excel file combined. ;)

        This is the only solution that I can imagine for your problem.

        In case that you want for fun to experiment with some code that I create to test your question I have posted under. It is nothing complicated and also have included some documentation to assist you with the explanation.

        Read more... (3 kB)

        I hope my proposed solution did confused you more. My intention was to propose an alternative solution to your problem.

        Seeking for Perl wisdom...on the process...not there...yet!
[reply]
[d/l]
Re: writing after parsing
by thanos1983 (Parson) on Aug 08, 2014 at 13:31 UTC

    Hello Ashwitha,

    Answer to your question!

    I do not know if you still follow your question, but I decided to spend some time and become more familiar with *.csv files and also answer to your question.

    Warning

    My proposed solution is modified according to the output that I see from my test *.xls files that I created. I hope that is exactly what you require, otherwise the structure is the same you need to check your syntax and modify it accordingly.

    Structure

    I have found a dynamic solution to your question, I hope that this is what you are looking for. By dynamic I mean that the input in not restricted to 2 *.xls files that you requested but it handle nth *.xls files as an input. I think this is better since you might need 2 today but tomorrow maybe you need 10 etc.

    The majority of the coding in my script was taken from Spreadsheet::ParseExcel where you can more information about it, but also the credit should go to the author Douglas Wilson.

    So the answer to your question is this sample of code:

    Update: (11/08/2014)

    Based on the improved corrections of Anonymous Monk (see comments bellow for more information).

    Read more... (4 kB)

    The code is able to read all sheets from each *.xls file, but it can not put them back to the same position. I have not looked in to yet, because I do not want to spend more time on it. I do not know if you can solve it in 5 minutes or if it is extremely difficult. I leave this up to you. I noticed though that when I am exporting an *.xls file with multiple sheets (e.g. more than 2) that the *.csvfile only contains the sheet that you are exporting not all!!!!! So I am not sure how to do it or hot script it to it. I am sure there is a way to put together all sheets again, but as I said, I do not want to spend time right now.

    I have chosen to use a specific output for the *.cvs file the same that I get if I apply save as from an excel file.

    Sample of an excel file in .cvs format.

    Item_1,Cost_1,Sold_1,Profit_1
Keyboard_1,$10.00,$16.00,$6.00
Monitor_1,$80.00,$120.00,$40.00
Mouse_1,$5.00,$7.00,$2.00
,,Total_1,$48.00

    [download]

    I have tested the code, that my *.cvs output file can be regenerated in excel file, but also I found a script that can make the transformation for me.

    I am not posting the script here, since it is not relevant with the question.

    Sample of test sample.xls file that I tested my code.

    Item_1,Cost_1,Sold_1,Profit_1
Keyboard_1,$10.00,$16.00,$6.00
Monitor_1,$80.00,$120.00,$40.00
Mouse_1,$5.00,$7.00,$2.00
,,Total_1,$48.00

    [download]

    Sample of test test.xls file, this one contains two sheets numbered as sheet 1 and sheet 2. I can not demonstrate here it looks like just import them in you excel file as two separate file, or copy and paste them to test your code.

    Item_2,Cost_2,Sold_2,Profit_2
Keyboard_2,$10.00,$16.00,$6.00
Monitor_2,$80.00,$120.00,$40.00
Mouse_2,$5.00,$7.00,$2.00
,,Total_2,$48.00

Item_3,Cost_3,Sold_3,Profit_3
Keyboard_3,$10.00,$16.00,$6.00
Monitor_3,$80.00,$120.00,$40.00
Mouse_3,$5.00,$7.00,$2.00
,,Total_3,$48.00

    [download]

    The output of the code through this particular files:

    Item_1,Cost_1,Sold_1,Profit_1
Keyboard_1,$10.00,$16.00,$6.00
Monitor_1,$80.00,$120.00,$40.00
Mouse_1,$5.00,$7.00,$2.00
,,Total_1,$48.00
Item_2,Cost_2,Sold_2,Profit_2
Keyboard_2,$10.00,$16.00,$6.00
Monitor_2,$80.00,$120.00,$40.00
Mouse_2,$5.00,$7.00,$2.00
,,Total_2,$48.00
Item_3,Cost_3,Sold_3,Profit_3
Keyboard_3,$10.00,$16.00,$6.00
Monitor_3,$80.00,$120.00,$40.00
Mouse_3,$5.00,$7.00,$2.00
,,Total_3,$48.00

    [download]

    Sorry for the long answer and post but I think it was necessary to explain everything. In case you have some problems to understand how the code operates do not hesitate to ask me. Although I would strong suggest to you to go through the CPAN link that I provide you earlier it has a lot of nice information, since you are planning to work with *.xls files I would advice you to read it again and again.

    Hope this solves your question or at least to come as close as possible to your solution.

    Seeking for Perl wisdom...on the process...not there...yet!
[reply]
[d/l]
[select]

      Hello thanos1983, since you've indicated that you're still learning, here are a few more suggestions:

      1. Although it might work, I'd again suggest not to use $_ as much as you are. For example, inside your sub extra, you're using it for at least two different things; it'd be much easier to keep track of this if you used named lexical variables instead, e.g. my $filename and my $line. $_ is fine for short pieces of code, but using it over long spans can cause problems later on when you or someone else attempts to read and modify the code.
      2. Style: Intermixing sub declarations with code makes the program flow a little hard to follow. Usually, one would put all the subs at the bottom of the file, with all the other code above.
      3. Design: Perhaps the program structure might be more clear if you split extra into two subs, one for reading the XLS and one for writing the CSV? Also, writing CSV is much better with Text::CSV.
      4. Style: Proper indentation helps the reader. See also perltidy.

      Keep up the good work learning, and the effort you are putting into your responses is admirable. (Just please make sure they stay correct, and/or are marked as "I'm still learning", so other people learning can benefit from them as much as you are.)

[reply]
[d/l]
[select]

        Hello Anonymous Monk,

        That is true I am over using the parameters ($_,@_) because I found them so useful and to be 100% honest I thought they operate faster than defining a new parameter (e.g. my $line).

        But as I said I am still learning and to be honest I am trying to reach and provide comments in most questions that I can. The reasons are two, firstly people more experienced than me can also comment on top of my comments so I can learn from them, secondly I think that more experienced people tend to forget how was it back when "they where new to programming" and usually provide a straight answer to the problem. I found my self feeling a bit wrong about it because apart from a solution to the problem it would be nice the user to actually now and understand why this is happening and why I should write my code like this.

        Well to be even more honest I am only coding almost 2 years, and I have been working with a variety of programming languages so I am not specialized in all of them. When I start working more with Perl I got impressed with the abilities. So now I am learning and trying to get my hands "dirty" as much as possible to be able to assist people with my long comments and answers. :D

        I took in consideration your comments from the question but when I finished this code was 2 days before. I was looking forward to improve the user input using see question-2. I am still working with it trying to pass it through an OO process. So I did not spend time to modify this code. I wanted to provide a sample of solution to the user so he can start working with it, because I have a few ideas to push the code more before stop working with it.

        But because it might take more time I did not modify it.

        Any way I have already wrote too much, thank you again for your assistance to my questions I hope that I will get better soon. ;)

        I am recent graduate and looking forward to get a job in the telecommunication where I will get new challenges to learn and modify my skills even further.

        Seeking for Perl wisdom...on the process...not there...yet!
[reply]
[d/l]

Back to Seekers of Perl Wisdom