PHP ExcelWriter::writeLine方法代码示例

 function ImportData($sql, $fileName = "har_excel.xls", $download = false)
 {
     $excel = new ExcelWriter($fileName);
     if ($excel == false) {
         $this->error = $excel->error;
         return false;
     }
     $this->db->query($sql);
     if ($this->db->numRows() == 0) {
         $this->error = "No data found in the table";
         return false;
     }
     if ($row = $this->db->fetchAssoc()) {
         for ($i = 0; $i < count($row); $i++) {
             $fields[] = $this->db->fieldName($i);
         }
         $excel->writeLine($fields);
         do {
             $excel->writeLine($row);
         } while ($row = $this->db->fetchAssoc());
     }
     $excel->close();
     $this->db->close();
     if ($download) {
         if (!headers_sent()) {
             $this->download_file($fileName, true);
         } else {
             $this->error = "Error :Headers already Sent.Can't Download file.";
         }
     }
     return;
 }

 /**
  * Function generates a excel product report for google base
  * 
  * 
  * @return file
  */
 function googleProduct()
 {
     include "classes/Lib/excelwriter.inc.php";
     $excel = new ExcelWriter("GoogleBase_Product_Feed.xls");
     if ($excel == false) {
         echo $excel->error;
     }
     $myArr = array("Product Id", "Product Title", "Description", "Product Price", "Link", "Brand", "Image Link", "Weight");
     $excel->writeLine($myArr);
     $sql = 'select product_id,title,description,price,brand,thumb_image,weight from products_table';
     $obj = new Bin_Query();
     if ($obj->executeQuery($sql)) {
         $cnt = count($obj->records);
         for ($i = 0; $i < $cnt; $i++) {
             $product_id = $obj->records[$i]['product_id'];
             $title = $obj->records[$i]['title'];
             $description = strip_tags($obj->records[$i]['description']);
             $price = $obj->records[$i]['price'];
             $brand = $obj->records[$i]['brand'];
             $thumb_image = $obj->records[$i]['thumb_image'];
             $image_link = $_SERVER['SERVER_NAME'] . '/' . $thumb_image;
             $weight = $obj->records[$i]['weight'];
             $link = $_SERVER['SERVER_NAME'] . '/?do=prodetail&action=showprod&id=' . $product_id;
             $excel->writeRow();
             $excel->writeCol($product_id);
             $excel->writeCol($title);
             $excel->writeCol($description);
             $excel->writeCol($price);
             $excel->writeCol($link);
             $excel->writeCol($brand);
             $excel->writeCol($image_link);
             $excel->writeCol($weight);
         }
         $excel->close();
     }
     if (strpos($_SERVER['USER'], 'MSIE')) {
         // IE cannot download from sessions without a cache
         header('Cache-Control: public');
     } else {
         //header("Cache-Control: no-cache, must-revalidate");
         header("Cache-Control: no-cache");
     }
     $file = "GoogleBase_Product_Feed.xls";
     //chmod ($file, 0755);
     header("Pragma: no-cache");
     header("Content-Type: php/doc/xml/html/htm/asp/jpg/JPG/sql/txt/jpeg/gif/bmp/png/xls/csv/x-ms-asf\n");
     header("Connection: close");
     header("Content-Disposition: attachment; filename=" . $file . "\n");
     header("Content-Transfer-Encoding: binary\n");
     header("Content-length: " . (string) filesize("{$file}"));
     $fd = fopen($file, "rb");
     fpassthru($fd);
 }

 /**
  * Function generates a product details in an excel/xml/csv/tsv format file
  * 
  * 
  * @return file
  */
 function productDataExport()
 {
     if ($_POST['export'] == 'excel') {
         include "classes/Lib/excelwriter.inc.php";
         $excel = new ExcelWriter("Product_Detail.xls");
         if ($excel == false) {
             echo $excel->error;
         }
         $myArr = array("SlNo", "Title", "Description", "Brand", "Model", "Msrp", "Price", "Shipping Cost", "Intro Date");
         $excel->writeLine($myArr);
         $j = 1;
         $sql = 'select sku,title,description,brand,model,msrp,price,shipping_cost,intro_date from products_table';
         $obj = new Bin_Query();
         if ($obj->executeQuery($sql)) {
             $cnt = count($obj->records);
             for ($i = 0; $i < $cnt; $i++) {
                 $title = $obj->records[$i]['title'];
                 $description = $obj->records[$i]['description'];
                 $brand = $obj->records[$i]['brand'];
                 $model = $obj->records[$i]['model'];
                 $msrp = $obj->records[$i]['msrp'];
                 $price = $obj->records[$i]['price'];
                 $shipping_cost = $obj->records[$i]['shipping_cost'];
                 $intro_date = $obj->records[$i]['intro_date'];
                 //$doj =  $obj->records[$i]['user_doj'];
                 $excel->writeRow();
                 $excel->writeCol($j);
                 $excel->writeCol($title);
                 $excel->writeCol($description);
                 $excel->writeCol($brand);
                 $excel->writeCol($model);
                 $excel->writeCol($msrp);
                 $excel->writeCol($price);
                 $excel->writeCol($shipping_cost);
                 $excel->writeCol($intro_date);
                 //$excel->writeCol($doj);
                 $j++;
             }
             $excel->close();
         }
         if (strpos($_SERVER['USER'], 'MSIE')) {
             // IE cannot download from sessions without a cache
             header('Cache-Control: public');
         } else {
             //header("Cache-Control: no-cache, must-revalidate");
             header("Cache-Control: no-cache");
         }
         $file = "Product_Detail.xls";
         //chmod ($file, 0755);
         header("Pragma: no-cache");
         header("Content-Type: php/doc/xml/html/htm/asp/jpg/JPG/sql/txt/jpeg/gif/bmp/png/xls/csv/x-ms-asf\n");
         header("Connection: close");
         header("Content-Disposition: attachment; filename=" . $file . "\n");
         header("Content-Transfer-Encoding: binary\n");
         header("Content-length: " . (string) filesize("{$file}"));
         $fd = fopen($file, "rb");
         fpassthru($fd);
         /*function xlsBOF() 
         			{
         				echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);  
         				return;
         			}
         	
         			function xlsEOF() 
         			{
         				echo pack("ss", 0x0A, 0x00);
         				return;
         			}
         	
         			function xlsWriteNumber($Row, $Col, $Value) 
         			{
         				echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
         				echo pack("d", $Value);
         				return;
         			}
         	
         			function xlsWriteLabel($Row, $Col, $Value ) 
         			{
         				$L = strlen($Value);
         				echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
         				echo $Value;
         				return;
         			}
         			//Send header
         			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=user_report.xls"); 
         			header("Content-Transfer-Encoding: binary ");
         			xlsBOF();
         					xlsWriteLabel(1,0,"No");
//.........这里部分代码省略.........

    $outputFileName = 'data/excel/' . $datename . '.xls';
    $content = array();
    $archive = array("接收人", "发送人", "发送时间", "内容");
    $content[] = $archive;
    $wheresql = '';
    $vuidtype = getGP('vuidtype', 'P');
    if (!is_superadmin() && $vuidtype == '') {
        $wheresql .= " AND uid ='" . $_USER->id . "'";
    }
    if ($vuidtype != '') {
        if ($vuidtype == '-1') {
            $wheresql .= get_subordinate($_USER->id, 'uid');
        } else {
            $wheresql .= " and uid='" . $vuidtype . "'";
        }
    }
    $sql = "SELECT * FROM " . DB_TABLEPRE . "sms_send WHERE 1 {$wheresql} ORDER BY id desc";
    $result = $db->query($sql);
    while ($row = $db->fetch_array($result)) {
        $archive = array("" . $row['receiveperson'] . "", "" . get_realname($row['uid']) . "", "" . str_replace("-", ".", $row[date]) . "", "" . $row['content'] . "");
        $content[] = $archive;
    }
    $excel = new ExcelWriter($outputFileName);
    if ($excel == false) {
        echo $excel->error;
    }
    foreach ($content as $v) {
        $excel->writeLine($v);
    }
    $excel->sendfile($outputFileName);
}

$relatorioDAO = new RelatorioDAO();
echo '<pre>';
$empresas = $empresaDAO->listarTodas();
foreach ($empresas as $emp) {
    $id_empresa = $emp->id_empresa;
    echo "\n > " . $emp->fantasia . " ";
    $nomeArquivo = 'conciliacao_franquia' . date("Ymd") . "_" . $id_empresa . ".xls";
    $arquivoDiretorio = "../relatorios/conciliacao_franquia/" . $nomeArquivo;
    $excel = new ExcelWriter($arquivoDiretorio);
    if ($excel == false) {
        echo $excel->error;
        exit;
    }
    //Unidade
    $myArr = array('Relação de pedidos em conciliação franquia:' . $emp->fantasia);
    $excel->writeLine($myArr);
    //periodo
    $myArr = array('Até: ' . date('d') . '/' . date('m') . '/' . date('Y'));
    $excel->writeLine($myArr);
    //espaço
    $myArr = array(' ');
    $excel->writeLine($myArr);
    //Escreve o nome dos campos de uma tabela
    $excel->writeLine(array('PEDIDO', 'ABERTO HÁ (dias) ', 'ATENDENTE'));
    $total_comissao = 0;
    $total = 0;
    $cont = 0;
    $old_id_usuario = '';
    $pedidos = $pedidoDAO->listarConciliacaoFranquia($emp->id_empresa);
    foreach ($pedidos as $pe) {
        $diff = time() - strtotime($pe->data_atividade);

 /**
  * Function exports the admin activity report from the database into various file format
  * Excel/CSV/TSV/XML Formats
  * 
  * @return file
  */
 function adminActivityDataExport()
 {
     if ($_POST['export'] == 'excel') {
         include "classes/Lib/excelwriter.inc.php";
         $excel = new ExcelWriter("adminactivity_Detail.xls");
         if ($excel == false) {
             echo $excel->error;
         }
         $myArr = array("SlNo", "UserId", "Url", "VisitedOn");
         $excel->writeLine($myArr);
         $j = 1;
         $sql = 'select user_id,url,visited_on from admin_activity_table';
         $obj = new Bin_Query();
         $obj->executeQuery($sql);
         if ($obj->executeQuery($sql)) {
             $cnt = count($obj->records);
             for ($i = 0; $i < $cnt; $i++) {
                 $user_id = $obj->records[$i]['user_id'];
                 $url = $obj->records[$i]['url'];
                 $visited_on = $obj->records[$i]['visited_on'];
                 $excel->writeRow();
                 $excel->writeCol($j);
                 $excel->writeCol($user_id);
                 $excel->writeCol($url);
                 $excel->writeCol($visited_on);
                 $j++;
             }
             $excel->close();
         }
         if (strpos($_SERVER['USER'], 'MSIE')) {
             // IE cannot download from sessions without a cache
             header('Cache-Control: public');
         } else {
             //header("Cache-Control: no-cache, must-revalidate");
             header("Cache-Control: no-cache");
         }
         $file = "adminactivity_Detail.xls";
         //chmod ($file, 0755);
         header("Pragma: no-cache");
         header("Content-Type: php/doc/xml/html/htm/asp/jpg/JPG/sql/txt/jpeg/gif/bmp/png/xls/csv/x-ms-asf\n");
         header("Connection: close");
         header("Content-Disposition: attachment; filename=" . $file . "\n");
         header("Content-Transfer-Encoding: binary\n");
         header("Content-length: " . (string) filesize("{$file}"));
         $fd = fopen($file, "rb");
         fpassthru($fd);
         /*function xlsBOF() 
         			{
         				echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);  
         				return;
         			}
         	
         			function xlsEOF() 
         			{
         				echo pack("ss", 0x0A, 0x00);
         				return;
         			}
         	
         			function xlsWriteNumber($Row, $Col, $Value) 
         			{
         				echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
         				echo pack("d", $Value);
         				return;
         			}
         	
         			function xlsWriteLabel($Row, $Col, $Value ) 
         			{
         				$L = strlen($Value);
         				echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
         				echo $Value;
         				return;
         			}
         			//Send header
         			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=user_report.xls"); 
         			header("Content-Transfer-Encoding: binary ");
         			xlsBOF();
         					xlsWriteLabel(1,0,"No");
         					xlsWriteLabel(1,1,"First Name");
         					xlsWriteLabel(1,2,"Last Name");
         					xlsWriteLabel(1,3,"Display Name");
         					xlsWriteLabel(1,4,"Email");
         					xlsWriteLabel(1,5,"Date of Joining");
         								
         				   $xlsRow = 2;
         				   $j=1;
         					
         					//Query
         					
//.........这里部分代码省略.........

}
if ($c->c_final_contrato == 'on') {
    $campos_header[] = 'FINAL DE CONTRATO';
    $campo_total[] = '';
}
if ($c->c_royalties == 'on') {
    $campos_header[] = "ROYALTIES";
    $campos_header[] = "MES/ANO";
}
if ($c->c_observacoes == 'on') {
    $campos_header[] = "OBSERVACOES";
}
$nomeArquivo = 'franquia_' . date("Ym") . "_" . $controle_id_empresa . ".xls";
$arquivoDiretorio = "../relatorios/cadastrados/" . $nomeArquivo;
$excel = new ExcelWriter($arquivoDiretorio);
$excel->writeLine($campos_header);
$financeiroDAO = new FinanceiroDAO();
$lista = $financeiroDAO->relRoyaltiesEmAberto($c);
$id_empresa = 0;
foreach ($lista as $res) {
    $campos = array();
    if ($id_empresa != $res->id_empresa && $id_empresa > 0) {
        $campo_total[] = number_format((double) $valor_total, 2, '.', '');
        $excel->writeLine($campo_total);
        $excel->writeRow();
        $excel->writeRow();
        $valor_total = 0;
    }
    $campo_total = array();
    if ($c->c_royalties == 'on') {
        $campo_total[] = 'Total';

 /**
  * Function generates a report in the file format. 
  * @param array  $sql
  * 
  * @return file
  */
 function createReport($sql)
 {
     if ($_POST['export'] == 'excel') {
         include "classes/Lib/excelwriter.inc.php";
         $excel = new ExcelWriter("User_Detail.xls");
         if ($excel == false) {
             $excel->error;
         }
         $myArr = array("No", "Display Name", "First Name", "Last Name", "Email", "Address", "City", "State", "Zip Code", "Country");
         $excel->writeLine($myArr);
         $j = 1;
         $sql = 'select * from users_table';
         $obj = new Bin_Query();
         if ($obj->executeQuery($sql)) {
             $cnt = count($obj->records);
             for ($i = 0; $i < $cnt; $i++) {
                 $sqlAdd = "SELECT a.*,b.cou_code,b.cou_name FROM addressbook_table AS a LEFT JOIN country_table AS b ON b.cou_code=a.country   WHERE a.user_id='" . $obj->records[$i]['user_id'] . "'";
                 $objAdd = new Bin_Query();
                 $objAdd->executeQuery($sqlAdd);
                 $display_name = $obj->records[$i]['user_display_name'];
                 $first_name = $obj->records[$i]['user_fname'];
                 $last_name = $obj->records[$i]['user_lname'];
                 $email = $obj->records[$i]['user_email'];
                 $address = $objAdd->records[0]['address'];
                 $city = $objAdd->records[0]['city'];
                 $state = $objAdd->records[0]['state'];
                 $zip = $objAdd->records[0]['zip'];
                 $country = $objAdd->records[0]['cou_name'];
                 $doj = $obj->records[$i]['user_doj'];
                 $excel->writeRow();
                 $excel->writeCol($j);
                 $excel->writeCol($display_name);
                 $excel->writeCol($first_name);
                 $excel->writeCol($last_name);
                 $excel->writeCol($email);
                 $excel->writeCol($address);
                 $excel->writeCol($city);
                 $excel->writeCol($state);
                 $excel->writeCol($zip);
                 $excel->writeCol($country);
                 $excel->writeCol($doj);
                 $j++;
             }
             $excel->close();
         }
         if (strpos($_SERVER['USER'], 'MSIE')) {
             // IE cannot download from sessions without a cache
             header('Cache-Control: public');
         } else {
             //header("Cache-Control: no-cache, must-revalidate");
             header("Cache-Control: no-cache");
         }
         $file = "User_Detail.xls";
         //chmod ($file, 0755);
         header("Pragma: no-cache");
         header("Content-Type: php/doc/xml/html/htm/asp/jpg/JPG/sql/txt/jpeg/gif/bmp/png/xls/csv/x-ms-asf\n");
         header("Connection: close");
         header("Content-Disposition: attachment; filename=" . $file . "\n");
         header("Content-Transfer-Encoding: binary\n");
         header("Content-length: " . (string) filesize("{$file}"));
         $fd = fopen($file, "rb");
         fpassthru($fd);
     } else {
         if ($_POST['export'] == 'xml') {
             $sqlselect = "select user_id,user_fname,user_lname,user_display_name,user_email,user_doj from users_table";
             $obj = new Bin_Query();
             if ($obj->executeQuery($sqlselect)) {
                 header("Content-Type: text/xml");
                 header("Pragma: public");
                 header("Expires: 0");
                 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
                 header("Content-Type: xml/force-download");
                 header("Content-Type: xml/octet-stream");
                 header("Content-Type: xml/download");
                 header("Content-Disposition: attachment;filename=user_report.xml");
                 header("Content-Transfer-Encoding: binary ");
                 echo "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n";
                 echo "<userdetails>\n";
                 $count = count($obj->records);
                 for ($i = 0; $i < $count; $i++) {
                     $sqlAdd = "SELECT a.*,b.cou_code,b.cou_name FROM addressbook_table AS a LEFT JOIN country_table AS b ON b.cou_code=a.country   WHERE a.user_id='" . $obj->records[$i]['user_id'] . "'";
                     $objAdd = new Bin_Query();
                     $objAdd->executeQuery($sqlAdd);
                     echo "<userid>" . $obj->records[$i]['user_id'] . "</userid>\n";
                     echo "<displayname>" . $obj->records[$i]['user_display_name'] . "</displayname>\n";
                     echo "<firstname>" . $obj->records[$i]['user_fname'] . "</firstname>\n";
                     echo "<lastname>" . $obj->records[$i]['user_lname'] . "</lastname>\n";
                     echo "<email>" . $obj->records[$i]['user_email'] . "</email>\n";
                     echo "<address>" . $objAdd->records[0]['address'] . "</address>\n";
                     echo "<city>" . $objAdd->records[0]['city'] . "</city>\n";
                     echo "<state>" . $objAdd->records[0]['state'] . "</state>\n";
                     echo "<zipcode>" . $objAdd->records[0]['zip'] . "</zipcode>\n";
                     echo "<country>" . $objAdd->records[0]['cou_name'] . "</country>\n";
                     echo "<userdoj>" . $obj->records[$i]['user_doj'] . "</userdoj>\n";
//.........这里部分代码省略.........

 public function export()
 {
     if ($this->Session->check('id')) {
         App::import('Vendor', 'ExcelWriter');
         $date = date("Y-m-d");
         $this->loadModel('Sticker');
         $this->loadModel('Categorie');
         $cond = "Sticker.id=Favsticker.sticker_id";
         $conditions = "GROUP BY Favsticker.sticker_id HAVING count(Favsticker.sticker_id) >= 2";
         $data = $this->Sticker->find('all', array('fields' => array('Sticker.id', 'Sticker.name', 'Sticker.image', 'Sticker.type_id', 'Sticker.category_id', 'Sticker.created', 'Sticker.modified'), 'joins' => array(array('table' => 'favstickers', 'alias' => 'Favsticker', 'type' => 'inner', 'conditions' => $cond)), 'conditions' => $conditions));
         foreach ($data as $key => $value) {
             $f = $this->Categorie->find('first', array('fields' => 'name', 'conditions' => array('id' => $value['Sticker']['category_id'])));
             $data[$key]['Sticker']['category_name'] = $f['Categorie']['name'];
         }
         $fileName = WWW_ROOT . "files" . DS . "Excel" . DS . "PopularSticker_Report" . "_" . $date . ".xls";
         $this->autoRender = false;
         $excel = new ExcelWriter($fileName);
         $excel->writeCol("S. No.");
         $excel->writeCol("Name");
         $excel->writeCol("Category");
         $excel->writeCol("Type");
         $excel->writeCol("Created");
         $excel->writeCol("Modified");
         foreach ($data as $key => $value) {
             if ($key == 0) {
                 $excel->writeLine(array(), array());
             }
             $excel->writeCol($key + 1, array());
             $excel->writeCol(ucfirst($value['Sticker']['name']));
             $excel->writeCol(ucfirst($value['Sticker']['category_name']));
             if ($value['Sticker']['type_id'] == 1) {
                 $excel->writeCol("Free");
             } else {
                 $excel->writeCol("Paid");
             }
             $excel->writeCol(date("F j, Y, g:i A", strtotime($value['Sticker']['created'])));
             $excel->writeCol(date("F j, Y, g:i A", strtotime($value['Sticker']['modified'])));
             $excel->writeLine(array(), array());
         }
         $this->redirect(DS . "files" . DS . "Excel" . DS . "PopularSticker_Report" . "_" . $date . ".xls");
     } else {
         $this->redirect(array('controller' => 'Users', 'action' => 'index'));
     }
 }

function write_xls($sql)
{
    $sql = str_replace('\\', '', $sql);
    $excel = new ExcelWriter('excel_report/' . date('m_d_Y_s') . '_feedback_report.xls');
    $file_name = date('m_d_Y_s') . '_feedback_report.xls';
    if ($excel == false) {
        echo $excel->error;
    }
    $result1 = mysql_query($sql) or die(mysql_error());
    $arr1 = mysql_fetch_array($result1);
    if (mysql_num_rows($result1) != 0) {
        $myArr = array("Branch", branch_name($arr1['b_id']), "", "Semester", sem_name($arr1['sem_id']));
        $excel->writeLine($myArr);
        $myArr = array("", "", "", "", "", "");
        $excel->writeLine($myArr);
        $myArr = array("Batch", batch_name($arr1['batch_id']), "", "Division", division_name($arr1['division_id']), "", "Feedback No", $arr1['feedback_no']);
        $excel->writeLine($myArr);
        $myArr = array("", "", "", "", "", "");
        $excel->writeLine($myArr);
        $myArr = array("Faculty Name:", faculty_name($arr1['f_id']), "", "Subject:", subject_name($arr1['sub_id']));
        $excel->writeLine($myArr);
        $myArr = array("", "", "", "", "", "");
        $excel->writeLine($myArr);
        $myArr = array("", "", "", "", "", "");
        $excel->writeLine($myArr);
        /*$myArr=array("Remark(s)");//"Ans1","Ans2","Ans3","Ans4","Ans5","Ans6","Ans7","Ans8","Ans9"
        		$excel->writeLine($myArr);
        		$myArr=array("","","","","","");
        		$excel->writeLine($myArr);*/
        $myArr = array("Ans1", "Ans2", "Ans3", "Ans4", "Ans5", "Ans6", "Ans7", "Ans8", "Ans9", "Remark");
        $excel->writeLine($myArr);
        $result = mysql_query($sql) or die(mysql_error());
        $total_ids = "0";
        $r_id = 1;
        while ($arr = mysql_fetch_array($result)) {
            /*if($arr['remark']!=NULL)
            		{	
            			$myArr=array(strtolower($arr['remark']));				
            			$excel->writeLine($myArr);
            			$r_id++;			
            		}*/
            $myArr = array($arr['ans1'], $arr['ans2'], $arr['ans3'], $arr['ans4'], $arr['ans5'], $arr['ans6'], $arr['ans7'], $arr['ans8'], $arr['ans9'], $arr['remark']);
            $excel->writeLine($myArr);
            $r_id++;
        }
        $myArr = array("", "", "", "", "", "");
        $excel->writeLine($myArr);
        $excel->close();
        return $file_name;
    } else {
        echo '<p align=center>No Record Found!.</p>';
    }
}

}
pt_register('POST', 'id_empresa');
pt_register('POST', 'crescimento');
$empresaDAO = new EmpresaDAO();
$emp = $empresaDAO->selectPorId($id_empresa);
$arquivoDiretorio = "./exporta/" . $controle_id_usuario . ".xls";
$nomeArquivo = $controle_id_usuario . ".xls";
$excel = new ExcelWriter($arquivoDiretorio);
if ($excel == false) {
    echo $excel->error;
    exit;
}
//Escreve o nome dos campos de uma tabela
$linha_arq = 'Relatorio de Planejamento Econômico Financeiro - Franquia ' . $emp->fantasia . ' - Taxa de Crescimento de ' . $crescimento . '%;';
$myArr = explode(';', $linha_arq);
$excel->writeLine($myArr);
$linha_arq = 'ANO I;Operacionais;';
$myArr = explode(';', $linha_arq);
$excel->writeLine($myArr);
#inicio do ano I
$linha_arq = ';1;2;3;4;5;6;7;8;9;10;11;12';
$myArr = explode(';', $linha_arq);
$excel->writeLine($myArr);
$linha_arq = 'MES';
$cont = 0;
$linha_dias_uteis = 'DIAS UTEIS';
$linha_despesa = 'DESPESAS';
$linha_vendas = 'VENDAS';
$linha_lucro = 'LUCRO';
$linha_operacoes = 'OPERAÇÕES DO MÊS';
$linha_pedido_medio = 'TICKET MÉDIO';

 public function builtXLS()
 {
     $this->load->language('sale/customer');
     $this->load->model('sale/customer');
     $url = '';
     if (isset($this->request->get['filter_name'])) {
         $url .= '&filter_name=' . urlencode(html_entity_decode($this->request->get['filter_name'], ENT_QUOTES, 'UTF-8'));
     }
     if (isset($this->request->get['filter_email'])) {
         $url .= '&filter_email=' . urlencode(html_entity_decode($this->request->get['filter_email'], ENT_QUOTES, 'UTF-8'));
     }
     if (isset($this->request->get['filter_customer_group_id'])) {
         $url .= '&filter_customer_group_id=' . $this->request->get['filter_customer_group_id'];
     }
     if (isset($this->request->get['filter_status'])) {
         $url .= '&filter_status=' . $this->request->get['filter_status'];
     }
     if (isset($this->request->get['filter_birthday'])) {
         $url .= '&filter_birthday=' . $this->request->get['filter_birthday'];
     }
     if (isset($this->request->get['filter_ip'])) {
         $url .= '&filter_ip=' . $this->request->get['filter_ip'];
     }
     if (isset($this->request->get['filter_date_added'])) {
         $url .= '&filter_date_added=' . $this->request->get['filter_date_added'];
     }
     if (isset($this->request->get['sort'])) {
         $url .= '&sort=' . $this->request->get['sort'];
     }
     if (isset($this->request->get['order'])) {
         $url .= '&order=' . $this->request->get['order'];
     }
     if (isset($this->request->get['page'])) {
         $url .= '&page=' . $this->request->get['page'];
     }
     if (isset($this->request->get['pdv'])) {
         $url .= '&pdv=' . $this->request->get['pdv'];
     }
     require_once DIR_SYSTEM . "library/excel/excelwriter.inc.php";
     $name_file = DIR_APPLICATION . 'controller/sale/customer_report/customer_report.xls';
     if (isset($this->request->get['filter_name'])) {
         $filter_name = $this->request->get['filter_name'];
     } else {
         $filter_name = null;
     }
     if (isset($this->request->get['filter_email'])) {
         $filter_email = $this->request->get['filter_email'];
     } else {
         $filter_email = null;
     }
     if (isset($this->request->get['filter_customer_group_id'])) {
         $filter_customer_group_id = $this->request->get['filter_customer_group_id'];
     } else {
         $filter_customer_group_id = null;
     }
     if (isset($this->request->get['filter_status'])) {
         $filter_status = $this->request->get['filter_status'];
     } else {
         $filter_status = null;
     }
     if (isset($this->request->get['filter_birthday'])) {
         $filter_birthday = $this->request->get['filter_birthday'];
     } else {
         $filter_birthday = null;
     }
     if (isset($this->request->get['filter_ip'])) {
         $filter_ip = $this->request->get['filter_ip'];
     } else {
         $filter_ip = null;
     }
     if (isset($this->request->get['filter_date_added'])) {
         $filter_date_added = $this->request->get['filter_date_added'];
     } else {
         $filter_date_added = null;
     }
     if (isset($this->request->get['sort'])) {
         $sort = $this->request->get['sort'];
     } else {
         $sort = 'name';
     }
     if (isset($this->request->get['order'])) {
         $order = $this->request->get['order'];
     } else {
         $order = 'ASC';
     }
     $filter_data = array('filter_name' => $filter_name, 'filter_email' => $filter_email, 'filter_customer_group_id' => $filter_customer_group_id, 'filter_status' => $filter_status, 'filter_birthday' => $filter_birthday, 'filter_date_added' => $filter_date_added, 'filter_ip' => $filter_ip, 'sort' => $sort, 'order' => $order);
     $results = $this->model_sale_customer->getCustomers($filter_data);
     $excel = new ExcelWriter($name_file);
     if (count($results) > 0) {
         $myArr = array('Nome', 'Email', 'Sexo', 'Data de Aniversário', 'Data de Cadastro');
         $excel->writeLine($myArr, 'title');
         foreach ($results as $result) {
             if ($result['sex'] == 'F') {
                 $sex = 'Mulher';
             } else {
                 $sex = 'Homem';
             }
             $myArr = array($result['firstname'] . ' ' . $result['lastname'], $result['email'], $sex, date('d/m/Y', strtotime($result['birthday'])), date('d/m/Y H:i:s', strtotime($result['date_added'])));
             $excel->writeLine($myArr, 'rows');
         }
//.........这里部分代码省略.........

//.........这里部分代码省略.........
             } elseif ($request['Appusers']['sort'] == 5) {
                 if ($request['Appusers']['sorttype'] == 1) {
                     $order = "`Appuser`.`created` ASC";
                 } else {
                     $order = "`Appuser`.`created` DESC";
                 }
             } else {
                 $order = "`Appuser`.`email` ASC";
             }
             $result = $this->Appuser->query("SELECT  `Appuser`.`id`, `Appuser`.`zip`, `Appuser`.`city`, `Appuser`.`email`, `Appuser`.`country`, `Appuser`.`created`, `Appuser`.`is_active`,( SELECT  SUM(`count`) FROM  digistic_stickerapp.stickeruses WHERE  user_id = `Appuser`.`id` ) AS totalused,( SELECT  COUNT(*) FROM  digistic_stickerapp.devices WHERE  user_id = `Appuser`.`id` ) AS tdevice,( SELECT COUNT(`id`) FROM digistic_stickerapp.purchasedstickers WHERE user_id=`Appuser`.`id` ) AS pstickers FROM  digistic_stickerapp.`appusers` AS Appuser {$conditions} GROUP BY  `Appuser`.`id` ORDER BY {$order}");
         }
         // pr($result);die;
         if ($request['Appusers']['type'] == 1 || $request['Appusers']['type'] == 2) {
             if (empty($result)) {
                 $this->Session->setFlash("No Data Found", "flash_custom");
                 $this->redirect(array('controller' => 'Reports'));
             }
         }
         if ($request['Appusers']['type'] == 1 || $request['Appusers']['type'] == 2) {
             $fileName = WWW_ROOT . "files" . DS . "Excel" . DS . "Appuser_Report" . "_" . $reportname . "_" . $date . ".xls";
             $excel = new ExcelWriter($fileName);
             $excel->writeCol("S. No.", array('font-weight' => 'bold'));
             $excel->writeCol("Email", array('font-weight' => 'bold'));
             $excel->writeCol("Activation Status", array('font-weight' => 'bold'));
             $excel->writeCol("ZIP", array('font-weight' => 'bold'));
             $excel->writeCol("City", array('font-weight' => 'bold'));
             $excel->writeCol("Country", array('font-weight' => 'bold'));
             $excel->writeCol("Total Stickers Used", array('font-weight' => 'bold'));
             $excel->writeCol("Total Devices", array('font-weight' => 'bold'));
             $excel->writeCol("Total Stickers Purchased", array('font-weight' => 'bold'));
             $excel->writeCol("Date Registered", array('font-weight' => 'bold'));
             foreach ($result as $key => $data) {
                 if ($key == 0) {
                     $excel->writeLine(array(), array());
                 }
                 $excel->writeCol($key + 1, array());
                 $excel->writeCol($data['Appuser']['email']);
                 if ($data['Appuser']['is_active'] == 1) {
                     $excel->writeCol("Active");
                 } else {
                     $excel->writeCol("Inactive");
                 }
                 if (!empty($data['Appuser']['zip'])) {
                     $excel->writeCol($data['Appuser']['zip']);
                 } else {
                     $excel->writeCol("Not Available");
                 }
                 $excel->writeCol($data['Appuser']['city']);
                 $excel->writeCol($data['Appuser']['country']);
                 if (!empty($data['0']['totalused'])) {
                     $excel->writeCol($data['0']['totalused']);
                 } else {
                     $excel->writeCol('None');
                 }
                 if (!empty($data['0']['tdevice'])) {
                     $excel->writeCol($data['0']['tdevice']);
                 } else {
                     $excel->writeCol('None');
                 }
                 if (!empty($data['0']['pstickers'])) {
                     $excel->writeCol($data['0']['pstickers']);
                 } else {
                     $excel->writeCol('None');
                 }
                 $excel->writeCol(date("F j, Y, g:i A", strtotime($data['Appuser']['created'])));
                 $excel->writeLine(array());

<?php

include "excelwriter.inc.php";
$excel = new ExcelWriter("abc.xls");
if ($excel == false) {
    echo $excel->error;
    exit;
}
//write header
$header = array("<b>Col-1</b>", "<b>Col-2</b>", "Col-3");
//print header
$excel->writeLine($header);
//fetch all data from db according to requirement
while (true) {
    $excel->writeRow();
    $excel->writeCol("{$variable}-for-col-1");
    $excel->writeCol("{$variable}-for-col-3");
    $excel->writeCol("{$variable}-for-col-3");
}
$excel->close();
$file = "abc.xls";
if (file_exists($file)) {
    header('Content-Description: File Transfer');
    header("Content-Type: application/vnd.ms-excel");
    header('Content-Disposition: attachment; filename=' . basename($file));
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Pragma: public');
    header('Content-Length: ' . filesize($file));
    ob_clean();

 public function purchasedexport()
 {
     if ($this->Session->check('id')) {
         $this->autoRender = false;
         App::import('Vendor', 'ExcelWriter');
         $date = date("Y-m-d");
         $this->loadModel('Purchasedsticker');
         $request = $this->request->data;
         // echo "Work in Progress \n";
         // pr($request);die;
         $conditions = array('Purchasedsticker.by_admin' => 0);
         if ($request['Stickers']['type'] == 1) {
             $reportname = "FULL";
             if ($request['Stickers']['reporttype'] == 1) {
                 $conditions = "Purchasedsticker.by_admin=0";
             } elseif ($request['Stickers']['reporttype'] == 2) {
                 $conditions = "Purchasedsticker.by_admin=1";
             } elseif ($request['Stickers']['reporttype'] == 3) {
                 $conditions = array();
             }
         } elseif ($request['Stickers']['type'] == 2) {
             $reportname = "By_DATE";
             $from = $request['Stickers']['from'];
             $to = $request['Stickers']['to'];
             $conditions = "WHERE Purchasedsticker.created >= '" . date($from . ' 00:00:00') . "' AND Purchasedsticker.created <='" . date($to . ' 23:59:59') . "' ";
             if ($request['Stickers']['reporttype'] == 1) {
                 $conditions .= " AND Purchasedsticker.by_admin=0";
             } elseif ($request['Stickers']['reporttype'] == 2) {
                 $conditions .= " AND Purchasedsticker.by_admin=1";
             } elseif ($request['Stickers']['reporttype'] == 3) {
             }
         }
         // pr($conditions);die;
         if ($request['Stickers']['sort'] == 1) {
             if ($request['Stickers']['sorttype'] == 1) {
                 $order = "Sticker.name ASC";
             } else {
                 $order = "Sticker.name DESC";
             }
         } elseif ($request['Stickers']['sort'] == 2) {
             if ($request['Stickers']['sorttype'] == 1) {
                 $order = "Categorie.name ASC";
             } else {
                 $order = "Categorie.name DESC";
             }
         } elseif ($request['Stickers']['sort'] == 5) {
             if ($request['Stickers']['sorttype'] == 1) {
                 $order = "total ASC";
             } else {
                 $order = "total DESC";
             }
         } else {
             $order = "Sticker.name ASC";
         }
         $cond = "`Sticker`.`id`=`Purchasedsticker.sticker_id`";
         $cond2 = "`Categorie`.`id`=`Sticker`.`category_id`";
         $result = $this->Purchasedsticker->find('all', array('fields' => array('Sticker.id', 'Sticker.name', 'Sticker.category_id', 'Sticker.type_id', 'Sticker.is_active', 'Sticker.is_deleted', 'Sticker.created', 'Sticker.modified', 'Purchasedsticker.created', 'Categorie.name', 'COUNT(`Purchasedsticker`.`sticker_id`) AS total'), 'joins' => array(array('table' => 'stickers', 'alias' => 'Sticker', 'type' => 'left', 'conditions' => $cond), array('table' => 'categories', 'alias' => 'Categorie', 'type' => 'left', 'conditions' => $cond2)), 'conditions' => $conditions, 'order' => $order, 'group' => 'Sticker.id'));
         // $log = $this->Purchasedsticker->getDataSource()->getLog(false, false);
         // debug($log);
         // foreach($result as $key=>$value){
         //
         // }
         // echo "Work in progress.\n";
         //
         // pr($result);die;
         if (empty($result)) {
             $this->Session->setFlash("No Data Found", "flash_custom");
             $this->redirect(array('controller' => 'Reports'));
         }
         $fileName = WWW_ROOT . "files" . DS . "Excel" . DS . "Purchased_Stickers_Report" . "_" . $reportname . "_" . $date . ".xls";
         $excel = new ExcelWriter($fileName);
         $excel->writeCol("S. No.", array('font-weight' => 'bold'));
         $excel->writeCol("Name", array('font-weight' => 'bold'));
         $excel->writeCol("Activation Status", array('font-weight' => 'bold'));
         $excel->writeCol("Category", array('font-weight' => 'bold'));
         if ($request['Stickers']['reporttype'] == 2) {
             $excel->writeCol("Times Given", array('font-weight' => 'bold'));
         } else {
             $excel->writeCol("Times Purchased", array('font-weight' => 'bold'));
         }
         foreach ($result as $key => $data) {
             if ($key == 0) {
                 $excel->writeLine(array(), array());
             }
             $excel->writeCol($key + 1, array());
             $excel->writeCol($data['Sticker']['name']);
             if ($data['Sticker']['is_active'] == 1) {
                 $excel->writeCol("Active");
             } else {
                 $excel->writeCol("Inactive");
             }
             $excel->writeCol($data['Categorie']['name']);
             $excel->writeCol($data['0']['total']);
             $excel->writeLine(array());
         }
         $this->redirect(DS . "files" . DS . "Excel" . DS . "Purchased_Stickers_Report" . "_" . $reportname . "_" . $date . ".xls");
     } else {
         $this->redirect(array('controller' => 'Users', 'action' => 'index'));
     }
 }