none
copy existing worksheet from workbook and insert it in anthor workbook using c# and open xml. RRS feed

  • Question

  • Hi,

    I need to copy existing worksheet from workbook and insert it in anthor workbook using c# and open xml.

    It's work for integer value but not for string.

    and when i add  this line   SharedStringTablePart newSharedStringTable = newWorkbookPart.AddPart<SharedStringTablePart>(sharedStringTable);

    i have erro message open one instance of the type is allowed for this parent

    any ideas???

    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(filenamesource, filenameTarget, "sheet1");
            }
    
            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 TargetfileName, string sheetName)
            {
                
                   //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(TargetfileName, 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 = SheetName;
                        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();
                }
            }
        }
    }
    

                                                                 
    Wednesday, December 31, 2014 12:23 PM

All replies

  • Hi a_wiseman2010,

    Thanks for posting in MSDN forum.

    According to the description, you were copying existing worksheet into another workbook. However based on the code below:

    WorkbookPart newWorkbookPart = newXLFile.AddWorkbookPart();
    

    It add a new workpart after it open the workbook. Did you want to copy a worksheet to an exiting workbook or a new workbook?

    And also the code works well for me? What's the error message you got? Here is my test step:
    1. create a workbook name workbook1

    2. set range("A1")="test", Range("B2")=1 in Sheet1 from workbook1

    3. create workbook named workbook2 and create a new sheet named Sheet2, write some values into cells

    4. run the code copy Sheet1 from workbook1 to workbook2

    No mater whether I did step 3, the code alwasy copy the worksheet successfully. Did I miss any steps?

    Reards & 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.

    Thursday, January 1, 2015 6:45 AM
    Moderator
  • Hi,

    thks for your reply.

    I want to copy a worksheet from an excel File(filename)  to  anthor excel file(Targetfilename)

    the code alwasy copy just the number value successfully but not the string/text value

    That why I try to add this line to copy the sharedstring from source excel to target excel. 

    SharedStringTablePart newSharedStringTable = newWorkbookPart.AddPart<SharedStringTablePart>(sharedStringTable);

    Friday, January 2, 2015 9:11 AM
  • Hi a_wiseman2010,

    As far as I test, the code copy the strings well and it would replace the orignal workbook with the worksheet from the workbook.

    Would mind sharing a sample project with the demo spreadsheets to help us to understand this issue exactly?

    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, January 2, 2015 9:24 AM
    Moderator