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);
}
/**
* 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);
}
/**
* @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;
}
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');
}
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);
}
/**
* 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);
}
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);
}
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");
}
}
/**
* 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";
}
}
/**
* 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;
}
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');
}
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);
}
