public function generate($format = "Excel5", $docName = "Tabelle") { switch ($format) { case 'Excel2007': include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel2007.php'; $writer = new PHPExcel_Writer_Excel2007($this); $ext = 'xlsx'; $header = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'; //supprime le pre-calcul $writer->setPreCalculateFormulas(false); break; case 'Excel2003': include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel2007.php'; $writer = new PHPExcel_Writer_Excel2007($this); $writer->setOffice2003Compatibility(true); $ext = 'xlsx'; //supprime le pre-calcul $writer->setPreCalculateFormulas(false); break; case 'Excel5': include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel5.php'; $writer = new PHPExcel_Writer_Excel5($this); $ext = 'xls'; break; case 'CSV': include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/CSV.php'; $writer = new PHPExcel_Writer_CSV($this); $writer->setDelimiter(","); //l'op�rateur de s�paration est la virgule $writer->setSheetIndex(0); //Une seule feuille possible $ext = 'csv'; break; case 'PDF': include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/PDF.php'; $writer = new PHPExcel_Writer_PDF($this); $writer->setSheetIndex(0); //Une seule feuille possible $ext = 'pdf'; break; case 'HTML': include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/HTML.php'; $writer = new PHPExcel_Writer_HTML($this); $writer->setSheetIndex(0); //Une seule feuille possible $ext = 'html'; break; } header('Content-type:' . $header); header('Content-Disposition:inline;filename=' . $docName . '.' . $ext); $writer->save('php://output'); }
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); }
/** * test xls export * * @return void * * @todo save and test xls file (with xls reader) * @todo check metadata * @todo add note/partner checks again? */ public function testExportXls() { $excelObj = $this->_instance->generate(); // output as csv $xlswriter = new PHPExcel_Writer_CSV($excelObj); $xlswriter->setSheetIndex(1); //$xlswriter->save('php://output'); $csvFilename = tempnam(sys_get_temp_dir(), 'csvtest'); $xlswriter->save($csvFilename); $this->assertTrue(file_exists($csvFilename)); $export = file_get_contents($csvFilename); $this->assertEquals(1, preg_match("/PHPUnit/", $export), 'no name'); $this->assertEquals(1, preg_match("/Description/", $export), 'no description'); $this->assertEquals(1, preg_match('/' . preg_quote(Tinebase_Core::getUser()->accountDisplayName) . '/', $export), 'no creator'); $this->assertEquals(1, preg_match('/open/', $export), 'no leadstate'); unlink($csvFilename); }
/** * test xls export * * @return void * * @todo save and test xls file (with xls reader) * @todo check metadata * @todo add note/partner checks again? */ public function testExportXls() { $excelObj = $this->_instance->generate(); // output as csv $xlswriter = new PHPExcel_Writer_CSV($excelObj); $xlswriter->setSheetIndex(1); //$xlswriter->save('php://output'); $csvFilename = tempnam(sys_get_temp_dir(), 'csvtest'); $xlswriter->save($csvFilename); //$noteString = Tinebase_Translation::getTranslation('Tinebase')->_('created') . ' ' . Tinebase_Translation::getTranslation('Tinebase')->_('by'); $this->assertTrue(file_exists($csvFilename)); $export = file_get_contents($csvFilename); $this->assertEquals(1, preg_match("/PHPUnit/", $export), 'no name'); $this->assertEquals(1, preg_match("/Description/", $export), 'no description'); $this->assertEquals(1, preg_match('/Admin Account, Tine 2.0/', $export), 'no creator'); $this->assertEquals(1, preg_match('/open/', $export), 'no leadstate'); //$this->assertEquals(1, preg_match('/Kneschke/', $export), 'no partner'); //$this->assertEquals(1, preg_match('/' . $noteString . '/', $export), 'no note'); unlink($csvFilename); }
/** * test xls export * * @return void * * @todo save and test xls file (with xls reader) * @todo check metadata * @todo add note/partner checks again? */ public function testExportXls() { // skip tests for php7 // PHP Fatal error: 'break' not in the 'loop' or 'switch' context in /usr/local/share/tine20.git/tine20/vendor/codeplex/phpexcel/PHPExcel/Calculation/Functions.php on line 574 if (PHP_VERSION_ID >= 70000) { $this->markTestSkipped('FIXME in php7'); } $translate = Tinebase_Translation::getTranslation('Crm'); $excelObj = $this->_instance->generate(); // output as csv $xlswriter = new PHPExcel_Writer_CSV($excelObj); $xlswriter->setSheetIndex(1); //$xlswriter->save('php://output'); $csvFilename = tempnam(sys_get_temp_dir(), 'csvtest'); $xlswriter->save($csvFilename); $this->assertTrue(file_exists($csvFilename)); $export = file_get_contents($csvFilename); $this->assertEquals(1, preg_match("/PHPUnit/", $export), 'no name'); $this->assertEquals(1, preg_match("/Description/", $export), 'no description'); $this->assertEquals(1, preg_match('/' . preg_quote(Tinebase_Core::getUser()->accountDisplayName) . '/', $export), 'no creator'); $this->assertEquals(1, preg_match('/' . $translate->_('open') . '/', $export), 'no leadstate'); unlink($csvFilename); }
/** * 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); }