PHP Spreadsheet_Excel_Writer类代码示例

 function Pman_Core_SimpleExcel($data, $cfg)
 {
     // print_r($cfg);exit;
     require_once 'Spreadsheet/Excel/Writer.php';
     // Creating a workbook
     $outfile2 = $this->tempName('xls');
     // var_dump($outfile2);
     $workbook = new Spreadsheet_Excel_Writer($outfile2);
     //$workbook = new Spreadsheet_Excel_Writer();
     $workbook->setVersion(8);
     // sending HTTP headers
     $this->workbook = $workbook;
     $formats = array();
     $cfg['formats'] = isset($cfg['formats']) ? $cfg['formats'] : array();
     foreach ($cfg['formats'] as $f => $fcfg) {
         $this->formats[$f] =& $workbook->addFormat();
         foreach ((array) $fcfg as $k => $v) {
             $this->formats[$f]->{'set' . $k}($v);
         }
     }
     if (!empty($cfg['workbook'])) {
         $this->buildPage(array(), $data, $cfg);
     } elseif (!empty($cfg['workbooks'])) {
         foreach ($cfg['workbooks'] as $i => $wcfg) {
             $this->buildPage(array(), $data[$i], $wcfg);
         }
     }
     // if workbooks == false - > the user can call buildpage..
     if (!empty($cfg['leave_open'])) {
         $this->outfile2 = $outfile2;
         return;
     }
     $workbook->close();
     $this->outfile2 = $outfile2;
 }

 private function export_xls($fileName = null, $data = null, $fields = array())
 {
     $fileName = 'fengjie';
     $data = array(array(1, 1, 1), array(1, 1, 1));
     $this->layout = 'ajax';
     $this->autoLayout = false;
     //        return;
     require_once 'Spreadsheet/Excel/Writer.php';
     $workbook = new Spreadsheet_Excel_Writer();
     $workbook->send($fileName . '.xls');
     $workbook->setVersion(8);
     $worksheet =& $workbook->addWorksheet('My first worksheet');
     $worksheet->setInputEncoding('UTF-8');
     $j = 0;
     foreach ($fields as $field) {
         $worksheet->write(0, $j, $field);
     }
     $i = 1;
     foreach ($data as $row) {
         $j = 0;
         foreach ($row as $value) {
             $worksheet->write($i, $j, $value);
             $j++;
         }
         $i++;
     }
     $workbook->close();
 }

 function exportList($params = array())
 {
     require_once 'Spreadsheet/Excel/Writer.php';
     $records = $this->getList($params);
     $workbook = new Spreadsheet_Excel_Writer();
     $workbook->send('export.xls');
     $worksheet =& $workbook->addWorksheet('Report');
     $worksheet->write(0, 0, 'User');
     $worksheet->write(0, 1, 'Date');
     $worksheet->write(0, 2, 'Time');
     $worksheet->write(0, 3, 'ID');
     $worksheet->write(0, 4, 'Panel');
     $row = 0;
     foreach ($records as $record) {
         $row++;
         $date = $record->rdate;
         $year = substr($date, 0, 4);
         $month = substr($date, 4, 2);
         $day = substr($date, 6, 2);
         $time = str_pad($record->rtime, 6, '0', STR_PAD_LEFT);
         $hour = substr($time, 0, 2);
         $min = substr($time, 2, 2);
         $sec = substr($time, 4, 2);
         $worksheet->write($row, 0, $record->LN . ', ' . $record->FN);
         $worksheet->write($row, 1, "{$month}/{$day}/{$year}");
         $worksheet->write($row, 2, "{$hour}:{$min}:{$sec}");
         $worksheet->write($row, 3, $record->CardNumber);
         $worksheet->write($row, 4, $record->panel);
     }
     $workbook->close();
 }

 public function WriteToXLS($filename)
 {
     $xls = new Spreadsheet_Excel_Writer($filename);
     $sheet = $xls->addWorksheet('auto2you.ru');
     $sheet->setColumn(0, 0, 130);
     $sheet->setColumn(1, 1, 20);
     $sheet->setColumn(2, 2, 15);
     $sheet->setColumn(3, 3, 30);
     $titleFormat = $xls->addFormat();
     $titleFormat->setFontFamily('Helvetica');
     $titleFormat->setBold();
     $titleFormat->setAlign('center');
     $itemFormat = $xls->addFormat();
     $titles = array('Имя', 'Город', 'Телефон', 'E-mail');
     foreach ($titles as $ind => $title) {
         $sheet->write(0, $ind, $title, $titleFormat);
     }
     $ind = 1;
     foreach ($this->data as $item) {
         $sheet->write($ind, 0, $item['name'], $itemFormat);
         $sheet->write($ind, 1, $item['city'], $itemFormat);
         $sheet->write($ind, 2, $item['phone'], $itemFormat);
         $sheet->write($ind, 3, $item['email'], $itemFormat);
         $ind++;
     }
     $xls->close();
 }

 function createXLSAdv(&$rows, $row_labels = null, $row_headers = null, $title = null)
 {
     ob_clean();
     ob_start();
     require_once JPATH_COMPONENT_SITE . DS . 'libraries' . DS . 'pear' . DS . 'PEAR.php';
     require_once JPATH_COMPONENT_SITE . DS . 'libraries' . DS . 'Excel' . DS . 'Writer.php';
     // Creating a workbook
     $workbook = new Spreadsheet_Excel_Writer();
     $worksheet = $workbook->addWorksheet($title);
     $BIFF = new Spreadsheet_Excel_Writer_BIFFwriter();
     $format = new Spreadsheet_Excel_Writer_Format($BIFF);
     $format->setBold(1);
     $format->setAlign('center');
     for ($k = 0; $k < count($row_labels); $k++) {
         $worksheet->write(0, $k, $row_headers[$row_labels[$k]], $format);
     }
     for ($i = 0; $i < count($rows); $i++) {
         for ($k = 0; $k < count($row_labels); $k++) {
             $worksheet->write($i + 1, $k, $rows[$i][$row_labels[$k]]);
         }
     }
     $workbook->close();
     $attachment = ob_get_contents();
     @ob_end_clean();
     echo $attachment;
 }

 /**
  * Excel保存
  */
 function saveToExcel($data, $filename)
 {
     Configure::write('debug', 0);
     App::import('Vendor', 'include_path');
     App::import('Vendor', 'Spreadsheet_Excel_Writer', array('file' => 'Spreadsheet' . DS . 'Excel' . DS . 'Writer.php'));
     $workbook = new Spreadsheet_Excel_Writer();
     $workbook->send($filename);
     $worksheet =& $workbook->addWorksheet('story');
     $format =& $workbook->addFormat();
     $format->setSize(9);
     $header_format =& $workbook->addFormat();
     $header_format->setSize(9);
     $header_format->setFgColor('gray');
     // ヘッダー
     $header = array('Story Id', 'Story', 'Description', 'Story Points', sprintf(__('Count of %s', true), __('Task', true)), sprintf(__('Sum of %s', true), __('Remaining Hours', true)), 'Businessvalue', 'Sprint', 'Priority', 'Resolution', 'Team', 'Created');
     $row = 0;
     $col = 0;
     for ($i = 0; $i < count($header); $i++) {
         $worksheet->write($row, $col, $this->sjis(__($header[$i], true)), $header_format);
         $col++;
     }
     // データ
     $row++;
     foreach ($data as $item) {
         $col = 0;
         $worksheet->writeNumber($row, $col, $this->sjis($item["Story"]["id"]), $format);
         $col++;
         $worksheet->write($row, $col, $this->sjis($item["Story"]["name"]), $format);
         $col++;
         $worksheet->write($row, $col, $this->sjis($item["Story"]["description"]), $format);
         $col++;
         $worksheet->write($row, $col, $this->sjis($item["Story"]["storypoints"]), $format);
         $col++;
         $worksheet->write($row, $col, $this->sjis($item["Story"]["task_count"]), $format);
         $col++;
         $worksheet->write($row, $col, $this->sjis($item["Story"]["total_hours"]), $format);
         $col++;
         $worksheet->write($row, $col, $this->sjis($item["Story"]["businessvalue"]), $format);
         $col++;
         $worksheet->write($row, $col, $this->sjis($item["Sprint"]["name"]), $format);
         $col++;
         $worksheet->write($row, $col, $this->sjis($item["Priority"]["name"]), $format);
         $col++;
         $worksheet->write($row, $col, $this->sjis(@$item["Resolution"]["name"]), $format);
         $col++;
         $worksheet->write($row, $col, $this->sjis(@$item["Team"]["name"]), $format);
         $col++;
         $worksheet->write($row, $col, date('Y-m-d', strtotime($item["Story"]["created"])), $format);
         $col++;
         $row++;
     }
     // 横幅設定
     $width = array(4, 50, 50, 10, 10, 10, 10, 10, 20, 10, 10);
     for ($i = 0; $i < count($width); $i++) {
         $worksheet->setColumn($i, $i, $width[$i]);
     }
     $workbook->close();
     exit;
 }

 public static function output($table, $name = '')
 {
     error_reporting(0);
     if (!is_object($table) && !is_array($table)) {
         exit;
     }
     if ($name == '') {
         $name = 'output' . Invocation::next();
     }
     if (!preg_match('/^.*\\.xls$/i', $name)) {
         $name .= '.xls';
     }
     $fileName = PHPExt::getTempDir() . '/' . $name;
     $workbook = new Spreadsheet_Excel_Writer($fileName);
     $worksheet =& $workbook->addWorksheet(basename($name));
     $rowIdx = 0;
     if ($table instanceof DBTable || is_array($table)) {
         foreach ($table as $row) {
             if ($rowIdx == 0) {
                 foreach (array_keys($row) as $col => $heading) {
                     $worksheet->write($rowIdx, $col, $heading);
                 }
                 $rowIdx++;
             }
             foreach (array_values($row) as $col => $val) {
                 $worksheet->write($rowIdx, $col, $val);
             }
             $rowIdx++;
         }
     } else {
         if ($table instanceof PDOStatement) {
             while ($row = $table->fetch(DB::FETCH_ASSOC)) {
                 if ($rowIdx == 0) {
                     foreach (array_keys($row) as $col => $heading) {
                         $worksheet->write($rowIdx, $col, $heading);
                     }
                     $rowIdx++;
                 }
                 foreach (array_values($row) as $col => $val) {
                     $worksheet->write($rowIdx, $col, $val);
                 }
                 $rowIdx++;
             }
         }
     }
     $workbook->close();
     $workbook->send($name);
     $fp = fopen($fileName, 'rb');
     fpassthru($fp);
     fclose($fp);
     unlink($fileName);
     exit;
 }

 function init()
 {
     $workbook = new Spreadsheet_Excel_Writer();
     $worksheet =& $workbook->addWorksheet('sheet1');
     $worksheet->write(0, 0, iconv('UTF-8', 'TIS-620', 'ต้น'));
     $worksheet->write(0, 1, 'Age');
     $worksheet->write(1, 0, 'John Smith');
     $worksheet->write(1, 1, 30);
     $worksheet->write(2, 0, 'Johann Schmidt');
     $worksheet->write(2, 1, 31);
     $worksheet->write(3, 0, 'Juan Herrera');
     $worksheet->write(3, 1, 32);
     $this->_workbook = $workbook;
 }

 /**
  * создает файл в формате XLS (Microsoft Excel) на основе переданных данных
  *
  * @param array $data содержимое ячеек таблицы
  * @param string $name имя отчета
  * @return string содержимое файла
  */
 public function xls(&$data, $name)
 {
     $fileName = date("Y-m-d_H-i-s");
     $workBook = new Spreadsheet_Excel_Writer();
     $workBook->setTempDir(BASEPATH . 'cache/');
     $workBook->setVersion(8);
     $workBook->send(__("report") . "_{$fileName}.xls");
     $formatBold =& $workBook->addFormat();
     $formatBold->setBold();
     $formatTitle =& $workBook->addFormat();
     $formatTitle->setBold();
     $formatTitle->setColor('black');
     $formatTitle->setPattern(1);
     $formatTitle->setFgColor('gray');
     $formatTitle->setAlign('merge');
     $workSheet =& $workBook->addWorksheet('Report');
     $workSheet->setInputEncoding('utf-8');
     $row_count = 0;
     foreach ($data as $row) {
         $col_count = 0;
         foreach ($row as $column) {
             $workSheet->write($row_count, $col_count, $column, $row_count ? $formatBold : $formatTitle);
             $col_count++;
         }
         $row_count++;
     }
     //      ширина столбцов
     //      $workSheet->setColumn(0, 0, 30);
     //      $workSheet->setColumn(2, 2, 30);
     $workBook->close();
 }

 public function group_get_members($pGroup)
 {
     if (!$pGroup instanceof koala_group) {
         throw new Exception("parameter is not an instance of koala_group", E_PARAM);
     }
     if (!$pGroup->is_admin(lms_steam::get_current_user())) {
         throw new Exception("no admin rights.operation canceled.");
     }
     $cache = get_cache_function($pGroup->get_id(), CACHE_LIFETIME_STATIC);
     switch (get_class($pGroup)) {
         case "koala_group_course":
             $group_name = $pGroup->get_course_id() . " - " . $pGroup;
             $members = $cache->call("lms_steam::group_get_members", $pGroup->steam_group_learners->get_id());
             break;
         default:
             $members = $cache->call("lms_steam::group_get_members", $pGroup->get_id());
             break;
     }
     // INITIALIZATION
     $course_id = $pLmsGroupCourse->get_course_id();
     $course_name = $pLmsGroupCourse->get_course_dsc_short();
     $semester = $pLmsGroupCourse->get_semester();
     $excel = new Spreadsheet_Excel_Writer();
     $excel->send($course_id . "_" . $semester->get_name());
     $sheet =& $excel->addWorksheet(gettext("participants"));
     // WRITE EXCEL SHEET
     $sheet->writeString(0, 0, $course_id . " - " . $course_name());
     $sheet->writeSting(1, 0, $semester->get_name());
     $sheet->writeString(3, 0, gettext("student id"));
     $sheet->writeString(3, 1, gettext("forename"));
     $sheet->writeString(3, 2, gettext("surname"));
     $no_members = count($members);
     if ($no_members > 0) {
         $row = 5;
         for ($i = $start; $i < $end; $i++) {
             $member = $members[$i];
             $sheet->writeString($row, 1, $member["USER_FIRSTNAME"]);
             $sheet->writeString($row, 2, $member["USER_FULLNAME"]);
             $sheet->writeString($row, 3, $member["USER_EMAIL"]);
             $sheet->writeString($row, 4, $member["USER_PROFILE_FACULTY"]);
             $row++;
         }
     }
     $excel->close();
 }

 protected function assertSameAsInFixture($filename, Spreadsheet_Excel_Writer $workbook)
 {
     $this->assertEmpty($workbook->_filename, "Testing with fixtures works only for standard output");
     // we have to fix timestamp for fixtures to work
     $workbook->_timestamp = 1000000000;
     // somewhere in 2001
     ob_start();
     $workbook->close();
     $data = ob_get_clean();
     $fullPath = self::FIXTURES_PATH . $filename;
     if ($this->shouldUpdateFixtures()) {
         file_put_contents($fullPath, $data);
     }
     if (!is_file($fullPath)) {
         $this->fail("Fixture {$filename} not found");
     }
     // TODO: should we save data for future analysis?
     //file_put_contents("{$fullPath}.work", $data);
     $this->assertEquals(file_get_contents($fullPath), $data, "Output differs for {$filename}");
 }

 function post($fname)
 {
     $ml = (int) ini_get('suhosin.post.max_value_length');
     if (empty($_POST['_json'])) {
         header("HTTP/1.0 400 Internal Server Error");
         die($ml ? "Suhosin Patch enabled - try and disable it!!!" : 'no JSON sent');
     }
     if (empty($_POST['_json'])) {
         header("HTTP/1.0 400 Internal Server Error");
         die("Missing json attribute");
     }
     $json = json_decode($_POST['_json']);
     require_once 'Spreadsheet/Excel/Writer.php';
     // Creating a workbook
     $outfile2 = $this->tempName('xls');
     // var_dump($outfile2);
     $workbook = new Spreadsheet_Excel_Writer($outfile2);
     //$workbook = new Spreadsheet_Excel_Writer();
     $workbook->setVersion(8);
     // sending HTTP headers
     $worksheet = $workbook->addWorksheet("Sheet 1");
     if (is_a($worksheet, 'PEAR_Error')) {
         die($worksheet->toString());
     }
     //print_R($worksheet);
     $worksheet->setInputEncoding('UTF-8');
     for ($r = 0; $r < count($json); $r++) {
         $row = $json[$r];
         for ($c = 0; $c < count($row); $c++) {
             $worksheet->write($r, $c, $row[$c]);
         }
     }
     $workbook->close();
     require_once 'File/Convert.php';
     $fc = new File_Convert($outfile2, "application/vnd.ms-excel");
     $fn = $fc->convert("application/vnd.ms-excel");
     $fc->serve('attachment', 'excel-' . date('Y-m-d-H-i-s') . '.xls');
     // can fix IE Mess
     unlink($outfile2);
 }

/**
 * Genera un reporte en Excel
 *
 * @param array $result
 * @param array $sumArray
 * @param string $title
 * @param array $weightArray
 * @param array $headerArray
 */
function xls($result, $sumArray, $title, $weightArray, $headerArray)
{
    error_reporting(0);
    $file = md5(uniqid());
    $config = Config::read('config');
    $active_app = Router::get_application();
    $workbook = new Spreadsheet_Excel_Writer("public/temp/{$file}.xls");
    $worksheet =& $workbook->addWorksheet();
    $titulo_verdana =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 20));
    $titulo_verdana2 =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 18));
    $workbook->setCustomColor(12, 0xf2, 0xf2, 0xf2);
    $column_title =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 12, 'fgcolor' => 12, 'border' => 1, 'bordercolor' => 'black', "halign" => 'center'));
    $column =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 11, 'border' => 1, 'bordercolor' => 'black'));
    $column_centered =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 11, 'border' => 1, 'bordercolor' => 'black', "halign" => 'center'));
    $worksheet->write(0, 0, strtoupper($config->{$active_app}->name), $titulo_verdana);
    $worksheet->write(1, 0, "REPORTE DE " . strtoupper($title), $titulo_verdana2);
    $worksheet->write(2, 0, "FECHA " . date("Y-m-d"), $titulo_verdana2);
    for ($i = 0; $i <= count($headerArray) - 1; $i++) {
        $worksheet->setColumn($i, $i, $weightArray[$i]);
        $worksheet->write(4, $i, $headerArray[$i], $column_title);
    }
    $l = 5;
    foreach ($result as $row) {
        for ($i = 0; $i <= count($row) - 1; $i++) {
            if (!is_numeric($row[$i])) {
                $worksheet->writeString($l, $i, $row[$i], $column);
            } else {
                $worksheet->writeString($l, $i, $row[$i], $column_centered);
            }
        }
        $l++;
    }
    $workbook->close();
    error_reporting(E_ALL ^ E_STRICT);
    if (isset($raw_output)) {
        print "<script type='text/javascript'> window.open('" . KUMBIA_PATH . "temp/" . $file . ".xls', null);  </script>";
    } else {
        Generator::forms_print("<script type='text/javascript'> window.open('" . KUMBIA_PATH . "temp/" . $file . ".xls', null);  </script>");
    }
}

 function renderXls()
 {
     $workbook = new Spreadsheet_Excel_Writer();
     // sending HTTP headers
     $workbook->send($this->getKursus()->getKursusNavn());
     // Creating a worksheet
     $worksheet = $workbook->addWorksheet('Deltagere');
     $format_bold = $workbook->addFormat();
     $format_bold->setBold();
     $format_bold->setSize(8);
     $format_italic = $workbook->addFormat();
     $format_italic->setItalic();
     $format_italic->setSize(8);
     $format =& $workbook->addFormat();
     $format->setSize(8);
     $i = 0;
     $worksheet->write($i, 0, 'Vejle Idrætshøjskole: ' . $this->getKursus()->getKursusNavn(), $format_bold);
     $i = 2;
     foreach ($this->getKursus()->getTilmeldinger() as $deltager) {
         $worksheet->write($i, 0, $deltager->get('navn'), $style);
         $worksheet->write($i, 1, $deltager->get('cpr'), $style);
         $i++;
     }
     $worksheet->hideGridLines();
     // Let's send the file
     $data = $workbook->close();
     $response = new k_HttpResponse(200, $data);
     $response->setEncoding(NULL);
     $response->setContentType("application/excel");
     /*
     $response->setHeader("Content-Length", strlen($data));
     $response->setHeader("Content-Disposition", "attachment;filename=\"\"");
     $response->setHeader("Content-Transfer-Encoding", "binary");
     $response->setHeader("Cache-Control", "Public");
     $response->setHeader("Pragma", "public");
     */
     throw $response;
 }

//.........这里部分代码省略.........
        // set header and footer fonts
        $pdf->setHeaderFont(Array($pdfdefaultfont, '', PDF_FONT_SIZE_MAIN));
        $pdf->setFooterFont(Array($pdfdefaultfont, '', PDF_FONT_SIZE_DATA));

        // set default header data
        // the path looks awkward - did not find a better solution to set the image path?
        $pdf->SetHeaderData("statistics.png", 10, $statlang->gT("Quick statistics",'unescaped') , $statlang->gT("Survey")." ".$surveyid." '".FlattenText($surveyInfo['surveyls_title'],true,'UTF-8')."'");


        // set default monospaced font
        $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

        //set margins
        $pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
        $pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
        $pdf->SetFooterMargin(PDF_MARGIN_FOOTER);

        //set auto page breaks
        $pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

        //set image scale factor
        $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

        //set some language-dependent strings
        $pdf->setLanguageArray($l);
    }
    if($outputType=='xls')
    {
        /**
         * Initiate the Spreadsheet_Excel_Writer
         */
        include_once(dirname(__FILE__)."/classes/pear/Spreadsheet/Excel/Writer.php");
        if($pdfOutput=='F')
        $workbook = new Spreadsheet_Excel_Writer($tempdir.'/statistic-survey'.$surveyid.'.xls');
        else
        $workbook = new Spreadsheet_Excel_Writer();

        $workbook->setVersion(8);
        // Inform the module that our data will arrive as UTF-8.
        // Set the temporary directory to avoid PHP error messages due to open_basedir restrictions and calls to tempnam("", ...)
        if (!empty($tempdir)) {
            $workbook->setTempDir($tempdir);
        }
        if ($pdfOutput!='F')
        $workbook->send('statistic-survey'.$surveyid.'.xls');

        // Creating the first worksheet
        $sheet =& $workbook->addWorksheet(utf8_decode('results-survey'.$surveyid));
        $sheet->setInputEncoding('utf-8');
        $sheet->setColumn(0,20,20);
        $separator="~|";
    }
    /**
     * Start generating
     */

    // creates array of post variable names
    for (reset($_POST); $key=key($_POST); next($_POST)) { $postvars[]=$key;}

    $aQuestionMap=array();
    foreach ($fieldmap as $field)
    {
        if(isset($field['qid']) && $field['qid']!='')
        $aQuestionMap[]=$field['sid'].'X'.$field['gid'].'X'.$field['qid'];
    }