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