5
$\begingroup$

I want to implement the option to import data from Excel and then the ability to trim this data in order to fit the needs.

The import from Excel: (source)

Button["import from Excel", Module[{file}, 
   file = SystemDialogInput["FileOpen", {NotebookDirectory[] <>"data\\", 
   {"Excel files" -> {"*.xls;*.xlsx;*.xlsm"}}}];
   If[file != "$Canceled", rawdata = Flatten[Import[file, "xls"], 1]]],
   Method -> "Queued"]

As an example rawdata should be:

rawdata={{"n", "t(n)", "t(n)/t(n-1)", "Ø(t(n)/t(n-1))"}, {20., 1.15, "", 1.30081}, 
{21., 1.54, 1.33913, ""}, {22., 2.43, 1.57792, ""}, {23., 3.29, 1.35391, ""}, 
{24., 4.26, 1.29483, ""}, {25., 5.43, 1.27465, ""}, {26., 7.18, 1.32228, ""}, 
{27., 9.2, 1.28134, ""}, {28., 12.04, 1.3087, ""}, {29., 15.68, 1.30233, ""}, 
{30., 20.34, 1.29719, ""}, {31., 26.11, 1.28368, ""}, {32., 33.09, 1.26733, ""}, 
{33., 42.67, 1.28951, ""}, {34., 53.56, 1.25521, ""}, {35., 68., 1.2696, ""}, 
{36., 84.51, 1.24279, ""}, {37., 106.95, 1.26553, ""}, {38., 136.85, 1.27957, ""}, 
{39., 172.71, 1.26204, ""}, {40., 215.64, 1.24857, ""}}

Now i imagined the possibility to transpose and trim this data with something like this:

Row[{Checkbox[Dynamic[transpose]], TextCell[" transpose"]}]

Dynamic[TableForm[data = If[transpose, Transpose[rawdata], rawdata]]]

Dynamic[TableForm[Join[{Prepend[ConstantArray[Checkbox[True], Length@data[[1]]], ""]},
Map[Prepend[#, Checkbox[True]] &, data]]]]

Of course i want to merge the 2 TableForms into one, but my attempt to do so has failed:

Dynamic[TableForm[Function[list, If[transpose, Transpose[list], list]][Join[
  {Prepend[ConstantArray[Checkbox[True], Length@data[[1]]], ""]}, 
  Map[Prepend[#, Checkbox[True]] &, data]]]]]

My goal:

The checkboxes near each row/column shall deselect that row/column in the data and grey it out in the TableForm for better clarity. After pressing a confirmation button the data is saved as selected.

My Problem:

Right now the Checkboxes do not contain a variable (because i was not able to automatically assign variable names with a command like StringJoin <>) and so i was not able to read the values of the checkboxes. And the merging of the two TableForms.

If anybody has an alternative solution i'm open for suggestions.

$\endgroup$
4
$\begingroup$

Download code

You can download a notebook with all the code in it by running the following code:

Import["http://goo.gl/NaH6rM"]["http://i.stack.imgur.com/sMWJ2.png"]

Code with comments

The first thing to do is to separate your headings from your data:

rawdata = {{"n", "t(n)", "t(n)/t(n-1)", "Ø(t(n)/t(n-1))"}, {20., 1.15,
     "", 1.30081}, {21., 1.54, 1.33913, ""}, {22., 2.43, 1.57792, 
    ""}, {23, 3.29, 1.35391, ""}, {24., 4.26, 1.29483, ""}, {25., 
    5.43, 1.27465, ""}, {26., 7.18, 1.32228, ""}, {27., 9.2, 1.28134, 
    ""}, {28., 12.04, 1.3087, ""}, {29., 15.68, 1.30233, ""}, {30., 
    20.34, 1.29719, ""}, {31., 26.11, 1.28368, ""}, {32., 33.09, 
    1.26733, ""}, {33., 42.67, 1.28951, ""}, {34., 53.56, 1.25521, 
    ""}, {35., 68., 1.2696, ""}, {36., 84.51, 1.24279, ""}, {37., 
    106.95, 1.26553, ""}, {38., 136.85, 1.27957, ""}, {39., 172.71, 
    1.26204, ""}, {40., 215.64, 1.24857, ""}};
headings = First@rawdata;
data = Rest@rawdata;

The functions that display the data table come next. They all take an argument called "stem" which I might as well have called "symbol". This is the symbol I use to store information about the table. If you pick "el" as the symbol as I've done in my example code then el[1] will be either true or false depending on whether row one is selected or not. Since this information is used by various parts of the interface I will be passing around this symbol a lot.

checkboxes[stem_, n_] := Array[Checkbox@*Dynamic@*stem, n]
setCheckboxes[stem_, value_, n_] := Array[(stem[#] = value) &, n]
highlight[data_, stem_] := Module[{n = Length@data, unselected},
  unselected = Pick[Range@n, Array[stem, n], False];
  MapAt[Style[#, Gray] &, data, 
   Transpose[{unselected, ConstantArray[All, Length@unselected]}]]
  ]
display[data_, stem_, transposed_: False] := Module[{table},
  If[transposed,
    table = 
      Dynamic@Deploy@
        TableForm[Transpose@highlight[data, el], 
         TableHeadings -> { headings, checkboxes[el, Length@data]}];,
    table = 
      Dynamic@Deploy@
        TableForm[highlight[data, el], 
         TableHeadings -> {checkboxes[el, Length@data], headings}];
    ]
   setCheckboxes[el, True, Length@data];
  table
  ]

The code above takes care of the table, here are some buttons that can modify it:

export[data_, stem_] := Module[{n = Length@data, indices},
  indices = Pick[Range@n, Array[stem, n]];
  data[[indices]]
  ]

ButtonBar[{
  "Transpose" :> (transposed = If[transposed, False, True]),
  "Export" :> (exported = export[data, el]),
  "Select all" :> setCheckboxes[el, True, Length@data],
  "Deselect all" :> setCheckboxes[el, False, Length@data]
  }]

Finally, to show the table you should use the following code. Note that some parts of the table are updated inside display but the information about whether it is transposed is given to display. I have to tell Dynamic explicitly that it is only this information that I want to have dynamically passed to display.

Dynamic[display[data, el, transposed], TrackedSymbols :> {transposed}]

Example

Example

$\endgroup$
  • $\begingroup$ is there an easy way to also add checkboxes to select/deselect the columns? for this example data i want to be able to only select the first 2 column for import. Does the command @* work with Mathematica 9.0 and below? $\endgroup$ – kon Apr 7 '15 at 8:25
  • $\begingroup$ @kon f@*g is new in Mathematica 10, however you can replace it with Composition[f,g] in other versions. Adding checkboxes like that is possible with the same techniques, so hopefully by studying this code the extension to what you want to do should within reach for you. $\endgroup$ – C. E. Apr 7 '15 at 8:35
  • 1
    $\begingroup$ Thanks it's working now. I realized MapAt[...,{{1,All}}] is also not available at older versions so i had to create Tuples, but that also made it easier to combine the row and column selections. $\endgroup$ – kon Apr 7 '15 at 11:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.