|
|
|
phpexcel ช่วยดูให้หน่อยครับติดอยู่ปัญหาเดียว ตามรูปนะครับ |
|
|
|
|
|
|
|
รูป
โค้ดครับ
Code (PHP)
<?php
header('Content-Type: text/html ; charset=UTF8');
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Asia/Bangkok');
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
/** Include PHPExcel */
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
$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.");
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
$objPHPExcel->getProperties()->setCategory("Test result file");
//set page margins for a worksheet, use this code
$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(1);
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.75);
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.75);
$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(1);
// Set Orientation, size and scaling
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToPage(true);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);
// rename the sheet
$objPHPExcel->getActiveSheet()->setTitle('BS Report');
// let's bold and size the header font and write the header
// as you can see, we can specify a range of cells, like here: cells from A1 to A4
$objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getFont()->setBold(true)->setSize(12);
//Set Head BORDER
$styleArray=array(
'borders'=>array(
'allborders'=>array(
'style'=>PHPExcel_Style_Border::BORDER_MEDIUM
)
)
);
$objPHPExcel->getActiveSheet()->getStyle('A3:H3')->applyFromArray($styleArray);
//Set Format Cell 1
$objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('H1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
$objPHPExcel->getActiveSheet()->mergeCells('A1:G1');
//Set column
//$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(7);
//$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A3', 'SN')
->setCellValue('B3', 'ชื่อโปรแกรม')
->setCellValue('C3', 'ชื่อผู้ติดต่อ')
->setCellValue('D3', 'ชื่อกิจการ')
->setCellValue('E3', 'ที่อยู่1')
->setCellValue('F3', 'ที่อยู่2')
->setCellValue('G3', 'เบอร์โทรศัพท์')
->setCellValue('H3', 'Email');
// EX $objPHPExcel->getActiveSheet()->setCellValue('A1', ''.iconv('TIS-620','UTF-8',"ลำดับ").'');
$select = $_POST['select'];
include("connectdb.php");
if( $select=="ทั้งหมด" ){
$sql= "select * from bill
inner join member on bill.bs_id = member.bs_id
inner join serial_number on bill.sn_id = serial_number.sn_id
inner join program on bill.program_id = program.program_id
WHERE bill.bill_id like '$select' ";
}else{
$sql= "select * from bill
inner join member on bill.bs_id = member.bs_id
inner join serial_number on bill.sn_id = serial_number.sn_id
inner join program on bill.program_id = program.program_id
WHERE program.program_name like '$select' ";
}
$result=mysql_query($sql);
$i=2;
while($rs=mysql_fetch_array($result)){
$sn = $rs['sn'];
$program_name = $rs['program_name'];
$bs_name = $rs['bs_name'];
$bs_company = $rs['bs_company'];
$bs_address = $rs['bs_address'];
$bs_address2 = $rs['bs_address2'];
$bs_tel = $rs['bs_tel'];
$bs_email = $rs['bs_email'];
//Set Format Cell
$objPHPExcel->getActiveSheet()->getStyle('A3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('C3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('D3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('E3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('F3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('G3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('H3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//Set BORDER
/*$styleArray=array(
'borders'=>array(
'allborders'=>array(
'style'=>PHPExcel_Style_Border::BORDER_THIN
)
)
);
$objPHPExcel->getActiveSheet()->getStyle('A2:H2'.$i)->applyFromArray($styleArray);*/
$objPHPExcel->getActiveSheet()->getStyle('A4:H4'.$i)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A3'.$i, $sn)
->setCellValue('B3'.$i, $program_name)
->setCellValue('C3'.$i, $bs_name)
->setCellValue('D3'.$i, $bs_company)
->setCellValue('E3'.$i, $bs_address)
->setCellValue('F3'.$i, $bs_address2)
->setCellValue('G3'.$i, $bs_tel)
->setCellValue('H3'.$i, $bs_email);
$i+=1;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace('.php', '.xls', __FILE__));
echo date('H:i:s');
echo 'Export Complete';
echo '<script>window.open("01simple.xls","_top")</script>';
?>
1. ข้อมูลในดาต้าเบสมี 2 ข้อมูลแต่ทำไมแสดงอยู่กลางตาราง
2. ทำไมตารางแสดงเยอะจังครับ
3. เบอร์โทรศัพท์เลข 0 หาย เช็คดูในดาต้าเบส มี0 ครับ
รบกวนด้วยนะครับ
Tag : PHP, CakePHP, Excel (Excel.Application)
|
|
|
|
|
|
Date :
2013-02-06 12:07:42 |
By :
Lov3 |
View :
5218 |
Reply :
5 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ตั้งแต่ตรงนี้
Code (PHP)
//Set Format Cell
$objPHPExcel->getActiveSheet()->getStyle('A3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('C3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('D3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('E3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('F3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('G3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('H3'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//Set BORDER
/*$styleArray=array(
'borders'=>array(
'allborders'=>array(
'style'=>PHPExcel_Style_Border::BORDER_THIN
)
)
);
$objPHPExcel->getActiveSheet()->getStyle('A2:H2'.$i)->applyFromArray($styleArray);*/
$objPHPExcel->getActiveSheet()->getStyle('A4:H4'.$i)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A3'.$i, $sn)
->setCellValue('B3'.$i, $program_name)
->setCellValue('C3'.$i, $bs_name)
->setCellValue('D3'.$i, $bs_company)
->setCellValue('E3'.$i, $bs_address)
->setCellValue('F3'.$i, $bs_address2)
->setCellValue('G3'.$i, $bs_tel)
->setCellValue('H3'.$i, $bs_email);
$i+=1;
}
ให้ลบ เลข 3 ออกจาก getStyle('A3'.$i) ให้เหลือ getStyle('A'.$i) ดู เนื่องจาก $i มีค่าเริ่มต้นเป็นสอง มันเลย รันเป็น คอลัมภ์ A32, B32 ไปเรื่อย ๆ
ส่วนที่เลข ศูนย์หายไปให้ลองเซตค่า คอลัมภ์ เป็นแบบนี้
Code (PHP)
$objPHPExcel->getActiveSheet()->setCellValueExplicit('A1', '0890333333', PHPExcel_Cell_DataType::TYPE_STRING);
ประมาณนี้
Code (PHP)
$objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$i, $bs_tel, PHPExcel_Cell_DataType::TYPE_STRING);
ได้ไม่ได้ยังไงบอกด้วยหน่ะครับ
|
|
|
|
|
Date :
2013-02-06 13:27:21 |
By :
ไวยวิทย์ |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ง่ะ
Code (PHP)
$objPHPExcel->getActiveSheet()->setCellValueExplicit('A1', '0890333333', PHPExcel_Cell_DataType::TYPE_STRING);
มันแค่เป็นข้อมูลตัวอย่าง เฉยๆ คุณก็ลบออกแล้วเอา ข้อมูลของคุณที่ได้จากฐานข้อมูลใส่แทนไป เลยครับ
ส่วนตรงนี้
Code (PHP)
$objPHPExcel->getActiveSheet()->getStyle('A:H'.$i)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
ก็เปลี่ยนเป็นอย่างนี้
Code (PHP)
$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':H'.$i.'')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
ค่าที่ได้มันก็จะเป็น A2:H2 หรือ A3:H3 ไปเรื่อย ๆ
ลองดูครับ
|
|
|
|
|
Date :
2013-02-06 14:06:35 |
By :
ไวยวิทย์ |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ต้องการตรวจสอบว่าข้อมูลที่อ่านมาจากไฟล์ Excel มันมี Format เป็นอะไรจะต้องเขียนอย่างไรครับ
|
|
|
|
|
Date :
2014-06-07 21:49:06 |
By :
pongeiei |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Load balance : Server 02
|