/** * Excel 파일을 CSV 파일로 변환 * * @param $excelPath string excel 파일 경로 * @param $csvPath string csv 파일 경로 * @return void */ public static function convertExcelIntoCsv(string $excelPath, string $csvPath) { echo "\nConverting Excel into CSV format...\n"; try { // excel 파일을 로드하여 PHPExcel 선언 $objPhpExcel = \PHPExcel_IOFactory::load($excelPath); // Excel->CSV 형식의 Object로 변환 $objWriter = new \PHPExcel_Writer_CSV($objPhpExcel); // csv 경로에 같은 파일이 있으면 삭제 if (file_exists($csvPath)) { echo "CSV file rewriting...\n"; unlink($csvPath); } // 해당 경로에 csv 파일 저장 $objWriter->save($csvPath); echo "Conversion success! \n"; } catch (\PHPExcel_Reader_Exception $re) { die('Error loading file: ' . $e->getMessage()); } finally { // 메모리 release 작업 if ($objPhpExcel instanceof \PHPExcel_IOFactory) { $objPhpExcel->disconnectWorksheets(); unset($objPhpExcel); } unset($objWriter); } }
public function __construct($inputFilePath, XFTranslationMemory $translationMemory = null) { $objPHPExcel = PHPExcel_IOFactory::load($inputFilePath); $csvWriter = new PHPExcel_Writer_CSV($objPHPExcel); $csvOut = $inputFilePath . '.csv'; $csvWriter->setExcelCompatibility(); $csvWriter->save($csvOut); parent::__construct($csvOut, $translationMemory); }
function xlsToCsv($file_xls_input, $file_name_csv_output) { $objReader = new PHPExcel_Reader_Excel5(); try { /** Load $inputFileName to a PHPExcel Object **/ $objPHPExcel = $objReader->load($file_xls_input); return true; } catch (PHPExcel_Reader_Exception $e) { die('Error loading file: ' . $e->getMessage()); } $writer = new PHPExcel_Writer_CSV($objPHPExcel); $writer->save($file_name_csv_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); }
function array_to_spreadsheet($arr) { $CI =& get_instance(); PHPExcel_Shared_File::setUseUploadTempDirectory(true); $objPHPExcel = new PHPExcel(); //Default all cells to text $objPHPExcel->getDefaultStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); for ($k = 0; $k < count($arr); $k++) { for ($j = 0; $j < count($arr[$k]); $j++) { $objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($j, $k + 1, $arr[$k][$j]); } } if ($CI->config->item('spreadsheet_format') == 'XLSX') { $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); } else { $objWriter = new PHPExcel_Writer_CSV($objPHPExcel); } ob_start(); $objWriter->save('php://output'); $excelOutput = ob_get_clean(); return $excelOutput; }
/** * 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); }
/** * 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; } } }
/** * 生成CSV文件 */ function write_CSV($title = '', $data = '', $name = '') { $objPHPExcel = $this->_excelComm($title, $data, $name); header("Content-Type: text/csv;charset=UTF-8"); header("Content-Disposition: attachment; filename={$name}.csv"); header('Cache-Control:must-revalidate,post-check=0,pre-check=0'); header('Expires:0'); header('Pragma:public'); $objWriter = new PHPExcel_Writer_CSV($objPHPExcel, 'CSV'); $objWriter->save("php://output"); exit; }
protected function onPrint() { $cmd = $this->db->createCommand(SQL::SQL_GET_EXPORT); $cmd->bindValue(":id", $this->Request['id']); $data = $cmd->query(); $data1 = $data->read(); $objPHPExcel = new PHPExcel(); $sheet = $objPHPExcel->getActiveSheet(); $data = $this->getData(); $header = array(); if (count($data) > 0) { $column = 'A'; $line = 1; foreach ($data[0] as $k => $v) { if ($this->Request['pdf'] == 'true') { $objPHPExcel->getActiveSheet()->getStyle($column . $line)->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle($column . $line)->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_WHITE)); $objPHPExcel->getActiveSheet()->getStyle($column . $line)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle($column . $line)->getFill()->getStartColor()->setRGB('7C7C7C'); } $sheet->setCellValue($column . $line, $k); $objPHPExcel->getActiveSheet()->getColumnDimension($column)->setAutoSize(true); $column++; } $line = 3; $fill = false; foreach ($data as $d) { $column = 'A'; foreach ($d as $field) { $field = addslashes($field); $sheet->setCellValue($column . $line, $field); if ($fill && $this->Request['pdf'] == 'true') { $objPHPExcel->getActiveSheet()->getStyle($column . $line)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle($column . $line)->getFill()->getStartColor()->setRGB('D7D7D7'); } $column++; } $line++; $fill = !$fill; } } if ($this->Request['pdf'] == 'true') { header('Content-Type: application/pdf'); header('Content-Disposition: attachment;filename="' . $data1['name'] . '.pdf"'); header('Cache-Control: max-age=0'); $objPHPExcel->getActiveSheet()->setShowGridLines(false); $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF'); $objWriter->save('php://output'); exit; } else { if ($this->Request['excel'] == 'true') { header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $data1['name'] . '.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->setOffice2003Compatibility(true); $objWriter->save('php://output'); exit; } } if ($this->Request['csv'] == 'true') { header("Content-type: application/vnd.ms-excel"); header('Content-Disposition: attachment;filename="' . $data1['name'] . '.csv"'); header('Cache-Control: max-age=0'); $objWriter = new PHPExcel_Writer_CSV($objPHPExcel); $session = Prado::getApplication()->getSession(); //$objWriter->set???($session['csv_escaped']); $objWriter->setEnclosure($session['csv_enclosed']); $objWriter->setDelimiter($session['csv_terminated']); $objWriter->save('php://output'); exit; } }
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"); } }
function exportCSV($data, $excelFileName, $sheetTitle, $firstrow) { /* 实例化类 */ import('Vendor.phpExcel.PHPExcel'); $objPHPExcel = new PHPExcel(); /* 设置输出的excel文件为2007兼容格式 */ //$objWriter=new PHPExcel_Writer_Excel5($objPHPExcel);//非2007格式 $csvWriter = new PHPExcel_Writer_CSV($objPHPExcel, 'CSV'); /* 设置当前的sheet */ $objPHPExcel->setActiveSheetIndex(0); $objActSheet = $objPHPExcel->getActiveSheet(); /*设置宽度*/ $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(40); /* sheet标题 */ $objActSheet->setTitle($sheetTitle); $j = 'A'; foreach ($firstrow as $value) { $objActSheet->setCellValue($j . '1', $value); $j++; } $i = 2; foreach ($data as $value) { /* excel文件内容 */ $j = 'A'; foreach ($value as $value2) { // $value2=iconv("gbk","utf-8",$value2); $objActSheet->setCellValue($j . $i, $value2); $j++; } $i++; } /* 生成到浏览器,提供下载 */ ob_end_clean(); //清空缓存 header("Pragma: public"); header("Expires: 0"); header('Content-Type: application/vnd.ms-excel;charset=gbk'); header("Cache-Control:must-revalidate,post-check=0,pre-check=0"); header("Content-Type:application/force-download"); header("Content-Type:application/vnd.ms-execl"); header("Content-Type:application/octet-stream"); header("Content-Type:application/download"); header('Content-Disposition:attachment;filename="' . $excelFileName . '.csv"'); header("Content-Transfer-Encoding:binary"); $csvWriter->save('php://output'); }
/** * Create sheet from query and dump named file to browser. * @param $query the query. */ function writeXlsx($query) { PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); $objPHPExcel = new PHPExcel(); if (!isset($this->rowParser)) { $this->rowParser = new DefaultRowParser(); } $objPHPExcel->getProperties()->setCreator($this->creator); $objPHPExcel->getProperties()->setLastModifiedBy($this->author); $objPHPExcel->getProperties()->setTitle($this->title); $objPHPExcel->getProperties()->setSubject($this->subject); $objPHPExcel->getProperties()->setDescription($this->description); $objPHPExcel->getProperties()->setKeywords($this->keywords); $objPHPExcel->getProperties()->setCategory($this->catagory); global $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; $resultSet = $this->dbConn->Execute($query); if ($resultSet === false) { die("<br>Cannot get spreadsheet data with <pre>" . $query . "</pre> reason " . $this->dbConn->ErrorMsg() . "<br>"); } //echo $query; //$colcount = $resultSet->FieldCount(); // start writing in 3rd row, top isf for title and link. $row = 3; $this->tableHeader = $this->rowParser->parseToTableHeader($resultSet); $headCount = count($this->tableHeader); $headerStyles = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 0, 'color' => array('argb' => 'FFC0C0C0'))); for ($i = 0; $i < $headCount; $i++) { $name = $this->tableHeader[$i]; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, $row, $name); $coor = XLSWriter::cellCoordinate($i, $row); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); } $row++; // get types $this->columnTypes = $this->rowParser->parseTypes($resultSet); $XlsTypes = array(); //error_log('there are ' . count($this->columnTypes) . ' types from db =' . print_r($this->columnTypes, true), 0); for ($i = 0; $i < count($this->columnTypes); $i++) { $ftype = PHPExcel_Cell_DataType::TYPE_NUMERIC; //error_log("found type = {$this->columnTypes[$i]} for column {$i}", 0); switch ($this->columnTypes[$i]) { case 'char': case 'bpchar': case 'varchar': case 'text': case 'date': $ftype = PHPExcel_Cell_DataType::TYPE_STRING; break; case 'int2': case 'int4': case 'int8': case '_numeric': case 'numeric': case 'float8': $ftype = PHPExcel_Cell_DataType::TYPE_NUMERIC; break; default: $ftype = PHPExcel_Cell_DataType::TYPE_STRING; break; } $XlsTypes[] = $ftype; } $cellStyleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 0, 'color' => array('argb' => 'FF0000'))); $oldValue = ''; if ($this->firstWeightColumn > 0) { // add weights row $this->weigthsRow = $row; $coor = XLSWriter::cellCoordinate($this->firstWeightColumn - 1, $row); $objPHPExcel->getActiveSheet()->setCellValue($coor, 'Weights', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); $weightSum = 0; $w = 0; ${$weightLast} = count($this->weights) - 1; for (; $w < count($this->weights); $w++) { $coor = XLSWriter::cellCoordinate($this->firstWeightColumn + $w, $row); $weightSum += $this->weights[$w]; $objPHPExcel->getActiveSheet()->setCellValue($coor, $this->weights[$w], PHPExcel_Cell_DataType::TYPE_NUMERIC); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); } $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row); $wBegin = XLSWriter::cellCoordinate($this->firstWeightColumn, $row); $wEnd = XLSWriter::cellCoordinate($this->firstWeightColumn + ${$weightLast}, $row); $formula = "=SUM({$wBegin}:{$wEnd})"; $objPHPExcel->getActiveSheet()->setCellValue($coor, $formula, PHPExcel_Cell_DataType::TYPE_FORMULA); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row - 1); $objPHPExcel->getActiveSheet()->setCellValue($coor, 'Total WT', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); $row++; } while (!$resultSet->EOF) { $rowData = $this->rowParser->parse($resultSet); $headCount = count($this->tableHeader); //$resultSet->FieldCount(); $changeColor = false; if ($this->colorChangerColumn >= 0) { if ($oldValue != $rowData[$this->colorChangerColumn]) { $changeColor = true; $oldValue = $rowData[$this->colorChangerColumn]; } } else { if ($this->autoZebra) { $changeColor = true; } } if ($changeColor) { $cellStyleArray['fill']['color']['argb'] = $this->rainBow->getCurrentAsARGBString(); $this->rainBow->getNext(); } $i = 0; for (; $i < $headCount; $i++) { $value = $rowData[$i]; $coor = XLSWriter::cellCoordinate($i, $row); $xlstype = isset($XlsTypes[$i]) ? $XlsTypes[$i] : PHPExcel_Cell_DataType::TYPE_STRING; //error_log("writing cell type = {$xlstype} for column {$i}, value {$value}", 0); $objPHPExcel->getActiveSheet()->setCellValueExplicit($coor, $value, $xlstype); if ($this->columnTypes[$i] == 'date') { $objPHPExcel->getActiveSheet()->getStyle($coor)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2); } else { if ($this->columnTypes[$i] == 'time') { $objPHPExcel->getActiveSheet()->getStyle($coor)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8); } } $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($cellStyleArray); } if ($this->weightedSumsColumn >= 0) { $weightLast = count($this->weights) - 1; $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row); $wBegin = XLSWriter::cellCoordinateAbsoluteRow($this->firstWeightColumn, $this->weigthsRow); $wEnd = XLSWriter::cellCoordinateAbsoluteRow($this->firstWeightColumn + $weightLast, $this->weigthsRow); $rBegin = XLSWriter::cellCoordinate($this->firstWeightColumn, $row); $rEnd = XLSWriter::cellCoordinate($this->firstWeightColumn + $weightLast, $row); $wSumCoor = XLSWriter::cellCoordinateAbsolute($this->weightedSumsColumn, $this->weigthsRow); $formula = "=SUMPRODUCT({$wBegin}:{$wEnd},{$rBegin}:{$rEnd})/{$wSumCoor}"; $objPHPExcel->getActiveSheet()->setCellValueExplicit($coor, $formula, PHPExcel_Cell_DataType::TYPE_FORMULA); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($cellStyleArray); } $row++; $resultSet->moveNext(); } $row = 1; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $this->linkText); $objPHPExcel->getActiveSheet()->getCell('A' . $row)->getHyperlink()->setUrl($this->linkUrl); $row++; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $this->title); $objPHPExcel->getActiveSheet()->getStyle('A' . $row)->applyFromArray($headerStyles); $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($headerStyles); $rightCell1 = XLSWriter::cellCoordinate(min($headCount - 1, 10), $row); $objPHPExcel->getActiveSheet()->mergeCells('A' . $row . ':' . $rightCell1); $rightCell2 = XLSWriter::cellCoordinate(min($headCount - 1, 10), 1); $objPHPExcel->getActiveSheet()->mergeCells('A1:' . $rightCell2); // set format $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1); $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0); for ($i = 'A', $j = 0; $i <= 'Z' && $j < $headCount; $i++, $j++) { $objPHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(true); // $objPHPExcel->getActiveSheet()->getStyle($i . '2')->applyFromArray($styleArray); } PHPExcel_Calculation::getInstance()->clearCalculationCache(); PHPExcel_Calculation::getInstance()->disableCalculationCache(); PHPExcel_Calculation::getInstance()->calculate(); switch ($this->excelFormat) { case 'Excel2007': $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $this->mimeType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'; break; case 'Excel5': $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $this->mimeType = 'application/vnd.ms-excel'; break; default: $objWriter = new PHPExcel_Writer_CSV($objPHPExcel); $this->mimeType = 'text/comma-separated-values'; break; } $tempFile = tempnam('/tmp/', 'PHPEXCEL'); // '/tmp/'.$filename; $objWriter->setPreCalculateFormulas(true); $objWriter->save($tempFile); $fp = @fopen($tempFile, 'r'); if ($fp != false) { header("Content-type: " . $this->mimeType); header("Pragma: public"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Length: " . filesize($tempFile)); header("Content-Disposition: attachment; filename=\"{$this->filename}\""); fpassthru($fp); fclose($fp); $objPHPExcel->disconnectWorksheets(); unset($objPHPExcel); unlink($tempFile); exit(0); } else { echo "cannot copy file {$tempFile} to out stream\n"; } }
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); }