Visit Our UNIX and Linux User Community


Perl : to get all the hyperlinks from the xlsx sheet(hyperlinks not visible in excel sheet directly)


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Perl : to get all the hyperlinks from the xlsx sheet(hyperlinks not visible in excel sheet directly)
# 1  
Old 03-14-2013
Perl : to get all the hyperlinks from the xlsx sheet(hyperlinks not visible in excel sheet directly)

Hi folks,

I have a requirement in perl to print all the hyperlink from the spreadsheet(xlsx).
Spreadsheet contains few lines of hyperlink data (pic attached).

P.S. Hyperlink is behind the data and not visible in excel sheet directly.

Now using perl script I need to copy the hyperlinks in seperate excel sheet.

I have browsed CPAN modules but havnt found the module that suits my requirement.

Could you please help me on this ?
Perl : to get all the hyperlinks from the xlsx sheet(hyperlinks not visible in excel sheet directly)-excelgif
# 2  
Old 03-14-2013
Force a zip tool to look at the xlsx file, for xlsx is a zip archive of many files. Many windows explorer version will open it if you rename it whatever.zip ! You need to filter the listing of the zip for usable file types. You do not want to text-filter any images! Most of the internal files are xml is text. I see vml and rels files labeled as XML by PKZIP. You will see the patterns around URLs. Almost any text tool can extract them: awk, sed, PERL. You can get a list of internal files of interest from a unzip list and tell unzip to extract and pipe them to stdout, where you filter out the URLS.
This User Gave Thanks to DGPickett For This Post:
# 3  
Old 03-15-2013
Many thanks...

Could you please explain step by step to be followed or please let me know the modules that are required to fulfil the task.

Thanks in advance...
# 4  
Old 03-15-2013
Well, I would start with man unzip and find how to get a file listing of the xlsx on stdout so I could filter out which are text-like, usually xml. Then I can use unzip to extract each of those files to stdout, where I can used sed to find and strip out the URLs I want. First look at it in pg or the like. Find the URL you know you want. There may be many URLs on a line, so you need to separate them onto different lines and dispose of non-URL lines and line bits. Something like
Code:
unzip <list_options> xxx.xlsx | pg
 
unzip <list_options> xxx.xlsx | egrep <patterns_you_like> | pg
 
unzip <list_options> xxx.xlsx | egrep <patterns_you_like> | xargs <run_only_if_input_opts> unzip <unzip_to_stdout_options> xxx.xlsx | pg
 
unzip <list_options> xxx.xlsx | egrep <patterns_you_like> | xargs <run_only_if_input_opts> unzip <unzip_to_stdout_options> xxx.xlsx | sed '<script_to_delete_separate_trim_URLs>' | pg

If you want to stay in PERL, there are unzip APIs http://perldoc.perl.org/IO/Uncompress/Unzip.html

And direct XLSX access APIs: http://search.cpan.org/~dmow/Spreads...dsheet/XLSX.pm

Last edited by DGPickett; 03-15-2013 at 12:53 PM..

Previous Thread | Next Thread
Test Your Knowledge in Computers #464
Difficulty: Medium
chrony was developed and written in Python.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Summing up the data from different excel sheet into one excel sheet

Hi Folks, Can you please advise for any script in unix such that for example , i have 3 different excel sheet at the location /ppt/gfr/exc so the name s of the excel sheet are 1excel.xslx 2excel.xslx 3excel.xslx now in these 3 different excel sheet there is lot of data for example each... (3 Replies)
Discussion started by: punpun66
3 Replies

2. Shell Programming and Scripting

Uploading excel sheet to sharepoint portal using perl

Thourgh Perl scripting, Is it possible to upload excel sheet to sharepoint portal ? If the answer is YES.. Could you please share your thoughts and required CPAN modules or any examples to proceed further? Regards, Giridhar S ---------- Post updated at 04:26 AM ---------- Previous update... (0 Replies)
Discussion started by: giridhar276
0 Replies

3. Shell Programming and Scripting

Perl Reading Excel sheet isssue

There is a perl scriptwhich will read Excel sheet and create one file(.v) . Excel sheet::: A B C D 1 cpu_dailog 2 3 4 Perl will create the file(.v) like thsi ::: assert (cpu_dailog_iso ==2) ; assert (cpu_dailog_reset ==3); assert (cpu_dailog_idle... (3 Replies)
Discussion started by: naaj_ila
3 Replies

4. Shell Programming and Scripting

Perl :Is it possible to read the excel 2007 sheet on unix machine using spredsheet::xlsx module

I have an Excel 2007 excel sheet on windows machine and using Spreadsheet::XLSX I had written a script to read the excel sheet and was successful. My requirement is I need to generate another excel sheet from the old excel 2007 sheet on unix machine. Now is it possible to read the excel... (2 Replies)
Discussion started by: giridhar276
2 Replies

5. Shell Programming and Scripting

Perl : Deleting the records in the excel sheet

I have a excel sheet with contains the records as below.. also uploaded the input excelsheet and the output excel sheet(expected output). 322mpls32.net.xyz.comBW: 44.0 M Hrly Avg (IN /... (1 Reply)
Discussion started by: giridhar276
1 Replies

6. Shell Programming and Scripting

Perl : not capturing all the data from excel sheet

Hi folks, I am working on assignment that captures all the records(2 columns one column contains names and other contain date of birth) from excel sheet stored in a directory and checks for current date and month. If it matches current date and month then the matched records are printed as... (1 Reply)
Discussion started by: giridhar276
1 Replies

7. Programming

Excel sheet modification using perl module

Hi , can any one tell me,"How to extract the same format from existing excel file to new excel file " using Spreadsheet::WriteExcel or Spreadsheet::ParseExcel module ??? Example_pgm: Below program is used to read existing excel file..In this program "my $cell = $_;" line is used to... (0 Replies)
Discussion started by: kavi.mogu
0 Replies

8. Shell Programming and Scripting

Excel sheet modification using perl module

Hi , Is there any possibility to read excel sheet in column by column order ?...Thanks in advance,........ :confused: (1 Reply)
Discussion started by: kavi.mogu
1 Replies

9. Shell Programming and Scripting

Excel sheet modification using perl module

I need to insert new column to already existing file ..can any one help me..?? (6 Replies)
Discussion started by: kavi.mogu
6 Replies

10. Shell Programming and Scripting

Excel sheet modification using perl module

Is there any possibility to move the content from one cell to another cell (Excel sheet) using perl module? (3 Replies)
Discussion started by: kavi.mogu
3 Replies

Featured Tech Videos