Format type for exported Excel in telerik reporting is not proper for Date and Number fields

[Vikram]

Hi All, 

Format type in excel for the date and number fields is showing as 'General' and 'Custom'.

How to convert the excel format to 'Short Date' and 'Number' format type from report side.

Please find the attached files.

Thanks

Vikram

[Vikram]

HI All,

Please provide an a update on this issue. 

Thank

Vikram Mahendra

[Todor]

Hello Vikram,

To preserve the result from previewing the rendered document, formats are saved as custom formatting strings in the Excel file. This behavior can be modified by setting UseExtendedFormatting = False in the Excel device information settings. See Excel Formatting Strings for more details.

This way, a Numeric field that is formatted with the Format property of the TextBox will be exported as a Number in Excel.

The DateTime fields are exported as Date when the Format property of the TextBox is {0:d}. In the other cases, it gets exported in Custom or General format. This behavior doesn't seem correct to me and we will research it further.

If you format the field inline, for example, with the text function Format, it will be converted to a String and exported as Custom. That is the reason you don't get the desired type in Excel.

Regards,
Todor
Progress Telerik

[Vikram]

Hi Todor,

Thanks for your reply. 

Can you please help us with an example on how to set (UseExtendedFormatting = False), As we are not able to find Excel device information settings.

Waiting for your reply. 

Thanks 

Vikram

[Todor]

Hello Vikram,

You may provide the setting in the configuration file of the application that hosts the Reporting engine. See extensions Element article for details and an example. Here is how the section should look like:

<?xml version ="1.0"?>
<configuration>
	<configSections>
		<section name="Telerik.Reporting" type="Telerik.Reporting.Configuration.ReportingConfigurationSection, Telerik.Reporting" allowLocation="true" allowDefinition="Everywhere"/>
		...   
	</configSections>
	...
	<Telerik.Reporting>
		<extensions>
		  <render>
			<extension name="XLSX" description="Excel Description">
			  <parameters>
				<parameter name="UseExtendedFormatting" value="False"/>
			  </parameters>
			</extension>
		  </render>
		</extensions>
	</Telerik.Reporting>
	...
</configuration>

Regards,
Todor
Progress Telerik

[Vikram]

Hi Todor,

Thanks for reply. 

I added the above configuration code in Web.Config file. 

But still Number fields are in Custom format in Excel.

I am using Angular UI and Restful services.

Kindly let me know any solution for this issue. 

Thank, 

Vikram

 

[Todor]

Hello Vikram,

Please, check this silent video that demonstrates the outcome when exporting in Excel with different values for UseExtendedFormatting in the Standalone Report Designer. Note that with False, the numbers got exported as Excel Number format.

Just to clarify that the Reporting engine that runs in the designer and in the Telerik Reporting REST Service for .NET Framework is the same.

Regards,
Todor
Progress Telerik

[Vikram]

Hi Todor, 

The above solution is working fine for Excel Description (.xlsx) format. 

Not working for Excel 97-2003 (.xls) format.

Is there any work solution or workaround for reports exported in .xls format.

Thanks 

Vikram 

[Todor]

Hello Vikram,

For XLS rendering extension you need to specify another extension element in the configuration file - see Excel 2003 Device Information Settings:

<extension name="XLS" description="Old Excel Description">
  <parameters>
    <parameter name="UseExtendedFormatting" value="False"/>
  </parameters>
</extension>

My local tests confirmed that the approach works also for XLS.

Regards,
Todor
Progress Telerik

[Vikram]

Hi Todor, 

The solution given is working fine when we export to excel from standalone report designer as show in attachment, 

But when we try to export through the application it shows custom for both number and date fields. 

We tried adding your recent "XLS" extension solution in .exe file, but still now working. 

Please let us know what else we need to do. 

Thank you

Vikram 

 

[Todor]

Hello Vikram,

You must use the same configuration in the config file of your application. This would be the web.config file of the REST Service, or the app.config if you embed the reporting engine in a desktop application.

Regards,
Todor
Progress Telerik