我正在使用PHPExcell导出包含20列的4000行数据。我想要在工作表中将每隔一行颜色区分开来,于是我写了一个循环,但返回给我的结果是“
FAtal Error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 24 bytes) in D:\Data\xampp\htdocs\Classes\PHPExcel\Style\Supervisor.php
(致命错误:内存大小限制为134217728字节,尝试在D:\Data\xampp\htdocs\Classes\PHPExcel\Style\Supervisor.php文件中分配24字节)
我已经花费了2个小时来解决这个问题,但我失败了。我找到的每个主题都相当古老,2010年或更早。我已经尝试过使用缓存,类似于以下内容:
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array( ' memoryCacheSize ' => '8MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
但这并没有帮助。你有什么想法可以解决这个问题吗?
在我的循环结束时,我使用的是生成 xlsx 并格式化单元格的循环:
$i = 2;
foreach($table as $row){
$objPHPExcel->getActiveSheet()
->setCellValue('A'.$i, $row[1])
->setCellValue('B'.$i, $row[2])
->setCellValue('C'.$i, $row[3])
->setCellValue('D'.$i, $row[4])
->setCellValue('E'.$i, $row[5])
->setCellValue('F'.$i, $row[6])
->setCellValue('G'.$i, $row[7])
->setCellValue('H'.$i, $row[8])
->setCellValue('I'.$i, $row[9])
->setCellValue('J'.$i, $row[10])
->setCellValue('K'.$i, $row[11])
->setCellValue('L'.$i, $row[12])
->setCellValue('M'.$i, $row[13])
->setCellValue('N'.$i, $row[14])
->setCellValue('O'.$i, $row[15])
->setCellValue('P'.$i, $row[16])
->setCellValue('Q'.$i, $row[17])
->setCellValue('R'.$i, $row[20])
->setCellValue('S'.$i, $row[21])
->setCellValue('T'.$i, $row[22]);
// Color every second row
if($i%2!=0){
$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':T'.$i)->getFill()->applyFromArray(
array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'startcolor' => array('rgb' => 'e8f4ff')
));
}
//color column "O"
if($i%2==0){
$objPHPExcel->getActiveSheet()->getStyle('O'.$i)->getFill()->applyFromArray(
array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'startcolor' => array('rgb' => 'fffacd')
));
}
else{
$objPHPExcel->getActiveSheet()->getStyle('O'.$i)->getFill()->applyFromArray(
array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'startcolor' => array('rgb' => 'fff9bd')
));
}
// color column "N"
if($i%2==0){
$objPHPExcel->getActiveSheet()->getStyle('N'.$i)->getFill()->applyFromArray(
array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'startcolor' => array('rgb' => 'eaffee')
));
}
else{
$objPHPExcel->getActiveSheet()->getStyle('N'.$i)->getFill()->applyFromArray(
array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'startcolor' => array('rgb' => 'ccffcc')
));
}
$i++;
}
编辑:: 我贴出完整代码,因为我不知道还能做什么。我创建了一个新的phpexcel对象,然后导入只有第一行作为标题和列名的空模板。其余单元格完全为空,并带有预设样式。我为10000行设置了样式。但它仍然返回相同的错误。下面是代码:
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');
if (PHP_SAPI == 'cli')
die('This example should only be run from a Web Browser');
/** Include PHPExcel */
require_once '../Classes/PHPExcel.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader = $objReader->load('components/com_uhlist/Classes/template.xlsx');
$template = $objReader->getActiveSheet();
$objPHPExcel->addExternalSheet($template);
$objPHPExcel->setActiveSheetIndex(1);
$user = JFactory::getUser();
$username = $user->get('username');
// Set document properties
$objPHPExcel->getProperties()->setCreator($username)
->setLastModifiedBy($username)
->setTitle("User & Host List");
// Add some data
// Aktywowanie arkusza
//$objPHPExcel->setActiveSheetIndex(-0);
// Export danych
$db = JFactory::getDBO();
$query = 'SELECT * FROM '.$db->getPrefix().'users_n_hosts';
$db->setQuery($query);
$table = $db->loadRowList();
$i = 2;
foreach($table as $row){
$objPHPExcel->getActiveSheet()
->setCellValue('A'.$i, $row[1])
->setCellValue('B'.$i, $row[2])
->setCellValue('C'.$i, $row[3])
->setCellValue('D'.$i, $row[4])
->setCellValue('E'.$i, $row[5])
->setCellValue('F'.$i, $row[6])
->setCellValue('G'.$i, $row[7])
->setCellValue('H'.$i, $row[8])
->setCellValue('I'.$i, $row[9])
->setCellValue('J'.$i, $row[10])
->setCellValue('K'.$i, $row[11])
->setCellValue('L'.$i, $row[12])
->setCellValue('M'.$i, $row[13])
->setCellValue('N'.$i, $row[14])
->setCellValue('O'.$i, $row[15])
->setCellValue('P'.$i, $row[16])
->setCellValue('Q'.$i, $row[17])
->setCellValue('R'.$i, $row[20])
->setCellValue('S'.$i, $row[21])
->setCellValue('T'.$i, $row[22]);
$i++;
}
// Nazwa arkusza
$objPHPExcel->getActiveSheet()->setTitle('User & Host');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="User&Host_'.date('d-m-Y').'.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
最新代码::将数据插入模板:
$objPHPExcel = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objPHPExcel->load('components/com_uhlist/Classes/template.xlsx');
$objPHPExcel->setActiveSheetIndex(0);
$user = JFactory::getUser();
$username = $user->get('username');
// Set document properties
$objPHPExcel->getProperties()->setCreator($username)
->setLastModifiedBy($username)
->setTitle("User & Host List");
// Export danych
$db = JFactory::getDBO();
$query = 'SELECT * FROM '.$db->getPrefix().'autocad_users_n_hosts';
$db->setQuery($query);
$table = $db->loadRowList();
$i = 2;
foreach($table as $row){
$objPHPExcel->getActiveSheet()
->setCellValue('A'.$i, $row[1])
->setCellValue('B'.$i, $row[2])
->setCellValue('C'.$i, $row[3])
->setCellValue('D'.$i, $row[4])
->setCellValue('E'.$i, $row[5])
->setCellValue('F'.$i, $row[6])
->setCellValue('G'.$i, $row[7])
->setCellValue('H'.$i, $row[8])
->setCellValue('I'.$i, $row[9])
->setCellValue('J'.$i, $row[10])
->setCellValue('K'.$i, $row[11])
->setCellValue('L'.$i, $row[12])
->setCellValue('M'.$i, $row[13])
->setCellValue('N'.$i, $row[14])
->setCellValue('O'.$i, $row[15])
->setCellValue('P'.$i, $row[16])
->setCellValue('Q'.$i, $row[17])
->setCellValue('R'.$i, $row[20])
->setCellValue('S'.$i, $row[21])
->setCellValue('T'.$i, $row[22]);
$i++;
}
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="User&Host_'.date('d-m-Y').'.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
memoryCacheSize
而不是memoryCacheSize
(前面不需要空格)。 - Aistis