 |
สอบถามเกี่ยวกับ Export mssql (sqlsrv ) to Excel ,CSV จะต้องใช้ยังไง ค้นหาในเว็บยังไม่เห็นคนทำครับ |
| |
 |
|
|
 |
 |
|
สามารถทำได้หรือไม่ครับ ลองไปหลายรอบไม่ได้เลย ใช้ PHPExcel.php
Code (PHP)
<?php
session_start();
date_default_timezone_set("Asia/Bangkok");
require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
$f = $_SESSION["fdate"];
$t = $_SESSION["tdate"];
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment;Filename=document_name.xls");
require("DB.php");
//$connInfo = array( "Database"=>$DB_NAME, "UID"=>$DB_USER, "PWD"=>$DB_PASS,"CharacterSet" => "UTF-8");
//$dbhandle = sqlsrv_connect($DB_HOST, $connInfo);
if($dbhandle === false) {
die(print_r(sqlsrv_errors(), true));
}
// $f = $_POST["fdate"];
// $t = $_POST["tdate"];
$fdate = date("Y-m-d H:i:s",strtotime($f));
$tdate = date("Y-m-d 23:59:59",strtotime($t));
$strSQL = "SELECT EID,CUSTOMER_NAME,CONTACT_ID,TEXT_NOTE,INST_DATE ";
$strSQL .= "FROM TBL_CUSTOMERS "
. "WHERE INST_DATE BETWEEN '".$fdate."' AND '".$tdate."' "
. "ORDER BY INST_DATE ASC ";
echo $strSQL;
$objQuery =sqlsrv_query($dbhandle,$strSQL);
if($objQuery)
{
//*** Get Document Path ***//
$strPath = realpath(basename(getenv($_SERVER["SCRIPT_NAME"]))); // C:/AppServ/www/myphp
//*** Excel Document Root ***//
$strFileName = "MyXls/MyExcel.xls";
//*** Connect to Excel.Application ***//
// $xlApp = new COM("Excel.Application");
$xlApp = new COM("Excel.Application") or die("Cannot create Excel object");
$xlBook = $xlApp->Workbooks->Add();
//*** Create Sheet 1 ***//
$xlBook->Worksheets(1)->Name = "My Customer";
$xlBook->Worksheets(1)->Select;
//*** Header ***//
$xlApp->ActiveSheet->Cells(1,1)->Value = "EID";
$xlApp->ActiveSheet->Cells(1,2)->Value = "CONTACT_ID";
$xlApp->ActiveSheet->Cells(1,3)->Value = "CUSTOMER_NAME";
$xlApp->ActiveSheet->Cells(1,4)->Value = "TEXT_NOTE";
$xlApp->ActiveSheet->Cells(1,5)->Value = "INST_DATE";
// $xlApp->ActiveSheet->Cells(1,6)->Value = "Used";
//***********//
$intRows = 2;
while($objResult = sqlsrv_fetch_array($objQuery))
{
//*** Detail ***//
$xlApp->ActiveSheet->Cells($intRows,1)->Value = $objResult["EID"];
$xlApp->ActiveSheet->Cells($intRows,2)->Value = $objResult["CONTACT_ID"];
$xlApp->ActiveSheet->Cells($intRows,3)->Value = $objResult["CUSTOMER_NAME"];
$xlApp->ActiveSheet->Cells($intRows,4)->Value = $objResult["TEXT_NOTE"];
$xlApp->ActiveSheet->Cells($intRows,5)->Value = $objResult["INST_DATE"];
// $xlApp->ActiveSheet->Cells($intRows,6)->Value = $objResult["Used"];
$intRows++;
}
@unlink($strFileName); //*** Delete old files ***//
$xlBook->SaveAs($strPath."/".$strFileName); //*** Save to Path ***//
//$xlBook->SaveAs(realpath($strFileName)); //*** Save to Path ***//
//*** Close & Quit ***//
$xlApp->Application->Quit();
$xlApp = null;
$xlBook = null;
$xlSheet1 = null;
}
sqlsrv_free_stmt($result);
sqlsrv_close($dbhandle);
?>
Excel Created <a href="<?php echo $strFileName?>">Click here</a> to Download.
Tag : PHP, Ms SQL Server 2008
|
|
|
 |
 |
 |
 |
| Date :
2015-08-20 15:51:36 |
By :
ftp |
View :
1978 |
Reply :
3 |
|
 |
 |
 |
 |
|
|
|
|
 |
 |
|
 |
 |
| |
|
|
|
| |
|
ข้างล่างเป็นตัวอย่าง แปลงจาก text file ครับ แล้ว export เป็นไฟล์ download
เพื่อจะเป็นประโยชน์บ้าง
Code (PHP)
<?php
require_once 'ClassExcel/PHPExcel.php';
include 'ClassExcel/PHPExcel/IOFactory.php';
include 'ClassExcel/PHPExcel/Writer/Excel2007.php';
$excel=new PHPExcel();
$excel->getProperties()->setCreator("Manit Pewklieng");
$excel->getProperties()->setLastModifiedBy("Manit Pewklieng");
$excel->getProperties()->setTitle("TextFile2XLSX");
$excel->getProperties()->setSubject("TextFile");
$excel->getProperties()->setDescription("Test convert TEXT File to Office 2007 XLSX, generated using PHPExcel classes.");
////////////////////////////////////////////////////////////////////////////////
$excel->setActiveSheetIndex(0);
////////////////////////////////////////////////////////////////////////////////
$ln=1;
$excel->getActiveSheet()->SetCellValue('A'.$ln, 'Sample');
$excel->getActiveSheet()->SetCellValue('B'.$ln, 'ID');
$excel->getActiveSheet()->SetCellValue('C'.$ln, 'Conc');
$excel->getActiveSheet()->SetCellValue('D'.$ln, '%RSC');
$excel->getActiveSheet()->SetCellValue('E'.$ln, 'ABS1');
$excel->getActiveSheet()->SetCellValue('F'.$ln, 'ABS2');
$excel->getActiveSheet()->SetCellValue('G'.$ln, 'ABS3');
$excel->getActiveSheet()->SetCellValue('H'.$ln, 'ABS4');
////////////////////////////////////////////////////////////////////////////////
$ln=2;
$eng=array(
'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'
);
$fl=file('text.txt');
foreach( $fl as $k=>$txt){
$ar_txt = explode(' ', $txt);
$j=0;
foreach($ar_txt as $k1=>$vl){
$excel->getActiveSheet()->SetCellValue($eng[$j].$ln, $vl); $j++;
}
$ln++;
}
/////////////////////////////////////////////////////////////////////////////////
$excel->getActiveSheet()->setTitle('TEST');
$fileName = 'test_'.date('YmdHis').'.xlsx';
$objWriter = new PHPExcel_Writer_Excel2007($excel);
$objWriter->save($fileName);
header('Content-Description: File Transfer');
header('Content-Disposition: attachment; filename=' . $fileName );
header('Content-Length: ' . filesize($fileName));
header('Content-Transfer-Encoding: binary');
ob_clean();
flush();
readfile($fileName);
unlink($fileName);
?>
|
ประวัติการแก้ไข
2015-08-21 06:49:54
| |
|
|
| |
| Date :
2015-08-21 06:48:39 |
By :
NewbiePHP |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ใจความสำคัญมันอยู่ที่การดึงข้อมูลออกมาแสดงครับ
Code (PHP)
<?php
ini_set('display_errors', 1);
error_reporting(~0);
$serverName = "localhost";
$userName = "sa";
$userPassword = "";
$dbName = "mydatabase";
$connectionInfo = array("Database"=>$dbName, "UID"=>$userName, "PWD"=>$userPassword, "MultipleActiveResultSets"=>true);
$conn = sqlsrv_connect( $serverName, $connectionInfo);
$stmt = "SELECT * FROM customer";
$query = sqlsrv_query($conn, $stmt);
?>
<table width="600" border="1">
<tr>
<th width="91"> <div align="center">CustomerID </div></th>
<th width="98"> <div align="center">Name </div></th>
<th width="198"> <div align="center">Email </div></th>
<th width="97"> <div align="center">CountryCode </div></th>
<th width="59"> <div align="center">Budget </div></th>
<th width="71"> <div align="center">Used </div></th>
</tr>
<?php
while($result = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC))
{
?>
<tr>
<td><div align="center"><?php echo $result["CustomerID"];?></div></td>
<td><?php echo $result["Name"];?></td>
<td><?php echo $result["Email"];?></td>
<td><div align="center"><?php echo $result["CountryCode"];?></div></td>
<td align="right"><?php echo $result["Budget"];?></td>
<td align="right"><?php echo $result["Used"];?></td>
</tr>
<?php
}
?>
</table>
<?php
sqlsrv_close($conn);
?>
|
| |
|
|
| |
| Date :
2015-08-21 09:43:25 |
By :
mr.win |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ดึงข้อมูลมาแสดงได้ปกติเลยครับ แต่จะกด export กลับทำไม่ได้ งง
|
| |
|
|
| |
| Date :
2015-08-21 09:55:07 |
By :
ftp |
|
|
| |
|
|
|
|
|
| |
|
|
|
|
|
