如何用PHP在网站上将数据导出为Excel文件呢?
(1)普通方法
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:filename=" . $filename . ".xls");
$strexport = "分类名\t商品名\t数量\r";
foreach (
row) {
row['分类名'] . "\t";
row['商品名'] . "\t";
row['数量'] . "\r";
}
//$strexport = iconv("utf-8", "gb2312//IGNORE", $strexport);
exit($strexport);
这样做可以导出一个xls文件,可以在电脑上直接使用,如果想在手机上查看,还需要在本地用Office或WPS另存一下。
(2)使用PHPExcel
/** PHPExcel */
require IA_ROOT . '/Classes/PHPExcel.php';
/** PHPExcel_Writer_Excel2007 */
require IA_ROOT . '/Classes/PHPExcel/Writer/Excel2007.php';
// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();
// Set properties
echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Loveuzi");
$objPHPExcel->getProperties()->setLastModifiedBy("Loveuzi");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
// Add some data
//echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', '分类名');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', '商品名!');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', '数量');
$index = 2;
foreach ($list as $row) {
$objPHPExcel->getActiveSheet()->SetCellValue('A' . $index, $row['分类名']);
$objPHPExcel->getActiveSheet()->SetCellValue('B' . $index, $row['商品名']);
$objPHPExcel->getActiveSheet()->SetCellValue('C' . $index, $row['数量']);
$index++;
}
// Rename sheet
echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle($filename);
// 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";
//清除缓冲区,避免乱码
ob_end_clean();
header('pragma:public');
header("Content-Disposition:attachment;filename=$filename");
header('Cache-Control: max-age=0');
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save('php://output');
这样通过PHPExcel导出的Excel表可以直接被手机打开,且样式、大小都可以设置。
以下是样式设置的一些示例方法:
//设置宽度
objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); //内容自适应
//设置align(需要引入PHPExcel_Style_Alignment)
objPHPExcel->getActiveSheet()->getStyle( 'A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直方向上中间居中
//合并拆分单元格
objPHPExcel->getActiveSheet()->unmergeCells('A28:B28'); // A28:B28再拆分
//字体大小、粗体、字体、下划线、字体颜色(需引入PHPExcel_Style_Font、PHPExcel_Style_Color)
objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
objPHPExcel->getDefaultStyle()->getFont()->setName( 'Arial');
objPHPExcel->getActiveSheet()->getStyle( 'A3:E3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
objPHPExcel->getActiveSheet()->getProtection()->setSheet( true); // 为了使任何表保护,需设置为真
objPHPExcel->getActiveSheet()->getStyle( 'B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED); //去掉保护
//给单元格内容设置url超链接
objActSheet->getCell('E26')->getHyperlink()->setTooltip( 'Navigate to website'); //鼠标移上去连接提示信息