/** * @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); }