How can I create an advanced grid interface on Mathematica?

Question

Working on a CDF for report creation, I miss having a nice grid interface, where I would have basic operations like being able to sort a columns by clicking on the columns head (ascending and descending), or maybe group and ungroup data.

This is a great example of what can be done in Mathematica, but the code is not avaliable.

Answer 1

This code is not generalized. It has been written for a specific problem but you can take it and should be able to make it a more general function -- add flexibility (e.g. add grid options) or tailor it to your needs.

ClearAll[frozenPaneGrid];

Options[frozenPaneGrid] = {"RowLabelSort" -> False};

frozenPaneGrid[tl_, tr_, bl_, br_, OptionsPattern[]] := 
  DynamicModule[{scroller = 1, scrollx = 0, scrolly = 0, options, 
    columns = Length[tr], headings = tr, i = 1, 
    order = Range[Length[bl]], initialOrder = Ordering[Flatten[{bl}]]},

   options = {
     Background -> {None, {{White, GrayLevel[0.93]}}},
     BaseStyle -> Directive[FontFamily -> "Helvetica", 11],
     Frame -> False,
     FrameStyle -> Directive[Thin, GrayLevel[0.75]]};

   Dynamic[

    Deploy@Grid[{
      {Pane[
        Grid[
         {{EventHandler[
            MouseAppearance[tl, 
             Framed[Style["Left Click Sort\nRight Click Reverse Sort",
                9], Background -> White]], {{"MouseClicked", 
               1} :> (If[TrueQ[OptionValue["RowLabelSort"]], 
                order = initialOrder, 
                order = Range[Length[bl]]]), {"MouseClicked", 
               2} :> (If[TrueQ[OptionValue["RowLabelSort"]], 
                order = Reverse@initialOrder, 
                order = Reverse@Range[Length[bl]]])}]}},
         Alignment -> {Left, Center},
         Dividers -> {{1 -> White, -1 -> LightGray}, {1 -> True, -1 ->
              True}},
         ItemSize -> {20, 1.75},
         Spacings -> {2, 0.5},
         options],
        {270, All},
        Alignment -> {Right, Top},
        ImageMargins -> 0],

       Pane[
        Grid[
         {Table[
           With[{j = j}, 
            EventHandler[
             MouseAppearance[headings[[j]], 
              Framed[Style[
                "Left Click Sort\nRight Click Reverse Sort", 9], 
               Background -> White]], {{"MouseClicked", 1} :> (i = j; 
                order = Ordering[br[[All, i]]]), {"MouseClicked", 
                2} :> (i = j; 
                order = Reverse@Ordering[br[[All, i]]])}]], {j, 
            columns}]},
         Alignment -> {Right, Center},
         Dividers -> {{-1 -> White}, {1 -> True, -1 -> True}},
         ItemSize -> {8, 1.75},
         Spacings -> {{2, {0.5}, 2}, 0.5},
         options],
        {655, All},
        Alignment -> {Left, Top},
        ImageMargins -> 0,
        ScrollPosition -> Dynamic[{scrollx, scroller}]]},
      {Pane[
        Grid[
         bl[[order]],
         Alignment -> {Left, Center},
         Dividers -> {{1 -> White, -1 -> LightGray}, None},
         ItemSize -> {20, 1.75},
         Spacings -> {2, 0.5},
         options],
        {270, 505},
        Alignment -> {Right, Top},
        AppearanceElements -> None,
        ImageMargins -> 0,
        ScrollPosition -> Dynamic[{scroller, scrolly}]],

       Pane[
        Grid[
         br[[order]],
         Alignment -> {Right, Center},
         Dividers -> {{-1 -> White}, None},
         ItemSize -> {8, 1.75},
         Spacings -> {{2, {0.5}, 2}, 0.5},
         options],
        {670, 520},
        Alignment -> {Left, Top},
        AppearanceElements -> None,
        ImageMargins -> 0,
        ScrollPosition -> Dynamic[{scrollx, scrolly}],
        Scrollbars -> {True, True}]
       }},
     Alignment -> {{Right, Left}, Top},
     Spacings -> {0, 0}],
    TrackedSymbols :> {order}]
   ];

If you start with a grid of data and sort/reverse sort how do you recover the initial order of the 1st column? My approach here is to introduce an option "RowLabelSort" which, when False, will sort the first column according to the initial ordering.

To make this Excel like -- similar to frozen panes -- I have joined 4 grids. To get the 4 grids joined and coordinated for scrolling I "link" the scroll position in each pane. On my system (at the time was 8.0.4 and mac 10.6.8) I had to use scroller = 1 to overcome some intermittent problems.

I think the rest of the code is pretty much self explanatory. Most of the code is grid option settings for appearance. Left click on a column header to sort. Right click to reverse sort. To make this explicit I have made a big mouse appearance instruction box. You can obviously replace that.

Because I have created this on a Mac, you may find that you have to tweak some of the sizes on Windows. See this for further discussion.

Usage:

frozenPaneGrid[top left cell, rest of top row, left hand column, main body of data, options]

Test data:

SeedRandom[123];
tmp = RandomInteger[{1, 100}, {27, 14}];

frozenPaneGrid["header", Table[y, {14}], 
 List /@ {"Ajax", "admixes", "Acrux", "Alex", "affix", "admixtures", 
   "Alexei", "affixed", "admixing", "ambidextrously", "admixed", 
   "admix", "affixing", "ambidextrous", "Alexandrians", "Alexandria", 
   "Anaxagoras", "Alexandrian", "annexation", "Alexandra", 
   "admixture", "Alexis", "Alexanders", "annex", "affixes", 
   "ambidexterity", "Alexander"}, tmp,
 "RowLabelSort" -> False]