/**
* @return PHPExcel_Writer_IWriter
*/
protected function createWriter()
{
$writer = new \PHPExcel_Writer_CSV($this->convertToDriver($this->getExportable()));
if ($this->getType() == 'CSV') {
$writer->setDelimiter($this->getExportable()->getDelimiter());
$writer->setEnclosure($this->getExportable()->getEnclosure());
$writer->setLineEnding($this->getExportable()->getLineEnding());
}
return $writer;
}
public function run($args)
{
Yii::import('application.components.PHPExcel.PHPExcel.Reader.PHPExcel_Reader_CSV');
Yii::import('application.components.PHPExcel.PHPExcel.Writer.PHPExcel_Writer_CSV');
Yii::import('application.modules.store.models');
$attributesInputFileName = Yii::getPathOfAlias('application.components.spreadsheetReader.translates') . '/' . 'site_store_attribute.csv';
$objReader = new PHPExcel_Reader_CSV();
$objPHPExcel = $objReader->load($attributesInputFileName);
$db = \Yii::app()->db;
$attributes = $db->createCommand()->select('id, title_ru')->from('site_store_attribute')->queryAll();
$attributesData = [];
foreach ($attributes as $attribute) {
$attributesData[] = ['id' => $attribute['id'], 'title_ru' => $attribute['title_ru']];
}
$attributesRow = 1;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'ID')->setCellValue('B1', 'Название');
$attributesRow++;
foreach ($attributesData as $value) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . $attributesRow, $value['id'])->setCellValue('B' . $attributesRow, $value['title_ru']);
$attributesRow++;
}
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$objWriter->setDelimiter(';');
$objWriter->setLineEnding("\r\n");
$objWriter->setSheetIndex(0);
$objWriter->setUseBOM(true);
$objWriter->save($attributesInputFileName);
$optionsInputFileName = Yii::getPathOfAlias('application.components.spreadsheetReader.translates') . '/' . 'site_store_attribute_option.csv';
$objReaderOptions = new PHPExcel_Reader_CSV();
$objPHPExcelOptions = $objReaderOptions->load($optionsInputFileName);
$attributesOptions = $db->createCommand()->select('id, value_ru')->from('site_store_attribute_option')->queryAll();
$attributesOptionsData = [];
foreach ($attributesOptions as $attributeOption) {
$attributesOptionsData[] = ['id' => $attributeOption['id'], 'value_ru' => $attributeOption['value_ru']];
}
$attributesOptionsRow = 1;
$objPHPExcelOptions->setActiveSheetIndex(0)->setCellValue('A1', 'ID')->setCellValue('B1', 'Название');
$attributesOptionsRow++;
foreach ($attributesOptionsData as $value) {
$objPHPExcelOptions->getActiveSheet()->setCellValue('A' . $attributesOptionsRow, $value['id'])->setCellValue('B' . $attributesOptionsRow, $value['value_ru']);
$attributesOptionsRow++;
}
$objWriterOptions = new PHPExcel_Writer_CSV($objPHPExcelOptions);
$objWriterOptions->setDelimiter(';');
$objWriterOptions->setLineEnding("\r\n");
$objWriterOptions->setSheetIndex(0);
$objWriterOptions->setUseBOM(true);
$objWriterOptions->save($optionsInputFileName);
}
/**
* Create an csv file for data export using PHPExcel library.
*
* @return void
*/
public function toCsvAction()
{
if (empty($this->filename)) {
throw new Exception('You must define $this->filename for the output filename');
}
if (empty($this->select)) {
throw new Exception('You must define $this->select a select statement');
}
if (empty($this->fields)) {
throw new Exception('You must define $this->fields as an array with all the fields');
}
$this->disableLayout();
$this->disableView();
if ($this->select) {
if ($this->filters) {
$filters = $this->filters;
foreach ($filters as $key => $filter) {
$filter_val = $this->_getParam($key);
if (!empty($filter_val)) {
$this->select->where("{$filter['associatedTo']} = ?", $filter_val);
}
}
}
if ($this->_getParam('order')) {
if (in_array($this->_getParam('order'), array_keys($this->fields))) {
$direction = 'ASC';
if (in_array($this->_getParam('order-direction'), array('ASC', 'DESC'))) {
$direction = $this->_getParam('order-direction');
}
$this->select->order("{$this->_getParam('order')} {$direction}");
}
}
$searchfor = $this->_getParam('searchfor');
if ($searchfor) {
$searching_on = array();
$search_keywords = explode(' ', $searchfor);
foreach ($this->tables as $table => $columns) {
foreach ($columns as $column) {
array_push($searching_on, $this->_db->quoteInto("{$table}.{$column} LIKE ?", "%{$searchfor}%"));
foreach ($search_keywords as $keyword) {
array_push($searching_on, $this->_db->quoteInto("{$table}.{$column} LIKE ?", "%{$keyword}%"));
}
}
}
if (!empty($searching_on)) {
$this->select->where(implode(' OR ', $searching_on));
}
}
$results = $this->_db->fetchAll($this->select);
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$column = 0;
$key = 1;
// Insert columns label, if needed set $key = 2
if ($this->addColumnsLabel) {
foreach ($this->fields as $field_name => $field_value) {
if (is_array($field_value)) {
$label = !empty($field_value['label']) ? $field_value['label'] : $this->view->getCibleText("list_column_{$field_name}");
} else {
$label = trim($field_value);
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, 1, $label);
$column++;
}
$key = 2;
}
foreach ($results as $value) {
foreach (array_keys($this->fields) as $i => $field_value) {
if (isset($value[$field_value])) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, $key, trim($value[$field_value]));
}
}
$key++;
}
// load the appropriate IO Factory writer
switch ($this->type) {
case 'Excel5':
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
// output the appropriate headers
header("Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment;filename={$this->filename}");
// output the file
$objWriter->save('php://output');
break;
case 'CSV':
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$objWriter->setDelimiter(';');
$objWriter->setLineEnding("\r\n");
// Save file on the server
if ($this->_exportFilesFolder) {
$objWriter->save($this->_exportFilesFolder . $this->filename);
} else {
$objWriter->save('php://output');
}
break;
default:
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
// output the appropriate headers
header("Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment;filename={$this->filename}");
// output the file
$objWriter->save('php://output');
break;
}
}
}
function generateBook($book, $format) {
$this->book= $book;
if (!$book->bookId)
$bookName= "spreadsheet-1";
else
$bookName= "spreadsheet-$book->bookId";
$filename= "default-".rand(1,9999);
/*SET SPREADSHEET PROPERTIES*/
if ($format!= "ods"){
$this->objPHPExcel = new PHPExcel();
$this->objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
$this->objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
$this->objPHPExcel->getProperties()->setTitle("Test Document");
$this->objPHPExcel->getProperties()->setSubject("Test Document");
$this->objPHPExcel->getProperties()->setDescription("Test document generated using PHP classes.");
$this->objPHPExcel->getProperties()->setKeywords("office php");
$this->objPHPExcel->getProperties()->setCategory("Test result file");
}
else{
$this->objPHPOds= new PHPOds(); //create a new ods file
}
/*GENERATE THE SHEETS*/
$this->_generateSheets($format);
global $cnf;
$currentDir= $cnf['path']['Temp']."/"; // Get the Storage Folder
switch($format){
case "ods":
saveOds($this->objPHPOds,"$filename.$format"); //save the object to a ods file
break;
case "pdf":
$objWriter1 = new PHPExcel_Writer_PDF($this->objPHPExcel);
$objWriter1->writeAllSheets();
$objWriter1->setTempDir($currentDir);
$objWriter1->save("$filename.$format"); //save the object to a pdf file
break;
case "xls":
$objWriter2 = new PHPExcel_Writer_Excel5($this->objPHPExcel);
$objWriter2->setTempDir($currentDir);
$objWriter2->save("$filename.$format"); //save the object to a xls file
break;
case "xlsx":
$objWriter3 = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel2007');
$objWriter3->save($currentDir."$filename.$format"); //save the object to a xlsx file
break;
case "csv":
$objWriter4 = new PHPExcel_Writer_CSV($this->objPHPExcel);
//$objWriter4->setTempDir($currentDir);
$objWriter4->setDelimiter(';');
$objWriter4->setEnclosure('');
$objWriter4->setLineEnding("\r\n");
$objWriter4->save("$filename.$format"); //save the object to a CSV file
break;
case "html":
$objWriter5 = new PHPExcel_Writer_HTML($this->objPHPExcel);
$objWriter5->writeAllSheets();
//$objWriter5->setTempDir($currentDir);
$objWriter5->save("$filename.$format"); //save the object to a HTML file
break;
}
if ($format != "ods")
$this->_send("$filename.$format", $format, $bookName);
}
/**
* Attempts to save the workbook or file.
* It will attempt to save in the original file format
* but if not it will default to xlsx
* @throws \Exception
* @throws \PHPExcel_Reader_Exception
*/
public function saveBook()
{
switch ($this->fileExtension) {
case 'xlsx':
$save = \PHPExcel_IOFactory::createWriter($this->workbook, 'Excel2007');
break;
case 'xlsm':
$save = \PHPExcel_IOFactory::createWriter($this->workbook, 'Excel2007');
break;
case 'xltx':
$save = \PHPExcel_IOFactory::createWriter($this->workbook, 'Excel2007');
break;
case 'xltm':
$save = \PHPExcel_IOFactory::createWriter($this->workbook, 'Excel2007');
break;
case 'xls':
$save = \PHPExcel_IOFactory::createWriter($this->workbook, 'Excel5');
break;
case 'xlt':
$save = \PHPExcel_IOFactory::createWriter($this->workbook, 'Excel5');
break;
case 'ods':
$save = \PHPExcel_IOFactory::createWriter($this->workbook, 'Excel2007');
break;
case 'ots':
$save = \PHPExcel_IOFactory::createWriter($this->workbook, 'Excel2007');
break;
case 'xml':
$save = \PHPExcel_IOFactory::createWriter($this->workbook, 'Excel2007');
break;
case 'txt':
case 'csv':
$save = new \PHPExcel_Writer_CSV($this->workbook);
$save->setDelimiter($this->fileInfo['delimiter']);
$save->setEnclosure($this->fileInfo['enclosure']);
$save->setLineEnding($this->fileInfo['lineEnding']);
$save->setSheetIndex(0);
break;
}
if (isset($save)) {
if ($this->fileExtension == 'ods' || $this->fileExtension == 'odt') {
$this->filePath = str_replace($this->fileExtension, 'xlsx', $this->filePath);
}
$save->save($this->getFilePath());
if ($this->fileExtension == 'txt' || $this->fileExtension == 'csv') {
// clean up extra blank rows made by PHPExcel
$data = file_get_contents($this->filePath);
$data = rtrim($data, "\t\r\n");
$data .= $this->fileInfo['lineEnding'];
file_put_contents($this->filePath, $data);
}
} else {
throw new \Exception("Unable to create PHPExcel Writer");
}
}
public function run($args)
{
Yii::import('application.components.PHPExcel.PHPExcel.Reader.PHPExcel_Reader_CSV');
Yii::import('application.components.PHPExcel.PHPExcel.Writer.PHPExcel_Writer_CSV');
Yii::import('application.modules.dictionary.models');
$inputFileType = 'CSV';
$inputFileName = Yii::getPathOfAlias('application.components.spreadsheetReader.translates') . '/' . 'site_dictionary_dictionary_data.csv';
$objReader = new PHPExcel_Reader_CSV();
$objPHPExcel = $objReader->load($inputFileName);
$db = \Yii::app()->db;
$dictionaries = $db->createCommand()->select('id, group_id, name_ru')->from('site_dictionary_dictionary_data')->queryAll();
$data = [];
$group = [];
foreach ($dictionaries as $dictionary) {
$group = $db->createCommand()->select('name_ru')->from('site_dictionary_dictionary_group')->where('id=' . $dictionary['group_id'])->queryRow();
$data[] = ['id' => $dictionary['id'], 'group_name' => $group['name_ru'], 'name_ru' => $dictionary['name_ru']];
}
$row = 1;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'ID')->setCellValue('B1', 'Название справочника')->setCellValue('C1', 'Значение');
$row++;
foreach ($data as $value) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $value['id'])->setCellValue('B' . $row, $value['group_name'])->setCellValue('C' . $row, $value['name_ru']);
$row++;
}
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$objWriter->setDelimiter(';');
$objWriter->setLineEnding("\r\n");
$objWriter->setSheetIndex(0);
$objWriter->setUseBOM(true);
$objWriter->save($inputFileName);
}
