Read Workbook.....Insert Values From Worksheets Into DB

Topics: Developer Forum, User Forum
Aug 28, 2012 at 3:42 PM
Edited Aug 28, 2012 at 3:43 PM

Hello,

      Currently I am trying to read a workbook, sheet for sheet, and insert the data into my DB. How would I go about getting the values from the second, third sheet ,etc.......and insert the values for them. I have written code that will read a workbook and return the values. My problem is just getting values from the workbook excluding page 1. With that said, my workbook has 6 worksheets and data from each sheet goes to different tables in my database. Furthermore, my worksheets have pre-defined sheet names if that helps. Having said all of that, I know that I have to use Insert Into in order to insert values into my DB. Any input would be greatly appreciated! Thanks in advance! Here is my code so far.... 

require_once 'PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load('.');
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    $worksheetTitle     = $worksheet->getTitle();
    $highestRow         = $worksheet->getHighestRow(); 
    $highestColumn      = $worksheet->getHighestColumn(); 
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    $nrColumns = ord($highestColumn) - 64;
    echo "<br>The worksheet ".$worksheetTitle." has ";
    echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
    echo ' and ' . $highestRow . ' row.';
    echo '<br>Data: <table border="1"><tr>';
    for ($row = 1; $row <= $highestRow; ++ $row) {
        echo '<tr>';
        for ($col = 0; $col < $highestColumnIndex; ++ $col) {
            $cell = $worksheet->getCellByColumnAndRow($col, $row);
            $val = $cell->getValue();
            $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
            echo '<td>' . $val . '<br></td>';
          }
           echo '</tr>';
        }
      echo '</table>';





Aug 24, 2013 at 12:29 PM
Edited Aug 24, 2013 at 12:34 PM
$cellValueAsString='';
                echo '<br >'.'<table>' . "\n";
                    foreach ($objPHPExcel->getWorksheetIterator() as $sheet) {
                        //print_r($sheet);
                        $highestRow =$sheet->getHighestRow(); // e.g. 10
                        //$highestColumn ='M'; // e.g 'F'
                        $highestColumn =$sheet->getHighestColumn(); // e.g 'F'
                        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5

                        for ($row = 2; $row <= $highestRow; ++$row) {
                          echo '<tr>' . "\n";
                          for ($col = 1; $col <= $highestColumnIndex; ++$col) {
                              $option=$sheet->getCellByColumnAndRow($col, $row)->getValue();
                                if ($option instanceof PHPExcel_RichText) {
                                    $elementrfs = $option->getRichTextElements();
                                    // Loop through rich text elements
                                    foreach ($elementrfs as $element) {
                                        if ($element instanceof PHPExcel_RichText_Run) {

                                            if ($element->getFont()->getSuperScript()) {
                                                $cellValueAsString .= '<sup>';
                                            } else if ($element->getFont()->getSubScript()) {
                                                $cellValueAsString .= '<sub>';
                                            }
                                        }
                                         // Convert UTF8 data to PCDATA
                                        $cellText = $element->getText();
                                        $cellValueAsString .= htmlspecialchars($cellText);
                                        if ($element instanceof PHPExcel_RichText_Run) {
                                            if ($element->getFont()->getSuperScript()) {
                                                $cellValueAsString .= '</sup>';
                                            } else if ($element->getFont()->getSubScript()) {
                                                $cellValueAsString .= '</sub>';
                                            }
                                        }
                                    }
                                      echo '<td>'.$cellValueAsString . '</td>' . "\n";
                                }
                                else
                                {
                                    echo '<td>'.$option.'</td>';
                                }
                            }
                      echo '</tr>';
                    }

                    }echo '</table>' . "\n";




I want to get superscript and subscript values in each cell value in separately. And how to validate each cell values and insert these values in mysql table.Please clearly explained with exapmples.


Thanks in advance.
Aug 24, 2013 at 6:58 PM
Edited Aug 29, 2013 at 8:38 PM
This doesn't address the superscript/subscript portion but it will give you a working example that inserts data into MySQL tables.

https://phpexcel.codeplex.com/discussions/453644
  • Christopher Mullins