Appnitro Software Forums » MachForm

Excel Formatting: Any way to have certain fields/cells export vertically?

(7 posts)
  • Started 5 months ago by Brainwrap
  • Latest reply from Brainwrap

  1. Brainwrap
    Member

    OK, I know I worded that very confusingly, but let me explain the issue:

    I have a form for teachers to enter their name, the schools' name, and up to 16 student names for an awards program.

    Naturally, the exported csv/excel file displays the columns as such:

    Entry 1: TEACHER A / SCHOOL A / STUDENT 1A / STUDENT 2A / STUDENT 3A
    Entry 2: TEACHER B / SCHOOL B / STUDENT 1B / STUDENT 2B / STUDENT 3B

    The problem is that the client needs it to display the results like so:

    TEACHER A / SCHOOL A / STUDENT 1A
    TEACHER A / SCHOOL A / STUDENT 2A
    TEACHER A / SCHOOL A / STUDENT 3A
    TEACHER B / SCHOOL B / STUDENT 1B
    TEACHER B / SCHOOL B / STUDENT 2B
    TEACHER B / SCHOOL B / STUDENT 3B

    Is there any way anyone can think of to do this, short of limiting the form to a single student and making the teacher fill the whole thing out again for every additional student?

    I'm assuming that some convoluted Excel Macro or PivotTable solution would do the trick, but I know almost nothing about either of those and am hoping there's some way of resolving this within MachForm.

    Any suggestions would be greatly appreciated.

    Posted 5 months ago #

  2. Brainwrap
    Member

    OK, it looks like I'd have to make some changes to the export_entries.php file, presumably somewhere down in the "print data" area...but I have no idea how to word it:

    if($type == 'xls'){
			//print column header
			if(!$column_label_has_printed){
				$i=0;
				foreach ($column_label as $label){
					$worksheet->write(0, $i, $label,$format_bold);
					$i++;
				}
				$column_label_has_printed = true;
			}

			//print column data

			foreach ($form_data as $row_data){
				$col_num = 0;
				foreach ($row_data as $data){
					//echo "rownum -1: ".($row_num - 1)."\n";
					//echo "colnum: ".$col_num."\n";
					if(empty($file_url[$row_num-1][$col_num])){
						$worksheet->write($row_num, $col_num, $data);
					}else{
						$worksheet->writeUrl($row_num,$col_num,$file_url[$row_num-1][$col_num],$data);
					}
					$col_num++;
				}

			}
		}elseif ($type == 'csv'){
			if(!$column_label_has_printed){
				fputcsv($out, $column_label);
				$column_label_has_printed = true;
			}

			foreach ($form_data as $row_data){
        		fputcsv($out, $row_data);
        	}
		}

		$row_num++;
		unset($form_data);
        unset($file_url);
	}
    Posted 5 months ago #

  3. Brainwrap
    Member

    Anyone? Sorry to be pushy, but I have kind of a rush situation here...any assistance would be greatly appreciated...

    For that matter, if it turns out that something like this *can't* be done, I'd appreciate it if I could be told that definitively so I can move on to option B...

    Thanks in advance,

    --Charles

    Posted 5 months ago #

  4. yuniar
    MachForm Founder

    Hi Charles,

    Sorry for delayed response on this! We actually have been thinking about this and trying to figure out an easy solution.

    Unfortunately, we can't figure out a simple solution for this.
    Since this would require pretty much new custom code, more than modifying the above code.

    I'm sorry if I can't be more helpful on this.

    An idea I had in mind is to write a script which parse the CSV file and then reconstruct the data again according your need above.

    Posted 5 months ago #

  5. Brainwrap
    Member

    Yuniar--

    Thanks for getting back to me.

    One thing which may be helpful to know is that I'm *only* using the "short name" (First / Last) field type for the student names. That is, the "teacher name" is extended (Prefix / First / Last / Suffix), and no other "short names" are listed elsewhere on any other forms.

    I figured this might be helpful because perhaps a rule could be set up that says "IF (any short_name field) = POPULATED, go to the next row, populate it with a duplicate of every field before it, then paste the following (short_name field) into the last column" and then repeat the process. Or something like that.

    If it's helpful, here's the actual form used: http://www.thescholarsprogram.com/machform/embed.php?id=5

    As you can see, there's actually some additional fields that aren't relevant, and there's actually 16 total student names.

    One solution I tried already was to go with a single text box for the student names instead of individual fields; the teacher would enter 1 name per line. This results in the first name appearing in the proper place, and the second, third, etc. names showing up on the 2nd, 3rd, 4th, etc rows in the CSV file. However, all but the first name populate the first column instead of the last one, and both the first & last names appear in a single cell instead of being separated into 2 cells for sorting.

    Is there any way of forcing a hidden "new entry" field in after each instance of a "short name" field (which would treat the entry as up to 16 entries instead of one)?

    Thanks again. While I'm waiting to hear from you, I'll start pursuing other options.

    Posted 5 months ago #

  6. Brainwrap
    Member

    One other thing I should note: I may be willing to pay (within reason) for some custom coding if that would make it feasible to resolve this.

    Posted 5 months ago #

  7. Brainwrap
    Member

    Yuniar--

    One other point, which may (?) make it easier to write a CSV parsing/reconstructing script:

    It's OK if the script caused the other fields (Teacher Name, School Name, School Address, etc) to be repeated 15 more times (for a total of 16), even if there are fewer than 16 student names per entry.

    In other words, it's OK if the exported CSV or Excel file includes numerous lines without *any* student names listed.

    So, if there were only 3 student names entered and the script output looked something like the following...

    Teacher A / Email / Grade / School / Address / Student 1A First / Student 1A Last
    Teacher A / Email / Grade / School / Address / Student 2A First / Student 2A Last
    Teacher A / Email / Grade / School / Address / Student 3A First / Student 3A Last
    Teacher A / Email / Grade / School / Address / ______ / _______
    Teacher A / Email / Grade / School / Address / ______ / _______
    Teacher A / Email / Grade / School / Address / ______ / _______
    Teacher B / Email / Grade / School / Address / Student 1B First / Student 1B Last
    Teacher B / Email / Grade / School / Address / Student 2B First / Student 2B Last
    Teacher B / Email / Grade / School / Address / ______ / _______
    Teacher B / Email / Grade / School / Address / ______ / _______
    (...etc)

    ...that would be fine, as we could easily just do a quick sort in Excel to just shove all the blank rows to the end and delete them.

    Don't know if this helps, but thought I'd mention it.

    Posted 5 months ago #

RSS feed for this topic

Reply

You must log in to post.