[WD17] Table2Excel - NULL Values

Startbeitrag von Michael Drechsel am 24.10.2013 13:44


I use table2excel to export a table (which contains NULL values) into an excel file.
After that I open the xls and search for the cell to delete the "0 €" (because its the NULL Value field)

But how to do that ? Neither NULL nor "" works. In excel I could delete the content of the field, but how can I do that with windev ??


If this field a nummeric field, did you disable "null authorized" und set the default value to "0" ?

von Lewi - am 25.10.2013 21:08
Hi Lewi,

I got the numeric values from a webservice. Because you can´t really tranfer NULL values I have a second boolean field which hold the information (the numeric field is "0" or NULL).

In the DiplayTableRow section I check if its true or not and overwrite the "0" with " " so that the user can´t see any value. Works so far till to the moment when the user makes an export to excel. Here you can´t overwrite an numeric field with " ". It displays always "0".

My workaround in the Excel sheet: I set the foreground color=background color for these fields (with windev). It´s not really good (every reformating of the excel sheet destroys my solution) but for the moment I have no other idea.

von Michael Drechsel - am 26.10.2013 08:16
Your Webservice delieverd a value from a database? For the database field, you can in the analysis defined, that the field is not "null authorized" an the default value is 0 (for automatic). That is, what i mean.

von Lewi - am 26.10.2013 10:53
Hi Lewi,

yes it comes from a database. But as I wrote its useless because I can´t transfer NULL Values via webservice "at is" to the frontend.

von Michael Drechsel - am 26.10.2013 10:59
Hi Michael,

I can think of two possible solutions:
1. Set the numeric colums to be text columns. The numeric cells will be marked by Excel to be numeric in text cells, but that's no big deal.
2. Postprocess the Excel sheet using the xls functions and apply a mask to the numeric cells: #,00;-#,00;;


von Piet van Zanten - am 26.10.2013 11:54
Hi Piet,

1. Can´t do that because I need the automatic calculations of the windev table and that goes only if the column is numeric. IMHO I can´t change it on the fly before the export to excel.

2. Which XLS Function do you mean ? I use the xlsDocument variable (http://doc.pcsoft.fr/en-US/?1000017464&name=xlsdocument_type_variable) and can change the value and the layout, don´t see the possibility to change the mask.

von Michael Drechsel - am 26.10.2013 12:32
Hi Michael,

I'm referring to the Mask property of the xlsCell variable.
MyWorksheet[11,"D"]..Mask="#.##0,00 €;-#.##0,00 €;;"
This works in Webdev 18.


von Piet van Zanten - am 26.10.2013 13:13
Hi Piet,


I am not an Excel Export, #,00;-#,00;; makes an numeric field empty ?

von Michael Drechsel - am 26.10.2013 14:08
Hi Michael,

The Excel mask has max. four sections of code, separated by a semicolon (;) to format a number.
These sections handle the layout for positive numbers, negative numbers, zero and text.
So if the third section is empty, a zero value will display an empty cell instead of 0,00


von Piet van Zanten - am 26.10.2013 15:16
Hi Piet,

you are my hero of the day ... works perfect !!!

Many thanks ... :cheers:

von Michael Drechsel - am 26.10.2013 15:50
Nice to know, cheers!

von Piet van Zanten - am 26.10.2013 16:53
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.