Opening excel document created by php

Question

I have managed to create an excel document using php however I am getting an error everytime I open the document, even though everything else is fine. the error is the file you are trying to open is in different format than specified by the file extension ...

My code to export to excel:

public function actionExportToExcel() {

        //header('Content-type: text/csv');
        header('Content-Disposition: attachment; filename="project-report-' . date('YmdHi') .'.xls"');
        header("Content-Type: application/ms-excel");

        $model=new ViewWebprojectreport('search');
        $model->unsetAttributes();  // clear any default values

        if(Yii::app()->user->getState('exportModel'))
            $model=Yii::app()->user->getState('exportModel');


        $dataProvider = $model->search(false);
        $dataProvider->pagination->pageSize = $model->count();
        // csv header
        echo    ViewWebprojectreport::model()->getAttributeLabel("StartDATE")."\t".
                ViewWebprojectreport::model()->getAttributeLabel("PROJECT")."\t".
                "Survey Number\t".
                ViewWebprojectreport::model()->getAttributeLabel("ActualEndDate")."\t".
                ViewWebprojectreport::model()->getAttributeLabel("OFFICE")."\t".
                ViewWebprojectreport::model()->getAttributeLabel("PERCENT")."\t".
                ViewWebprojectreport::model()->getAttributeLabel("PERCENTPlanned")."\t".
                ViewWebprojectreport::model()->getAttributeLabel("KM")."\t".
                ViewWebprojectreport::model()->getAttributeLabel("KMPlanned")."\t".
                ViewWebprojectreport::model()->getAttributeLabel("COUNTRY")."\t".
                ViewWebprojectreport::model()->getAttributeLabel("AREA")."\t".
                ViewWebprojectreport::model()->getAttributeLabel("ASAAREA").
                " \r\n";
        // csv data
        foreach ($dataProvider->getData() as $data) {
            //if you want all data use this looop
            /*foreach ($data as $key => $value) {
                echo $value.",";
            }
            echo "\r\n";*/
            echo "$data->StartDATE\t$data->PROJECT\t".$data->PROJCODE . $data->PROJID ."\t$data->ActualEndDate\t$data->OFFICE\t$data->PERCENT\t$data->PERCENTPlanned\t$data->KM\t$data->KMPlanned\t$data->COUNTRY\t$data->AREA\t$data->ASAAREA\t\r\n";
        }


    }

I do not want to export as csv but straight into excel format file. What am I missing?

Answer 1

I used PHPExcel

    $objPHPExcel = new PHPExcel();
    spl_autoload_register(array('YiiBase', 'autoload'));

    $objPHPExcel->getProperties()->setCreator(Yii::app()->user->__userInfo['name'])
        ->setLastModifiedBy(Yii::app()->user->__userInfo['name'])
        ->setTitle("Weekly Status")
        ->setSubject("Weekly Status");

    $sheet = $objPHPExcel->setActiveSheetIndex(0);
    $highestRow = $sheet->getHighestRow();
    $highestColumn = $sheet->getHighestColumn();

    $model=new ViewWebprojectreport('search');
    $model->unsetAttributes();  // clear any default values

    if(Yii::app()->user->getState('exportModel'))
        $model=Yii::app()->user->getState('exportModel');


    $dataProvider = $model->weeklystatus(array(),true,false);
    $dataProvider->pagination->pageSize = $model->count();

    //data
    foreach ($dataProvider->getData() as $data) {
        //if you want all data use this looop
        $highestColumn = "A";
        foreach ($data as $key => $value) {
            if(! in_array($key,array("id","PROCESSOR","DEPTCODE","PERCENTPlanned","MCSALE"))){
                if($key == "name")
                    $key = "Client";
                else
                    $key = ViewWebprojectreport::model()->getAttributeLabel("$key");
                if($highestRow == 1){
                    $sheet->setCellValue($highestColumn.$highestRow,$key);
                    //Yii::log($key,"ERROR");
                }
                //echo $value.",";
                if($highestRow == 1){
                    $highestRow++;
                    $sheet->setCellValue($highestColumn.$highestRow,$value);
                    $highestRow--;
                }else
                    $sheet->setCellValue($highestColumn.$highestRow,$value);

                $highestColumn++;
            }


        }
        //Yii::log($highestRow,"ERROR");
        if($highestRow == 1)
            $highestRow++;

        $highestRow++;
        //echo "\r\n";*/
        //echo "$data->StartDATE\t$data->ProjectEndDate\t$data->PROJECT\t".$data->PROJCODE . $data->PROJID ."\t$data->ActualEndDate\t$data->PROCESSOR\t$data->OFFICE\t$data->DEPTCODE\t$data->PERCENT\t$data->PERCENTPlanned\t$data->KM\t$data->KMPlanned\t$data->MC\t$data->MCSALE\t$data->CATEGORY\t$data->COUNTRY\t$data->AREA\t$data->PROJINFO\t$data->REGION\t$data->ASAAREA\t\r\n";
    }


    $filename = $_GET['type'].'statusreport_'.date('Y-m-d_H-i-s_T').'.xls';

    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="'.$filename.'"');
    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, 'Excel5');
    $objWriter->save(Yii::app()->params['exportToDir'].$filename);
    $objWriter->save('php://output');
    Yii::app()->end();

Answer 2

This is because the file is actually basically just a CSV file with an XLS extension to make it open in Excel. See this Microsoft doc for more information: http://support.microsoft.com/kb/948615 - it happens in new versions of Excel. Older ones will happily export them.

The reason for doing it this way was because it is so much simpler to export a CSV file than an Excel one. I'd like to write a proper Excel exporter sometime, but that will take time to read and understand the Excel file format, and I've not had a chance to do that yet.

One option is simply to rename the file name to .csv, and keep the user interface as saying that it is an Excel file (Excel is quite happy to read csv files). Given that Windows tends to hide the file extension, this seems like a fairly attractive option.

It would be helpful solution for solving varied kinds of excel problems - link

let me know if i can help you more.