Hi, I'm having a small problem generating an excel file using the PHPExcel class. I make a query in the database and get a string with an amount x of chars, and I enter this value into an excel template. But when the string exceeds 43 chars I need to make the excel spreadsheet merge two lines or more so that the text does not appear cut; The process of merging the cells according to the number of chars is working, but when the text is inserted into the cell, there are breaks of lines and extra spaces that should not exist, which leaves the text messy. I would like to know how I can insert the text in the merged cells in the same way as it is in the database, without the additional line breaks and spaces.
<?php
//configurações do banco
ini_set('max_execution_time', 300);
include_once("../../function/conexao.php");
include_once '../../Classes/PHPExcel/IOFactory.php';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("../../Templates/PedidoCompra.xlsx");
// Create new PHPExcel object
//$objPHPExcel = new PHPExcel();
// Set document properties
if(isset($_POST['PDC'])){
$Dados = $_POST['PDC'];
//atualização dos dados do pedido
$data1 = explode("/",$Dados[10]);
$Dados[10] = substr($data1[2],0,4)."/$data1[1]/$data1[0]";
if(empty($Dados[16])){
$Dados[16]="0.00";
}
if(empty($Dados[9])){
$Dados[9]="0.00";
}
if(empty($Dados[16])){
$Dados[16]="0.00";
}
if(empty($Dados[17])){
$Dados[17]="0.00";
}
//if($_POST['btPDC']=="ATUALIZAR"){
$sql_up = "update financeiro_pedido_compra set
pgto = '$Dados[12]',
contato='$Dados[3]',
data='$Dados[10]',
desconto='$Dados[16]',
IPI='$Dados[9]',
FRETE='$Dados[17]',
status='GERADO',
data_modif=now(),
usuario='$_SESSION[Usuario]' where pedidopk = $Dados[1]";
mysql_query($sql_up);
//echo $sql_up;
if ($error = mysql_errno()) die("<script>alert(\"1 - Erro UP, informe ao atualizar Administrador\");history.back();</script>");
//}
$sql = "select nome,endereco1,num_end1,bairro1,cep1,cidade1,uf1,cgc,insc_est,concat(ddd1,'-',num1) as tel,cond_pgfk from financeiro_cliente where cgc='".$Dados[0]."'";
$DadosCliente = mysql_fetch_assoc(mysql_query($sql,$conexao));
// SELECIONAR OS DADOS PARA PLANILHA
$query = mysql_query($sql,$conexao);
// INICIAMOS A CRIAÇÃO DA TABELA
$TotIPI = ($Dados[9]/100)*$Dados[8];
$TotIPI = number_format($TotIPI, 2, ',', ' ');
$Total = $Dados[8] + $TotIPI;
$Dados[11] = strtoupper($Dados[11]);
$Dados[6] = strtoupper($Dados[6]);
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue("C3", "$DadosCliente[nome]")
->setCellValue("C4", "$DadosCliente[endereco1], N°: $DadosCliente[num_end1]")
->setCellValue("C5", "$DadosCliente[bairro1]")
->setCellValue("C6", "$DadosCliente[cep1]")
->setCellValue("I4", "$DadosCliente[cidade1]")
->setCellValue("I5", "$DadosCliente[uf1]")
->setCellValue("I6", "$DadosCliente[cgc]")
->setCellValue("I7", "$DadosCliente[insc_est]")
->setCellValue("I8", "$DadosCliente[tel]")
->setCellValue("C10", "$Dados[1]") //pcn
->setCellValue("F10", "$Dados[2]")//data emissao
->setCellValue("I10", "$Dados[12]")
->setCellValue("C7", "$Dados[3]");//ordem
$select=mysql_query("select item,concat(substr(mpfk,3,2),codigo),descricao,qtd,un,vlrun,vlrtot from financeiro_pedido_compra_item where pedidofk = '$Dados[1]' order by item") ;
$row =14;
$Total=0;
$data1 = explode("/",$Dados[10]);
$Dados[10] = substr($data1[2],0,4)."/$data1[1]/$data1[0]";
while($Itens = mysql_fetch_array($select)){
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue("A$row", "$Itens[0]")//codigo
->setCellValue("C$row", "$Itens[1]")//codigo
->setCellValue("E$row", "$Itens[2]]")//descricao
->setCellValue("J$row", "$Itens[3]")//qtd
->setCellValue("K$row", "$Itens[4]")//un
->setCellValue("L$row", "$Itens[5]")//Val Un
->setCellValue("N$row", "$Itens[6]")//Total
->setCellValue("O$row", "$Dados[9]")//ipi
->setCellValue("P$row", "$Itens[3]")//qtd
->setCellValue("Q$row", "$Dados[10]");//data entrega
$objPHPExcel->getActiveSheet()->mergeCells("C$row:D$row");
$objPHPExcel->getActiveSheet()->mergeCells("L$row:M$row");
//Verificação de quantidade de chars e mescla de linhas
$Char = strlen($Itens[2]);
if($Char>43){
$IntDiv=(int)($Char/43);
$row2=$row+$IntDiv;
$objPHPExcel->getActiveSheet()->mergeCells("E$row:I$row2");
$row+=$IntDiv+1;
}else{
$objPHPExcel->getActiveSheet()->mergeCells("E$row:I$row");
$row++;
}
//$objPHPExcel->getActiveSheet()->getStyle("E$row")->getAlignment()->setWrapText(false);
$Total+=$Itens[6];
}
//}/*
$TotIPI = $Total * ($Dados[9]/100);
$TotalGeral = $Total+$TotIPI+$Dados[17];
$TotalGeral=$TotalGeral-$Dados[16];
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue("L38", "$Total")//codigo
->setCellValue("P38", "$TotIPI")
->setCellValue("Q38", $TotalGeral)
->setCellValue("N38", "$Dados[17]");//codigo
$objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(12);
$objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(12);
$objPHPExcel->getActiveSheet()->getRowDimension('5')->setRowHeight(12);
$objPHPExcel->getActiveSheet()->getRowDimension('6')->setRowHeight(12);
$objPHPExcel->getActiveSheet()->getRowDimension('7')->setRowHeight(12);
$objPHPExcel->getActiveSheet()->getRowDimension('8')->setRowHeight(12);
$arquivo = 'PedidoCompra'.date("Ymd")."_ $Dados[1]";
// Criamos uma tabela HTML com o formato da planilha
}
$objPHPExcel->getActiveSheet()->setTitle('Custo');
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.openxmlformats-officedocument.SpreadsheetML.Sheet');
header("Content-Disposition: attachment;filename=\"$arquivo.xlsx\"");
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
? >