/** * Implements hook_civicrm_export(). * * Called mostly to export search results. */ function civiexportexcel_civicrm_export($exportTempTable, $headerRows, $sqlColumns, $exportMode) { $writeHeader = true; $rows = array(); $query = "SELECT * FROM {$exportTempTable}"; $dao = CRM_Core_DAO::executeQuery($query); while ($dao->fetch()) { $row = array(); foreach ($sqlColumns as $column => $dontCare) { $row[$column] = $dao->{$column}; } $rows[] = $row; } $dao->free(); CRM_CiviExportExcel_Utils_SearchExport::export2excel2007($headerRows, $sqlColumns, $rows); }
/** * * See @CRM_Report_Utils_SearchExport::export2excel2007(). */ static function makeExcel(&$headers, &$columnTypes, &$rows) { $config = CRM_Core_Config::singleton(); $csv = ''; // Generate an array with { 0=>A, 1=>B, 2=>C, ... } $foo = array(0 => '', 1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'E'); $a = ord('A'); $cells = array(); for ($i = 0; $i < count($foo); $i++) { for ($j = 0; $j < 26; $j++) { $cells[$j + $i * 26] = $foo[$i] . chr($j + $a); } } include 'PHPExcel/Classes/PHPExcel.php'; $objPHPExcel = new PHPExcel(); // Does magic things for date cells // https://phpexcel.codeplex.com/discussions/331005 PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); // Set document properties $objPHPExcel->getProperties()->setCreator("CiviCRM")->setLastModifiedBy("CiviCRM")->setTitle(ts('Export'))->setSubject(ts('Export'))->setDescription(ts('Export')); $sheet = $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setTitle('Export'); // Add the column headers. $col = 0; $cpt = 1; foreach ($headers as $h) { try { $objPHPExcel->getActiveSheet()->setCellValue($cells[$col] . $cpt, $h); } catch (Exception $e) { die(print_r($e, 1)); } $col++; } // Add rows. $cpt = 2; // Convert the sql headers to civi types $columnHeaders = CRM_CiviExportExcel_Utils_SearchExport::sqlTypesToCivi($columnTypes); foreach ($rows as $row) { $displayRows = array(); $col = 0; foreach ($columnTypes as $k => $v) { $value = CRM_Utils_Array::value($k, $row); if (!isset($value)) { $col++; continue; } // Remove HTML, unencode entities $value = html_entity_decode(strip_tags($value)); $objPHPExcel->getActiveSheet()->setCellValue($cells[$col] . $cpt, $value); // Cell formats if (CRM_Utils_Array::value('type', $columnHeaders[$k]) & CRM_Utils_Type::T_DATE) { $objPHPExcel->getActiveSheet()->getStyle($cells[$col] . $cpt)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD); // Set autosize on date columns. // We only do it for dates because we know they have a fixed width, unlike strings. // For eco-friendlyness, this should only be done once, perhaps when processing the headers initially $objPHPExcel->getActiveSheet()->getColumnDimension($cells[$col])->setAutoSize(true); } elseif (CRM_Utils_Array::value('type', $columnHeaders[$k]) & CRM_Utils_Type::T_MONEY) { $objPHPExcel->getActiveSheet()->getStyle($cells[$col])->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL); } $col++; } $cpt++; } $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); return ''; // FIXME }