Trying to understand C# and XML Spreadsheets in ASP.net

Question

I'm currently building a web application that will give users the same information they see on a table on the current page, in Excel spreadsheet format. I found some previous code in another project that did the same thing.

Small question first: Does anyone know how the Column ss:Width works? It's not an exact match. Column ss:AutoFitWidth="0" ss:Width="114" returns a column width of 21.00...

Where I'm having most of my trouble is, all of the data that is passed into the spreadsheet has leading and tailing whitespace; I have no idea how to remove it. I've tried .Trim() when I make my .ToString() query and I've been looking into other methods, but I haven't seemed to find it. The following is a snippet of my worksheet code:

   <Worksheet ss:Name="UserMgmt1">
        <Table>        
            @*Name*@
            <Column ss:AutoFitWidth="0" ss:Width="114" /> 
             <Row>
                 <Cell ss:StyleID="s29">
                  <Data ss:Type="String">Name</Data> 
                  </Cell>

             </Row>
        @foreach (tbl_Users query in ViewBag.contents )
            {
            <Row>
                <Cell ss:StyleID="s31">
                    <ss:Data ss:Type="String" >
                        @(query.Name.ToString().Trim())
                    </ss:Data>
                </Cell>
            </Row>
            }
     </Table>
 </Worksheet>

Forgot to add the ActionResult that implements this code:

    public ActionResult _Excel(tblUsers model)
    {
        List<tbl_Users> inquiriesList = UserManageModel.GetResults(model);
        ViewBag.contents = inquiriesList;
        return View("_Excel");
    }

And the call:

@using (Html.BeginForm("_Excel", "UserManage", FormMethod.Post))
{
    <input type="submit" value="View Detailed List of Users in Excel" />
}

The result looks like this in Excel, with that much whitespace surrounding each string item:

                     Administrator

Can you make the example smaller and also show more of the context? Like, maybe show a view containing a smaller example, and show the XML that the example produces. — John Saunders, Feb 13 '13 at 19:05
What renders the XML as a spreadsheet? Those aren't HTML elements. — Sam Axe, Feb 13 '13 at 19:08
@JohnSaunders No problem, I hope my added examples give a little more insight — MacSalty, Feb 13 '13 at 19:20

score 2 · Answer 1 · answered Feb 13 '13 at 19:40

2

According to the XML Excel spec on MSDN http://msdn.microsoft.com/en-us/library/aa140066(v=office.10).aspx#odc_xmlss_ss:column The ss:Width is the column width in points (which may render differently based on screen resolution, etc).

The extra spaces around your trimmed text may be due to your indenting. Try collapsing that XML element like this:

<ss:Data ss:Type="String" >@(query.Name.ToString().Trim())</ss:Data>

answered Feb 13 '13 at 19:40

tgolisch

6,549
3
24
42

That's exactly what it ended up being. Thank you. – MacSalty Feb 13 '13 at 19:56

score 0 · Answer 2 · answered Feb 13 '13 at 19:35

It turns out my problem all along was the formatting of my <Row> code

<ss:Data ss:Type="String" >
       @(query.Name.ToString().Trim())
</ss:Data>

The leading and following whitespace between the <ss:Data> tags is copied into the excel document. I have no idea why it does this, but this solves my problem.

Trying to understand C# and XML Spreadsheets in ASP.net

2 Answers2