CODEWITHSUNDEEP
phpphpexcelxlsx
jilseego
jilseego

Reputation: 1103

How to detect formatting chars from excel file?

I am creating a small script that imports data from xlsx file into the db. I am using PHPExcel library to read the data. The problem is it doesn't seem to have a way to convert characters into their equivalent html tags. The reason behind this is, I am going to pull the saved data into a page with html tags already. So one way I can think of to solve this is to parse the text taken from a each cell by the reader and convert each character to its html tag equivalent. Here's a sample of text from the xlsx cell:

Contributing factors that increase the risk of frequent headaches:

I'd like to convert the circular bullet and line breaks into their equivalent html tags before saving to the db. How do I achieve this?

EDIT: Here's the code:

<?php

include 'PHPExcel.php';

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
//$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load("test.xlsx");
$objWorksheet = $objPHPExcel->getActiveSheet();

?>

<html>
  <head>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
  </head>
  <body>
<?php
echo '<table>' . "\n";
foreach ($objWorksheet->getRowIterator() as $row) {
  echo '<tr>' . "\n";

  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(false); // This loops all cells,
                                                     // even if it is not set.
                                                     // By default, only cells
                                                     // that are set will be
                                                     // iterated.
  foreach ($cellIterator as $cell) {
    echo '<td>' . $cell->getValue() . '</td>' . "\n";
  }

  echo '</tr>' . "\n";
}
echo '</table>' . "\n";
?>
  </body>
</html>

Upvotes: 1

Views: 576

Answers (1)

Mihai Stancu
Mihai Stancu

Reputation: 16107

htmlentities should do the trick along with nl2br:

$value = $cell->getValue();
$value = htmlentities($value);
$value = nl2br($value);

If the string is in doublequotes you might want to get rid of the quotes before applying htmlentities:

$value = $cell->getValue();
$value = trim($value, '"');
$value = htmlentities($value);
$value = nl2br($value);

htmlentities would render the bullet as &bull; so you'll get a visual glyph representing the bullet, not a ul/ol list.

Upvotes: 1

Related Questions