public function CreateFile($data) { $format = $data["format"] == "excel" || $data["format"] == "csv" ? $data["format"] : "csv"; $PHPExcel = "PHPExcel"; if (!class_exists($PHPExcel)) { include_once plugin_dir_path(__FILE__) . 'extlibs/PHPExcel/PHPExcel.php'; } include_once plugin_dir_path(__FILE__) . 'extlibs/PHPExcel/PHPExcel/Writer/Excel2007.php'; $fields = Fields::Get($data["swichdatatype"]); $obj = new \PHPExcel(); $objProperties = $obj->getProperties(); $objActiveSheet = $obj->getActiveSheet(); $objProperties->setCreator("MakeRise"); $objProperties->setTitle("Data Exported List"); $products_with_variations = isset($data["products_with_variations"]) ? $data["products_with_variations"] : 'Off'; $orders_with_fancy = isset($data["orders_with_fancy"]) ? $data["orders_with_fancy"] : 'Off'; $filter = array("recordsstatus" => $data["recordsstatus"], "recordsauthor" => $data["recordsauthor"], "date-from" => $data["date-from"], "date-to" => $data["date-to"], "datatype" => $data["swichdatatype"], "products_with_variations" => $products_with_variations, "orders_with_fancy" => $orders_with_fancy, "order" => "ID" . " ASC"); if (isset($data["prod-cats"])) { $filter["prod-cats"] = $data["prod-cats"]; } $wpdatas = $this->GetData($filter); $obj->setActiveSheetIndex(0); $i = 0; if ($data["cols"]) { foreach ($data["cols"] as $value) { if (!in_array($value, $data["fields"])) { continue; } $a = Fields::GetAlphas($i); $f = strip_tags($fields[$value][0]); $f = str_replace("Variations: ", "", $f); $objActiveSheet->SetCellValue($a . "1", $f); $ColumnDimension = $objActiveSheet->getColumnDimension($a); $ColumnDimension->setAutoSize(true); $Style = $objActiveSheet->getStyle($a . "1"); $StyleFont = $Style->getFont(); $StyleFont->setBold(true); $r = 2; foreach ($wpdatas as $wpdata) { if (isset($wpdata[$value])) { $objActiveSheet->SetCellValue($a . $r, $wpdata[$value]); } $r++; } $i++; } } $objActiveSheet->setTitle('List'); $filetype = $format == "csv" ? ".csv" : ".xls"; $dt = new \DateTime("now"); $fname = "Data_" . $dt->format("Y-m-d_h-i-s") . "__" . uniqid() . $filetype; if ($format == "csv") { $objw = new \PHPExcel_Writer_CSV($obj); $objw->setDelimiter(';'); $objw->setEnclosure('"'); } else { $objw = new \PHPExcel_Writer_Excel2007($obj); } $files = glob(plugin_dir_path(__FILE__) . "tmp/*"); foreach ($files as $file) { $dt = new \DateTime(date("F d Y H:i:s.", filemtime($file))); $dt->modify("+1 day"); $dt_now = new \DateTime("now"); if ($dt_now > $dt && is_file($file)) { unlink($file); } } $objw->save(plugin_dir_path(__FILE__) . "tmp/" . $fname); return array("fileurl" => plugin_dir_url(__FILE__) . "tmp/" . $fname, "filename" => $fname); }
/** * @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; }
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"); } }