Using PHPExcel I am trying to generate a file with names and projects obtained from the database. I am able to vertically print the names, but I want the project names to be printed as headings horizontally. Currently project titles are printed vertically. Here is what is available at the moment:

<?php require_once ('PHPExcel/Classes/PHPExcel.php'); include('inc/database_connection.php'); $conn = mysqli_connect(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD, DATABASE_NAME); $output=''; if (isset($_POST['export_excel'])) { $sql="SELECT username FROM user ORDER BY username ASC"; $sql2="SELECT * FROM project"; $result2=mysqli_query($conn, $sql2); if (!$result2) { die('Invalid query: ' . mysqli_error($conn)); } while($row2 = mysqli_fetch_array( $result2)) { $title[] = $row2['title']; } //echo '<th>'.$ttl.'</th>'; $result=mysqli_query($conn, $sql); if(!$result || mysqli_num_rows($result)>0) { if(isset($title)) { foreach($title as $ttl) { $output .= ' <table class = "table" bordered = "1"> <tr> <th>User</th> <th>'.$ttl.'</th> </tr> '; } } while($row = mysqli_fetch_array($result)) { $output .= ' <tr> <td>'.$row["username"].'</td> </tr> '; } $output .= '</table>'; header('Content-type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="projects.xls"'); echo $output; } } ?>

Names are printed vertically, I need to print out the names of the projects horizontally. How can this be done? thank

  • And here PHPExcel, if you display html? - Dmitry Kozlov
  • @DmitryKozlov I use PHPExcel to generate an Excel file from the data. - Yevgeniy Bagackiy
  • I see an example in which the html-table is output, which is issued from the content-type Excel, but there is no PHPExcel call - Dmitry Kozlov
  • Or is it some other or not a complete example - Dmitry Kozlov
  • @DmitryKozlov why then can I save this code as an excel file? - Yevgeniy Bagackiy

1 answer 1

As the commentators have already noted above, your script incorrectly generates an Excel document.

Excellent article on the topic of working with PHPExcel in Russian is presented here .

Let's try on the basis of the above article to slightly remake your script. Honestly, I did not understand how user names and project names should be related, so do not judge strictly.

 <?php require_once ('PHPExcel/Classes/PHPExcel.php'); include('inc/database_connection.php'); $conn = mysqli_connect(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD, DATABASE_NAME); // ΠŸΠΎΠ΄ΠΊΠ»ΡŽΡ‡Π°Π΅ΠΌ Excel2007.php require_once('PHPExcel/Writer/Excel2007.php'); // Π‘ΠΎΠ·Π΄Π°Π΅ΠΌ ΠΎΠ±ΡŠΠ΅ΠΊΡ‚ класса PHPExcel $xlsx = new PHPExcel(); // УстанавливаСм индСкс Π°ΠΊΡ‚ΠΈΠ²Π½ΠΎΠ³ΠΎ листа $xlsx->setActiveSheetIndex(0); // ΠŸΠΎΠ»ΡƒΡ‡Π°Π΅ΠΌ Π°ΠΊΡ‚ΠΈΠ²Π½Ρ‹ΠΉ лист $sheet = $xlsx->getActiveSheet(); // ΠŸΠΎΠ΄ΠΏΠΈΡΡ‹Π²Π°Π΅ΠΌ лист $sheet->setTitle('Имя листа'); // Π‘Ρ‚ΠΈΠ»ΡŒ Π·Π°Π³ΠΎΠ»ΠΎΠ²ΠΊΠ° $HeadStyle = array ( 'fill' => array ( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array ( 'rgb' => 'EAF1DD' ) ), 'font' => array ( 'bold' => true ), 'borders' => array ( 'top' => array ( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array ( 'rgb' => '808080' ) ), 'bottom' => array ( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array ( 'rgb' => '808080' ) ), 'left' => array ( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array ( 'rgb' => '808080' ) ), 'right' => array ( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array ( 'rgb' => '808080' ) ) ), 'alignment' => array ( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'wrap' => true ) ); if (isset($_POST['export_excel'])) { $sql="SELECT username FROM user ORDER BY username ASC"; $sql2="SELECT * FROM project"; $result2=mysqli_query($conn, $sql2); if (!$result2) { die('Invalid query: ' . mysqli_error($conn)); } while($row2 = mysqli_fetch_array( $result2)) { $title[] = $row2['title']; } $result=mysqli_query($conn, $sql); if(!$result || mysqli_num_rows($result)>0) { if(isset($title)) { $Column=0; foreach($title as $ttl) { // ЗаписываСм Π² ячСйку имя ΠΏΡ€ΠΎΠ΅ΠΊΡ‚Π° $sheet->setCellValueByColumnAndRow($Column,0,$ttl); // Π—Π°Π΄Π°Ρ‘ΠΌ ячСйкС ΡΡ‚ΠΈΠ»ΡŒ Π·Π°Π³ΠΎΠ»ΠΎΠ²ΠΊΠ° - Ρ†Π²Π΅Ρ‚, ΡˆΡ€ΠΈΡ„Ρ‚, Π³Ρ€Π°Π½ΠΈΡ†Ρ‹, Ρ†Π΅Π½Ρ‚Ρ€ΠΈΡ€ΠΎΠ²Π°Π½ΠΈΠ΅ $sheet->getStyleByColumnAndRow($Column,0)->applyFromArray($HeadStyle); $Column++; } } $Column=0; while($row = mysqli_fetch_array($result)) { // ЗаписываСм Π² ячСйку имя ΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚Π΅Π»Ρ $sheet->setCellValueByColumnAndRow($Column,1,$row["username"]); $Column++; } // Π’Ρ‹Π²ΠΎΠ΄ΠΈΠΌ Π·Π°Π³ΠΎΠ»ΠΎΠ²ΠΊΠΈ header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT" ); header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" ); header ( "Cache-Control: no-cache, must-revalidate" ); header ( "Pragma: no-cache" ); header ( "Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ); header ( "Content-Disposition: attachment; filename=\"projects.xlsx" ); // Π’Ρ‹Π²ΠΎΠ΄ΠΈΠΌ содСрТимоС Ρ„Π°ΠΉΠ»Π° $objWriter = new PHPExcel_Writer_Excel2007($xlsx); $objWriter->save('php://output'); } } ?>
  • In each project there are processes, each person is tied to each process. I want to display the names and processes to which each person is attached to each project. Thanks for the link and the converted example. One question is what is $i inside getStyleByColumnAndRow($i,1) ? - Yevgeniy Bagackiy
  • I understand this column, but where you designate it is Yevgeniy Bagackiy
  • Yes, that's right, it should have been $ Column. Thanks for pointing out the error, fixed it. - Andrey