static function ArrayToExcel($array)
{
require_once "php_writeexcel-0.3.0/class.writeexcel_workbook.inc.php";
require_once "php_writeexcel-0.3.0/class.writeexcel_worksheet.inc.php";
$workbook = new writeexcel_workbook("/tmp/test.xls");
$worksheet = $workbook->addworksheet($worksheetname);
$heading = $workbook->addformat(array('align' => 'center', 'bold' => 1, 'bg_color' => 'black', 'color' => 'white'));
// heading
$i = 0;
$h = array();
foreach (array_keys($array[0]) as $val) {
$worksheet->write(0, $i++, $val, $heading);
array_push($h, $val);
}
// values
for ($i = 0; $i < count($array); $i++) {
for ($j = 0; $j < count($h); $j++) {
$worksheet->write($i + 1, $j, $array[$i][$h[$j]]);
}
}
$workbook->close();
header("Content-type: application/zip");
header("Content-disposition: inline; filename=excel.xls");
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header("Pragma: public");
echo file_get_contents("/tmp/test.xls");
unlink("/tmp/test.xls");
die;
}
public function render_xls_file($name = "state")
{
$tmp_file = tempnam(sys_get_temp_dir(), "state_");
header("Content-Type: application/x-msexcel; name=\"" . $name . ".xls\"");
header("Content-Disposition: inline; filename=\"" . $name . ".xls\"");
$workbook = new writeexcel_workbook($tmp_file);
$worksheet =& $workbook->addworksheet();
for ($i = 0; $i < count($this->datas); $i++) {
for ($j = 0; $j < count($this->datas[$i]); $j++) {
$worksheet->write($i, $j, $this->datas[$i][$j]);
}
}
$workbook->close();
$fh = fopen($tmp_file, "rb");
fpassthru($fh);
unlink($tmp_file);
}
function Workbook($filename)
{
//print_r($filename);
if (is_array($filename)) {
$file = $filename['fname'];
} else {
$file = $filename;
}
parent::writeexcel_workbook($file);
}
// +-------------------------------------------------+
// © 2002-2004 PMB Services / www.sigb.net pmb@sigb.net et contributeurs (voir www.sigb.net)
// +-------------------------------------------------+
// $Id: all.inc.php,v 1.54 2015-10-29 10:56:12 jpermanne Exp $
if (stristr($_SERVER['REQUEST_URI'], ".inc.php")) {
die("no access");
}
//Récupération des variables postées, on en aura besoin pour les liens
$page = $_SERVER[SCRIPT_NAME];
if ($dest == "TABLEAU") {
//Export excel
$fname = str_replace(" ", "", microtime());
$fname = str_replace("0.", "", $base_path . "/temp/" . $fname);
require_once "{$class_path}/writeexcel/class.writeexcel_workbook.inc.php";
require_once "{$class_path}/writeexcel/class.writeexcel_worksheet.inc.php";
$workbook = new writeexcel_workbook($fname);
$worksheet = $workbook->addworksheet();
//formats
$workbook->set_custom_color(12, 00, 204, 255);
$heading_blue = $workbook->addformat(array('fg_color' => 12));
$heading_10 = $workbook->addformat(array('bold' => 1, 'size' => 10));
$heading_12 = $workbook->addformat(array('bold' => 1, 'size' => 12));
} else {
echo "<form action='empr.php' method='post' name='FormEmpr'>";
echo "<input name='lvl' value='all' type='hidden'>";
echo "<input name='prolonge_id' value='0' type='hidden'>";
// Si click bouton de prolongation, et prolongation autorisée
if ($prolonge_id > 0 && $opac_pret_prolongation == 1) {
//Il faut prolonger un livre
$prolongation = TRUE;
//on recupere les informations du pret
public function render_xls_file($name = "state")
{
$tmp_file = tempnam(sys_get_temp_dir(), "state_");
header("Content-Type: application/x-msexcel; name=\"" . $name . ".xls\"");
header("Content-Disposition: inline; filename=\"" . $name . ".xls\"");
$workbook = new writeexcel_workbook($tmp_file);
$worksheet =& $workbook->addworksheet();
foreach ($this->datas_tcd['cols'] as $key => $label) {
$worksheet->write(0, $key + 1, $label);
}
$nb_ligne = 1;
foreach ($this->datas_tcd['values'] as $row => $cols) {
$worksheet->write($nb_ligne, 0, $row);
$nb_col = 1;
foreach ($this->datas_tcd['cols'] as $key) {
$worksheet->write($nb_ligne, $nb_col, $cols[$key] ? $cols[$key] : 0);
$nb_col++;
}
$nb_ligne++;
}
$workbook->close();
$fh = fopen($tmp_file, "rb");
fpassthru($fh);
unlink($tmp_file);
}
function show_results_stats($id_proc = 0)
{
global $msg, $dbh, $form_type, $categ, $numero_page, $limite_page, $sub, $charset;
global $dest, $pmb_set_time_limit, $force_exec, $erreur_explain_rqt, $nombre_lignes_total;
@set_time_limit($pmb_set_time_limit);
//Récupération des variables postées, on en aura besoin pour les liens
$page = $_SERVER[SCRIPT_NAME];
$requete = "SELECT idproc, name, requete, comment, num_vue FROM statopac_request where idproc='" . $id_proc . "' ";
$res = mysql_query($requete, $dbh);
$row = mysql_fetch_row($res);
//Requete et calcul du nombre de pages à afficher selon la taille de la base 'pret'
//********************************************************************************/
// récupérer ici la procédure à lancer
$sql = $row[2];
$sql = str_replace("VUE()", "statopac_vue_{$row['4']}", $sql);
if (preg_match_all("|!!(.*)!!|U", $sql, $query_parameters) && $form_type == "") {
$hp = new parameters($id_proc, "statopac_request");
$hp->gen_form("edit.php?categ=stat_opac&sub=&action=execute&id_proc=" . $id_proc . "&force_exec=" . $force_exec);
} else {
$param_hidden = "";
if ($force_exec) {
$param_hidden .= "<input type='hidden' name='force_exec' value='" . $force_exec . "' />";
//On a forcé la requete
}
if (preg_match_all("|!!(.*)!!|U", $sql, $query_parameters)) {
$hp = new parameters($id_proc, "statopac_request");
$hp->get_final_query();
$sql = $hp->final_query;
$param_hidden .= $hp->get_hidden_values();
//Je mets les paramêtres en champ caché en cas de forçage
$param_hidden .= "<input type='hidden' name='form_type' value='gen_form' />";
//Je mets le marqueur des paramêtres en champ caché en cas de forçage
}
$sql = str_replace("VUE()", "statopac_vue_{$row['4']}", $sql);
if ($dest != "TABLEAU" && $dest != "TABLEAUHTML" && $dest != "TABLEAUCSV") {
print "<form class=\"form-edit\" id=\"formulaire\" name=\"formulaire\" action='./edit.php?categ=stat_opac&sub=&action=execute&id_proc=" . $id_proc . "&force_exec=" . $force_exec . "' method=\"post\">";
print "<input type='button' class='bouton' value='" . htmlentities($msg[654], ENT_QUOTES, $charset) . "' onClick='this.form.action=\"./edit.php?categ=stat_opac\";this.form.submit();' />";
if (!explain_requete($sql) && SESSrights & EDIT_FORCING_AUTH && !$force_exec) {
print $param_hidden;
print "<input type='button' id='procs_button_exec' class='bouton' value='" . htmlentities($msg["procs_force_exec"], ENT_QUOTES, $charset) . "' onClick='this.form.action=\"./edit.php?categ=stat_opac&sub=&action=execute&id_proc=" . $id_proc . "&force_exec=1\";this.form.submit();' />";
} else {
print "<input type='submit' id='procs_button_exec' class='bouton' value='" . htmlentities($msg[708], ENT_QUOTES, $charset) . "'/>";
}
print "<br />";
print "</form>";
// la procédure n'a pas de parm ou les paramètres ont été reçus
if (!explain_requete($sql) && !(SESSrights & EDIT_FORCING_AUTH && $force_exec)) {
die("<br /><br />" . $sql . "<br /><br />" . htmlentities($msg["proc_param_explain_failed"], ENT_QUOTES, $charset) . "<br /><br />" . $erreur_explain_rqt);
}
}
$req_nombre_lignes = "";
if (!$nombre_lignes_total) {
$req_nombre_lignes = mysql_query($sql);
if (!$req_nombre_lignes) {
die($sql . "<br /><br />" . mysql_error());
}
$nombre_lignes_total = mysql_num_rows($req_nombre_lignes);
}
$param_hidden .= "<input type='hidden' name='nombre_lignes_total' value='" . $nombre_lignes_total . "' />";
//Je garde le nombre de ligne total pour le pas refaire la requête à la page suivante
//Si aucune limite_page n'a été passée, valeur par défaut : 10
if (!$limite_page) {
$limite_page = 10;
}
$nbpages = $nombre_lignes_total / $limite_page;
// on arondi le nombre de page pour ne pas avoir de virgules, ici au chiffre supérieur
$nbpages_arrondi = ceil($nbpages);
// on enlève 1 au nombre de pages, car la 1ere page affichée ne fait pas partie des pages suivantes
$nbpages_arrondi = $nbpages_arrondi - 1;
if (!$numero_page) {
$numero_page = 0;
}
$limite_mysql = $limite_page * $numero_page;
//REINITIALISATION DE LA REQUETE SQL
switch ($dest) {
case "TABLEAU":
case "TABLEAUHTML":
case "TABLEAUCSV":
if (!$req_nombre_lignes) {
$res = @mysql_query($sql, $dbh) or die($sql . "<br /><br />" . mysql_error());
} else {
$res = $req_nombre_lignes;
}
break;
default:
echo "<h1>" . htmlentities($msg["opac_admin_menu"], ENT_QUOTES, $charset) . " : " . htmlentities($msg["stat_opac_menu"], ENT_QUOTES, $charset) . "</h1>";
echo "<h1>" . htmlentities($row[1], ENT_QUOTES, $charset) . "</h1><h2>" . htmlentities($row[3], ENT_QUOTES, $charset) . "</h2>";
$sql = $sql . " LIMIT " . $limite_mysql . ", " . $limite_page;
// on execute la requete avec les bonnes limites
$res = @mysql_query($sql, $dbh) or die($sql . "<br /><br />" . mysql_error());
echo "<p>";
break;
}
$nbr_lignes = @mysql_num_rows($res);
$nbr_champs = @mysql_num_fields($res);
if ($nbr_lignes) {
switch ($dest) {
case "TABLEAU":
$fichier_temp_nom = tempnam(sys_get_temp_dir(), $fichier_temp_nom);
$workbook = new writeexcel_workbook($fichier_temp_nom);
$worksheet =& $workbook->addworksheet();
$worksheet->write(0, 0, $row[1]);
$worksheet->write(0, 1, $row[3]);
for ($i = 0; $i < $nbr_champs; $i++) {
// entête de colonnes
$fieldname = mysql_field_name($res, $i);
$worksheet->write(2, $i, ${fieldname});
}
for ($i = 0; $i < $nbr_lignes; $i++) {
$row = mysql_fetch_row($res);
$j = 0;
foreach ($row as $dummykey => $col) {
if (is_numeric($col) && preg_match("/^0/", $col)) {
$col = "'" . $col;
}
if (trim($col) == '') {
$col = " ";
}
$worksheet->write($i + 3, $j, $col);
$j++;
}
}
$workbook->close();
$fh = fopen($fichier_temp_nom, "rb");
fpassthru($fh);
unlink($fichier_temp_nom);
break;
case "TABLEAUHTML":
echo "<h1>{$row['1']}</h1><h2>{$row['3']}</h2>{$sql}<br/>";
echo "<table>";
for ($i = 0; $i < $nbr_champs; $i++) {
$fieldname = mysql_field_name($res, $i);
print "<th align='left'>{$fieldname}</th>";
}
for ($i = 0; $i < $nbr_lignes; $i++) {
$row = mysql_fetch_row($res);
echo "<tr>";
foreach ($row as $dummykey => $col) {
/*if (is_numeric($col)){
$col = "'".$col ;
}*/
if (trim($col) == '') {
$col = " ";
}
print '<td>' . $col . '</td>';
}
echo "</tr>";
}
echo "</table>";
break;
case "TABLEAUCSV":
for ($i = 0; $i < $nbr_champs; $i++) {
$fieldname = mysql_field_name($res, $i);
print "{$fieldname}\t";
}
for ($i = 0; $i < $nbr_lignes; $i++) {
$row = mysql_fetch_row($res);
echo "\n";
foreach ($row as $dummykey => $col) {
/* if (is_numeric($col)) {
$col = "\"'".(string)$col."\"" ;
} */
print "{$col}\t";
}
}
break;
default:
echo "<table>";
for ($i = 0; $i < $nbr_champs; $i++) {
$fieldname = mysql_field_name($res, $i);
print "<th align='left'>{$fieldname}</th>";
}
$odd_even = 0;
for ($i = 0; $i < $nbr_lignes; $i++) {
$row = mysql_fetch_row($res);
if ($odd_even == 0) {
echo "\t<tr class='odd'>";
$odd_even = 1;
} elseif ($odd_even == 1) {
echo "\t<tr class='even'>";
$odd_even = 0;
}
foreach ($row as $dummykey => $col) {
if (trim($col) == '') {
$col = " ";
}
print '<td>' . $col . '</td>';
}
echo "</tr>";
}
echo "</table><hr>";
echo "<p align=left size='-3' class='pn-normal'>\n\t\t\t\t\t<form name='navbar' class='form-edit' action='{$page}' method='post'>";
echo "\n\t\t\t\t\t<input type='hidden' name='numero_page' value='{$numero_page}' />\n\t\t\t\t\t<input type='hidden' name='id_proc' value='{$id_proc}' />\n\t\t\t\t\t<input type='hidden' name='categ' value='{$categ}' />\n\t\t\t\t\t<input type='hidden' name='sub' value='{$sub}' />";
print $param_hidden;
// LIENS PAGE SUIVANTE et PAGE PRECEDENTE
// si le nombre de page n'est pas 0 et si la variable numero_page n'est pas définie
// dans cette condition, la variable numero_page est incrémenté et est inférieure à $nombre
// constitution des liens
$suivante = $numero_page + 1;
$precedente = $numero_page - 1;
// affichage du lien précédent si nécéssaire
if ($precedente >= 0) {
$nav_bar .= "<img src='./images/left.gif' border='0' title='{$msg['48']}' alt='[{$msg['48']}]' hspace='3' align='bottom' onClick=\"document.navbar.dest.value='';document.navbar.numero_page.value='{$precedente}'; document.navbar.limite_page.value='{$limite_page}'; document.navbar.submit(); \"/>";
}
for ($i = 0; $i <= $nbpages_arrondi; $i++) {
if ($i == $numero_page) {
$nav_bar .= "<strong>" . ($i + 1) . "/" . ($nbpages_arrondi + 1) . "</strong>";
}
}
if ($suivante <= $nbpages_arrondi) {
$nav_bar .= "<img src='./images/right.gif' border='0' title='{$msg['49']}' alt='[{$msg['49']}]' hspace='3' align='bottom' onClick=\"document.navbar.dest.value='';document.navbar.numero_page.value='{$suivante}'; document.navbar.limite_page.value='{$limite_page}'; document.navbar.submit(); \" />";
}
echo $nav_bar;
echo "\n\t\t\t\t\t<input type='hidden' name='dest' value='' />\n\t\t\t\t\t{$msg['edit_cbgen_mep_afficher']} <input type='text' name='limite_page' value='{$limite_page}' class='saisie-5em' /> {$msg['1905']}\n\t\t\t\t\t<input type='submit' class='bouton' value='" . $msg['actualiser'] . "' onclick=\"this.form.dest.value='';document.navbar.numero_page.value=0;\" /><font size='4'> </font>\n\t\t\t\t\t<input type='image' src='./images/tableur.gif' border='0' onClick=\"this.form.dest.value='TABLEAU';\" alt='Export tableau EXCEL' title='Export tableau EXCEL' /><font size='4'> </font>\n\t\t\t\t\t<input type='image' src='./images/tableur_html.gif' border='0' onClick=\"this.form.dest.value='TABLEAUHTML';\" alt='Export tableau HTML' title='Export tableau HTML' />\n\t\t\t\t\t</form></p>";
break;
}
} else {
echo $msg["etatperso_aucuneligne"];
}
mysql_free_result($res);
}
}
function print_results_tableau($liste_result)
{
global $base_path, $msg, $dbh;
$fichier_temp_nom = str_replace(" ", "", microtime());
$fichier_temp_nom = str_replace("0.", "", $fichier_temp_nom);
$fname = tempnam($base_path . "/temp", $fichier_temp_nom . ".xls");
$workbook = new writeexcel_workbook($fname);
$worksheet =& $workbook->addworksheet();
$req = "select * from " . $this->p_perso->prefix . "_custom where multiple=1 order by ordre";
$res = pmb_mysql_query($req, $dbh);
$num_col = 0;
$num_ligne = 0;
$field_id = array();
while ($champ = pmb_mysql_fetch_object($res)) {
$field_id[] = $champ->idchamp;
$worksheet->write($num_ligne, $num_col, $champ->titre);
$num_col++;
}
$num_ligne++;
foreach ($liste_result as $idfiche => $liste) {
$num_col = 0;
foreach ($field_id as $idchamp) {
$val = "";
if ($liste[$idchamp]) {
foreach ($liste[$idchamp] as $cle => $valeur) {
if ($val) {
$val .= "\n";
}
$val .= $valeur;
}
}
$worksheet->write($num_ligne, $num_col, $val);
$num_col++;
}
$num_ligne++;
}
$workbook->close();
header("Content-Type: application/x-msexcel; name=\"Tableau.xls\"");
header("Content-Disposition: inline; filename=\"Tableau.xls\"");
$fh = fopen($fname, "rb");
fpassthru($fh);
unlink($fname);
}
$pair_impair = "odd";
}
$parity++;
$tr_javascript = " onmouseover=\"this.className='surbrillance'\" onmouseout=\"this.className='{$pair_impair}'\" onmousedown=\"document.location='./edit.php?categ=procs&sub=&action=execute&id_proc={$row['0']}';\" ";
$buf_contenu .= "\n<tr class='{$pair_impair}' {$tr_javascript} style='cursor: pointer'>\n\t\t\t\t\t<td><strong>{$row['1']}</strong><br />\n\t\t\t\t\t\t<small>{$row['3']}</small></td>\n\t\t\t\t</tr>";
}
}
$buf_contenu = "<table><tr><th colspan=4>" . $buf_tit . "</th></tr>" . $buf_contenu . "</table>";
print gen_plus("procclass" . $buf_class, $buf_tit, $buf_contenu);
}
switch ($dest) {
case "TABLEAU":
$fichier_temp_nom = str_replace(" ", "", microtime());
$fichier_temp_nom = str_replace("0.", "", $fichier_temp_nom);
$fname = tempnam("./temp", $fichier_temp_nom . ".xls");
$workbook = new writeexcel_workbook($fname);
$worksheet =& $workbook->addworksheet();
break;
case "TABLEAUHTML":
echo "<h1>" . $msg[1130] . " : " . $msg[1131] . "</h1>";
break;
case "TABLEAUCSV":
break;
case "EXPORT_NOTI":
$fichier_temp_nom = str_replace(" ", "", microtime());
$fichier_temp_nom = str_replace("0.", "", $fichier_temp_nom);
$fname = tempnam("./temp", $fichier_temp_nom . ".doc");
break;
default:
echo "<h1>" . $msg[1130] . " : " . $msg[1131] . "</h1>";
break;
public function render_xls_file($name = "state")
{
global $msg, $charset;
$tmp_file = tempnam(sys_get_temp_dir(), "state_");
header("Content-Type: application/x-msexcel; name=\"" . $name . ".xls\"");
header("Content-Disposition: inline; filename=\"" . $name . ".xls\"");
$workbook = new writeexcel_workbook($tmp_file);
$worksheet =& $workbook->addworksheet();
$show_fields_tabl = $this->my_param["group"]["show_fields"];
$group_fields_tabl = $this->my_param["group"]["group_fields"];
if (count($show_fields_tabl)) {
//1ère ligne
$nb_ligne = 0;
$nb_colonne = 0;
foreach ($group_fields_tabl as $value) {
$worksheet->write($nb_ligne, $nb_colonne, $this->datas[0][$value]);
$nb_colonne++;
}
foreach ($show_fields_tabl as $value) {
$worksheet->write($nb_ligne, $nb_colonne, $this->datas[0][$value]);
$nb_colonne++;
}
//2ème ligne
$nb_ligne++;
$nb_colonne = 0;
foreach ($group_fields_tabl as $value) {
$worksheet->write($nb_ligne, $nb_colonne, $msg["editions_state_view_group_distinct"]);
$nb_colonne++;
}
foreach ($show_fields_tabl as $value) {
$worksheet->write($nb_ligne, $nb_colonne, $msg["editions_state_view_group_filter_" . $this->my_param["group"]["function_fields"]["function_field_" . $value]]);
$nb_colonne++;
}
//Résultat
$new_data = $this->sqlite_calc_group();
if (count($new_data)) {
foreach ($new_data as $ligne_result) {
$nb_ligne++;
$nb_colonne = 0;
foreach ($group_fields_tabl as $value) {
$worksheet->write($nb_ligne, $nb_colonne, $ligne_result["champ_" . $value]);
$nb_colonne++;
}
foreach ($show_fields_tabl as $value) {
$worksheet->write($nb_ligne, $nb_colonne, $ligne_result["alias_" . $value]);
$nb_colonne++;
}
}
}
}
$workbook->close();
$fh = fopen($tmp_file, "rb");
fpassthru($fh);
unlink($tmp_file);
}
function writeReportToExcelFile($fileName, $filterlist = false)
{
global $currentModule, $current_language;
$mod_strings = return_module_language($current_language, $currentModule);
require_once "include/php_writeexcel/class.writeexcel_workbook.inc.php";
require_once "include/php_writeexcel/class.writeexcel_worksheet.inc.php";
$workbook = new writeexcel_workbook($fileName);
$worksheet =& $workbook->addworksheet();
# Set the column width for columns 1, 2, 3 and 4
$worksheet->set_column(0, 3, 25);
# Create a format for the column headings
$header =& $workbook->addformat();
$header->set_bold();
$header->set_size(12);
$header->set_color('blue');
$arr_val = $this->GenerateReport("PDF", $filterlist);
$totalxls = $this->GenerateReport("TOTALXLS", $filterlist);
if (isset($arr_val)) {
foreach ($arr_val[0] as $key => $value) {
$worksheet->write(0, $count, utf8_decode($key), $header);
$count = $count + 1;
}
$rowcount = 1;
foreach ($arr_val as $key => $array_value) {
$dcount = 0;
foreach ($array_value as $hdr => $value) {
//$worksheet->write($key+1, $dcount, iconv("UTF-8", "ISO-8859-1", $value));
$value = decode_html($value);
$worksheet->write($key + 1, $dcount, iconv("UTF-8", "Windows-1252", $value));
$dcount = $dcount + 1;
}
$rowcount++;
}
$rowcount++;
$count = 0;
if (is_array($totalxls[0])) {
foreach ($totalxls[0] as $key => $value) {
$chdr = substr($key, -3, 3);
$translated_str = in_array($chdr, array_keys($mod_strings)) ? $mod_strings[$chdr] : $key;
$worksheet->write($rowcount, $count, utf8_decode($translated_str));
$count = $count + 1;
}
}
$rowcount++;
foreach ($totalxls as $key => $array_value) {
$dcount = 0;
foreach ($array_value as $hdr => $value) {
//$worksheet->write($key+1, $dcount, iconv("UTF-8", "ISO-8859-1", $value));
//if ($dcount==1)
// $worksheet->write($key+$rowcount, 0, utf8_decode(substr($hdr,0,strlen($hdr)-4)));
$value = decode_html($value);
$worksheet->write($key + $rowcount, $dcount, iconv("UTF-8", "Windows-1252", $value));
$dcount = $dcount + 1;
}
}
}
$workbook->close();
}
function dump_ldap($mode = 0)
{
global $config;
$ldap = $config->get_ldap_link();
$display = "";
if ($mode == 2) {
// Single Entry Export !
/* Get required attributes */
$d = base64_decode($_GET['d']);
$n = base64_decode($_GET['n']);
/* Create dn to search entries in */
$dn = $d . $n;
/* Create some strings */
$date = date('dS \\of F Y ');
$fname = tempnam("/tmp", "demo.xls");
/* Create xls workbench */
$workbook = new writeexcel_workbook($fname);
/* Create some styles to generate xls */
$title_title = $workbook->addformat(array(bold => 1, color => 'green', size => 11, underline => 2, font => 'Helvetica'));
$title_bold = $workbook->addformat(array(bold => 1, color => 'black', size => 10, font => 'Helvetica'));
# Create a format for the phone numbers
$f_phone = $workbook->addformat();
$f_phone->set_align('left');
$f_phone->set_num_format('\\0#');
/* If the switch reaches default (it should not),
this will be set to false, so nothig will be created ... */
$save = true;
/* Check which type of data was requested */
switch ($d) {
/* PEOPLE
Get all peoples from this $dn
and put them into the xls work sheet */
case get_people_ou():
$user = $ldap->gen_xls($dn, "(objectClass=*)", array("uid", "dateOfBirth", "gender", "givenName", "preferredLanguage"));
$intitul = array(_("Birthday") . ":", _("Sex") . ":", _("Surname") . "/" . _("Given name") . ":", _("Language") . ":");
// name of the xls file
$name_section = _("Users");
$worksheet = $workbook->addworksheet(_("Users"));
$worksheet->set_column('A:B', 51);
$user_nbr = count($user);
$worksheet->write('A1', sprintf(_("User list of %s on %s"), $n, $date), $title_title);
$r = 3;
for ($i = 1; $i < $user_nbr; $i++) {
if ($i > 1) {
$worksheet->write('A' . $r++, "");
}
$worksheet->write('A' . $r++, _("User ID") . ": " . $user[$i][0], $title_bold);
for ($j = 1; $j < 5; $j++) {
$r++;
$worksheet->write('A' . $r, $intitul[$j - 1]);
$user[$i][$j] = utf8_decode($user[$i][$j]);
$worksheet->write('B' . $r, $user[$i][$j]);
}
$worksheet->write('A' . $r++, "");
}
break;
/* GROUPS
Get all groups from th $dn
and put them into the xls work sheet */
/* GROUPS
Get all groups from th $dn
and put them into the xls work sheet */
case get_groups_ou():
/* Get group data */
$groups = $ldap->gen_xls($dn, "(objectClass=*)", array("cn", "memberUid"), TRUE, 1);
$intitul = array(_("Members") . ":");
//name of the xls file
$name_section = _("Groups");
$worksheet = $workbook->addworksheet(_("Groups"));
$worksheet->set_column('A:B', 51);
//count number of groups
$groups_nbr = count($groups);
$worksheet->write('A1', sprintf(_("Groups of %s on %s"), $n, $date), $title_title);
$r = 3;
for ($i = 1; $i < $groups_nbr; $i++) {
$worksheet->write('A' . $r++, _("User ID") . ": " . $groups[$i][0][0], $title_bold);
for ($j = 1; $j <= 2; $j++) {
$r++;
$worksheet->write('A' . $r, $intitul[$j - 1]);
for ($k = 0; $k <= $groups[$i][$j]['count']; $k++) {
$worksheet->write('B' . $r, $groups[$i][$j][$k]);
$r++;
}
}
}
break;
/* SYSTEMS
Get all systems from th $dn
and put them into the xls work sheet */
/* SYSTEMS
Get all systems from th $dn
and put them into the xls work sheet */
case get_ou("systemManagement", "systemRDN"):
$name_section = _("Servers");
$computers = $ldap->gen_xls($dn, "(&(objectClass=*)(cn=*))", array("cn", "description", "uid"));
$intitul = array(_("Description") . ":", _("User ID") . ":");
$worksheet = $workbook->addworksheet(_("Computers"));
$worksheet->set_column('A:B', 32);
//count number of computers
$computers_nbr = count($computers);
$r = 1;
for ($i = 1; $i < $computers_nbr; $i++) {
if ($i > 1) {
$worksheet->write('A' . $r++, "");
}
$worksheet->write('A' . $r++, _("Common name") . ": " . $computers[$i][0], $title_bold);
for ($j = 1; $j < 3; $j++) {
$r++;
$worksheet->write('A' . $r, $intitul[$j - 1]);
$computers[$i][$j] = utf8_decode($computers[$i][$j]);
$worksheet->write('B' . $r, $computers[$i][$j]);
}
$worksheet->write('A' . $r++, "");
}
break;
/* SYSTEMS
Get all systems from th $dn
and put them into the xls work sheet */
/* SYSTEMS
Get all systems from th $dn
and put them into the xls work sheet */
case get_ou("servgeneric", "serverRDN"):
$servers = $ldap->gen_xls($dn, "(objectClass=*)", array("cn"));
$intitul = array(_("Server name") . ":");
//name of the xls file
$name_section = _("Servers");
$worksheet = $workbook->addworksheet(_("Servers"));
$worksheet->set_column('A:B', 51);
//count number of servers
$servers_nbr = count($servers);
$worksheet->write('A1', sprintf(_("Servers of %s on %s"), $n, $date), $title_title);
$r = 3;
$worksheet->write('A' . $r++, _("Servers") . ": ", $title_bold);
for ($i = 1; $i < $servers_nbr; $i++) {
for ($j = 0; $j < 1; $j++) {
$r++;
$worksheet->write('A' . $r, $intitul[$j]);
$servers[$i][$j] = utf8_decode($servers[$i][$j]);
$worksheet->write('B' . $r, $servers[$i][$j]);
}
}
break;
case "dc=addressbook,":
//data about addressbook
/* ADDRESSBOOK
Get all addressbook entries from $dn
and put them into the xls work sheet */
$address = $ldap->gen_xls($dn, "(objectClass=*)", array("cn", "displayName", "facsimileTelephoneNumber", "givenName", "homePhone", "homePostalAddress", "initials", "l", "mail", "mobile", "o", "ou", "pager", "telephoneNumber", "postalAddress", "postalCode", "sn", "st", "title"));
$intitul = array(_("Common name") . ":", _("Display name") . ":", _("Fax") . ":", _("Name") . "/" . _("Given name") . ":", _("Home phone") . ":", _("Home postal address") . ":", _("Initials") . ":", _("Location") . ":", _("Mail address") . ":", _("Mobile phone") . ":", _("City") . ":", _("Postal address") . ":", _("Pager") . ":", _("Phone number") . ":", _("Address") . ":", _("Postal code") . ":", _("Surname") . ":", _("State") . ":", _("Function") . ":");
//name of the xls file
$name_section = _("Address book");
$worksheet = $workbook->addworksheet(_("Servers"));
$worksheet->set_column('A:B', 51);
//count number of entries
$address_nbr = count($address);
$worksheet->write('A1', sprintf(_("Address book of %s on %s"), $n, $date), $title_title);
$r = 3;
for ($i = 1; $i < $address_nbr; $i++) {
if ($i > 1) {
$worksheet->write('A' . $r++, "");
}
$worksheet->write('A' . $r++, _("Common Name") . ": " . $address[$i][0], $title_bold);
for ($j = 1; $j < 19; $j++) {
$r++;
$worksheet->write('A' . $r, $intitul[$j]);
$address[$i][$j] = utf8_decode($address[$i][$j]);
$worksheet->write('B' . $r, $address[$i][$j], $f_phone);
}
$worksheet->write('A' . $r++, "");
}
break;
default:
$save = false;
echo "Specified parameter '" . $d . "' was not found in switch-case.";
}
if ($save) {
$workbook->close();
}
// We'll be outputting a xls
header('Content-type: application/x-msexcel');
// It will be called demo.xls
header('Content-Disposition: attachment; filename=xls_export_' . $name_section . ".xls");
// The source is in original.xls
readfile($fname);
unlink($fname);
} elseif ($mode == 3) {
// Full Export !
$dn = base64_decode($_GET['dn']);
//data about users
$user = $ldap->gen_xls(get_people_ou() . $dn, "(objectClass=*)", array("uid", "dateOfBirth", "gender", "givenName", "preferredLanguage"));
$user_intitul = array(_("Day of birth") . ":", _("Sex") . ":", _("Surname") . "/" . _("Given name") . ":", _("Language") . ":");
//data about groups
$groups = $ldap->gen_xls(get_groups_ou() . $dn, "(objectClass=*)", array("cn", "memberUid"), TRUE, 1);
$groups_intitul = array(_("Members") . ":");
//data about computers
$computers = $ldap->gen_xls("ou=computers," . $dn, "(objectClass=*)", array("cn", "description", "uid"));
$computers_intitul = array(_("Description") . ":", _("UID") . ":");
//data about servers
$servers = $ldap->gen_xls(get_ou("servgeneric", "serverRDN") . $dn, "(objectClass=*)", array("cn"));
$servers_intitul = array(_("Name") . ":");
//data about addressbook
$address = $ldap->gen_xls("dc=addressbook," . $dn, "(objectClass=*)", array("cn", "displayName", "facsimileTelephoneNumber", "givenName", "homePhone", "homePostalAddress", "initials", "l", "mail", "mobile", "o", "ou", "pager", "telephoneNumber", "postalAddress", "postalCode", "sn", "st", "title"));
$address_intitul = array("cn", _("Display name") . ":", _("Fax") . ":", _("Surname") . "/" . _("Given name") . ":", _("Phone number") . ":", _("Postal address") . ":", _("Initials") . ":", _("City") . ":", _("Email address") . ":", _("Mobile") . ":", _("Organization") . ":", _("Organizational unit") . ":", _("Pager") . ":", _("Phone number") . ":", _("Postal address") . ":", _("Postal Code") . ":", _("Surname") . ":", _("State") . ":", _("Title") . ":");
//name of the xls file
$name_section = _("Full");
$date = date('dS \\of F Y ');
$fname = tempnam("/tmp", "demo.xls");
$workbook = new writeexcel_workbook($fname);
$worksheet = $workbook->addworksheet(_("Users"));
$worksheet2 = $workbook->addworksheet(_("Groups"));
$worksheet3 = $workbook->addworksheet(_("Servers"));
$worksheet4 = $workbook->addworksheet(_("Computers"));
$worksheet5 = $workbook->addworksheet(_("Address book"));
$worksheet->set_column('A:B', 51);
$worksheet2->set_column('A:B', 51);
$worksheet3->set_column('A:B', 51);
$worksheet4->set_column('A:B', 51);
$worksheet5->set_column('A:B', 51);
$title_title = $workbook->addformat(array(bold => 1, color => 'green', size => 11, font => 'Helvetica'));
$title_bold = $workbook->addformat(array(bold => 1, color => 'black', size => 10, font => 'Helvetica'));
# Create a format for the phone numbers
$f_phone = $workbook->addformat();
$f_phone->set_align('left');
$f_phone->set_num_format('\\0#');
//count number of users
$user_nbr = count($user);
$worksheet->write('A1', sprintf(_("User list of %s on %s"), $dn, $date), $title_title);
$r = 3;
for ($i = 1; $i < $user_nbr; $i++) {
if ($i > 1) {
$worksheet->write('A' . $r++, "");
}
$worksheet->write('A' . $r++, _("User ID") . ": " . $user[$i][0], $title_bold);
for ($j = 1; $j < 5; $j++) {
$r++;
$worksheet->write('A' . $r, $user_intitul[$j - 1]);
$user[$i][$j] = utf8_decode($user[$i][$j]);
$worksheet->write('B' . $r, $user[$i][$j]);
}
$worksheet->write('A' . $r++, "");
}
//count number of groups
$groups_nbr = count($groups);
$worksheet2->write('A1', sprintf(_("Groups of %s on %s"), $dn, $date), $title_title);
$r = 3;
for ($i = 1; $i < $groups_nbr; $i++) {
$worksheet2->write('A' . $r++, _("User ID") . ": " . $groups[$i][0][0], $title_bold);
for ($j = 1; $j <= 2; $j++) {
$r++;
$worksheet2->write('A' . $r, $group_intitul[$j - 1]);
for ($k = 0; $k <= $groups[$i][$j]['count']; $k++) {
$worksheet2->write('B' . $r, $groups[$i][$j][$k]);
$r++;
}
}
}
//count number of servers
$servers_nbr = count($servers);
$worksheet3->write('A1', sprintf(_("Servers of %s on %s"), $dn, $date), $title_title);
$r = 3;
$worksheet3->write('A' . $r++, _("Servers") . ": ", $title_bold);
for ($i = 1; $i < $servers_nbr; $i++) {
for ($j = 0; $j < 1; $j++) {
$r++;
$worksheet3->write('A' . $r, $servers_intitul[$j]);
$servers[$i][$j] = utf8_decode($servers[$i][$j]);
$worksheet3->write('B' . $r, $servers[$i][$j]);
}
}
//count number of computers
$computers_nbr = count($computers);
$worksheet4->write('A1', sprintf(_("Computers of %s on %s"), $dn, $date), $title_title);
$r = 3;
for ($i = 1; $i < $computers_nbr; $i++) {
if ($i > 1) {
$worksheet->write('A' . $r++, "");
}
$worksheet4->write('A' . $r++, _("Common name") . ": " . $computers[$i][0], $title_bold);
for ($j = 1; $j < 3; $j++) {
$r++;
$worksheet4->write('A' . $r, $computers_intitul[$j - 1]);
$computers[$i][$j] = utf8_decode($computers[$i][$j]);
$worksheet4->write('B' . $r, $computers[$i][$j]);
}
$worksheet4->write('A' . $r++, "");
}
//count number of entries
$address_nbr = count($address);
$worksheet5->write('A1', sprintf(_("Address book of %s on %s"), $dn, $date), $title_title);
$r = 3;
for ($i = 1; $i < $address_nbr; $i++) {
if ($i > 1) {
$worksheet5->write('A' . $r++, "");
}
$worksheet5->write('A' . $r++, _("Common name") . ": " . $address[$i][0], $title_bold);
for ($j = 1; $j < 19; $j++) {
$r++;
$worksheet5->write('A' . $r, $address_intitul[$j]);
$address[$i][$j] = utf8_decode($address[$i][$j]);
$worksheet5->write('B' . $r, $address[$i][$j], $f_phone);
}
$worksheet5->write('A' . $r++, "");
}
$workbook->close();
// We'll be outputting a xls
header('Content-type: application/x-msexcel');
// It will be called demo.xls
header('Content-Disposition: attachment; filename=' . $name_section . ".xls");
readfile($fname);
unlink($fname);
} elseif ($mode == 4) {
// IVBB LDIF Export
$dn = base64_decode($_GET['dn']);
echo $display;
}
}
function excel_file_generator($headings, $data, $filename = 'Attendee List')
{
$this->epl->load_library("php_writeexcel-0.3.0/class.writeexcel_workbook.inc.php", false);
$this->epl->load_library("php_writeexcel-0.3.0/class.writeexcel_worksheet.inc.php", false);
$fname = tempnam(EPL_FULL_PATH, "tempexport");
$workbook = new writeexcel_workbook($fname);
$worksheet = $workbook->addworksheet();
$heading = $workbook->addformat(array(bold => 1, color => 'black', size => 11, merge => 1, align => 'left', border_color => "black", top => 1, bottom => 1, left => 1, right => 1));
$heading->set_text_wrap();
//Column widths
$worksheet->set_column('A:S', 14);
//using this so the Nordic letters are encoded correctly
//array_walk( $headings, create_function( '&$item', '$item = utf8_decode($item);' ) );
$worksheet->set_row(0, 30);
$worksheet->write(0, 0, $headings, $heading);
$heading = $workbook->addformat(array(bold => 0, color => 'blue', size => 11, align => 'left'));
$format1 = $workbook->addformat();
$format1->set_color('black');
//$border1->set_bold();
$format1->set_size(11);
$format1->set_pattern(0x1);
$format1->set_fg_color('white');
$format1->set_border_color('black');
$format1->set_top(1);
$format1->set_bottom(1);
$format1->set_left(1);
$format1->set_right(1);
$format1->set_align('left');
$format1->set_align('vcenter');
$format1->set_text_wrap();
//TODO make this better, doing all this for the set_zize 8
$format2 = $workbook->addformat();
$format2->set_color('black');
$format2->set_size(8);
$format2->set_pattern(0x1);
$format2->set_fg_color('white');
$format2->set_border_color('black');
$format2->set_top(1);
$format2->set_bottom(1);
$format2->set_left(1);
$format2->set_right(1);
$format2->set_align('left');
$format2->set_align('vcenter');
$format2->set_text_wrap();
$data_row = 1;
foreach ($data as $k => $v) {
$worksheet->set_row($data_row, 53);
$col = 0;
foreach ($v as $_k => $_v) {
$_format = $format1;
if ($col == 2) {
$_format = $format2;
}
if ($col == 1) {
$_v = $_v . ' ';
}
$worksheet->write($data_row, $col, $_v, $_format);
$col++;
}
$data_row++;
}
$worksheet->print_area(0, 0, --$data_row, --$col);
$worksheet->set_zoom(85);
$worksheet->fit_to_pages(1, 1);
$worksheet->set_margins_LR(0.64);
$worksheet->set_margins_TB(0.5);
$worksheet->set_landscape();
$workbook->close();
header("Content-Type: application/x-msexcel; name=\"attendee_list.xls\"");
header("Content-Disposition: inline; filename=\"{$filename}.xls\"");
$fh = fopen($fname, "rb");
fpassthru($fh);
@unlink($fname);
}
function ExcelGeneration() {
$worksheet = "";
require_once 'excel.php';
require_once "php_writeexcel-0.3.0/class.writeexcel_workbook.inc.php";
require_once "php_writeexcel-0.3.0/class.writeexcel_worksheet.inc.php";
$workbook = new writeexcel_workbook(self::TempFolderAddress);
$worksheet = & $workbook->addworksheet("Sheet1");
$heading = & $workbook->addformat(array('align' => 'center', 'bold' => 1, 'bg_color' => 'blue', 'color' => 'white'));
if ($this->rowNumber)
$worksheet->write(0, 0, "ردیف", $heading);
for ($i = 0; $i < count($this->columns); $i++) {
$worksheet->write(0, $i + ($this->rowNumber ? 1 : 0), $this->columns[$i]->header, $heading);
if ($this->columns[$i]->HaveSum != -1)
$this->EnableSumRow = true;
}
if (is_array($this->mysql_resource)) {
for ($index = 0; $index < count($this->mysql_resource); $index++) {
$row = $this->mysql_resource[$index];
if ($this->rowNumber)
$worksheet->write($index + 1, 0, ($index + 1));
for ($i = 0; $i < count($this->columns); $i++) {
$val = "";
if(!empty($this->columns[$i]->renderFunction))
eval("\$val = " . $this->columns[$i]->renderFunction . "(\$row,\$row[\$this->columns[\$i]->field]);");
else
$val = $row[$this->columns[$i]->field];
$worksheet->write($index + 1, $i + ($this->rowNumber ? 1 : 0), $val);
}
}
} else {
$index = 0;
while ($row = $this->mysql_resource->fetch()) {
if ($this->rowNumber)
$worksheet->write($index + 1, 0, ($index + 1));
for ($i = 0; $i < count($this->columns); $i++) {
$val = "";
/* if(!empty($this->columns[$i]->renderFunction))
eval("\$val = " . $this->columns[$i]->renderFunction . "(\$row,\$row[\$this->columns[\$i]->field]);");
else */
$val = $row[$this->columns[$i]->field];
$worksheet->write($index + 1, $i + ($this->rowNumber ? 1 : 0), $val);
}
$index++;
}
}
$workbook->close();
header("Content-type: application/ms-excel");
header("Content-disposition: inline; filename=excel.xls");
echo file_get_contents(self::TempFolderAddress);
unlink(self::TempFolderAddress);
die();
}
} else {
$sql_hp = "and bk_hp<>''";
}
$sql = "select count(*) as cnt from {$g5['sms5_book_table']} where 1 {$sql_bg} {$sql_hp} order by bk_name";
$total = sql_fetch($sql);
if (!$total['cnt']) {
alert_just('데이터가 없습니다.');
}
$qry = sql_query("select * from {$g5['sms5_book_table']} where 1 {$sql_bg} {$sql_hp} order by bk_name");
/*================================================================================
php_writeexcel http://www.bettina-attack.de/jonny/view.php/projects/php_writeexcel/
=================================================================================*/
include_once G5_LIB_PATH . '/Excel/php_writeexcel/class.writeexcel_workbook.inc.php';
include_once G5_LIB_PATH . '/Excel/php_writeexcel/class.writeexcel_worksheet.inc.php';
$fname = tempnam(G5_DATA_PATH, "tmp.xls");
$workbook = new writeexcel_workbook($fname);
$worksheet = $workbook->addworksheet();
$num2_format =& $workbook->addformat(array(num_format => '\\0#'));
// Put Excel data
$data = array('이름', '전화번호');
$data = array_map('iconv_euckr', $data);
$col = 0;
foreach ($data as $cell) {
$worksheet->write(0, $col++, $cell);
}
for ($i = 1; $res = sql_fetch_array($qry); $i++) {
$res = array_map('iconv_euckr', $res);
$hp = get_hp($res['bk_hp'], $hyphen);
if ($no_hp && $res['bk_hp'] != '' && !$hp) {
continue;
}
public function get_excel_summarize()
{
global $msg;
global $charset, $fichier_temp_nom, $titre_page;
global $cashdesk_filter, $start_date, $stop_date;
if (!count($this->cashdesk_list)) {
return "";
}
if (!$cashdesk_filter) {
$cashdesk_filter = array();
}
if (!$cashdesk_filter[0]) {
$cashdesk_filter = array();
}
$fname = tempnam("./temp", "{$fichier_temp_nom}.xls");
$workbook = new writeexcel_workbook($fname);
$worksheet =& $workbook->addworksheet();
$worksheet->write(0, 0, $titre_page);
$i = 2;
$j = 2;
$worksheet->write($i, $j++, $msg["cashdesk_edition_name"]);
$worksheet->write($i, $j++, $msg["cashdesk_edition_transac_name"]);
$worksheet->write($i, $j++, $msg["cashdesk_edition_transac_unit_price"]);
$worksheet->write($i, $j++, $msg["cashdesk_edition_transac_montant"]);
$worksheet->write($i, $j++, $msg["cashdesk_edition_transac_realisee_no"]);
$worksheet->write($i, $j++, $msg["cashdesk_edition_transac_realisee"]);
$worksheet->write($i, $j++, $msg["cashdesk_edition_transac_encaissement_no"]);
$worksheet->write($i, $j++, $msg["cashdesk_edition_transac_encaissement"]);
$i++;
foreach ($this->cashdesk_list as $index => $cashdesk) {
if (count($cashdesk_filter)) {
if (!in_array($cashdesk['id'], $cashdesk_filter)) {
continue;
}
}
$cashdesk_info = new cashdesk($cashdesk['id']);
$all_transactions = $cashdesk_info->summarize($start_date, $stop_date, $transactype, $encaissement);
if (!count($all_transactions)) {
continue;
}
foreach ($all_transactions as $transactions) {
$j = 2;
$worksheet->write($i, $j++, $cashdesk['name']);
$worksheet->write($i, $j++, $transactions['name']);
$worksheet->write($i, $j++, $transactions['unit_price']);
$worksheet->write($i, $j++, $transactions['montant']);
$worksheet->write($i, $j++, $transactions['realisee_no']);
$worksheet->write($i, $j++, $transactions['realisee']);
$worksheet->write($i, $j++, $transactions['encaissement_no']);
$worksheet->write($i, $j++, $transactions['encaissement']);
$i++;
}
}
$workbook->close();
$fh = fopen($fname, "rb");
fpassthru($fh);
unlink($fname);
}
}
$now=$argv[1];
// Recupere env dolibarr
$version='$Revision: 1.8 $';
require_once("../../htdocs/master.inc.php");
require_once(PHP_WRITEEXCEL_PATH."/class.writeexcel_workbook.inc.php");
require_once(PHP_WRITEEXCEL_PATH."/class.writeexcel_worksheet.inc.php");
$error = 0;
$fname = DOL_DATA_ROOT.'/export-contacts.xls';
$workbook = new writeexcel_workbook($fname);
$page = &$workbook->addworksheet('Export Dolibarr');
$page->set_column(0,4,18); // A
$sql = "SELECT distinct c.email, c.name, c.firstname, s.nom ";
$sql .= " FROM ".MAIN_DB_PREFIX."socpeople as c";
$sql .= ", ".MAIN_DB_PREFIX."societe as s";
$sql .= " WHERE s.rowid = c.fk_soc";
$sql .= " AND c.email IS NOT NULL";
$sql .= " ORDER BY c.email ASC";
$resql=$db->query($sql);
if ($resql)
{
/**
* Export to Excel
*
* @param \Sng\Recordsmanager\Utility\Query $query
*/
public function exportToEXCEL(\Sng\Recordsmanager\Utility\Query $query)
{
$rows = array_merge(array($query->getHeaders()), $query->getRows());
$dirName = PATH_site . 'typo3temp/';
$filename = 'TYPO3_' . $query->getFrom() . '_export_' . date('dmy-Hi') . '.xls';
require_once PATH_site . "typo3conf/ext/recordsmanager/Resources/Private/Php/php_writeexcel-0.3.0/class.writeexcel_workbook.inc.php";
require_once PATH_site . "typo3conf/ext/recordsmanager/Resources/Private/Php/php_writeexcel-0.3.0/class.writeexcel_worksheet.inc.php";
$fname = $dirName . $filename;
$workbook = new \writeexcel_workbook($fname);
$worksheet = $workbook->addworksheet();
$header = $workbook->addformat();
$header->set_bold();
$header->set_size(12);
$line = 0;
foreach ($rows as $row) {
$col = 0;
foreach ($row as $field => $value) {
$value = self::cleanString($value);
if ($line == 0) {
$worksheet->write($line, $col++, $value, $header);
} else {
if (is_numeric($value)) {
$value = $value . " ";
}
$worksheet->write($line, $col++, $value);
}
}
$line++;
}
$workbook->close();
header("Content-Type: application/x-msexcel; name=\"" . $filename . "\"");
header("Content-Disposition: inline; filename=\"" . $filename . "\"");
$fh = fopen($fname, "rb");
fpassthru($fh);
unlink($fname);
exit;
}
function print_bud($id_bibli = 0, $id_bud = 0)
{
global $dbh, $msg, $charset;
global $pmb_gestion_devise;
global $acquisition_gestion_tva;
global $base_path, $class_path;
global $line;
if (!$id_bibli || !$id_bud) {
return;
}
//Export excel
$fname = str_replace(" ", "", microtime());
$fname = str_replace("0.", "", $base_path . "/temp/" . $fname);
require_once "{$class_path}/writeexcel/class.writeexcel_workbook.inc.php";
require_once "{$class_path}/writeexcel/class.writeexcel_worksheet.inc.php";
$workbook = new writeexcel_workbook($fname);
$worksheet = $workbook->addworksheet();
$bold = $workbook->addformat(array(bold => 1));
//Recuperation budget
$bud = new budgets($id_bud);
$lib_bud = $bud->libelle;
$mnt_bud = $bud->montant_global;
$devise = $pmb_gestion_devise;
switch ($acquisition_gestion_tva) {
case '0':
case '2':
$htttc = $msg['acquisition_ttc'];
$k_htttc = 'ttc';
$k_htttc_autre = 'ht';
break;
default:
$htttc = $msg['acquisition_ht'];
$k_htttc = 'ht';
$k_htttc_autre = 'ttc';
break;
}
if (!$bud->type_budget) {
$typ_bud = $msg['acquisition_budg_aff_rub'];
} else {
$typ_bud = $msg['acquisition_budg_aff_glo'];
}
//montant total pour budget par rubriques
if ($bud->type_budget == TYP_BUD_GLO) {
$mnt['tot'][$k_htttc] = $bud->montant_global;
$totaux = array('tot' => $mnt['tot'][$k_htttc], 'ava' => 0, 'eng' => 0, 'fac' => 0, 'pay' => 0, 'sol' => 0);
$totaux_autre = array('tot' => 0, 'ava' => 0, 'eng' => 0, 'fac' => 0, 'pay' => 0, 'sol' => 0);
} else {
$totaux = array('tot' => 0, 'ava' => 0, 'eng' => 0, 'fac' => 0, 'pay' => 0, 'sol' => 0);
$totaux_autre = array('tot' => 0, 'ava' => 0, 'eng' => 0, 'fac' => 0, 'pay' => 0, 'sol' => 0);
}
switch ($bud->statut) {
case STA_BUD_VAL:
$sta_bud = $msg['acquisition_statut_actif'];
break;
case STA_BUD_CLO:
$sta_bud = $msg['acquisition_statut_clot'];
break;
case STA_BUD_PRE:
default:
$sta_bud = $msg['acquisition_budg_pre'];
break;
}
$seu_bud = $bud->seuil_alerte;
//Recuperation exercice
$exer = new exercices($bud->num_exercice);
$lib_exer = $exer->libelle;
$lib_mnt_bud = number_format($mnt_bud, '2', '.', '');
$worksheet->write($line, 0, $msg['acquisition_bud'], $bold);
$worksheet->write($line, 1, $lib_bud);
$worksheet->write($line, 2, $msg['acquisition_budg_montant'], $bold);
//problème du symbole euro à faire passer en encodage iso...
$worksheet->write($line, 3, $lib_mnt_bud . " " . ($charset == "utf-8" ? html_entity_decode(stripslashes($devise)) : mb_convert_encoding(html_entity_decode(stripslashes($devise)), "windows-1252", "utf-8")) . " " . $htttc);
$line++;
$worksheet->write($line, 0, $msg['acquisition_budg_exer'], $bold);
$worksheet->write($line, 1, $lib_exer);
$worksheet->write($line, 2, $msg['acquisition_budg_aff_lib'], $bold);
$worksheet->write($line, 3, $typ_bud);
$line++;
$worksheet->write($line, 0, $msg['acquisition_statut'], $bold);
$worksheet->write($line, 1, $sta_bud);
$worksheet->write($line, 2, $msg['acquisition_budg_seuil'], $bold);
$worksheet->write($line, 3, $seu_bud . " %");
$line += 2;
if ($acquisition_gestion_tva == 1) {
$worksheet->write($line, 0, $msg['acquisition_rub'], $bold);
$worksheet->write($line, 1, $msg['acquisition_rub_mnt_tot'], $bold);
$worksheet->write($line, 2, $msg['acquisition_rub_mnt_ava_ht'], $bold);
$worksheet->write($line, 3, $msg['acquisition_rub_mnt_eng_ht'], $bold);
$worksheet->write($line, 4, $msg['acquisition_rub_mnt_fac_ht'], $bold);
$worksheet->write($line, 5, $msg['acquisition_rub_mnt_pay_ht'], $bold);
$worksheet->write($line, 6, $msg['acquisition_rub_mnt_sol'], $bold);
} elseif ($acquisition_gestion_tva == 2) {
$worksheet->write($line, 0, $msg['acquisition_rub'], $bold);
$worksheet->write($line, 1, $msg['acquisition_rub_mnt_tot'], $bold);
$worksheet->write($line, 2, $msg['acquisition_rub_mnt_ava_ttc'], $bold);
$worksheet->write($line, 3, $msg['acquisition_rub_mnt_eng_ttc'], $bold);
$worksheet->write($line, 4, $msg['acquisition_rub_mnt_fac_ttc'], $bold);
$worksheet->write($line, 5, $msg['acquisition_rub_mnt_pay_ttc'], $bold);
$worksheet->write($line, 6, $msg['acquisition_rub_mnt_sol'], $bold);
} else {
$worksheet->write($line, 0, $msg['acquisition_rub'], $bold);
$worksheet->write($line, 1, $msg['acquisition_rub_mnt_tot'], $bold);
$worksheet->write($line, 2, $msg['acquisition_rub_mnt_ava'], $bold);
$worksheet->write($line, 3, $msg['acquisition_rub_mnt_eng'], $bold);
$worksheet->write($line, 4, $msg['acquisition_rub_mnt_fac'], $bold);
$worksheet->write($line, 5, $msg['acquisition_rub_mnt_pay'], $bold);
$worksheet->write($line, 6, $msg['acquisition_rub_mnt_sol'], $bold);
}
$q = budgets::listRubriques($id_bud, 0);
$list_n1 = pmb_mysql_query($q, $dbh);
while ($row = pmb_mysql_fetch_object($list_n1)) {
//montant total pour budget par rubriques
$mnt['tot'][$k_htttc] = $row->montant;
//montant a valider
$mnt['ava'] = rubriques::calcAValider($row->id_rubrique);
//montant engage
$mnt['eng'] = rubriques::calcEngage($row->id_rubrique);
//montant facture
$mnt['fac'] = rubriques::calcFacture($row->id_rubrique);
//montant paye
$mnt['pay'] = rubriques::calcPaye($row->id_rubrique);
//solde
$mnt['sol'][$k_htttc] = $mnt['tot'][$k_htttc] - $mnt['eng'][$k_htttc];
foreach ($totaux as $k => $v) {
$totaux[$k] = $v + $mnt[$k][$k_htttc];
}
foreach ($totaux_autre as $k => $v) {
$totaux_autre[$k] = $v + $mnt[$k][$k_htttc_autre];
}
$lib_mnt = array();
foreach ($mnt as $k => $v) {
$lib_mnt[$k] = number_format($mnt[$k][$k_htttc], 2, '.', '');
if ($acquisition_gestion_tva && $k != "tot" && $k != "sol") {
$lib_mnt_autre[$k] = number_format($mnt[$k][$k_htttc_autre], 2, '.', '');
}
}
if ($bud->type_budget == TYP_BUD_GLO) {
$lib_mnt['tot'] = '';
$lib_mnt['sol'] = '';
}
$line++;
$worksheet->write($line, 0, $row->libelle);
$worksheet->write($line, 1, $lib_mnt["tot"]);
$worksheet->write($line, 2, $lib_mnt["ava"]);
$worksheet->write($line, 3, $lib_mnt["eng"]);
$worksheet->write($line, 4, $lib_mnt["fac"]);
$worksheet->write($line, 5, $lib_mnt["pay"]);
$worksheet->write($line, 6, $lib_mnt["sol"]);
if ($acquisition_gestion_tva) {
$line++;
if ($lib_mnt_autre["tot"]) {
$worksheet->write($line, 1, $lib_mnt_autre["tot"]);
}
if ($lib_mnt_autre["ava"]) {
$worksheet->write($line, 2, $lib_mnt_autre["ava"]);
}
if ($lib_mnt_autre["eng"]) {
$worksheet->write($line, 3, $lib_mnt_autre["eng"]);
}
if ($lib_mnt_autre["fac"]) {
$worksheet->write($line, 4, $lib_mnt_autre["fac"]);
}
if ($lib_mnt_autre["pay"]) {
$worksheet->write($line, 5, $lib_mnt_autre["pay"]);
}
if ($lib_mnt_autre["sol"]) {
$worksheet->write($line, 6, $lib_mnt_autre["sol"]);
}
}
//Sous-rubriques
$nb_sr = rubriques::countChilds($row->id_rubrique);
if ($nb_sr) {
printSousRubriques($bud, $row->id_rubrique, $worksheet, 1);
}
}
//recuperation de la liste complete des rubriques
if ($bud->type_budget == TYP_BUD_GLO) {
$totaux['tot'] = $bud->montant_global;
$totaux['sol'] = $totaux['tot'] - $totaux['eng'];
}
foreach ($totaux as $k => $v) {
if (is_numeric($v)) {
$totaux[$k] = number_format($v, 2, '.', '');
} else {
$totaux[$k] = ' ';
}
}
foreach ($totaux_autre as $k => $v) {
if (is_numeric($v) && $k != 'tot' && $k != 'sol') {
$totaux_autre[$k] = number_format($v, 2, '.', '');
} else {
$totaux_autre[$k] = ' ';
}
}
$line += 2;
$worksheet->write($line, 0, $msg["acquisition_budg_montant"], $bold);
$worksheet->write($line, 1, $totaux["tot"], $bold);
$worksheet->write($line, 2, $totaux["ava"], $bold);
$worksheet->write($line, 3, $totaux["eng"], $bold);
$worksheet->write($line, 4, $totaux["fac"], $bold);
$worksheet->write($line, 5, $totaux["pay"], $bold);
$worksheet->write($line, 6, $totaux["sol"], $bold);
if ($acquisition_gestion_tva) {
$line++;
$worksheet->write($line, 1, $totaux_autre["tot"], $bold);
$worksheet->write($line, 2, $totaux_autre["ava"], $bold);
$worksheet->write($line, 3, $totaux_autre["eng"], $bold);
$worksheet->write($line, 4, $totaux_autre["fac"], $bold);
$worksheet->write($line, 5, $totaux_autre["pay"], $bold);
$worksheet->write($line, 6, $totaux_autre["sol"], $bold);
}
//Final
$workbook->close();
header("Content-Type: application/x-msexcel; name=\"budget.xls\"");
header("Content-Disposition: inline; filename=\"budget.xls\"");
$fh = fopen($fname, "rb");
fpassthru($fh);
unlink($fname);
die;
}
<?php
# Demonstrates Spreadsheet::WriteExcel's named colors and the Excel
# color palette.
#
# reverse('©'), March 2002, John McNamara, jmcnamara@cpan.org
# PHP port by Johann Hanne, 2005-11-01
set_time_limit(10);
require_once "class.writeexcel_workbook.inc.php";
require_once "class.writeexcel_worksheet.inc.php";
$fname = tempnam("/tmp", "colors.xls");
$workbook = new writeexcel_workbook($fname);
# Some common formats
$center =& $workbook->addformat(array('align' => 'center'));
$heading =& $workbook->addformat(array('align' => 'center', 'bold' => 1));
# Try this to see the default Excel 5 palette
# $workbook->set_palette_xl5();
######################################################################
#
# Demonstrate the named colors.
#
$colors = array(
