File Error:data may have been lost while giving hy

Question

I am writing a code to generate excel using Spreadsheet::WriteExcel. I want to write the data into cells and give the hyperlink to it. I did that. But while opening the file it shows warning saying file data may have been lost.

$Log_Sheet->write(12,0,'AA',$ERR_DESCR_Format );
$Log_Sheet->write_url(12, 0,  'internal:sheet3!A1'  );

I want to write the content and provide link without getting warning. How to do it?

Answer 1

Spreadsheet::WriteExcel, greatness though it is, hasn't been updated for several years, and to my knowledge it only supports the pre-Office 2007 extension of .xls. I'd really recommend you upgrade to Excel::Writer::XLSX, which is McNamara's fabulous update to the open XML standard format (xlsx).

So, I can't say for sure because this error does not happen on Excel::Writer::XLSX, but my initial theory is you are writing two different values to the exact same cell (12, 0), so the one is essentially clobbering the other -- hence the warning.

If you do upgrade to Excel-Writer, the warning should disappear. More importantly, if you want a hyperlink, you include the link and the friendly description all in the same call:

$worksheet->write_url(12, 0, 'internal:Sheet3!A1', 'Click Here!');

For what it's worth, I think the write method will also correctly interpret this as a link:

$worksheet->write(12, 0, 'internal:Sheet3!A1', 'Click Here!');

-- EDIT 12/21/2018 --

If want to do a hyperlink on a merged cell, one way to do it is to use Excel's hyperlink function:

my $merged = $workbook->add_format();
$worksheet->merge_range("A10:A11",
  qq{=hyperlink("#'Sheet3'!A1","Click Here!")},
  $merged);