Excel found unreadable content in '*.xlsx'. 10 is too many columns?

Topics: User Forum
Nov 13, 2010 at 12:55 AM

In a file which has been working, I am suddenly getting the error:

Excel found unreadable content in '*.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

I have determined that this only occurs when I have more than 9 columns of data being output. If I comment out everything past column I, the file is readable. If I comment out column I, show column J and comment out K and beyond, it still works. The file is fairly large (a few thousand rows of data) and does not seem to matter what data is in the columns that I comment out (I have tried rearranging columns to no effect).

The report works perfectly with fewer columns and I can put some things in later columns (for instance, you'll see that M1, M2, N1 and N2 are populated by the code) but when including more than 9 columns in my loop it is causing the error.

Server is a Windows 2003 / IIS6 / PHP 5.2.14 setup. PHPExcel is 1.7.4. My apologies for the length, here is the full code of my report generation:

<html>
<head>
</head>
<body onload="parent.stopWait();">
<?php
/** Error reporting */
error_reporting(E_ALL);

/** Include path **/
ini_set('include_path', ini_get('include_path').';C:\\php\\PEAR\\PHPExcel\\;C:\\program files\\php\\pear\\PHPExcel\\');

/** PHPExcel */
include 'PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';
$objPHPExcel = new PHPExcel();

// Set properties
// echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Reporting");
$objPHPExcel->getProperties()->setLastModifiedBy("Reporting");
$objPHPExcel->getProperties()->setTitle("Dashboard By Sales Rep");
$objPHPExcel->getProperties()->setSubject("Dashboard By Sales Rep");
$objPHPExcel->getProperties()->setDescription("List of assigned clients by sales rep.");

$objPHPExcel->setActiveSheetIndex(0);

// Here we populate the cells
include '../scripts/config.inc.php'; // Get database settings/variables. Connect to DB
$query = "SELECT tbl_Companies.salesRep, tbl_Companies.name, clientstatus.description as clientStatus, coalesce(calls.colorCode, \"#00ff00\") as colorCode, calls.callDate, callstatus.description as callStatus, ";
$query .= "calls.spokeTo, calls.nextCallBack, replace( trim( tbl_Companies.state ) , '', NULL ) as state, salestatus.description as saleStatus, calls.cycleStatus ";
$query .= "FROM tbl_companies AS tbl_Companies LEFT JOIN tbl_lastcalls ON tbl_lastcalls.companyID = tbl_Companies.id LEFT JOIN tbl_calls AS calls ON calls.id = tbl_lastcalls.callID ";
$query .= "LEFT JOIN tbl_statusdetails as clientstatus on clientstatus.id = tbl_Companies.clientStatus ";
$query .= "LEFT JOIN tbl_statusdetails as callstatus ON callstatus.id = calls.callStatus ";
$query .= "LEFT JOIN tbl_statusdetails as salestatus ON salestatus.id = calls.saleStatus ";
$query .= "WHERE salesRep <> '' AND salesRep is not null ";
$query .= "ORDER BY salesRep, name;";

// Column headers
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Sales Rep');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Company Name');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Client Status');
$objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Color Code');
$objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Last Call Date');
$objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Last Call Status');
$objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Last Call Spoke With');
$objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Next Callback Date');
$objPHPExcel->getActiveSheet()->SetCellValue('I1', 'Last Call Sale Status');
$objPHPExcel->getActiveSheet()->SetCellValue('J1', 'State');
$objPHPExcel->getActiveSheet()->SetCellValue('K1', 'Sale Cycle Status');
$objPHPExcel->getActiveSheet()->SetCellValue('M1', 'Generated on:');
$objPHPExcel->getActiveSheet()->SetCellValue('N1', date('m/d/Y H:i:s'));
$objPHPExcel->getActiveSheet()->SetCellValue('M2', 'Number of Records:');

$i = 1;
if ($result = mysqli_query($dbConn, $query)) {
	$objPHPExcel->getActiveSheet()->SetCellValue('N2', mysqli_num_rows($result));
	while ($row = mysqli_fetch_assoc($result)) {
		$i++;
		
		// Dump row values into cells
		$objPHPExcel->getActiveSheet()->SetCellValue('A' . $i, $row['salesRep']);
		$objPHPExcel->getActiveSheet()->SetCellValue('B' . $i, $row['name']);
		$objPHPExcel->getActiveSheet()->SetCellValue('C' . $i, $row['clientStatus']);
		$objPHPExcel->getActiveSheet()->SetCellValue('D' . $i, $row['colorCode']);
		$objPHPExcel->getActiveSheet()->SetCellValue('E' . $i, $row['callDate']);
		$objPHPExcel->getActiveSheet()->SetCellValue('F' . $i, $row['callStatus']);
		$objPHPExcel->getActiveSheet()->SetCellValue('G' . $i, $row['spokeTo']);
		$objPHPExcel->getActiveSheet()->SetCellValue('H' . $i, $row['nextCallBack']);
		$objPHPExcel->getActiveSheet()->SetCellValue('I' . $i, $row['saleStatus']);
		$objPHPExcel->getActiveSheet()->SetCellValue('J' . $i, $row['state']);
		$objPHPExcel->getActiveSheet()->SetCellValue('K' . $i, $row['cycleStatus']);
				
		// Style the color code
		$objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
		$objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getFill()->getStartColor()->setRGB(str_replace('#', '', $row['colorCode']));
		
		// Format the date field
		$objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
		$objPHPExcel->getActiveSheet()->getStyle('H' . $i)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
		
	}
}

// Auto-fit the columns
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Report');
		
// Save Excel 2007 file
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

?>
<!--
<?php
echo $query . "\r\n";
echo mysqli_error($dbConn) . "\r\n";
?>
-->
</body>
</html>

Thank you for any suggestions. For now, I am spitting out the data with two of the columns shown as empty but I would like to be able to provide all of the data being requested.

Coordinator
Nov 13, 2010 at 11:33 AM

This is a new one on me, and I regularly work with workbooks that contain more than 10 columns. Is there anything in the values of the saleStatus and cycleStatus in your database that could be causing the problem. What datatype are the columns? What are typical data values? Do they contain NULLS?

Nov 13, 2010 at 2:28 PM

It doesn't seem to matter which 9 columns I have -- I can comment out two fields that are currently working and the sheet appears perfectly fine.

To answer your specific questions, saleStatus and cycleStatus can be null, but otherwise are only characters (no numbers or punctuation, they are enumerated values in the database). But they will appear in the sheet just fine if I comment out say State and nextCallBack or salesRep and name. I can show the column headers but it seems to be something about drawing them in the loop that breaks the sheet.

Coordinator
Nov 13, 2010 at 9:21 PM

I really am stumped by this. I've never experienced it, and can see no logical reason why PHPExcel should do this... the library doesn't even know that it's methods are being called from within a loop.

Is it possible for you to create a simple script (not dependent on a database) that will replicate this problem?

Nov 14, 2010 at 9:51 PM
It appears to be a data size issue -- When using around 10,000 rows with the below it created a document just fine but setting it up to 40,000 I consistently get the error. 
Am I running into a memory limit with the phpExcel object? The script memory limit on the server is 256M



<html> <head> </head> <body onload="parent.stopWait();"> <?php function rand_str($length = 32, $chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890') { // Length of character list $chars_length = (strlen($chars) - 1); // Start our string $string = $chars{rand(0, $chars_length)}; // Generate random string for ($i = 1; $i < $length; $i = strlen($string)) { // Grab a random character from our list $r = $chars{rand(0, $chars_length)}; // Make sure the same two characters don't appear next to each other if ($r != $string{$i - 1}) $string .= $r; } // Return the string return $string; } /** Error reporting */ error_reporting(E_ALL); /** Include path **/ ini_set('include_path', ini_get('include_path').';C:\\php\\PEAR\\PHPExcel\\;C:\\program files\\php\\pear\\PHPExcel\\'); /** PHPExcel */ include 'PHPExcel.php'; /** PHPExcel_Writer_Excel2007 */ include 'PHPExcel/Writer/Excel2007.php'; // Create new PHPExcel object. Start drawing wait bar /* * echo "<html><head><title>MCA CRM Report Generator</title></head><body>\r\n"; * echo "<span id=\"preload\">Generating Report, please wait...</span>\r\n"; * echo "<span id=\"loadbar\"></span>\r\n"; */ $objPHPExcel = new PHPExcel(); // Set properties // echo date('H:i:s') . " Set properties\n"; $objPHPExcel->getProperties()->setCreator("MCA CRM Reporting"); $objPHPExcel->getProperties()->setLastModifiedBy("MCA CRM Reporting"); $objPHPExcel->getProperties()->setTitle("Dashboard By Sales Rep"); $objPHPExcel->getProperties()->setSubject("Dashboard By Sales Rep"); $objPHPExcel->getProperties()->setDescription("List of assigned clients by sales rep."); // Add some data // echo date('H:i:s') . " Add some data\n"; $objPHPExcel->setActiveSheetIndex(0); // Here we populate the cells // echo date('H:i:s') . "Generating Report...\r\n"; $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Sales Rep'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Company Name'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Client Status'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Color Code'); $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Last Call Date'); $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Last Call Status'); $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Last Call Spoke With'); $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Next Callback Date'); $objPHPExcel->getActiveSheet()->SetCellValue('I1', 'Last Call Sale Status'); $objPHPExcel->getActiveSheet()->SetCellValue('J1', 'State'); $objPHPExcel->getActiveSheet()->SetCellValue('K1', 'Sale Cycle Status'); $objPHPExcel->getActiveSheet()->SetCellValue('M1', 'Generated on:'); $objPHPExcel->getActiveSheet()->SetCellValue('N1', date('m/d/Y H:i:s')); $objPHPExcel->getActiveSheet()->SetCellValue('M2', 'Number of Records:'); $i = 1; $x = rand(10000, 50000); if ($result = true) { $objPHPExcel->getActiveSheet()->SetCellValue('N2', 40000); while ($i <= 40000) { $i++; // Dump row values into cells $objPHPExcel->getActiveSheet()->SetCellValue('A' . $i, rand_str(rand(1,50))); $objPHPExcel->getActiveSheet()->SetCellValue('B' . $i, rand_str(rand(1,50))); $objPHPExcel->getActiveSheet()->SetCellValue('C' . $i, rand_str(rand(1,50))); $objPHPExcel->getActiveSheet()->SetCellValue('D' . $i, rand_str(rand(1,50))); $objPHPExcel->getActiveSheet()->SetCellValue('E' . $i, '1/1/2001'); $objPHPExcel->getActiveSheet()->SetCellValue('F' . $i, rand_str(rand(1,50))); $objPHPExcel->getActiveSheet()->SetCellValue('G' . $i, rand_str(rand(1,50))); $objPHPExcel->getActiveSheet()->SetCellValue('H' . $i, '1/2/2001'); $objPHPExcel->getActiveSheet()->SetCellValue('I' . $i, rand_str(rand(1,50))); $objPHPExcel->getActiveSheet()->SetCellValue('J' . $i, rand_str(2)); $objPHPExcel->getActiveSheet()->SetCellValue('K' . $i, rand(0, 6)); // Style the color code $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getFill()->getStartColor()->setRGB(str_replace('#', '', 'f0f0f0')); // Format the date field $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getNumberFormat()->setFormatCode('mm/dd/yyyy'); $objPHPExcel->getActiveSheet()->getStyle('H' . $i)->getNumberFormat()->setFormatCode('mm/dd/yyyy'); } } /* * echo "<script type=\"text/javascript\">\r\n"; * echo " document.getElementById('preload').innerHTML = 'Nearly finished, please wait...';\r\n"; * echo "</script>\r\n"; */ // echo date('H:i:s') . "Finished generation, cleanup and output...\r\n"; // Auto-fit the columns $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true); // Rename sheet $objPHPExcel->getActiveSheet()->setTitle('Report'); // Save Excel 2007 file // echo date('H:i:s') . " Write to Excel2007 format\n"; $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); // Echo done // echo date('H:i:s') . " Done writing file.\r\n"; /* * echo "<script type=\"text/javascript\">\r\n"; * echo " document.getElementById('preload').innerHTML = 'Finished!';\r\n"; * echo " window.location = '" . str_replace('.php', '.xlsx', $_SERVER["PHP_SELF"]) . "';\r\n"; * echo "</script>\r\n"; * echo "</body></html>"; */ ?> <?php echo "Done." ?> </body> </html>

Coordinator
Nov 14, 2010 at 10:15 PM

Normally a memory problem would give you a PHP Allowed memory size of xxxx bytes exhausted (tried to allocate yyyy bytes) error

However, if you believe that it could be a memory issue, try enabling cell caching. This will allow you to work with up to 3 times as many cells in the same memory, albeit at a cost in speed.

Nov 15, 2010 at 9:11 AM

I have no problem with creating xlsx files with over 200K rows, so if the number of rows appears to be the problem, I don't think phpexcel is the limiting factor.