PERL: Split Excel Workbook to Indiv Excel files

sandeep78 · #1 (**permalink**) 09-24-2008

Hi,

I am trying to find a way to read an excel work book with multiple worksheets.
And write each worksheet into a new excel file using perl. My environment is Unix.

For example: I have an excel workbook TEST.xls and it has Sheet1, Sheet2, Sheet3 worksheets. I would like to create Sheet1.xls, Sheet2.xls .....

Any ideas are deeply appreciated.

Thanks for all your time.

ghostdog74 · #2 (**permalink**) 09-25-2008

if you have Python, you can install excel module, eg PyExcelarator

Code:

import pyExcelerator,os
book = pyExcelerator.parse_xls("test.xls")
curpath = os.path.dirname(__file__) 
for items in book:
    sheetname=items[0]
    contents=items[1]
    workbook = pyExcelerator.Workbook()
    worksheet = workbook.add_sheet("Sheet 1")     
    for key,value in contents.iteritems():
        print "key ",key, " value: ",value
        worksheet.write(key[0],key[1], value)
        workbook.save(os.path.join(curpath, sheetname+".xls"))

you can search CPAN for Perl excel modules too.

sandeep78 · #3 (**permalink**) 09-25-2008

Thank you for the response. I dont have python installed.

I checked for the excel modules on CPAN website. I managed to find the modules.

But, I am having trouble putting it together.

use strict;
use Spreadsheet::ParseExcel;
my $Excel = new Spreadsheet::ParseExcel;
my $Book = $Excel->Parse(Test.xls);
$WkS = $Book->{Worksheet}

I got to this point, where I can read the worksheet. How can I write the worksheet to a new excel file? Any ideas please.

Thank you.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
How to change cell background in excel sheet using perl	gujrathinr	Shell Programming and Scripting	0	07-10-2008 02:01 AM
Change the cell background in excel sheet using Perl	gujrathinr	UNIX for Dummies Questions & Answers	1	07-09-2008 09:37 AM
Adding columns to excel files using Perl	dolo21taf	Shell Programming and Scripting	1	02-20-2008 07:13 AM
PERL - [B]Cloning[/B] an Excel file	srinivay	Shell Programming and Scripting	5	01-11-2005 09:51 AM
Perl - Appending/Modifying Excel files	srinivay	Shell Programming and Scripting	2	01-10-2005 06:46 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by