Excel Export and Cell Formatting

Posted by: derek.hansen on 16 April 2018, 3:53 am EST

    • Post Options:
    • Link

    Posted 16 April 2018, 3:53 am EST

    I have a large application that was written using ActiveReports 6 and recently upgraded it to use version 11. After doing that I noticed that when exporting reports to Excel, all the exported data in Excel is now plain text, whereas when using v6 the data was formatted (Number, Date, etc.). The application creates a SectionReport and then places data on the report page using:

    sectionReport.CurrentPage.DrawText(“04/16/2018”, locationRectangle); // Example of writing a Date to the report
    

    After that, the report is finally exported to Excel using:

    using (var export = new XlsExport())
    {
    	export.AutoRowHeight = true;
    	export.UseCellMerging = true;
    
    	// When using AR v6, the Excel file had 04/16/2018 as a Date format
    	// When using AR v11, the Excel file has 04/16/2018 as a Text format
    	export.Export(sectionReport.Document, excelFile);
    }
    

    Looking at this, I’m actually surprised that the v6 Excel export formatted the data. Did that version look at the data for each page element to try and determine the cell format? A confirmation of this would be helpful for clarity.

    Lastly, and more importantly, can you provide a recommended course of action on how to convert code using Page.DrawText() into something that will have cell formatting when exported to Excel?

    Thanks,

  • Posted 16 April 2018, 4:36 pm EST

    Hello Derek,

    I have checked with both AR6 and AR11 version. Text drawn using “DrawText” method have “Text” format in exported excel file in both AR6 and AR11. Could you please share the stripped down application of AR6 which return the formatted data. Also, please share the exact version of AR6 and AR11 that you are using.

    Thanks,

    Mohit

  • Posted 17 April 2018, 2:43 am EST

    I have created two simple C# projects that demonstrate the Excel export differences and have attached them. One project uses ActiveReports version 6.3.4797 and the other uses 11.2.10750 (I did not include the referenced dlls, and the exact versions probably don’t matter).

    The “Version6” project creates a simple report with $100.50 displayed on it using DrawText(). When you close the report viewer window it will export the report to an Excel file. The data field in the Excel file is formatted as “Currency”.

    The “Version11” project is exactly the same except that the exported Excel field is formatted as “Text”.

    I am looking for a solution on how I can get ActiveReports version 11 to export this report to Excel with the cell type set to “Currency” like version 6 did.

    Thanks,

    ActiveReportsSupportExample.zip

  • Posted 17 April 2018, 6:18 pm EST

    Hello,

    Thanks for the information.

    I am able to replicate the problem at our end. I have escalated this to the development team(tracking id 257222) and will revert you back once have any information from them.

    Thanks

    Mohit

  • Posted 27 April 2018, 12:06 am EST

    Hello,

    We cannot provide the ETA for the above issue. You can use the “TextBox” instead of the “DrawText” method to draw the text in report as workaround. You can use the “OutputFormat” property to set format of cell in excel. Please refer the sample.

    Thanks,

    Mohit

    Version11_Modified.zip

  • Posted 30 April 2018, 9:49 pm EST

    Thanks for the suggestion. The issue that I run into doing that is that my report is drawn page by page inside the SectionReport.PageEnd event. I subscribe to the SectionReport.PageEnd event and use CurrentPage.DrawText() within that handler. If I change it to add TextBox controls instead of using DrawText there, I get the exception below. Is there a way to add TextBox controls to individual pages?

    GrapeCity.ActiveReports.ReportException:
    Controls can't be added or removed after report starts running. Please modify your ControlCollection in the ReportStart event
    
    Exception Properties:
    ErrorCode: InvalidReport
    ErrorData: null
    
    Stack Trace:
       at GrapeCity.ActiveReports.SectionReportModel.ControlCollection.#Mwb()
       at GrapeCity.ActiveReports.SectionReportModel.ControlCollection.#Owb(Int32 index, ARControl control, Boolean withNameCheck)
       at GrapeCity.ActiveReports.SectionReportModel.ControlCollection.Add(ARControl control)
    
  • Posted 1 May 2018, 10:06 pm EST

    Hello,

    Sorry for the inconvenience caused to you.

    I am discussing with our development team to find the solution for this. Will inform you once we found any solution for this.

    Thanks,

    Mohit

  • Posted 2 May 2018, 6:24 pm EST

    Hello Derek,

    You can add the “TextBox” on the designer layout of the section report at the specific location. After that, you can change the visibility of textbox or change the textbox value according to the individual pages. Please refer the attached sample.

    Hope it helps.

    Thanks,

    Mohit

    Version11_Modified.zip

  • Posted 7 May 2018, 5:05 am EST

    Thanks. Your solution works, but I don’t think it is going to work in my situation. Having to add TextBoxes for all the data on all pages at the beginning would be a huge amount of controls for a many page report. Since my reports are currently done page by page using DrawText to draw out each piece of data, I wouldn’t be surprised if this would be hundreds of thousands of controls on a single report. Then I would need to determine which tiny subset of those controls applies to each page in order to hide or show them.

    Can you tell me what version of ActiveReports this behavior changed on? I don’t want to have to downgrade all the way back to version 6.

  • Posted 9 May 2018, 7:29 pm EST

    Hello Derek,

    Can you tell me what version of ActiveReports this behavior changed on

    Unfortunately, this occurs in AR6 SP3 and higher version.

    Our development team is still working on this issue and will inform once any I get any information from them,

    Thanks,

    Mohit

  • Posted 4 June 2018, 9:23 pm EST

    Hello Derek,

    This is intended behaviour in the latest release of AR. You can use the following workaround to achieve your requirement:

    
    private void _pageReport_PageStart(object sender, EventArgs e)
    		{
    			var report = sender as SectionReport;
                report.CurrentPage.ControlInfoBegin(GrapeCity.ActiveReports.Document.Section.ControlType.None, "", new RectangleF(1, 1, 0.5f, 0.2f));
                report.CurrentPage.Font = new Font("Arial", 8, FontStyle.Regular);
                var x = new GrapeCity.ActiveReports.Document.Section.TextInfoItem("$#,##0.00", 100.5, Application.CurrentCulture, Application.CurrentCulture);
                report.CurrentPage.WriteTextInfo(x);
                report.CurrentPage.DrawText("$100.50", 1, 1, 0.5f, 0.2f);
                report.CurrentPage.ControlInfoEnd();
            }
    
    

    Please refer the attached sample.

    Thanks,

    Mohit

    Version11.zip

  • Posted 29 August 2018, 3:24 am EST

    Hi Mohit,

    I wanted to tag my question to this thread since it’s very similar to Derek’s scenario.

    We also have a large application with a little over 400 reports and we noticed the new (and apparently) intended behavior of our cells now being formatted as plain text when exporting to Excel when we upgraded to ActiveReports 10. Setting the OutputFormat on the text control does indeed address the behavior but having to set the correct OutputFormat (i.e. currency, number, etc) for every text control on every report for 400+ reports is a large impact to us.

    Are there any alternatives to doing this?

    For example, is there some global setting to get the reports to export to Excel the way they used to in version 6?

    If we upgrade to the latest version of Active reports, is the behavior still the same?

    Is there some generic string that we can set the OutputFormat to so that Excel properly interprets the strings rather than having to inspect every text control on every report on an individual basis?

    Thanks.

  • Posted 29 August 2018, 9:05 pm EST

    Hello Jack,

    I have escalated your issue to our development team and will inform you once I get any information from them.

    Thanks,

  • Posted 31 August 2018, 1:54 am EST

    Thanks Mohit.

  • Posted 2 September 2018, 5:25 pm EST

    Hello Jack,

    I am creating the utility for you. Could you please tell me which report type you have used in your project(Rpx or .cs or .vb)?

    Thanks,

  • Posted 4 September 2018, 3:16 am EST

    Hi Mohit,

    We use both .vb and .cs for our reports (most are .vb but we’ve been transitioning to .cs).

  • Posted 5 September 2018, 6:57 am EST

    Hi Mohit,

    Is there any ETA on when that tool/utility will be available?

  • Posted 5 September 2018, 7:37 pm EST

    Hello Jack,

    It is easy to create the utility for “RPX” based section report. However, the lots of problems we are facing while creating utility for the code based reports(.cs or .vb). I am discussing with our development team on this and will update you once we come to any conclusion.

    Thanks,

  • Posted 5 September 2018, 8:15 pm EST

    Hello Jack,

    After discussing with the development team, this is not possible to create the utility for the code based report. I will give you utility for “RPX” format if you want.

    I know how you feel but we are very sorry. You need to do the manual conversion.

    Thanks,

  • Posted 6 September 2018, 1:07 am EST

    Hi Mohit,

    This is very disappointing news.

    Can you give me any information as to why this existing functionality of Active Reports was changed in the first place so that we may better inform our customers why it no longer works for them?

    This will help us explain the scenario to them and will help explain why it’s going to take us so much time to give them back the functionality they had before they upgraded.

  • Posted 6 September 2018, 5:23 pm EST

    Hello Jack,

    Actually, we had the many customers who faced the problems due to this functionality. On exporting to excel, it changes the format of the cell automatically which looks like the number. So, we had to restrict the conversion according to the “OutputFormat” property to prevent the automatic conversion of format.

    Hope it clarifies.

    Also, we are sorry for the inconvenience caused to you.

    Thanks,

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels