Boardy Boardy - 5 months ago 22
SQL Question

Importing file contents directly into database - Excel spreadsheet needing repair

I am working on a project where I am storing files directly within a MySQL Database. The code is working perfectly fine, storing and downloading text files, pictures and PDF, but when using Excel sreadsheets it has a problem.

If I store an excel spread (.xlsx) file, the upload works fine, but when I download the file, the download seems to work fine, but when I open the spreadsheet I get the following:

We found a problem with some content in 'filename.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbench, click Yes?'


When I press
Yes
I then get the following:

Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.


When doing this, Excel loads up fine and everything looks OK, but why would I be getting this error, and why would it need to be repaired.

I am storing the file using the following:

private function writeFileToDatabase($fileName, $tmpName, $type, $fileSize, $fileArea, &$error)
{
$fileName = mysqli_escape_string($this->conn, $fileName);
$fileSize = mysqli_escape_string($this->conn, $fileSize);
$type = mysqli_escape_string($this->conn, $type);
$fileArea = mysqli_escape_string($this->conn, $fileArea);

//Open the uploaded file
$fh = fopen($tmpName, 'r');
if (!$fh)
{
$error = "Could not open uploaded file";
return false;
}
$content = fread($fh, filesize($tmpName));
$content = mysqli_escape_string($this->conn, $content);
fclose($fh);

//Store the file in the database
$query = "INSERT INTO file_store (RecordID, Area, FileName, ContentType, Size, FileContent) "
. "VALUES ('$this->recordID', '$fileArea', '$fileName', '$type', '$fileSize', '$content')";
$result = $this->conn->query($query);
}


I download the file using the following:

function downloadFile($fileID)
{
$fileID = mysqli_escape_string($this->conn, $fileID);

$query = "SELECT * FROM file_store WHERE id='$fileID'";
$result = $this->conn->query($query);
if ($result)
{
$data = $result->fetch_array();
header("Content-length: " . $data["Size"]);
header("Content-type: " . $data["ContentType"]);
header("Content-Disposition: attachment; filename=" . $data["FileName"]);
echo $data["FileContent"];
}
else
{
echo mysqli_error($this->conn);
}
}


Thanks for any help you can provide.

Answer

After

echo $data["FileContent"];

Try Adding this:

ob_clean();
flush();

If that still doesn't work - maybe try trapping for content type and if it's not a text file add another header:

header('Content-Transfer-Encoding: binary');