OpenXML: Converting Excel Theme Color and Tint value to RGB value

 

Problem:

I was needed to import Excel data with rich text format and display in WPF grid. From RunProperties of OpenXml api I used to get all type of properties like Bold, Italic , Underline, Color. But there have two types of colors :  Theme Color and another RGB color. But Theme color is specific to Office and depends on theme. There have 10 sets of color for Excel 2007.


First Row contains 10 colors which is defined in Theme. In theme1.xml you will find these sets of colors like :



These 10 colors are used in Theme color palate but in different order. If we create a dictionary this will look like below snapshot where above 10 colors are ordered property according to Theme color.


When you will select first color then OpenXml Color property will give you Color.Theme = 0, which is integer value from 0 to 9. So from this dictionary you can map Color.Theme integer value to RGB value. So this is easy to get RGB value from first top 10 colors from theme colors from the above dictionary. But next 50 theme colors are not defined in theme1.xml. These colors are generated from top 10 theme colors in this dictionary by changing lightness. For example second row first color is 5% darker than Theme Color with 0 means “FFFFFF” . And these percentage value of darkness or lightness is defined in Tint value of OpenXml Color object. The value of Tint between –1 to +1 depends on lightness or darkness. Lets the color you have chosen is  from column 6 and second row which is 80% lighter than top color of  Column 6. So the Color.Theme value is 5. and Tint value will be 0.79998168889431442 a double value and for darkness this will be –0.0.79998168889431442 . So the challenge now is to convert Theme color and Tint color to RGB value.
 

Solution :

Tint value is a calculation of brightness or luminance of original color. So the final Luminance or brightness of color depend on calculation of  80% lightness/darkness. For that we need to convert of original RGB color value to HLS ( Hug, Luminance, Saturation) mode. Let say for RGB color for Theme color =5 from the above dictionary is #C0504D and we have to convert this color into HSL value and then from Tint value 0.79998168889431442  ( 80% lightness of that theme color) We have to calculate final HLS value of that color. After converting final HLS value to RGB we will get RGB value theme color 5 with 80% lightness.
Here we will need converter from RGB to HLS. I got help from codeproject article http://www.codeproject.com/Articles/49650/WPF-Color-Conversions  to get method of converting RGB to HLS. I am also mentioning code here which actually you will need.
 

RGB to HLS Converter :

 private struct HlsColor
    {

        public double A;

        public double H;

        public double L;

        public double S;

    }
    private HlsColor RgbToHls(Color rgbColor)
    {

        var hlsColor = new HlsColor();

        double r = (double)rgbColor.R / 255;

        double g = (double)rgbColor.G / 255;

        double b = (double)rgbColor.B / 255;

        double a = (double)rgbColor.A / 255;

        double min = Math.Min(r, Math.Min(g, b));

        double max = Math.Max(r, Math.Max(g, b));

        double delta = max - min;

        if (max == min)
        {

            hlsColor.H = 0;

            hlsColor.S = 0;

            hlsColor.L = max;

            return hlsColor;

        }

        hlsColor.L = (min + max) / 2;

        if (hlsColor.L < 0.5)
        {

            hlsColor.S = delta / (max + min);

        }

        else
        {

            hlsColor.S = delta / (2.0 - max - min);

        }

        if (r == max) hlsColor.H = (g - b) / delta;

        if (g == max) hlsColor.H = 2.0 + (b - r) / delta;

        if (b == max) hlsColor.H = 4.0 + (r - g) / delta;

        hlsColor.H *= 60;

        if (hlsColor.H < 0) hlsColor.H += 360;

        hlsColor.A = a;

        return hlsColor;

    }

 

HLS to RGB Converter :

    private Color HlsToRgb(HlsColor hlsColor)
    {

        var rgbColor = new Color();

        if (hlsColor.S == 0)
        {

            rgbColor = Color.FromArgb((int)(hlsColor.A * 255), (int)(hlsColor.L * 255), (int)(hlsColor.L * 255),

            (int)(hlsColor.L * 255));

            return rgbColor;

        }

        double t1;

        if (hlsColor.L < 0.5)
        {

            t1 = hlsColor.L * (1.0 + hlsColor.S);

        }

        else
        {

            t1 = hlsColor.L + hlsColor.S - (hlsColor.L * hlsColor.S);

        }

        double t2 = 2.0 * hlsColor.L - t1;

        double h = hlsColor.H / 360;

        double tR = h + (1.0 / 3.0);

        double r = SetColor(t1, t2, tR);

        double tG = h;

        double g = SetColor(t1, t2, tG);

        double tB = h - (1.0 / 3.0);

        double b = SetColor(t1, t2, tB);

        rgbColor = Color.FromArgb((int)(hlsColor.A * 255), (int)(r * 255), (int)(g * 255), (int)(b * 255));

        return rgbColor;

    }

    private double SetColor(double t1, double t2, double t3)
    {

        if (t3 < 0) t3 += 1.0;

        if (t3 > 1) t3 -= 1.0;

        double color;

        if (6.0 * t3 < 1)
        {

            color = t2 + (t1 - t2) * 6.0 * t3;

        }

        else if (2.0 * t3 < 1)
        {

            color = t1;

        }

        else if (3.0 * t3 < 2)
        {

            color = t2 + (t1 - t2) * ((2.0 / 3.0) - t3) * 6.0;

        }

        else
        {

            color = t2;

        }

        // Set return value 

        return color;

    }

 

Calculate Final Luminance ( L ) Value from Tint value :

MSDN have mentioned the algorithm to calculate final Luminance from Tint value in this article. http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.tabcolor.aspx .
I have used that algorithm to calculate Final Luminance (L) value  with following function :

    private double CalculateFinalLumValue(DoubleValue tint, float lum)
    {

        if (tint == null)
        {

            return lum;

        }

        double lum1 = 0;

        if (tint.Value < 0)
        {

            lum1 = lum * (1.0 + tint.Value);

        }

        else
        {

            lum1 = lum * (1.0 - tint.Value) + (255 - 255 * (1.0 - tint.Value));

        }

        return lum1;

    } 

For top 10 colors Tint value will be null so do not need to calculate luminance  or you can RGB value directly if you get Tint value is null.  Here HLSMAX is considered 255.
 

Calculating Final RGB Value from Theme Volor and Tint value :

Now lets call these function to calculate final RGB value from theme color and Tint value .

        var colorProperty = (runProperty as DocumentFormat.OpenXml.Spreadsheet.Color);

        var themeValue = colorProperty.Theme;

        if (themeValue != null)
        {

            string colourValue = _colorPallate[Convert.ToInt32(themeValue.Value)];

            Color fromHtml = ColorTranslator.FromHtml("#" + colourValue);

            HlsColor rgbToHls = RgbToHls(fromHtml);

            double calculateFinalLumValue = CalculateFinalLumValue(colorProperty.Tint, (float)rgbToHls.L * 255) / 255;

            rgbToHls.L = calculateFinalLumValue;

            Color hlsToRgb = HlsToRgb(rgbToHls);

        } 

Here as converting RGB value to HLS value we get L value between 0 to 1 so for converting it into range 0 to 255 it is multiplied by 255 for calculating Tint Value. Final L value is set to original HLS value and converted again to RGB value.
This is the full algorithm for converting Excel or Office theme color and Tint value to RGB color value.

My colleague Md. Masudur Rahman also worked with me to solve this problem.

How to use RequireJS using JQuery with Object Oriented Structure

RequireJS is really a nice JavaScript file which helps to write Javascript in modular way . It helps to load dependencies when needed and optimizes code.
you can learn and check the requireJS main site. There has sample project on how to use requireJS with JQuery and I have used that project.
This http://requirejs.org/docs/jquery.html page discussed well about this project structures. I worked with Javascript in object oriented way before but faced problems with dependency loading. requireJS modular pattern solved my problem.
I have done little experiment on requireJS which I am sharing here.

When you will open the project's app.html file you will the following code

<script data-main="scripts/main" src="scripts/require-jquery.js"></script> 

the data-main attribute telling you there have main.js script where from your javascript program will start. In ASP.NET we sometimes write code in our .aspx page and start with jquery ready() method, we also use ASP.NET controls value as arguments. To check with that I have changed the <script> tag simple as referencing require-jquery.js file.

<script type="text/javascript" src="scripts/require-jquery.js"></script>

Now this looks as how we reference jquery.js. jquery is included in require-jquery.js for optimization so you do not need to reference jquery again. Now lets call jquery ready method in html file.

<script language="javascript" type="text/javascript"> 
require(["jquery"], function ($) {
            $(function () {
               
                        alert('<p>this is a text</p>');                 
               
            });
        });
 </script>

This will display alert message. require() method has first argument which is saying I need jquery inside this method. requireJS will load jquery and after that $(function(){}); method will run by query. So this is very easy to use. 

Now I want to encode my alert message. But I want to do in modular way. Here should have a helper class so that any function can use encoder and decoder and for that I want to create a static class “HtmlEncoder”. And other class which will need encoder or decoder will load this class before calling. For that I have created another class with name “HtmlEncoder.js” inside scripts folder. 

define( function (){

    return {
        Encode: function(value) {
            return $("<div/>").text(value).html();
        },

        Decode: function(value) {
            return $("<div/>").html(value).text();
        }
        
    };
});

and in the main app.html page just calling  the method like that

  require(["jquery"], function ($) {
             $(function () {

                require(["scripts/HtmlEncoder"], function (htmlEncoder) {
                    $("#button1").click(function () {
                       
                        alert( htmlEncoder.Encode('<p>this is a text</p>'));
                    });
                });

            });
        });

Here only a single object of HtmlEncoder will be created. if you call another function with same dependency of “scripts/HtmlEncoder” then you will not get new object. It will work as singleton object. But do I need to create always a .js file for every dependency as I have created HtmlEnoder.js file for defining HtmlEncoder. No, you do not need to create .js file for defining a class. Youcan create definition without create a .js file for that. For that you need to give a name of definition. Let create HtmlEncoder in app.html page. 

    define("HtmlEncoder", {
             id:1,
            Encode: function (value) {
                return $("<div/>").text(value).html() + require('HtmlEncoder').id++;
            },

            Decode: function (value) {
                return $("<div/>").html(value).text();
            }


        });

As this is define in app.html so you can give dependency as follows:

 require(["jquery"], function ($) {
             $(function () {

                require(["HtmlEncoder"], function (htmlEncoder) {
                    $("#button1").click(function () {
                       
                        alert( htmlEncoder.Encode('<p>this is a text</p>'));
                    });
                });

            });
        });

Here in require statement I have mention only “HtmlEncoder” so it will find definition inside app.html. and load this instance.Also you can see I have created this little different way as we usually create a static class in javascript. and both give single object.

But I want to create different object for my definition. For that you need to define a constructor function and return the constructor. 

Lets create another file Employee.js and code like bellow :

define(function () {
    function Employee() {
        return {
            Id: 1,
            Name: "name",

            Roll: "roll",
            getEmployee: function () {

                return this.Name + this.Roll + this.Id++;
            }
        };
    }
    return (Employee);
});

Here you can see Employee constructor is defined and also returned. Now using this constructor we can create different objects of Employee class.

Now lets call the require method with Employee and create new instance of employee. 

 require(["jquery"], function ($) {
             $(function () {
                require(["scripts/HtmlEncoder", "scripts/Employee"], function (HtmlEncoder, Employee) {
                        var employee1 = new Employee();
                        var encodedValue = HtmlEncoder.Encode("<p>this is value</p>");
                        alert(encodedValue);
                        alert(HtmlEncoder.Encode(employee1.getEmployee()));
                  
                });
      });
});

HtmlEncoder is working as static here but as I have defined contructor for Employee so I can now create new instance for Employee object. 

Here I have tried to represent RequireJS in simple way through simple example by calling jquery inside app.html page but there have Advanced feature in RequireJS which you will find in requireJS documentation. I have tried to demonstrate how we can use HtmlEnoder like helper method. and also how  define can be created without without creating another .js file. Also created another definition with constructor function for creating multiple instance.

The Anatomy of a Large-Scale Hypertextual Web Search Engine

Recently I have taken seminar on Google search engine based on the paper http://infolab.stanford.edu/~backrub/google.html by Sergey Brin and Lawrence Page. Lots of interesting things I have learned from these paper. Also there have many resources in internet which helped me create my presentation. I am here sharing resources which helped me to make my presentation successful. Also there have lots of video in youtube about google history. PageRank, Google file system, scalability of Google technologies really impressive.

 

Resources:

1. Wikipedia article:

http://en.wikipedia.org/wiki/Google

2. Good presentations :

http://www.cis.temple.edu/~vasilis/Courses/CIS664/Papers/An-google.ppt

3. Another good presentation :

http://ssrnet.snu.ac.kr/course/os2007-2/anatomywebsearch-2007.ppt

4. Another good presentation:

http://students.sabanciuniv.edu/emrekaplan/docs/index.php?dir=presentations%2F&download=The+Anatomy+of+a+Large-Scale+Hypertextual+Web+Search.pptx

5. Google File System :

http://en.wikipedia.org/wiki/Google_File_System

6. Google Technology :

www.infonortics.com/publications/google/technology.pdf

7.  Google Bomb:

http://en.wikipedia.org/wiki/Google_bomb

8. PageRanking

http://www.cs.uvm.edu/~xwu/kdd/PageRank.ppt