[PHP] EXPORT MySql to XLS

[Kugas]

boas...

eu consegui por a ferramenta kuase a funcar lol mas há um probema... eu exporto pa excel ai tudo bem mas kd abro o excel da-me um erro e kd faço ok exporta-me so uma linha enkto eu mandei exportar 5 por exemplo...

aki esta o codigo:

Code Select Expand

addworksheet(\\'Preços\\');





# Frozen panes

# $worksheet1->freeze_panes(0, 0); # 1 row



# Un-frozen panes. The divisions must be specified in terms of row and column

# dimensions. The default row height is 12.75 and the default column width

# is 8.43

#

#######################################################################

#

# Set up some formatting and text to highlight the panes

#



# Create a format for the column headings

$header =& $workbook->addformat();

$header->set_bold();

$header->set_font("Courier New");

$header->set_align(\\'center\\');

$header->set_align(\\'vcenter\\');

# Create a format for the column headings

$headerm =& $workbook->addformat();

$headerm->set_bold();

$headerm->set_font("Courier New");

$headerm->set_align(\\'center\\');

$headerm->set_fg_color(\\'yellow\\');

$headerm->set_align(\\'vcenter\\');

$headerm->set_pattern();

$headerm->set_merge();



# Create a "vertical justification" format

$format1 =& $workbook->addformat();

$format1->set_align(\\'vjustify\\');



# Create a "text wrap" format

$format2 =& $workbook->addformat();

$format2->set_text_wrap();

$format2->set_fg_color(\\'red\\');

$format2->set_align(\\'center\\');

#######

# Create a "text wrap" format

$format3 =& $workbook->addformat();

$format3->set_text_wrap();

$format3->set_fg_color(\\'gray\\');

$format3->set_align(\\'center\\');

#######

# Create a "text wrap" format

$peso =& $workbook->addformat();

$peso->set_text_wrap();

$peso->set_fg_color(\\'green\\');

$peso->set_align(\\'center\\');

#######



$header =& $workbook->addformat();

$header->set_color(\\'black\\');

$header->set_align(\\'center\\');

$header->set_align(\\'vcenter\\');

$header->set_pattern();

$header->set_fg_color(\\'gray\\');



$center =& $workbook->addformat();

$center->set_align(\\'center\\');

$header->set_merge();

$center->set_merge();



#######################################################################

/*

Make a top line on your excel sheet at line 1 (starting at 0).

The first number is the row number and the second number is the column, both are start at \\'0\\'

*/

$worksheet1->set_column(\\'A:I\\', 16); #o 16 é a largura da coluna

$worksheet1->set_row(0, 20); # o 20 é a altura da primeira linha

$worksheet1->set_selection(\\'A1\\');



####HEADER####

$worksheet1->write(0, 0, \\'Preços\\', $headerm);

$worksheet1->write_blank(0, 1, $headerm);

$worksheet1->write_blank(0, 2, $headerm);

$worksheet1->write_blank(0, 3, $headerm);

$worksheet1->write_blank(0, 4, $headerm);

$worksheet1->write_blank(0, 5, $headerm);

$worksheet1->write_blank(0, 6, $headerm);

$worksheet1->write_blank(0, 7, $headerm);

$worksheet1->write_blank(0, 8, $headerm);

###########



#######BODY##########

#1Ã,º Valor  Corresponde ao numero da linha

#2Ã,º Valor Correponde ao numero da coluna

#linha 3

$worksheet1->write(2, 0, \\'Ponto Venda\\', $header);

$worksheet1->write(2, 1, \\'Promotor\\', $header);

$worksheet1->write(2, 2, \\'Seguemento\\', $header);

$worksheet1->write(2, 3, \\'Familia\\', $header);

$worksheet1->write(2, 4, \\'Produto\\', $header);

$worksheet1->write(2, 5, \\'PVP Recomendado\\', $header);

$worksheet1->write(2, 6, \\'PVP Praticado\\', $header);

$worksheet1->write(2, 7, \\'Nota\\', $header);

$worksheet1->write(2, 8, \\'Data Recolha\\', $header);



// Put data records from mysql by while loop.

while ($rowexcel=mysql_fetch_array($stmtexcel))

{



#linha 4

$worksheet1->write(3, 0, $rowexcel[\\'nomeCliente\\'].\\'-\\'.$rowexcel[\\'localidade\\']);

$worksheet1->write(3, 1, $rowexcel[\\'merch\\']);

$worksheet1->write(3, 2, $rowexcel[\\'segm\\']);

$worksheet1->write(3, 3, $rowexcel[\\'fami\\']);

$worksheet1->write(3, 4, $rowexcel[\\'nomeProd\\']);

$worksheet1->write(3, 5, $rowexcel[\\'pvpr\\']);

$worksheet1->write(3, 6, $rowexcel[\\'pvp\\'], $row[\\'pvp\\']>=$row[\\'pvpr\\']?$peso:$format2);

$worksheet1->write(3, 7, $rowexcel[\\'notas\\']);

$worksheet1->write(3, 8, $rowexcel[\\'datareg\\']);

#linha 4

}

###########



$workbook->close();

$fh=fopen($fname, "rb");

fpassthru($fh);

unlink($fname);

?>
e o erro é este:



cump\\'s

[Teotónio Ricardo]

Já tentas-te com outra versão diferente do Excell?

[Kugas]

ja descobri lol

tinha que fazer o seguinte:

Code Select Expand


$linha=3;

While()

{

$worksheet1->write($linha, 0, $rowexcel[\\'nomeCliente\\'].\\'-\\'.$rowexcel[\\'localidade\\']);

bla bla

bla bla

....

$linha++;

}
nao... nao era preciso fazer com outra versao

Obrigado na mesma... abraço

[Teotónio Ricardo]

Já agora, o phpmyadmin permite-te fazer isso automáticamente, penso eu de que.

[Kugas]

eu uso o phpmyadmin... como se faz???

[Teotónio Ricardo]

Selecionas a bd, vais à tab "Export" ou "Exportar" e selecionas a opção "dados CSV para MS Excel" e clicas em Executar.