invalid file format

Jan 8, 2009 at 12:33 PM
I'm trying to create an excel with multiplesheets in it.I'm using the following Code
$xls=newPHPExcel();
$xls->getProperties()->setCreator("Me");
$xls->getProperties()->setLastModifiedBy("Me");
$xls->getProperties()->setTitle("Prijslijst");
$xls->getProperties()->setSubject("prijslijst");
$xls->getProperties()->setDescription("prijslijstvoor".$this->refcli['c_numero']);

$this->add_page(&$xls,&$this->ToComeNl,0,"ToComeNl");
$xls->createSheet();
$this->add_page($xls,&$this->ToComeFr,1,"ToComeFr");
$xls->createSheet();
$this->add_page($xls,&$this->ToComeHw,2,"ToComeHw");
$xls->createSheet();
$this->add_page($xls,&$this->nl,3,"Nl");
$xls->createSheet();
$this->add_page($xls,&$this->fr,4,"fr");
header("Pragma:public");
header("Expires:0");
header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=prijslijst.xlsx");
header("Content-Transfer-Encoding:binary");

$objWriter=newPHPExcel_Writer_Excel2007($xls);
$objWriter->save("php://output");

The Add page function goes as follows:
//$xls is the PHPExcel object
//list is an array with values that are placed in the sheet
//$i is the number of the sheet
//$title is the title of the sheet
function add_page($xls, $list, $i, $title)[
        $xls->setActiveSheetIndex($i);
        $xls->getActiveSheet()->setTitle($title);
        $xls->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $xls->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
        $xls->getActiveSheet()->getPageSetup()->setFitToWidth();
        
        $xls->getActiveSheet()->getHeaderFooter()->setOddFooter('&P of &N');
        $xls->getActiveSheet()->setShowGridlines(true);
        $xls->getActiveSheet()->getPageMargins()->setLeft(0.1);
        $xls->getActiveSheet()->getPageMargins()->setRight(0.1);
        $xls->getActiveSheet()->getPageMargins()->setTop(0.1);
        $xls->getActiveSheet()->getPageMargins()->setBottom(0.3);
        
        // Standaard font
        $xls->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial');
        $xls->getActiveSheet()->getDefaultStyle()->getFont()->setSize(9);
        //Plaats titel
        $xls->getActiveSheet()->mergeCells('A1:K2');
        $xls->getActiveSheet()->setCellValue('A1', $this->titel['welkom']." ".$this->refcli['c_nom1']);
        $xls->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $xls->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $xls->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //Geldigheid
        $xls->getActiveSheet()->mergeCells('A3:G3');
        $begin = date("d/m/Y");
        $eind = date("d/m/Y",time()*60*60*24*30);
        $xls->getActiveSheet()->setCellValue('A3', $this->titel['zin1']." $begin ".$this->titel['zin2']." $eind ".$this->titel['zin3']);
        //Euro
        $xls->getActiveSheet()->mergeCells('A4:G4');
        $xls->getActiveSheet()->setCellValue('A4', $this->titel['euro']);
        // Wijzigingen
        $xls->getActiveSheet()->mergeCells('H3:K4');
        $xls->getActiveSheet()->setCellValue('H3', $this->titel['history']." ".date("d/m/Y",$this->histo_time));
        $xls->getActiveSheet()->getStyle('H3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $xls->getActiveSheet()->getStyle('H3')->getFill()->getStartColor()->setARGB('FFFFFACD');
        $xls->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
        $xls->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
        $xls->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
        $xls->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
        $xls->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
        $xls->getActiveSheet()->getColumnDimension('F')->setWidth(10.86);
        $xls->getActiveSheet()->getColumnDimension('G')->setWidth(8.86);
        
        $row = 5;
        $colum = 0;
        $colums = array(0=>"A",1=>"B",2=>"C",3=>"D",4=>"E",5=>"F",6=>"G",7=>"H",8=>"I",9=>"J",10=>"K",11=>"L",12=>"M",13=>"N",14=>"O",15=>"P",16=>"Q");
        
        if ($this->refcli['c_lan'] == "F")[
            $taal = "fr";
        ]else[    
            $taal = "nl";
        ]
        
        foreach($list as $key=>$item)[
            // Titel niveau 1        
            $xls->getActiveSheet()->mergeCells("A".$row.":"."K".$row);
            if(isset($this->groep_info[$item['id']][$taal]))[
                $xls->getActiveSheet()->setCellValue("A".$row, strtoupper($this->groep_info[$item['id']][$taal]));
            ]else[
                $xls->getActiveSheet()->setCellValue("A".$row, "Unknown title");
            ]
            $xls->getActiveSheet()->getStyle("A".$row)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
            $xls->getActiveSheet()->getStyle("A".$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $xls->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $xls->getActiveSheet()->getStyle("A".$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $xls->getActiveSheet()->getStyle("A".$row)->getFill()->getStartColor()->setARGB('FF000080');
            $row++;
            
            //Titel niveau 2
            foreach($item['sub'] as $key2=>$item2)[
                $xls->getActiveSheet()->mergeCells("A".$row.":"."K".$row);
                $xls->getActiveSheet()->getStyle("A".$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $xls->getActiveSheet()->getStyle("A".$row)->getFill()->getStartColor()->setARGB('FFD15FEE');
                if(isset($this->groep_info[$item['id']][$taal]) and isset($this->groep_info[$item2['id']][$taal]) and $this->groep_info[$item['id']][$taal] <> $this->groep_info[$item2['id']][$taal])[
                    $xls->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
                    $xls->getActiveSheet()->setCellValue("A".$row, $this->groep_info[$item2['id']][$taal]);
                ]
                $row++;
                //Titels
                $colum = 0;
                    //Dispo
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->getStartColor()->setARGB('FFD15FEE');
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFont()->setBold(true);
                if(isset($this->titel['dispo']))[ $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $this->titel['dispo']);]
                $colum++;
                    //refart
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->getStartColor()->setARGB('FFD15FEE');
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFont()->setBold(true);
                if(isset($this->titel['ref']))[ $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $this->titel['ref']);]
                $colum++;
                    //Title
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->getStartColor()->setARGB('FFD15FEE');
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFont()->setBold(true);
                if(isset($this->titel['titel']))[ $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $this->titel['titel']);]
                $colum++;
                    //Taal
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->getStartColor()->setARGB('FFD15FEE');
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFont()->setBold(true);
                if(isset($this->titel['taal']))[ $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $this->titel['taal']);]
                $colum++;
                    //barcode
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->getStartColor()->setARGB('FFD15FEE');
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFont()->setBold(true);
                if(isset($this->titel['barcode']))[ $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $this->titel['barcode']);]
                $colum++;
                    //platform
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->getStartColor()->setARGB('FFD15FEE');
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFont()->setBold(true);
                if(isset($this->titel['platform']))[ $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $this->titel['platform']);]
                $colum++;
                    //verpakking
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->getStartColor()->setARGB('FFD15FEE');
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFont()->setBold(true);
                if(isset($this->titel['verpakking']))[ $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $this->titel['verpakking']);]
                $colum++;
                    //prijs
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->getStartColor()->setARGB('FFD15FEE');
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFont()->setBold(true);
                if(isset($this->titel['prijs']))[ $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $this->titel['prijs']);]
                $colum++;
                    //vkpe
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->getStartColor()->setARGB('FFD15FEE');
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFont()->setBold(true);
                if(isset($this->titel['vkpe']))[ $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $this->titel['vkpe']);]
                $colum++;
                    //vkpi
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->getStartColor()->setARGB('FFD15FEE');
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFont()->setBold(true);
                if(isset($this->titel['vkpi']))[ $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $this->titel['vkpi']);]
                $colum++;
                    //boite
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFill()->getStartColor()->setARGB('FFD15FEE');
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getFont()->setBold(true);
                if(isset($this->titel['boite']))[ $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $this->titel['boite']);]
                $colum++;
                $row++;
                    //Articles
                foreach($item2['art'] as $key3=>$refart)[
                    if(isset($this->art_info[$refart]))[
                        $geg = & $this->art_info[$refart];
                        foreach($this->art_info[$refart]['prijs_klant'] as $key4=>$item4)[
                            $colum = 0;
                            //Dispo
                            switch($geg['s_cat'])[
                            case "NEW":
                                $xls->getActiveSheet()->setCellValue($colums[$colum].$row, date("d/m/Y",$geg['dispo']));
                                break;
                            case "EOL":
                                $xls->getActiveSheet()->setCellValue($colums[$colum].$row, "EOL");
                                break;
                            ]
                            $colum++;
                            //refart
                            $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $geg['s_gamnum']);
                            $colum++;
                            //title
                            $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $geg['s_nomnl']);
                            $colum++;
                            //taal
                            $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $geg['taal']['text']);
                            $colum++;
                            // //barcode
                            $xls->getActiveSheet()->getCell($colums[$colum].$row)->setValueExplicit($geg['s_cbare'], PHPExcel_Cell_DataType::TYPE_STRING);
                            $colum++;
                            //platform
                            $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $geg['platform']['text']);
                            $colum++;
                            // //verpakking
                            $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $geg['boite']['text']);
                            $colum++;
                            //prijs
                            $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $item4);
                            $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
                            $colum++;
                            //vkpe
                            $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $geg['s_pvbase']);
                            $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
                            $colum++;
                            //vkpi
                            $xls->getActiveSheet()->getCell($colums[$colum].$row)->setValueExplicit($geg['base_inc'], PHPExcel_Cell_DataType::TYPE_NUMERIC);
                            $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
                            $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $geg['base_inc']);
                            $colum++;
                            //boite
                            $xls->getActiveSheet()->getCell($colums[$colum].$row)->setValueExplicit($geg['s_carton'], PHPExcel_Cell_DataType::TYPE_NUMERIC);
                            $xls->getActiveSheet()->getStyle($colums[$colum].$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
                            $xls->getActiveSheet()->setCellValue($colums[$colum].$row, $geg['s_carton']);
                            $colum++;
                            $row++;
                        ]
                    ]
                ]
            ]
        ]
        $k = $colums[$colum-1];
        $l = $row-1;
        $xls->getActiveSheet()->getPageSetup()->setPrintArea('A1:'.$k.$l);
    ]
When i try to open the created excel with excel 2003 with the office2007 convertor installed i recieve the following errors:
    - Excel cannot open the fil 'prijslijst.xlsx' because the file format of file extension is not valid. Verify that the file has not been corrupted and that the file extension mathces the format of the file.
    - The convertor failed to open the file.
Can anybody help me?




Developer
Jan 8, 2009 at 3:18 PM
Can you send me the file (erik at phpexcel dot net)

Developer
Jan 8, 2009 at 4:07 PM
[file received]

Opening in Notepad shows the problem:
http://img149.imageshack.us/img149/9840/notepaduz9.png

I'm afraid you need to raise maximum execution time. For example:

ini_set('max_execution_time', 300);


Jan 8, 2009 at 4:31 PM
Edited Jan 8, 2009 at 4:47 PM
Hi,

I've added the ini_set('max_execution_time', 300); but i'm still getting the same error. The created file is empty. any idea?[file received]


Developer
Jan 8, 2009 at 5:40 PM
How does the file look when you open it in a text editor?

Jan 9, 2009 at 9:59 AM
Edited Jan 9, 2009 at 11:01 AM
  Hi,
I've changed the script so that the file is saved to disk. I'm now recieving a xlsx file but when i open it i get: the convertor failed to open the file (i'm using office 2003 with the convertor).
I've send you the file.
Office 2007 gives following message: Excel found unreadable content in file.xlsx. Do you want to recover the contents of this workbook? I you trust the source of this workbook click yes.
Clicking yes gives the following logfile
<style> BODY[font:x-small 'Verdana';margin-right:1.5em] .c[cursor:hand] .b[color:red;font-family:'Courier New';font-weight:bold;text-decoration:none] .e[margin-left:1em;text-indent:-1em;margin-right:1em] .k[margin-left:1em;text-indent:-1em;margin-right:1em] .t[color:#990000] .xt[color:#990099] .ns[color:red] .dt[color:green] .m[color:blue] .tx[font-weight:bold] .db[text-indent:0px;margin-left:1em;margin-top:0px;margin-bottom:0px;padding-left:.3em;border-left:1px solid #CCCCCC;font:small Courier] .di[font:small Courier] .d[color:blue] .pi[color:blue] .cb[text-indent:0px;margin-left:1em;margin-top:0px;margin-bottom:0px;padding-left:.3em;font:small Courier;color:#888888] .ci[font:small Courier;color:#888888] PRE[margin:0px;display:inline] </style>
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <logFileName>error021000_02.xml</logFileName>
  <summary>Errors were detected in file 'file.xlsx'</summary>
- <removedRecords summary="Following is a list of removed records:">
  <removedRecord>Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)</removedRecord>
  </removedRecords>
  </recoveryLog>


Developer
Jan 9, 2009 at 11:07 PM
[file received]
After inspection of the file, it looks like the writer stopped while processing the last sheet.

Are you getting any errors? Try to use

error_reporting(E_ALL);
ini_set('display_errors', 1);

Jan 12, 2009 at 11:57 AM
Even after adding the suggested code, i'm not getting any errors.
Developer
Jan 13, 2009 at 12:35 AM
This code is rather complicated and a bit hard to debug in this forum. I sent you private message.

Developer
Jan 14, 2009 at 9:12 AM
[solved by raising memory limit]

ini_set('memory_limit', '256M');