Loop using PHPExcel

Question

Loop using PHPExcel

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Good I attach my doubt, in an image, since I believe that it will be better.

Greetings

EDIT:

The order of the Client's Actions will not always be like that, I mean It may be that the client Pedro Sell, then Buy, then Sell ... etc

And when you insert it in the excel it will only fill in the 1st Purchase with the date and time of the aforementioned purchase.

php phpexcel

asked by TaGy 07.09.2017 в 19:08

source

2 answers

function strstr (from the library string.h) made by me Accumulate notifications in the status bar [duplicated]

score 0 · Answer 1

To cycle through the loop horizontally, sort the data according to the format you need.

Considering that this is your Database answer:

$row = mysql_fetch_row($result);

Ideally, the database will filter it by Accion='Compra' , in any case this code will select only% of Accion='Compra' :

//los datos selecccionado se almacenan en "$rowLimpia"
$rowLimpia = array();
for($i=0;$i<count($row);$i++){
    if($row[$i]["Accion"]=="Compra"){
        array_push($rowLimpia,$row[$i]);
    }
}

Now to order Horizontally:

//Los datos ordenados estaran en "$data"
$data = array();
$datahijo = array();
for($i=0;$i<count($rowLimpia);$i++){
    //comparando "Id" de la fila actual con la anterior, si es diferente indica que se esta cambiando a los registro del siguente Cliente.
    if($rowLimpia[$i]["Id"]==@$rowLimpia[($i-1)]["Id"]){
        $datahijo[$contadorCompra."_Compra"] = $rowLimpia[$i]['Fecha'];
    }else{//Cuando es el siguiente Cliente...
        //... se agrega el Cliente anterior al array $data
        if(count($datahijo)>0){array_push($data,$datahijo);}
        //... se reinician variables
        $contadorCompra=1;              
        $datahijo = array();
        $datahijo["Cliente"] = $rowLimpia[$i]['Cliente'];
        $datahijo[$contadorCompra."_Compra"] = $rowLimpia[$i]['Fecha'];
    }       
    $contadorCompra++;
}
//se agrega el ultimo Cliente encontrado al array $data.
if(count($datahijo)>0){array_push($data,$datahijo);}
//probando
var_dump($data);

Then there are records with Data: Client, 1st Purchase, 2nd Purchase, ...

[![introducir la descripción de la imagen aquí][1]][1]

Ordered in this way you only have to send them to the excel directly:

$filaInicial=1;
$columnas = ["","A","B","C","D","E","F","G","H"];
$columnaInicial=2;//equivalente a "B" segun $columnas;
for($i=0;$i<count($data);$i++){
    //pintando Cliente
    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$filaInicial, $data[$i]["Cliente"]);
    for($j=1;$j<count($data[$i]);$j++){
        //pintando Compras
        $objPHPExcel->getActiveSheet()->SetCellValue($columnaInicial.$filaInicial, $data[$i][$j."_Cliente"]);
    }
    $filaInicial++;
}

score 0 · Answer 2

You have to cycle while you get records. When the customer id is different from the previous one, you add information to the first column and restart the index of the column where you should start to put the user's purchases.

$resource=$mysqli->query("select id, cliente,accion,fecha... where accion='compra'");
$id=0;
$linea=3;
while($row=$resource->fetch_array()){
    if($id!=$row[0]){
         $linea++; 
         $libro->getActiveSheet()->getCellByColumnAndRow(0, $linea)->setValue($row[1]);
         $columna=1;
    }
    $libro->getActiveSheet()->getCellByColumnAndRow($columna, $linea)->setValue($row[2]);
    $columna++;
}

I only did it for purchases because for Sales you do not mention what should be done.