|
 |
| |
Export Database to Excel แล้วไฟล์excelไม่เปลี่ยนค่า จะจำค่าที่ queryครั้งแรกเสมอ |
| |
 |
|
|
 |
 |
|
จากลิ๊ง https://www.thaicreate.com/php/php-export-database-to-excel.html ลอง config ค่าตามที่ตั้งไว้แล้วทุกอย่างรันได้แต่ไม่เขียนค่าตามที่ query ใหม่ลงใน excel ทดลอง query ที่หน้าเว็บก็ออกปรกติ
ตัวอย่าง code ครับ
Code (PHP)
<?php
session_start();
if($_SESSION['IDadmin'] == "")
{ echo "Please Login!";
exit();
}
if($_SESSION['status'] != "user")
{
echo "This page for User only!";
exit();
}
?><head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-874"/>
<META http-equiv="Content-Type" content="text/html; charset=TIS620" />
<title>หน้าสำหรับAdmin</title>
-->
</style><style type="text/css">
<!--
body,td,th {
font-family: AngsanaUPC;
font-size: 14px;
}
a {
font-family: AngsanaUPC;
}
h1,h2,h3,h4,h5,h6 {
font-family: AngsanaUPC;
}
-->
</style></head>
<?php
$objConnect = mysql_connect("localhost","root","123") or die("Error Connect to Database");
//mysql_query("SET character_set_results=utf-8");
mysql_query( "SET NAMES TIS620", $objConnect );
$objDB = mysql_select_db("nurse");
//mysql_query("SET NAMES TIS620");
$strSQL = "SELECT * FROM name WHERE IDmentalhealth= '".$_SESSION['IDadmin']."' AND IDtest=1 ";
//$strSQL = "SELECT * FROM name WHERE IDtest= '1' ";
//mysql_query("SET NAMES TIS620");// Set Char
$objQuery =mysql_query($strSQL)or die ("Error Query [".$strSQL."]");
//mysql_query("SET character_set_results=utf8");
// mysql_query("SET character_set_client=utf8");
//mysql_query("SET character_set_connection=utf8");
if($objQuery)
{
//*** Get Document Path ***//
$strPath = realpath(basename(getenv($_SERVER["nuse"]))); // C:/AppServ/www/myphp
//*** Excel Document Root ***//
$strFileName = "MyXls/Testexcel.xls";
//*** Connect to Excel.Application ***//
$xlApp = new COM("Excel.Application");
///
$xlBook = $xlApp->Workbooks->Add();
//*** Create Sheet 1 ***//
$xlBook->Worksheets(1)->Name = "My Customer";
$xlBook->Worksheets(1)->Select;
//*** Width & Height (A1:A1) ***//
echo iconv("ISO-8859-1", "UTF-8", "This is a test.");
$xlApp->ActiveSheet->Range("A1:A1")->ColumnWidth = 10.0;
$xlApp->ActiveSheet->Range("B1:B1")->ColumnWidth = 13.0;
$xlApp->ActiveSheet->Range("C1:C1")->ColumnWidth = 23.0;
$xlApp->ActiveSheet->Range("D1:D1")->ColumnWidth = 12.0;
$xlApp->ActiveSheet->Range("E1:E1")->ColumnWidth = 13.0;
$xlApp->ActiveSheet->Range("F1:F1")->ColumnWidth = 12.0;
//*** Report Title ***//
$xlApp->ActiveSheet->Range("A1:F1")->BORDERS->Weight = 1;
$xlApp->ActiveSheet->Range("A1:F1")->MergeCells = True;
$xlApp->ActiveSheet->Range("A1:F1")->Font->Bold = True;
$xlApp->ActiveSheet->Range("A1:F1")->Font->Size = 20;
$xlApp->ActiveSheet->Range("A1:F1")->HorizontalAlignment = -4108;
$xlApp->ActiveSheet->Cells(1,1)->Value = "Customer Report";
//*** Header ***//
$xlApp->ActiveSheet->Cells(3,1)->Value = "IDname";
$xlApp->ActiveSheet->Cells(3,1)->Font->Bold = True;
$xlApp->ActiveSheet->Cells(3,1)->VerticalAlignment = -4108;
$xlApp->ActiveSheet->Cells(3,1)->HorizontalAlignment = -4108;
$xlApp->ActiveSheet->Cells(3,1)->BORDERS->Weight = 1;
$xlApp->ActiveSheet->Cells(3,2)->Value = "ชื่อ";
$xlApp->ActiveSheet->Cells(3,2)->Font->Bold = True;
$xlApp->ActiveSheet->Cells(3,2)->VerticalAlignment = -4108;
$xlApp->ActiveSheet->Cells(3,2)->HorizontalAlignment = -4108;
$xlApp->ActiveSheet->Cells(3,2)->BORDERS->Weight = 1;
$xlApp->ActiveSheet->Cells(3,3)->Value = "นามสกุล";
$xlApp->ActiveSheet->Cells(3,3)->Font->Bold = True;
$xlApp->ActiveSheet->Cells(3,3)->VerticalAlignment = -4108;
$xlApp->ActiveSheet->Cells(3,3)->HorizontalAlignment = -4108;
$xlApp->ActiveSheet->Cells(3,3)->BORDERS->Weight = 1;
$xlApp->ActiveSheet->Cells(3,4)->Value = "อายุราชการ";
$xlApp->ActiveSheet->Cells(3,4)->Font->Bold = True;
$xlApp->ActiveSheet->Cells(3,4)->VerticalAlignment = -4108;
$xlApp->ActiveSheet->Cells(3,4)->HorizontalAlignment = -4108;
$xlApp->ActiveSheet->Cells(3,4)->BORDERS->Weight = 1;
$xlApp->ActiveSheet->Cells(3,5)->Value = "ตำแหน่ง";
$xlApp->ActiveSheet->Cells(3,5)->Font->Bold = True;
$xlApp->ActiveSheet->Cells(3,5)->VerticalAlignment = -4108;
$xlApp->ActiveSheet->Cells(3,5)->HorizontalAlignment = -4108;
$xlApp->ActiveSheet->Cells(3,5)->BORDERS->Weight = 1;
$xlApp->ActiveSheet->Cells(3,6)->Value = "หน่วยงาน";
$xlApp->ActiveSheet->Cells(3,6)->Font->Bold = True;
$xlApp->ActiveSheet->Cells(3,6)->VerticalAlignment = -4108;
$xlApp->ActiveSheet->Cells(3,6)->HorizontalAlignment = -4108;
$xlApp->ActiveSheet->Cells(3,6)->BORDERS->Weight = 1;
//***********//
$intRows = 4;
while($objResult = mysql_fetch_array($objQuery))
{
//*** Detail ***//
$xlApp->ActiveSheet->Cells($intRows,1)->Value = $objResult["IDname"];
$xlApp->ActiveSheet->Cells($intRows,1)->BORDERS->Weight = 1;
$xlApp->ActiveSheet->Cells($intRows,1)->HorizontalAlignment = -4108;
$xlApp->ActiveSheet->Cells($intRows,2)->Value = $objResult["name"];
$xlApp->ActiveSheet->Cells($intRows,2)->BORDERS->Weight = 1;
$xlApp->ActiveSheet->Cells($intRows,3)->Value = $objResult["surname"];
$xlApp->ActiveSheet->Cells($intRows,3)->BORDERS->Weight = 1;
$xlApp->ActiveSheet->Cells($intRows,4)->Value = $objResult["age3"];
$xlApp->ActiveSheet->Cells($intRows,4)->HorizontalAlignment = -4108;
$xlApp->ActiveSheet->Cells($intRows,4)->BORDERS->Weight = 1;
$xlApp->ActiveSheet->Cells($intRows,5)->Value = $objResult["position"];
$xlApp->ActiveSheet->Cells($intRows,5)->BORDERS->Weight = 1;
//$xlApp->ActiveSheet->Cells($intRows,5)->NumberFormat = "$#,##0.00";
$xlApp->ActiveSheet->Cells($intRows,6)->Value = $objResult["department"];
$xlApp->ActiveSheet->Cells($intRows,6)->BORDERS->Weight = 1;
$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;
}
mysql_close($objConnect);
?>
Excel Created <a href="<?=$strFileName?>">Click here</a> to Download.
</body>
</html>
Tag : PHP, MySQL, Excel (Excel.Application)
|
|
|
 |
 |
 |
 |
| Date :
2011-05-18 20:52:52 |
By :
comci |
View :
986 |
Reply :
1 |
|
 |
 |
 |
 |
|
|
|
|
 |
 |
|
 |
 |
| |
|
|
|
| |
|
Code (PHP)
unlink($strFileName); //*** Delete old files ***//
เอา @ ออกครับ ดูว่ามันลบไฟล์เก่าสำเร็จหรือเปล่าครับ
|
| |
|
|
| |
| Date :
2011-05-19 09:56:33 |
By :
webmaster |
|
|
| |
|
|
|
|
|
| |
|
|
|
|
|
| |
|
 Load balance : Server 00
|
