Jump to content

.xls export


jor133d

Recommended Posts

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

post-53792-0-31370900-1377800960_thumb.png

Link to comment
Share on other sites

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"}
Link to comment
Share on other sites

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 by Psycho
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

?>
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.