.xls export

[jor133d]

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";
?>

[Psycho]

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.

[jor133d]

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"}

[Psycho]

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

[jor133d]

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.

[molossus]

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

[Psycho]

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";

?>

[Psycho]

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.

[jor133d]

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!

[Psycho]

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.

[jor133d]

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!