<?php

    
/**
    * This class can be used to export php-data or mysql-data in diferent formats
    **/
    
class export_system
    
{
        
/**
        * export php-data (1D-arrays or 2D-arrays) to other formats
        * @param $raw_data the array you want to export
        * @param $type the format to export to ('csv','old_csv','xls', 'xml','html', 'xlsx')
        * @param $filename filename to send in headers
        * @param $include_headers get a row with headers at top of the file, false to only get data
        * @param $name the name of the worksheeat
        * @return boolean
        **/
        
function send_export($raw_data$type 'csv'$filename NULL$include_headers TRUE$name 'exported data'$extra NULL)
        {
            if(
$this->send_header($type$filename))
            {
                echo 
$this->export($raw_data$type 'csv'$include_headers$name$extra);
                return 
TRUE;
            }
            else
            {
                return 
FALSE;
            }
        }

        
/**
        * export php-data (1D-arrays or 2D-arrays) to other formats
        * @param $raw_data the array you want to export
        * @param $type the format to export to ('csv','old_csv','xls', 'xml','html', 'xlsx')
        * @param $include_headers get a row with headers at top of the file, false to only get data
        * @param $name the name of the worksheeat
        * @return file content of exported data in given export type
        **/
        
public function export($raw_data$type 'csv'$include_headers TRUE$name 'exported data'$extra NULL)
        {
            
/* if not php data in a array, then somthing went wrong */
            
if(!is_array($raw_data))
            {
                return 
FALSE;
            }

            
/* convert 1D-arrays to 2D-arrays */
            
if(!is_array(current($raw_data)))
            {
                
$raw_data = array($raw_data);
            }

            
/* what type did you want to export to */
            
switch($type)
            {
                
/* Comma Seperated Values (delimited with ';', enclosed by '"')*/
                
case 'csv':
                {
                    
/* add header row if wanted */
                    
if($include_headers)
                    {
                        
$data '"' implode('";"',array_keys(current($raw_data))) . '"' "\n";
                    }

                    
/* add each row of data */
                    
foreach($raw_data as $current_row)
                    {
                        
$data .= '"' implode('";"'$current_row) . '"' "\n";
                    }

                    
/* return the data */
                    
return mb_convert_encoding($data'ISO-8859-1''UTF-8');
                }

                
/* Comma Seperated Values (delimited with ';', not enclosed by '"')*/
                
case 'old_csv':
                {
                    
/* add header row if wanted */
                    
if($include_headers)
                    {
                        
$data '"' implode('";"',array_keys(current($raw_data))) . '"' "\n";
                    }

                    
/* add each row of data */
                    
foreach($raw_data as $current_row)
                    {
                        
$data .= implode(';'$current_row) . "\n";
                    }

                    
/* return the data */
                    
return mb_convert_encoding($data'ISO-8859-1''UTF-8');
                }

                
/* xml of 'microsoft office spreadsheet' also knowned as 'Microsoft Excel 2003 XML'*/
                
case 'xml':
                case 
'xls':
                case 
'excel_xml':
                {
                    
/* define difrent starts end ends */
                    
$row_start '            <Row>' "\n";
                    
$header_row_cell_start '                <Cell ss:StyleID="Header">' "\n" '                    <Data ss:Type="String">';
                    
$normal_row_cell_start '                <Cell>' "\n" '                    <Data ss:Type="String">';
                    
$cell_end '</Data>' "\n" '                </Cell>' "\n";
                    
$row_end '            </Row>' "\n";

                    
/* start of data */
                    
$data  '<?xml version="1.0" encoding="UTF-8"?>' "\n";
                    
$data .= '<?mso-application progid="Excel.Sheet"?>' "\n";
                    
$data .= '<Workbook xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel">' "\n";
                    
$data .= '    <Styles>' "\n";
                    
$data .= '        <Style ss:ID="Default" ss:Name="Default"/>' "\n";
                    
$data .= '        <Style ss:ID="Header" ss:Name="Header">' "\n";
                    
$data .= '            <Font ss:Bold="1" ss:Color="#ffffff"/>' "\n";
                    
$data .= '            <Interior ss:Color="#280099" ss:Pattern="Solid"/>' "\n";
                    
$data .= '        </Style>' "\n";
                    
$data .= '    </Styles>' "\n";

                    
/* name of worksheet */
                    
$data .= '    <ss:Worksheet ss:Name="' $name '">' "\n";
                    
$data .= '        <Table>' "\n";

                    
/* add header row if wanted */
                    
if($include_headers)
                    {
                        
$data .= $row_start;
                        
$data .= $header_row_cell_start;
                        
$data .= implode($cell_end $header_row_cell_startarray_keys(current($raw_data)));
                        
$data .= $cell_end;
                        
$data .= $row_end;
                    }

                    
/* add each row of data */
                    
foreach($raw_data as $current_row)
                    {
                        
$data .= $row_start;
                        
$data .= $normal_row_cell_start;
                        
$data .= implode($cell_end $normal_row_cell_start$current_row);
                        
$data .= $cell_end;
                        
$data .= $row_end;
                    }

                    
/* end data */
                    
$data .= '        </Table>' "\n";
                    
$data .= '    </ss:Worksheet>' "\n";
                    
$data .= '</Workbook>' "\n";

                    
/* convert hyperlink to real hyperlinks */
                    
$regexp "#<Cell>([\t\n\r ]*)<Data ss:Type=\"String\">=HYPERLINK\(\"([^\"]*)\";\"([^\")]*)\"\)</Data>#";
                    
$replace_to "<Cell ss:HRef=\"\\2\">\\1<Data ss:Type=\"String\">\\3</Data>";
                    
$data preg_replace($regexp,$replace_to,$data);

                    
/* return the data */
                    
return $data;
                }

                
/* html, a <table border="1"> with the data */
                
case 'html':
                {
                    
/* start of data */
                    
$data '<table border="1">' "\n";

                    
/* add header row if wanted */
                    
if($include_headers)
                    {
                        
$data .= '    <tr><th>' implode('</th><th>',array_keys(current($raw_data))) . '</th></tr>' "\n";
                    }

                    
/* add all rows of data */
                    
foreach($raw_data as $current_row)
                    {
                        
$data .= '    <tr><td>' implode('</td><td>'$current_row) . '</td></tr>' "\n";
                    }

                    
/* end the data */
                    
$data .= '</table>';

                    
/* return the data */
                    
return $data;
                }

                case 
'xlsx':
                {
                    
/* create a file name */
                    
$temp_file tempnam('/tmp/''export_xlsx_');
                    
$timestamp time();

                    
/* create an empty zip file */
                    
$zip = new ZipArchive();
                    if(
$zip->open($temp_fileZIPARCHIVE::CREATE) !== TRUE)
                    {
                        return 
FALSE;
                    }

                    
/* add directory structure */
                    
$zip->addEmptyDir('xl');
                    
$zip->addEmptyDir('xl/_rels');
                    
$zip->addEmptyDir('xl/worksheets');
                    
$zip->addEmptyDir('docProps');
                    
$zip->addEmptyDir('_rels');

                    
/* add default constant files */
                    
$zip->addFromString('[Content_Types].xml''<?xml version="1.0" encoding="UTF-8"?>' PHP_EOL '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Override PartName="/_rels/.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/><Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/><Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/><Override PartName="/xl/_rels/workbook.xml.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>' PHP_EOL '</Types>');
                    
$zip->addFromString('docProps/core.xml''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' PHP_EOL '<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><cp:revision>0</cp:revision></cp:coreProperties>');
                    
$zip->addFromString('docProps/app.xml''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' PHP_EOL '<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><TotalTime>0</TotalTime></Properties>');
                    
$zip->addFromString('_rels/.rels''<?xml version="1.0" encoding="UTF-8"?>' PHP_EOL '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>' PHP_EOL '</Relationships>');
                    
$zip->addFromString('xl/_rels/workbook.xml.rels''<?xml version="1.0" encoding="UTF-8"?>' PHP_EOL '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>' PHP_EOL '</Relationships>');
                    
$zip->addFromString('xl/styles.xml''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' PHP_EOL '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><numFmts count="1"><numFmt formatCode="GENERAL" numFmtId="164"/></numFmts><fonts count="5"><font><name val="Arial"/><family val="2"/><sz val="10"/></font><font><name val="Arial"/><family val="0"/><sz val="10"/></font><font><name val="Arial"/><family val="0"/><sz val="10"/></font><font><name val="Arial"/><family val="0"/><sz val="10"/></font><font><name val="Arial"/><family val="2"/><b val="true"/><color rgb="00FFFFFF"/><sz val="10"/></font></fonts><fills count="3"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill><fill><patternFill patternType="solid"><fgColor rgb="00280099"/><bgColor rgb="00000080"/></patternFill></fill></fills><borders count="1"><border diagonalDown="false" diagonalUp="false"><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count="21"><xf applyAlignment="true" applyBorder="true" applyFont="true" applyProtection="true" borderId="0" fillId="0" fontId="0" numFmtId="164"><alignment horizontal="general" indent="0" shrinkToFit="false" textRotation="0" vertical="bottom" wrapText="false"/><protection hidden="false" locked="true"/></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="2" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="2" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="43"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="41"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="44"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="42"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="9"></xf>' .
                        
'<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="2" fontId="4" numFmtId="164"></xf></cellStyleXfs><cellXfs count="2"><xf applyAlignment="false" applyBorder="false" applyFont="false" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="164" xfId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="2" fontId="4" numFmtId="164" xfId="20"></xf></cellXfs><cellStyles count="7"><cellStyle builtinId="0" customBuiltin="false" name="Normal" xfId="0"/><cellStyle builtinId="3" customBuiltin="false" name="Comma" xfId="15"/><cellStyle builtinId="6" customBuiltin="false" name="Comma [0]" xfId="16"/><cellStyle builtinId="4" customBuiltin="false" name="Currency" xfId="17"/><cellStyle builtinId="7" customBuiltin="false" name="Currency [0]" xfId="18"/><cellStyle builtinId="5" customBuiltin="false" name="Percent" xfId="19"/><cellStyle builtinId="54" customBuiltin="true" name="Header" xfId="20"/></cellStyles><colors><indexedColors><rgbColor rgb="00000000"/><rgbColor rgb="00FFFFFF"/><rgbColor rgb="00FF0000"/><rgbColor rgb="0000FF00"/><rgbColor rgb="000000FF"/><rgbColor rgb="00FFFF00"/><rgbColor rgb="00FF00FF"/><rgbColor rgb="0000FFFF"/><rgbColor rgb="00800000"/><rgbColor rgb="00008000"/><rgbColor rgb="00280099"/><rgbColor rgb="00808000"/><rgbColor rgb="00800080"/><rgbColor rgb="00008080"/><rgbColor rgb="00C0C0C0"/><rgbColor rgb="00808080"/><rgbColor rgb="009999FF"/><rgbColor rgb="00993366"/><rgbColor rgb="00FFFFCC"/><rgbColor rgb="00CCFFFF"/><rgbColor rgb="00660066"/><rgbColor rgb="00FF8080"/><rgbColor rgb="000066CC"/><rgbColor rgb="00CCCCFF"/><rgbColor rgb="00000080"/><rgbColor rgb="00FF00FF"/><rgbColor rgb="00FFFF00"/><rgbColor rgb="0000FFFF"/><rgbColor rgb="00800080"/><rgbColor rgb="00800000"/><rgbColor rgb="00008080"/><rgbColor rgb="000000FF"/><rgbColor rgb="0000CCFF"/><rgbColor rgb="00CCFFFF"/><rgbColor rgb="00CCFFCC"/><rgbColor rgb="00FFFF99"/><rgbColor rgb="0099CCFF"/><rgbColor rgb="00FF99CC"/><rgbColor rgb="00CC99FF"/><rgbColor rgb="00FFCC99"/><rgbColor rgb="003366FF"/><rgbColor rgb="0033CCCC"/><rgbColor rgb="0099CC00"/><rgbColor rgb="00FFCC00"/><rgbColor rgb="00FF9900"/><rgbColor rgb="00FF6600"/><rgbColor rgb="00666699"/><rgbColor rgb="00969696"/><rgbColor rgb="00003366"/><rgbColor rgb="00339966"/><rgbColor rgb="00003300"/><rgbColor rgb="00333300"/><rgbColor rgb="00993300"/><rgbColor rgb="00993366"/><rgbColor rgb="00333399"/><rgbColor rgb="00333333"/></indexedColors></colors></styleSheet>');

                    
/* add workbook with name and app = Puggan */
                    
$zip->addFromString('xl/workbook.xml''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' PHP_EOL '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><fileVersion appName="Puggan"/><workbookPr backupFile="false" showObjects="all" date1904="false"/><workbookProtection/><bookViews><workbookView activeTab="0" firstSheet="0" showHorizontalScroll="true" showSheetTabs="true" showVerticalScroll="true" tabRatio="212" windowHeight="8192" windowWidth="16384" xWindow="0" yWindow="0"/></bookViews><sheets><sheet name="' $name '" sheetId="1" state="visible" r:id="rId2"/></sheets><calcPr iterateCount="100" refMode="A1" iterate="false" iterateDelta="0.001"/></workbook>');

                    
$string_list = array();
                    if(
$include_headers)
                    {
                        
$row_nr 1;
                        
$column_nr 0;
                        
$firestrow reset($raw_data);
                        
$sheetData '<row collapsed="false" customFormat="false" customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="1">';
                        foreach(
array_keys($firestrow) as $headline)
                        {
                            
$cell_name $this->excel_column_name($column_nr) . $row_nr;
                            
$column_nr++;
                            if(
$headline)
                            {
                                
$headline = (string) $headline;
                                if(!isset(
$string_list[$headline]))
                                {
                                    
$string_list[$headline] = count($string_list);
                                }
                                
$sheetData .= '<c r="' $cell_name '" s="1" t="s"><v>' $string_list[$headline] . '</v></c>';
                            }
                        }
                        
$sheetData .= '</row>';
                    }
                    else
                    {
                        
$row_nr 0;
                    }
                    foreach(
$raw_data as $current_row)
                    {
                        
$row_nr++;
                        
$column_nr 0;
                        
$sheetData .= '<row collapsed="false" customFormat="false" customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="' $row_nr '">';
                        foreach(
$current_row as $current_cell)
                        {
                            
$cell_name $this->excel_column_name($column_nr) . $row_nr;
                            
$column_nr++;

                            if(
$current_cell OR $current_cell === OR $current_cell === '0')
                            {
                                
$current_cell = (string) $current_cell;
                                if(!isset(
$string_list[$current_cell]))
                                {
                                    
$string_list[$current_cell] = count($string_list);
                                }
                                
$sheetData .= '<c r="' $cell_name '" s="0" t="s"><v>' $string_list[$current_cell] . '</v></c>';
                            }
                        }
                        
$sheetData .= '</row>';
                    }

                    if(
$string_list)
                    {
                        
$string_list_xml '';
                        foreach(
array_keys($string_list) as $string)
                        {
                            
$string_list_xml .= '<si><t>' str_replace(array('&''<''>'), array('&amp;''&lt;''&gt;'), $string) . '</t></si>';
                        }
                    }
                    else
                    {
                        
$string_list_xml '';
                    }

                    
$count count($string_list);
                    
$zip->addFromString('xl/sharedStrings.xml''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' PHP_EOL '<sst count="' $count '" uniqueCount="' $count '" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">' $string_list_xml '</sst>');

                    
$zip->addFromString('xl/worksheets/sheet1.xml''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' PHP_EOL '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheetPr filterMode="false"><pageSetUpPr fitToPage="false"/></sheetPr><dimension ref="A1:Y2"/><sheetViews><sheetView colorId="64" defaultGridColor="true" rightToLeft="false" showFormulas="false" showGridLines="true" showOutlineSymbols="true" showRowColHeaders="true" showZeros="true" tabSelected="true" topLeftCell="A1" view="normal" windowProtection="false" workbookViewId="0" zoomScale="100" zoomScaleNormal="100" zoomScalePageLayoutView="100"><selection activeCell="A1" activeCellId="0" pane="topLeft" sqref="A1"/></sheetView></sheetViews><cols><col collapsed="false" hidden="false" max="1025" min="1" style="0" width="11.5764705882353"/></cols><sheetData>' $sheetData '</sheetData><printOptions headings="false" gridLines="false" gridLinesSet="true" horizontalCentered="false" verticalCentered="false"/><pageMargins left="0.7875" right="0.7875" top="1.05277777777778" bottom="1.05277777777778" header="0.7875" footer="0.7875"/><pageSetup blackAndWhite="false" cellComments="none" copies="1" draft="false" firstPageNumber="1" fitToHeight="1" fitToWidth="1" horizontalDpi="300" orientation="portrait" pageOrder="downThenOver" paperSize="9" scale="100" useFirstPageNumber="true" usePrinterDefaults="false" verticalDpi="300"/><headerFooter differentFirst="false" differentOddEven="false"><oddHeader>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12&amp;A</oddHeader><oddFooter>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12Page &amp;P</oddFooter></headerFooter></worksheet>');

                    
$zip->close();
                    
$data file_get_contents($temp_file);
                    
unlink($temp_file);
                    return 
$data;
                }

                case 
'xlsx_multi':
                {
                    
/* create a file name */
                    
$temp_file tempnam('/tmp/''export_xlsx_');
                    
$timestamp time();

                    
/* create an empty zip file */
                    
$zip = new ZipArchive();
                    if(
$zip->open($temp_fileZIPARCHIVE::CREATE) !== TRUE)
                    {
                        return 
FALSE;
                    }

                    
/* add directory structure */
                    
$zip->addEmptyDir('xl');
                    
$zip->addEmptyDir('xl/_rels');
                    
$zip->addEmptyDir('xl/worksheets');
                    
$zip->addEmptyDir('xl/_rels');
                    
$zip->addEmptyDir('_rels');
                    
$zip->addEmptyDir('docProps');

                    
$sheet_numbers range(1count($raw_data));

                    
$relations = array();
                    
$workbook = array();
                    foreach(
array_keys($raw_data) as $index => $sheet_name)
                    {
                        
$relations[] = '<Relationship Id="rId' . ($index 2) . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet' . ($index 1) . '.xml"/>';
                        
$workbook[] = '<sheet name="' $sheet_name '" sheetId="' . ($index 1) . '" state="visible" r:id="rId' . ($index 2) . '"/>';
                    }
                    
$relations[] = '<Relationship Id="rId' . (count($raw_data) + 2) . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>';

                    
                    
/* add default constant files */
                    
$zip->addFromString('[Content_Types].xml''<?xml version="1.0" encoding="UTF-8"?>' PHP_EOL '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Override PartName="/_rels/.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/><Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/><Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/><Override PartName="/xl/_rels/workbook.xml.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Override PartName="/xl/worksheets/sheet' implode('.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/worksheets/sheet'$sheet_numbers) . '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>' PHP_EOL '</Types>');
                    
$zip->addFromString('docProps/core.xml''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' PHP_EOL '<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><cp:revision>0</cp:revision></cp:coreProperties>');
                    
$zip->addFromString('docProps/app.xml''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' PHP_EOL '<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><TotalTime>0</TotalTime></Properties>');
                    
$zip->addFromString('_rels/.rels''<?xml version="1.0" encoding="UTF-8"?>' PHP_EOL '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>' PHP_EOL '</Relationships>');
                    
$zip->addFromString('xl/_rels/workbook.xml.rels''<?xml version="1.0" encoding="UTF-8"?>' PHP_EOL '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>' implode(''$relations). PHP_EOL '</Relationships>');
                    
$zip->addFromString('xl/styles.xml''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' PHP_EOL '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><numFmts count="1"><numFmt formatCode="GENERAL" numFmtId="164"/></numFmts><fonts count="5"><font><name val="Arial"/><family val="2"/><sz val="10"/></font><font><name val="Arial"/><family val="0"/><sz val="10"/></font><font><name val="Arial"/><family val="0"/><sz val="10"/></font><font><name val="Arial"/><family val="0"/><sz val="10"/></font><font><name val="Arial"/><family val="2"/><b val="true"/><color rgb="00FFFFFF"/><sz val="10"/></font></fonts><fills count="3"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill><fill><patternFill patternType="solid"><fgColor rgb="00280099"/><bgColor rgb="00000080"/></patternFill></fill></fills><borders count="1"><border diagonalDown="false" diagonalUp="false"><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count="21"><xf applyAlignment="true" applyBorder="true" applyFont="true" applyProtection="true" borderId="0" fillId="0" fontId="0" numFmtId="164"><alignment horizontal="general" indent="0" shrinkToFit="false" textRotation="0" vertical="bottom" wrapText="false"/><protection hidden="false" locked="true"/></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="2" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="2" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="43"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="41"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="44"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="42"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="9"></xf>' .
                        
'<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="2" fontId="4" numFmtId="164"></xf></cellStyleXfs><cellXfs count="2"><xf applyAlignment="false" applyBorder="false" applyFont="false" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="164" xfId="0"></xf><xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="2" fontId="4" numFmtId="164" xfId="20"></xf></cellXfs><cellStyles count="7"><cellStyle builtinId="0" customBuiltin="false" name="Normal" xfId="0"/><cellStyle builtinId="3" customBuiltin="false" name="Comma" xfId="15"/><cellStyle builtinId="6" customBuiltin="false" name="Comma [0]" xfId="16"/><cellStyle builtinId="4" customBuiltin="false" name="Currency" xfId="17"/><cellStyle builtinId="7" customBuiltin="false" name="Currency [0]" xfId="18"/><cellStyle builtinId="5" customBuiltin="false" name="Percent" xfId="19"/><cellStyle builtinId="54" customBuiltin="true" name="Header" xfId="20"/></cellStyles><colors><indexedColors><rgbColor rgb="00000000"/><rgbColor rgb="00FFFFFF"/><rgbColor rgb="00FF0000"/><rgbColor rgb="0000FF00"/><rgbColor rgb="000000FF"/><rgbColor rgb="00FFFF00"/><rgbColor rgb="00FF00FF"/><rgbColor rgb="0000FFFF"/><rgbColor rgb="00800000"/><rgbColor rgb="00008000"/><rgbColor rgb="00280099"/><rgbColor rgb="00808000"/><rgbColor rgb="00800080"/><rgbColor rgb="00008080"/><rgbColor rgb="00C0C0C0"/><rgbColor rgb="00808080"/><rgbColor rgb="009999FF"/><rgbColor rgb="00993366"/><rgbColor rgb="00FFFFCC"/><rgbColor rgb="00CCFFFF"/><rgbColor rgb="00660066"/><rgbColor rgb="00FF8080"/><rgbColor rgb="000066CC"/><rgbColor rgb="00CCCCFF"/><rgbColor rgb="00000080"/><rgbColor rgb="00FF00FF"/><rgbColor rgb="00FFFF00"/><rgbColor rgb="0000FFFF"/><rgbColor rgb="00800080"/><rgbColor rgb="00800000"/><rgbColor rgb="00008080"/><rgbColor rgb="000000FF"/><rgbColor rgb="0000CCFF"/><rgbColor rgb="00CCFFFF"/><rgbColor rgb="00CCFFCC"/><rgbColor rgb="00FFFF99"/><rgbColor rgb="0099CCFF"/><rgbColor rgb="00FF99CC"/><rgbColor rgb="00CC99FF"/><rgbColor rgb="00FFCC99"/><rgbColor rgb="003366FF"/><rgbColor rgb="0033CCCC"/><rgbColor rgb="0099CC00"/><rgbColor rgb="00FFCC00"/><rgbColor rgb="00FF9900"/><rgbColor rgb="00FF6600"/><rgbColor rgb="00666699"/><rgbColor rgb="00969696"/><rgbColor rgb="00003366"/><rgbColor rgb="00339966"/><rgbColor rgb="00003300"/><rgbColor rgb="00333300"/><rgbColor rgb="00993300"/><rgbColor rgb="00993366"/><rgbColor rgb="00333399"/><rgbColor rgb="00333333"/></indexedColors></colors></styleSheet>');

                    
/* add workbook with name and app = Puggan */
                    
$zip->addFromString('xl/workbook.xml''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' PHP_EOL '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><fileVersion appName="Puggan"/><workbookPr backupFile="false" showObjects="all" date1904="false"/><workbookProtection/><bookViews><workbookView activeTab="0" firstSheet="0" showHorizontalScroll="true" showSheetTabs="true" showVerticalScroll="true" tabRatio="212" windowHeight="8192" windowWidth="16384" xWindow="0" yWindow="0"/></bookViews><sheets>' implode(''$workbook) . '</sheets><calcPr iterateCount="100" refMode="A1" iterate="false" iterateDelta="0.001"/></workbook>');

                    
$string_list = array();
                    
                    
$sheet_number 0;
                    foreach(
$raw_data as $sheet_name => $raw_sheet_data)
                    {
                        
$sheet_number++;
                        
$sheetData '';
                        if(
$include_headers)
                        {
                            
$row_nr 1;
                            
$column_nr 0;
                            
$firestrow reset($raw_sheet_data);
                            
$sheetData '<row collapsed="false" customFormat="false" customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="1">';
                            foreach(
array_keys($firestrow) as $headline)
                            {
                                
$cell_name $this->excel_column_name($column_nr) . $row_nr;
                                
$column_nr++;
                                if(
$headline)
                                {
                                    
$headline = (string) $headline;
                                    if(!isset(
$string_list[$headline]))
                                    {
                                        
$string_list[$headline] = count($string_list);
                                    }
                                    
$sheetData .= '<c r="' $cell_name '" s="1" t="s"><v>' $string_list[$headline] . '</v></c>';
                                }
                            }
                            
$sheetData .= '</row>';
                        }
                        else
                        {
                            
$row_nr 0;
                        }
                        foreach(
$raw_sheet_data as $current_row)
                        {
                            
$row_nr++;
                            
$column_nr 0;
                            
$sheetData .= '<row collapsed="false" customFormat="false" customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="' $row_nr '">';
                            if(
is_array($current_row))
                            {
                                foreach(
$current_row as $current_cell)
                                {
                                    
$cell_name $this->excel_column_name($column_nr) . $row_nr;
                                    
$column_nr++;

                                    if(
$current_cell OR $current_cell === OR $current_cell === '0')
                                    {
                                        
$current_cell = (string) $current_cell;
                                        if(!isset(
$string_list[$current_cell]))
                                        {
                                            
$string_list[$current_cell] = count($string_list);
                                        }
                                        
$sheetData .= '<c r="' $cell_name '" s="0" t="s"><v>' $string_list[$current_cell] . '</v></c>';
                                    }
                                }
                            }
                            else
                            {
                                
trigger_error("Data row should be an array, found " getType($current_row) . "\n" print_r($current_rowTRUE));
                            }
                            
$sheetData .= '</row>';
                        }

                        if(
$string_list)
                        {
                            
$string_list_xml '';
                            foreach(
array_keys($string_list) as $string)
                            {
                                
$string_list_xml .= '<si><t>' str_replace(array('&''<''>'), array('&amp;''&lt;''&gt;'), $string) . '</t></si>';
                            }
                        }
                        else
                        {
                            
$string_list_xml '';
                        }
                        
                        
$zip->addFromString("xl/worksheets/sheet{$sheet_number}.xml"'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' PHP_EOL '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheetPr filterMode="false"><pageSetUpPr fitToPage="false"/></sheetPr><dimension ref="A1:Y2"/><sheetViews><sheetView colorId="64" defaultGridColor="true" rightToLeft="false" showFormulas="false" showGridLines="true" showOutlineSymbols="true" showRowColHeaders="true" showZeros="true" tabSelected="true" topLeftCell="A1" view="normal" windowProtection="false" workbookViewId="0" zoomScale="100" zoomScaleNormal="100" zoomScalePageLayoutView="100"><selection activeCell="A1" activeCellId="0" pane="topLeft" sqref="A1"/></sheetView></sheetViews><cols><col collapsed="false" hidden="false" max="1025" min="1" style="0" width="11.5764705882353"/></cols><sheetData>' $sheetData '</sheetData><printOptions headings="false" gridLines="false" gridLinesSet="true" horizontalCentered="false" verticalCentered="false"/><pageMargins left="0.7875" right="0.7875" top="1.05277777777778" bottom="1.05277777777778" header="0.7875" footer="0.7875"/><pageSetup blackAndWhite="false" cellComments="none" copies="1" draft="false" firstPageNumber="1" fitToHeight="1" fitToWidth="1" horizontalDpi="300" orientation="portrait" pageOrder="downThenOver" paperSize="9" scale="100" useFirstPageNumber="true" usePrinterDefaults="false" verticalDpi="300"/><headerFooter differentFirst="false" differentOddEven="false"><oddHeader>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12&amp;A</oddHeader><oddFooter>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12Page &amp;P</oddFooter></headerFooter></worksheet>');
                    }

                    
$count count($string_list);
                    
$zip->addFromString('xl/sharedStrings.xml''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' PHP_EOL '<sst count="' $count '" uniqueCount="' $count '" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">' $string_list_xml '</sst>');

                    
$zip->close();
                    
$data file_get_contents($temp_file);
                    
unlink($temp_file);
                    return 
$data;
                }

                
/* unknowned format, error */
                
default:
                {
                    return 
FALSE;
                }
            }
        }

        function 
send_header($type$filename NULL)
        {
            switch(
$type)
            {
                
/* Comma Seperated Values (delimited with ';', enclosed by '"')*/
                
case 'csv':
                case 
'old_csv':
                {
                    
header('Content-Type: text/csv');
                    if(
$filename)
                    {
                        
header('Content-Disposition: attachment; filename="' $filename '.csv"');
                    }
                    return 
TRUE;
                }

                
/* xml of 'microsoft office spreadsheet' also knowned as 'Microsoft Excel 2003 XML'*/
                
case 'xml':
                case 
'excel_xml':
                {
                    
header('Content-Type: text/xml');
                    if(
$filename)
                    {
                        
header('Content-Disposition: attachment; filename="' $filename '.xml"');
                    }
                    return 
TRUE;
                }

                case 
'xls':
                {
                    
header('Content-Type: application/vnd.ms-excel');
                    if(
$filename)
                    {
                        
header('Content-Disposition: attachment; filename="' $filename '.xls"');
                    }
                    return 
TRUE;
                }

                case 
'xlsx':
                case 
'xlsx_multi':
                {
                    
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                    if(
$filename)
                    {
                        
header('Content-Disposition: attachment; filename="' $filename '.xlsx"');
                    }
                    return 
TRUE;
                }

                
/* unknowned format, error */
                
default:
                {
                    return 
FALSE;
                }
            }
        }
        
        public function 
excel_column_name($column_nr)
        {
            static 
$charlist 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
            
$charlist_length strlen($charlist);
            
$name '';
            
$column_nr = (int) $column_nr;
            
$column_nr_part $column_nr $charlist_length;
            if(
$column_nr >= $charlist_length)
            {
                
$name $this->excel_column_name(($column_nr $column_nr_part)/$charlist_length -1);
            }
            return 
$name substr($charlist$column_nr_part1);
        }

//         /**
//         * export mysql-data to other formats
//         * @param $query sql-query (SELECT ...)
//         * @param $type the format to export to ('csv','old_csv','xml','html')
//         * @param $include_headers get a row with headers at top of the file, false to only get data
//         * @param $name the name of the worksheeat (xml only)
//         **/
//         public function export_by_query($query, $type = 'csv', $include_headers = TRUE, $name = 'Blad 1')
//         {
//             /* convert mysql-query to php-array, and then to exported format */
//             return $this->export($this->database()->read($query), $type, $include_headers, $name);
//         }
    
}