File format/Extension error

Topics: Developer Forum
Apr 13, 2011 at 8:07 PM

I am getting an error generated by Microsoft Excel when I try to open the file I am creating with PHPExcel. I get a pop-up that says:

"Excel cannot open the file 'capacity_report[1].xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

The headers and other file definitions are the same as another file that I already have had working, so I don't know what the problem is. When I save teh file to a folder and open it with a text editor, it contains a whole bunch of gobble-de-gook, so I know it's generating something.

Here's the code I wrote (it's a method inside a class that pulls the data from the db and sets up arrays of data that this method uses to create the display):

//CREATE EXCEL FILE WITH PHPEXCEL
	public function create_excel_cap_table(){
		$direction = array('up','down');
		$output = "";
		$i = 0;
		include('../Classes/PHPExcel.php');
		$objPHPExcel = new PHPExcel();
		//START EXCEL OUPUT
		$title = $this->level_name_selected . "XLSX document";
		$objPHPExcel->getProperties()->setCreator("David Newey")
									 ->setLastModifiedBy("David Newey")
									 ->setTitle($title)
									 ->setSubject($title)
									 ->setDescription("Office 2007 XLSX document for ". $this->level_name_selected)
									 ->setKeywords("office 2007 openxml php capacity " . $this->level_name_selected)
									 ->setCategory($this->level_name_selected);
		//SET THE ACTIVE SHEET							 
		$objPHPExcel->setActiveSheetIndex(0);
		//GET AND DISPLAY THE DATA FOR EACH DIRECTION UP AND DOWN
		while($i < count($direction)){
			$dbname = 'capacity';
			require('db_connect.php');
			$this->up_down = $direction[$i];	
			$this->get_all_weeks_pct();
			$this->get_level_names();
			$this->get_percents();
						
			if (count($this->each_week_ending) < $this->nbr_col){
				$this->nbr_col = count($this->each_week_ending);
				}
			//CREATE THE HEADER ROW
			$col = 'B';
			$this->set_row_nbr();
			$row = $this->excel_row_nbr;
			if ($this->up_down == "down"){
				$up_d = "Downstream > 75%";
				}
			elseif($this->up_down == "up"){
				$up_d = "Upstream > 75%";
				}
			$objPHPExcel->getActiveSheet()->setCellValue($col . $row, $up_d);
			$objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
			$col++;
			for($x = 0;$x<$this->nbr_col;$x++){		
				$objPHPExcel->getActiveSheet()->setCellValue($col . $row, $this->each_week_ending[$x]);
				$objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
				$col++;
				}
			//GIVE THE HEADER ROW A BACKGROUND OF LIGHT BLUE
			$last_col = $col;
			$range = 'B'.$row.':'.$last_col.$row;
			$objPHPExcel->getActiveSheet()->getStyle($range)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
															 ->getStartColor()->setARGB('FF99ccff');
			//PUT TOGETHER THE TABLE BODY												 
			$y=0;
			$n=$row+1;
			$this->excel_row_nbr = $n;
			$side_range = "B".$row.":";
			$body_range = "C".$row.":";
			$col='C';
			foreach ($this->level_names as $lev_name){
				if ($lev_name != "" && $lev_name != ""){
					if ($i == 0){
						$objPHPExcel->getActiveSheet()->setCellValue($col . $n, $lev_name);
						$objPHPExcel->getActiveSheet()->getStyle($col . $n)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
															 ->getStartColor()->setARGB('FF000000');
						}
					else{
						$objPHPExcel->getActiveSheet()->setCellValue($col . $n, $lev_name);
						$objPHPExcel->getActiveSheet()->getStyle($col . $n)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
															 ->getStartColor()->setARGB('FF99ccff');
						}
					$col++;
					$this->get_color_set(1);
					for($j=0;$j<$this->nbr_col;$j++){
						$cell_nbr = $this->set_number($this->all_pcts[$y][$j]);
						$objPHPExcel->getActiveSheet()->setCellValue($col . $n, $cell_nbr);
						$color = $this->set_excel_color($this->all_pcts[$y][$j],$this->colors);
						$objPHPExcel->getActiveSheet()->getStyle($col . $n)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
																		   ->getStartColor()->setARGB($color);
						$col++;
						}
					$n++;
					$this->excel_row_nbr++;
					$col='C';				
					}
				$y++;	
				}
			$side_range .= "B". $this->excel_row_nbr;
			$body_range .= $last_col. $this->excel_row_nbr;
			
			//SET THE STYLE FOR THE LEFT COLUMN WITH THE LEVEL NAMES
			$styleArray = array(
				'alignment' => array(
					'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
					),
				'borders'=> array(
					'outline' => array(
						'color' => array('argb' => 'FFF5F5F5'),
					),
				),
			);
			//APPLY THE STYLE
			$objPHPExcel->getActiveSheet()->getStyle($side_range)->applyFromArray($styleArray);	
			//SET THE STYLE FOR THE REST OF THE TABLE
			$styleArray = array(
				'alignment' => array(
					'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
					),
				'borders'=> array(
					'outline' => array(
						'color' => array('argb' => 'FFF5F5F5'),
					),
				),
			);
			//APPLY THE STYLE
			$objPHPExcel->getActiveSheet()->getStyle($body_range)->applyFromArray($styleArray);
			$i++;
		}
		header('Content-Type: application/vnd.openXMLformats-officedocument.spreadsheetml.sheet');
		header('Content-Disposition: attachment;filename="capacity_report.xlsx"');
		header('Cache-Control: max-age=0');	
		
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		$objWriter->save('php://output'); 
		}
Coordinator
Apr 19, 2011 at 9:27 PM

Does that bunch of gobbledegook contain anything readable, such as error messages, leading white space, etc?

Apr 19, 2011 at 10:46 PM

I resolved this. Discovered I was echoing something just before the Excel output and you can't do that.