none
How to copy spreadsheet to another excel file in Openxml? RRS feed

All replies

  • Hi Hants,

    Thank you for posting.

    Please take a look at this blog article: http://blogs.msdn.com/b/brian_jones/archive/2009/02/19/how-to-copy-a-worksheet-within-a-workbook.aspx which introduces how to copy a worksheet.

    I hope you can get some useful information from it and just feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 23, 2011 9:48 AM
  • Actually I have searched this blog and have tried to get similar thing done in different workbooks, but have no lucky. Yes, this blog shows us something useful information but it's not enough for sheet copying between different workbooks. I will appreciate someone could give a sample for this topic.
    Sunday, March 27, 2011 2:44 AM
  • Paste my test code, anybody can figure out why it does not work?

      static void CopySheet2(string filename, string sheetName, string clonedSheetName, string strTargetFileName)
            {
                //Open workbook
                using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(filename, true))
                {
                    WorkbookPart sourceWorkbookPart = mySpreadsheet.WorkbookPart;
                    //Get the source sheet to be copied
                    WorksheetPart sourceSheetPart = GetFirstWorkSheetPart(sourceWorkbookPart, sheetName);

                    //Take advantage of AddPart for deep cloning
                    using (SpreadsheetDocument targetSpreadSheet = SpreadsheetDocument.Open(strTargetFileName, true))
                    {
                        WorkbookPart targetWorkbookPart = targetSpreadSheet.WorkbookPart;

                        SpreadsheetDocument tempSpreadSheetDoc = SpreadsheetDocument.Create(new MemoryStream(), mySpreadsheet.DocumentType);
                        WorkbookPart tempWorkbookPart = tempSpreadSheetDoc.AddWorkbookPart();
                        WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart<WorksheetPart>(sourceSheetPart);
                       
                       
                        //Add cloned sheet and all associated parts to workbook
                        WorksheetPart clonedSheet = targetWorkbookPart.AddPart<WorksheetPart>(tempWorksheetPart);

                        //Table definition parts are somewhat special and need unique ids...so let's make an id based on count
                        //int numTableDefParts = sourceSheetPart.GetPartsCountOfType<TableDefinitionPart>();
                        int numTableDefParts = targetWorkbookPart.Workbook.Sheets.Count() + 1;
                        tableId = numTableDefParts;

                        //Clean up table definition parts (tables need unique ids)
                        if (numTableDefParts != 0)
                            FixupTableParts(clonedSheet, numTableDefParts);
                        //There should only be one sheet that has focus
                        CleanView(clonedSheet);

                        //Add new sheet to main workbook part
                        Sheets sheets = targetWorkbookPart.Workbook.GetFirstChild<Sheets>();
                        Sheet copiedSheet = new Sheet();
                        copiedSheet.Name = clonedSheetName;
                        copiedSheet.Id = targetWorkbookPart.GetIdOfPart(clonedSheet);
                        copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;
                        sheets.Append(copiedSheet);
                        //Save Changes

                        targetWorkbookPart.Workbook.Save();
                       
                    }
                }
            }

     

      static WorksheetPart GetWorkSheetPart(WorkbookPart workbookPart, string sheetName)
            {
                //Get the relationship id of the sheetname
                string relId = workbookPart.Workbook.Descendants<Sheet>()
                .Where(s => s.Name.Value.Equals(sheetName))
                .First()
                .Id;
                return (WorksheetPart)workbookPart.GetPartById(relId);
            }

            static WorksheetPart GetFirstWorkSheetPart(WorkbookPart workbookPart, string sheetName)
            {
                //Get the relationship id of the sheetname
                string relId = workbookPart.Workbook.Descendants<Sheet>().First().Id;
                //.Where(s => s.Name.Value.Equals(sheetName))
                //.First()
                //.Id;
                return (WorksheetPart)workbookPart.GetPartById(relId);
            }

            static void CleanView(WorksheetPart worksheetPart)
            {
                //There can only be one sheet that has focus
                SheetViews views = worksheetPart.Worksheet.GetFirstChild<SheetViews>();
                if (views != null)
                {
                    views.Remove();
                    worksheetPart.Worksheet.Save();
                }
            }

            static void FixupTableParts(WorksheetPart worksheetPart, int numTableDefParts)
            {
                //Every table needs a unique id and name
                foreach (TableDefinitionPart tableDefPart in worksheetPart.TableDefinitionParts)
                {
                    tableId++;
                    tableDefPart.Table.Id = (uint)tableId;
                    tableDefPart.Table.DisplayName = "CopiedTable" + tableId;
                    tableDefPart.Table.Name = "CopiedTable" + tableId;
                    tableDefPart.Table.Save();
                }
            }

     

    Friday, May 13, 2011 9:14 AM
  • hi Guys,

     

    me too facing the same issue.. please help me here

    Saturday, January 7, 2012 11:29 AM
  • Hi,

    Did anyone find a solution for this? I am also facing the same issue while copying a worksheet from one xlsx to another xlsx / xlsm file. Thanks.

    Tuesday, January 10, 2012 4:20 AM
  • Hi Patrick (and Balaji)

    I assume you're asking how to copy a worksheet from a xlsx to another xlsx. Exactly what's needed depends on what's in the worksheet you want to copy, and what's already present in the target workbook.

    For example, if I have a worksheet that only has numbers it's different than if I have a worksheet that also has text that's in the SharedStrings part.

    IOW, besides the Worksheet itself you need to be sure that the other parts are also copied.

    I used this article as a starting point
    http://blogs.msdn.com/b/brian_jones/archive/2009/02/19/how-to-copy-a-worksheet-within-a-workbook.aspx
    In combination with
    http://www.codeproject.com/KB/office/ExcelOpenXMLSDK.aspx

    In the article where they copy a sheet within a workbook, these parts are already present so this step isn't necessary in that scenario.

    My sample code, that follows, only deals with the SharedStrings part, but it should give you an idea...

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.IO;
    using System.Reflection;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    namespace CopySheetToNewWorkbook
    {
        class Program
        {
    
           static int tableId = 0;
    
            static void Main(string[] args)
            {
                string filename = @"C:\Test\workfileTestCopySheet.xlsx";
                File.Copy(@"C:\Test\TestCopySheet.xlsx", filename, true);
                CopySheet(filename, "Sheet1", "CopiedSheet");
            }
    
            static WorksheetPart GetWorkSheetPart(WorkbookPart workbookPart, string sheetName)
            {
                //Get the relationship id of the sheetname
                string relId = workbookPart.Workbook.Descendants<Sheet>()
                    .Where(s => s.Name.Value.Equals(sheetName))
                    .First()
                    .Id;
    
                return (WorksheetPart)workbookPart.GetPartById(relId);
            }
    
            static void CopySheet(string filename, string sheetName, string clonedSheetName)
            {
                
                   //Open workbook
                using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(filename, true))
                {
                    WorkbookPart workbookPart = mySpreadsheet.WorkbookPart;
                    //Get the source sheet to be copied
                    WorksheetPart sourceSheetPart = GetWorkSheetPart(workbookPart, sheetName);
    SharedStringTablePart sharedStringTable = workbookPart.SharedStringTablePart;
                    //Take advantage of AddPart for deep cloning
                    using (SpreadsheetDocument newXLFile = SpreadsheetDocument.Create(@"C:\Test\CopyResult.xlsx", mySpreadsheet.DocumentType))
                    {
                        WorkbookPart newWorkbookPart = newXLFile.AddWorkbookPart();
                        SharedStringTablePart newSharedStringTable = newWorkbookPart.AddPart<SharedStringTablePart>(sharedStringTable);
                        WorksheetPart newWorksheetPart = newWorkbookPart.AddPart<WorksheetPart>(sourceSheetPart);
                        //Table definition parts are somewhat special and need unique ids...so let's make an id based on count
                        int numTableDefParts = sourceSheetPart.GetPartsCountOfType<TableDefinitionPart>();
                        tableId = numTableDefParts;
    
                        //Clean up table definition parts (tables need unique ids)
                        if (numTableDefParts != 0)
                            FixupTableParts(newWorksheetPart, numTableDefParts);
                        //There should only be one sheet that has focus
                        CleanView(newWorksheetPart);
    
                        var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
    
                        //Worksheet ws = newWorksheetPart.Worksheet;
                        Workbook wb = new Workbook();
                        wb.Append(fileVersion);
                        
                        //Add new sheet to main workbook part
                        Sheets sheets = null;
                        //int sheetCount = wb.Sheets.Count();
                        if (wb.Sheets != null)
                        { sheets = wb.GetFirstChild<Sheets>(); }
                        else
                        { sheets = new Sheets(); }
    
                        Sheet copiedSheet = new Sheet();
                        copiedSheet.Name = clonedSheetName;
                        copiedSheet.Id = newWorkbookPart.GetIdOfPart(newWorksheetPart);
                        if (wb.Sheets != null)
                        { copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1; }
                        else { copiedSheet.SheetId = 1; }
    
                        sheets.Append(copiedSheet);
                        newWorksheetPart.Worksheet.Save();
    
                        wb.Append(sheets);
                        //Save Changes
                        newWorkbookPart.Workbook = wb;
                        wb.Save();
                        newXLFile.Close();
                    }
                }
            }
    
            static void CleanView(WorksheetPart worksheetPart)
            {
                //There can only be one sheet that has focus
                SheetViews views = worksheetPart.Worksheet.GetFirstChild<SheetViews>();
    
                if (views != null)
                {
                    views.Remove();
                    worksheetPart.Worksheet.Save();
                }
            }
    
            static void FixupTableParts(WorksheetPart worksheetPart, int numTableDefParts)
            {
                //Every table needs a unique id and name
                foreach (TableDefinitionPart tableDefPart in worksheetPart.TableDefinitionParts)
                {
                    tableId++;
                    tableDefPart.Table.Id = (uint)tableId;
                    tableDefPart.Table.DisplayName = "CopiedTable" + tableId;
                    tableDefPart.Table.Name = "CopiedTable" + tableId;
                    tableDefPart.Table.Save();
                }
            }
        }
    }
    

     


    Cindy Meister, VSTO/Word MVP
    Tuesday, January 10, 2012 4:33 PM
    Moderator
  • Hi again

    One way you can check which parts a spreadsheet file contains is to loop the Parts via the underlying System.IO.Packaging object:

                    System.IO.Packaging.PackagePartCollection parts = mySpreadsheet.Package.GetParts();
                    foreach (System.IO.Packaging.PackagePart p in parts)
                    {
                        System.Diagnostics.Debug.Print(p.Uri + " -- " + p.ContentType);
                    }
    
    

    You can use this information to determine which kinds of parts you need to pick up, in conjunction with checking the Relationships in the file.

    Another possibilty is to check the existence of the various kinds of parts that can depend from a worksheet. For example:
      DrawingsPart dp = sourceSheetPart.DrawingsPart;
      if (dp == null) { System.Diagnostics.Debug.Print("no drawing part"); }

     There's an entire list of parts that will appear in Intellisense (but SharedStrings isn't one of them).


    Cindy Meister, VSTO/Word MVP
    Tuesday, January 10, 2012 5:18 PM
    Moderator
  • This may be indirect .. but it works perfect ...

    void CopySheet(string filename, string sheetName, string tempFileName)
            {
                File.Copy(filename, tempFileName , true);
                using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(tempFileName, true))
                {
                    IEnumerable<Sheet> sheets = mySpreadsheet.WorkbookPart.Workbook.Descendants<Sheet>()
                        .Where((s) => s.Name.InnerText.ToUpper() != sheetName.ToUpper());
                    while (sheets.Count() > 0)
                    {
                        sheets.First().Remove();
                    }                
                    mySpreadsheet.WorkbookPart.Workbook.Save();
                }
            }


    • Proposed as answer by muik Monday, October 22, 2012 2:46 PM
    Monday, June 25, 2012 9:35 PM
  • Hi,

    My name is Ozgur Topcu. I am one of the developers in the OfficeWriter team at SoftArtisans. I came across this thread about copying a worksheet into a different XLSX workbook. The solution presented here using the OOXML SDK could very well address the issue but as you can see, it requires a good chunk of code and logic to keep track of the things that should be copied across, such as drawing parts, shared strings etc. I thought it would be useful here to mention that all of that coding and logic could be achieved with just one method call when using the OfficeWriter API. In the latest version 8.2 of OfficeWriter, the Worksheets.CopySheet() method in the ExcelApplication API now supports copying a worksheet between different XLSX workbooks - http://wiki.softartisans.com/display/EW8/Worksheets.CopySheet%28Worksheet%2C+Int32%2C+String%29 . If you have any questions about the OfficeWriter API, just feel free to post it.

    Thank you.

    Ozgur Topcu

    SoftArtisans

    Tuesday, July 10, 2012 12:36 AM
  • The idea of copying the whole workbook and then to get rid of the sheets we don't want is the ideal solution for most situations if you want to avoid writing lots of code.

    However, if the worksheet you want to keep contains reference to data in other sheets, you risk breaking the worksheet, so it's not a solution that translates to these scenarios:

    • Worksheets with formulas that reference other sheets;
    • Worksheets that feature "dropdown lists" where the list is defined in another sheet.

    Then again this is just off the top of my head, since I'm about to write such a functionality.

    Monday, October 22, 2012 2:54 PM