PHPExcel帮助-如何在自定义表格布局中使用PHPexcel导出数据 [英] PHPExcel help - How to Export data with PHPexcel in an custom table layout
本文介绍了PHPExcel帮助-如何在自定义表格布局中使用PHPexcel导出数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我对PHPExcel相当陌生，并且正在学习PHP.我设法使PHPexcel通过下面的代码从SQL导出数据.现在，它只是以基本样式输出，这意味着它刚刚序列化了SQL中的数据，下一条记录将显示在下一行中.我添加了一张我想完成的图像，这是PHPexcel可能实现的还是我梦dream以求的.
I am quite new to PHPExcel and learning PHP as I go. I managed to get PHPexcel to export my data from SQL via the code below. Right now its just outputting in basic style, meaning its just serialized the data from SQL and next record would show up in the next row. I added an image of what I would like to accomplish, is this something possible with PHPexcel or am I dreaming.
如果有人可以向我发送一个有关如何完成此操作的快速示例，将不胜感激.
If anyone could send me a quick example of how to accomplish this it would be greatly appreciated.
图片 http://postimg.org/image/m3n60hn25/
下面是一个简单的序列化导出的工作示例.
Below is a working sample of a simple serialized export.
<?php
// connection with the database
$dbhost = "localhost";
$dbuser = "IMC_COE2";
$dbpass = "XXX";
$dbname = "IMC_COE2";
mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);
// require the PHPExcel file
require '/Excelphp/Classes/PHPExcel.php';
// simple query
$query = 'SELECT client, team_name,support_team_prime,prime_comments,support_team_backup,backup_comments,escalation1,escalation1_comments,escalation2,escalation2_comments,escalation3,escalation3_comments,escalation4,escalation4_comments,note FROM tbl_address ORDER by team_name DESC';
$headings = array('Client Name','Team Name','Prime Contact','Comments','Backup Contacts','Comments','Escalation 1','Comments','Escalation 2','Comments','Escalation 3','Comments','Escalation 4','Comments','Additional notes');
if ($result = mysql_query($query) or die(mysql_error())) {
// Create a new PHPExcel object
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('List of Users');
$rowNumber = 1;
$col = 'A';
foreach($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$heading);
$col++;
}
// Loop through the result set
$rowNumber = 2;
while ($row = mysql_fetch_row($result)) {
$col = 'A';
foreach($row as $cell) {
$objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
$col++;
}
$rowNumber++;
}
// Freeze pane so that the heading line won't scroll
$objPHPExcel->getActiveSheet()->freezePane('A2');
// Save as an Excel BIFF (xls) file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="userList.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit();
}
echo 'a problem has occurred... no data retrieved from the database';
10月2日更新: 这是我现在的位置，我唯一的问题是PHPExcel的导出仅显示1条记录.我无法找出丢失的代码，这些代码将向我显示所有格式和单元格数组相同的记录.
UPDATE October 2nd: Here is where I am at now, the only problem I have is that PHPExcel's export is only showing 1 record. I cant figureout the missing code that will show me all the records in the same format and cell array.
图片: http://postimg.org/image/8gicg5gtl/8a918f3e/
到目前为止，我的工作进展顺利，但仍然没有成功:
Heres my progress so far but still not getting a successful loop:
<?php
/** PHPExcel */
require_once '/Excelphp/Classes/PHPExcel.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
$objPHPExcel->getActiveSheet()->mergeCells('B2:D2');
$objPHPExcel->getActiveSheet()->mergeCells('B4:D4');
$objPHPExcel->getActiveSheet()->mergeCells('B7:D7');
$objPHPExcel->getActiveSheet()->mergeCells('B12:D12');
$objPHPExcel->getActiveSheet()->mergeCells('C3:D3');
$objPHPExcel->getActiveSheet()->mergeCells('B13:D13');
$objPHPExcel->getActiveSheet()->getStyle('C3:D3')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('C5:D5')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('C6:D6')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('C8:D8')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('C9:D9')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('C10:D10')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('C11:D11')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('B13:D13')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
//Setting for borders
$styleArray = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN,'color' => array('argb' => 'FFA0A0A0'),),),);
$objPHPExcel->getActiveSheet()->getStyle('B2:B13')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('C2:C13')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('D2:D13')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B2:D2')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B3:D3')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B4:D4')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B5:D5')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B6:D6')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B7:D7')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B8:D8')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B9:D9')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B10:D10')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B11:D11')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B12:D12')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B13:D13')->applyFromArray($styleArray);
//Background color on cells
$objPHPExcel->getActiveSheet()->getStyle('B2:D2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFCCE5FF');
$objPHPExcel->getActiveSheet()->getStyle('B4:D4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFCCE5FF');
$objPHPExcel->getActiveSheet()->getStyle('B7:D7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFCCE5FF');
$objPHPExcel->getActiveSheet()->getStyle('B12:D12')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFCCE5FF');
$objPHPExcel->getActiveSheet()->getStyle('B7:D7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFCCE5FF');
$objPHPExcel->getActiveSheet()->getStyle('B3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
$objPHPExcel->getActiveSheet()->getStyle('B5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
$objPHPExcel->getActiveSheet()->getStyle('B6')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
$objPHPExcel->getActiveSheet()->getStyle('B8')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
$objPHPExcel->getActiveSheet()->getStyle('B9')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
$objPHPExcel->getActiveSheet()->getStyle('B10')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
$objPHPExcel->getActiveSheet()->getStyle('B11')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '')
->setCellValue('B1', '')
->setCellValue('C1', '')
->setCellValue('D1', '')
->setCellValue('E1', '')
->setCellValue('A14', '')
->setCellValue('B14', '')
->setCellValue('C14', '')
->setCellValue('D14', '')
->setCellValue('E14', '')
->setCellValue('B3', 'Client:')
->setCellValue('B5', 'Prime:')
->setCellValue('B4', 'Support group contacts')
->setCellValue('B6', 'Backup:')
->setCellValue('B7', 'Escalations')
->setCellValue('B8', 'Escalation 1:')
->setCellValue('B9', 'Escalation 2:')
->setCellValue('B10', 'Escalation 3:')
->setCellValue('B11', 'Escalation 4:')
->setCellValue('B12', 'Notes');
// SQl database connections
$db = mysql_connect("localhost", "IMC_COE2", "IMC123");
mysql_select_db("IMC_COE2",$db);
$sql="select client, team_name,support_team_prime,prime_comments,support_team_backup,backup_comments,escalation1,escalation1_comments,escalation2,escalation2_comments,escalation3,escalation3_comments,escalation4,escalation4_comments,note from tbl_address ORDER by team_name";
$result=mysql_query($sql);
$numrows=mysql_num_rows($result);
if ($numrows>0)
{
$row=2;
while($data=mysql_fetch_array($result))
{
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('C'.$row, $data['client'])
->setCellValue('B'.$row, $data['team_name'])
->setCellValue('C'.($row+3), $data['support_team_prime'])
->setCellValue('D'.($row+3), $data['prime_comments'])
->setCellValue('C'.($row+4), $data['support_team_backup'])
->setCellValue('D'.($row+4), $data['backup_comments'])
->setCellValue('C'.($row+6), $data['escalation1'])
->setCellValue('D'.($row+6), $data['escalation1_comments'])
->setCellValue('C'.($row+7), $data['escalation2'])
->setCellValue('D'.($row+7), $data['escalation2_comments'])
->setCellValue('C'.($row+8), $data['escalation3'])
->setCellValue('D'.($row+8), $data['escalation3_comments'])
->setCellValue('C'.($row+9), $data['escalation4'])
->setCellValue('D'.($row+9), $data['escalation4_comments'])
->setCellValue('B'.($row+10), $data['note']);
}
}
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Directory Tool Full dump');
// 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 (Excel5)
ob_end_clean();
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Export-Directory Tool.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit;
?>
推荐答案
这是最终结果，仍然需要调整，但是非常感谢Mark Baker的帮助.加载excel文件大约需要1-2分钟，但是它可以工作.我要弄清楚如何优化代码以获得最佳性能.
Here is the final result, still needs tweaking but big thanks to Mark Baker who helped me out. It takes about 1-2 minutes to load the excel file but it works. I gonna figure out how to optimize the code for best performance.
<?php
/** PHPExcel */
require_once '/Excelphp/Classes/PHPExcel.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
$rows=2;
//This is the hard coded *non dynamic* cell formatting
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
$objPHPExcel->getActiveSheet()->getSheetView()->setZoomScale(85);
// SQl database connections
$db = mysql_connect("localhost", "IMC_COE2", "IMC123");
mysql_select_db("IMC_COE2",$db);
$sql="select client, team_name,support_team_prime,prime_comments,support_team_backup,backup_comments,escalation1,escalation1_comments,escalation2,escalation2_comments,escalation3,escalation3_comments,escalation4,escalation4_comments,note from tbl_address ORDER by team_name";
$result=mysql_query($sql);
$numrows=mysql_num_rows($result);
if ($numrows>0)
{
while($data=mysql_fetch_array($result))
{
//This section is the actual data import fromt he SQL database *dont touch*
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('C'.($rows+1), $data['client']) //this will give cell C2.
->setCellValue('B'.$rows, $data['team_name']) // this will give cell B2
->setCellValue('C'.($rows+3), $data['support_team_prime']) //this will give C5
->setCellValue('D'.($rows+3), $data['prime_comments']) // This will give D5
->setCellValue('C'.($rows+4), $data['support_team_backup']) //This will give C6
->setCellValue('D'.($rows+4), $data['backup_comments']) //This will give D6 etc...
->setCellValue('C'.($rows+6), $data['escalation1'])
->setCellValue('D'.($rows+6), $data['escalation1_comments'])
->setCellValue('C'.($rows+7), $data['escalation2'])
->setCellValue('D'.($rows+7), $data['escalation2_comments'])
->setCellValue('C'.($rows+8), $data['escalation3'])
->setCellValue('D'.($rows+8), $data['escalation3_comments'])
->setCellValue('C'.($rows+9), $data['escalation4'])
->setCellValue('D'.($rows+9), $data['escalation4_comments'])
->setCellValue('B'.($rows+11), $data['note']);
//Row height adjustments
$objPHPExcel->getActiveSheet()
->getRowDimension($rows+3)
->setRowHeight(100);
$objPHPExcel->getActiveSheet()
->getRowDimension($rows+4)
->setRowHeight(100);
$objPHPExcel->getActiveSheet()
->getRowDimension($rows+6)
->setRowHeight(100);
$objPHPExcel->getActiveSheet()
->getRowDimension($rows+7)
->setRowHeight(100);
$objPHPExcel->getActiveSheet()
->getRowDimension($rows+8)
->setRowHeight(100);
$objPHPExcel->getActiveSheet()
->getRowDimension($rows+9)
->setRowHeight(100);
$objPHPExcel->getActiveSheet()
->getRowDimension($rows+11)
->setRowHeight(100);
//Cell Merging
$objPHPExcel->getActiveSheet()->mergeCells('B'.$rows.':D'.$rows);
$objPHPExcel->getActiveSheet()->mergeCells('B'.($rows+2).':D'.($rows+2));
$objPHPExcel->getActiveSheet()->mergeCells('B'.($rows+5).':D'.($rows+5));
$objPHPExcel->getActiveSheet()->mergeCells('B'.($rows+10).':D'.($rows+10));
$objPHPExcel->getActiveSheet()->mergeCells('C'.($rows+1).':D'.($rows+1));
$objPHPExcel->getActiveSheet()->mergeCells('B'.($rows+11).':D'.($rows+11));
//Cell Wraptext
$objPHPExcel->getActiveSheet()->getStyle('C'.($rows+1).':D'.($rows+1))->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('C'.($rows+3).':D'.($rows+3))->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('C'.($rows+4).':D'.($rows+4))->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('C'.($rows+6).':D'.($rows+6))->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('C'.($rows+7).':D'.($rows+7))->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('C'.($rows+8).':D'.($rows+8))->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('C'.($rows+9).':D'.($rows+9))->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('B'.($rows+11).':D'.($rows+11))->getAlignment()->setWrapText(true);
//Background color on cells
$objPHPExcel->getActiveSheet()->getStyle('B'.$rows.':D'.$rows)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF9BC2E6');
$objPHPExcel->getActiveSheet()->getStyle('B'.($rows+2).':D'.($rows+2))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF9BC2E6');
$objPHPExcel->getActiveSheet()->getStyle('B'.($rows+5).':D'.($rows+5))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF9BC2E6');
$objPHPExcel->getActiveSheet()->getStyle('B'.($rows+10).':D'.($rows+10))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF9BC2E6');
$objPHPExcel->getActiveSheet()->getStyle('B'.($rows+1))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6F1FA');
$objPHPExcel->getActiveSheet()->getStyle('B'.($rows+3))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6F1FA');
$objPHPExcel->getActiveSheet()->getStyle('B'.($rows+4))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6F1FA');
$objPHPExcel->getActiveSheet()->getStyle('B'.($rows+6))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6F1FA');
$objPHPExcel->getActiveSheet()->getStyle('B'.($rows+7))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6F1FA');
$objPHPExcel->getActiveSheet()->getStyle('B'.($rows+8))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6F1FA');
$objPHPExcel->getActiveSheet()->getStyle('B'.($rows+9))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6F1FA');
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('B'.($rows+1), 'Client:')
->setCellValue('B'.($rows+3), 'Prime:')
->setCellValue('B'.($rows+2), 'Support group contacts')
->setCellValue('B'.($rows+4), 'Backup:')
->setCellValue('B'.($rows+5), 'Escalations')
->setCellValue('B'.($rows+8), 'Escalation 1:')
->setCellValue('B'.($rows+7), 'Escalation 2:')
->setCellValue('B'.($rows+8), 'Escalation 3:')
->setCellValue('B'.($rows+9), 'Escalation 4:')
->setCellValue('B'.($rows+10), 'Notes');
$rows+=14;
}
}
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Directory Tool Full dump');
// 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 (Excel5)
ob_end_clean();
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Export-Directory Tool.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit;
?>
这篇关于PHPExcel帮助-如何在自定义表格布局中使用PHPexcel导出数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
