Sample code : Replicating the Excel 12 data bars with xlsgen

In the following code, we'll replicate the Excel 12 data bars feature which allow to keep bars side by side with figures. For more information and a screenshot on the Excel 12 data bars, please take a look here on the official Microsoft Excel blog. The good news is that with the sample code provided, data bars become visible and useable on older Excel versions. The following code is written in C#, you'll have to make the appropriate translations if you want this code in another language.


A replication of the data bar feature, in Excel 97/2000/XP/2003

 

C# code

xlsgen.CoXlsEngineClass excel = new xlsgen.CoXlsEngineClass();

IXlsWorkbook wbk = excel.New( @"databar.xls" );
IXlsWorksheet wksht = wbk.AddWorksheet( "samplesheet" );

IXlsStyle style = wksht.NewStyle();
style.Format = "#,###.00";
style.Apply();

wksht.get_Columns("C1:C1").Width = 15;

double[] myfigures = new double[] { 
                                     4073.00,
                                     5018.00,
                                     4707.00,
                                     7422.00,
                                     9456.00,
                                     3498.00
                                  };

// compute max off the list
double m = myfigures[0];
foreach(double d in myfigures)
  if (m < d) m = d;

// create the data bars
for (int i = 0; i < myfigures.Length; i++)
{
  wksht.set_Float(5 + i, 2, myfigures[i]);

  wksht.NewPicture(@"databar1.jpg", 
                   5 + i, 3, /* top-left corner cell*/
                   5 + i, 3, /* bottom-right corner cell*/
                   10, /*top*/
                   100, /*left*/
                   30, /*bottom*/
                   (int)(1000 * (1 - myfigures[i] / m)) /*right*/);
}

wksht.set_Label(3,2, "Sample data bars (Excel 97/2000/XP/2003/2006)");

wbk.Close();

System.Diagnostics.Process.Start( @"databar.xls" );

excel = null;

The good news is that since the data bar elements are pictures, it's really up to you to choose the picture of your choice. Here is a different one :


A different data bar in Excel 97/2000/XP/2003 obtained using a different data bar element picture

 

The C# source code complete with project workspace can be found here in a zip file.