Clickable URL Link formatting in CSV file Download in PHP-CodePudding

I am offering the users the option to download tabular data from my PHP site webpage. Once a user clicks on the available "Download as CSV/Excel" button, the data in the HTML table on the webpage is successfully downloaded in a CSV file.

All good till here and working fine with the help of following PHP code:

header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=data.csv");
header("Pragma: no-cache");
header("Expires: 0");
...
...
...

$csvdata.="{$row['ProdName']},{$row['ProdQty']},{$row['ProdPrice']},<a href='https://example.com/page1.html'>Link</a>,https://example.com/page1.html,"."\n";
echo $csvdata."\n";

Here is a screenshot of how data in CSV file when opened in MS Excel appears:

All is great for columns A, B and C.

However, for column D (or E), I want to incorporate a CLICKABLE URL link, such that when the user opens the downloaded CSV file in MS Excel, they should be able to get a clickable link which when clicked should take them to the respective webpage on my site in the browser.

Ideally, it should appear as the "More Details" blue link as seen in cells D9 and D10 in the screenshot.

As can be seen from the PHP code above, I have tried to use <a href> for value in column D, and a direct text URL for column E, but neither is opening as clickable link when file is opened in Excel.

Any ways how to achieve it by modifying the PHP code?

CodePudding user response：

"CSV" stands for "comma-separated values" (or sometimes "character-separated values") and that's literally all it is - a bunch of values, separated by commas (or some other character). It has no formatting, no interactivity, no special features, it's just data.

If you want a spreadsheet which includes custom formatting and features, you'll need to generate some other file format, like the "Open Office XML Spreadsheet" format (".xlsx") produced by Excel. That's not going to be as easy as putting together a CSV file, but there are libraries out there to help you do it, like PhpSpreadsheet. There is an example in the manual of how to make a cell clickable using that library; adapted to your example:

$spreadsheet->getActiveSheet()->setCellValue('D2', 'More Details');
$spreadsheet->getActiveSheet()->getCell('D2')->getHyperlink()->setUrl('https://example.com/page1.html')

CodePudding user response：

Please use the HYPERLINK function for the CSV hyperlinks to work

Example (save as test.csv please) :

a, b, c, d ,"=HYPERLINK(""http://www.yahoo.com"",""See Yahoo"")"
e, f, g, h ,"=HYPERLINK(""http://www.yahoo.com"",""See Yahoo"")"

So , amend your code to use the above format such as:

<?php
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=data.csv");
header("Pragma: no-cache");
header("Expires: 0");

echo '1,2,3,"=HYPERLINK(""http://www.yahoo.com"",""See Yahoo"")"' . "," . '"=HYPERLINK(""http://www.google.com"",""Google"")"'."\n";
echo '8,7,6,"=HYPERLINK(""http://www.yahoo.com"",""See Yahoo"")"' . "," . '"=HYPERLINK(""http://www.google.com"",""Google"")"'."\n";


?>