Data not exporting to spreadsheet

Question

Samyx 36 Junior Poster in Training

12 Years Ago

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;
?>

database-design php

2 Contributors
3 Replies
384 Views
4 Days Discussion Span
Latest Post 12 Years Ago Latest Post by Samyx

Dani AI

Generated 9 Months Ago

Quick recap and likely cause

Thanks to for the follow-up and to for the security reminder. The symptom you described — spreadsheet formatting present but data blank plus the Excel “We found a problem with some content…” repair dialog — commonly means the XLSX stream was corrupted before Excel read it (usually by extra output such as PHP warnings, stray whitespace or a UTF‑8 BOM). It can also happen when the query returns no values because the code is reading the wrong associative keys. Both problems are common when streaming Excel from PHP. (phpspreadsheet.readthedocs.io)

Short, practical checklist (what to check first)

Confirm the query result in PHP (don’t rely only on pgAdmin): test the pg_query return and check row count / pg_last_error so you know whether rows exist for the loop. Associative arrays use the column name (or an alias) as the key; using a dotted/table-qualified name will be empty. (php.net)
Prevent any extra output when streaming a binary file: disable display_errors (log errors instead), remove any echo/print/debug output, and remove trailing whitespace/closing PHP tags in included files. Any PHP warning or whitespace printed before headers/binary content will corrupt the XLSX. Use output buffering carefully (ob_clean/ob_end_clean) only to remove truly unwanted buffered output. (php.net)
Fix runtime warnings that would be printed: calling the wrong DB close function (e.g., mysqli_close on a PostgreSQL resource) triggers a runtime warning and will corrupt the file stream — use the matching pg_close or let the script end. (stackoverflow.com)

If the file still fails

Save the workbook to a temp file on disk and open that copy locally. If the saved file opens fine but the streamed file is repaired/corrupt, the problem is almost certainly extra output/BOM or headers/order of operations. If you want a more modern, supported library, migrate from PHPExcel (archived) to PhpSpreadsheet. (github.com)

Summary: verify the query in PHP, stop any printed warnings/output when you call the writer, use correct PG array keys, and use the right DB close function. Those steps will fix the blank-data / repair-dialog pattern you saw.

All 3 Replies

Taywin 312 Posting Virtuoso

12 Years Ago

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).

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Samyx 36 Junior Poster in Training · Answer 1 · 2014-08-22T15:41:17+00:00

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

Samyx 36 Junior Poster in Training · Answer 2 · 2014-08-26T22:24:18+00:00

Solved. I moved line 30 to line 7, and now it is working.

Data not exporting to spreadsheet

Dani AI

Recommended Answers Collapse Answers

All 3 Replies

Recommended Answers