function phpexcel_import in PHPExcel 7.3

Same name and namespace in other branches

1. 8.3 phpexcel.inc \phpexcel_import()
2. 6.2 phpexcel.api.inc \phpexcel_import()
3. 6 phpexcel.api.inc \phpexcel_import()
4. 7 phpexcel.api.inc \phpexcel_import()
5. 7.2 phpexcel.inc \phpexcel_import()

Import an Excel file.

Simple API function that will load an Excel file from $path and parse it as a multidimensional array.

Parameters

string $path: The path to the Excel file. Must be readable.

boolean $keyed_by_headers = TRUE: If TRUE, will key the row array with the header values and will skip the header row. If FALSE, will contain the headers in the first row and the rows will be keyed numerically.

boolean $keyed_by_worksheet = FALSE: If TRUE, will key the data array with the worksheet names. Otherwise, it will use a numerical key.

array $custom_calls = NULL: An associative array of methods and arguments to call on the PHPExcel Reader object. For example, if you wish to load only a specific worksheet to save time, you could use:

phpexcel_import('path/to/file.xls', TRUE, TRUE, array(
  'setLoadSheetsOnly' => array(
    'My sheet',
  ),
));

Return value

array|int The parsed data as an array on success, PHPEXCEL_ERROR_LIBRARY_NOT_FOUND or PHPEXCEL_ERROR_FILE_NOT_READABLE on error.

See also

hook_phpexcel_import()

Related topics

8 calls to phpexcel_import()

PHPExcelTest::testCustomMethodCalls in tests/phpexcel.test

Test the ability to pass custom methods and arguments on import.

PHPExcelTest::testDBResultExport in tests/phpexcel.test

Test db_result export.

PHPExcelTest::testIgnoreHeaders in tests/phpexcel.test

Test "ignore_headers" option.

PHPExcelTest::testIssue1988868 in tests/phpexcel.test

A cell with a value of '0' must get exported as such.

PHPExcelTest::testIssue2501147 in tests/phpexcel.test

Empty cells must be imported as empty strings, and not shift adjacent cells to the left.

... See full list

File

./phpexcel.inc, line 382

Defines the phpexcel api functions that other modules can use.

Code

function phpexcel_import($path, $keyed_by_headers = TRUE, $keyed_by_worksheet = FALSE, $custom_calls = array()) {
  if (is_readable($path)) {
    $library = libraries_load('PHPExcel');
    if (!empty($library['loaded'])) {

      // Determine caching method.
      list($cache_method, $cache_settings) = _phpexcel_get_cache_settings();

      // Is it available ? If not, return an error.
      if (empty($cache_method)) {
        return PHPEXCEL_CACHING_METHOD_UNAVAILABLE;
      }
      PHPExcel_Settings::setCacheStorageMethod($cache_method, $cache_settings);
      $xls_reader = PHPExcel_IOFactory::createReaderForFile($path);
      $custom_calls = array(
        'setReadDataOnly' => array(
          TRUE,
        ),
      ) + $custom_calls;
      if (!empty($custom_calls)) {
        foreach ($custom_calls as $method => $args) {
          if (method_exists($xls_reader, $method)) {
            call_user_func_array(array(
              $xls_reader,
              $method,
            ), $args);
          }
        }
      }
      $xls_data = $xls_reader
        ->load($path);
      $data = array();
      $headers = array();
      $options = array(
        'path' => $path,
        'keyed_by_headers' => $keyed_by_headers,
        'keyed_by_worksheet' => $keyed_by_worksheet,
        'custom_calls' => $custom_calls,
      );
      $i = 0;
      phpexcel_invoke('import', 'full', $xls_data, $xls_reader, $options);
      foreach ($xls_data
        ->getWorksheetIterator() as $worksheet) {
        $j = 0;
        phpexcel_invoke('import', 'sheet', $worksheet, $xls_reader, $options);
        foreach ($worksheet
          ->getRowIterator() as $row) {
          if ($keyed_by_worksheet) {
            $i = $worksheet
              ->getTitle();
          }
          $k = 0;
          $cells = $row
            ->getCellIterator();
          $cells
            ->setIterateOnlyExistingCells(FALSE);
          phpexcel_invoke('import', 'row', $row, $xls_reader, $options);
          foreach ($cells as $cell) {
            $value = $cell
              ->getValue();
            $value = drupal_strlen($value) ? trim($value) : '';
            if (!$j && $keyed_by_headers) {
              $value = drupal_strlen($value) ? $value : $k;
              phpexcel_invoke('import', 'pre cell', $value, $cell, $options, $k, $j);
              $headers[$i][] = $value;
            }
            elseif ($keyed_by_headers) {
              phpexcel_invoke('import', 'pre cell', $value, $cell, $options, $k, $j);
              $data[$i][$j - 1][$headers[$i][$k]] = $value;
              phpexcel_invoke('import', 'post cell', $data[$i][$j - 1][$headers[$i][$k]], $cell, $options, $k, $j);
            }
            else {
              $col_index = $k;
              if ($cells
                ->getIterateOnlyExistingCells()) {
                $col_index = PHPExcel_Cell::columnIndexFromString($cell
                  ->getColumn()) - 1;
              }
              phpexcel_invoke('import', 'pre cell', $value, $cell, $options, $col_index, $j);
              $data[$i][$j][$col_index] = $value;
              phpexcel_invoke('import', 'post cell', $data[$i][$j][$col_index], $cell, $options, $col_index, $j);
            }
            $k++;
          }
          $j++;
        }
        if (!$keyed_by_worksheet) {
          $i++;
        }
      }

      // Free up memory.
      $xls_data
        ->disconnectWorksheets();
      unset($xls_data);
      return $data;
    }
    else {
      watchdog('phpexcel', "Couldn't find the PHPExcel library. Excel import aborted.", array(), WATCHDOG_ERROR);
      return PHPEXCEL_ERROR_LIBRARY_NOT_FOUND;
    }
  }
  else {
    watchdog('phpexcel', "The path '@path' is not readable. Excel import aborted.", array(
      '@path' => $path,
    ));
    return PHPEXCEL_ERROR_FILE_NOT_READABLE;
  }
}