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);
}
