Примеры работы с файлами эксель (excel) используя PHPExcel
Работа с XLSX ("новый" Эксель)
04.12.2015
Общее
Есть статья по работе с новой библиотекой: Работа с 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) столкнулся с ней на мастер хосте. Смена через хостинг панель не помогла, обратился в суппорт - сделали