สอบถามการ Export Data to excel โดย phpspreadsheet หน่อยครับ
เรียนพี่ๆ ทุกท่านครับ ผมอยากสอบถามและวิธีการ Export Data to excel โดย phpspreadsheet หน่อยครับว่า code ชุด Export ผมต้องใช้คำสั่งเพิ่มตรงไหนบ้างครับ
Code page : index.php (ผมรับค่าส่วนนี้เพื่อนำไปเช็คแล้ว query ข้อมูลตามที่เลือก
Code (PHP)
<form action="opentesting.php" id="export_filesim" method="post" enctype="multipart/form-data">
<div class="card-tools">
<select class="form-control select2bs4" style="border-color: #66adea" id="export_occupier" name="export_occupier" required>
<option value="0">-- กรุณาเลือก --</option>
<?php
$result = mysqli_query($connect,"SELECT DISTINCT occupier FROM table_sim ");
while($row = mysqli_fetch_array($result)){
echo '<option value="'.$row['occupier'].'">'.$row['occupier'].'</option>';
}
?>
</select>
</div>
<div class="card-tools">
<button type="button" class="btn btn-danger" name="export_file_btn" id="export_file_btn">
<i class="fa fa-file-excel"></i> Exports File
</button>
</div>
</form>
)
หน้ารับค่า : opentesting.php
Code (PHP)
date_default_timezone_set("Asia/Bangkok");
session_start();
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
if(isset($_POST['export_occupier'])){
$Export_Acc = $_POST['export_occupier'];
$fileName = 'CHECK_SIM-FILE_'.time();
$query_sim = "SELECT * FROM table_sim WHERE occupier='".$Export_Acc."' ORDER BY sim_id asc";
$query_run = mysqli_query($connect,$query_sim);
if(mysqli_num_rows($query_run)>0){
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'phone');
$sheet->setCellValue('B1', 'service');
$sheet->setCellValue('C1', 'simmoney');
$sheet->setCellValue('D1', 'expiration');
$sheet->setCellValue('E1', 'occupier');
$sheet->setCellValue('F1', 'status');
$sheet->setCellValue('G1', 'note');
$sheet->setCellValue('H1', 'linegroup');
$sheet->setCellValue('I1', 'log_user');
$sheet->setCellValue('J1', 'checksim');
$rowcount = 2;
foreach ($query_run as $data){
$sheet->setCellValue('A'.$rowcount, $data['phone']);
$sheet->setCellValue('B'.$rowcount, $data['service']);
$sheet->setCellValue('C'.$rowcount, $data['simmoney']);
$sheet->setCellValue('D'.$rowcount, $data['expiration']);
$sheet->setCellValue('E'.$rowcount, $data['occupier']);
$sheet->setCellValue('F'.$rowcount, $data['status']);
$sheet->setCellValue('G'.$rowcount, $data['note']);
$sheet->setCellValue('H'.$rowcount, $data['linegroup']);
$sheet->setCellValue('I'.$rowcount, $data['log_user']);
$sheet->setCellValue('J'.$rowcount, $data['checksim']);
$rowcount++;
}
if($NameOccupier == 'Xlsx'){
$writer = new Xlsx($spreadsheet);
$final_filename = $fileName.'.xlsx';
}elseif($Export_Acc == 'Xls'){
$writer = new Xls($spreadsheet);
$final_filename = $fileName.'.xls';
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.urlencode($final_filename).'"');
$writer->save('php://output');
//$writer->save($final_filename);*/
}else{
$_SESSION['status'] = "No Record found to Export";
header("Location: index.php");
}
}
Tag : PHP, MySQL, HTML, JavaScript, Excel (Excel.Application), Google Chrome
Date :
2021-08-20 21:30:51
By :
NIRUTB
View :
1672
Reply :
8
ตอบความคิดเห็นที่ : 3 เขียนโดย : TheGreatGod_of_Death เมื่อวันที่ 2021-08-21 09:40:27
รายละเอียดของการตอบ ::
ใช่ครับ code ชุดนี้ผมลบออกแล้ว
Code (PHP)
date_default_timezone_set("Asia/Bangkok");
session_start();
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
//$Export_Acc = $_POST['export_occupier'];
if(isset($_POST['export_file_btn'])){
$Export_Acc = $_POST['export_occupier'];
$fileName = 'CHECK_SIM-FILE_'.time();
//$query_sim = "SELECT * FROM table_sim ";
$query_sim = "SELECT * FROM table_sim WHERE occupier='".$Export_Acc."' ORDER BY sim_id asc";
$query_run = mysqli_query($connect,$query_sim);
//$user_row = mysqli_fetch_assoc($query_run);
if(mysqli_num_rows($query_run)>0){
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'phone');
$sheet->setCellValue('B1', 'service');
$sheet->setCellValue('C1', 'simmoney');
$sheet->setCellValue('D1', 'expiration');
$sheet->setCellValue('E1', 'occupier');
$sheet->setCellValue('F1', 'status');
$sheet->setCellValue('G1', 'note');
$sheet->setCellValue('H1', 'linegroup');
$sheet->setCellValue('I1', 'log_user');
$sheet->setCellValue('J1', 'checksim');
$rowcount = 2;
foreach ($query_run as $data){
$sheet->setCellValue('A'.$rowcount, $data['phone']);
$sheet->setCellValue('B'.$rowcount, $data['service']);
$sheet->setCellValue('C'.$rowcount, $data['simmoney']);
$sheet->setCellValue('D'.$rowcount, $data['expiration']);
$sheet->setCellValue('E'.$rowcount, $data['occupier']);
$sheet->setCellValue('F'.$rowcount, $data['status']);
$sheet->setCellValue('G'.$rowcount, $data['note']);
$sheet->setCellValue('H'.$rowcount, $data['linegroup']);
$sheet->setCellValue('I'.$rowcount, $data['log_user']);
$sheet->setCellValue('J'.$rowcount, $data['checksim']);
$rowcount++;
//$queryOccupier = "SELECT * FROM table_sim WHERE occupier='".$Export_Acc."' ORDER BY sim_id asc";
//$Occupier_run = mysqli_query($connect,$queryOccupier);
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.urlencode($final_filename).'"');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
//$writer->save($final_filename);*
}else{
$_SESSION['status'] = "No Record found to Export";
header("Location: index.php?page=importfile");
//$_SESSION['status_code'] = "Success";
//echo
}
}
Date :
2021-08-21 10:36:07
By :
NIRUTB
ข้อมูลก็มาแล้ว
ถ้าหมายถึงข้อมูลผลลัพธ์ออกไม่ตรงตามฐานข้อมูล เช่น row มาไม่ครบ,
ข้อมูลบางคอลัมน์ (simmoney, note, expiration,...) ไม่ตรง
ลองเลือก namespace แค่อย่างเดียว (แต่จริงๆ ไม่น่าเกี่ยว)
Code (PHP)
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// use PhpOffice\PhpSpreadsheet\Writer\Xls;
// ...
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
ยังไงลองดูก่อน
ถ้ายังไม่ได้...ลองอัปเดตเฟรมเวิร์กเป็นเวอร์ชันล่าสุด
Date :
2021-08-21 11:44:00
By :
TheGreatGod_of_Death
Load balance : Server 05