jor133d Posted August 29, 2013 Share Posted August 29, 2013 I am really a novice PHP user and exhausted my knowledge at this point. I need some help with exporting to .xls. I had found some form builder code. It exports the data now but instead of horizontally displaying the field responses, it creates a new line each time. See my attachment. Would anybody be able to assit me in having the export properly display in a new cell in the same row instead of a new row? Here is the code: <?php if ($handle = opendir('../submissions/')) { if (!isset($_SESSION)) { session_start(); } if (!(isset($_SESSION['user_email']))) { header( 'Location: login.php'); } $i = 1; // Loop through the submissions while (false !== ($entry = readdir($handle))) { if ((substr($entry, 0, 4)=='subm')) { $temp = json_decode(file_get_contents('../submissions/'.$entry), 1); $i++; $set_it[$i]=0; //if ( $temp['seen']=='1' ) { $seen = 'Read'; } else { $seen = 'Unread'; } $new = json_decode($temp['content'],1); // Loop through the input fields within a submission foreach ($new as $skey=>$value) { $skey++; if ( !(empty($value['type'])) && !($value['type']=='captcha')) { $mess[$skey] = "$value[value]"; if (!($set_it[$i])) { $set_it[$i]=1; $line[$i*$skey] = "$temp[added]\t$temp[form_id]\t$mess[$skey]"; } else { $line[$i*$skey] = "\t\t$mess[$skey]"; } } } // End of foreach (field loop) $i++; } } // End of While (submission loop) closedir($handle); } $header = 'Form Submission Data'; $sz = sizeof($line); $a=0; $data=''; foreach($line as $key=>$row) { $data.="$row\n"; } $data = "Date\tForm Id\tFirst Name\tLast Name\tEmail\tPhone Number\tBest time to contact\tCity\tRating\tReason for rating\n$data"; header("Content-type: application/x-msdownload"); header("Content-Disposition: attachment; filename=form_data.xls"); header("Pragma: no-cache"); header("Expires: 0"); print "$data"; ?> Quote Link to comment Share on other sites More sharing options...
Psycho Posted August 29, 2013 Share Posted August 29, 2013 Looks like there are a few problems here, but it's impossible to determine why the output is the way it is from what you have supplied. We really need to see a sample of the input file. Quote Link to comment Share on other sites More sharing options...
jor133d Posted August 29, 2013 Author Share Posted August 29, 2013 Is this what you are looking for? {"content":"{\"2\":{\"label\":\"\",\"value\":\"\",\"type\":null,\"validation\":null,\"required\":null,\"min\":null,\"max\":null,\"tooltip\":null,\"custom\":null,\"custom2\":null},\"3\":{\"label\":\"Based+on+your+most+recent+call+to+our+Call+Center%2C+how+likely+is+it+that+you+would+recommend+CitizensFirst+Credit+Union+to+a+friend+or+colleague%3F\",\"value\":\"10 (Very Likely)\",\"type\":\"radio\",\"validation\":\"\",\"required\":\"1\",\"min\":\"\",\"max\":\"\",\"tooltip\":\"field0\",\"custom\":null,\"custom2\":null},\"4\":{\"label\":\"What+is+the+primary+reason+for+the+rating+you+gave%3F\",\"value\":\"Because Anna is absolutely incredible. \",\"type\":\"para\",\"validation\":\"\",\"required\":\"0\",\"min\":\"0\",\"max\":\"60\",\"tooltip\":\"field1\",\"custom\":null,\"custom2\":null},\"5\":{\"label\":\"divider\",\"value\":\"\",\"type\":\"divider\",\"validation\":\"divider\",\"required\":null,\"min\":null,\"max\":null,\"tooltip\":null,\"custom\":null,\"custom2\":null},\"6\":{\"label\":\"Gender\",\"value\":\"Female\",\"type\":\"dropdown\",\"validation\":\"\",\"required\":\"1\",\"min\":\"\",\"max\":\"\",\"tooltip\":\"field3\",\"custom\":null,\"custom2\":null},\"7\":{\"label\":\"Age\",\"value\":\"30-45\",\"type\":\"dropdown\",\"validation\":\"\",\"required\":\"1\",\"min\":\"\",\"max\":\"\",\"tooltip\":\"field4\",\"custom\":null,\"custom2\":null},\"8\":{\"label\":\"Marital+Status\",\"value\":\"Married with children\",\"type\":\"dropdown\",\"validation\":\"\",\"required\":\"1\",\"min\":\"\",\"max\":\"\",\"tooltip\":\"field5\",\"custom\":null,\"custom2\":null},\"9\":{\"label\":\"Household+income\",\"value\":\"Prefer not to answer\",\"type\":\"dropdown\",\"validation\":\"\",\"required\":\"1\",\"min\":\"\",\"max\":\"\",\"tooltip\":\"field6\",\"custom\":null,\"custom2\":null},\"10\":{\"label\":\"City+you+reside+in\",\"value\":\"Oshkosh\",\"type\":\"dropdown\",\"validation\":\"\",\"required\":\"0\",\"min\":\"\",\"max\":\"\",\"tooltip\":\"field7\",\"custom\":null,\"custom2\":null},\"11\":{\"label\":\"If+other%2C+please+specify\",\"value\":\"Neenah\",\"type\":\"text\",\"validation\":\"\",\"required\":\"0\",\"min\":\"0\",\"max\":\"60\",\"tooltip\":\"field8\",\"custom\":null,\"custom2\":null},\"12\":{\"label\":\"divider\",\"value\":\"If you would like CitizensFirst to contact you regarding your responses, please complete the following:\",\"type\":\"divider\",\"validation\":\"divider\",\"required\":null,\"min\":null,\"max\":null,\"tooltip\":null,\"custom\":null,\"custom2\":null},\"13\":{\"label\":\"Name\",\"value\":\"Betsy Doran\",\"type\":\"text\",\"validation\":\"\",\"required\":\"0\",\"min\":\"0\",\"max\":\"60\",\"tooltip\":\"field10\",\"custom\":null,\"custom2\":null},\"14\":{\"label\":\"Email\",\"value\":\"aallen@citizensfirst.com\",\"type\":\"email\",\"validation\":\"email\",\"required\":\"0\",\"min\":\"\",\"max\":\"\",\"tooltip\":\"field11\",\"custom\":\"\",\"custom2\":\"\"},\"15\":{\"label\":\"Phone\",\"value\":\"920-236-7040\",\"type\":\"text\",\"validation\":\"\",\"required\":\"0\",\"min\":\"0\",\"max\":\"60\",\"tooltip\":\"field12\",\"custom\":null,\"custom2\":null},\"16\":{\"label\":\"Best+time+to+call\",\"value\":\"8am - 5pm \",\"type\":\"text\",\"validation\":\"\",\"required\":\"0\",\"min\":\"0\",\"max\":\"60\",\"tooltip\":\"field13\",\"custom\":null,\"custom2\":null},\"17\":{\"label\":\"title\",\"value\":\"Rate your most recent call to our Call Center\",\"type\":null,\"validation\":null,\"required\":null,\"min\":null,\"max\":null,\"tooltip\":null,\"custom\":null,\"custom2\":null}}","added":"23 Aug 2013 (09:45)","form_id":"1","form_name":"Call Center","seen":"1"} Quote Link to comment Share on other sites More sharing options...
Psycho Posted August 29, 2013 Share Posted August 29, 2013 (edited) Are the fields in the records always in the same order/positions? For example, 'email' is at index #16 in the subarray for 'content'? If so, it's easy. If not, it will take a little more code. but, we'd need some way to determine which fields go with which values. I'd use the labels, but some are very long. Will the tooltips be consistent for each value? Also, I see you want to output first name and last name in different columns, but the input data only has a single value for "name". You can create a rule for parsing the name into different values, but need to decide how to handle when there aren't exactly two "name" parts in the values. Edit: one last question. I see there is a field for city and another for "if other city, please specify". They both have values. Do you want the "other" city if it has a value, else use the city value from the drop-down? Edited August 29, 2013 by Psycho Quote Link to comment Share on other sites More sharing options...
jor133d Posted August 29, 2013 Author Share Posted August 29, 2013 Yes, they will always be in the same order and position. The titles can change to match the position if that is easier. Same with name, there is no real reason to have first and last name in different columns. There can be just one field for it called name. Quote Link to comment Share on other sites More sharing options...
molossus Posted August 29, 2013 Share Posted August 29, 2013 Maybe this can help , PHPexcel : http://phpexcel.codeplex.com extract the folder put it on xampp, mamp ,or wamp , run example 5 Go to: xampp/htdocs/phpexcel/Examples look for these files : 05featuredemo.php and 05featuredemo.inc.php run 05featuredemo.php to see the excel that it generates , also look at 05featuredemo.inc.php to see how the table values are being generated Quote Link to comment Share on other sites More sharing options...
Psycho Posted August 29, 2013 Share Posted August 29, 2013 Ok, here you go. I could actually make this more efficient (i.e. not have to traverse over the $lines array), but doing it this way makes it so much easier to debug - just do a print_r() on the array to see what it contains. <?php //Set directory of the input files $directory = '../submissions/'; session_start(); if (!isset($_SESSION['user_email'])) { header( 'Location: login.php'); } //Get array of all files $files = glob("{$directory}*.subm"); //Process the files into a combined array $lines = array(); foreach($files as $file) { $record = json_decode(file_get_contents($file), 1); $content = json_decode($record['content'], 1); //Create flat array for this record $line = array( 'Date' => $record['added'], 'Form Id' => $record['form_id'], 'Name' => $content[13]['value'], 'Email' => $content[14]['value'], 'Phone Number' => $content[15]['value'], 'Best time to contact' => $content[16]['value'], 'City' => (!empty($content[11]['value'])) ? $content[11]['value'] : $content[10]['value'], 'Rating' => $content[3]['value'], 'Reason for rating' => $content[4]['value'] ); //Add current record to combined array $lines[] = $line; } //Create the output data //---------------------- //Build the header row $output = implode("\t", array_keys($lines[0])) . "\n"; //Add each record to the output foreach($lines as $line) { $output .= implode("\t", $line) . "\n"; } //Output the file header("Content-type: application/x-msdownload"); header("Content-Disposition: attachment; filename=form_data.xls"); header("Pragma: no-cache"); header("Expires: 0"); print "$output"; ?> Quote Link to comment Share on other sites More sharing options...
Psycho Posted August 29, 2013 Share Posted August 29, 2013 Maybe this can help , PHPexcel : http://phpexcel.codeplex.com extract the folder put it on xampp, mamp ,or wamp , run example 5 Go to: xampp/htdocs/phpexcel/Examples look for these files : 05featuredemo.php and 05featuredemo.inc.php run 05featuredemo.php to see the excel that it generates , also look at 05featuredemo.inc.php to see how the table values are being generated His issue is not in creating the file - it is in processing the input data into the appropriate format for the output. Quote Link to comment Share on other sites More sharing options...
jor133d Posted August 30, 2013 Author Share Posted August 30, 2013 I tried your code and all I seem to get is a blank document. Would you consider taking a look at my code? I can zip it all up for you. If you would consider this, how much would you charge? Thanks! Quote Link to comment Share on other sites More sharing options...
Psycho Posted August 30, 2013 Share Posted August 30, 2013 If you are getting a blank page, it's probably because it is not finding the files. I took the content you provided in reply #3 and put it in a file named test.subm and that file was located in a folder titled "submissions" that was at the same level as the folder in which the executed file was running. Do the files have "subm" as an extension? I assumed so and put the period in the pattern used to find the files. Quote Link to comment Share on other sites More sharing options...
jor133d Posted August 30, 2013 Author Share Posted August 30, 2013 Yeah, gosh, that was silly. I have it working now. You have been so helpful. PM me your paypal. At the very least I can buy you a beer, ha! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.