Xom9ik

DB to Excel (custom fields)

May 10th, 2018
157
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 5.31 KB | None | 0 0
  1. // Using https://github.com/PHPOffice/PHPExcel
  2. <?php
  3. require '/PHPExcel.php';
  4.     class Db
  5.     {
  6.         public static function getConnection()
  7.         {
  8.             $params = array('host' => 'localhost', 'dbname' => 'korlabDB', 'user' => 'root', 'password' => '12345');
  9.             $dsn = "mysql:host={$params['host']};dbname={$params['dbname']}";
  10.             $db = new PDO($dsn, $params['user'], $params['password']);
  11.             $db->exec("set names utf8");
  12.             return $db;
  13.         }
  14.     }
  15.     $db = Db::getConnection();
  16.     $stmt = $db->prepare('select * from users');
  17.     $stmt->execute();
  18.     $data = [];
  19.     $counter = 0;
  20.     echo $res['id'];
  21.     while($res = $stmt->fetch(PDO::FETCH_ASSOC)){
  22.         $data[$counter]['id'] = $res['id'];
  23.         $data[$counter]['username'] = $res['username'];
  24.         $data[$counter]['email'] = $res['email'];
  25.         $data[$counter]['name'] = $res['name'];
  26.         $data[$counter]['surname'] = $res['surname'];
  27.         $data[$counter]['middlename'] = $res['middlename'];
  28.  
  29.         $data[$counter]['specialty_1'] = $res['specialty_1'];
  30.         $data[$counter]['zno_competition_score_1'] = $res['zno_competition_score_1'];
  31.         $data[$counter]['priority_1'] = $res['priority_1'];
  32.  
  33.         $data[$counter]['specialty_2'] = $res['specialty_2'];
  34.         $data[$counter]['zno_competition_score_2'] = $res['zno_competition_score_2'];
  35.         $data[$counter]['priority_2'] = $res['priority_2'];
  36.  
  37.         $data[$counter]['specialty_3'] = $res['specialty_3'];
  38.         $data[$counter]['zno_competition_score_3'] = $res['zno_competition_score_3'];
  39.         $data[$counter]['priority_3'] = $res['priority_3'];
  40.  
  41.         $counter++;
  42.     }
  43.  
  44.     $objPHPExcel = new PHPExcel();
  45.     $objPHPExcel->getProperties()->setCreator("MRPL-server")
  46.                              ->setLastModifiedBy("MRPL-server")
  47.                              ->setTitle("Users")
  48.                              ->setSubject("users feedback")
  49.                              ->setDescription("Description")
  50.                              ->setKeywords("office 2007 openxml php")
  51.                              ->setCategory("result file");
  52.     $objPHPExcel->setActiveSheetIndex(0)
  53.         ->setCellValue('A1', 'id')
  54.         ->setCellValue('B1', 'Имя пользователя')
  55.         ->setCellValue('C1', 'Email')
  56.         ->setCellValue('D1', 'Имя')
  57.         ->setCellValue('E1', 'Фамилия')
  58.         ->setCellValue('F1', 'Отчество')
  59.  
  60.         ->setCellValue('G1', 'Специальность №1')
  61.         ->setCellValue('H1', 'Конкурсный балл №1')
  62.         ->setCellValue('I1', 'Приоритет №1')
  63.  
  64.         ->setCellValue('J1', 'Специальность №2')
  65.         ->setCellValue('K1', 'Конкурсный балл №2')
  66.         ->setCellValue('L1', 'Приоритет №2')
  67.  
  68.         ->setCellValue('M1', 'Специальность №3')
  69.         ->setCellValue('N1', 'Конкурсный балл №3')
  70.         ->setCellValue('O1', 'Приоритет №3');
  71.     for($i = 0; $i<count($data); $i++ ){
  72.         $j = $i + 2;
  73.         $objPHPExcel->setActiveSheetIndex(0)
  74.             ->setCellValue('A'.$j, $data[$i]['id'])
  75.             ->setCellValue('B'.$j, $data[$i]['username'])
  76.             ->setCellValue('C'.$j, $data[$i]['email'])
  77.             ->setCellValue('D'.$j, $data[$i]['name'])
  78.             ->setCellValue('E'.$j, $data[$i]['surname'])
  79.             ->setCellValue('F'.$j, $data[$i]['middlename'])
  80.  
  81.             ->setCellValue('G'.$j, $data[$i]['specialty_1'])
  82.             ->setCellValue('H'.$j, $data[$i]['zno_competition_score_1'])
  83.             ->setCellValue('I'.$j, $data[$i]['priority_1'])
  84.  
  85.             ->setCellValue('J'.$j, $data[$i]['specialty_2'])
  86.             ->setCellValue('K'.$j, $data[$i]['zno_competition_score_2'])
  87.             ->setCellValue('L'.$j, $data[$i]['priority_2'])
  88.  
  89.             ->setCellValue('M'.$j, $data[$i]['specialty_3'])
  90.             ->setCellValue('N'.$j, $data[$i]['zno_competition_score_3'])
  91.             ->setCellValue('O'.$j, $data[$i]['priority_3']);
  92.     }
  93.  
  94.     $objPHPExcel->getActiveSheet()->setTitle('Simple_clean');
  95.  
  96.     $objPHPExcel->setActiveSheetIndex(0);
  97.     $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
  98.     $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
  99.     $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
  100.     $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
  101.     $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
  102.     $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
  103.     $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
  104.     $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
  105.     $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
  106.     $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
  107.     $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
  108.     $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
  109.     $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
  110.  
  111.     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  112.     header('Content-Disposition: attachment;filename="Report.xlsx"');
  113.     header('Cache-Control: max-age=0');
  114.  
  115.     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  116.     $objWriter->save('php://output');
  117.     echo "ok";
  118. ?>
Advertisement
Add Comment
Please, Sign In to add comment