Konu: MYSQL den EXCEL veri çekme

Merhaba Arkadaşlar benim şöyle bir sorunu var yardımcı olursanız sevinirim,
internette bulduğum kodları düzenleyerek istediğim gibi msql den verileri excel e çekmeyi başardım ancak nasıl oluyorsa sadece son eklenen veriyi excel e yazıyor tüm database deki verileri nasıl yazdırabilirim acaba ?

php-excel.class.php dosyası

PHP- Kodu:

<?php

/**
 * Excel_XML
 */

/**
 * Class Excel_XML
 * 
 * A simple export library for dumping array data into an excel
 * readable format. Supports OpenOffice Calc as well.
 * 
 * @author    Oliver Schwarz <oliver.schwarz@gmail.com>
 */
class Excel_XML
{

        /**
         * MicrosoftXML Header for Excel
         * @var string
         */
        const sHeader = "<?xml version="1.0" encoding="%s"?\>\n<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">";

        /**
         * MicrosoftXML Footer for Excel
         * @var string
         */
        const sFooter = "</Workbook>";

        /**
         * Worksheet & Data
         * @var array
         */
        private $aWorksheetData;

        /**
         * Output string
         * @var string
         */
        private $sOutput;

        /**
         * Encoding to be used
         * @var string
         */
        private $sEncoding;

        /**
         * Constructor
         *
         * Instanciates the class allowing a user-defined encoding.
         *
         * @param string $sEncoding Charset encoding to be used
         */
        public function __construct($sEncoding = 'UTF-8')
        {
                $this->sEncoding = $sEncoding;
                $this->sOutput = '';
        }

        /**
         * Add a worksheet
         *
         * Creates a new worksheet and adds the given data to it.
         * @param string $title Title of worksheet
         * @param array $data 2-dimensional array of data
         */
        public function addWorksheet($title, $data)
        {
                $this->aWorksheetData[] = array(
                        'title' => $this->getWorksheetTitle($title),
                        'data'  => $data
                );
        }

        /**
         * Send workbook to browser
         *
         * Sends the finished workbook to the browser using PHP's header
         * directive.
         *
         * @param string $filename Filename to use for sending the workbook
         */
        public function sendWorkbook($filename)
        {
                if (!preg_match('/\.(xml|xls)$/', $filename)):
                        throw new Exception('Filename mimetype must be .xml or .xls');
                endif;
                $filename = $this->getWorkbookTitle($filename);
                $this->generateWorkbook();
                if (preg_match('/\.xls$/', $filename)):
                        header("Content-Type: application/vnd.ms-excel; charset=" . $this->sEncoding);
                        header("Content-Disposition: inline; filename="" . $filename . """);
                else:
                        header("Content-Type: application/xml; charset=" . $this->sEncoding);
                        header("Content-Disposition: attachment; filename="" . $filename . """);
                endif;
                echo $this->sOutput;
        }

        /**
         * Write workbook to file
         *
         * Writes the workbook into the file/path given as a parameters.
         * The method checks whether the directory is writable and the
         * file is not existing and writes the file.
         *
         * @param string $filename Filename to use for writing (must contain mimetype)
         * @param string $path Path to use for writing [optional]
         */
        public function writeWorkbook($filename, $path = '')
        {
                $this->generateWorkbook();
                $filename = $this->getWorkbookTitle($filename);
                if (!$handle = @fopen($path . $filename, 'w+')):
                        throw new Exception(sprintf("Not allowed to write to file %s", $path . $filename));
                endif;
                if (@fwrite($handle, $this->sOutput) === false):
                        throw new Exception(sprintf("Error writing to file %s", $path . $filename));
                endif;
                @fclose($handle);
                return sprintf("File %s written", $path . $filename);
        }

        /**
         * Get workbook output
         *
         * Just returns the generated workbook content.
         *
         * @return string Output generated by the class
         */
        public function getWorkbook()
        {
                $this->generateWorkbook();
                return $this->sOutput;
        }

        /**
         * Compatibility: Add an array
         *
         * This method implements compatibility to version 1.1. Though using
         * self::addWorksheet surely is an improvement, nobody should be
         * forced to rewrite his code.
         *
         * @param array $data Data to be added
         */
        public function addArray($data)
        {
                $this->addWorksheet('Table1', $data);
        }

        /**
         * Compatibility: Generate XML
         *
         * This method implements compatibility to version 1.1 and generates
         * the excel file.
         *
         * @param string $filename Filename (without mimetype)
         */
        public function generateXML($filename)
        {
                $filename = $this->getWorkbookTitle($filename);
                $filename .= '.xls';
                $this->sendWorkbook($filename);
        }

        /**
         * Workbook title correction
         *
         * Corrects filename (if necessary) stripping out non-allowed
         * characters.
         *
         * @param string $filename Desired filename
         * @return string Corrected filename
         */
        private function getWorkbookTitle($filename)
        {
                return preg_replace('/[^aA-zZ0-9\_\-\.]/', '', $filename);
        }

        /**
         * Worksheet title correction
         *
         * Corrects the worksheet title (given by the user) by the allowed
         * characters by Excel.
         *
         * @param string $title Desired worksheet title
         * @return string Corrected worksheet title
         */
        private function getWorksheetTitle($title)
        {
                $title = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
                return substr ($title, 0, 31);
        }

        /**
         * Generate the workbook
         *
         * This is the main wrapper to generate the workbook.
         * It will invoke the creation of worksheets, rows and
         * columns.
         */
        private function generateWorkbook()
        {
                $this->sOutput .= stripslashes(sprintf(self::sHeader, $this->sEncoding)) . "\n";
                foreach ($this->aWorksheetData as $item):
                        $this->generateWorksheet($item);
                endforeach;
                $this->sOutput .= self::sFooter;
        }

        /**
         * Generate the Worksheet
         *
         * The second wrapper generates the worksheet. When the worksheet
         * data seems to be more than the excel allowed maximum lines, the
         * array is sliced.
         *
         * @param array $item Worksheet data
         * @todo Add a security check to testify whether this is an array
         */
        private function generateWorksheet($item)
        {
                $this->sOutput .= sprintf("<Worksheet ss:Name="%s">\n    <Table>\n", $item['title']);
                if (count($item['data']))
                        $item['data'] = array_slice($item['data'], 0, 65536);
                foreach ($item['data'] as $k => $v):
                        $this->generateRow($v);
                endforeach;
                $this->sOutput .= "    </Table>\n</Worksheet>\n";
        }

        /**
         * Generate the single row
         * @param array Item with row data
         */
        private function generateRow($item)
        {
                $this->sOutput .= "        <Row>\n";
                foreach ($item as $k => $v):
                        $this->generateCell($v);
                endforeach;
                $this->sOutput .= "        </Row>\n";
        }

        /**
         * Generate the single cell
         * @param string $item Cell data
         */
        private function generateCell($item)
        {
                $type = 'String';
                if (is_numeric($item)):
                        $type = 'Number';
                        if ($item{0} == '0' && strlen($item) > 1 && $item{1} != '.'):
                                $type = 'String';
                        endif;
                endif;
                $item = str_replace(''', '&apos;', htmlspecialchars($item, ENT_QUOTES));
                $this->sOutput .= sprintf("            <Cell><Data ss:Type="%s">%s</Data></Cell>\n", $type, $item);
        }

        /**
         * Deconstructor
         * Resets the main variables/objects
         */
        public function __destruct()
        {
                unset($this->aWorksheetData);
                unset($this->sOutput);
        }

}

?>

buda index dosyası

PHP- Kodu:

<?php error_reporting(0);  
ob_start();
  $vt_sunucu          = 'localhost';
  $vt_kullanici_adi   = 'deneme';
  $vt_kullanici_sifre = '123456';
  $vt_adi           = 'deneme';

  @ $mysql_baglanti = mysql_connect($vt_sunucu,$vt_kullanici_adi,$vt_kullanici_sifre,$vt_adi);
  @ mysql_select_db("$vt_adi",$mysql_baglanti) or die ("Veritabanı Seçim Hatası");
  
  
  $sip_ex=mysql_query("SELECT * FROM urunler"); 
  
mysql_query("SET NAMES 'utf8'"); 
mysql_query("SET CHARACTER SET utf8"); 
mysql_query("SET COLLATION_CONNECTION = 'utf8_turkish_ci'");  


if (mysql_errno())
  {
    echo 'Hata: Veritabanına Bağlanılamıyor.. Lütfen Tekrar Deneyiniz';
    exit;
  }
?>


<?php

require dirname(__FILE__) . '/php-excel.class.php';


while($exc = mysql_fetch_array($sip_ex)){
    
$data=array(
array(
'product_id',
'name(tr-tr)',
'categories',
'sku',
'upc',
'ean',
'jan',
'isbn',
'mpn',
'location',
'quantity',
'model',
'manufacturer',
'image_name',
'shipping',
'price',
'points',
'date_added',
'date_modified',
'date_available',
'weight',
'weight_unit',
'length',
'width',
'height',
'length_unit',
'status',
'tax_class_id',
'seo_keyword',
'description(tr-tr)',
'meta_title(tr-tr)',
'meta_description(tr-tr)',
'meta_keywords(tr-tr)',
'stock_status_id',
'store_ids',
'layout',
'related_ids',
'tags(tr-tr)',
'sort_order',
'subtract',
'minimum'
),

array(
$exc['product_id'],
$exc['name'],
$exc['categories'],
$exc['sku'],
$exc['upc'],
$exc['ean'],
$exc['jan'],
$exc['isbn'],
$exc['mpn'],
$exc['location'],
$exc['quantity'],
$exc['model'],
$exc['manufacturer'],
$exc['image_name'],
$exc['shipping'],
$exc['price'],
$exc['points'],
$exc['date_added'],
$exc['date_modified'],
$exc['date_available'],
$exc['weight'],
$exc['weight_unit'],
$exc['length_i'],
$exc['width'],
$exc['height'],
$exc['length_unit'],
$exc['status'],
$exc['tax_class_id'],
$exc['seo_keyword'],
$exc['description'],
$exc['meta_title'],
$exc['meta_description'],
$exc['meta_keywords'],
$exc['stock_status_id'],
$exc['store_ids'],
$exc['layout'],
$exc['related_ids'],
$exc['tags'],
$exc['sort_order'],
$exc['subtract'],
$exc['minimum']
));
}
$xls = new Excel_XML;
$xls->addWorksheet('Products', $data);
$xls->addWorksheet('Names2', $data2);
$xls->sendWorkbook('opencart_icin.xls');
?>

PDO ile yaptığım örneği kullanabilirsin hocam

[SOURCE="php"]
<?php
include("excelwriter.inc.php");
$excel=new ExcelWriter("excel.xls");
if($excel==false)
echo $excel->error;

$myArr=array("ÜRÜN ADI","ÜRÜN KODU","FİYAT");
$excel->writeLine($myArr);

try {
$db = new PDO("mysql:host=localhost;dbname=DB_NAME", "DB_USER", "PAROLA");
} catch ( PDOException $e ){
print $e->getMessage();
}
$db->query("SET CHARACTER SET utf8");

$query = $db->query("SELECT * FROM urunler", PDO::FETCH_ASSOC);
if ( $query->rowCount() ){
foreach( $query as $row ){
$urun_adi=$row['urun_adi'];
$urun_kodu=$row['urun_kodu'];
$urun_fiyat=$row['urun_fiyat'];
$myArr=array($urun_adi,$urun_kodu,$urun_fiyat);
$excel->writeLine($myArr);
}
}
$excel->close();
$db = null;
?>
[/SOURCE]