Skip to content

Instantly share code, notes, and snippets.

@agustinprosperi

agustinprosperi/export.php

Last active September 2, 2016 10:45
Show Gist options
  • Save agustinprosperi/c96918304f95902afdfe4269c2415e16 to your computer and use it in GitHub Desktop.
Save agustinprosperi/c96918304f95902afdfe4269c2415e16 to your computer and use it in GitHub Desktop.
Download ZIP
Export-Import posts en excel con PHPExcel_1.8.0_doc con las imagenes adjuntas
Raw
export.php
<?php
// Incluir en el functions.php del sitio a exportar
// exportLocales
if( isset($_GET['exportLocales']) ){
// error_reporting(E_ALL);
// ini_set('display_errors', TRUE);
// ini_set('display_startup_errors', TRUE);
add_action( 'init', function(){
$locales = new WP_Query( array(
'post_type' => 'locales',
'post_status' => array('publish','draft'),//get_post_stati(),
'nopaging' => true,
) );
if( empty($locales->posts) ) return;
$prefix = 'cmb_';
//
require_once __DIR__.'/PHPExcel_1.8.0_doc/Classes/PHPExcel.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("MB Web")
->setLastModifiedBy("MB Web")
->setTitle("export")
->setSubject("export")
->setDescription("")
->setKeywords("")
->setCategory("");
//add fata from DB
$i = 0; $bgcolor = 'FFFFFF';
foreach ( $locales->posts as $k=>$post ) :
$i++;
// echo'<pre style="font-size:12px; line-height:1.1; white-space:pre-wrap;">'; var_dump($post); echo'</pre>'; exit();
// taxonomias
$ciudad = wp_get_post_terms( $post->ID, 'ciudad' );
if( !empty($ciudad) ) $ciudad = $ciudad[0]->name;
$tipo = wp_get_post_terms( $post->ID, 'tipo' );
if( !empty($tipo) ) $tipo = $tipo[0]->name;
// datos de post en array
$post_data = json_encode( array(
'ID' => $post->ID,
'post_date' => $post->post_date,
'post_date_gmt' => $post->post_date_gmt,
'post_excerpt' => $post->post_excerpt,
'post_status' => $post->post_status,
'post_name' => $post->post_name,
'post_modified' => $post->post_modified,
'post_modified_gmt' => $post->post_modified_gmt,
'ciudad' => $ciudad,
'tipo' => $tipo
) );
// meta data
$meta_data = json_encode( array(
'local_direct' => get_post_meta($post->ID, $prefix.'local_direct', 1),
'local_lat' => get_post_meta($post->ID, $prefix.'local_lat', 1),
'local_long' => get_post_meta($post->ID, $prefix.'local_long', 1),
'local_facebook' => get_post_meta($post->ID, $prefix.'local_facebook', 1),
'local_twitter' => get_post_meta($post->ID, $prefix.'local_twitter', 1),
'local_google' => get_post_meta($post->ID, $prefix.'local_google', 1),
'local_web' => get_post_meta($post->ID, $prefix.'local_web', 1),
'local_email' => get_post_meta($post->ID, $prefix.'local_email', 1),
'tlf_local' => get_post_meta($post->ID, $prefix.'tlf-local', 1),
'horario_local' => get_post_meta($post->ID, $prefix.'horario-local', 1),
'responsable_local' => get_post_meta($post->ID, $prefix.'responsable-local', 1),
'grifos_local' => get_post_meta($post->ID, $prefix.'grifos-local', 1),
'artesana_local' => get_post_meta($post->ID, $prefix.'artesana-local', 1),
'comer_local' => get_post_meta($post->ID, $prefix.'comer-local', 1),
'carta_local' => get_post_meta($post->ID, $prefix.'carta-local', 1)
) );
// imagen destacada
$fimg = get_post_thumbnail_id($post->ID);
// traer todos las imagenes adjuntas
$args = array('post_type'=>'attachment', 'numberposts'=>null, 'post_status'=>null, 'post_parent' => $post->ID);
if( !empty($fimg) ){
$args['post__not_in'] = array($fimg);
$fimg = wp_get_attachment_url($fimg);
}
$attachments = get_posts($args);
// crear array para los adjuntos
$adjuntos = array();
foreach ($attachments as $key => $file){
$adjuntos[] = wp_get_attachment_url($file->ID);
}
if( !empty($adjuntos) ){
$adjuntos = json_encode($adjuntos);
}
//set row on phpexcel object
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$i, $post_data)
->setCellValue('B'.$i, $fimg)
->setCellValue('C'.$i, $post->post_content)
->setCellValue('D'.$i, $post->post_title)
->setCellValue('E'.$i, $meta_data)
->setCellValue('F'.$i, $adjuntos)
;
$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':F'.$i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB( $bgcolor );
//$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':F'.$i)->getAlignment()->setIndent(1);
$bgcolor = ($bgcolor=='EEEEEE')? 'FFFFFF': 'EEEEEE';
endforeach;// exit;
//
$totr = $i;//count($reservas)+1;
//data headers style
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getStyle( 'A1:F'.$totr )->applyFromArray(array(
'borders' => array(
'inside' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
),
'outline' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
)
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
'rotation' => 0,
'wrap' => TRUE
)
)
);
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('hoja1');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// file name
$fname = 'locales_' . date( "Y-m-d H.i.s" );
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'. $fname .'.xls"');
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, 'Excel5');
$objWriter->save('php://output');
exit;
} );
}
Raw
import.php
<?php
// Incluir en el functions.php del sitio donde se importaran los posts
// importLocales
if( isset($_GET['importLocales']) ){
// error_reporting(E_ALL);
// ini_set('display_errors', TRUE);
// ini_set('display_startup_errors', TRUE);
add_action( 'init', function(){
$file = __DIR__.'/locales_2016-08-10 11.41.08.xls';
if( !file_exists( $file ) ) return;
$prefix = 'cmb2_';
/** Include PHPExcel_IOFactory */
require_once __DIR__.'/PHPExcel_1.8.0_doc/Classes/PHPExcel/IOFactory.php';
/** Identify the type of $file **/
$inputFileType = PHPExcel_IOFactory::identify( $file );
/** Create a new Reader of the type that has been identified **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/** Advise the Reader that we only want to load cell data **/
$objReader->setReadDataOnly(true);
/** Load $file to a PHPExcel Object **/
$objPHPExcel = $objReader->load($file);
// return the cells in array
$excelData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
if( empty($excelData) ) return;
global $wpdb;
$categoria_local_arr = array();
$ciudad_arr = array();
foreach ($excelData as $k=>$v){
if( $k%2==0 ) set_time_limit(100);
if( empty($v['A']) ) continue;
// if exists chau
// $exists = $wpdb->get_var( sprintf("SELECT ID FROM %s WHERE post_type='locales' AND post_title='%s'", $wpdb->posts, $v['D']) );
// if( $exists ) continue;
// start
$pd = json_decode($v['A']);
$clID = '';
// traer categoria-local id
if( !empty($pd->tipo) ){
if( !array_key_exists($pd->tipo, $categoria_local_arr) ){
$clID = term_exists( $pd->tipo, 'categoria-local' );
if( empty($clID) ){
$clID = wp_insert_term( $pd->tipo, 'categoria-local' );
$clID = $clID['term_id'];
}else
$clID = $clID['term_id'];
$categoria_local_arr[ $pd->tipo ] = $clID;
}
$clID = $categoria_local_arr[ $pd->tipo ];
}
$cID = '';
// traer ciudad id
if( !empty($pd->ciudad) ){
if( !array_key_exists($pd->ciudad, $ciudad_arr) ){
$cID = term_exists( $pd->ciudad, 'ciudad' );
if( empty($cID) ){
$cID = wp_insert_term( $pd->ciudad, 'ciudad' );
$cID = $cID['term_id'];
}else
$cID = $cID['term_id'];
$ciudad_arr[ $pd->ciudad ] = $cID;
}
$cID = $ciudad_arr[ $pd->ciudad ];
}
//
$metaData = json_decode($v['E']);
$pcontent = empty($v['C'])? '': $v['C'];
// set post data
$my_post = array (
'post_type' => 'locales',
'post_content' => $pcontent,
'post_title' => $v['D'],
'post_date' => $pd->post_date,
'post_date_gmt' => $pd->post_date_gmt,
'post_excerpt' => $pd->post_excerpt,
'post_status' => $pd->post_status,
'post_name' => $pd->post_name,
'post_modified' => $pd->post_modified,
'post_modified_gmt' => $pd->post_modified_gmt,
'meta_input' => array(
$prefix.'local_tel' => $metaData->tlf_local,
$prefix.'local_horario' => $metaData->horario_local,
$prefix.'local_responsable' => $metaData->responsable_local,
$prefix.'local_grifos' => $metaData->grifos_local,
$prefix.'local_cervezaart' => $metaData->artesana_local,
$prefix.'local_comer' => $metaData->comer_local,
$prefix.'local_direccion' => $metaData->local_direct,
$prefix.'local_fb' => $metaData->local_facebook,
$prefix.'local_tw' => $metaData->local_twitter,
$prefix.'local_g' => $metaData->local_google,
$prefix.'local_email' => $metaData->local_email,
$prefix.'local_url' => $metaData->local_web,
)
);
// tax_input
if( !empty($clID) ){
$my_post['tax_input']['categoria-local' ] = array($clID);
}
if( !empty($cID) ){
$my_post['tax_input']['ciudad' ] = array($cID);
}
// 'google_map_locales'
if( !empty($metaData->local_lat) || !empty($metaData->local_long) ){
$my_post['meta_input']['google_map_locales' ] = array( 'latitude'=>$metaData->local_lat, 'longitude'=>$metaData->local_long );
}
$my_post_ID = wp_insert_post( $my_post, true );
if( !is_wp_error($my_post_ID) ){
// load wp_handle_upload if not loaded yet
if ( !function_exists('media_sideload_image') ){
require_once( ABSPATH.'/wp-admin/includes/media.php' );
require_once( ABSPATH.'/wp-admin/includes/file.php' );
require_once( ABSPATH.'/wp-admin/includes/image.php' );
}
// thumbnail
if( !empty($v['B']) ){
$img = media_sideload_image( $v['B'], $my_post_ID, '', 'src' );
if( !is_wp_error($img) ){
$img_id = $wpdb->get_var( sprintf("SELECT ID FROM %s WHERE guid='%s'", $wpdb->posts, $img) );
// guardar imagen subida como destacada del post
set_post_thumbnail( $my_post_ID, $img_id );
}
}
// imagenes adjuntas
if( !empty($v['F']) ){
$forGalIds = array();
$fimgs = json_decode($v['F']);
foreach($fimgs as $value){
// $findImg = str_replace('https://cerveceraindependiente.com/', $home_url, $value);
// $findImg = $wpdb->get_var( sprintf("SELECT ID FROM %s WHERE guid='%s'", $wpdb->posts, $findImg) );
// if( empty($findImg) ){
$img = media_sideload_image( $value, $my_post_ID, '', 'src' );
if( !is_wp_error($img) ){
$forGalIds[] = $wpdb->get_var( sprintf("SELECT ID FROM %s WHERE guid='%s'", $wpdb->posts, $img) );
}
// }else{
// $forGalIds[] = $findImg;
// }
}
if( !empty($forGalIds) ){
// traer galerias para reemplazar
$galleries = get_post_galleries( $my_post_ID, false );
if( !empty($galleries) ){
$post_content = $wpdb->get_var( sprintf("SELECT post_content FROM %s WHERE ID=%d", $wpdb->posts, $my_post_ID) );
foreach ($galleries as $g){
$ids = explode(',', $g['ids']);
if( count($ids)==count($forGalIds) ){
$post_content = str_replace( $g['ids'], implode(',', $forGalIds), $post_content);
}else{
$tr = array_slice($forGalIds, 0, count($ids));
array_splice($forGalIds, 0, count($ids));
$post_content = str_replace( $g['ids'], implode(',', $tr), $post_content);
}
}
$wpdb->update( $wpdb->posts, array('post_content' => $post_content, ), array('ID' => $my_post_ID, ) );
}
}
}
}else{
var_dump($my_post_ID);
}
}
echo 'If you see just this :)';
exit();
}, 0 );
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment