php - 将数据库导出到php中excel文件中的不同工作表

Question

我想将联系人从数据库导出到一个 Excel 文件中，以便每个类别都是一个单独的工作表。我一直在研究这个[示例][1]，它工作得很好，除了我不知道如何管理工作表。这会将我的所有数据放入一张表中。

这是我的代码:

<?php
  $xls_filename = 'export_'.date('Y-m-d').'.xls'; // Define Excel (.xls) file name

  header("Content-Type: application/xls");
  header("Content-Disposition: attachment; filename=$xls_filename");
  header("Pragma: no-cache");
  header("Expires: 0");

  include_once "connect.php";
  $sep = "\t"; // tabbed character
  $query1 = "select * from category";
  $result1 = mysqli_query($conn, $query1);
  if (!$result) {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    $check = false;
  }
  else {
    while($category = mysqli_fetch_object($result1)) {
      $query2 = "select * from contact inner join contact_category on contact.email = contact_category.contact_email
         where contact_category.category_id ='$category->id'";
      $result2 = mysqli_query($conn, $query2);
      if (!$result2) {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
        $check = false;
      }
      else {
        echo $category->name;
        //$category_name should be my new sheet's name
        $schema_insert .= "First name".$sep;
        $schema_insert .= "Last name".$sep;
        $schema_insert .= "\n";
        while($contact= mysqli_fetch_object($result2)) {
          $schema_insert .= $contact->first_name.$sep;
          $schema_insert .= $contact->last_name.$sep;
          $schema_insert .= ""."\n";
        }
      }
    }
    print(trim($schema_insert));
    print "\n";
  }
?>

Answer 1

最终使用Excel Writer (XML) for PHP 。这是代码:

  include('excel/ExcelWriterXML.php');
  include_once "connect.php";

  $xml = new ExcelWriterXML;
  $xml->docAuthor('Author');

  $format1 = $xml->addStyle('StyleHeader');
  $format1->fontBold();
  $format1->fontColor("darkslateblue");
  $format1->alignVertical('Center');
  $format1->alignHorizontal('Center');
  $format1->fontSize('12');
  $format1->alignWraptext();

  $format2 =  $xml->addStyle('StyleEmail');
  $format2->fontBold();
  $format2->fontColor("darkred");

  $format3 =  $xml->addStyle('StyleRows');
  $format3->alignWraptext();

  $query1 = "select * from category";
  $result1 = mysqli_query($conn, $query1);
  if (!$result) {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    $check = false;
  }
  else {
    while($category = mysqli_fetch_object($result1)) {
      $query2 = "select * from contact inner join contact_category on contact.email = contact_category.contact_email
         where contact_category.category_id ='$category->id'";
      $result2 = mysqli_query($conn, $query2);
      if (!$result2) {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
        $check = false;
      }
      else {
        $sheet = $xml->addSheet($category->name);
        $sheet->writeString(1, 1, 'Title', 'StyleHeader');
        $sheet->writeString(1, 2, 'First name', 'StyleHeader');
        $sheet->writeString(1, 3, 'Last name', 'StyleHeader');
        $sheet->writeString(1, 4, 'Email', 'StyleHeader');
        $sheet->writeString(1, 5, 'Position', 'StyleHeader');
        $sheet->writeString(1, 6, 'Institution', 'StyleHeader');
        $sheet->writeString(1, 7, 'Phone', 'StyleHeader');
        $sheet->writeString(1, 8, 'Address', 'StyleHeader');
        $sheet->writeString(1, 9, 'City', 'StyleHeader');
        $sheet->writeString(1, 10, 'Zip code', 'StyleHeader');
        $i = 2;
        while($contact= mysqli_fetch_object($result2)) {
          $sheet->writeString($i, 1, $contact->title, "StyleRows");
          $sheet->writeString($i, 2, $contact->first_name, "StyleRows");
          $sheet->writeString($i, 3, $contact->last_name, "StyleEmail");
          $sheet->writeString($i, 4, $contact->email, "StyleRows");
          $sheet->writeString($i, 5, $contact->position, "StyleRows");
          $sheet->writeString($i, 6, $contact->institution, "StyleRows");
          $sheet->writeString($i, 7, $contact->phone, "StyleRows");
          $sheet->writeString($i, 8, $contact->address, "StyleRows");
          $sheet->writeString($i, 9, $contact->city, "StyleRows");
          $sheet->writeString($i, 10, $contact->zipcode, "StyleRows");
          $i = $i + 1;
        }
      }
    }
  }

  $xml->sendHeaders();
  $xml->writeData();
?>