Removing Trailing spaces from Excel using asp.net

Ben · Jan 13, 2005

Hi All,
I have an Excel issue which I am unable to solve for the past couple of
days.
I have a printable version of a report , which uses the basic
Response.ContentType="application/vnd.ms-excel"; to give the report in
excel.
Now one of the fields is numeric, but Excel adds a trailing space to
every number, hence that column does not give a sum. Is there any way to
remove these trailing spaces via .net?
Thanks,
Ben

jongalloway · Jan 13, 2005

The best way to do this is to tell Excel the cell is a numeric value by
adding the format attribute x:num to the TD tag:
<td x:num> 42 </td>

- Jon
http://weblogs.asp.net/jgalloway

Ben · Jan 14, 2005

To give you a more clear pciture of my problem....
I have an aspx page with a repeater and the datasourse is a datatable,
since i have a simeple html output and an excel output, i only specify
the application type, depending on which option the user wants to see.So
my question is...how to do i specify in my code to format the cells of
the worksheet?
Thanks,
Ben

jongalloway · Jan 15, 2005

Here's an example:

<asp:Repeater id="Repeater1" runat="server">
<HeaderTemplate>
<table border="1">
<tr bgcolor="#ffcc99">
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Salary</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr bgcolor="#ffcccc">
<td><%# DataBinder.Eval(Container.DataItem, "au_id") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "au_fname") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "au_lname") %></td>
<td x:num><%# DataBinder.Eval(Container.DataItem, "salary")
%></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>

Note that the salary field td tag includes x:num.
- Jon
http://weblogs.asp.net/jgalloway

Ben · Jan 15, 2005

Tried adding x:num formatting to the table....doe snot remove the
trailing spaces. Also if i see the format cells, it says general and not
numeric.Any idea?
Thanks,
Ben

jongalloway · Jan 16, 2005

If you're mostly concerned about trimming spaces, you can just use the
string.Trim() method:

<%# Convert.ToString(DataBinder.Eval(Container.DataItem,
"salary")).Trim() %>

For the x:num formatting to work, it needs to be applied to every table
cell (<td>), not to the entire table. Also, I think your HTML tag on
the ASPX page needs to include the excel namespace:
<html xmlns:x="urn:schemas-microsoft-com

ffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">

Sorry I left that out before - I can't find my sample code that does
this.

- Jon
http://weblogs.asp.net/jgalloway

adv: Design reports in Word or Excel	3	Jan 3, 2008
How to return an excel file or excel data from ASP.NET	2	Jan 29, 2006
Export and Import from Excel into a Web page	2	Feb 28, 2005
Save an Excel from to client using Asp.Net	4	Sep 18, 2003
Column width issues when exporting to Excel using ASP.NET	0	Jan 4, 2007
[SUMMARY] To Excel (#17)	1	Feb 3, 2005
Asp.net Important Topics.	0	Jan 18, 2007
Using a decorator to remove parameters from a call	6	Apr 13, 2009

Removing Trailing spaces from Excel using asp.net

Ben

jongalloway

Ben

jongalloway

Ben

jongalloway

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads