Member Avatar for pabz_sarquilla

Hi -

Can someone please help me?
I have a html table generated by php mysql and have a header to download it to excel.
My problem is, I have this cell/table data that displays .10 in html and when I exported it in excel it displays as 0.1. The proper output should be the ones displayed in html table, and they don't allow me to add a single quote or anything to change the value to string.
Is there anyway special php formatting I need to know before downloading it to excel?
Can anyone guide me on these please?

Thanks,
Pabz

  • 2 Contributors
  • 4 Replies
  • 2K Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by pabz_sarquilla

Dani AI

Generated

Nice catch by on using the Excel text mask to preserve the literal value. If you need these cells to stay numeric (so formulas work) but always show two decimals, tell Excel’s HTML importer the exact number format. For example, apply a custom number format to the cell(s) you export:

<td style="mso-number-format:'0.00'">0.1</td>  <!-- renders 0.10 -->

Excel understands these Office-only HTML formats and will keep the value numeric while displaying the fixed decimals. (docs.telerik.com)

If you truly must display .10 (no leading 0) and still keep the value numeric, use a format with only # placeholders to the left of the decimal:

<td style="mso-number-format:'#.00'">0.1</td>  <!-- renders .10 -->

In Excel custom formats, 0 forces a digit, whereas # shows a digit only if one exists; using # before the decimal omits the leading zero. (That behavior follows Excel’s number-format placeholder rules; applying it via mso-number-format in HTML uses the same engine.) (support.microsoft.com)

Longer term, consider exporting a real .xlsx and setting the number format explicitly in code. With PhpSpreadsheet (the modern successor to PHPExcel), you can do:

$sheet->setCellValue('A1', 0.1);
$sheet->getStyle('A1')->getNumberFormat()->setFormatCode('#.00'); // or '0.00'

This avoids HTML quirks and gives consistent results across Excel versions. (phpspreadsheet.readthedocs.io)

Note: mso-number-format is proprietary to Office HTML; browsers will ignore it (which is fine here) and validators may flag it as nonstandard.

Recommended Answers

All 4 Replies

Member Avatar for petr.pavel

Hi there,
in HTML apply this

style="mso-number-format:'\@';"

to cells with your . values or define a CSS class with this style and apply the class.

Member Avatar for pabz_sarquilla

Thanks for the reply petr.pavel.
I tried the style that you suggested and it added a single quote before the value. I ask them about this before and they prefer if theres no extra character shown.
Late yesterday, a co worker of mine gave me a link ...http://www.akbkhome.com/blog.php/View/78/78_Generating_excel_again.html. XML to excel is one of the solution but for a quicker way we can use PEAR spreadsheet excel writer..http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.php.
Hope this one could help someone else..thanks

Member Avatar for petr.pavel

I tried the style that you suggested and it added a single quote before the value.

Uhm, I find it difficult to believe that a CSS style could add an apostrophe.
Can you give me an URL with the HTML table you're trying to import? Or can you attach it here?
Petr

Member Avatar for pabz_sarquilla

Sorry petr.pavel, my bad.
I forgot to remove the single quote I've added to my code before.
And I tried you suggestion again and it works, Thanks!!!
You have saved me a lot of time...Cheers!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.