Qlik sense Dot Net SDK Excel download with color code

This blog is about downloading data of a straight table to excel using .Net SDK. The idea is to explore .Net SDK capabilities in enabling the download with color codes on the cells. The default excel download options in mashup does not preserve the color codes of the cell. This can also be achieved through mashup and javascript plugins for excel. But having a server-side can help you have more control and logging of download, which could come handy in an enterprise setting with strict data policies.

Nuget Packages Needed

1.       Qlik sense .Net SDK.

2.       EPPlus to create excel. You can also achieve using the built-in excel library.

Key Components
  1. Dimension Info
  2. Measure Info
  3. Hypercube

This example aims at showing necessary components that you need to access and not the efficiency. You can refine this code to work efficiently. The example can be downloaded at Qlik-Dot-Net-SDK-Excel-download-with-color-code

How To?

This web API get the app name and the object id as input.

  •  Once the app is opened, the object details are fetched using the following statement
Qlik.Sense.Client.Visualizations.Table obj = app.GetObject<Qlik.Sense.Client.Visualizations.Table>;(objectid);
  • Create a page object to fetch the hypercube data
var first10CellsPage = new NxPage { Top = 0, Left = 0, Width = 5, Height = 1000 };

IEnumerable<NxDataPage> data = obj.GetHyperCubeData("/qHyperCubeDef", new[] { first10CellsPage });
  •  Find out the number of measures and dimensions present in the hypercube using the DimensionInfo and MeasureInfo of the object and the number of rows of records in the hypercube
int dimcount = obj.DimensionInfo.Count();

int measurecount = obj.MeasureInfo.Count();

int rowcount = data.ElementAt(0).Matrix.Count();
  • Use the above information to create the array to store the data, header and the color-code. Iterate through the measureInfo, DimensionInfo and Hupercube to populate the arrays.The key thing to note is that the color code of the cells are in the Attibute Expression Values. It is fetched using the below code.
NxCell el = data.ElementAt(0).Matrix.ElementAt(i).ElementAt(j);

double col=0;

if (el.AttrExps!= null )  col= el.AttrExps.Values.ElementAt (0).Num.ToString ()!="NaN"? el.AttrExps.Values.ElementAt(0).Num:0;

if (data.ElementAt(0).Matrix.ElementAt(0).ElementAt(j).IsOtherCell)

{

header[1 + i, j] = dim_alternate_title[i,0];

data_color[1 + i, j] = col;

}

else

{

header[1 + i, j] = data.ElementAt(0).Matrix.ElementAt(i).ElementAt(j).Text;

data_color[1 + i, j] = col;

}

 

 

  •  The color is stored as double. A function is provided to convert the double to hexadecimal. This is fetched from internet and not my own.
internal static string DoubleToHex(double value, int maxDecimals)

{

string result = string.Empty;

if (value < 0)

{

result += "-";

value = -value;

}

if (value > ulong.MaxValue)

{

result += double.PositiveInfinity.ToString();

return result;

}

ulong trunc = (ulong)value;

result += trunc.ToString("X");

value -= trunc;

if (value == 0)

{

return result;

}

result += ".";

byte hexdigit;

while ((value != 0) &amp;&amp; (maxDecimals != 0))

{

value *= 16;

hexdigit = (byte)value;

result += hexdigit.ToString("X");

value -= hexdigit;

maxDecimals--;

}

return result;

}

 

 

  • The hexadecimal is given as input to the excel function.
string hex=DoubleToHex(data_color[i, j], 0);

Color bg = System.Drawing.ColorTranslator.FromHtml("#" + hex);

 

 

 

 

 

You May Also Like

About the Author: codeatroost

Leave a Reply

Your email address will not be published. Required fields are marked *