1

I have a one-to-many like array.

Structure example:

 [ [ InvoiceID, [ InvoiceEntry1_label, InvoiceEntry2_label ], [ InvoiceEntry1_value, InvoiceEntry2_value ], Total ], [ InvoiceID, [ InvoiceEntry1_label, InvoiceEntry2_label ], [ InvoiceEntry1_value, InvoiceEntry2_value ], Total ], and so on ]

How can I group rows using PHPExcel, such that after sorting in Excel, rows which belong to an Invoice will stick together?

Do not confuse with Excel outlining and grouping functionality, for which rows won't stick together after sort.

I already know how to use PHPExcel. A simple example or a documentation reference will do as an answer. I couldn't find one myself.

Improve this question

2 Answers 2

Reset to default
1

I'm not totally certain how I'd approach this in MS Excel itself.

I'm sure there probably is a solution. In the past I have "hidden" the duplicated cell values such as invoice number using conditional formatting, setting the foreground/text colour to the background colour if the invoiceId value matches the invoiceId value of the previous row... when sorted, a previously "hidden" invoiceId value may then become "unhidden" and previously "unhidden" invoiceId values may become "hidden". It's a kludgy solution, but I don't know a better way of doing it in MS Excel itself, without the benefit of pivot tables.

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

2 Comments

I was thinking about hidding the same repeated value. However I am aiming at something cleaner (no VBA and no risk for error or malformed styling). Pivot tables would spread on too many sheets.
After studying some more, I agree with you, only Pivot tables are a viable solution :(.
1

In Excel this can only be done using Pivot Tables like this:

  • worksheet with source data, with all rows expanded to the "many" part (for example InvoiceID would be copied for each row);
  • pivot table
    • in the pivot fields list, all columns are placed in the "Row Labels" section, with no columns in the other 3 sections (Report Filter, Values, Column Labels); optionally, only fields from the "many" part (of the one-to-many relation) can be moved to the Values section;
    • right clicked each field, field settings, set "Subtotals" to "none"
    • right clicked each field, field settings, set "Layout" to "Show item labels in tabular form"
    • moved all fields from the "many" part to be last in order;

The above enabled sticking of rows after sorting and also filtering, with a really nice looking one-to-many representation (where the many part is splitting the current rows into child rows).

PHPExcel does not yet support Pivot Tables at all ("Codeplex: Implement pivot tables"), so the above Excel Pivot Table cannot be created, edited or read using PHPExcel.

Improve this answer

2 Comments

I know, Support for pivot tables is on the "to do" list (and fairly high up in priority), but time (as always) is the constraint... hopefully before the end of the year, but it'll be too late in this instance
I just noticed you are the main developer on PHPExcel. Thank you for the best Excel format reader/writer out there, and thank you for making it open source :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.