1608504 - Using RAS.NET to modify the Date Order of a date field is not respected when exporting to Excel

When using the RAS.NET SDK to modify the Date Order on a date time field and exporting to Excel, the date order specified in code is not respected.

For example if the date order on the report template is set to mm/dd/yyyy and you change the order to yyyy/mm/dd in code and then export to various formats, Excel will output with the field showing mm/dd/yyyy.  Note that the other formats (pdf, rtf, and word doc) all respect the changes in their respective exports.

Crystal Reports 2008

Visual Studio 2008

Use RAS.NET to modify the Date Order and you will see that upon exporting to Excel format the date time order will stay the same as in the report template.

Other formats (pdf, rtf, doc) all respect the changes made in code and show the field correctly.

This issue is deemed by design.

A workaround is to either change the display format on the OS or to change the date field to a string at runtime and then export.

This can be done in the Crystal Reports Designer, where in the Excel Format Options, we have an option “Convert date values to strings”. If you check this option, excel will not format the date according to the OS setting.

To achieve this in an SDK application, before exporting , we need to set this option as true(refer to the below sample code).

------- set option Convert date values to strings -------

CrystalDecisions.ReportAppServer.ReportDefModel.ExcelExportFormatOptions exFormatOpt_old = (CrystalDecisions.ReportAppServer.ReportDefModel.ExcelExportFormatOptions)crReportDocument.ReportClientDocument.get_SavedExportOptions(CrystalDecisions.ReportAppServer.ReportDefModel.CrReportExportFormatEnum.crReportExportFormatMSExcel);

if (exFormatOpt_old.ConvertDatesToStrings == false)
{
 CrystalDecisions.ReportAppServer.ReportDefModel.ExcelExportFormatOptions exFormatOpt_new = (CrystalDecisions.ReportAppServer.ReportDefModel.ExcelExportFormatOptions)exFormatOpt_old.Clone(true);
 exFormatOpt_new.ConvertDatesToStrings = true;

 crReportDocument.ReportClientDocument.set_SavedExportOptions(CrystalDecisions.ReportAppServer.ReportDefModel.CrReportExportFormatEnum.crReportExportFormatMSExcel, exFormatOpt_new);
}

----------------------------

excel export date order RAS , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem