none
Export Grid Data to Excel RRS feed

  • Question

  • Hi guys,

    I want to export grid data to excel .When i click a button i need a prompt window with open/save/cancel options using Open XML Format SDK 2.0




     Thanks in advance
    Friday, February 19, 2010 6:57 AM

All replies

  • Hi vijay.nalla,

    Thanks for your question.

    To help solve your problem, could you please describe your scenario or requirements in detail? Do you want to export grid data to Excel or open/save files using Open XML SDK 2.0? If you need to export data, where is the data source from?

    Thanks,

    Lu
    Monday, February 22, 2010 9:22 AM
  • Hu Lu Zhang thank you for your response

    Actually we have a silver light application where based on some parameters we will populate Grid data and also based on grid data Visifire charts will be displayed.We are using webservices for communication with SQL Analytics.
     Now i want the same Grid data  and charts  to be exported to Excel with open /save/ cancel option.One thing to be observed is silverlight cannot export visifire graphs to excel as i read in one of the forums http://www.visifire.com/forums/index.php?showtopic=74.

    So we are trying to go with OPEN XML Format SDK 2.0.What i need is a .net application where i can do the following tasks,i am not bothered about the integration part with silverlight for time being.For now this is my requirement.If we can first solve this problem later we can explore with the rest of the thing.


    If the question is not yet cleared ,Please reply me with question.


    Thanks
    Vijay

    Tuesday, February 23, 2010 8:38 AM
  • Hi Vijay,

    Thanks for your description.

    I want to make it clear that where is your grid data stored in? Or maybe you have already extract the data from database and what format is it? Is your need to export these data and charts to Excel using SDK?

    Thanks,

    Lu
    Wednesday, February 24, 2010 9:56 AM
  • Hu Lu Zhang thank you for your response once again


    I will explain in detail.

    I have some controls like measure,Top x,etc. donot bother about the controls whatever it is .Now i have a go button on the silverlight screen.When i click go button based on parameters(controls) selected a call to webservice is made to SQL Analytics(Cubes) where i will fetch some data in the form of xml string and is populated to grid and also at the same time visifire charts are displayed based on the data.ie we will pass some datapoints that we wish to to generate charts.There will be export to excel button in this silverlight screen.Now when i click this button i need to export grid data and charts to excel by asking for open/save/cancel options.I think we need a server side solution for our requirement.

    We know that Silverlight doesnt support bitmap object so we cannot export charts.So we need to figure out a way to export data and charts usnig OPEN XML Format SDK. I  have been searching in the web since 2 weeks but i didnt find proper solution to this.Even i dont see much online help for this except the documentation which is difficult to understand.Hope you understand my exact requirement.


    If you need further clarification revert me with questions.

    Your help is very much appreciated.

    Thanks
    Vijay
    Thursday, February 25, 2010 5:12 AM
  • Hi Vijay,

    Thanks again for your detailed description!

    From the decription now I can conclude that you need an Open XML Format SDK solution to export a chart into Excel. The chart is based on data which is in xml format. Is that right? Can I create a chart in Excel based on the xml data in Excel using SDK or you need to export the chart directly in the format of bitmap (as an image)?

    Thanks,

    Lu

    Thursday, February 25, 2010 9:56 AM
  • Hi Lu ,

    Thanks for your response.

    IF you can provide both the solutions it would be better for me and i  also want to export the data from the grid  using open xml sdk.And when i export i need to get an option open/save/cancel.


    Thanks once again.


    Thanks
    Vijay
    Thursday, February 25, 2010 5:10 PM
  • Hi Lu,


    Did you get any solution for my requirement.If you can create chart in Excel based on the xml data in Excel using SDK and also grid data to excel asking user for save/open/ cancel.

    Thanks in advance.






    Thanks
    Vijay
    Saturday, February 27, 2010 6:40 PM
  • Hi Vijay,

    In your scenario, we suggest you create a template in Excel (which contains both data and the chart based on it), then fill the data based on your .xml file.

    We suggest you using the Productivity Tool (download) to achieve this. You could create a template in Excel and open it in Tool, then use "Reflect Code" to see how to generate the code using SDK.

    As for how to import your xml data (change the value of your template data), you could modify the data in the template, and save as another file, then use "Compare Files" -> "View Part Diff" -> "View Part code" to see how to change the values.

    Hope this helps. If you have any question, please let me know.

    Thanks,

    Lu

    Monday, March 1, 2010 8:45 AM
  • Hi Lu
    Thanks For your response.

    Before asking you any questions,let me know one thing.You said I have to take excel template.So ,I will not get option for save/open /cancel is this right.Thanks for providing me the link, The link you provided is SDK ,is this right.I already have SDK ,but " You could create a template in Excel and open it in Tool, then use "Reflect Code" to see how to generate the code using SDK" can you tell me the steps for using Reflect code or can you provide a small example  with steps so that i can be more clear.

    Thanks in advance.


    Thanks
    Vijay

    Monday, March 1, 2010 3:35 PM
  • Hi Vijay,

    Thanks for your reply.

    Firstly, the Open XML SDK Tool can be downloaded from the same link as the SDK, which is the first file "OpenXMLSDKTool.msi" among the files to be downloaded. After installing, you could find it under the path of ~\Program Files(x86)\Open XML SDK\V2.0\tool\OpenXmlSdkTool.exe and run it.

    Generally, it takes the following steps to generate sample code: make some changes to the source document and save it as a different document as the target document; compare the source and target to see what has been changed in the package. For the details of using the tool to reflect code, you could try the following steps:

    1. Create an empty xlsx document and save it, such as “source.xlsx”.
    2. Add two columns of data, for example, to the empty xlsx and insert a chart according to the data into sheet1, then save it as “target.xlsx”.
    3. Open the Tool; click "Compare Files" to compare the “source.docx” and “target.docx” files.
    4. In the "File Comparison" tab, click "View Part Diff" to show the differences between the selected two parts (especially for "/xl/worksheets/sheet1.xml" and "/xl/charts/chart1.xml").
    5. Click “View Part code” to see the complete sample code on how to generate a template with data and chart.

    You could also try the steps to find out how to change the data values in cells using SDK. For the tool's feature, you could also refer to this link: http://www.youtube.com/watch?v=KSSMLR19JWA

    As for your "open/save/cancel options" question, I'm not sure what's the exact requirement. Do you mean you want to have "open" option to open the data source (the .xml data) where you want to export from? For "cancel", do you mean you need this option during the exporting process? I don't know why you need "save" option? Could you describe your scenario in detail so we can help find a solution?

    Hope this helps. If you have any question, please let me know.

    Thanks,

    Lu
    Tuesday, March 2, 2010 1:35 AM
  • Hi Lu

     

    Thanks For you help.I am able to generate the code.I will more on this mean while about your question.

    User will be provided with Export to Excel button in the page.So when user clicks on the button the data in the grid and chart data should be exported to excel ,but we need to provide him the option whether he wants to open it directly ,or save or cancel the export to excel,why because we cannot tel him that the file is saved in the following location.Did you get my point.I mean for example when we are downloading the open XML SDK tool it is giving a popup message whether to run,save,cancel,this allows user to have multiple options,the same thing with the excel ,I need option open/save/cancel.I think you will get now.

    If you further have any doubts revert me back.

     

    Thanks in advance

    Once again thanks Lu

     

    Thanks

    Vijay

     

    Tuesday, March 2, 2010 4:35 AM
  • Hi Vijay,

    Thanks for your description!

    I think your open/save/cancel funtions are related to UI and need some monitoring during the exporting process, which Open XML SDK doesn't support. The Open XML SDK is a collection of classes that let you create and manipulate Open XML documents. So you could populate the data into a spreadsheet based on Open XML Format. But you can't monitor the process using SDK. For example, if you need to cancel the exporting process, you may need to catch the "cancel" event (which SDK can't do) and delete all the data which has already been populated into Excel (which can be done by the SDK). Is that clear to you? 

    BTW, there is an MSDN online how-to article which may help you on how to insert a chart into a spreadsheet.

    Hope this helps. If you have any question, please let me know.

    Thanks,

    Lu
    Tuesday, March 2, 2010 7:13 AM
  • Hi Lu

     

    Thanks for your clarification.Is there any other way not (with XML SDK) to import data and charts to excel by allowing user to save/open/cancel options in silverlight.

    Thanks in advance

     

    Thanks

    Vijay

    Wednesday, March 3, 2010 4:51 AM
  • Hi Lu

     Sorry for the prevoius post i said import instead of export.

    Thanks for your clarification.Is there any other way not (with XML SDK) to export data and charts to excel by allowing user to save/open/cancel options in silverlight.

    Thanks in advance

     

    Thanks

    Vijay

    Wednesday, March 3, 2010 4:52 AM
  • Hi Vijay,

    Thanks for your feedback.

    Maybe you could go to the Microsoft Silverlight Forum: http://social.microsoft.com/Forums/en-US/silverlightit/threads or refer to http://forums.silverlight.net/ to find out some helpful information.

    BTW, another link on How to populate a spreadsheet with data from a real SQL database and create a cool looking chart based on that data, you could refer to: http://blogs.msdn.com/brian_jones/archive/2008/11/04/document-assembly-solution-for-spreadsheetml.aspx

    Hope this helps. If you have any question, please let me know.

    Thanks,

    Lu
    Wednesday, March 3, 2010 5:17 AM
  • Hi Vijay,

    In your scenario, we suggest you create a template in Excel (which contains both data and the chart based on it), then fill the data based on your .xml file.

    We suggest you using the Productivity Tool (download) to achieve this. You could create a template in Excel and open it in Tool, then use "Reflect Code" to see how to generate the code using SDK.

    As for how to import your xml data (change the value of your template data), you could modify the data in the template, and save as another file, then use "Compare Files" -> "View Part Diff" -> "View Part code" to see how to change the values.

    Hope this helps. If you have any question, please let me know.

    Thanks,

    Lu


    what's the specific information about this tool? Where is its documentation?
    Sunday, August 29, 2010 2:33 AM
  • I find an article which gives an answer to your question.

    You can read the article on

    http://janewdaisy.wordpress.com/2011/02/25/fast-export-data-from-database-to-excel-and-generate-chart-without-automation/

    Tuesday, March 1, 2011 5:43 AM
    • Proposed as answer by Learning hard Monday, October 8, 2012 5:35 AM
    Monday, October 8, 2012 5:35 AM