drupal 生成excel的两种方法

1.利用xml dom 对象，将数据写入一个xml文件，然后利用这个xml文件来生成excel，这样文件读写只需要执行一次，因此效率较高。

/**
 * @param $file
 * @param $header excel，或者说是table的表头
 * @param $rows excel中的数据
 * @return string 
 */
function create_xml($file, $header, $rows) {
    $dom = new DOMDocument('1.0', 'utf-8');
    $path = file_directory_path() . "/" . $file . ".xml";
    $workbook = $dom->createElement('Workbook');
    $workbook->setAttribute('xmlns', 'urn:schemas-microsoft-com:office:spreadsheet');
    $workbook->setAttribute('xmlns:o', 'urn:schemas-microsoft-com:office:office');
    $workbook->setAttribute('xmlns:x', 'urn:schemas-microsoft-com:office:excel');
    $workbook->setAttribute('xmlns:ss', 'urn:schemas-microsoft-com:office:spreadsheet');
    $workbook->setAttribute('xmlns:html', 'http://www.w3.org/TR/REC-html40');
    $dom->appendChild($workbook);
    $documentproperties = $dom->createElement('DocumentProperties');
    $documentproperties->setAttribute('xmlns', 'urn:schemas-microsoft-com:office:office');
    $workbook->appendChild($documentproperties);
    create_text_node($dom, $documentproperties, 'Author', 'xxx');
    create_text_node($dom, $documentproperties, 'LastAuthor', 'xxx');
    create_text_node($dom, $documentproperties, 'Created', date('Y-m-d H:i:s', time()));
    create_text_node($dom, $documentproperties, 'Company', 'xxx');
    create_text_node($dom, $documentproperties, 'Version', '14.0');
    $OfficeDocumentSettings = $dom->createElement('OfficeDocumentSettings');
    $OfficeDocumentSettings->setAttribute('xmlns', 'urn:schemas-microsoft-com:office:office');
    $workbook->appendChild($OfficeDocumentSettings);
    $AllowPNG = $dom->createElement('AllowPNG');
    $OfficeDocumentSettings->appendChild($AllowPNG);
    $ExcelWorkbook = $dom->createElement('ExcelWorkbook');
    $ExcelWorkbook->setAttribute('xmlns', 'urn:schemas-microsoft-com:office:excel');
    $workbook->appendChild($ExcelWorkbook);
    create_text_node($dom, $ExcelWorkbook, 'WindowHeight', '12900');
    create_text_node($dom, $ExcelWorkbook, 'WindowWidth', '25600');
    create_text_node($dom, $ExcelWorkbook, 'WindowTopX', '0');
    create_text_node($dom, $ExcelWorkbook, 'WindowTopY', '0');
    create_text_node($dom, $ExcelWorkbook, 'ProtectStructure', 'False');
    create_text_node($dom, $ExcelWorkbook, 'ProtectWindows', 'False');
    $Styles = $dom->createElement('Styles');
    $workbook->appendChild($Styles);
    $Style = $dom->createElement('Style');
    $Style->setAttribute('ss:ID', 'Default');
    $Style->setAttribute('ss:Name', 'Normal');
    $Styles->appendChild($Style);
    $Alignment = $dom->createElement('Alignment');
    $Alignment->setAttribute('ss:Vertical', 'Bottom');
    $Style->appendChild($Alignment);
    $Borders = $dom->createElement('Borders');
    $Style->appendChild($Borders);
    $Font = $dom->createElement('Font');
    $Font->setAttribute('ss:FontName', '宋体');
    $Font->setAttribute('x:CharSet', '134');
    $Font->setAttribute('x:Family', 'Swiss');
    $Font->setAttribute('ss:Size', '12');
    $Font->setAttribute('ss:Color', '#000000');
    $Style->appendChild($Font);
    $Interior = $dom->createElement('Interior');
    $Style->appendChild($Interior);
    $NumberFormat = $dom->createElement('NumberFormat');
    $Style->appendChild($NumberFormat);
    $Protection = $dom->createElement('Protection');
    $Style->appendChild($Protection);
    $Worksheet = $dom->createElement('Worksheet');
    $Worksheet->setAttribute('ss:Name', 'sheet1');
    $workbook->appendChild($Worksheet);
    $Table = $dom->createElement('Table');
    $Worksheet->appendChild($Table);
    $Row = $dom->createElement('Row');
    $Table->appendChild($Row);

    //make header 
    if (is_array($header) && !empty($header)) {
        foreach ($header as $h) {
            $Cell = $dom->createElement('Cell');
            $Row->appendChild($Cell);
            create_text_node($dom, $Cell, 'Data', $h, array("ss:Type" => "String"));
        }
    }
    if (is_array($rows) && !empty($rows)) {
        foreach ($rows as $row) {
            $Row = $dom->createElement('Row');
            $Table->appendChild($Row);
            if (is_array($row) && !empty($row)) {
                foreach ($row as $val) {
                    $Cell = $dom->createElement('Cell');
                    $Row->appendChild($Cell);
                    if (is_array($val)) {
                        $text_val = $val['value'];
                        $text_type = $val['type'];
                    } else {
                        $text_val = $val;
                        $text_type = 'String';
                    }
                    create_text_node($dom, $Cell, 'Data', $text_val, array("ss:Type" => $text_type));
                }
            }
        }
    }

    $WorksheetOptions = $dom->createElement('WorksheetOptions');
    $WorksheetOptions->setAttribute("xmlns", "urn:schemas-microsoft-com:office:excel");
    $Worksheet->appendChild($WorksheetOptions);


    $output = $dom->saveXML();
    //$dom->save($path);
    return $output;
}

function create_text_node(&$dom, &$parent, $node, $value, $attr = array()) {
    $node = $dom->createElement($node);
    if (is_array($attr) && !empty($attr)) {
        foreach ($attr as $k => $v) {
            $node->setAttribute($k, $v);
        }
    }
    $node_value = $dom->createTextNode($value);
    $node->appendChild($node_value);
    $parent->appendChild($node);
}

2.利用php的读写文件的方法，相当于每一行的数据都需要执行打开写入的操作，因此效率会降低。

<?php
/**
 *excel导出类
 */
class excel_exporter {
    function add_row(&$file_handle, $data) {
    	if (!empty($data)) {
        foreach ($data as $key => $value) {
            $data[$key] = '"'. str_replace('"', '""', str_replace("n", ' ', $value)) .'"';
        }
    	}
        $row = implode("t", $data) . "n";
        if (function_exists('mb_convert_encoding')) {
            $row = mb_convert_encoding($row, 'UTF-16LE', 'UTF-8');
        }
        @fwrite($file_handle, $row);
    }

    function set_headers($filename) {
        drupal_set_header("Content-Type: application/force-download");
        drupal_set_header("Content-Type: application/x-msexcel");
        drupal_set_header("Content-Disposition: attachment; filename=$filename.xls");
    }

    function bof(&$file_handle) {
        $output = '';
        if (function_exists('mb_convert_encoding')) {
            $output = chr(255) . chr(254);
        }
        @fwrite($file_handle, $output);
    }

    function eof(&$file_handle) {
    }
}

/**
 * @param $header excel,table头
 * @param $rows excel数据
 */
function excel_download($header,$rows){
  $exporter = new excel_exporter();
  $file_name = tempnam(variable_get('file_directory_temp', file_directory_temp()), time());
  $handle = @fopen($file_name, 'w');
  $exporter->bof($handle);

  $exporter->add_row($handle, $header);
  foreach ($rows as $value) {
    $exporter->add_row($handle, $value);
  }

  // Add the closing bytes.
  $exporter->eof($handle);
  // Close the file.
  @fclose($handle);
  $export_name = t('Download_excel') . '_' . date('Ymd');
  $exporter->set_headers($export_name);
  @readfile($file_name); // The @ makes it silent.
  @unlink($file_name); // Clean up, the @ makes it silent.
}
?>