Searching within XLS files - is it good approach ?

Discussion in 'Perl Misc' started by Telemach, Feb 18, 2008.

  1. Telemach

    Telemach Guest

    I'm newbie in Perl but don't give up so easily. My question is : Is it
    possible to achieve my goals browsing just a xls file ? I know it's
    good to convert to some better format but I would be happy if that
    step is not necessary.

    I have big Excel file which has over 20 worksheets.
    What I need is to search for $value1 within the whole spreadsheet and
    if found to search for $value2 within a worksheet where $value1 was
    found. I know that cells have multi line input like :

    ABCaaa
    CDEbbb
    EFGccc
    etc.

    so if $value2 = "CDE" I need Perl to get only CDEbbb and put under
    $result1

    next I want to read the col/row number of search result for $value2
    and check the cell on two colums right from this one and copy the
    content to $result2, if cell is blank I need to check
    every single one above until cell with content is found

    I'd appreciate any help about what module and commands shall be used
    or where can I find an examples or a tutorial about searching withing
    XLS files.

    Kind regards,

    - Telemach -
    Telemach, Feb 18, 2008
    #1
    1. Advertising

  2. Telemach <> writes:

    > I'm newbie in Perl but don't give up so easily. My question is : Is it
    > possible to achieve my goals browsing just a xls file ? I know it's
    > good to convert to some better format but I would be happy if that
    > step is not necessary.


    I've only used SpreadSheet::WriteExcel, which as the name implies,
    doesn't read excel files, but the documentation for that module points
    to a lot modules that do. See:

    <http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.20/lib/
    Spreadsheet/WriteExcel.pm#READING_EXCEL_FILES>

    As far as I can tell, most of those will only work on Windows.

    Even more can probably be found if you search for "excel" on
    <http://search.cpan.org/>


    --
    Joost Diepenmaat | blog: http://joost.zeekat.nl/ | work: http://zeekat.nl/
    Joost Diepenmaat, Feb 18, 2008
    #2
    1. Advertising

  3. Telemach

    Telemach Guest

    > As far as I can tell, most of those will only work on Windows.

    I need to get this working under linux


    > Even more can probably be found if you search for "excel" on
    > <http://search.cpan.org/>


    I found some but still can't get a decent documentation how to search
    and not only read.

    - Telemach -
    Telemach, Feb 18, 2008
    #3
  4. On Mon, 18 Feb 2008, Telemach wrote:
    > Is it possible to achieve my goals browsing just a xls file ?


    Try this one:
    http://search.cpan.org/~szabgab/Spreadsheet-ParseExcel-0.32/

    --
    (stephan paul) Arif Sahari Wibowo
    _____ _____ _____ _____
    /____ /____/ /____/ /____
    _____/ / / / _____/ http://www.arifsaha.com/

    Disclaimer: IANAL, IANALP, IANAMD, IANAMP, IANAAP
    my statements - if any - should be treated as such.

    Xinnian Kuaile! 新年快樂 Gongxi Facai 恭喜發財
    S P Arif Sahari Wibowo, Feb 18, 2008
    #4
  5. Telemach

    Telemach Guest

    > Try this one:http://search.cpan.org/‾szabgab/Spreadsheet-ParseExcel-0.32/

    Do you suggest making a foreach loop that will go thru all of
    worksheets matching $variable with every single cell Val ?

    I'm asking because there is no direct sub or command to make a search
    and I'm wondering if this module will be enough.

    - Telemach -
    Telemach, Feb 18, 2008
    #5
  6. Telemach

    Guest

    Telemach <> wrote:
    > > As far as I can tell, most of those will only work on Windows.

    >
    > I need to get this working under linux


    Then Spreadsheet::parseExcel is the only one I know of.

    >
    > > Even more can probably be found if you search for "excel" on
    > > <http://search.cpan.org/>

    >
    > I found some but still can't get a decent documentation how to search
    > and not only read.


    If you aren't using Windows, Excel can't do the searching for you. Do it
    in Perl.

    Read, test, read, test, read, test.

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    The costs of publication of this article were defrayed in part by the
    payment of page charges. This article must therefore be hereby marked
    advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
    this fact.
    , Feb 18, 2008
    #6
  7. Telemach

    Telemach Guest

    > Read, test, read, test, read, test.

    From what I understand there is no sub for searching and dedicated
    loop will be needed.

    - Telemach -
    Telemach, Feb 18, 2008
    #7
  8. Telemach <> wrote:
    >> Read, test, read, test, read, test.

    >
    > From what I understand there is no sub for searching and dedicated
    > loop will be needed.



    That's correct.

    Now that you know what is needed, all that's left is to write
    code that does what is needed.


    --
    Tad McClellan
    email: perl -le "print scalar reverse qq/moc.noitatibaher¥100cmdat/"
    Tad J McClellan, Feb 19, 2008
    #8
  9. On Mon, 18 Feb 2008, Telemach wrote:
    > Do you suggest making a foreach loop that will go thru all of
    > worksheets matching $variable with every single cell Val ?


    Why not? An excel spreadsheet basically is a 3D array, how else
    you find a value in a 3D array? That's not difficult to code,
    right?

    > I'm asking because there is no direct sub or command to make a
    > search


    Might as well, since giving the particular function won't give
    much advantage anyway.

    --
    (stephan paul) Arif Sahari Wibowo
    _____ _____ _____ _____
    /____ /____/ /____/ /____
    _____/ / / / _____/ http://www.arifsaha.com/

    Xinnian Kuaile! 新年快樂 Gongxi Facai 恭喜發財
    S P Arif Sahari Wibowo, Feb 19, 2008
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jim Bancroft

    Good approach to calling Urls remotely?

    Jim Bancroft, Aug 15, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    357
    =?Utf-8?B?YmlsbHI=?=
    Aug 16, 2005
  2. =?Utf-8?B?c25pY2tz?=

    How to embed XLS files within PPT in an ASP.NET app

    =?Utf-8?B?c25pY2tz?=, Jan 20, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    4,228
    Steven Cheng[MSFT]
    Jan 23, 2006
  3. George2
    Replies:
    3
    Views:
    286
    Rolf Magnus
    Dec 24, 2007
  4. Replies:
    14
    Views:
    197
    John W. Krahn
    Dec 12, 2006
  5. stumblng.tumblr
    Replies:
    1
    Views:
    198
    stumblng.tumblr
    Feb 4, 2008
Loading...

Share This Page