Excel export changes number to date

This public forum is for user-to-user discussions of ASP.NET Report Maker. Note that this is not support forum.

Excel export changes number to date

Postby sotry » Tue May 22, 2012 3:40 am

I have a Text column in my Access database that show grades. The values are: prek, 3-5, 6-8, prek-2, or 9-12. I created a report of the table. When I export this report to Excel, the number values show up as dates. I can't figure out how to fix this. The text "prek-2" shows up correctly, but "3-5" shows up as "5-Mar". Any ideas? Thanks.
sotry
 

Re: Excel export changes number to date

Postby Webmaster » Tue May 22, 2012 10:03 am

This is an issue with Excel which tries to convert numeric data. To fix, use the Row_Rendered server event to format the field as '3-5 (add a apostrophe at front) so Excel will treat the data as string. For example:

If Export = "excel" Then
<Field>.ViewValue = "'" & <Field>.ViewValue
End If
Webmaster
 
Posts: 10728
Joined: Fri Aug 06, 2004 5:59 pm

Re: Excel export changes number to date

Postby sotry » Thu May 24, 2012 4:13 am

Thanks for this info. I tried implementing that and I get a compilation error. You may not have intended that I copy it verbatim, but I am not a Developer. Here is the error I am getting. Any ideas on what is wrong is very appreciated!

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS1002: ; expected

Source Error:

Line 916: // Row Rendering event
Line 917: public void Row_Rendering() {
Line 918: If Export = "excel" Then
Line 919: "Grade level (s) Covered".ViewValue = "'" & "Grade level (s) Covered".ViewValue
Line 920: End If

Source File: c:\inetpub\wwwroot\project14\Assessment_by_Titlesmry.aspx Line: 918
sotry
 

Re: Excel export changes number to date

Postby Webmaster » Thu May 24, 2012 9:45 am

You have generated as C# so use C# syntax.

if (Export == "excel") {
<Field>.ViewValue = "'" + <Field>.ViewValue;
}

Also check the correct <Field> object name in App_Code/<Table>info.cs since you use a field name with non-alphanumeric characters.
Webmaster
 
Posts: 10728
Joined: Fri Aug 06, 2004 5:59 pm

Re: Excel export changes number to date

Postby sotry » Sat May 26, 2012 5:33 am

Hi, unfortunately, it's still not working...

I got the field name and added the code you suggested. There is no info.css file in the App_Code folder so I got the field name from: C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\project14\1c4ffa9d\fe1dff0f\App_Web_7kf7n-hm.5.cs

public void Row_Rendering() {
if (Export == "excel") {
Grade_level28s29_Buckets_Covered_28preK2D22C_32D52C_62D82C_92D122C_college29.ViewValue = "'" + Grade_level28s29_Buckets_Covered_28preK2D22C_32D52C_62D82C_92D122C_college29.ViewValue;
}
}

There is no error generated but the Excel export is the same. It looks like it is not adding the ' before the value.

Your help is really appreciated. Thanks
sotry
 

Re: Excel export changes number to date

Postby Webmaster » Sat May 26, 2012 9:10 am

Use the "Row_Rendered" server event, NOT "Row_Rendering".
Webmaster
 
Posts: 10728
Joined: Fri Aug 06, 2004 5:59 pm

Re: Excel export changes number to date

Postby sotry » Tue May 29, 2012 5:47 am

Thanks, that worked!

Is there any other way to force Excel to treat the data as a string?
Also, do you have a suggestion for how to get it to only add the apostrophe if the field has data? Currently, it is adding the apostrophe to blank fields also.

Thanks again so much for your help!
sotry
 

Re: Excel export changes number to date

Postby Webmaster » Tue May 29, 2012 9:31 am

Just add your checking for null/empty field value. There should be plenty of examples on the web.
Webmaster
 
Posts: 10728
Joined: Fri Aug 06, 2004 5:59 pm

Re: Excel export changes number to date

Postby sotry » Thu May 31, 2012 4:29 am

Thanks again for your help. Your solution did work and it a great temporary fix. However, since the column holds grades and not years, have an apostrophe before the number looks strange in our file. Just want to make sure there is no other fix you have for making Excel treat the column as a string? As a test, I uploaded the same data to Zoho (from Excel ss) and did an Excel export on the view and the data showed up correctly. Any info is very appreciated. Thanks
sotry
 

Re: Excel export changes number to date

Postby Webmaster » Thu May 31, 2012 9:51 am

> As a test, I uploaded the same data to Zoho (from Excel ss) and did an Excel export on the view and the data showed up correctly.

Check the exported excel file data by clicking "F2" to view the actual data in the cell.
Webmaster
 
Posts: 10728
Joined: Fri Aug 06, 2004 5:59 pm

Re: Excel export changes number to date

Postby sotry » Fri Jun 01, 2012 2:22 am

I added this code to check for empty or NULL values before adding the ':

public void Row_Rendered() {
if (Export == "excel") {
foreach(DataRow row in Assessment_by_Titlesmry.Rows)
{
object value = row["Grade_level28s29_Buckets_Covered_28preK2D22C_32D52C_62D82C_92D122C_college29"];
if (value != DBNull.Value) {
Grade_level28s29_Buckets_Covered_28preK2D22C_32D52C_62D82C_92D122C_college29.ViewValue = "'" + Grade_level28s29_Buckets_Covered_28preK2D22C_32D52C_62D82C_92D122C_college29.ViewValue;
}
}
}
}

and I am getting this error:
CS0117: 'ASP.Assessment_by_Titlesmry' does not contain a definition for 'Rows'

For the Zoho export, if I click F2 in a cell, it changes the value to a date, but the export is working correctly - I get 3-11 in a row instead of 11-Mar.
sotry
 

Re: Excel export changes number to date

Postby Webmaster » Fri Jun 01, 2012 10:39 am

Just use:
if (Export == "excel") {
if (ew_NotEmpty(<Field>.ViewValue)) <Field>.ViewValue = "'" + <Field>.ViewValue;
}
Webmaster
 
Posts: 10728
Joined: Fri Aug 06, 2004 5:59 pm

Re: Excel export changes number to date

Postby sotry » Sat Jun 09, 2012 5:50 am

That worked. Thank you very much for your help!
sotry
 


Return to General Discussion (ASP.NET Report Maker)