none
Help in copying worksheets between two Excel workbooks and preserv any image

    Question

  • Hi

    In my project I try to create Excel workbooks using Open XML.

    One of the steps is to copy all sheets from one workbook to another, and in the process handle namecollisions and images which may be located in the worksheet.

    I haven't found any sample solutions here or in other Open XML forums.

    Does anybody have a working solution for this?


    Best Regards Peter Karlström Midrange AB, Sweden

    Tuesday, January 29, 2013 4:26 PM

Answers

  • Hi

    I gave up digging into the various not functioning suggestions in this forum and tested another approach; http://epplus.codeplex.com/

    This open source Project covers all the demands in my Project regarding worksheet copying, cell formatting and so on. Great work!

    I can recomend this library.


    Best Regards Peter Karlström Midrange AB, Sweden

    Tuesday, April 16, 2013 1:14 PM

All replies

  • Hi Peter,

    Thanks for posting in the MSDN Forum.

    It's based on my experience that you can use the way which I mentioned in http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/947b375f-add1-4f05-8922-52b97cabe0f9. It's a similar issue. And I think Excel solution will easier than PowerPoint. Please try it. If you have any trouble to do that please feel free to let me know.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 30, 2013 6:33 AM
    Moderator
  • Hi Tom

    I tried your suggestion to use the Powerpoint-approach for my Excel-function, but soon realized that there wsa to many differences between the two application for me to carry on.

    Therefore I looked around a little bit more and found a description about how to merge several workbooks using Open XML in this thread:http://msdn.microsoft.com/en-in/library/ee396252(en-us,office.12).aspx

    I changed the objective to merging two already existing workbooks.
    No errors occur when I run the application, but when I open the resulting workbook, there is som errors.
    First a message (in swedish) about unreadable content i the workbook. There I can choose Yes or No for resetting the workbook.
    Then a repair-message: Removed records: Cellinformation from /xl/worksheets/sheet16.xml-del
    When the workbook is opened ther is two new sheets, but in the workbook I merged from there was only one.
    The correct copied sheet contains the cellinformation correct.

    Below is the complete code in VB:

        Public Sub EXInsertFDO(ByVal exlTarget As String, ByVal exlSource As String)
    
            Dim mergedWorkbookPart As WorkbookPart = Nothing
            Dim mergedWorksheetPart As WorksheetPart = Nothing
            Dim childWorksheetPart As WorksheetPart = Nothing
            Dim mergedWorkbookSheets As Sheets = Nothing
            Dim childWorkbookSheets As Sheets = Nothing
            Dim newMergedSheet As Sheet = Nothing
            Dim mergedSheetData As SheetData = Nothing
            Dim childSharedStringTablePart As SharedStringTablePart = Nothing
    
            ' Open target workbook package.
            Using mergedWorkbook As SpreadsheetDocument = SpreadsheetDocument.Open(exlTarget, True)
    
                ' Get workbook part from target package
                mergedWorkbookPart = mergedWorkbook.WorkbookPart()
    
                ' Get the Sheets element in the merged workbook for use later.
                mergedWorkbookSheets = mergedWorkbookPart.Workbook.GetFirstChild(Of Sheets)()
    
                ' Get the Shared String Table part in the targeted workbook.
                mergedSharedStringTablePart = mergedWorkbookPart.SharedStringTablePart()
    
                ' For each source workbook to merge...
                'For Each workbookName As String In sourceWorkbookNames
                ' Open the source workbook. The following will throw an
                ' exception if the source workbook does not exist.
                Using childWorkbook As SpreadsheetDocument = SpreadsheetDocument.Open(exlSource, False)
                    ' Get the Sheets element in the source workbook.
                    childWorkbookSheets = childWorkbook.WorkbookPart.Workbook.GetFirstChild(Of Sheets)()
                    ' Get the Shared String Table part of the child workbook.
                    childSharedStringTablePart = childWorkbook.WorkbookPart.SharedStringTablePart
    
                    ' For each worksheet in the child workbook...
                    For Each childSheet As Sheet In childWorkbookSheets
                        ' Get a worksheet part for the child worksheet using it's relationship Id. 
                        childWorksheetPart = DirectCast(childWorkbook.WorkbookPart.GetPartById(childSheet.Id), WorksheetPart)
    
                        ' Add a worksheet part to the merged workbook based on the child worksheet.
                        mergedWorksheetPart = mergedWorkbookPart.AddPart(Of WorksheetPart)(childWorksheetPart)
    
                        ' There should be only one worksheet that is set as the main view.
                        CleanView(mergedWorksheetPart)
    
                        ' Create a Sheet element for the new sheet in the targeted workbook.
                        newMergedSheet = New Sheet()
    
                        ' Set the Name, Id, and SheetId attributes of the new Sheet element.
                        newMergedSheet.Name = GenerateWorksheetName(mergedWorkbookSheets, childSheet.Name.Value)
    
                        newMergedSheet.Id = mergedWorkbookPart.GetIdOfPart(mergedWorksheetPart)
    
                        newMergedSheet.SheetId = CUInt(mergedWorkbookSheets.ChildElements.Count) + 1
    
                        ' Add the new Sheet element to the Sheets element in the targeted workbook.
                        mergedWorkbookSheets.Append(newMergedSheet)
    
                        ' Get the SheetData element of the new worksheet part in the targeted workbook.
                        mergedSheetData = mergedWorksheetPart.Worksheet.GetFirstChild(Of SheetData)()
    
                        'For each row of data...
                        For Each row As Row In mergedSheetData.Elements(Of Row)()
                            ' For each cell in the row...
                            For Each cell As Cell In row.Elements(Of Cell)()
                                ' If the cell is using a shared string, merge 
                                ' the string from the child workbook into the targeted workbook. 
                                If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
                                    ProcessCellSharedString(mergedWorksheetPart, cell, mergedSharedStringTablePart, childSharedStringTablePart)
                                End If
                            Next
                        Next
                    Next
                End Using
    
                'Save the changes to the targeted workbook.
                mergedWorkbookPart.Workbook.Save()
            End Using
        End Sub
    
        Private Shared Sub CleanView(worksheetPart As WorksheetPart)
            'There can only be one sheet that has the focus.
            Dim views As SheetViews = worksheetPart.Worksheet.GetFirstChild(Of SheetViews)()
    
            If views IsNot Nothing Then
                views.Remove()
            End If
        End Sub
    
        Private Shared Function GenerateWorksheetName(mergedWorkbookSheets As Sheets, baseSheetName As String) As String
            ' Generate a name for the worksheet.
            ' Must be unique and < 32 characters long.
            Dim count As Integer = 0
            Dim newBaseSheetName As String = baseSheetName
    
            ' While the new sheet name is already used...
            While IsSheetNameInUse(mergedWorkbookSheets, newBaseSheetName)
                count += 1
    
                ' Decrease the length of the sheet's base name to keep the
                ' length under 32 characters.
                If baseSheetName.Length >= (28 - Digits(count)) Then
                    newBaseSheetName = baseSheetName.Substring(0, 28 - Digits(count))
                Else
                    newBaseSheetName = baseSheetName
                End If
    
                newBaseSheetName = newBaseSheetName & ("(" & count & ")")
            End While
    
            Return newBaseSheetName.ToString()
        End Function
    
        Private Shared Function IsSheetNameInUse(mergedWorkbookSheets As Sheets, sheetName As String) As Boolean
            For Each mergedSheet As Sheet In mergedWorkbookSheets
                If mergedSheet.Name.Value.Equals(sheetName) Then
                    Return True
                End If
            Next
    
            Return False
        End Function
    
        Private Shared Function Digits(number As Integer) As Integer
            If number <> 0 Then
                Dim n As Integer = 0
    
                While number > 0
                    n += 1
                    number /= 10
                End While
    
                Return n
            Else
                Return 1
            End If
        End Function
    
        Private Shared Sub ProcessCellSharedString(mergedWorksheetPart As WorksheetPart, cell As Cell, mergedSharedStringTablePart As SharedStringTablePart, childSharedStringTablePart As SharedStringTablePart)
            ' The provided cell is using a shared string, so the cell value
            ' is an index into the workbook's shared string table, pointing to
            ' the actual string value. Check the shared strings in the merged
            ' workbook. If there is not already a matching shared string 
            ' item, copy the shared string item from the shared string 
            ' table in the child workbook to the shared string table in the 
            ' merged workbook. Fix up the shared string index in the cell 
            ' value in either case.
    
            Dim newMergedSharedStringItem As SharedStringItem = Nothing
            Dim childSharedStringItem As SharedStringItem = Nothing
    
            Dim matchedIndex As Integer = -1
            Dim currentIndex As Integer = 0
            Dim sharedStringIndex As Integer = Convert.ToInt32(cell.CellValue.Text)
    
            childSharedStringItem = childSharedStringTablePart.SharedStringTable.Elements(Of SharedStringItem)().ElementAt(sharedStringIndex)
    
            ' See if there is a shared string item in the merged workbook
            ' that matches the item in the child workbook.
            For Each mergedSharedStringItem As SharedStringItem In mergedSharedStringTablePart.SharedStringTable
                If childSharedStringItem.OuterXml = mergedSharedStringItem.OuterXml Then
                    ' There is a match, so use the existing shared string item.
                    matchedIndex = currentIndex
                    Exit For
                End If
                currentIndex += 1
            Next
    
            ' If there was a match...
            If matchedIndex <> -1 Then
                ' then use the existing shared string item.
                cell.CellValue.Text = matchedIndex.ToString()
            Else
                ' else add the shared string item from the child workbook to the
                ' merged workbook and set the cell in the merged worksheet to
                ' point to it. The index into the shared string table is 0-based 
                ' so we use the current shared string item count for the index 
                ' used by the cell so that it's pointing at the correct item 
                ' once we add the item and increment the item count below.
    
                cell.CellValue.Text = mergedSharedStringTablePart.SharedStringTable.Count.ToString()
    
                ' Add the new string item to the table.
                newMergedSharedStringItem = DirectCast(childSharedStringItem.Clone(), SharedStringItem)
                mergedSharedStringTablePart.SharedStringTable.AppendChild(Of SharedStringItem)(newMergedSharedStringItem)
    
                ' Increment the count of shared string items.
                mergedSharedStringTablePart.SharedStringTable.Count.Value += 1
                mergedSharedStringTablePart.SharedStringTable.UniqueCount.Value += 1
    
                ' Save the changes to the shared string table in the merged
                ' workbook.
                mergedSharedStringTablePart.SharedStringTable.Save()
            End If
    
            ' Save the changes to the new worksheet in the merged workbook.
            mergedWorksheetPart.Worksheet.Save()
        End Sub
    

    Where in all this is the error?

    Thanks in advance


    Best Regards Peter Karlström Midrange AB, Sweden

    Friday, February 01, 2013 3:17 PM
  • Hi Petter,

    It seems that mergedworksheetPart.Workbook hasn't been initialized due to I never find nitialization process for worksheet node in you provided code. And I will supplement that not all string value will stored in SharedStringTablePart. That just a recommend option for users. Some spreadsheet document especially which created via OpenXml will use Inlinestring instead of store string value in SharedstringTable. Please consider this point in order to avoid missing data in you new sheet.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 05, 2013 6:31 AM
    Moderator
  • Hi Tom

    This is a little less information than I had hoped for:

    Where do I put the "initialization process for worksheet node" and how does it look like? If this is not done in my code, then what is "mergedWorkbookSheets = mergedWorkbookPart.Workbook.GetFirstChild(Of Sheets)()"?

    You mentioned that Excel was very like Powerpoint in this copying issue, but with sharedStringTable and other unmentioned places where information is stored, this is not my understanding.

    This was the only sample of Open XML code I could find which in a tiny way addressed my problem.
    If there are other samples of copying worksheets between two existing Excel spreadsheets with Open XML, please point me in that direction.

    There are two things in this which often is mixed: There is Open XML SDK support, and then we have raw XML. Some samples mix the two togehter and makes this very hard to understand and implement in your own solutions.

    Can you help me with this copying issue?


    Best Regards Peter Karlström Midrange AB, Sweden

    Tuesday, February 05, 2013 7:04 AM
  • Hi Peter,

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

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 06, 2013 5:11 AM
    Moderator
  • Hello Peter,

    While copying the Excel Sheets, you need to take of the relationships, themes etc associated with the Sheet. There is no readymade sample available that address all the possible scenarios. If there is any scenario that is not implemented by the code, a warning message will be displayed in Excel about unreadable content.

    Your question falls into paid support category which requires more in depth level of support. Please visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    The following links may be helpful for you before opening the support incident.

    http://blogs.msdn.com/b/brian_jones/archive/2009/02/19/how-to-copy-a-worksheet-within-a-workbook.aspx

    http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/d0971ec0-7d22-4c38-b686-b32e6bd01d44

    Thanks,

    Sreerenj G Nair

    Monday, February 11, 2013 9:27 PM
  • Hi

    I gave up digging into the various not functioning suggestions in this forum and tested another approach; http://epplus.codeplex.com/

    This open source Project covers all the demands in my Project regarding worksheet copying, cell formatting and so on. Great work!

    I can recomend this library.


    Best Regards Peter Karlström Midrange AB, Sweden

    Tuesday, April 16, 2013 1:14 PM