MYSQL查询后使用phpexcel从excel中删除字段 [英] Removing fields from excel using phpexcel after MYSQL query

问题描述

我正在使用PHPExcel从MYSQL数据库导出数据并将其导入到excel文件中.

我有一个看起来像这样的数据库(这也是我使用PHPExcel生成它时excel文件的当前样子):

======================================================
|           Question                  |   Answer     |
=========+===========+===============================|
| Do you listen to music?             |    YES       |            
|----------------------------------------------------|
| Who is your favorite music artists? | Justin Beiber| 
|----------------------------------------------------|
| <p> Select an Answer<p>             |              | 
|----------------------------------------------------|
|Are you a Male or female             |      M       | 
|----------------------------------------------------|

我希望我的excel文件看起来像这样:

======================================================
|           Question                  |   Answer     |
=========+===========+===============================|
| Do you listen to music?             |    YES       |            
|----------------------------------------------------|
| Who is your favorite music artists? | Justin Beiber| 
|----------------------------------------------------|
|Are you a Male or female             |      M       | 
|----------------------------------------------------|

我正在使用以下代码:

$objPHPExcel = new PHPExcel();

$col = 1; 
while($row_data = mysql_fetch_assoc($result)) {
    $row = 1;
    if ($col == 1) {
        $row_headings = array_keys($row_data);
        foreach($row_headings as $value) {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
            $row++;
        }
        $row = 1;
        $col++;
    }
    foreach($row_data as $value) {
      if (!strstr('<p>', $value)){
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);

    $row++;
         }
    }
    $col++;
}

解决方案

一种选择是在执行SQL查询时排除<p> Select an Answer<p>，您可以选择列和行，然后从选择中选择DELETE WHERE question = "<p> Select an Answer<p> "

或者，如果列Anwser的值为null或""，而Question的值为<p> Select an Answer<p>，那么您也可以说:

if($value !== null)
{
  // Output...
} 

例如:

$objPHPExcel = new PHPExcel();

$col = 1; 
while($row_data = mysql_fetch_assoc($result)) {
    $row = 1;
    if ($col == 1) {
        $row_headings = array_keys($row_data);
        foreach($row_headings as $value) {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
            $row++;
        }
        $row = 1;
        $col++;
    }
    foreach($row_data as $value) {
        if($value !== null || $value !== '')
        {
         $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
         $row++;
        }
    }
    $col++;
}

或者您可以简单地删除包含<p> Select an Answer<p>的行，除非需要它们.

I'm using PHPExcel to export data from a MYSQL database and import it into an excel file.

I've got a database that looks like this (this is also what my excel file currently looks like when I generate it using PHPExcel):

======================================================
|           Question                  |   Answer     |
=========+===========+===============================|
| Do you listen to music?             |    YES       |            
|----------------------------------------------------|
| Who is your favorite music artists? | Justin Beiber| 
|----------------------------------------------------|
| <p> Select an Answer<p>             |              | 
|----------------------------------------------------|
|Are you a Male or female             |      M       | 
|----------------------------------------------------|

I want my excel file to look like this:

======================================================
|           Question                  |   Answer     |
=========+===========+===============================|
| Do you listen to music?             |    YES       |            
|----------------------------------------------------|
| Who is your favorite music artists? | Justin Beiber| 
|----------------------------------------------------|
|Are you a Male or female             |      M       | 
|----------------------------------------------------|

I'm using this code:

$objPHPExcel = new PHPExcel();

$col = 1; 
while($row_data = mysql_fetch_assoc($result)) {
    $row = 1;
    if ($col == 1) {
        $row_headings = array_keys($row_data);
        foreach($row_headings as $value) {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
            $row++;
        }
        $row = 1;
        $col++;
    }
    foreach($row_data as $value) {
      if (!strstr('<p>', $value)){
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);

    $row++;
         }
    }
    $col++;
}

解决方案

One option would be to exclude the <p> Select an Answer<p> when doing a SQL query you would SELECT your columns and rows then from your selection you could DELETE WHERE question = "<p> Select an Answer<p> "

Or if column Anwser value is null or "" when Question value is <p> Select an Answer<p>, then you can could also say:

if($value !== null)
{
  // Output...
} 

In example:

$objPHPExcel = new PHPExcel();

$col = 1; 
while($row_data = mysql_fetch_assoc($result)) {
    $row = 1;
    if ($col == 1) {
        $row_headings = array_keys($row_data);
        foreach($row_headings as $value) {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
            $row++;
        }
        $row = 1;
        $col++;
    }
    foreach($row_data as $value) {
        if($value !== null || $value !== '')
        {
         $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
         $row++;
        }
    }
    $col++;
}

Or you could simply delete rows that contain <p> Select an Answer<p> unless you need them.

这篇关于MYSQL查询后使用phpexcel从excel中删除字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！