php - phpexcel安排php内的数据

码：

<?php require_once('Connections/valesilveira.php'); ?>
<?php
mysql_query("SET NAMES 'utf8'");
mysql_query('SET character_set_connection=utf8');
mysql_query('SET character_set_client=utf8');
mysql_query('SET character_set_results=utf8');
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$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;
}
}

$colname_dados = "-1";
if (isset($_GET['id_proposta'])) {
$colname_dados = $_GET['id_proposta'];
}
mysql_select_db($database_valesilveira, $valesilveira);
$query_dados = sprintf("SELECT posicao, dados.desc, quantidade, unitario FROM propostas, clientes, dados WHERE propostas.id_proposta = dados.id_proposta AND propostas.id_cliente = clientes.id_cliente AND propostas.id_proposta = %s ORDER BY posicao ASC", GetSQLValueString($colname_dados, "int"));
$dados = mysql_query($query_dados, $valesilveira) or die(mysql_error());
$row_dados = mysql_fetch_assoc($dados);
$totalRows_dados = mysql_num_rows($dados);

$colname_propostas = "-1";
if (isset($_GET['id_proposta'])) {
$colname_propostas = $_GET['id_proposta'];
}
mysql_select_db($database_valesilveira, $valesilveira);
$query_propostas = sprintf("SELECT id_proposta, data FROM propostas WHERE id_proposta = %s", GetSQLValueString($colname_propostas, "int"));
$propostas = mysql_query($query_propostas, $valesilveira) or die(mysql_error());
$row_propostas = mysql_fetch_assoc($propostas);
$totalRows_propostas = mysql_num_rows($propostas);

$data = $row_propostas['data'];
setlocale (LC_ALL, "pt_BR", "ptb");
$data_formatada = strftime("%d de %B de %Y", strtotime($data));
?>
<?php


// require the PHPExcel file
require 'Classes/PHPExcel.php';

// simple query

$headings = array('posicao','desc','quantidade','unitario','iliquido','total');

if ($result = mysql_query($query_dados) or die(mysql_error())) {
    // Create a new PHPExcel object
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->getActiveSheet()->setTitle('List of Data');

    $rowNumber = 1;
    $col = 'A';
    foreach($headings as $heading) {
       $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$heading);
       $col++;
    }

    // Loop through the result set
    $rowNumber = 2;
    while ($row = mysql_fetch_row($result)) {
       $col = 'A';
       foreach($row as $cell) {
          $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
          $col++;
       }
       $rowNumber++;
    }


    // Freeze pane so that the heading line won't scroll
    $objPHPExcel->getActiveSheet()->freezePane('A2');

    // Save as an Excel BIFF (xls) file
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

   header('Content-Type: application/vnd.ms-excel');
   header('Content-Disposition: attachment;filename="userList.xls"');
   header('Cache-Control: max-age=0');

   $objWriter->save('php://output');
   exit();
}
echo 'a problem has occurred... no data retrieved from the database';
?>
<?php
mysql_free_result($dados);

mysql_free_result($propostas);

mysql_free_result($data);
?>

好的，我到目前为止已经完成了代码，但是这仅从数据库中获取数据并将其循环，我想为最后2个列进行一些安排。

我得到posicao，desc，quantidade，unitario的数据，但对于iliquido和总计，我想进行一些计算。

对于iliquido，我想做一个unitario * quantidade，并在列上显示所有结果。

对于总数，我想用iliquido值的总和作为单行，例如= sum（2ndrow：lastrow）。

最佳答案

您只需插入将作为普通Excel公式的公式作为PHPExcel单元格的值即可。因此，只需输入：

=A$row*C$row

在该列中，您希望获得该行的unitario * quantidade结果。总和也一样。

例如，在执行此循环时：

while ($row = mysql_fetch_row($result)) {
   $col = 'A';
   foreach($row as $cell) {
      $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
      $col++;
   }
   //add this line to get the unitario*quantidade result
   //in the last column
   //here I'm assuming that quantitade and unitario are placed
   //in column A and C in your excel, but this may be wrong, so
   //replace them with the proper values
   $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,"=A$row*C$row");
   $rowNumber++;
}

关于php - phpexcel安排php内的数据，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28499256/