CODEWITHSUNDEEP
phpphpexcel
fred2
fred2

Reputation: 1110

PHPExcel creates 'unreadable content'

I've tried to fix this problem about 1000 different ways. Appreciate if anyone else can spot the problem.

I have code using PHPExcel that generates multiple Excel sheets and saves them to disk.

When opening everything from the second file onwards using MS Excel 2010, I get the error "Excel found unreadable content in FILENAME. Do you want to recover the contents of this workbook." The file is 'recovered' and works perfectly ok. Opening in OPen Office does not produce any errors.

Here's a simplified version of the code.

<?php
$filenames = array("filenames go here");

$sheet1 = PHPExcel_IOFactory::load($template1);
$writer1 = new PHPExcel_Writer_Excel2007($sheet1);
$writer1->save('somefilename.xlsx');
//This file opens without problem
$sheet1->disconnectWorksheets();
unset($sheet1);
unset($writer1);

$i = 0;
foreach($filenames as $file){
    $template = "template2.xlsx";
    $fileName = 'filename' . $i . ' .xlsx';
    $spreadsheet = PHPExcel_IOFactory::load($template);
    $objWriter = new PHPExcel_Writer_Excel2007($spreadsheet);
    $objWriter->save($fileName);
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);
    //This file throws error when opened in Excel 2007+
    $i++;
 }
?>

I've checked the following things:

  1. There's no obvious php error messages, whitespace or extraneous input that are to be corrupting the file.
  2. The template file opens fine in Excel 2010.
  3. I've tried using different PHPExcel writer methods, but they all produce the same problem.

The real code does a whole load of extra stuff, but I've checked that it is not responsible by commenting it out. The code above seems to be the only place where a problem could be introduced.

Any suggestions gratefully received.

====EDITED==== This is the error log produced by Excel:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
     <logFileName>error049120_01.xml</logFileName>
     <summary>Errors were detected in file 'C:\Users\USERNAME\AppData\Local\Temp\FILENAME.xlsx'</summary>
     -<additionalInfo>
           <info>Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.</info>
     </additionalInfo>
</recoveryLog>

Upvotes: 1

Views: 10430

Answers (5)

happymacarts
happymacarts

Reputation: 2604

I had a similar error on my file and followed the recommendation posted by Andrey Yanko, however I was still receiving similar errors. I found suggestions on another post and by adding an

"exit;"

at the end of my script that helped to remove the remaining errors

Upvotes: 10

Andrey Yanko
Andrey Yanko

Reputation: 166

This bug was related to configuration mbstring.func_overload in php.ini. You can use workaround to avoid this bug - just add at begin of your script:

if (function_exists('mb_internal_encoding')) {
    $oldEncoding=mb_internal_encoding();
    mb_internal_encoding('latin1');
}

and at the end:

if (function_exists('mb_internal_encoding'))
    mb_internal_encoding($oldEncoding);

It helped me, so, I believe it'll help you as well.

Upvotes: 4

fred2
fred2

Reputation: 1110

It appears that this bug was related to an individual xlsx file that I was using as a 'template'. The code works fine when another file is used as the template instead.

The original template did not seem to be corrupted, so somewhere along the line PHPExcel was causing a problem, but not sure where.

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212522

Obvious problems:

$sheet1 = PHPExcel_IOFactory::load($template1);
$writer1 = new PHPExcel_Writer_Excel2007($sheet1);
$writer1->save(sheet1);

save($sheet1) - $sheet1 isn't a filename, it's a PHPExcel object, a complete workbook... the save method expects the filename you want to save as its argument

foreach($filenames as $file){
    $template = "template2.xlsx";
    $fileName = 'filename.xlsx';
    $spreadsheet = PHPExcel_IOFactory::load($template);
    $objWriter = new PHPExcel_Writer_Excel2007($spreadsheet);
    $objWriter->save($fileName);
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);
}

Each loop saves using the same filename, overwriting any saves from previous iterations

If MS Excel complains about unreadable content, but still opens the file, it will normally tell you what has been discarded to make the file readable... but without knowing that message, or seeing a copy of the actual file, it's impossible to help you.

Upvotes: 2

Daniel A. White
Daniel A. White

Reputation: 191037

Could it be that your are saving it as .xls not .xlsx?

Opening a new .xlsx, renamed to have .xls, gives me a similar warning.

Upvotes: 1

Related Questions