.xls export

PHP
1

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 assist me in having the export properly display in a new cell in the same row instead of a new row?

<?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]\	$temp[form_id]\	$mess[$skey]";
					}
					else
					{
						$line[$i*$skey] = "\	\	$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\
";
}

$data = "Date\	Form Id\	First Name\	Last Name\	Email\	Phone Number\	Best time to contact\	City\	Rating\	Reason for rating\
$data";

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=form_data.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$data";
?>
2

I’m not sure I see why you’re building the $line array using the index $i * $skey, though I’m no PHP expert. It seems as if you create an array called $line, then for each individual field you create a new entry in that array. Shouldn’t $i be the line number, so you’d be either setting $line[$i] = “$temp[added]” and so on, or $line[$i] .= “$mess[$skey]” ?


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] = "$temp[added]\	$temp[form_id]\	$mess[$skey]";
					}
					else
					{
						$line[$i] .= "\	$mess[$skey]";
					}

				}
			} // End of foreach (field loop)

			$i++;

Although to make the columns line up you’d have to do something about adding a tab when the field is empty, rather than just ignore it in the start of the loop. And I’m not sure what the data looks like, so it’s hard to say properly.

And, wouldn’t it be better to give it a “.csv” extension rather than “.xls”, as it’s not a proper xls binary file? I can see you can open it, so perhaps it doesn’t matter too much.