php - phpexcel-generated xls file open as plain xml -
i've been using phpexcel in part of application , works fine. in cases file created phpexcel can opened libreoffice. in 1 controller generate fine xls file. file can downloaded. every time open file libreoffice shows text import. file shows actual xml file in libreoffice calc. problem?
<?php class excelxml_workbook {
private $worksheets = array(); private $styles = array(); private $view = null; private $filename = ''; private $translateheaders = false; private $translatevaluecolumns = array(); private $hideheaderdata = false; private $datasetheaderstyleid = null; private $cellstylemap = array(); public function __construct($filename = '', zend_view_interface $view = null, $translateheaders = false, $translatevaluecolumns = array(), $hideheaderdata = false) { // initialise worksheet collection , add 1 worksheet $this->worksheets = array(); $this->worksheets[] = new excelxml_worksheet($this); $this->view = $view; $this->filename = $filename; $this->translateheaders = $translateheaders; $this->translatevaluecolumns = $translatevaluecolumns; $this->hideheaderdata = $hideheaderdata; $this->initworkbookstyle(); } public function __get($name) { return $this->$name; } public function __set($name, $value) { $this->$name = $value; } public function __isset($name) { return isset($this->$name); } private function initworkbookstyle() { // default text single solid black lines inner , outer borders. $this->addstylefromarray(array( 'borders' => array( 'allborders' => array( 'style' => excelxml_style_border::style_continuous, 'weight' => 1, 'color' => '#000000', ), ), )); // white bold text on red background single solid black lines inner , outer borders. $this->datasetheaderstyleid = $this->addstylefromarray(array( 'font' => array( 'bold' => true, 'color' => '#ffffff' ), 'alignment' => array( 'horizontal' => excelxml_style_alignment::horizontal_center, 'vertical' => excelxml_style_alignment::vertical_center ), 'fill' => array( 'type' => excelxml_style_interior::pattern_solid, 'color' => '#ff0000' ), 'borders' => array( 'allborders' => array( 'style' => excelxml_style_border::style_continuous, 'weight' => 1, 'color' => '#000000' ), ), )); $this->cellstylemap['h_center'] = $this->addstylefromarray(array( 'alignment' => array( 'vertical' => excelxml_style_alignment::vertical_center, 'horizontal' => excelxml_style_alignment::horizontal_center ) )); $this->cellstylemap['h_left'] = $this->addstylefromarray(array( 'alignment' => array( 'vertical' => excelxml_style_alignment::vertical_center, 'horizontal' => excelxml_style_alignment::horizontal_left ) )); $this->cellstylemap['h_right'] = $this->addstylefromarray(array( 'alignment' => array( 'vertical' => excelxml_style_alignment::vertical_center, 'horizontal' => excelxml_style_alignment::horizontal_right ) )); } /** * generate header xml of workbook */ private function getheader() { echo '<?xml version="1.0" encoding="utf-8"?><?mso-application progid="excel.sheet"?> <workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/tr/rec-html40">'; } /** * add sheet workbook * @param excelxml_worksheet $sheet */ public function addsheet($sheet) { $this->worksheets[] = $sheet; } /** * worksheet name/title * @param string $name * @return excelxml_worksheet */ public function getsheetbyname($name) { $worksheet = null; $worksheetcount = count($this->worksheets); ($i = 0; $i < $worksheetcount; $i++) { if ($this->worksheets[$i]->title == $name) { $worksheet = $this->worksheets[$i]; } } return $worksheet; } /** * sheet index * * @param int $sheetindex sheet index * @return excelxml_worksheet * @throws exception */ public function getsheet($sheetindex = 0) { if (!isset($this->worksheets[$sheetindex])) { throw new exception("sheet index out of bounds."); } return $this->worksheets[$sheetindex]; } /** * add style array workbook * @param array $stylearray * @param boolean $isdefault * @param int $parentstyleid * @return int $styleid */ public function addstylefromarray($stylearray = array(), $parentstyleid = null) { $style = new excelxml_style(); foreach ($stylearray $stylename => $property) { switch ($stylename) { case excelxml_style::font: $bold = ($property['bold'] == true)? 1 : 0; $color = (!empty($property['color']))? $property['color'] : ''; $fontname = (!empty($property['name']))? $property['name'] : ''; $italic = (!empty($property['italic']))? 1 : 0; $style->setfont($bold, $color, $fontname, $italic); break; case excelxml_style::alignment: $wraptext = !empty($property['wraptext'])? 1 : 0; $horizontal = !empty($property['horizontal'])? $property['horizontal'] : ''; $vertical = !empty($property['vertical'])? $property['vertical'] : ''; $style->setalignment($wraptext, $horizontal, $vertical); break; case excelxml_style::interior: $color = (!empty($property['color']))? $property['color'] : ''; $pattern = !empty($property['type'])? $property['type'] : 'solid'; $style->setbackgroundcolor($color, $pattern); break; case excelxml_style::borders: if (!empty($property['allborders'])) { $color = (!empty($property['allborders']['color']))? $property['allborders']['color'] : ''; $linestyle = !empty($property['style'])? $property['style'] : excelxml_style_border::style_continuous; $weight = !empty($property['weight'])? $property['weight'] : 1; $style->setborder($color, $linestyle, $weight); } break; } } return $this->setstyle($style, $parentstyleid); } /** * set style object workbook * * @param excelxml_style $style * @param string $parentstyleid * @return int */ private function setstyle( excelxml_style $style, $parentstyleid = null) { $this->styles[] = $style; $styleid = count($this->styles); $style->setid($styleid); $style->setparentstyleid($parentstyleid); return $styleid; } /** * set header data containing information respective worksheet * @param array $headerdataarray */ public function setheaderdata($headerdataarray) { foreach ($headerdataarray $sheetindex => $headerdata) { $sheet = $this->getsheet($sheetindex); $sheet->headerdata = $headerdata; } } /** * set column format style respective worksheet * @param array $columnformatarray */ public function setcolumnformatdata($columnformatarray) { foreach ($columnformatarray $sheetindex => $columnformat) { $sheet = $this->getsheet($sheetindex); $sheet->columnformat = $columnformat; } } /** * set cell styling respective worksheet * @param array $cellformatarray */ public function setcellformatdata($cellformatarray) { $hashkey = ''; foreach ($cellformatarray $sheetindex => $cellformatdata) { foreach ($cellformatdata $row => $cellformatbycolumn) { foreach ($cellformatbycolumn $column => $cellformat) { $formatarray = !is_array($cellformat['font'])? array($cellformat['font']) : $cellformat['font']; $fontstylearray = array( 'bold' => in_array( report_report::font_format_bold, $formatarray), 'italic' => in_array( report_report::font_format_italic, $formatarray), 'underline' => in_array( report_report::font_format_underline, $formatarray)? excelxml_style_font::underline_single : excelxml_style_font::underline_none ); $hashkey = $fontstylearray['bold'].'|'.$fontstylearray['italic'].'|'.$fontstylearray['underline']; if (!isset($this->cellstylemap[$hashkey])) { $sheet = $this->getsheet($sheetindex); $columnalignstyle = null; $parentid = null; if (isset($sheet->columnformat[$column]) && isset($sheet->columnformat[$column]['align'])) { $columnalignstyle = $sheet->columnformat[$column]['align']; if (isset($this->cellstylemap['h_'.$columnalignstyle])) { $parentid = $this->cellstylemap['h_'.$columnalignstyle]; } } $this->cellstylemap[$hashkey] = $this->addstylefromarray( array('font' => $fontstylearray), $parentid ); } if (isset($this->cellstylemap[$hashkey])) { $cellformatarray[$sheetindex][$row][$column] = $this->cellstylemap[$hashkey]; } } } } foreach ($cellformatarray $sheetindex => $cellformat) { $sheet = $this->getsheet($sheetindex); $sheet->cellformat = $cellformat; } } /** * set column title respective worksheet * @param array $columntitlearray */ public function setcolumntitledata($columntitlearray) { foreach ($columntitlearray $sheetindex => $columntitles) { $sheet = $this->getsheet($sheetindex); $sheet->columntitles = $columntitles; } } /** * set column type respective worksheet * @param array $columntypearray */ public function setcolumntypedata($columntypearray) { foreach ($columntypearray $sheetindex => $columntypes) { $sheet = $this->getsheet($sheetindex); $sheet->columntypes = $columntypes; } } /** * set custom dataset header respective worksheet * @param array $datasetheaderarray */ public function setdatasetheader($datasetheaderarray) { foreach ($datasetheaderarray $sheetindex => $datasetheader) { $sheet = $this->getsheet($sheetindex); $sheet->datasetheader = $datasetheader; } } /** * set excel dataset respective worksheet * there multiple dataset 1 worksheet * * @param array $datasetarray */ public function setdataset($datasetarray) { foreach ($datasetarray $sheetindex => $dataset) { $sheet = $this->getsheet($sheetindex); if ($dataset instanceof report_multidataset) { foreach ($dataset->datasetarray $dataset) { $sheet->setdatasetlist($dataset); } } else { $sheet->setdatasetlist($dataset); } } } /** * render workbook style xml */ private function renderstyleasxml() { if (count($this->styles) > 0) { $stylexml = '<ss:styles>'; foreach ($this->styles $style) { $stylexml .= $style->asxml(); } $stylexml .= '</ss:styles>'; } echo $stylexml; } /** * footer of workbook end file */ private function getfooter() { echo '</workbook>'; } /** * set necessary header make file downloadable excel xml */ private function outputexcelheaders() { ini_set('zlib.output_compression','off'); // set headers header('pragma: public'); header("expires: mon, 26 jul 1997 05:00:00 gmt"); // date in past header('last-modified: '.gmdate('d, d m y h:i:s').' gmt'); header('cache-control: no-store, no-cache, must-revalidate'); // http/1.1 header('cache-control: pre-check=0, post-check=0, max-age=0'); // http/1.1 header("pragma: no-cache"); header("expires: 0"); header('content-description: file transfer'); header('content-transfer-encoding: none'); header('content-type: application/vnd.ms-excel;'); // should work ie & opera header("content-type: application/x-msexcel"); // should work rest header('content-disposition: attachment; filename="'.basename($this->filename).'.xml"'); } /** * prepare excel provided data before */ private function prepareexcel() { $this->getheader(); $this->renderstyleasxml(); foreach ($this->worksheets $sheetindex => $sheet) { $sheet->render(); } $this->getfooter(); } /** * render excelxml file , give download */ public function render() { $this->outputexcelheaders(); $this->prepareexcel(); } /** * prepare , save file given path * @param string $path */ public function save($path) { ob_start(); $this->prepareexcel(); file_put_contents($path.directory_separator.$this->filename, ob_get_contents()); ob_end_clean(); } /** * content of file send attachment in email * @return zend_mime_part */ public function renderasmimepart() { ob_start(); $this->prepareexcel(); $mimepart = new zend_mime_part(ob_get_contents()); $mimepart->encoding = zend_mime::encoding_base64; $mimepart->type = 'application/x-msexcel'; $mimepart->disposition = 'attachment'; $mimepart->filename = $this->filename.'.xml'; ob_end_clean(); return $mimepart; } }
?>
Comments
Post a Comment