<?php require_once('Connections/connWater.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
mysql_select_db($database_connWater, $connWater);
$query_Recordset1 = "SELECT * FROM hourly_t";
$Recordset1 = mysql_query($query_Recordset1, $connWater) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<table border="1" cellpadding="0" cellspacing="0">
<tr>
<td width="203" bordercolor="#000000"><div align="center">TimeTag_DT</div></td>
<td width="187" bordercolor="#000000"><div align="center">Device_ID</div></td>
<td width="181" bordercolor="#000000"><div align="center">Reading_FL</div></td>
<td width="250" bordercolor="#000000"><div align="center">dReading_FL</div></td>
</tr>
<?php do { ?>
<tr>
<td bordercolor="#000000"><div align="center"><?php echo $row_Recordset1['TimeTag_DT']; ?></div></td>
<td bordercolor="#000000"><div align="center"><?php echo $row_Recordset1['Device_ID']; ?></div></td>
<td bordercolor="#000000"><div align="center"><?php echo $row_Recordset1['Reading_FL']; ?></div></td>
<td bordercolor="#000000"><div align="center"><?php echo $row_Recordset1['dReading_FL']; ?></div></td>
</tr>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
<div align="center"><br>
<input name="btnExport" type="button" value="Export" onClick="JavaScript:window.location='test_csv_export.php';">
</div>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
2. file test_csv_export.php
<?php require_once('Connections/connWater.php'); ?>
<?php
$filName = "customer.csv";
$objWrite = fopen("customer.csv", "w");
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
mysql_select_db($database_connWater, $connWater);
$query_Recordset1 = "SELECT * FROM hourly_t";
$Recordset1 = mysql_query($query_Recordset1, $connWater) or die(mysql_error());
//$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<?php
while($objResult = mysql_fetch_array($Recordset1))
{
fwrite($objWrite, "\"$objResult[TimeTag_DT]\",\"$objResult[Device_ID]\",\"$objResult[Reading_FL]\",\"$objResult[dReading_FL]\"\n");
}
fclose($objWrite);
echo "<br>Generate CSV Done.<br><a href=$filName>Download</a>";
?>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
3. send to XLS excel file
<?php require_once('Connections/connWater.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
mysql_select_db($database_connWater, $connWater);
$query_Recordset1 = "SELECT * FROM hourly_t";
$Recordset1 = mysql_query($query_Recordset1, $connWater) or die(mysql_error());
//$objResult = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
/*
$objConnect = mysql_connect("localhost","root","root") or die("Error Connect to Database");
$objDB = mysql_select_db("mydatabase");
$strSQL = "SELECT * FROM customer";
$objQuery = mysql_query($strSQL); */
if($Recordset1)
{
//*** Get Document Path ***//
$strPath = realpath(basename(getenv($_SERVER["SCRIPT_NAME"]))); // C:/AppServ/www/myphp
//*** Excel Document Root ***//
$strFileName = "MyExcel.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;
//*** Header ***//
$xlApp->ActiveSheet->Cells(1,1)->Value = "TimeTag_DT";
$xlApp->ActiveSheet->Cells(1,2)->Value = "Device_ID";
$xlApp->ActiveSheet->Cells(1,3)->Value = "Reading_FL";
$xlApp->ActiveSheet->Cells(1,4)->Value = "dReading_FL";
//***********//
$intRows = 2;
while($objResult = mysql_fetch_array($Recordset1))
{
//*** Detail ***//
$xlApp->ActiveSheet->Cells($intRows,1)->Value = $objResult["TimeTag_DT"];
$xlApp->ActiveSheet->Cells($intRows,2)->Value = $objResult["Device_ID"];
$xlApp->ActiveSheet->Cells($intRows,3)->Value = $objResult["Reading_FL"];
$xlApp->ActiveSheet->Cells($intRows,4)->Value = $objResult["dReading_FL"];
$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;
}
?>
</body>
</html>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
Excel Created <a href="<?=$strFileName?>">Click here</a> to Download.
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
ไม่มีความคิดเห็น:
แสดงความคิดเห็น