exportexcel.php

一个页面界面的邮件程序

<?php
require_once("../inc/constants.inc.php");
require_once("../inc/properties.inc.php");
require_once("../inc/tools.inc.php");
require_once("../inc/db.inc.php");
require_once("../inc/i18n.inc.php");
include_once "Spreadsheet/Excel/Writer.php";

if (!isset($_SESSION['user'])) {
	die();
}

define("NAME", 0);
define("COUNT", 1);

function transformDateForExport($date) {
	if ($date == "0000-00-00")
		return "";

	$d1 = explode("-", $date);
	if ($d1[0] != '0000')		
		return $d1[2]."/".$d1[1]."/".$d1[0];
	else
		return $d1[2]."/".$d1[1];
}

$nbMaxContacts = $nbMaxAddress = 0;
// The maximum of contacts
$sql = "SELECT count(*) " .
		"FROM `person` P, `contact` C, `user_person` UP " .
		"WHERE C.idperson = P.id AND P.id = UP.idperson AND UP.iduser = '".$_SESSION['user'] ."' " .
		"GROUP BY P.id";
$result = mysql_query($sql) or die("Error in SQL : " . $sql);
if ($result && mysql_num_rows($result) > 0) {	
	while ($line = mysql_fetch_array($result, MYSQL_NUM)) {
		if ($line[0] > $nbMaxContacts)
			 $nbMaxContacts = $line[0];
	}
}
mysql_free_result($result);
// The maximum of addresses
$sql = "SELECT count(*) " .
		"FROM `person` P, `address` A, `user_person` UP " .
		"WHERE A.idperson = P.id AND P.id = UP.idperson AND UP.iduser = '".$_SESSION['user'] ."' " .
		"GROUP BY P.id";
$result = mysql_query($sql) or die("Error in SQL : " . $sql);
if ($result && mysql_num_rows($result) > 0) {	
	while ($line = mysql_fetch_array($result, MYSQL_NUM)) {
		if ($line[0] > $nbMaxAddress)
			 $nbMaxAddress = $line[0];
	}
}
mysql_free_result($result);



$xls = & new Spreadsheet_Excel_Writer();
$xls->send("GContact-export.xls");

$sheet = & $xls->addWorksheet('Users');


$sql = "SELECT P.id, G.name, P.title, P.firstname, P.lastname, P.birthdate, P.note FROM `group` G, `person` P, `user_person` UP " .
		" WHERE P.id = UP.idperson AND G.id = P.idgroup AND UP.iduser = '".$_SESSION['user'] ."'" .
		" ORDER BY G.name, P.firstname, P.lastname";
$result = mysql_query($sql) or die("Error in SQL : " . $sql);


$formatBold = &$xls->addFormat(array('Bold' => 700) );
$formatDate = &$xls->addFormat(array('NumFormat' => 'D/M/YYYY') );

$beginAdd = $beginCt = $x = 0;
$y = 1;
if ($result && mysql_num_rows($result) > 0) {	

	while ($line = mysql_fetch_array($result, MYSQL_NUM)) {
		$x = 0;	
		$i = 0;
		while($i < sizeof($line)) {	

			// Fill headers on the first line
			if ($y == 1) {
				$name = mysql_field_name($result, $i);
				$sheet->write(0, $x, $name, $formatBold);
			}

			$format = null;
			$type  = mysql_field_type($result, $i);

			if ($type == "date") {
				$format = $formatDate;
				$value = transformDateForExport($line[$i]);
			} else {
				$value = $line[$i];
			}			
	
			if ($format != null)
				$sheet->write($y, $x, $value, $format);
			else
				$sheet->write($y, $x, $value);
			$x++;
			$i++;
		}
		
		$beginCt = $x;
		if ($y == 1) {
			// Adding contacts headers
			for($i = 0; $i < $nbMaxContacts; $i++) {
				$sheet->write(0, $x++, "contact", $formatBold);
			}
			// Adding addresses headers
			for($i = 0; $i < $nbMaxAddress; $i++) {
				$sheet->write(0, $x++, "address", $formatBold);
			}
		}
		$x = $beginCt;
			
		/*
		 * Contacts
		 */
		$sqlCt = "SELECT * FROM `contact` C WHERE C.idperson = '". $line[0] ."' ORDER BY type";
		$resultCt = mysql_query($sqlCt);
		if ($resultCt && mysql_num_rows($resultCt) > 0) {
			while ($lineCt = mysql_fetch_assoc($resultCt)) {
				$contact = $lineCt['type'] ." : ". $lineCt['value'];
				if ($lineCt['note'] != "")
					$contact .= "\n" . $lineCt['note'];
				$sheet->write($y, $x++, $contact);
			}
		}
		mysql_free_result($resultCt);		

		
		// Adding contacts offset
		$x = $beginCt + $nbMaxContacts;
		
		// Addresses
		$sqlAddr = "SELECT * FROM address WHERE idperson = '". $line[0] ."' ORDER BY title";
		$resultAddr = mysql_query($sqlAddr);
		if ($resultAddr && mysql_num_rows($resultAddr) > 0) {
			while ($lineAd = mysql_fetch_assoc($resultAddr)) {
				$address =  $lineAd['title'] ." : ". $lineAd['street'] ." ". $lineAd['street_next'] .
							"\n". $lineAd['zipcode'] ." ". $lineAd['town'] ." ". $lineAd['country'];
				if ($lineAd['note'] != "")
					$address .= "\n" . $lineAd['note'];				
				$sheet->write($y, $x++, $address);
			}
		}
		mysql_free_result($resultAddr);		

		// Next person
		$y++;
	}
}
mysql_free_result($result);

$xls->close();
?>