topic Re: export to excel underline entire cell in QlikView

export to excel underline entire cell

mstoler — Tue, 23 Feb 2016 15:45:41 GMT

Hello,

I have a requirement that when I export a straight table to excel that I need to underline the entire cell.

If I set the format to <u> only the part with the number is underlined; I need to have the entire cell underlined just like in a financial statement.

Thank You,

Michael

Re: export to excel underline entire cell

Nicole-Smith — Tue, 23 Feb 2016 20:53:12 GMT

Right click on the cell, choose "Custom Format Cell", and you should be able to change the "Border after cell" to underline it however you'd like (style and color):

If you need to enable "Custom Format Cell", check out this link: option of custom cell format

Re: export to excel underline entire cell

mstoler — Tue, 23 Feb 2016 20:57:19 GMT

Hello,

When I export this to Excel the entire cell is not underlined.

I think a macro is required for this.

Michael

Re: export to excel underline entire cell

Nicole-Smith — Tue, 23 Feb 2016 21:45:43 GMT

That's weird because it works for me:

Re: export to excel underline entire cell

mstoler — Tue, 23 Feb 2016 21:53:13 GMT

Can this be conditionally turned on and off for specific rows by using a value in the Dimensions?

I will only want this on for a few columns and a few rows - not the entire chart?

Thanks,

Re: export to excel underline entire cell

Nicole-Smith — Wed, 24 Feb 2016 16:04:00 GMT

I can't find a way to only format a specific cell, so I'm at a loss on this one

Maybe someone else can chime in who has done this in the past...

Re: export to excel underline entire cell

marcus_sommer — Wed, 24 Feb 2016 16:54:40 GMT

I'm not sure if you could adjust the layout in this way like do you want it. The conditional attribut-expressions didn't seem to work and the custom cell-format isn't condtional. A further (rather ugly) way might be to use something like this as expression:

sum(YourField) & chr(10) & '--------------'

whereby & chr(10) & '--------------' could be a variable with a condition on the dimension-value and maybe with a parameter for the column-width, like:

var:

if(Dim = 'x', chr(10) & repeat('-', $1), '')

exp:

sum(YourField) & $(var(10))

A different way could be not to export the chart else to copy the data into a xls master-file which is layouted like needed and pulled the data from a (hidden?) data-sheet. Especially by complex layout-requirements is such method easier then to build any workarounds or the usage of many/complex macros.

- Marcus

Re: export to excel underline entire cell

mstoler — Wed, 24 Feb 2016 16:58:43 GMT

That is probably what I am going to do.

The only problem is when I export to excel the dashes are left-justified; I would like them right-justified.

Michael

Re: export to excel underline entire cell

marcus_sommer — Wed, 24 Feb 2016 17:50:20 GMT

You could try to play with spaces like:

if(Dim = 'x', chr(10) & repeat(' ', 5) & repeat('-', $1), '')

- Marcus

Re: export to excel underline entire cell

mstoler — Wed, 24 Feb 2016 17:52:01 GMT

I think the best solution is an excel macro to right-justify the cells.

That is what I am working on now.

Re: export to excel underline entire cell

HirisH_V7 — Wed, 24 Feb 2016 18:49:41 GMT

Hi,

Conditionally means you want the rows should be underlined as for every two rows on3 under line or what!!