Archive for January, 2012

Export WordPress data into excel

Posted: January 28, 2012 in WordPress

DECLARE GLOBAL VARIABLE SINCE WE ARE USING WP CUSTOM QUERIES
global $wpdb,$post;
$file= "tablename";
SET YOUR BLOG ID NECESSARY ONLY WHEN FETCHING FROM CENTRALIZED DB
$wpdb->blogid = '257';
$wpdb->set_prefix( $wpdb->base_prefix );

ADD COLUMN HEADER WHICH HAS TO APPEAR IN EXCEL SHEET/CSV
$column_name = array(‘Business Name’, ‘City’, ‘Today Status’, ‘Tomorrow Status’, ‘Details’);
foreach( $column_name as $caname )
$output .= $caname.”\t”;

PRINT COLUMN HEADERS
echo $output;

CUSTOM QUERY TO FETCH POST AND POSTMETA VALUES ( YOU CAN CHANGE TABLE NAMES TO GET DESIRED OUTPUT )
$expid = “SELECT DISTINCT ID FROM “.$wpdb->prefix.”posts LEFT JOIN “.$wpdb->prefix.”postmeta ON ( “.$wpdb->prefix.”posts.ID=”.$wpdb->prefix.”postmeta.post_id )”;
$expids = $wpdb->get_results( $wpdb->prepare( $expid ));

ADDS NEXT LINE BASED ON WHICH BROWSER AGENT
$unewline = “\r\n”;
if (strstr(strtolower($_SERVER[“HTTP_USER_AGENT”]), ‘win’)) {
$unewline = “\r\n”;
} else if (strstr(strtolower($_SERVER[“HTTP_USER_AGENT”]), ‘mac’)) {
$unewline = “\r”;
} else {
$unewline = “\n”;
}

foreach( $expids as $t ){
ADD YOUR META KEY TO GET RESULT
$city = get_post_meta($t->ID, ‘METAKEY’, true);
$history = get_post_meta($t->ID, ‘METAKEY’, true);
if($history){
$output1 .= strip_tags($history[‘bname’]).”\t”.strip_tags($city).”\t”.strip_tags($history[‘todayStatusStr’]). “\t”. strip_tags($history[‘tomorrowStatusStr’]). “\t”. strip_tags($history[‘details’]) . $unewline;
}
}

CREATES YOUR FILE WITH DATE AND TIME
$filename = $file.”_”.date(“Y-m-d_H-i”,time());

IMPORTANT PART THIS CREATES EXCEL HEADERS
header(“Content-type: application/vnd.ms-excel”);
header(“Content-disposition: xls” . date(“Y-m-d”) . “.xls”);
header( “Content-disposition: filename=”.$filename.”.xls”);

PRINT YOUR COLUMNS ON EXCEL
print $output1;
exit;