Export Excel

[Mike]

Hi!

I recently started to experiment with the PivotGrid and i have to say - very cool! But i couldn't find a way to export the data to excel, is that possible? Or is there a way to iterate through all the cells so i can export them myself?

Kind regards
Michael

[Rosen Vladimirov]

Hi Michael,

Thanks for evaluating RadPivotGrid. Export functionality is in our to-do list and it should be ready for our official release (Q1 2013). With the current release ti will be quite a difficult task to implement it on your own so I would suggest you to wait until to official release and check our internal builds in 2013 - maybe we will be able to include the export functionality even earlier, but I cannot give you exact timeframe.

Don't hesitate to contact us if you have any suggestions or concerns.

All the best,
Rosen Vladimirov
the Telerik team

[Mike]

Hello!

Can you give me an approx. release date for the Q1 2013. Is it early Q1 2013 or later?

[Rosen Vladimirov]

Hello Michael,

The official release is expected to be in the end of February or beginning of March.

Feel free to send us any suggestions or concerns you may have regarding RadPivotGrid.

Regards,
Rosen Vladimirov
the Telerik team

[Gazzman]

Hi,

Has the export function been released?

Thanks,

George

[Rosen Vladimirov]

Hi George,

I'm glad to introduce you the first official release of RadPivotGrid. You can download our official 2013 Q1 release under your account. Unfortunately export functionality is not available with this release, but you can check all of the new features in our online help and our official page.

Do not hesitate to contact us if you have any suggestions or concerns.

Regards,
Rosen Vladimirov
the Telerik team

[Gazzman]

Hi Rosen,

Can you give an indication on when the export/print function is going to be available please? I would like to make a decision if using the Pivot grid is a viable option i.e. if print/export is going to be available on an internal build in the comming weeks then I can plan around this.

Many thanks,

George

[Mike]

Same here, i was really hoping to get the Export Feature in Q1 2013.

[Rosen Vladimirov]

Hi all,

We are sorry for the caused inconvenience. The new target for export functionality is Q2 2013 release. Of course we have already started working on this feature and we'll try to push it for the ServicePack, but I cannot promise you with a hundred percent assurance that we'll have it on time.

I'm really sorry for the caused inconvenience. Feel free to contact us if you have any problems or concerns with RadPivotGrid.

Kind regards,
Rosen Vladimirov
the Telerik team

[Gazzman]

Thanks Rosen,

I am not asking you to commit to a date however a genral guideline would be useful i.e. When is the service pack planned for release? Can we expect the export function to be available at then end of Q2?

A ball park weeks or months answer would help.

Many thanks,

George

[Gazzman]

Apologies Roseln, you have already answered the question i.e. Q2 2013.

Thanks,

George

[Rosen Vladimirov]

Hi George,

No problem. The service pack is expected for the end of March (beginning of April). We'll do our best to include export functionality in it, but if we do not succeed, than we'll target it for our Q2 release.

Hopefully this helps. Feel free to contact us if you still have any concerns or misunderstandings.

Regards,
Rosen Vladimirov
the Telerik team

[StevenDale]

Has this feature been implemented for the 2013 Q1 release? If so could you please provide an example of how to use it. Also are there any other print features for the Pivot Grid.

Thanks,

Billy Jacobs

[Rosen Vladimirov]

Hi Billy Jacobs,

Export to Excel is not included in our 2013 Q1 release, but I'm glad to announce that with our incoming Service Pack (it should be available for download at the end of the next week) you'll be able to use export functionality with RadPivotGrid.

Feel free to contact us if you have any problems or concerns.

Kind regards,
Rosen Vladimirov
the Telerik team

[Jon]

This is great news, what export types can we expect? 

[Rosen Vladimirov]

Hi Jon,

The most desired feature was export to Excel, so I can confirm that you will be able to export your data to xlsx file. We have also worked on several other formats, so we'll try to surprise you. In a few days you will be able to try our new export functionality.

Of course, if you have any specific requirement about export format, feel free to send your request to us and we'll check if it is possible to use it with our RadPivotGrid.

Kind regards,
Rosen Vladimirov
the Telerik team

[Jon]

I presented the Pivot Grid today to the business stakeholder and they are very excited and liked what they saw :) They did have one question on the Excel export: "will it be in pivot table format in Excel or just a flat data export"? If you could get me this info ASAP, i can continue to move forward with my development efforts.

Best regards,
Jon

[Rosen Vladimirov]

Hello Jon,

You will be able to export RadPivotGrid to flat table. We have not considered exporting to Pivot Table. We'll see what can be done in this area, but we will not have this feature for our Q2 release, as there are a lot of other planned features.

Hopefully this helps.

Greetings,
Rosen Vladimirov
the Telerik team

[StevenDale]

I downloaded the latest internal build 2013.1.401.40. Is this the version that has the export functionality? If so could you provide a simple example project demonstrating the new export functionality?

Thanks,

Billy Jacobs

[Rosen Vladimirov]

Hello Billy Jacobs,

Export functionality has been added in our Service Pack release (2013.1.403.40). This is our latest official release and I strongly recommend you to use it instead of the internal build. You can download it under your account. For an example project, I suggest you to take a look at our demos. You will find a new demo for RadPivotGrid with Export to xlsx (Excel), docx (Word), pdf and HTML.

Hopefully this helps. Feel free to contact us if you have any problems or concerns.

All the best,
Rosen Vladimirov
the Telerik team

[Mike]

Hi!

The new Export function is a very good start, even tough it's still a lot of work.

What's most important for me, is there a MVVM way for doing the Export? I mean without a reference to the RadPivotGrid Control.

[Rosen Vladimirov]

Hello Mike,

In order to use export functionality you must use RadPivotGrid's GenerateExport method. There's no other way to take all data that is necessary.

Hopefully this helps. Feel free to contact us if you have any problems or concerns.

All the best,
Rosen Vladimirov
the Telerik team

[Mike]

Ok, thank you!
I have two more problems:
- My PivotGrid is placed in a radbusyindicator, when the isbusy property of the busyindicator is set to true before i call the GenerateExport method, the method returns 0 rows.
- The export is extrem slow. 9843 rows with 37557 cells take several minutes for an excel Export. Is there a way to boost the Export?

[Rosen Vladimirov]

Hello Mike,

It looks like there is a performance problem when top and left borders of the cells are used. Setting them leads to huge calculations which are slowing down the whole export operation. Our Spreadsheet team is checking this problem and they'll also check what else can be done to increase the performance.
So to boost the export you should change the following line:

cellSelection.SetBorders(new CellBorders(leftBorder, topBorder, rightBorder, bottomBorder, insideBorder, insideBorder, null, null));

To:

cellSelection.SetBorders(new CellBorders(null, null, rightBorder, bottomBorder, insideBorder, insideBorder, null, null));

I'm sending you the modified version of the project. I've also added BusyIndicator and everything is working fine. Could you give it a try and inform us if it is acceptable solution for you?


Kind regards,
Rosen Vladimirov
the Telerik team

[Mike]

Hello!

Thanks for your efforts, i was able to get rid of the BusyIndicator problem (my own fault :-) ).
But your suggestion with the top and left border has nearly no impact. The export still takes about 10min!

I tried some combinations, maybe you can tune the export a bit more:

• Time for export: 00:10:03.3555635 (With SetCellProperties, but with null leftBorder and topBorder)
• Time for export: 00:01:58.6913792 (With SetCellProperties, but only with innerBorder)
• Time for export: 00:00:44.5637506 (Without SetCellProperties)

[Rosen Vladimirov]

Hello Mike,

In the examples I've sent you, the code is designed to create exactly the same visual representation of RadPivotGrid in Excel file. At some point this is causing slowness as we have to set many properties for each cell in Excel (borders, colors, etc.). You have already noticed this with your test, but just to clear it out - you can remove all code that set these properties and export only the data to excel. This way you'll not see colorized and polished table in Excel, but the export operation will be faster.
As I said, our team is currently checking what is causing this behavior and hopefully we'll have improvements in the near future.

All the best,
Rosen Vladimirov
the Telerik team

[Dan Ove]

Hi.
I can confirm that the Excel export is extremely slow. I have a simple pivot with 3 rows and 4 columns. I use the export functionality from the WPF Demo application.

I have done some testing and found out that if I run the export with the debugger attached it took 41.8 sec to generate the xlsx. If I started VS without the debugger it only took 1.5 sec.

I hope this helps.

Best regards

Dan Ove

[Rosen Vladimirov]

Hi Dan Ove,

Could you try the application from one of my previous posts -  RadPivotGrid-ExportToExcel-SL-BordersRemovedAndBusyIndicatorAdded.zip. It is optimized a little bit, so the export should be faster. Also you should be aware that using debug mode is loading a lot of information in the memory and based on your configuration and application, it can result in a delay of the operations.

I suggest you to try above mentioned example and see if it helps. As I've already said, we are working on this performance issue and hopefully we'll have a solution in the near future.

All the best,
Rosen Vladimirov
the Telerik team

[Dan Ove]

Hi

Your RadPivotGrid-ExportToExcel-SL-BordersRemovedAndBusyIndicatorAdded.zip did a export in approx. 5 sec. When I use the methods from the sample in my application the same pivot as before completes in 39 sec (2 sec faster with the optimized sample)

Dan Ove

[Mike]

Hi Dan Ove!

Thanks for the tip, i always tested in debugging mode. Without the debugger the Export is a lot faster.

[Dan Ove]

Hi again

I found out why the generation proceeded slowly under debug mode. In one way or another have Visual Studio IntelliTrace been turned on. (Maybe the VS update 2 installation?) After I disable it the export dropped from 40 sec to 1.5 sec.

Best regards

Dan Ove

[Richard]

Hi Rosen,

Can you please provide some sample on how to use GenerateExport of RadPivotGrid for WPF.

Thanks,
Chard

[StevenDale]

If you download the latest release build and install the local demos, there are examples and source code for this. You can also launch the demos on the telerik site to see the examples. http://demos.telerik.com/wpf/

[Thomas]

Hi,

i have some problems with the export to excel. I used near the same code from the export demo.
The file "Pivot.PNG" shows a screenshot from the filled pivot in my client and the file "pivot export.PNG" shows a screenshot from the exported excel file. 

these are my problems:

1. It seems that the collapsing of "01.01.2013" has no effect to the export. All cells "under" this element have been exported.
    This behaviour is equal to the export demo.
2. the format of the deepest date cells  is wrong for me. I know, there is a SetFormat Method for the CellSelection class, but i dont want to     set the format for every cell. Is it possible to localize the export data?
3. Row 4 in the export: Why is "Jan 2013" removed with "1.1.2013 0:00"?
4. something goes completly wrong on Row 6 and 32 in the export screenshot.

Is there a way to export only the text in all date cells without any intepretation of the underlying date object?

Edit: another question:
Currently the export methods block the ui thread. And it seems hard to transfer it to another thread, because the Workook or the PivotExportModel need the UI Thread for some methods. Is there a way to transfer the whole export process to a different thread?

Best Regards,
Thomas

[Polya]

Hello Thomas,

Regarding 1. It seems that the collapsing of "01.01.2013" has no effect to the export. All cells "under" this element have been exported.
In export functionality we are currently not taking into account expand/collapse behavior. This is by design - in your exports we will export everything no matter if it is collapsed or not.

Regarding 2. the format of the deepest date cells  is wrong for me. I know, there is a SetFormat Method for the CellSelection class, but i don't want to set the format for every cell. Is it possible to localize the export data?

This behavior is most probably because of the difference in the culture you are using in your custom group and the Excel's culture which is always the CurrentCulture. The only way is to set the format with CellSelection.SetFormat().

Regarding 3. Row 4 in the export: Why is "Jan 2013" removed with "1.1.2013 0:00"?

This is because as excel, in our spreadsheet we can parse the String Jan 2013 to date, which is automatically done. To modify this behavior we should use the CellSelection.SetFormat() method to set the correct format for these groups. In the attached project you can find an example of how to achieve this, assuming that "Jan 2013" represents the .ToString() of a custom group made by you. ( Search in MainWindow.xaml.cs : "Apply custom format only when the value's name is your custom group" )

Regarding 4. something goes completely wrong on Row 6 and 32 in the export screenshot.

This is most probably because the combination of your current culture and the format "01.01.2013" which in this culture is parsed as double. Is "01.01.2013" also the string representation of another custom group you created? If so you can try and use the same procedure ( using the CellSelection.SetFormat() ) as described above.

Could you try attached project and inform us if you still have any problems or concerns. I'm looking forward to hearing from you.

Regards,
Polya
Telerik

[Nayoung]

Hi! 

I'm wondering can PivotGrid's feature such as grouping export to excel?
PivotGrid's GenerateExport Method doesn't send me out the grouped(when row or column has several pivots) detail but only the Cells.

Thanks, Nayoung

[Dinko | Tech Support Engineer]

Hello Nayoung,

I am not sure that I have fully understood your question. What do you mean by grouped detail? Basically, the export functionality of the RadPivotGrid by default will export all cells in the control. No matter if the groups are collapsed or visible. The GenerateExport() method will return an object of type PivotExportModel which contains the collection of all cells. This collection includes the group text cells. 

Regards,
Dinko
Progress Telerik