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
2. EPPlus to create excel. You can also achieve using the built-in excel library.
Key Components
- Dimension Info
- Measure Info
- 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
- Create a page object to fetch the hypercube data
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 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.
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.
{
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) && (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.
Color bg = System.Drawing.ColorTranslator.FromHtml("#" + hex);
Leave a Reply