Excel export to HTML cells overlapping

Question

I have an excel workbook that I am trying to export to an HTML format (vba script which is called from Powershell below). When I run it on my local machine (Win10, Office 2016) the formatting is perfectly fine. However, when I try to run the same script from the server where it is supposed to live (WS2012, Office 2016) the formatting is ruined (except, oddly in Internet Explorer on certain machines).

I cannot figure out why the initiating computer is causing a problem with formatting for only this one section.

Original in Excel:

excel

Working:

working

Not working:

not working

So there's something between running on my local machine and on the server, the server had been on O'2013 but even after trying O'2016 it still doesn't work. I also disabled macro security in case that was screwing something up (it was disabled on my local) which didn't help either.

The excel file and the vba should be fine since it does work normally.

Macro to export HTML:

With ActiveWorkbook.PublishObjects.Add(xlSourceWorkbook, _

        "C:\Tasks\Phonebook-HTML\Web\Index.htm", , , xlHtmlStatic, _

        "TELEPHONEBOOK_18991", "")

        .Publish (True)

        .AutoRepublish = False

    End With

Working HTML:

<tr height="59" style="mso-height-source:userset;height:44.45pt">

  <td height="59" class="xl154" style="height:44.45pt"><a href="sheet007.html#RANGE!A2"><span style="color:#0070C0;font-size:16.0pt;

  font-weight:700">A</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A27"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">B</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A94"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">C</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A157"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">D</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A215"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">E</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A227"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">F</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A247"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">G</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A281"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">H</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A328"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">I</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A334"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">J</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A354"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">K</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A391"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">L</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A422"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">M</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A503"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">N</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A517"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">O</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A534"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">P</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A585"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">Q</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A587"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">R</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A634"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">S</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A695"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">T</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A718"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">U</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A719"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">V</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A741"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">W</span></a></td>

  <td class="xl154"><a href=""><span style="color:#0070C0;font-size:16.0pt;

  font-weight:700">X</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A767"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">Y</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A771"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">Z</span></a></td>

</tr>

NOT Working HTML:

<tr height="59" style="mso-height-source:userset;height:44.45pt">

  <td height="59" class="xl154" style="height:44.45pt"><a href="sheet007.html#RANGE!A2"><span style="color:#0070C0;font-size:16.0pt;

  font-weight:700">A</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A27"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">B</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A94"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">C</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A157"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">D</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A215"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">E</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A227"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">F</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A247"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">G</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A281"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">H</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A328"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">I</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A334"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">J</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A354"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">K</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A391"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">L</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A422"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">M</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A503"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">N</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A517"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">O</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A534"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">P</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A585"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">Q</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A587"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">R</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A634"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">S</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A695"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">T</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A718"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">U</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A719"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">V</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A741"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">W</span></a></td>

  <td class="xl154"><a href=""><span style="color:#0070C0;font-size:16.0pt;

  font-weight:700">X</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A767"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">Y</span></a></td>

  <td class="xl154"><a href="sheet007.html#RANGE!A771"><span style="color:#0070C0;

  font-size:16.0pt;font-weight:700">Z</span></a></td>

</tr>

score 1 · Accepted Answer · answered Aug 28 '19 at 00:31

NOTE: There is no difference in the Working vs Non-Working HTML. I used a Diff compare, they're 100% the same.

The excel file and the vba should be fine since it does work normally.

Looks like you've hit KB257757: https://support.microsoft.com/en-au/help/257757/considerations-for-server-side-automation-of-office

Somehow the Merge cells of the text "should be updated every Monday, Wednesday, Friday morning" is pushing the Alphabet to the right.

I am suspicious of the PublishObjects, despite Server-Side Automation of Excel being unsupported can you see if this code works:

Workbook workbook = new Workbook(); 
workbook.LoadFromFile("Book1.xlsx"); 
Worksheet sheet = workbook.Worksheets[0]; 
sheet.SaveToHtml("sample.html");

If that doesn't fix things instead of using PublishObjects you could try to export the HTML file yourself, using this code: https://gist.github.com/jkatricak/801024 and if you need to preserve HTML styles you can adapt: Export the dataGridView to Excel with all the cells format

This would give you the option to format the HTML table with your own CSS, eg:

<style type="text/css">
    table.tableizer-table {
        font-size: 12px;
        border: 1px solid #CCC; 
        font-family: Arial, Helvetica, sans-serif;
    } 
    .tableizer-table td {
        padding: 4px;
        margin: 3px;
        border: 1px solid #CCC;
    }
    .tableizer-table th {
        background-color: #104E8B; 
        color: #FFF;
        font-weight: bold;
    }
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th></tr>
</tbody></table>

Essentially if the above methods are unhelpful you'll need to work around it. Microsoft came up with the XML format that you can use without Microsoft.Office.Excel.Interop.DLL with ClosedXML (or OpenXML if you're up for a challenge). You could try other paid for products like EPPPlus - YMMV.

Thank you! See my post below, this was very helpful – aric8456 Aug 28 '19 at 13:51 — aric8456, Aug 28 '19 at 13:51

score 1 · Answer 2 · answered Aug 28 '19 at 13:50

@Jeremy Thompson

You got my brain thinking along the right lines: Somehow the Merge cells of the text "should be updated every Monday, Wednesday, Friday morning" is pushing the Alphabet to the right. I simply added in an extra row between those two sections and it seems to have stabilized the two. We're cooking with gas now!

Excel

excel

Web web

Excel export to HTML cells overlapping

2 Answers2