What is a promise in Javascript?

Question

Andrés Vélez

Asked: 2020-07-31 14:01:56 +0800 CST 2020-07-31 14:01:56 +0800 CST 2020-07-31 14:01:56 +0800 CST

Download DB in various Excel

772

is that I have a site that downloads the databases in an excel file but the thing is that I already reach so much data that the excel file arrives damaged, for which it occurred to me that I could only upload let's say a thousand data and that when it is the thousand and one data, save it in another excel file, I don't 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 is by memory since when I remove some of the tables it already downloads it well.

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

class PostsController extends AppController {

    public $components = array('Paginator');

    public $paginate = array(
        'limit' => 25,
        'order' => array(
            'Post.title' => 'asc'
        )
    );
}

1 Answers

Voted

Miller Andrés Trujillo Achury · Answer 1 · 2020-07-31T18:02:40+08:00

Best Answer

Miller Andrés Trujillo Achury

2020-07-31T18:02:40+08:002020-07-31T18:02:40+08:00

One option is to use limitin the SQL statements:

SELECT * FROM DATA LIMIT OFFSET, COUNT;

Where OFFSETis from which row you will get the data and COUNTis 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 give you the third element of the table as a result, because since it OFFSEThas 2, that is, it will omit the first 2 rows and since COUNTit 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 go saving each of the 1000 data in a different excel with a while.

1

Download DB in various Excel

HTML button that sends you to another page

Why do I get the error "Call to undefined function mysql_connect()"?

How to create an HTML button that works as a link?

How to separate a String in Java. How to use split()

Filter by dates in sql server

How to limit the number of decimal places in a double?

For each in JavaScript?

Position footer ALWAYS glued to the footer

Definitive Guide to Type Conversion in Java

How to properly compare Strings (and objects) in Java?