Работа с XLSX ("новый" Эксель)

04.12.2015

Примеры работы с файлами эксель (excel) используя PHPExcel

Общее

Есть статья по работе с новой библиотекой: Работа с XLSX через обновленную библиотеку (phpoffice/phpspreadsheet)

Страница библиотеки на packagist: phpoffice/phpexcel

Установка

Для начала нужно устанавливать composer

Затем проинструктировать пакет через консоль:

 
composer require phpoffice/phpexcel

Полезное:

Получение даты из соответствующей ячейки:

 
            $oTMP->dEnd = PHPExcel_Shared_Date::ExcelToPHP($oTMP->dEnd);
            $oTMP->dEnd = date('d.m.Y', $oTMP->dEnd);

Примеры

Создание и запись в файл

Create_and_Write.php (Download)

 <?php

  error_reporting(E_ALL);
  ini_set('display_errors', 'On');

  require_once('vendor/autoload.php');

  $sOutFile = 'all.xlsx';
   
  $objPHPExcel = new PHPExcel();
  $objPHPExcel->setActiveSheetIndex(0)->setTitle("Отчет");
   
  $iLine = 1;
  for($i = 0; $i < 10; $i++)
  {

      $FL = 'A'; // First Leter
       
      // Записываем в файл
      $objPHPExcel->getActiveSheet()
        ->setCellValue(($FL++).$iLine, $i)
        ->setCellValue(($FL++).$iLine, $i*10)
      ;
       
      $iLine++;

  }
 
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  $objWriter->save($sOutFile);

Чтение данных

for ($i = 2; $i <= $oActiveSheet->getHighestRow(); $i++)

Read.php (Download)

 <?php

  error_reporting(E_ALL);
  ini_set('display_errors', 'On');

  require_once('vendor/autoload.php');

  $sInFile = 'all.xlsx';

  $oExcel = PHPExcel_IOFactory::load($sInFile);

 
  $aRet = array();

  for ($i = 2; $i <= 20; $i++)  // 2 - пропускаем первую строку (Нумерация с 1)
  {
    $aRet[$i]['c1:'] = $oExcel->getActiveSheet()->getCell ( 'A' . $i )->getValue();
    $aRet[$i]['c2:'] = $oExcel->getActiveSheet()->getCell ( 'B' . $i )->getValue();
  }
  
  echo '<pre>';
  print_r($aRet);
  echo '</pre>';

Еще примеры:

Создаем файл, пишем в него данные, сохраняем. Стоит обратить внимание на то, что я заменил A, B, C на "фишку" :-)

 
/**
* Генерирует отчет со всеми пользователями и их кредитами в XLSX
* Возвращает путь до файла
* 
* @return string
*/
public function exportAll2xlsx()
{
  // Init
  $db = JFactory::getDBO();
  $sOutFile = '/_files/all.xlsx';
  
  // PHPExcel
  require_once (JPATH_ROOT.'/so/ext/PHPExcel/Classes/PHPExcel.php');
  $objPHPExcel = new PHPExcel();
  $objPHPExcel->setActiveSheetIndex(0)->setTitle("Отчет");
  
  // Получение всех клиентов
  $query = $db->getQuery(true);
  $query->select('*')
  		->from('sojos_users AS a')
  		->where(' a.id > 772')
  ;
  $db->setQuery($query);
  $aRows = $db->loadObjectList();

  $iLine = 1;
  foreach($aRows as $oUser)
  {
	{
      $FL = 'A'; // First Leter
	  
	  // Записываем в файл
      $objPHPExcel->getActiveSheet()
        ->setCellValue(($FL++).$iLine, $oUser->id)
        ->setCellValue(($FL++).$iLine, $oUser->last_name)
        ->setCellValue(($FL++).$iLine, $oUser->gc_name)
        ->setCellValue(($FL++).$iLine, $oUser->middle_name)
        ->setCellValue(($FL++).$iLine, $oUser->date_of_birth)
        ->setCellValue(($FL++).$iLine, $oUser->place_of_birth)
        ->setCellValue(($FL++).$iLine, $oUser->passport)
        ->setCellValue(($FL++).$iLine, $oUser->issued_by)
        ->setCellValue(($FL++).$iLine, $oUser->date_of_issue)
        ->setCellValue(($FL++).$iLine, $oUser->code_division)
      ;
      
      $iLine++;
	}

  }

  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  $objWriter->save(JPATH_ROOT.$sOutFile);


  
  return $sOutFile;
}

Вставляем картинку в файл и сохраняем

InsertPic.php (Download)

 <?
  echo '<?xml version="1.0" encoding="utf-8"?>';
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

  <title>Генератор</title>

</head>

<body>
<?
  $BabsPath = dirname(__FILE__);

  date_default_timezone_set('Europe/London');

/** PHPExcel */
  require_once $BabsPath.'/../../Lib/xls/PHPExcel.php';

  $objPHPExcel = new PHPExcel();
  $objPHPExcel->getActiveSheet()->setTitle("My Sheet"); 

  $sPath = dirname(__FILE__);
  $sGetFile = $sPath.'/input.xls';
  $sPutFile = $sPath.'/output.xlsx';

  $gdImage = imagecreatefromjpeg('pic.jpg');
  // Add a drawing to the worksheetecho date('H:i:s') . " Add a drawing to the worksheet\n";
  $objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
  $objDrawing->setName('Sample image');$objDrawing->setDescription('Sample image');
  $objDrawing->setImageResource($gdImage);
  $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
  $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
  $objDrawing->setHeight(150);
  $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  $objWriter->save($sPutFile);



?>

<div>
  <b>Работа завершена</b>
</div>

</body>

</html>

Открываем файл, перебераем все емейлы и сохраняем в базу

DoIt.php (Download)

 <?
  $sPath = dirname(__FILE__);

    /**
     * Text
     *
     * @param mixed $OBJ
     * @return object
     */
    function conv_obj_UTF_2_1251(&$OBJ)
    {
      if (is_object($OBJ))
      {
        $class_vars = get_object_vars($OBJ);
        foreach ($class_vars as $name => $value)
        {
          if (is_string($value))
          {
            $val = '';
            try
            {
              @$val = iconv("UTF-8", "windows-1251", $value);
            }
            catch (Exception $e)
            {
              echo 'Выброшено исключение: ',  $e->getMessage(), "\n";
            }

            if ($val)
            {
              $OBJ->$name = $val;
//              echo 'ok<hr>';
            }
          }
        }
      }
      else
      {
        $OBJ = iconv("UTF-8", "windows-1251", $OBJ);
      }

      return $OBJ;
    }

  function changeEmailDelimeter($sText)
  {
    return str_replace(';', ',', $sText);
  }

  require_once($sPath.'/lib/PDO/classes/database.php');
  require_once($sPath.'/lib/PDO/classes/table.php');

/*
  $DB = c_database::init(array(
     'host'     => 'mysql52.1gb.ru',
     'user'     => 'gb_profs_mail',
     'password' => '1051f5f312',
     'database' => 'gb_profs_mail',
     'port'     => '',
     'charset'  => 'utf8',
     'database_type' => 'mysql'
  ));
*/

  $DB = c_database::init(array(
     'host'     => 'localhost',
     'user'     => 'root',
     'password' => '',
     'database' => 'db_test',
     'port'     => '',
     'database_type' => 'mysql'
  ));


  class tbl_items extends c_table
  {
    var $id						= null;
    var $fio					= null;
    var $title					= null;
    var $type					= null;
    var $district				= null;
    var $address				= null;
    var $telephone				= null;
    var $site					= null;
    var $email					= null;

    public function __construct()
    {
      global $DB;
      $this->construct('jinm_somailer_recipient', 'id', $DB);
    }

  }


  date_default_timezone_set('Europe/Moscow');
  require_once ($sPath.'/lib/xls/PHPExcel.php');

  $oExcel = PHPExcel_IOFactory::load($sPath . '/input/IN.xlsx');


  // Brgin
  define('_LastLine', 392);

  for ($i=2; $i <= _LastLine; $i++)  // 2 - пропускаем первую строку (Нумерация с 1)
  {
     $oRow = new tbl_items();
       $oRow->id = 0;
       $oRow->title = $oExcel->getActiveSheet()->getCell ( 'A' . $i )->getValue(); // Сокращенное наименование образовательного учреждения
       $oRow->type = $oExcel->getActiveSheet()->getCell ( 'B' . $i )->getValue(); // Тип образовательного учреждения
       $oRow->email = changeEmailDelimeter($oExcel->getActiveSheet()->getCell ( 'C' . $i )->getValue()); // Электронная почта
       $oRow->district = $oExcel->getActiveSheet()->getCell ( 'D' . $i )->getValue(); // Район
       $oRow->address = $oExcel->getActiveSheet()->getCell ( 'E' . $i )->getValue(); // Адрес Образовательного учреждения
       $oRow->fio = $oExcel->getActiveSheet()->getCell ( 'F' . $i )->getValue(); // Ф.И.О.
       $oRow->telephone = $oExcel->getActiveSheet()->getCell ( 'G' . $i )->getValue(); // Контактный телефон председателя ППО (моб./раб.)
       $oRow->site = $oExcel->getActiveSheet()->getCell ( 'H' . $i )->getValue(); // Интернет адрес образовательного учреждения

     conv_obj_UTF_2_1251($oRow);

     $oRow->save();

//     echo $oRow->title.'<br>';
  }

?>

Ошибки

Multibyte function overloading in PHP must be disabled for string functions (2) столкнулся с ней на мастер хосте. Смена через хостинг панель не помогла, обратился в суппорт - сделали

Категории: PHP