How to read subscript/superscript/new line using PHPExcel library when importing excel file to PHP and storing in MySQL

Question

I am having trouble saving the excel file in mysql database, It contains enter(new line) inside a cell and symbols & superscripts as well. But it stores as plain text only.

$objReader = new PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader->load($inputFileName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);

then i read some data and use

reset($sheetData);

to reset the pointer. and again use foreach() loop, to add the data into an array and insert that array to mysql table. Does any of these steps remove pre-formatting (superscript/subscript/new line inside a cell and bold/italics)? and How can I put the data in the table exactly as in the excel? Edit: I am using v1.8 of PHPExcel, v5.4 of PHP and MySQL v5.6

Answer 1

include APPPATH.'/spreadsheetreader/php-excel-reader/excel_reader2.php';  
require(APPPATH.'/spreadsheetreader/SpreadsheetReader.php');
require(APPPATH.'/spreadsheetreader/SpreadsheetReader_CSV.php');
require(APPPATH.'/spreadsheetreader/SpreadsheetReader_ODS.php');
require(APPPATH.'/spreadsheetreader/SpreadsheetReader_XLS.php');
require(APPPATH.'/spreadsheetreader/SpreadsheetReader_XLSX.php');
class Dashboard extends REST_Controller
{
        public function __construct()
        {
            parent:: __construct();

            $this->load->library("PHPExcel");


        }


    public function dashboard_post()
    {


                $Reader = new SpreadsheetReader('./upload/'.$filename); 
                $totalSheet = count($Reader->sheets());
   //print_r($totalSheet);exit;
    // For Loop for all sheets 
    if($totalSheet>0)
    {
               for($i=0;$i<$totalSheet;$i++)
               {
                 $Reader->ChangeSheet($i);

                 foreach ($Reader as $Row)
                 {
                         $data=array(

                                'your table column name'=>isset($Row[1]) ? $Row[1] : '',
                                'your table column name'=>isset($Row[2]) ? $Row[2] : '',
                                'your table column name'=>isset($Row[3]) ? $Row[3] : '',
                                'your table column name'=>isset($Row[4]) ? $Row[4] : '',);
                        }

Answer 2

The toArray() method is intended to provide a simple function to get the plain text from data cells in a spreadsheet; so all "formatting" of rich text (cells that contain different styles, colours, newlines and font information for different parts of the cell content) is removed to provide that plain text.

If you want to access that style information, then you need to get the data from the individual cells yourself using the cell's getValue() method; so you'll need to write your own loop to do that for all cells in the sheet; and you'll need to decide how you're going to store things like superscripts or bold/italic/underline in your database, and parse rich-text cell data accordingly