|
| Sample code : Replicating the Excel 12 color scales with xlsgen |
|
In the following code, we'll replicate the Excel 12 color scales bars feature which allow to apply a pattern of colors to cells according to a statistical function, for instance a ranking function. Note that if you would like to have truly native color scales (for Excel 2007 and more recent versions), you may want to use the corresponding interface available in xlsgen, and take a look at the corresponding page in our documentation. The good news is that with the sample code provided, color scales 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.
The sample code provided actually creates two examples of color scales. At the top is a linear gradient, cells are associated with a color depending on how big they are, with red shades in small values and green shades in big values. Att the bottom is a percent-based ranking, we compute the cells whose value is in the 10% of the biggest values in the series. The series is simply an arbitrary series of values, and if you had to reuse this code you would only have to make little changes other than the myfigures array itself.
Note : the code may look a little long and complicated, but it's really not. It creates all the data in the workbook and then sets up everything.
| C# code |
public struct StyleUnit
{
public StyleUnit(double t, IXlsStyle s) {
threshold = t;
style = s;
}
public double threshold;
public IXlsStyle style;
}
public void ProcessColorScales( IXlsWorksheet wksht,
IXlsStyle defaultStyle,
double[,] matrixFigures,
StyleUnit[] arrStyles,
int top,
int left,
int width,
int height)
{
// compute min and max off the list
double dmin = matrixFigures[0,0];
double dmax = dmin;
for (int r = 0; r < height; r++)
for (int c = 0; c < width; c++)
{
double d = matrixFigures[r,c];
if (dmax < d) dmax = d;
if (dmin > d) dmin = d;
}
for (int r = 0; r < height; r++)
for (int c = 0; c < width; c++)
{
double d = (dmax - dmin) > 0 ? (matrixFigures[r,c] - dmin) / (dmax - dmin) : 0;
defaultStyle.Apply();
foreach (StyleUnit su in arrStyles)
{
if (d > su.threshold)
continue;
su.style.Apply();
break; // no need to go further
}
wksht.set_Float(r + top, c + left, matrixFigures[r,c]);
}
}
xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();
IXlsWorkbook wbk = excel.New( @"colorscale.xls" );
IXlsWorksheet wksht = wbk.AddWorksheet( "samplesheet" );
wksht.get_Columns("B1:B1").Width = 15;
wksht.get_Columns("C1:F1").Width = 10;
IXlsStyle bold = wksht.NewStyle();
bold.Font.Bold = 1;
IXlsStyle boldAligned = bold.Duplicate();
boldAligned.Alignment.Horizontal = xlsgen.enumHorizontalAlignment.halign_center;
boldAligned.Apply();
wksht.set_Label(3,3, "Jan");wksht.set_Label(11,3, "Jan");
wksht.set_Label(3,4, "Feb");wksht.set_Label(11,4, "Feb");
wksht.set_Label(3,5, "Mar");wksht.set_Label(11,5, "Mar");
wksht.set_Label(3,6, "Apr");wksht.set_Label(11,6, "Apr");
bold.Apply();
wksht.set_Label(4,2, "Investment 1");wksht.set_Label(12,2, "Investment 1");
wksht.set_Label(5,2, "Investment 2");wksht.set_Label(13,2, "Investment 2");
wksht.set_Label(6,2, "Investment 3");wksht.set_Label(14,2, "Investment 3");
wksht.set_Label(7,2, "Investment 4");wksht.set_Label(15,2, "Investment 4");
wksht.set_Label(8,2, "Investment 5");wksht.set_Label(16,2, "Investment 5");
IXlsStyle style = wksht.NewStyle();
style.Format = "#,##0.00";
style.Apply();
IXlsStyle style1 = wksht.NewStyle();
style1.Format = "#,##0.00";
style1.Pattern.BackgroundColor = 0xFF0000;
IXlsStyle style2 = style1.Duplicate();
IXlsStyle style3 = style1.Duplicate();
IXlsStyle style4 = style1.Duplicate();
IXlsStyle style5 = style1.Duplicate();
IXlsStyle style6 = style1.Duplicate();
IXlsStyle style7 = style1.Duplicate();
IXlsStyle style8 = style1.Duplicate();
IXlsStyle style9 = style1.Duplicate();
style2.Pattern.BackgroundColor = 0xFF0000;
style3.Pattern.BackgroundColor = 0xFF8800;
style4.Pattern.BackgroundColor = 0xFFAA00;
style5.Pattern.BackgroundColor = 0xFFDD00;
style6.Pattern.BackgroundColor = 0xFFFF00;
style7.Pattern.BackgroundColor = 0xAAFF00;
style8.Pattern.BackgroundColor = 0x88FF00;
style9.Pattern.BackgroundColor = 0x00FF00;
// precompute a number of thresholds for the linear gradient
StyleUnit[] linearGradient = new StyleUnit[] { new StyleUnit(0.2, style1),
new StyleUnit(0.3, style2),
new StyleUnit(0.4, style3),
new StyleUnit(0.5, style4),
new StyleUnit(0.6, style5),
new StyleUnit(0.7, style6),
new StyleUnit(0.8, style7),
new StyleUnit(0.9, style8),
new StyleUnit(1.0, style9),
};
// and for the top 10 percent cells
StyleUnit[] top10percent = new StyleUnit[] { new StyleUnit(0.9, style),
new StyleUnit(1.0, style9)};
// the actual figures, those are supposed to change over time
double[,] myfigures = new double[,] {
{0.50, 0.44, 0.21, 0.10},
{0.60, 0.33, 0.36, 0.25},
{0.20, 0.45, 0.28, 0.27},
{0.08, 0.17, 0.21, 0.01},
{0.07, 0.08, 0.11, 0.31},
};
// linear gradient
style.Apply();
wksht.set_Label(4, 8, "Linear gradient");
ProcessColorScales(wksht,
style,
myfigures,
linearGradient,
4, // top
3, // left
4, // width
5); // height
style.Apply();
wksht.set_Label(12, 8, "Top 10% in green");
ProcessColorScales(wksht,
style,
myfigures,
top10percent,
12, // top
3, // left
4, // width
5); // height
wbk.Close();
System.Diagnostics.Process.Start( @"colorscale.xls" );
excel = null;
|
The C# source code complete with project workspace can be found here in a zip file.