Download BD in several Excel

Navigation
0

is that I have a site that downloads the bd in an excel file but the thing is that I arrive at so much data that the excel file arrives damaged, so it occurred to me that I could only upload, say, a thousand data and that when the thousand thousand data is saved in another excel file, I do not know if that is possible.

this is my code 

 error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

if (PHP_SAPI == 'cli')
    die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once "PHPExcel-1.8/Classes/PHPExcel.php";


// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
ini_set("memory_limit","2048M");

$link = mysqli_connect("localhost", "", "");
mysqli_select_db($link, "");
$tildes = $link->query("SET NAMES 'utf16'"); //Para que se muestren las tildes correctamente
// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
                             ->setLastModifiedBy("Maarten Balliauw")
                             ->setTitle("Office 2007 XLSX Test Document")
                             ->setSubject("Office 2007 XLSX Test Document")
                             ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                             ->setKeywords("office 2007 openxml php")
                             ->setCategory("Test result file");
$result0 = mysqli_query($link, "SELECT * FROM quote_new_users ORDER BY id DESC");//que carro cotizo
$result1 = mysqli_query($link, "SELECT * FROM workshop_users ORDER BY id DESC");
$result2 = mysqli_query($link, "SELECT * FROM soat_users ORDER BY id DESC");
$result3 = mysqli_query($link, "SELECT * FROM democar_users ORDER BY id DESC");
$result4 = mysqli_query($link, "SELECT * FROM contact_users ORDER BY id DESC");
$result5 = mysqli_query($link, "SELECT * FROM use_users ORDER BY id DESC");
$result6 = mysqli_query($link, "SELECT * FROM chevy_users ORDER BY id DESC");
$result7 = mysqli_query($link, "SELECT * FROM spares_users ORDER BY id DESC");

//$result12 = mysqli_query($link, "SELECT * FROM quote_new_users ORDER BY id DESC");
//$result13 = mysqli_query($link, "SELECT * FROM quote_new_users ORDER BY id DESC");

$i=5;
while ($fila = mysqli_fetch_array($result0)){
    $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$i, $fila['id'])
            ->setCellValue('B'.$i, $fila['name'])
            ->setCellValue('C'.$i, $fila['email'])
            ->setCellValue('D'.$i, $fila['tel'])
            ->setCellValue('E'.$i, $fila['date_created'])
            ;
    $i++;
}
// Add some data
// $titulosColumnas = array('Codigo', 'Nombre', 'Correo', 'Telefono', 'Fecha de creacion');
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'USUARIOS REGISTRADOS')
            ->setCellValue('A3', 'Codigo')
            ->setCellValue('B3', 'Nombre')
            ->setCellValue('C3', 'Correo')
            ->setCellValue('D3', 'Telefono ')
            ->setCellValue('E3', 'Fecha de creacion')
            ;
 // Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Cotizador');
for($i = 'A'; $i <= 'E'; $i++){
                $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($i)->setAutoSize(TRUE);
            }
            $objPHPExcel->getActiveSheet(0)->freezePaneByColumnAndRow(0,4);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);







$i=5;
$objPHPExcel->createSheet();
while ($fila = mysqli_fetch_array($result1)){
    $objPHPExcel->setActiveSheetIndex(1)
            ->setCellValue('A'.$i, $fila['id'])
            ->setCellValue('B'.$i, $fila['name'])
            ->setCellValue('C'.$i, $fila['email'])
            ->setCellValue('D'.$i, $fila['tel'])
            ->setCellValue('E'.$i, $fila['plate'])
            ->setCellValue('F'.$i, $fila['model'])
            ->setCellValue('G'.$i, $fila['date_created'])
            ;
    $i++;
}
// Add some data
$objPHPExcel->setActiveSheetIndex(1)
            ->setCellValue('A1', 'USUARIOS REGISTRADOS')
            ->setCellValue('A3', 'Codigo')
            ->setCellValue('B3', 'Nombre')
            ->setCellValue('C3', 'Correo')
            ->setCellValue('D3', 'Telefono')
            ->setCellValue('E3', 'Placa')
            ->setCellValue('F3', 'Modelo')
            ->setCellValue('G3', 'Fecha de creacion')
            ;
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Taller');
for($i = 'A'; $i <= 'G'; $i++){
                $objPHPExcel->setActiveSheetIndex(1)->getColumnDimension($i)->setAutoSize(TRUE);
            }
            $objPHPExcel->getActiveSheet(1)->freezePaneByColumnAndRow(0,4);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(1);





$i=5;
$objPHPExcel->createSheet();
while ($fila = mysqli_fetch_array($result2)){
    $objPHPExcel->setActiveSheetIndex(2)
            ->setCellValue('A'.$i, $fila['id'])
            ->setCellValue('B'.$i, $fila['name'])
            ->setCellValue('C'.$i, $fila['email'])
            ->setCellValue('D'.$i, $fila['tel'])
            ->setCellValue('E'.$i, $fila['plate'])
            ->setCellValue('F'.$i, $fila['date_created'])
            ;
    $i++;
}
// Add some data
$objPHPExcel->setActiveSheetIndex(2)
            ->setCellValue('A1', 'USUARIOS REGISTRADOS')
            ->setCellValue('A3', 'Codigo')
            ->setCellValue('B3', 'Nombre')
            ->setCellValue('A3', 'Correo')
            ->setCellValue('D3', 'Telefono')
            ->setCellValue('E3', 'Placa')
            ->setCellValue('F3', 'Fecha de creacion')
            ;
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Soat');
for($i = 'A'; $i <= 'F'; $i++){
                $objPHPExcel->setActiveSheetIndex(2)->getColumnDimension($i)->setAutoSize(TRUE);
            }
            $objPHPExcel->getActiveSheet(2)->freezePaneByColumnAndRow(0,4);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(2);






$i=5;
$objPHPExcel->createSheet();
while ($fila = mysqli_fetch_array($result3)){
    $objPHPExcel->setActiveSheetIndex(3)
            ->setCellValue('A'.$i, $fila['id'])
            ->setCellValue('B'.$i, $fila['name'])
            ->setCellValue('C'.$i, $fila['email'])
            ->setCellValue('D'.$i, $fila['tel'])
            ->setCellValue('E'.$i, $fila['date_created'])
            ;
    $i++;
}
// Add some data
$objPHPExcel->setActiveSheetIndex(3)
            ->setCellValue('A1', 'USUARIOS REGISTRADOS')
            ->setCellValue('A3', 'Codigo')
            ->setCellValue('B3', 'Codigo - Nombre')
            ->setCellValue('C3', 'Correo')
            ->setCellValue('D3', 'Telefono')
            ->setCellValue('E3', 'Fecha de creacion')
            ;
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Demos');
for($i = 'A'; $i <= 'E'; $i++){
                $objPHPExcel->setActiveSheetIndex(3)->getColumnDimension($i)->setAutoSize(TRUE);
            }
            $objPHPExcel->getActiveSheet(3)->freezePaneByColumnAndRow(0,4);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(3);







$i=5;
$objPHPExcel->createSheet();
while ($fila = mysqli_fetch_array($result4)){
    $objPHPExcel->setActiveSheetIndex(4)
            ->setCellValue('A'.$i, $fila['id'])
            ->setCellValue('B'.$i, $fila['name'])
            ->setCellValue('C'.$i, $fila['email'])
            ->setCellValue('D'.$i, $fila['tel'])
            ->setCellValue('E'.$i, $fila['date_created'])
            ;
    $i++;
}
// Add some data
$objPHPExcel->setActiveSheetIndex(4)
            ->setCellValue('A1', 'USUARIOS REGISTRADOS')
            ->setCellValue('A3', 'Codigo')
            ->setCellValue('B3', 'Nombre')
            ->setCellValue('C3', 'Correo')
            ->setCellValue('D3', 'Telefono')
            ->setCellValue('E3', 'Fecha de creacion')
            ;
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Contacto');
for($i = 'A'; $i <= 'E'; $i++){
                $objPHPExcel->setActiveSheetIndex(4)->getColumnDimension($i)->setAutoSize(TRUE);
            }
            $objPHPExcel->getActiveSheet(4)->freezePaneByColumnAndRow(0,4);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(4);





$i=5;
$objPHPExcel->createSheet();
while ($fila = mysqli_fetch_array($result5)){
    $objPHPExcel->setActiveSheetIndex(5)
            ->setCellValue('A'.$i, $fila['id'])
            ->setCellValue('B'.$i, $fila['name'])
            ->setCellValue('C'.$i, $fila['email'])
            ->setCellValue('D'.$i, $fila['tel'])
            ->setCellValue('E'.$i, $fila['date_created'])
            ;
    $i++;
}
// Add some data
$objPHPExcel->setActiveSheetIndex(5)
            ->setCellValue('A1', 'USUARIOS REGISTRADOS')
            ->setCellValue('A3', 'Codigo')
            ->setCellValue('B3', 'Nombre')
            ->setCellValue('C3', 'Email')
            ->setCellValue('D3', 'Telefono')
            ->setCellValue('E3', 'Fecha de creacion')
            ;
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Usados');
for($i = 'A'; $i <= 'E'; $i++){
                $objPHPExcel->setActiveSheetIndex(5)->getColumnDimension($i)->setAutoSize(TRUE);
            }
            $objPHPExcel->getActiveSheet(5)->freezePaneByColumnAndRow(0,4);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(5);





$i=5;
$objPHPExcel->createSheet();
while ($fila = mysqli_fetch_array($result6)){
    $objPHPExcel->setActiveSheetIndex(6)
            ->setCellValue('A'.$i, $fila['id'])
            ->setCellValue('B'.$i, $fila['name'])
            ->setCellValue('C'.$i, $fila['email'])
            ->setCellValue('D'.$i, $fila['tel'])
            ->setCellValue('E'.$i, $fila['date_created'])
            ;
    $i++;
}
// Add some data
$objPHPExcel->setActiveSheetIndex(6)
            ->setCellValue('A1', 'USUARIOS REGISTRADOS')
            ->setCellValue('A3', 'Codigo')
            ->setCellValue('B3', 'Nombre')
            ->setCellValue('C3', 'Correo')
            ->setCellValue('D3', 'Telefono')
            ->setCellValue('E3', 'Fecha de creacion')
            ;
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('ChevyPlan');
for($i = 'A'; $i <= 'D'; $i++){
                $objPHPExcel->setActiveSheetIndex(6)->getColumnDimension($i)->setAutoSize(TRUE);
            }
            $objPHPExcel->getActiveSheet(6)->freezePaneByColumnAndRow(0,4);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(6);







$i=5;
$objPHPExcel->createSheet();
while ($fila = mysqli_fetch_array($result7)){
    $objPHPExcel->setActiveSheetIndex(7)
            ->setCellValue('A'.$i, $fila['id'])
            ->setCellValue('B'.$i, $fila['name'])
            ->setCellValue('C'.$i, $fila['email'])
            ->setCellValue('D'.$i, $fila['tel'])
            ->setCellValue('E'.$i, $fila['date_created'])
            ;
    $i++;
}
// Add some data
$objPHPExcel->setActiveSheetIndex(7)
            ->setCellValue('A1', 'USUARIOS REGISTRADOS')
            ->setCellValue('A3', 'Codigo')
            ->setCellValue('B3', 'Nombre')
            ->setCellValue('C3', 'Correo')
            ->setCellValue('D3', 'Telefono')
            ->setCellValue('E3', 'Fecha de creacion')
            ;
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Repuestos');
for($i = 'A'; $i <= 'D'; $i++){
                $objPHPExcel->setActiveSheetIndex(7)->getColumnDimension($i)->setAutoSize(TRUE);
            }
            $objPHPExcel->getActiveSheet(7)->freezePaneByColumnAndRow(0,4);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(7);













$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Autolarte.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;

This is the error that comes out:

And it's because of memory because when I remove some of the tables already, if I lower it bn.

In cakephp they told me that there was something that I did but in pure mysql I do not know how to do it. 

class PostsController extends AppController {

    public $components = array('Paginator');

    public $paginate = array(
        'limit' => 25,
        'order' => array(
            'Post.title' => 'asc'
        )
    );
}
    
asked by Andrés Vélez 31.07.2018 в 00:01
source

1 answer

1

One option is to use limit in SQL statements:

SELECT * FROM DATA LIMIT OFFSET, COUNT;

Where OFFSET is from which row you will get the data and COUNT is how many rows you will get.

For example, if you do this query in this SQL Fiddle :

SELECT * FROM DATA LIMIT 2, 1;

It will result in the third element of the table, because as OFFSET has 2, that is, it will omit the first 2 rows and as COUNT has 1, it will give you only one row.

So, if you want to jump from 1000 to 1000 you can do something like this:

SELECT * FROM DATA LIMIT 1000;

SELECT * FROM DATA LIMIT 1000, 1000;

SELECT * FROM DATA LIMIT 2000, 1000;

And save each of the 1000 data in a different excel with while .

    
answered by 31.07.2018 / 04:02
source
how to separate code in eclipse? Match error with regular expressions in preg_match_all