コード例 #1
0
        public WorksheetPart CopySheet(WorkbookPart workbookPart, WorksheetPart sourceSheetPart, string clonedSheetName)
        {
            //Il faut uiliser AddPart() qui est bien plus puissante que AddNewPart pour le clonage de WorksheetPart
            SpreadsheetDocument tempSheet         = SpreadsheetDocument.Create(new MemoryStream(), SpreadsheetDocumentType.Workbook);
            WorkbookPart        tempWorkbookPart  = tempSheet.AddWorkbookPart();
            WorksheetPart       tempWorksheetPart = tempWorkbookPart.AddPart <WorksheetPart>(sourceSheetPart);
            //Ajoute le clone et ses fils au workbook
            WorksheetPart clonedSheet = workbookPart.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 tableId          = numTableDefParts;

            //Clean up table definition parts (tables need unique ids)
            if (numTableDefParts != 0)
            {
                foreach (TableDefinitionPart tableDefPart in clonedSheet.TableDefinitionParts)
                {
                    tableId++;
                    tableDefPart.Table.Id          = (uint)tableId;
                    tableDefPart.Table.DisplayName = "CopiedTable" + tableId;
                    tableDefPart.Table.Name        = "CopiedTable" + tableId;
                    tableDefPart.Table.Save();
                }
            }
            //There can only be one sheet that has focus
            SheetViews views = clonedSheet.Worksheet.GetFirstChild <SheetViews>();

            if (views != null)
            {
                views.Remove();
                clonedSheet.Worksheet.Save();
            }

            //Add new sheet to main workbook part
            Sheets sheets      = workbookPart.Workbook.GetFirstChild <Sheets>();
            Sheet  copiedSheet = new Sheet();

            copiedSheet.Name    = clonedSheetName;
            copiedSheet.Id      = workbookPart.GetIdOfPart(clonedSheet);
            copiedSheet.SheetId = (uint)sheets.ChildElements.Count() + 5;//+5 car le template possede deja 2 feuilles avec pour id 3 et 4
            sheets.Append(copiedSheet);

            //Save Changes
            workbookPart.Workbook.Save();

            return(clonedSheet);
        }
コード例 #2
0
    public static void CloneWorkSheet(SpreadsheetDocument ss, String sheetNameToClone, String newSheetName)
    {
        WorkbookPart wbp = ss.WorkbookPart; // get reference to Workbook in SpreadSheet doc

        // Get the source sheet to be copied
        WorksheetPart source_wsp = GetWorkSheetPartByName(ss, sheetNameToClone);

        //Take advantage of AddPart for deep cloning
        SpreadsheetDocument temp_ss  = SpreadsheetDocument.Create(new MemoryStream(), ss.DocumentType);
        WorkbookPart        temp_wbp = temp_ss.AddWorkbookPart();
        WorksheetPart       temp_wsp = temp_wbp.AddPart <WorksheetPart>(source_wsp);
        //Add cloned sheet and all associated parts to workbook
        WorksheetPart cloned_wsp = wbp.AddPart <WorksheetPart>(temp_wsp);

        cloned_wsp.Worksheet.Save();

        //Table definition parts are somewhat special and need unique ids...so let's make an id based on count
        int numTableDefParts = source_wsp.GetPartsCountOfType <TableDefinitionPart>();

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

        Sheets sheets = wbp.Workbook.GetFirstChild <Sheets>();
        // Get a unique ID for the new sheet (important)
        uint sheetId = 1;

        if (sheets.Elements <Sheet>().Count() > 0)
        {
            sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
        }
        // Add new sheet to wbp's sheet collection
        Sheet cloned_sheet = new Sheet();

        cloned_sheet.Name    = newSheetName;
        cloned_sheet.Id      = wbp.GetIdOfPart(cloned_wsp);
        cloned_sheet.SheetId = sheetId;
        sheets.Append(cloned_sheet);

        // Save Changes
        wbp.Workbook.Save();
    }
コード例 #3
0
        //Create copy of template sheet
        private static void CloneSheet(SpreadsheetDocument document, string templateWSheetName, string clonedWSheetName)
        {
            WorkbookPart  workbookPart    = document.WorkbookPart;
            Sheet         sheet           = workbookPart.Workbook.Descendants <Sheet>().Where((s) => s.Name == templateWSheetName).FirstOrDefault();
            WorksheetPart sourceSheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);

            //Take advantage of AddPart for deep cloning
            SpreadsheetDocument tempSheet         = SpreadsheetDocument.Create(new MemoryStream(), document.DocumentType);
            WorkbookPart        tempWorkbookPart  = tempSheet.AddWorkbookPart();
            WorksheetPart       tempWorksheetPart = tempWorkbookPart.AddPart <WorksheetPart>(sourceSheetPart);

            //Add cloned sheet and all associated parts to workbook
            WorksheetPart clonedSheet      = workbookPart.AddPart <WorksheetPart>(tempWorksheetPart);
            int           numTableDefParts = sourceSheetPart.GetPartsCountOfType <TableDefinitionPart>();

            int tableId = numTableDefParts;

            //Clean up table definition parts (tables need unique ids)
            if (numTableDefParts != 0)
            {
                FixupTableParts(clonedSheet, numTableDefParts, tableId);
            }

            //There can only be one sheet that has focus
            SheetViews views = clonedSheet.Worksheet.GetFirstChild <SheetViews>();

            if (views != null)
            {
                views.Remove();
                clonedSheet.Worksheet.Save();
            }

            //Add new sheet to main workbook part
            Sheets sheets      = workbookPart.Workbook.GetFirstChild <Sheets>();
            Sheet  copiedSheet = new Sheet();

            copiedSheet.Name    = clonedWSheetName;
            copiedSheet.Id      = workbookPart.GetIdOfPart(clonedSheet);
            copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;
            sheets.Append(copiedSheet);
            //Save Changes
            workbookPart.Workbook.Save();
        }
コード例 #4
0
        public void InsertSheet(string toBeCopiedSheetName, string newSheetName)
        {
            WorkbookPart workbookPart = document.WorkbookPart;

            Sheet         sourceSheet     = FindSheetByName(toBeCopiedSheetName);
            WorksheetPart sourceSheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sourceSheet.Id);

            //Take advantage of AddPart for deep cloning
            MemoryStream tempStream = new MemoryStream();

            try
            {
                SpreadsheetDocument tempDocument = SpreadsheetDocument.Create(tempStream, document.DocumentType);
                try
                {
                    WorkbookPart  tempWorkbookPart  = tempDocument.AddWorkbookPart();
                    WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart <WorksheetPart>(sourceSheetPart);

                    //Add cloned sheet and all associated parts to workbook
                    WorksheetPart clonedSheet = workbookPart.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>();
                    if (numTableDefParts != 0)
                    {
                        //Every table needs a unique id and name
                        int tableId = numTableDefParts;

                        foreach (TableDefinitionPart tableDefPart in clonedSheet.TableDefinitionParts)
                        {
                            tableId++;

                            tableDefPart.Table.Id          = (uint)tableId;
                            tableDefPart.Table.DisplayName = newSheetName + tableDefPart.Table.DisplayName;
                            tableDefPart.Table.Name        = newSheetName + tableDefPart.Table.Name;

                            tableDefPart.Table.Save();
                        }
                    }

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

                    //Add new sheet to main workbook part
                    Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();

                    Sheet copiedSheet = new Sheet();
                    copiedSheet.Name    = newSheetName;
                    copiedSheet.Id      = workbookPart.GetIdOfPart(clonedSheet);
                    copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;

                    sheets.InsertAfter(copiedSheet, currentSheet);

                    //Save the sheet
                    workbookPart.Workbook.Save();


                    //making the copied sheet the current sheet
                    currentSheetName = newSheetName;
                    currentSheet     = copiedSheet;
                    currentWorksheet = clonedSheet.Worksheet;
                    currentSheetData = currentWorksheet.GetFirstChild <SheetData>();
                }
                finally
                {
                    tempDocument.Dispose();
                }
            }
            finally
            {
                tempStream.Dispose();
            }
        }
コード例 #5
0
        static public 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);


                //Take advantage of AddPart for deep cloning

                SpreadsheetDocument tempSheet = SpreadsheetDocument.Create(new MemoryStream(), mySpreadsheet.DocumentType);

                WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();

                WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart <WorksheetPart>(sourceSheetPart);

                //Add cloned sheet and all associated parts to workbook

                WorksheetPart clonedSheet = workbookPart.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>();

                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 = workbookPart.Workbook.GetFirstChild <Sheets>();

                Sheet copiedSheet = new Sheet();

                copiedSheet.Name = clonedSheetName;

                copiedSheet.Id = workbookPart.GetIdOfPart(clonedSheet);

                copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;

                sheets.Append(copiedSheet);

                //Save Changes

                workbookPart.Workbook.Save();
            }
        }
コード例 #6
0
ファイル: Class1.cs プロジェクト: hieult5/MTN
        public static void CopySheet(string filename, string sheetName, string clonedSheetName, string destFileName)
        {
            //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(destFileName, SpreadsheetDocumentType.Workbook))
                {
                    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
                    {
                        Name = clonedSheetName,
                        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();
                }
            }
        }