none
Copy Range from particular Sheet in Excel OpenXML Doc to specific location in second OpenXML Excel Sheet (INCLUDING DRAWING contained within Range) RRS feed

  • Question

  • I have been looking for this snippet for quite a while and either:

    1.I am asking Google the wrong question,
    2.I am using the wrong approach or
    3.no one has done this yet (unlikely, I know).

    I have a Template OpenXML Spreadsheet document with Drawings and Data/Computations on 10-15 Sheets with 10-15 per sheet. I wish to copy everything from a particular range on a particular sheet from the template file ie.['SheetZ1'!$X1$#:$Y1$#] of file1.xlsx to a particular position in a particular sheet in the output OpenXML spreadsheet file ie.['SheetZ2'!$X2$#:$Y2$#] in file2.xlsx. If it helps, each of the ranges are in a "Named Range" and I would perfer to address them by Range Name.

    So my business problem is: given a template spreadsheet full of Named Ranges containing Data, Calulations and Drawings, go to sheet Z and copy that range to my output OpenXML Spreadsheet into its correct location.

    If anyone has a snippet, knows of a snippet, or knows someone with a snippet, I would really appreciate it. I would like the snippet in VB.Net however I could convert C# as well.

    I do not wish to come off as a jerk or ungrateful, however I rarely find much value in comments like "well I have never done it, however you should be able to ... and then I think you can move that to .... and then maybe do this... or something like that..." I really just need a working snippet and I will fill in the rest.

    Monday, September 17, 2012 2:02 PM

All replies

  • Hi Scott,

    Thanks for posting in the MSDN Forum.

    As far as I know, that image will not content in the cells' please clarify whether you will get all of the image data for the specific worksheet?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, September 18, 2012 6:32 AM
    Moderator
  • No, I looked at the content of the cells using a code snippet on the openxml sdk and they do not contain the drawing objects. Using the OpenXML viewer in the SDK, I can see ALL the drawing objects. I would like a snippet which will return only those drawing objects with their position within the boundries of the cell range ON A PARTICULAR SHEET. So I need to determine for each object, is it on the sheet AND is it in the Named Range. I have not seen anything explaining how the objects sit on a spread sheet, however I would assume that they sit on a layer on top of the cells and have some absolute positional addressing based on upper left corner of A1, OR they sit on top of the cell layer with anchors relative to the cells.

    Tom - I know there are MS coders manipulating the OpenXML document in the Excel Application source code based on the end users actions. Is there materials, code snippets, libraries, White Papers, DOM Charts/documentation etc. used by the MS guys and girls that may be available? Excel knows which objects go on what spreadsheet and where to display them. I essentially need to be able to determine the same thing. I know there are some smart coders out there (especially in MS) writing and maintaining code which does this same thing.

    Thanks for the response!

    M. Scott Blalock



    • Edited by Scott Blalock Tuesday, September 18, 2012 12:14 PM Clarifying answer
    Tuesday, September 18, 2012 12:04 PM
  • Further inspection of the OpenXML package seems to indicate that within the xml doc for SheetX, there exist an DrawingX.xml document which contains the elements in the Drawing ie, Straight Connectors, FreeForm Shapes, etc.
    These elements seem to have a relative location (a Two Cell Anchor) but also seem like they may have a Absolute X/Y coordinate also (unless I am reading the xml tags poorly).

    Since I want not only drawings but regular cells with data, formulas, formatting, etc, it seems that a code snippet emulating Excel's "Copy Selection/Paste here" abilities in response to the user electing a group of cells, selecting copy and then clicking on another sheet and selecting paste.

    Continuing my research....  

    Tuesday, September 18, 2012 1:35 PM
  • Hi Scott,

    I will involve some experts into your issue to see whether they can help you. There might be some time delay, thanks for your patience.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, September 19, 2012 7:05 AM
    Moderator
  • The scope of this problem would require more time than allowed on a single forum post. If this is urgent I would open a support case with our Visual Studio Tools for Office team.

    Will Buffington
    Microsoft Excel Support

    Thursday, September 20, 2012 8:46 PM
  • Hmm thanks for the effort...

    M. Scott Blalock
    MSDN Member


    Friday, September 21, 2012 11:45 AM