[ Jocelyn Ireson-Paine's Home Page | Publications | Dobbs Code Talk Index | Dobbs Blog Version ]

Trials and tribulations: measuring drug efficacy in clinical trials, plotting graphs in Java with gnuplot, and reading Excel with JExcelAPI

Please look at this page. It is a gnuplot graph of a beautiful girl. Because, says the title, "gnuplot can plot any curve from a suitable datafile". If you don't like girls, gnuplot can also do penguins.

gnuplot is free, versatile, and easy to drive from a program. In this posting, I'll show you how to call it from Java, to generate graphs embedded in Web pages. I've been using it in work with the Oxford Pain Research Group, plotting graphs that summarise data from clinical trial spreadsheets about patients' responses to drugs. Our goal was to report on the drugs' efficacy, in units that doctors can easily understand and use when prescribing. As well as telling you about gnuplot, I'll explain about reading data from spreadsheets with Andrew Khan's free Java JExcelAPI library; and about structuring the reports as Web pages, making it easy to link related sections, and to link from summarised quantities back to the original data for the patients being summarised. But first, I have asked my friend Sebastian Straube to explain why one needs these measures of drug efficacy.

Contents

Individuals, averages, and graphs
by Sebastian Straube, Department of Occupational and Social Medicine, Göttingen University

Clinical trials and the measurement of pain intensity

Clinical trials are a way of assessing how well a particular treatment Y works for a particular disease X. Such trials can compare treatment Y to another treatment, or to several others, or to a placebo — a sugar pill with no biological effect. For example, a pain trial may compare different doses of a pain killer to placebo. The most reliable results are produced by clinical trials that are randomised (patients allocated to treatment groups at random) and double blind (neither patient nor physician know what treatment the patient is receiving).

For this blog posting, we have made up data from a fictitious pain trial. This is shown on this Pain Raw Data page. The important thing is the table, whose first three rows we show below:
 12345678DoseData errors?
89 0010.1370.0980.1970.2840.2760.1790.2780.3410 mg 
89 002-0.023-0.175-0.0220.3460.2210.220.2320.2230 mgRow 9 for this ID, row 17, had a week number, 9, that is more than the number of weeks in the trial. I have ignored it.
89 0030.1140.1870.1360.3760.2310.1420.1420.24110 mgRow 9 for this ID, row 53, had a week number, 9, that is more than the number of weeks in the trial. I have ignored it.

This trial has four treatment groups. That is, we are comparing four dosages of some drug Y: 0 mg, 10 mg, 15 mg, and 20 mg. The 0 mg group is of course the placebo group. Patients would typically receive the drug every day, perhaps in divided doses. The trial has 21 patients, each allocated at random to a treatment group. The left-hand column of the table contains patient IDs; the columns on the right report dose, and whether we detected errors when reading the data from Excel. The body of the table contains weekly measurements of pain relief, which we shall explain after the next paragraph.

Pain relief is an important outcome from trials of pain medicines. One way to measure it is to measure pain intensity (there are several scales for this — and all are by their very nature subjective) at various points of the trial and see how this changes over time. For the sake of argument, let's say that pain intensity is measured daily and weekly averages are calculated from the daily measurements for each patient. Before the trial begins we also measure a baseline pain score so that we have something to judge the changes against.

I should say that in the table shown, the weekly entries for each patient are calculated as:

1 - (pain intensity for the week / baseline pain score)
The subtraction makes this a measure of pain improvement. We displayed the data as improvements rather than as intensities because it made our "responder analysis" calculations, which we shall explain in a later section, easier to check.

Averages as a measure of drug efficacy

Once we have the weekly pain intensity measurements for each patient, we can calculate a weekly average for each treatment group. And by comparing these average pain intensity changes over time across treatment groups, we can compare the different treatments. The results can be shown as graphs. So a report on our example trial would have a graph plotting the 0 mg group's average pain intensity against time, one plotting the 10 mg group's average against time, and so on. This is a common way to compare treatments.

Averages of data from a lot of patients can be really helpful, a great way of summarising data. There are limits to how useful such averages can be, however, because not everyone is average. Let's say, in our example of painful disease X treated by drug Y the pain gets somewhat better, on average. The problem with averages is that they don't always reflect individual experience. Suppose some people with our disease X respond very well to drug Y and others respond not very well at all. The overall average doesn't represent either. Such a pattern of responses actually happens quite frequently, especially with painful conditions.

Responder analysis

If averages are not the way forward, what is? One possible solution is a "responder analysis", that is to ask how many people with disease X achieve a clinically meaningful response with drug Y, say having their pain halved. We could even have several levels of response, such as 30% reduction in pain (already a meaningful response), 50% reduction in pain (a very good response), and 70% reduction in pain (an even better response). Such response levels need to be calculated from individual patient data. We can take a patient's initial baseline pain score as 100% and then determine the percentage change from baseline for different time points in the trial, for example weekly. We can then classify the patients by their response level, e.g. into the categories mentioned above.

We can even take things a step further, and ask how many people achieve our desired level of pain relief without experiencing adverse events such as nausea that make them discontinue the treatment.

In our example data, you can see a classification by response on the Pain Response By Week Summary page. We show a fragment of this table below:
12
Any pain relief

0 mg: 7/21
10 mg: 9/21 NNT=7.51
15 mg: 10/22 NNT=4.21
20 mg: 13/21 NNT=3.41
Any pain relief
...
At least 15% pain relief
...
At least 15% pain relief
...
At least 30% pain relief
...
At least 30% pain relief
...
At least 50% pain relief
...
At least 50% pain relief
...
At least 70% pain relief
...
At least 70% pain relief
...

In this table, each cell corresponds to a particular response level at a particular week. (Because our example is fictitious, and it takes a long time to make fictitious data realistic, we show the contents of only one such cell, the first.) The cell has one line for each treatment group. Consider the first line. This states the dose — 0 mg — and a pair of numbers 7/21. The second number is the total number of patients in the group, i.e. the total number given 0 mg of drug Y. The first number is the number who experienced any pain relief. (The final number on each line, the NNT, is something that we shall look at in the next section.)

As another example, had we filled in the cell below this one, it would contain similar lines. Suppose the first such line contained the numbers 5/21. This would mean that 5 out of 21 patients receiving 0 mg had experienced at least 15% pain relief. Perhaps only 4 out of 21 would experience at least 30% relief. And so on.

We get a lot of information with this individual patient analysis that can be really useful for doctors and patients. The problem then arises of how to best represent all this information. A good way of doing so is in the form of graphs, such as the one shown here.
[Graph data points]

(Although this is fictitious data, the curves progress in the same way as in the graphs plotted during our research.) The proportion of patients achieving a desired level of pain response is shown on the y-axis, time in trials is represented on the x-axis.

This graph provides all sorts of valuable information. Let me give you an example. About 20% of patients achieve 50% pain relief or more, a clinically very good level of pain response. We also see that after four to six weeks, the proportion of patients who achieve 50% pain relief does not rise further. This sort of information has immediate relevance to clinical practice: only a minority of patients with disease X achieve a very good pain response with drug Y, and we could hypothesize that those who don't achieve it by four to six weeks are unlikely to achieve it later. So, if a patient with disease X does not get adequate pain relief after 4-6 weeks with drug Y, it might be time to try another treatment.

You could look at this as a therapeutic failure, but it isn't the doctor's or the patient's fault. It is in the nature of disease X and how it responds to treatment with drug Y. Overall population averages don't give that sort of information, and graphs are a really good way of illustrating these relationships. If you have a lot of such data to look at, a tool that automatically generates such graphs and allows you an overview is rather useful. This is one of many examples where pain researchers and computer scientists can cooperate, and as with so much interdisciplinary work, the result is greater than the sum of its parts. Pain Research at the Nuffield Department of Anaesthetics (Oxford) has been pioneering this approach.

Numbers needed to treat

This brings us to the "NNT" number shown on all lines except the 0 mg ones. In addition to calculating the response levels we can compare the proportion of patients achieving a given response level with active treatment to the proportion of patients achieving that same response level with placebo. One way of performing this comparison is to calculate so-called "numbers needed to treat" (NNTs).

An NNT of 10 means that 10 patients need to be treated with a particular therapy for one to get better. The ideal drug has an NNT of 1; everyone treated gets better. This hardly ever happens in medical practice, though some scenarios (treatment of certain infections with antibiotics) can get close. Otherwise an NNT of, say, 10 actually is not bad for a lot of diseases and some commonly used medicines have higher NNTs for their desired effect.

There are more ways of applying the individual patient approach. One area I am currently looking at is the effect of painful conditions and pain therapy on outcomes relevant to occupational medicine: time off work because of pain, its effect on productivity and so on. The individual responder approach holds great promise in this area, too. But perhaps it's time I stop and hand back over to Jocelyn.

Graphs, gnuplot, and Java

Thanks, Sebastian. Let me now explain how I plotted graphs such as the one above. I'll start with some background on gnuplot.

About gnuplot

gnuplot is a free plotting program, downloadable from www.gnuplot.info. The home page states that versions are available for Unix, OS/2, Windows, DOS, Macs, VMS, and Atari, amongst other platforms. Mention of Atari made me think that gnuplot must be old; In fact, says Wikipedia, it dates back to 1986. In that year, says author Thomas Williams in the FAQ:

I was taking a differential equation class and Colin was taking Electromagnetics, we both thought it'd be helpful to visualize the mathematics behind them. We were both working as sys admin for an EE VLSI lab, so we had the graphics terminals and the time to do some coding. The posting was better received than we expected, and prompted us to add some, albeit lame, support for file data.
Thomas Williams and Colin Kelley are two of the authors: the FAQ also acknowledges Russell Lang, Dave Kotz, John Campbell, Gershon Elber, and Alexander Woo.

gnuplot's versatility is demonstrated by the gnuplot demo plots page. This tells you how, when using gnuplot interactively, to display all the demonstrations that come with gnuplot. It also shows screenshots. And the Demo scripts for gnuplot version 4.2 page will take you directly to a list linking to over 50 examples, including Bezier splines for airfoils, image data for Larry Ewing's GIMP penguin, hidden surface removal in a plot of two interlocking tori, and stock-movement charts plotted with finance bars and candlesticks.

"gnuplot", says the FAQ, should be spelled with a lower-case "g". Even at the start of a sentence. gnuplot is nothing to do with GNU or the Free Software Foundation. It is also not covered by the General Public License, but it is free to use, though you are not allowed to give away modified versions.

So gnuplot is free. Is it reliable? The FAQ recycles a gratifyingly honest disclaimer from the README of a maths package by R. Freund:

For all intent and purpose, any description of what the codes are doing should be construed as being a note of what we thought the codes did on our machine on a particular Tuesday of last year. If you're really lucky, they might do the same for you someday. Then again, do you really feel *that* lucky?
I commend this disclaimer to Microsoft.

Although most users will run gnuplot interactively, it can easily be driven from a program, taking data points or expressions to be plotted from text .dat files, and plot commands — which do things like setting axis titles, legend styles, and line or point colours — from text .plt files. Your program can write these files, then invoke gnuplot to read them and save the graph it plots to a location specified in the .plt file.

A Java example

Let's now get down to details. This section will show three classes. Graph, which represents a graph; GraphToGnuplot, which is a module exporting the plotting routine; and Test, which demonstrates calling this to draw the "Drug trial A" graph shown earlier. You can download my example in this zip file.

The Graph class

Let's start with class Graph. An instance of Graph represents a graph of one or more clinical measurements against week number. In the version I'm posting here, I am saving paper by not defining access methods for the instance variables. GraphToGnuplot will therefore manipulate these directly, except for method Graph.addPoint.

package pain.dobbs;

public class Graph
{
  public int no_of_curves;
  // The graph displays this
  // number of curves on the
  // same axes.

  public String[] curve_title;
  // The i'th curve (counting from 1)
  // has name curve_title[ i-1 ].
  // This name will be displayed
  // against the curve or in a key.

  public int no_of_weeks;
  // The graph records data
  // for this number of weeks.

  public double[][] ys;
  // ys[ w-1 ][ c-1 ] is the Y
  // value for week w (counting
  // from 1) on curve c. The X
  // value is w.

  public String title;
  public String xtitle;
  public String ytitle;
  // These are the title for the
  // entire graph, displayed above
  // it; the X-axis title; and the
  // Y-axis title.


  // Create a Graph with the specified number
  // of weeks and curves.
  //
  public Graph( int no_of_weeks, int no_of_curves )
  {
    this.no_of_weeks = no_of_weeks;

    this.no_of_curves = no_of_curves;

    this.ys = new double[ no_of_weeks ][ no_of_curves ];
    for ( int i=1; i<=no_of_weeks; i++ ) {
      this.ys[ i-1 ] = new double[ no_of_curves ];
    }

    this.curve_title = new String[ no_of_curves ];
  }


  // Add the point (week_no,y) to curve curve_no.
  //
  public final void addPoint( int week_no, double y, int curve_no )
  {
    this.ys[ week_no-1 ][ curve_no-1 ] = y;
  }
}

The GraphToGnuplot module

Now let's look at GraphToGnuplot. I'm using this class as a module: it exports one static method, graphToGnuplot(g). This writes the points in g to a gnuplot .dat file, writes out a corresponding .plt file of plotting commands, runs gnuplot on them, and returns the name of the resulting image file, wrapped in a chunk of HTML that refers to it and to its .dat file.

The .dat file reference, incidentally, is in case our user want to plot the data using other programs. We will publish some of the graphs in research papers, and journals are meticulous about the appearance of included figures. So our users might want to tweak this appearance using plotting programs with which they are familiar.

Here, then, is the source for GraphToGnuplot. The real-life version called methods from some of my libraries, for reading and writing files and for replacing strings in templates. I've merged these into the code, so that you can run it without needing any other source.

package pain.dobbs;

import java.io.*;
import java.util.*;

public class GraphToGnuplot
{
  // Returns an HTML table containing an <IMG SRC=...> link to the
  // graph's PNG image file, with
  // a link to the graph's data-points file below it.
  //
  public static String graphToGnuplot( Graph g )
  {
    return graphToGnuplot( g, "c:\\pain\\dobbs\\graph.plt" );
  }


  // As the method above, but takes plotting commands from
  // the gnuplot PLT file named by plt_template. These commands
  // may contain strings delimited by dollar signs. We will
  // replace these by values of g's instance variables:
  // see outputGraphToPltString.
  //
  private static String graphToGnuplot( Graph g, String plt_template )
  {
    String filebase = "dr_dobbs";
    // In real-life use, replace this by a method that
    // generates a different, unique, filename every time
    // it is called.

    String dat_file = filebase + ".dat";
    String plt_file = filebase + ".plt";
    String png_file = filebase + ".png";

    outputGraphToDatFile( dat_file, g );

    outputGraphToPltFile( plt_template, plt_file, g, dat_file, png_file );

    try {
      Process p = Runtime.getRuntime().exec( "c:\\gnuplot\\bin\\wgnuplot.exe " + plt_file );
      // Runs my gnuplot executable. Change this to fit
      // its location on your machine.

      return "<TABLE>" + eol +
             "<TR>" + eol +
             "<TD>" + eol +
             "<IMG SRC=\"" + png_file + "\">" + eol +
             "</TD>" + eol +
             "<TD>" + eol +
             "<A HREF=\"" + dat_file + "\">[Graph data points]</A>" + eol +
             "</TD>" + eol +
             "</TR>" + eol +
             "</TABLE>";
    }
    catch ( java.io.IOException e ) {
      System.err.println( "Exception running gnuplot in graphToGnuplot: " + e.toString() );
      Runtime.getRuntime().exit(0);
      return null;
    }
  }


  // Writes a gnuplot PLT file to 'filename', generating it
  // from 'template', in which every substring delimited
  // by dollar signs has been replaced by the value of a
  // corresponding instance variable in g. Substrings
  // "$dat_name$" and "$png_name$" will be
  // replaced by the final two arguments, the names of the
  // gnuplot DAT file and the PNG image file to be
  // generated.
  //
  // Treats curve names specially. Replaces
  // "$curve_title1$" by g.curve_title[0],
  // "$curve_title2$" by g.curve_title[1],
  // up to the number of curves. However, the
  // PLT template has a fixed number of
  // curves written into its 'plot'
  // command. If the graph has a different number,
  // the PLT file may therefore not work.
  //
  private static void outputGraphToPltFile( String plt_template, String filename, Graph g, String dat_name, String png_name )
  {
    stringToFile( filename, graphToPltString(plt_template,g,dat_name,png_name) );
  }


  // Returns the contents of the PLT file referred
  // to above.
  //
  private static String graphToPltString( String plt_template, Graph g, String dat_name, String png_name )
  {
    String template = fileToString( plt_template );
    Map<String,String> params = new HashMap<String,String>();
    params.put( "$dat_name$", dat_name );
    params.put( "$png_name$", png_name );
    params.put( "$title$", g.title );
    params.put( "$xtitle$", g.xtitle );
    params.put( "$ytitle$", g.ytitle );
    for ( int i=1; i<=g.no_of_curves; i++ )
      params.put( "$curve_title" + i + "$", g.curve_title[ i-1 ] );
    return replaceInTemplate( template, params );
  }


  // Writes g's data points and labels to the gnuplot
  // DAT file named by filename.
  //
  private static void outputGraphToDatFile( String filename, Graph g )
  {
     stringToFile( filename, graphToDatString(g) );
  }


  // Returns a string containing g's data points and labels,
  // in the format expected for a gnuplot DAT file. To make the
  // file easy to read, the string contains comments, prefixed
  // by '#'.
  //
  private static String graphToDatString( Graph g )
  {
    String result = "";

    result += "# " + g.ytitle + " by " + g.xtitle + " for " + g.title + "." + kb7.io.Files.eol;
    result += "#" + eol;
    
    result += "# Column 1: " + g.xtitle + "." + kb7.io.Files.eol;
    for ( int curve_no=1; curve_no <= g.no_of_curves; curve_no++ )
      result += "# Column " + (curve_no+1) + ": " + g.curve_title[ curve_no-1 ] + "." + kb7.io.Files.eol;

    result += "#" + eol;

    for ( int week_no=1; week_no <= g.no_of_weeks; week_no++ ) {
      result += week_no;
      for ( int curve_no=1; curve_no <= g.no_of_curves; curve_no++ )
        result += " " + g.ys[ week_no-1 ][ curve_no-1 ];
      result += eol;
    }

    return result;
  }


  // End-of-line character.
  //
  private static String eol = System.getProperty( "line.separator", "\n" );


  // Replaces every dollar-delimited substring of 'template'
  // by replacements.get(key), where 'key' is the
  // substring including its delimiters.
  //
  private static String replaceInTemplate( String template, Map<String,String> replacements )
  {
    int delimiter_pos = template.indexOf("$");
    if ( delimiter_pos == -1 )
      return template;
    else {
      String pre = take( template, "$" );
      String post = drop( template, "$" );
      String key = take( post, "$" );
      String replacement = replacements.get( "$"+key+"$" );
      return pre + replacement + replaceInTemplate( drop( post, "$" )
                                                  , replacements
                                                  );
    }
  }


  // Returns the portion of s up to but not including the
  // first delimiter.
  //
  private static String take( String s, String delimiter )
  {
    int delimiter_pos = s.indexOf(delimiter);
    assert delimiter_pos != -1 : delimiter_pos;
    return s.substring( 0, delimiter_pos );
  }


  // Removes the portion of s up to and including the
  // first delimiter.
  //
  private static String drop( String s, String delimiter )
  {
    int delimiter_pos = s.indexOf(delimiter);
    assert delimiter_pos != -1 : delimiter_pos;
    return s.substring( delimiter_pos+delimiter.length() );
  }


  // Writes the string 's' to the specified file.
  // Does not append a newline. This is very old code,
  // not updated for what's available in recent Javas.
  // However, it works.
  //
  private static void stringToFile( String filename, String s )
  {
    try {
      PrintWriter out = new PrintWriter( new FileWriter(filename) );
      out.print( s );
      out.close();
    }
    catch ( java.io.IOException e ) {
      System.err.println( "Exception in stringToFile: " + e.toString() );
    }
  }


  // Returns the contents of the specified file.
  //
  private static String fileToString( String filename )
  {
    try {
      int c;
      StringBuffer sb = new StringBuffer();
      FileReader in = new FileReader(filename);
      while ( ( c = in.read() ) != -1 ) {
        sb.append( (char)c );
      }
      return sb.toString();
    }
    catch ( java.io.IOException e ) {
      System.err.println( "Exception in fileToString: " + e.toString() );
      Runtime.getRuntime().exit(0);
      return null;
    }
  }
}

Test data

Finally, here is the Test class. This runs graphToGnuplot on demo data, to plot the graph shown in Sebastian's explanation of responder analysis:

package pain.dobbs;

public class Test
{
  public static final void main( String[] argv )
  {
    int no_of_weeks = 13;
    int no_of_curves = 5;
    Graph g = new Graph( no_of_weeks, no_of_curves );

    g.title = "Drug trial A";
    g.xtitle = "Week";
    g.ytitle = "%";
    g.curve_title[0] = "Any pain relief";
    g.curve_title[1] = "At least 15% pain relief";
    g.curve_title[2] = "At least 30% pain relief";
    g.curve_title[3] = "At least 50% pain relief";
    g.curve_title[4] = "At least 70% pain relief";

    addDemonstrationData( g );

    System.out.println( GraphToGnuplot.graphToGnuplot( g ) );
  }

  
  private static void addDemonstrationData( Graph g )
  {
    g.addPoint( 1, 71.87, 1 );
    g.addPoint( 1, 49.13, 2 );
    g.addPoint( 1, 27.07, 3 );
    g.addPoint( 1, 11.43, 4 );
    g.addPoint( 1, 2.52, 5 );
    // To save paper, I shan't show the
    // plotting calls for the other weeks.
    // But they are in the zip file
    // containing this code.
  }
}

Java, JExcelAPI, and Excel data

The data from the trials came as Excel spreadsheets, so I needed some way of reading it in Java. (If you ask me why I used Java, it's because I already have a lot of Java code for constructing Web pages, written for a project on Web-based market-research questionnaires.) I'd already come across JExcelAPI, and because it is free and the tutorial was easy to follow, I decided to use that.

About JExcelAPI

JExcelAPI was written by Andy Khan, who makes it available free under the GNU Lesser General Public License. It reads data from spreadsheets, writes and updates spreadsheets (including such things as cell colouring and formatting), and can write to any output stream, including disk, HTTP, databases, and sockets. You can download it via the JExcelAPI main page at http://jexcelapi.sourceforge.net; and as already mentioned, Andy has written a tutorial, here. This has lots of sample code — with this, I was able to try JExcelAPI on our spreadsheets within half-an-hour of downloading it, and persuade myself it would do what we needed. (A useful argument for providing lots of sample code when you distribute software!)

A Java example

I used only a small part of JExcelAPI, the method for reading cells as strings. I'll show below how I did this. You can download the example and a spreadsheet to test it on from this zip file.

package pain.dobbs2;

import java.io.*;
import jxl.*;

// The 'main' method in this
// class demonstrates JExcelAPI,
// by reading cell A1 from test.xls.
//
public class JExcelDemo
{
  public static void main( String[] argv )
  {
    String filename = "c:\\pain\\dobbs2\\test.xls";
    String sheetname = "Sheet1";
    String A1 = readString( filename, sheetname, 0, 0 );
    System.out.println( "A1 = " + A1 );
  }


  // Convenience method where we want to
  // read one or a few cells from a file, and don't
  // mind the inefficiency of opening and closing
  // the file for each cell.
  // Column number x and row number y are
  // based at 0.
  //
  public static String readString( String filename
                                 , String sheetname
                                 , int x, int y
                                 )
  {
    try {
      Workbook workbook = Workbook.getWorkbook( new File( filename ) );
      Sheet sheet = workbook.getSheet( sheetname );
      String cell = readString( sheet, x, y );
      workbook.close();
      return cell;
    }
    catch( jxl.read.biff.BiffException e ) {
      System.err.println( e );
      System.exit(-1);
      return null;
    }
    catch ( java.io.IOException e ) {
      System.err.println( e );
      System.exit(-1);
      return null;
    }
  }


  // Return contents of cell (x,y) from 'sheet'.
  //
  private static final String readString( Sheet sheet
                                        , int x, int y
                                        )
  {
    return sheet.getCell(x,y).getContents();
  }
}

A note about classpaths

I often have trouble getting classpaths right, so it might be useful to show you how I handled JExcelAPI's. I downloaded the JExcelAPI jar file into c:\jexcel\jexcelapi\jxl.jar on my machine. For reasons that I can't now remember, I didn't want JExcelAPI on my default classpath — perhaps I was just imitating the tutorial — so I specified its classpath explicitly when compiling and running, as follows:

cd c:\pain\dobbs2\
javac -classpath %CLASSPATH%;c:\jexcel\jexcelapi\jxl.jar JExcelDemo.java
java -classpath %CLASSPATH%;c:\jexcel\jexcelapi\jxl.jar pain.dobbs2.JExcelDemo

Excel data, HTML, and links to individuals

I want to finish by saying a bit about how I presented our reports. The point here is that I decided to do so as HTML because it made it possible to link different parts of the report. One example is the .dat file accompanying each graph. Because, as I mentioned earlier, users might want to replot the graphs using other programs than gnuplot, we gave them a link back to each graph's points as a text file.

A second example came up in another study we did, on dental pain relief. In this, we plotted histograms to show the numbers of patients falling within specified percentage ranges of pain relief. When Sebastian was checking my calculations, he needed to see the original data for all the patients in each range. So I set a link from each bin (i.e. bar on the histogram) to a section listing the patient IDs in the bin, and then a link from each ID to the corresponding raw data.

It is for this same reason that the number of patients above the specified pain-relief threshold in the Pain Response By Week Summary links to an appropriate section in the Pain Response By Week Summary Links To Raw Data page.