0

Hello Everyone,

I am trying to export data directly from the database to a downloadable Excel spreadsheet, the formatting is showing. However, the data is not displaying. Can anyone help me? Also, when opening the spreadsheet it gives me the message "We found a problem with some content in 'FileName'. Do you want us to try to recover..." See code below:

<?php 

error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

//connecting to the database
//$con = pg_connect($hostname, $database, $username, $password) or die('Sorry, could not connect to database server');
$con = pg_connect('host=localhost dbname=Openclinica user=postgres password=l4c@n!');

/** PHPExcel */
include 'C:\PHPExcel\Classes\PHPExcel.php'; 

/** PHPExcel_Writer_Excel2007 */
include 'C:\PHPExcel\Classes\PHPExcel\Writer\Excel2007.php'; 

//$result = pg_query($con, "SELECT id, name, enrollment, population from countries");
//$queryXC =pg_query($con, "SELECT fname, col1, col2, col3,col4, col5,sequences from data_review.ctms_dash_reports");
$queryPerson ="select p.fname, cv,ml,fd,hspt,f_1572 from data_review.ctms_dashboard d, data_review.ctms_personnel p where d.fname=p.fid";
$result =pg_query($con,$queryPerson);

$number = 2;
$piname = "Root";
$sitename = "Brazil";
$revexpdate= date("m-d-Y");
$drugshipdate= date('m-d-Y'); 

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();  

// Set properties
$objPHPExcel->getProperties()->setCreator("Samara Galvao");
$objPHPExcel->getProperties()->setLastModifiedBy("Samara Galvao");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Report Test");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Report Test");
$objPHPExcel->getProperties()->setDescription("Test report for Office 2007 XLSX, generated using PHP classes.");  

$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Document Number:  '. $number);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
//$objPHPExcel->getActiveSheet()->setCellValue('B3', $number);
$objPHPExcel->getActiveSheet()->setCellValue('A4', 'PI Name:   '.$piname);
$objPHPExcel->getActiveSheet()->getStyle('A4')->getFont()->setBold(true);
//$objPHPExcel->getActiveSheet()->setCellValue('B4', $piname);
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Site Name:   '.$sitename);
$objPHPExcel->getActiveSheet()->getStyle('A5')->getFont()->setBold(true);
//$objPHPExcel->getActiveSheet()->setCellValue('B5', $sitename);
$objPHPExcel->getActiveSheet()->setCellValue('A6', 'Review Expiration Date:   '.$revexpdate);
$objPHPExcel->getActiveSheet()->getStyle('A6')->getFont()->setBold(true);
//$objPHPExcel->getActiveSheet()->setCellValue('B6', $revexpdate);
$objPHPExcel->getActiveSheet()->setCellValue('A7', 'Drug Shipment Authorization:   '.$drugshipdate);
$objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setBold(true);
//$objPHPExcel->getActiveSheet()->setCellValue('B7', $drugshipdate);

$objPHPExcel->getActiveSheet()->setCellValue('A9', 'Name');
$objPHPExcel->getActiveSheet()->setCellValue('B9', 'CV');
$objPHPExcel->getActiveSheet()->setCellValue('C9', 'ML');
$objPHPExcel->getActiveSheet()->setCellValue('D9', 'FD');
$objPHPExcel->getActiveSheet()->setCellValue('E9', 'HSPT');
$objPHPExcel->getActiveSheet()->setCellValue('F9', '1572');

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('ReportTest');
$objPHPExcel->setActiveSheetIndex(0); 

$objPHPExcel->getActiveSheet()->setCellValue('B1', 'CTMS TEST REPORT');
$objPHPExcel->getActiveSheet()->setCellValue('F1', PHPExcel_Shared_Date::PHPToExcel( gmmktime(0,0,0,date('m'),date('d'),date('Y')) ));
$objPHPExcel->getActiveSheet()->getStyle('F1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);

// Set column widths
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);

// Set fonts
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

//return records
$rowNumber=10;
while ($row=pg_fetch_array($result, PGSQL_ASSOC)) {
    $col ='A';
    $name = $row['p.fname']; //Investigator Name
    $cv = $row['cv']; //Curriculum Vitae sign date
    $ml = $row['ml']; //
    $fd = $row['fd'];//
    $hspt=$row['hspt'];//
    $f_1572=$row['f_1572'];//Form 1572 sign date
    $rows = array($name,$cv,$ml,$fd, $hspt, $f_1572); //this I create custom array
    foreach($rows as $cell) {
        $objPHPExcel->getActiveSheet(0)->setCellValue($col.$rowNumber,$cell);
        $col++;
    }
    $rowNumber++;
}
//$resultXC1 = pg_query($queryPerson) or die ('Could not find total');  
/*
     For ($row=10;$resXC1= pg_fetch_array($queryPerson, PGSQL_ASSOC);$row++)
     {
         For ($col=0;$col<6;$col++)
         {  
                  $myrowperson[$row][$col]=$resXC1[$col];

        } //2nd for
     }//1st for             

*/  

// Set fills
$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getFill()->getStartColor()->setARGB('FF808080');  

// Set header and footer. When no different headers for odd/even are used, odd header is assumed.
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BReport&RPrinted on &D');
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N'); 

// Set page orientation and size
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); 

// Add a drawing to the worksheet
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('C:\webroot\images\logo.jpg');
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); 

// Set style for header row using alternative method
$objPHPExcel->getActiveSheet()->getStyle('A9:F9')->applyFromArray(
                array(
                  'font'  => array(
                               'bold'  => true
                                           ),
                               'alignment' => array(
                                               'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
                                             ),
                               'borders' => array(
                                              'top'  => array(
                                                          'style' => PHPExcel_Style_Border::BORDER_THIN
                                                           )
                                             ),
                               'fill' => array(
                                          'type'  => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                                          'rotation'   => 90,
                                          'startcolor' => array(
                                                            'argb' => 'FFA0A0A0'
                                                           ),
                                          'endcolor'   => array(
                                                           'argb' => 'FFFFFFFF'
                                                           )
                                            )
                      )
);

$objPHPExcel->getActiveSheet()->getStyle('A9')->applyFromArray(
                              array(
                                'alignment' => array(
                                                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
                                             ),
                                'borders' => array(
                                               'left'  => array(
                                                           'style' => PHPExcel_Style_Border::BORDER_THIN
                                                               )
                                                   )
                                   )
);

$objPHPExcel->getActiveSheet()->getStyle('B9')->applyFromArray(
                              array(
                                'alignment' => array(
                                                 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
                                             )
                              )
);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_end_clean();
//$objWriter->setIncludeCharts(TRUE);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__)); 

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$filedate=date('dmY');

//header('Content-Type: application/xlsx');
header('Content-Disposition: attachment;filename=RegulatoryReport'.$filedate.'.xlsx');
header('Cache-Control: max-age=0');

// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1'); 

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0 

$objWriter->save('php://output');
ob_end_clean();

mysqli_close($con);

exit;
?>
2
Contributors
3
Replies
20
Views
2 Years
Discussion Span
Last Post by Samyx
1

Before going into any thing, have you checked that you are getting the result from DB? Also, please do NOT ever post real DB username & password online. If you ever did (or on this post), you should change the username and password used in the system. Next time, you could simply post fake username & password (or simply replace it with the literal words).

0

Thank you. I am fairly new to web programming. I only tested the query directly into my pgAdmin and it returns the values I want. How can I test if the code is recognizing the query?

Thanks

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.