|
Posted
almost 9 years
ago
by
Midlothainadair
I am trying to use PHPExcel to export reports. I have it setup so that it will pull from my MSSQL DB just fine, but it doesn't add any of the data, just garbage.
I've commented out the parts where it should be adding the data and it is still just
... [More]
posting garbage into the file.
I am doing this using PHP, so I don't know what's going on. I am not even able to open the file as is I have to change the extension from .xlsx to .xls. then I can open it. When I go to the file's info page, none of the set values from the PHP file are set in the Excel file. I have this:
$objPHPExcel->getProperties()->setCreator("Mike in IT")
->setLastModifiedBy("Mike in IT")
->setTitle($TableName)
->setSubject($TableName)
->setDescription("Report for " .$TableName. " using PHPExcel, generated using PHP classes.")
->setKeywords("office PHPExcel php " . $TableName)
->setCategory("Report Export File");
Which should set the info properties, but isn't.
Here's the whole file that I'm using for creating the Excel sheet, this is a modification of the 01simple-download-xlsx.php
query($hsql);
$rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
$CountHeadings = count($rHeadings); //Count how many columns that there will be
$tsqlHeadings = '';
$ColumnHeader = array();
for ($row = 0; $row < $CountHeadings; $row++)
{
$headings[$row] = $rHeadings[$row]["Headings"]; //fill the array of column headings for use in creating the DataTable
}
foreach($headings as $index => $columnName)
{
$ColumnHeader[] = array('db'=>$columnName,'dt'=>$index); //creates the array for creating the DataTable column headers when sent to the FilterSort.class
}
// Error reporting
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
if (PHP_SAPI == 'cli')
die('This example should only be run from a Web Browser');
// Include PHPExcel
require_once dirname(__FILE__) . './Classes/PHPExcel.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Mke in IT")
->setLastModifiedBy("Mke in IT")
->setTitle($TableName)
->setSubject($TableName)
->setDescription("Report for " .$TableName. " using PHPExcel, generated using PHP classes.")
->setKeywords("office PHPExcel php " . $TableName)
->setCategory("Report Export File");
// Add some data
$ColumnArray = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
$HeadingArray = $headings;
$primaryKey = 'id';
$table = $SQLTableName;
$request = $_POST;
$dataArray = array();
require('FilterSort.class.php');
$dataArraystr = json_encode(FilterSort::complex($request,$sqlConnect,$table,$primaryKey,$ColumnHeader));
$dataArraystr = str_replace('{"draw":0,"recordsTotal":76,"recordsFiltered":76,"data":[["','","',$dataArraystr);
$dataArraystr = str_replace('\r','',$dataArraystr);
$dataArraystr = str_replace('\n','',$dataArraystr);
$dataArraystr = str_replace('\t','',$dataArraystr);
$dataArraystr = str_replace('\"','',$dataArraystr);
$dataArraystr = str_replace('"]]}','',$dataArraystr);
$dataArraystr = str_replace('<\/div>','',$dataArraystr);
$dataArraystr = str_replace('Format([OPEN_FOR_SALES_ACT],','Format([OPEN_FOR_SALES_ACT],1)',$dataArraystr);
$dataArraystr = str_replace('"],["','::',$dataArraystr);
$dataArraystr = explode('::',$dataArraystr);
foreach($dataArraystr as $ArrayStr)
{
$dataArray[] = explode('","',$ArrayStr);
}
//$objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A2');
//$objPHPExcel->getActiveSheet()->fromArray($HeadingArray, NULL, 'A1');
//$objPHPExcel->getActiveSheet()->fromArray($_POST,NULL,'G2');
//$objPHPExcel->getActiveSheet()->setCellValue('F2','POST');
$CountOfArray = count($HeadingArray);
// Set title row bold
$objPHPExcel->getActiveSheet()->getStyle('A1:' .$ColumnArray[$CountOfArray-1]. '1')->getFont()->setBold(true);
// Set autofilter
// Always include the complete filter range!
// Excel does support setting only the caption
// row, but that's not a best practise...
$objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('SimpleTest');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="ExportAllToExcelTest.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 = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
?>
[Less]
|
