For such a large amount of data I would not recommend tools like PHPExcel or ApachePOI (for Java) because of their memory requirements. I have struggled with similar task recently and I have found convenient (but maybe little bit fiddly) way to inject data into spreadsheets. Serverside generation or updating of Excel spreadsheets can be achieved thus simple XML editing. I have XLSX spreadsheet sitting on the server and every time data is gathered from dB, I unzip it using php. Then I access specific XML files that are holding contents of worksheets that need to be injected and insert data manually. Afterwards, I compress spreadsheet folder in order to distribute it as an regular XLSX file. Whole process is quite fast and reliable. Obviously, there are few issues and glitches related to inner organisation of XLSX/Open XML file (e. g. Excel tend to store all strings in separate table and use references to this table in worksheets). But when injecting only data like numbers and strings, it is not that hard. If anyone is interested, I can provide some code.
Okay, here goes sample code for this. I have tried to comment what it does, but feel free to ask for further explanation.
<?php
/**
* Class for serverside spreadsheet data injecting
* Reqs: unzip.php, zip.php (containing any utility functions able to unzip files & zip folders)
*
* Author: Poborak
*/
class DataInjector
{
//spreadsheet file, we inject data into this one
const SPREADSHEET_FILE="datafile.xlsx";
// specific worksheet into which data are being injected
const SPREADSHEET_WORKSHEET_FILE="/xl/worksheets/sheet7.xml";
//working directory, spreadsheet is extracted here
const WSPACE_DIR="Wspace";
// query for obtaining data from DB
const STORE_QUERY = "SELECT * FROM stores ORDER BY store_number ASC";
private $dbConn;
private $storesData;
/**
* @param mysqli $dbConn
*/
function __construct(mysqli $dbConn) {
$this->dbConn = $dbConn;
}
/**
* Main method for whole injection process
* First data are gathered from DB and spreadsheet is decompressed to workspace.
* Then injection takes place and spreadsheet is ready to be rebuilt again by zipping.
*
* @return boolean Informace o úspěchu
*/
public function injectData() {
if (!$this->getStoresInfoFromDB()) return false;
if (!$this->explodeSpreadsheet(self::SPREADSHEET_FILE,self::WSPACE_DIR)) return false;
if (!$this->injectDataToSpreadsheet(self::WSPACE_SUBDIR.self::SPREADSHEET_WORKSHEET_FILE)) return false;
if (!$this->implodeSpreadsheet(self::SPREADSHEET_FILE,self::WSPACE_DIR)) return false;
return true;
}
/**
* Decompress spreadsheet file to folder
*
* @param string $spreadsheet
* @param string $targetFolder
*
* @return boolean success/fail
*/
private function explodeSpreadsheet($spreadsheet, $targetFolder) {
return unzip($spreadsheet,$targetFolder);
}
/**
* Compress source folder to spreadsheet file
*
* @param string $spreadsheet
* @param string $sourceFolder
*
* @return boolean success/fail
*/
private function implodeSpreadsheet($spreadsheet, $sourceFolder) {
return zip($sourceFolder,$spreadsheet);
}
/**
* Loads data from DB to member variable $storesDetails (as array)
*
* @return boolean success/fail
*/
private function getStoresInfoFromDb() {
unset($this->storesData);
if ($stmt = $this->dbConn->prepare(self::STORE_QUERY)) {
$stmt->execute();
$stmt->bind_result($store_number, $store_regional_manager, $store_manager, $store_city, $store_address);
while ($stmt->fetch()) {
$this->storesData[trim($store_number)] = array(trim($store_regional_manager),trim($store_manager),trim($store_address),trim($store_city));
}
$stmt->close();
}
return true;
}
/**
* Injects data from member variable $storesDetails to spreadsheet $ws
*
* @param string $ws target worksheet
*
* @return boolean success/fail
*/
private function injectDataToSpreadsheet($ws) {
$worksheet = file_get_contents($ws);
if ($worksheet === false or empty($this->storesData) return false;
$xml = simplexml_load_string($worksheet);
if (!$xml) return false;
// Loop through $storesDetails array containing rows of data
foreach ($this->storesData as $std){
// For each row of data create new row in excel worksheet
$newRow = $xml->sheetData->addChild('row');
// Loop through columns values in rowdata
foreach ($std as $cbd){
// Save each column value into next column in worksheets row
foreach ($this->storesData as $cbd){
$newCell = $newRow->addChild('c');
$newCell->addAttribute('t', "inlineStr");
$newIs = $newCell->addChild('is');
// text has to be saved as utf-8 (otherwise the spreadsheet file become corrupted)
if (!mb_check_encoding($cbd, 'utf-8')) $cbd = iconv("cp1250","utf-8",$cbd);
$newT = $newIs->addChild('t',$cbd);
}
}
}
// Save xml data back to worksheet file
if (file_put_contents($ws, $xml->asXML()) !== false) return true;
}
}
?>
