Removing fields from excel using phpexcel after MYSQL query

Question

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++;
}

marc_s · Accepted Answer · 2015-04-03 08:29:48Z

0

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

Or if column Anwser value is null or "" when Question value is  Select an Answer, 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  Select an Answer unless you need them.

edited Apr 3, 2015 at 8:29

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 6, 2012 at 2:09

Sterling Duchess

2,15016 gold badges54 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Merica Over a year ago

The problem is that there are multiple fields in the db that contain other html instruction statements like Fill out survey so I couldn't just take out the Select an Answer field.. Also, the db cannot be altered at this point. Is there a way I could take out fields that start with html code such as- if($value !== ) {write to excel file}

Sterling Duchess Over a year ago

Have you tried my example on your code should exclude any row's from your database that have Anwser column with and empty value or null value. Otherwise you can allways just say if(!strstr('', $value)) { ... } and it should not output any rows to excel that containt that tag. That is if all entries in your database are like that, that question contains some html and anwser contains nothing.

Merica Over a year ago

I've updated my code. I added the if(!strstr('', $value)) { ... } after the foreach, but I'm still getting the same output.

Merica Over a year ago

Could you tell me if there is there anything wrong with my code?

Sterling Duchess Over a year ago

Can you post entire php file

|

Collectives™ on Stack Overflow

Removing fields from excel using phpexcel after MYSQL query

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related