Output "true empty" cell with ODS Excel or ODS Tagsets.ExcelXP

[ProcWes]

I've received suggestions on how to solve different problems.

If I had posted that my car won't start and you posted a video of how to
fix a bike chain, we'd be at the same place as this. Sure, you've suggested
a good way to fix a bike chain, but the bike chain wasn't the problem.

Have a good day.

[Reeza]

---

@ProcWes wrote:
I posted a straight forward question. So far, I've gotten responses that
don't deal with the question and responses that ignore my messages.

---

Given what you've stated, and realizing I know nothing else about your process here are your options, as far as I know. 

1. DDE. I've checked and this works and you can also do custom formatting. It's more work than tagsets and considered antiquated.
2. Check the templates for the PROCs you're working with to modify them to report a blank not a space. I suspect this is possible but not sure.
3. Create a custom tagsets by modifying the ExcelXP tagset that produces blanks not spaces. 
4. Create entire graph in SAS
5. Custom VBA macro to convert spaces to missing. I'm fairly certain there's an example on here or lexjansen.com. 
6. Modify your Excel process to handle spaces (probably the easiest IMO). 

Good Luck.

[Tom]

---

@ProcWes wrote:

Here's one of the cells from looking at the XML output using Tagsets.ExcelXP

<Cell ss:StyleID="data__r1" ss:Index="16"><Data ss:Type="String"></Data></Cell>

That same cell using ODS HTML:

<td class="r b data" #,##0_);[Red](#,##0);_("-"??_)>&nbsp;</td>

Here is an example on how the tagset XML is changing the cell format when it sees missing:

<Cell ss:StyleID="data__r1" ss:Index="15"><Data ss:Type="Number">89987</Data></Cell>
<Cell ss:StyleID="data__r1" ss:Index="16"><Data ss:Type="String"></Data></Cell> 

You can see it changes from number to string.

---

So it looks like EXCEL and EXCELXP are both generating empty cells, but they are setting metadata that indicates the cells are string instead of numbers. Not sure why the ISBLANK() function in Excel is treating them as if they are not empty.  

Also it is interestingly the HTML output is generating a NON empty cell (since it contains the non-breaking space character), but I assume that when EXCEL converts the HTML file into an actual EXCEL spreadsheet is converting that to an empty cell.

What does Excel itself store in an empty cell when you create it by hand? 

[ProcWes]

I don't think I can answer that.  I created an XML by hand and it only contains records in the text file where there is data.  So it doesn't add anything...

<Cell><Data ss:Type="Number">1</Data></Cell>
<Cell><Data ss:Type="Number">2</Data></Cell>
<Cell ss:Index="4"><Data ss:Type="Number">4</Data></Cell>

(I had typed 1-4 in 4 boxes)

[Tom]

---

@ProcWes wrote:

I don't think I can answer that.  I created an XML by hand and it only contains records in the text file where there is data.  So it doesn't add anything...

<Cell><Data ss:Type="Number">1</Data></Cell>
<Cell><Data ss:Type="Number">2</Data></Cell>
<Cell ss:Index="4"><Data ss:Type="Number">4</Data></Cell>

(I had typed 1-4 in 4 boxes)

---

So it looks like Excel is smart enough to not even store the empty cell in the XML at all.

You might ask SAS if they can modify the behavior of ODS Excel to do that. Or for help it making a customized version of EXCELXP template that could just not output anything for empty cells.

[Reeza]

You could also post-process the file and remove the Cell entries you don't want using PRX. The Index (and StyleID, if you customize it) may change but I believe the rest is fixed.

<Cell ss:StyleID="data__r1" ss:Index="16"><Data ss:Type="String"></Data></Cell> 

Note that I'm not sure why yours is R1, mine are data__r.