How do I create a text file so when it is opened in Excel, rows are grouped together?

Question

I'm collecting some data via a Perl script. The data needs to be reviewed and processed by others using Excel. Currently, I'm writing the data out as a tab-delimited text file, and Excel can open this just fine.

There's a hierarchy to the data, however, and it would be easier for the reviewers to see a tree rather than a flat list. That is, rather than presenting the data in columns,

foo    foo1
foo    foo2
foo    foo3
bar    bar1
bar    bar2
...

present it as a click-to-expand tree:

foo
    foo1
    foo2
    foo3
bar
    bar1
    bar2
...

Excel's group function (found in 2007 under "Data > Outline > Group") is a good match for this presentation, being a bit simpler to operate than pivot tables.

What is the easiest way for us to go from this flat list of columns to this grouped list? Ideally, I could write out the data in a text form that Excel would apply the grouping automatically when it was imported. Alternatively, if there were a small number of steps the reviewer could apply after importing the data, like applying a macro or a template, that would be OK too.

Answer 1

1. Select all the rows, including the column headers, in the list you want to filter.

   ShowTip Click the top left cell of the range, and then drag to the bottom right cell.

2. On the Data menu, point to Filter, and then click Advanced Filter.
3. In the Advanced Filter dialog box, click Filter the list, in place.

4. Select the Unique records only check box, and then click OK.

   The filtered list is displayed and the duplicate rows are hidden.

5. On the Edit menu, click Office Clipboard.

   The Clipboard task pane is displayed.

6. Make sure the filtered list is still selected, and then click Copy Copy button.

   The filtered list is highlighted with bounding outlines and the selection appears as an item at the top of the Clipboard.

7. On the Data menu, point to Filter, and then click Show All.

   The original list is re-displayed.

8. Press the DELETE key.

   The original list is deleted.

9. In the Clipboard, click on the filtered list item.

   The filtered list appears in the same location as the original list.

Answer 2

Recent versions of Excel (2003 is what we use here) can use an XML format, xlsx. An xlsx file is a zip of an XML file. If you want to make a file that will open in Excel with the settings you want, try this: first create a template file with the grouping you want. Save it as xlsx. Unzip the file using your standard zip software. Take a look at what's inside. I haven't worked with grouping specifically, but all the commands in your spreadsheet will be there in xml form, you'll need to figure out where the grouping is set. Then it's a matter of making the appropriate changes to the xml and re-zipping. A bit of effort, but you can use this method to programmatically create files that are pre-grouped. There may be Perl libraries specifically geared towards this, I don't know.

Answer 3

A CSV file is also very easy to generate. OpenOffice lets you choose how to parse things, but excel excepts comma-delimited columns (without any kind of quote) and CRLF delimited rows.

A1,A2,A3
B1,B2,B3

etc.

Answer 4

Since you are already using perl, I suggest that you create the excel file directly in perl using the excellent CPAN module Spreadsheet::WriteExcel which has support for Excel outlines.

Works something like this:

     .
     .
     $worksheet->write('A2',  'foo');
     $worksheet->write('B3',  'foo1');
     $worksheet->write('B4',  'foo2');
     $worksheet->write('B5',  'foo3');
     $worksheet->set_row(2,  undef, undef, 0, 1, 1);
     $worksheet->set_row(3,  undef, undef, 0, 2);
     $worksheet->set_row(4,  undef, undef, 0, 2);
     $worksheet->set_row(5,  undef, undef, 0, 2);
     .
     .

Answer 5

Is there anyway, we can generate xlsx files using Perl? If yes, can you provide me an example. I have csv file. I have to convert it into xlsx using Perl. It would be great if someone knows it