Jump to content
Fórum Script Brasil
  • 0

tabela mysql para excel


shev
 Share

Question

Boa tarde,

Andei a pesquisar na net e não consegui encontrar (se calhar não pesquisei bem).

Existe alguma maneira de passar o conteúdo de uma tabela que está na base de dados para uma folha excel através de php?

Vi na internet um forma de passar um array em excel, mas esse código tem uma limitação de 35 linhas (já andei às voltas e não encontro como posso mudar, possivelmente é uma limitação interna...) e as minhas tabelas têm no máximo 1440 linhas (ya são muitas :P)

Se alguém me conseguisse ajudar ficava muito agradecido :)

Cumps

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

Eu fiz através do comando SELECT {relação de campos}

[iNTO OUTFILE 'nome_arquivo' opções_exportação]

FROM minhatabela.

Depois importei para o excel o arquivo txt gerado.

Leia o manual de sua ver~so do MySQL para saber mais detalhes desta característica.

Link to comment
Share on other sites

  • 0

Eu gostaria que aparece-se logo no ecrã para download/abrir o ficheiro xls ou xlsx.

Aproveito e deixo aqui o código do código que falei antes.

Tirei da net, a unica coisa que mudei foi que fiz ir buscar ao banco de dados os valores da tabela

<?php


/**
* Simple excel generating from PHP5
*
* @package Utilities
* @license http://www.opensource.org/licenses/mit-license.php
* @author Oliver Schwarz <[email protected]>
* @version 1.0
*/

/**
* Generating excel documents on-the-fly from PHP5
*
* Uses the excel XML-specification to generate a native
* XML document, readable/processable by excel.
*
* @package Utilities
* @subpackage Excel
* @author Oliver Schwarz <[email protected]>
* @version 1.1
*
* @todo Issue #4: Internet Explorer 7 does not work well with the given header
* @todo Add option to give out first line as header (bold text)
* @todo Add option to give out last line as footer (bold text)
* @todo Add option to write to file
*/
class Excel_XML
{

/**
* Header (of document)
* @var string
*/
private $header = "<?xml version=\"1.0\" encoding=\"%s\"?\>\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">";

/**
* Footer (of document)
* @var string
*/
private $footer = "</Workbook>";

/**
* Lines to output in the excel document
* @var array
*/
private $lines = array();

/**
* Used encoding
* @var string
*/
private $sEncoding;

/**
* Convert variable types
* @var boolean
*/
private $bConvertTypes;

/**
* Worksheet title
* @var string
*/
private $sWorksheetTitle;

/**
* Constructor
*
* The constructor allows the setting of some additional
* parameters so that the library may be configured to
* one's needs.
*
* On converting types:
* When set to true, the library tries to identify the type of
* the variable value and set the field specification for Excel
* accordingly. Be careful with article numbers or postcodes
* starting with a '0' (zero)!
*
* @param string $sEncoding Encoding to be used (defaults to UTF-8)
* @param boolean $bConvertTypes Convert variables to field specification
* @param string $sWorksheetTitle Title for the worksheet
*/
public function __construct($sEncoding = 'UTF-8', $bConvertTypes = false, $sWorksheetTitle = 'Table1')
{
$this->bConvertTypes = $bConvertTypes;
$this->setEncoding($sEncoding);
$this->setWorksheetTitle($sWorksheetTitle);
}

/**
* Set encoding
* @param string Encoding type to set
*/
public function setEncoding($sEncoding)
{
$this->sEncoding = $sEncoding;
}

/**
* Set worksheet title
*
* Strips out not allowed characters and trims the
* title to a maximum length of 31.
*
* @param string $title Title for worksheet
*/
public function setWorksheetTitle ($title)
{
$title = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
$title = substr ($title, 0, 500);
$this->sWorksheetTitle = $title;
}

/**
* Add row
*
* Adds a single row to the document. If set to true, self::bConvertTypes
* checks the type of variable and returns the specific field settings
* for the cell.
*
* @param array $array One-dimensional array with row content
*/
private function addRow ($array)
{
$cells = "";
foreach ($array as $k => $v):
$type = 'String';
if ($this->bConvertTypes === true && is_numeric($v)):
$type = 'Number';
endif;
$v = htmlentities($v, ENT_COMPAT, $this->sEncoding);
$cells .= "<Cell><Data ss:Type=\"$type\">" . $v . "</Data></Cell>\n";
endforeach;
$this->lines[] = "<Row>\n" . $cells . "</Row>\n";
}

/**
* Add an array to the document
* @param array 2-dimensional array
*/
public function addArray ($array)
{
foreach ($array as $k => $v)
$this->addRow ($v);
}


/**
* Generate the excel file
* @param string $filename Name of excel file to generate (...xls)
*/
public function generateXML ($filename = 'excel-export')
{
// correct/validate filename
$filename = preg_replace('/[^aA-zZ0-9\_\-]/', '', $filename);

// deliver header (as recommended in php manual)
header("Content-Type: application/vnd.ms-excel; charset=" . $this->sEncoding);
header("Content-Disposition: inline; filename=\"" . $filename . ".xls\"");

// print out document to the browser
// need to use stripslashes for the damn ">"
echo stripslashes (sprintf($this->header, $this->sEncoding));
echo "\n<Worksheet ss:Name=\"" . $this->sWorksheetTitle . "\">\n<Table>\n";
foreach ($this->lines as $line)
echo $line;

echo "</Table>\n</Worksheet>\n";
echo $this->footer;
}

}


// o meu código para gerar o array, indo buscar os valores ao banco de dados
// Se for buscar mais de 35 linhas ele dá problemas...

include "Meteorological Center/connect_meteorological_center.php";

$query = "SELECT * FROM a20120327 LIMIT 35";
$result = mysql_query($query, $db) or die("Could not complete database query");

$count = 1;

$data= array (array ('Time', 'Wind Direction', 'Avg Wind Speed'));

while ($row = mysql_fetch_array($result)) {

$data1= array (array( $row['measurement_time'], $row['wind_direction'], $row['avg_wind_speed']));

$data = array_merge($data, $data1);

}

// generate file (constructor parameters are optional)

$xls = new Excel_XML('UTF-8', false, 'My Test Sheet');
$xls->addArray($data);
$xls->generateXML('my-test');

?>[/codebox]

Se tiver mais de 35 linhas o excel não funciona bem, mostrando o array (como se o vissemos com um print_r) e no final algo deste genero:

<Row>

<Cell><Data ss:Type="String">00:33:00</Data></Cell>

<Cell><Data ss:Type="String">8</Data></Cell>

<Cell><Data ss:Type="String">4.8000</Data></Cell>

</Row>

Consegues-me ajudar?

Cumps

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



  • Forum Statistics

    • Total Topics
      151k
    • Total Posts
      649.1k
×
×
  • Create New...