none
Saving a grid of data with styles in a database RRS feed

  • Question

  • Hi

    I'm investigating ways of prepare, maintain and store flexible grid data including styles & formats in a database. Target platform for presentation is a Word document.

    The goal is to be able to specify/design a grid for my application, with headers and data in specific formats, like in the image below which is created in Excel. The final result will be used in a Word document.

    The grid "template" is then stored in a database and finally used for a questionarie application where data is filled into the grid by a user.
    Last step is to save the grid with the newly added information in the database.

    I have been looking into datagrid samples but you have to maintain styles separately.
    I have also been looking into using XML, which seems like a natural selection, but this was a vast area of knowledge which I currently don't posess.

    Is there anybody who has any ideas of how to accomplish this task?


    Best Regards Peter Karlström Midrange AB, Sweden


    Thursday, August 27, 2015 8:04 AM

Answers

  • Hi Peter,

    I agree with Cindy that about translating the Excel table to Word document. We can use Office automation to past the table cross the application and then we can save the table template with XML to the database so that we can recreate it later.

    Here is an example that insert the table into a new document via an exists table in the document:

    Dim a As String
    a = ActiveDocument.Tables(1).Range.XML
    'we can persit the table's xml to database here, then we can use this xml to recreate the table when you want
     Set doc = Application.Documents.Add
     
     Selection.InsertXML a

    Hope it is hlepful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 31, 2015 6:07 AM
    Moderator

All replies

  • HI Peter

    I don't understand what this has to do with Office development? Are you in the right forum?


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, August 27, 2015 5:00 PM
    Moderator
  • Hi Cindy

    Sorry, I missed that part of the presentation. A clarified the post a bit to be more specific.


    Best Regards Peter Karlström Midrange AB, Sweden

    Thursday, August 27, 2015 8:14 PM
  • Hi Peter,

    >>Saving a grid of data with styles in a database<<

    Based on my understanding, we can persit the property of table so that you can restore it after you recreating.

    Aso we can seriaize the table template via XML and then we can reinsert the table via its XML. Both the property and XML can deserialize the table in database.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, August 28, 2015 2:57 AM
    Moderator
  • Hello

    Thank you for your reply.

    This sounds interresting, but I haven't a clue on how to do this.

    Can you elaborate a litte more and maybe point to some samples?

    I understand that I can create a VST-application för Excel to extract and save a grid-part, but how will the extraction part be done?

    Once in the database, how do I put it in a Word document after reading it?

    Thanks in advance


    Best Regards Peter Karlström Midrange AB, Sweden

    Friday, August 28, 2015 8:49 AM
  • Hi Peter

    Do you have any particular kind of database in mind? Since this is an Office forum, my instincts think in terms of Access. Access can certainly import data directly from Excel, although "styling" will be lost.

    I guess the first question that comes to my mind is: Why use Excel at all? Why not enter data directly into the database?

    Considering Fei's thoughts: there will be no possibility to directly transfer Excel to Word via XML, whether via a database or directly from Excel to Word. It would require code that performs a XSLT ("Transformation"). If this is all a foreign language to you, I think a more conventional (COM) approach would be prefereable. (And I recall an earlier attempt you made to go the OpenXML route with something?)

    If we leave XML out of the picture, then data can go directly from Excel to Word with formatting using Copy/Paste. It can also be done by "inserting" the Excel workbook/-sheet as HTML.

    Access can export a datatable, query or report result to RTF, which will do its best to reproduce the formatting of the orginal, but I don't know whether that would be adequate - you'd need to test.

    If it turns out the data goes from the database to Word via code, then formatting the end-result will need to be done by the code. How much work this requires depends on whether you can use a prepared template to create the Word document so that you can leverage styles that will apply the formatting, or whether it all needs to be done in code. Theoretically, the formatting information can also be stored in the database and read by the code, but if you need the formatting in Excel to be stored that will also require code.


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, August 28, 2015 3:30 PM
    Moderator
  • Hi Peter,

    I agree with Cindy that about translating the Excel table to Word document. We can use Office automation to past the table cross the application and then we can save the table template with XML to the database so that we can recreate it later.

    Here is an example that insert the table into a new document via an exists table in the document:

    Dim a As String
    a = ActiveDocument.Tables(1).Range.XML
    'we can persit the table's xml to database here, then we can use this xml to recreate the table when you want
     Set doc = Application.Documents.Add
     
     Selection.InsertXML a

    Hope it is hlepful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 31, 2015 6:07 AM
    Moderator
  • Hi Fei

    One problem with your suggestion is that Range.XML is Word 2003, not Word 2007 and later. That would have to be Range.WordOpenXML to be certain you're getting everything...


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, August 31, 2015 2:24 PM
    Moderator
  • Hi Cindy,

    Thanks for the correcting, however it seems that this property also works in Word 2007 and later. I tested it in Word 2013. In addition, it is glad to learn more :)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 1, 2015 1:57 AM
    Moderator
  • Hi Cindy

    We have SQL Server in mind, since this project will involve +10 users and because of some other designchoices too.

    One of the important parameters here is to maintain styles and formatting of the grid, hence the mentioning of Excel, with which you have a great design-tool for the grid.

    I have laborated with Excel and saving a workbook as XML Worksheet 2003, which produces an XML-file with structure and styles maintained.

    If I could find a way of presenting this xml-data in Word, in a Windows forms application and on a web-page my goal is reached.


    Best Regards Peter Karlström Midrange AB, Sweden

    Tuesday, September 1, 2015 6:59 AM
  • Hi Peter,

    >>I have laborated with Excel and saving a workbook as XML Worksheet 2003, which produces an XML-file with structure and styles maintained.

    If I could find a way of presenting this xml-data in Word, in a Windows forms application and on a web-page my goal is reached.<<

    There is no such method we can directly pass the XML of Excel table into Word. However we can transfer it by copy/paste since the Word application will transer it for us by default. For example, you can copy the table from Excel to Word manually, then save the XML into database. Also you can use Office object model to copy/paste using code.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, September 1, 2015 7:15 AM
    Moderator
  • Hi Fei

    <<Thanks for the correcting, however it seems that this property also works in Word 2007 and later. I tested it in Word 2013. In addition, it is glad to learn more :)>>

    It can work, but it won't support anything introduced after Word 2003. For example, if the table cells contain content controls the content controls won't exist in the XML.


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, September 1, 2015 2:54 PM
    Moderator
  • Hi Peter

    <<I have laborated with Excel and saving a workbook as XML Worksheet 2003, which produces an XML-file with structure and styles maintained.

    If I could find a way of presenting this xml-data in Word, in a Windows forms application and on a web-page my goal is reached.>>

    It's not going to happen - at least, not without a lot of extensive coding on your side. Only Excel understands the 2003 XML file format, and even that is no longer used except for purposes of backwards compatibility.

    Your best bet might be to try saving the Excel file to the HTML file format. I understand older versions of Excel do this differently than more recent ones, so you might want to try both. If that gives acceptable results try using the HTML in a web page and opening it in Word.


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, September 1, 2015 2:58 PM
    Moderator
  • Hi Cindy & Fei

    Thanks for your replies

    The HTML-format will not do, because it won't be editable.

    After examining Fei's sample Word-code, I think this will actually do quite well.

    This is the typical scenario in our design:

    Step 1 - Create the grid
    Here we add columns, rows and formatting of the grid.
    The result will be a table where answers can be filled in. Here I felt an Excel-solution because of the flexibility among other things, but Word will do fine.

    Step 2 - Save the grid
    Here we save the grid complete with formatting as WordOpenXML. Still using Word from COM/VSTO.

    Step 3 - Use the grid in a questionarie
    Here the grid will be one of several questions in a questionarie. The questionarie will be used in an interview situation, and can be in a printed version on paper, or a webbased form.  Answers will be filled in during the interview.
    The printed version is created using Word. The webbased form will probably be an ASP.NET MVC-type solution.

    If the interview is held using a printed questionarie, the answers will be filled in separately after the interview in the webbased form solution.

    If the interview is held using the webbased interface, answers from the grid will be directly saved to the database together with the original formats headers and so on.

    Step 4 - Creating a report
    Here a report is created which will be delivered to the customer/interviewes. The report will be a PDF created from a Word document.

    To sum up this, we can use Word to create and maintain the grids from the database where it is saved as plain XML-text. Steps 1 and 2.

    We can also use Word when creating av printed version and the report.

    Finally I have to solve the webbased form issue, but I will take this question to the appropriate forum.

    Thanks a lot for your help


    Best Regards Peter Karlström Midrange AB, Sweden

    Tuesday, September 1, 2015 4:07 PM