setUrl to file

Topics: User Forum
Aug 14, 2009 at 9:53 AM

I'd like to create a zip file containing a database dump with several files that link to each other.

For that, I tried to insert a link within an xsl file using setUrl command.

 

$objPHPExcel->getActiveSheet()->getCell("A1")->getHyperlink()->setUrl("product_documents/xxx.pdf");

 

This creates a link that doesn't work, but if I edit it in Excel, don't change anything and click ok the link has changed somehow and works.

Link before as hover displays:

Mac: file://product_documents/xxx.pdf

Win: product_documents/xxx.pdf

Link after as hover displays:

Mac: file://localhost/.../product_documents/xxx.pdf

Win: file:///C:\Users\...\product_documents/xxx.pdf

I also tried to add "file://" to the URL but this I have to remove in Excel to make it working.

 

Please help.

Developer
Aug 14, 2009 at 2:06 PM

I will try to investigate this, it will be helpful if you can send me the two versions of the file. 1. before save, 2. after save.

Send to: erik at phpexcel dot net

Developer
Aug 16, 2009 at 3:18 AM

[file received]

I was able to verify the problem. These things should hopefully be fixed now:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10459

Download source code release tomorrow:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

 

Aug 17, 2009 at 8:37 AM

Thanks for your quick help.

I've downloaded the nightly half an hour ago and tested it but I can't see any changes...
The hyperlink to local file still doesn't work in my version.

In which file are you working?

Developer
Aug 17, 2009 at 12:52 PM

Can you try to run this test and see if you get the same as me:

$excel = new PHPExcel();

$sheet = $excel->getActiveSheet();
$sheet->getCell('A1')->setValue('Test link');
$sheet->getCell('A1')->getHyperlink('Test link')->setUrl('product_documents/xxx.pdf');

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
$writer->save('write.xls');

When I inspect the hyperlink properties, the hyperlink looks ok after the fix: product_documents\xxx.pdf.

http://img299.imageshack.us/img299/655/ss20090817134718.png

http://img299.imageshack.us/img299/5691/ss20090817134731.png

>> In which file are you working?

I made minor change in Classes\PHPExcel\Writer\Excel5\Worksheet.php.

Aug 17, 2009 at 2:16 PM
Edited Aug 17, 2009 at 2:18 PM

Thanks for your test code.

The code generates this: http://img17.imageshack.us/img17/2456/bild1num.png

As you wrote, hover looks like this: http://img8.imageshack.us/img8/7089/bild2kds.png

What I expected it to look like is this: http://img195.imageshack.us/img195/2108/bild3bjn.png

I tried to add your "external:" to my url by hand but the colon is replaced by a slash.

Also the file protocol doesn't work because relative urls are illegal.

At the bottom of this posting you can find a function for writing external urls. I think you'll understand what they are talking about and where similar code in PHPExcel can be found (I don't because I've never written excel code by hand).

PHPExcel looks like two other exporters:

PHPExcel

if ($cell->hasHyperlink()) {
	$url = $cell->getHyperlink()->getUrl();

	if ( strpos($url, 'sheet://') !== false ) {
		// internal to current workbook
		$url = str_replace('sheet://', 'internal:', $url);

	} else if ( preg_match('/^(http:|https:|ftp:|mailto:)/', $url) ) {
		// URL
		// $url = $url;

	} else {
		// external (local file)
		$url = 'external:' . $url;
	}
	
	$this->_writeUrl($row, $column, $url);
}

 

phpsurveyor

 

// Match http or ftp URL
elseif (preg_match("/^[fh]tt?p:\/\//",$token)) {
	return $this->writeUrl($row, $col, $token, $format);
}
// Match mailto:
elseif (preg_match("/^mailto:/",$token)) {
	return $this->writeUrl($row, $col, $token, $format);
}
// Match internal or external sheet link
elseif (preg_match("/^(?:in|ex)ternal:/",$token)) {
	return $this->writeUrl($row, $col, $token, $format);
}
 

 

kimport

 

function _writeUrlRange($row1, $col1, $row2, $col2, $url)
{
	// Check for internal/external sheet links or default to web link
	if (preg_match('[^internal:]', $url)) {
			return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url));
	}
	if (preg_match('[^external:]', $url)) {
			return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url));
	}
	return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url));
}

 

vbcll2

 

###############################################################################
#
# _write_url_external($row1, $col1, $row2, $col2, $url, $string, $format)
#
# Write links to external directory names such as 'c:\foo.xls',
# c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
#
# Note: Excel writes some relative links with the $dir_long string. We ignore
# these cases for the sake of simpler code.
#
# See also write_url() above for a general description and return values.
#
function _write_url_external() {

    $_=func_get_args();

    # Network drives are different. We will handle them separately
    # MS/Novell network drives and shares start with \\
    if (preg_match('[^external:\\\\]', $_[4])) {
        return call_user_func_array(array(&$this, '_write_url_external_net'), $_);
    }

    $record      = 0x01B8;                       # Record identifier
    $length      = 0x00000;                      # Bytes to follow

    $row1        = $_[0];                        # Start row
    $col1        = $_[1];                        # Start column
    $row2        = $_[2];                        # End row
    $col2        = $_[3];                        # End column
    $url         = $_[4];                        # URL string
    if (isset($_[5])) {
        $str         = $_[5];                        # Alternative label
    }
    $xf          = $_[6] ? $_[6] : $this->_url_format;  # The cell format

    # Strip URL type and change Unix dir separator to Dos style (if needed)
    #
    $url            = preg_replace('[^external:]', '', $url);
    $url            = preg_replace('[/]', "\\", $url);

    # Write the visible label
    if (!isset($str)) {
        $str = preg_replace('[\#]', ' - ', $url);
    }
    $str_error   = $this->write_string($row1, $col1, $str, $xf);
    if ($str_error == -2) {
        return $str_error;
    }

    # Determine if the link is relative or absolute:
    #   relative if link contains no dir separator, "somefile.xls"
    #   relative if link starts with up-dir, "..\..\somefile.xls"
    #   otherwise, absolute
    #
    $absolute    = 0x02; # Bit mask

    if (!preg_match('[\\]', $url)) {
        $absolute    = 0x00;
    }

    if (preg_match('[^\.\.\\]', $url)) {
        $absolute    = 0x00;
    }

    # Determine if the link contains a sheet reference and change some of the
    # parameters accordingly.
    # Split the dir name and sheet name (if it exists)
    #
    list($dir_long, $sheet) = preg_split('/\#/', $url);
    $link_type           = 0x01 | $absolute;

//!!!
    if (isset($sheet)) {
        $link_type |= 0x08;
        $sheet_len  = pack("V", length($sheet) + 0x01);
        $sheet      = join("\0", split('', $sheet));
        $sheet     .= "\0\0\0";
    } else {
        $sheet_len   = '';
        $sheet       = '';
    }

    # Pack the link type
    $link_type      = pack("V", $link_type);


    # Calculate the up-level dir count e.g.. (..\..\..\ == 3)
/* TODO
    $up_count    = 0;
    $up_count++       while $dir_long =~ s[^\.\.\\][];
    $up_count       = pack("v", $up_count);
*/

    # Store the short dos dir name (null terminated)
    $dir_short   = $dir_long . "\0";

    # Store the long dir name as a wchar string (non-null terminated)
    $dir_long       = join("\0", preg_split('', $dir_long, -1, PREG_SPLIT_NO_EMPTY));
    $dir_long       = $dir_long . "\0";

    # Pack the lengths of the dir strings
    $dir_short_len = pack("V", strlen($dir_short)      );
    $dir_long_len  = pack("V", strlen($dir_long)       );
    $stream_len    = pack("V", strlen($dir_long) + 0x06);

    # Pack the undocumented parts of the hyperlink stream
    $unknown1 =pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000'       );
    $unknown2 =pack("H*",'0303000000000000C000000000000046'               );
    $unknown3 =pack("H*",'FFFFADDE000000000000000000000000000000000000000');
    $unknown4 =pack("v",  0x03                                            );

    # Pack the main data stream
    $data        = pack("vvvv", $row1, $row2, $col1, $col2) .
                      $unknown1     .
                      $link_type    .
                      $unknown2     .
                      $up_count     .
                      $dir_short_len.
                      $dir_short    .
                      $unknown3     .
                      $stream_len   .
                      $dir_long_len .
                      $unknown4     .
                      $dir_long     .
                      $sheet_len    .
                      $sheet        ;

    # Pack the header data
    $length         = strlen($data);
    $header      = pack("vv",   $record, $length);

    # Write the packed data
    $this->_append($header . $data);

    return $str_error;
}

 

 

 

 

Developer
Aug 17, 2009 at 6:29 PM

I checked the tools you are mentioning. They seem to be based on PEAR::Spreadsheet_Excel_Writer (just like PHPExcel_Writer_Excel5) so they probably share many of the same bugs.

I now see what you mean with the hover tooltip. Will re-open work item 10459 and see if we can get it fixed.
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10459

Developer
Aug 17, 2009 at 6:43 PM

There was a problem with some option flag which seemed to be important for the hover text.

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10459

Can you try with next source code release tomorrow:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Aug 19, 2009 at 8:47 AM

Thank you very much!

The link is working now as well as the hover is correct.

Thanks

Fritz