topic Re: Read data from Excel Sheet Along With Colour ( Font Colour & Background Colour ) in QlikView

Read data from Excel Sheet Along With Colour ( Font Colour & Background Colour )

jyothish8807 — Wed, 20 Mar 2013 10:48:28 GMT

Hi Friends,

I are working on an assignment in which i collect data on an excel sheet and categories them on the basis of particular color.

Now I want to consolidate all workbooks along with the color given in excel sheet.

So please let me know is it possible to get data from excel sheet in to Qlikview along with the color given to it in Excel Sheet. ( Both Font color & Background Color ).

Regards

Jyothish KC

Re: Read data from Excel Sheet Along With Colour ( Font Colour & Background Colour )

jyothish8807 — Wed, 20 Mar 2013 10:56:57 GMT

Hmm....So there is no way to get the colours...????

Re: Read data from Excel Sheet Along With Colour ( Font Colour & Background Colour )

Anonymous — Wed, 20 Mar 2013 10:58:48 GMT

No thats not possible .. as far as i know

Regards,

anant

Re: Read data from Excel Sheet Along With Colour ( Font Colour & Background Colour )

jyothish8807 — Wed, 20 Mar 2013 11:02:00 GMT

Dammm .....

So is there any other way by which I can achieve my requirements..??

Re: Read data from Excel Sheet Along With Colour ( Font Colour & Background Colour )

azimabadi — Wed, 20 Mar 2013 11:02:45 GMT

Hi,

if the data structure in excel is not complicated and you can do a pre proccess on excel file before loading in QV, you can add some columns to Excel file and use excel functions such as ColorIndex(A3) to get the colors of cells in two new columns. then you can read it and process the color info in your QV app.

Best Regards

Peyman

Re: Read data from Excel Sheet Along With Colour ( Font Colour & Background Colour )

Anonymous — Wed, 20 Mar 2013 11:10:06 GMT

you can iport put your excel data into qlikview once loaded put in pivot/staright table

then you can expand your dimension or expression and put color function red() or green()

Regards,

anant

Re: Read data from Excel Sheet Along With Colour ( Font Colour & Background Colour )

jyothish8807 — Wed, 20 Mar 2013 11:11:44 GMT

Can you please explain a little more..?? Colorindex() function is giving an Error msg.

Re: Read data from Excel Sheet Along With Colour ( Font Colour & Background Colour )

jyothish8807 — Wed, 20 Mar 2013 11:14:15 GMT

No, This is not gonna work. I have a huge amount of data which is categorized on color basis, cant do on a pivot table.

Re: Read data from Excel Sheet Along With Colour ( Font Colour & Background Colour )

azimabadi — Wed, 20 Mar 2013 12:08:38 GMT

i will make a sample excel file and attach in next response asap

Regards

Peyman

Re: Read data from Excel Sheet Along With Colour ( Font Colour & Background Colour )

jyothish8807 — Wed, 20 Mar 2013 12:23:33 GMT

Thanx Alot..

Re: Read data from Excel Sheet Along With Colour ( Font Colour & Background Colour )

azimabadi — Wed, 20 Mar 2013 13:07:57 GMT

Ok,

please notice that i have two Functions in my Excel File defined:

Function CellBGColor(Optional myCell As Range)

    CellBGColor = myCell.Interior.ColorIndex

End Function
Function CellFontColor(Optional myCell As Range)

    CellFontColor = myCell.Font.ColorIndex

End Function

Regards

Peyman

Re: Read data from Excel Sheet Along With Colour ( Font Colour & Background Colour )

jyothish8807 — Thu, 21 Mar 2013 06:33:43 GMT

Thanx for the reply, but this not going to help me... , I want data along with the color in QV.

Re: Read data from Excel Sheet Along With Colour ( Font Colour & Background Colour )

azimabadi — Mon, 25 Mar 2013 07:53:04 GMT

first you need to read color information from excel. as mentioned you cen produce color info with two functions in excel.

then you need to read this color data alonwith your main data.

in QlikView you need to map the color data from excel to QV colors and atlast you usr this translated color info in background expression or everywhere you need it.

Regards

Peyman