1 Replies - 1055 Views - Last Post: 02 November 2013 - 12:46 PM Rate Topic: -----

#1 dannybarh  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 62
  • Joined: 19-February 11

export data from mysql to excel/csv format based on search results

Posted 02 November 2013 - 12:41 PM

am trying to export data from mysql db to excel format, but the excel file i get is damaged.
i get two varibles by $_GET, and run a search, and then creates the excel file based on the results...

          
 include "../inc/config.php"; 
                 $p_na = $_GET['p_na'];
		 $svreg = $_GET['svreg'];

		include_once("classes/PHPExcel.php"); 
		$result = mysql_query("SELECT * FROM history WHERE  p_na LIKE '$p_na%' AND svreg LIKE '$svreg%' ");  //select query
		// Execute the database query
		//$result = mysql_query($query) or die(mysql_error());
							
		// Instantiate a new PHPExcel object
		$objPHPExcel = new PHPExcel(); 
		// Set the active Excel worksheet to sheet 0
		$objPHPExcel->setActiveSheetIndex(0); 
		// Initialise the Excel row number
		$rowCount = 1; 
		// Iterate through each result from the SQL query in turn
		// We fetch each database result row into $row in turn
		while($row = mysql_fetch_array($result)){ 
		// Set cell An to the "name" column from the database (assuming you have a column called name)
		// where n is the Excel row number (ie cell A1 in the first row)
		$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $row['p_na']); 
		// Set cell Bn to the "age" column from the database (assuming you have a column called age)
		// where n is the Excel row number (ie cell A1 in the first row)
		$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['svreg']); 
		// Increment the Excel row counter
			$rowCount++; 
				      } 
							
			// Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file
			$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
			// Write the Excel file to filename some_excel_file.xlsx in the current directory
			$objWriter->save('some_excel_file.xlsx'); 
				
			//header to make force download the file
			header("Content-Disposition: attachment; filename=Export_Excel".date('d-m-Y').".csv");
			print $contents;	

This post has been edited by dannybarh: 02 November 2013 - 01:11 PM


Is This A Good Question/Topic? 0
  • +

Replies To: export data from mysql to excel/csv format based on search results

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3717
  • View blog
  • Posts: 5,979
  • Joined: 08-June 10

Re: export data from mysql to excel/csv format based on search results

Posted 02 November 2013 - 12:46 PM

You are printing a $contents variable. Where does that come from? I don't see it defined anywhere.

Since you are apparently saving the file on the server hard drive first, you may want to consider using the readfile function to print it to the output.

Also, instead of creating an actual Excel sheet, have you considered using a more neutral form, like CSV? Excel can read that format fine, as can all other spreadsheet applications. - I can't see the format of your current Excel sheet being too complicated for CSV, so it shouldn't really matter.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1