I have a site that downloads a DB to an excel file and they suggested that I do it with a zip file, so I was thinking about downloading each table in an excel file and putting everything in zip,
this is the code with which I create the excel files:
<?php
$bd123= $_POST["basededatos123"];
$link = mysqli_connect("", "", "");
mysqli_select_db($link, "");
//$tildes = $link->query("SET NAMES 'utf8'"); //Para que se muestren las tildes correctamente
$result = mysqli_query($link, "SELECT * FROM ".$bd123);
include ("PHPExcel-1.8/Classes/PHPExcel.php");
// Se crea el objeto PHPExcel
$objPHPExcel = new PHPExcel();
// Se asignan las propiedades del libro
$objPHPExcel->getProperties()->setCreator("Codedrinks") // Nombre del autor
->setLastModifiedBy("Codedrinks") //Ultimo usuario que lo modificó
->setTitle("Reporte Excel con PHP y MySQL") // Titulo
->setSubject("Reporte Excel con PHP y MySQL") //Asunto
->setDescription("Reporte de alumnos") //Descripción
->setKeywords("reporte alumnos carreras") //Etiquetas
->setCategory("Reporte excel"); //Categorias
$tituloReporte = "Datos Actualizados Del Usuario";
$titulosColumnas = array('Nombre', 'Correo', 'Celular', 'Placa', 'Creado');
// Se combinan las celdas A1 hasta D1, para colocar ahí el titulo del reporte
$objPHPExcel->setActiveSheetIndex(0)
->mergeCells('A1:E1');
// Se agregan los titulos del reporte
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1',$tituloReporte) // Titulo del reporte
->setCellValue('A3', $titulosColumnas[0]) //Titulo de las columnas
->setCellValue('B3', $titulosColumnas[1])
->setCellValue('C3', $titulosColumnas[2])
->setCellValue('D3', $titulosColumnas[3])
->setCellValue('E3', $titulosColumnas[4]);
//Se agregan los datos de los alumnos
$i = 4; //Numero de fila donde se va a comenzar a rellenar
while ($fila = mysqli_fetch_array($result)){
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$i, $fila['name'])
->setCellValue('B'.$i, $fila['email'])
->setCellValue('C'.$i, $fila['tel'])
->setCellValue('D'.$i, $fila['plate'])
->setCellValue('E'.$i, $fila['created']);
$i++;
}
$estiloTituloReporte = array(
'font' => array(
'name' => 'Verdana',
'bold' => true,
'italic' => false,
'strike' => false,
'size' =>16,
'color' => array(
'rgb' => 'FFFFFF'
)
),
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array(
'argb' => '4d3b03')
),
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_NONE
)
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
'rotation' => 0,
'wrap' => TRUE
)
);
$estiloTituloColumnas = array(
'font' => array(
'name' => 'Arial',
'bold' => true,
'color' => array(
'rgb' => 'FFFFFF'
)
),
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
'rotation' => 90,
'startcolor' => array(
'rgb' => 'FFFFFF'
),
'endcolor' => array(
'argb' => '0A3076'
)
),
'borders' => array(
'top' => array(
'style' => PHPExcel_Style_Border::BORDER_MEDIUM ,
'color' => array(
'rgb' => '000000'
)
),
'bottom' => array(
'style' => PHPExcel_Style_Border::BORDER_MEDIUM ,
'color' => array(
'rgb' => '000000'
)
)
),
'alignment' => array(
'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
'wrap' => TRUE
)
);
$estiloInformacion = new PHPExcel_Style();
$estiloInformacion->applyFromArray( array(
'font' => array(
'name' => 'Arial',
'color' => array(
'rgb' => '000000'
)
),
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array(
'argb' => 'ffffff')
),
'borders' => array(
'left' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN ,
'color' => array(
'rgb' => '838F68'
)
)
)
));
$objPHPExcel->getActiveSheet()->getStyle('A1:E1')->applyFromArray($estiloTituloReporte);
$objPHPExcel->getActiveSheet()->getStyle('A3:E3')->applyFromArray($estiloTituloColumnas);
$objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A4:E".($i-1));
for($i = 'A'; $i <= 'E'; $i++){
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($i)->setAutoSize(TRUE);
}
// Se asigna el nombre a la hoja
$objPHPExcel->getActiveSheet()->setTitle('Usuario');
// Se activa la hoja para que sea la que se muestre cuando el archivo se abre
$objPHPExcel->setActiveSheetIndex(0);
// Inmovilizar paneles
//$objPHPExcel->getActiveSheet(0)->freezePane('A4');
$objPHPExcel->getActiveSheet(0)->freezePaneByColumnAndRow(0,4);
// Se manda el archivo al navegador web, con el nombre que se indica, en formato 2007
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="DataUsers.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
//header('Location: www.google.com');
exit;
?>
I was thinking in test mode put other tables and do the same code and then put them in the zip, but I do not know how to implement it, the zip code they showed me is this:
$files = array('readme.txt', 'test.html', 'image.gif');
$zipname = 'file.zip';
$zip = new ZipArchive;
$zip->open($zipname, ZipArchive::CREATE);
foreach ($files as $file) {
$zip->addFile($file);
}
$zip->close();
header('Content-Type: application/zip');
header('Content-disposition: attachment; filename='.$zipname);
header('Content-Length: ' . filesize($zipname));
readfile($zipname);