none
copy sheets one work book to other work book using open xml with out using microsoft.interop.excel RRS feed

  • Question

  •          

    i am using miscrosoft.interop.excel in .net 4.0

    i want to copy the data contain in one sheet in one work book and paste into sheet different work book with sheet name, i have done this by using miscrosoft.interop.excel, i want same thing using open xml please give the solution

    i have done this by using miscrosoft.interop.excel this is the following code the same thing need to be done using openxml please help me on this

    Excel.Application oXL;
    Excel.Workbook oWB;
    Excel.Worksheet oSheet;
    Excel.Range oRange;

    oXL = new Excel.Application();

            //    // Set some properties 
            oXL.Visible = true;
            oXL.DisplayAlerts = false

      for (int iWorkbook = 0; iWorkbook < Files.Length; iWorkbook++)
                {
                    if (Files[iWorkbook] != string.Empty)
                    {
                        oXL.Workbooks.Add(Files[iWorkbook]);
                    }
                }
                for (int i = 2; i <= oXL.Workbooks.Count; i++)
                {
                    int count = oXL.Workbooks[i].Worksheets.Count;
                    oXL.Workbooks[i].Activate();

                    for (int j = 2; j <= count; j++)
                    {
                        Excel._Worksheet ws = (Excel._Worksheet)oXL.Workbooks[i].Worksheets[j];

                        ws.Select(Type.Missing);
                        ws.Cells.Select();
                        string name = ws.Name;
                        Excel.Range sel = (Excel.Range)oXL.Selection;
                        if (sel.Cells.Text != string.Empty)
                        {
                            sel.Copy(Type.Missing);
                            Excel._Worksheet sheet = (Excel._Worksheet)oXL.Workbooks[1].Worksheets.Add(
                            Type.Missing, Type.Missing, Type.Missing, Type.Missing
                            );

                            sheet.Paste(Type.Missing, Type.Missing);
                            sheet.Name = name;
                        }
                    }
                }
                string[] ConsolidatedFiles = Directory.GetFiles(filepath);

                if (ConsolidatedFiles.Length > 0)
                {
                    string filetemplates = filepath + "\\" + TemplateType + ".xlsx";
                    for (int file = 0; file < ConsolidatedFiles.Length; file++)
                    {
                        if (ConsolidatedFiles[file].ToString() == filetemplates)
                        {
                            File.Delete(ConsolidatedFiles[file]);
                        }
                    }
                }
                filepath = filepath + "\\" + TemplateType + ".xlsx";

                oWB.SaveAs(filepath, Excel.XlFileFormat.xlWorkbookDefault,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Excel.XlSaveAsAccessMode.xlExclusive,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);
                oWB.Close(Type.Missing, Type.Missing, Type.Missing);
                oWB = null;
                oXL.Quit();            

    i need to be do by using open xml please give the solution
    • Edited by prakiprakash Wednesday, October 31, 2012 12:12 PM
    Wednesday, October 31, 2012 5:35 AM

Answers

  • Hi prakiprakash,

    Thanks for posting in the MSDN Forum.

    After review your code I think you need to duplicate specific worksheet. Is it right? And there have many undefined variable in your code such as :"Files", "TemplateType" etc. 

    On other hand, following code seems will not work:

       if (sel.Cells.Text != string.Empty)

    You will receive exception for "!=" operator can not be applied to operands of type System.DBNull and string. So we can't understand your really goal indeed. I suppose that your only want to duplicate worksheet in this thread, and provide following snippet for you:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using com.mksword.Net.OpenXmlTools;
    
    namespace ConsoleApplication7
    {
        public class CopyData : SpreadsheetDocumentUtil
        {
            /// <summary>
            /// Here defined the SheetName which you will duplicate form original
            /// Worksheet
            /// </summary>
            private string _SheetName = "MyTargetWorksheet";
    
            /// <summary>
            /// This part stored Text data of the Sheet
            /// </summary>
            private SharedStringTablePart _SSTP = null;
    
            /// <summary>
            /// Is variable is the Worksheet which your want to copy.
            /// </summary>
            private WorksheetPart _WSP = null;
    
            public void Action()
            {
                // We warp some code which have few relationship with your issue
                // via HandleOpenSpreadsheetDocument please, the key operation is in
                // OriginalHandler method
                HandleOpenSpreadsheetDocument(OriginalHandler);
            }
    
            // We will get the target worksheet in this method and copy it to other
            // destination.
            private bool OriginalHandler(SpreadsheetDocument SSD)
            {
                bool result = false;
                WorkbookPart WBP = SSD.WorkbookPart;
                Workbook WB = WBP.Workbook;
                Sheet S = WB.Descendants<Sheet>().Where(SH => SH.Name == _SheetName)
                    .FirstOrDefault();
                _WSP = WBP.GetPartById(S.Id) as WorksheetPart;
                _SSTP = WBP.SharedStringTablePart;
                // We will paste it to destination workbook.
                HandleOpenSpreadsheetDocument(PasteWorksheet, true);
                result = true;
                return result;
            }
    
            // We will copy sheet to destination workbook
            private bool PasteWorksheet(SpreadsheetDocument SSD)
            {
                bool result = false;
                WorkbookPart WBP = SSD.WorkbookPart;
                WorksheetPart newWSP = WBP.AddNewPart<WorksheetPart>();
                Worksheet nWS = new Worksheet();
                SheetData nSD = new SheetData();
                Worksheet _WS = _WSP.Worksheet;
                SheetData _SD = _WS.Descendants<SheetData>().FirstOrDefault();
                foreach (Row _R in _SD.Descendants<Row>().ToList())
                {
                    Row nR = new Row()
                    {
                        RowIndex = _R.RowIndex
                    };
                    foreach (Cell _C in _R.Descendants<Cell>().ToList())
                    {
                        Cell nC = new Cell()
                        {
                            CellReference = _C.CellReference
                        };
                        if (_C.DataType == CellValues.SharedString)
                        {
                            nC.DataType = CellValues.InlineString;
                            string Value = CheckValue(_C);
                            InlineString ILS = new InlineString();
                            Text T = new Text() { Text = Value };
                            ILS.Append(T);
                            nC.Append(ILS);
                        }
                        else
                        {
                            nC.DataType = _C.DataType;
                            CellValue nCV = new CellValue();
                            nCV.Text = _C.CellValue.Text;
                            nC.Append(nCV);
                        }
                        nR.Append(nC);
                    }
                    nSD.Append(nR);
                }
                nWS.Append(nSD);
                newWSP.Worksheet = nWS;
                Workbook WB = WBP.Workbook;
                Sheets Ss = WB.Sheets;
                int Count = WB.Descendants<Sheet>().Count();
                Count++;
                Sheet S = new Sheet()
                {
                    Name = _SheetName,
                    Id = WBP.GetIdOfPart(newWSP),
                    SheetId = (UInt32Value)(UInt32)Count
                };
                Ss.Append(S);
                WB.Save();
                return result;
            }
    
            // retriev string form original sheet
            private string CheckValue(Cell _C)
            {
                string result = null;
                List<SharedStringItem> collection = _SSTP
                    .SharedStringTable.Descendants<SharedStringItem>().ToList();
                result = collection[int.Parse(_C.CellValue.Text)].Text.Text;
                return result;
            }
        }
    }

    I hope it can help you.

    Have a good day,

    Tom


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


    Thursday, November 1, 2012 3:56 AM
    Moderator

All replies

  • hello 

     i am using open xml in .net 4.0

    i want to copy the data contain in one sheet in one work book and paste into sheet different work book with sheet name, i have done this by using miscrosoft.interop.excel, i want same thing using open xml please give the solution

    Wednesday, October 31, 2012 5:14 AM
  • Hi,

    Thanks for posting in the MSDN Forum.

    According to your description, I think this issue is an Open XML question so I move it to this forum.

    Also, based on your issue, I would recommend you this blog. It directs a detailed solution of how to copy a worksheet within workbook by using Open XML. Please take a look at

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

    Hope it helps.

    Best Regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us


    Thursday, November 1, 2012 2:13 AM
    Moderator
  • Hi prakiprakash,

    Thanks for posting in the MSDN Forum.

    After review your code I think you need to duplicate specific worksheet. Is it right? And there have many undefined variable in your code such as :"Files", "TemplateType" etc. 

    On other hand, following code seems will not work:

       if (sel.Cells.Text != string.Empty)

    You will receive exception for "!=" operator can not be applied to operands of type System.DBNull and string. So we can't understand your really goal indeed. I suppose that your only want to duplicate worksheet in this thread, and provide following snippet for you:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using com.mksword.Net.OpenXmlTools;
    
    namespace ConsoleApplication7
    {
        public class CopyData : SpreadsheetDocumentUtil
        {
            /// <summary>
            /// Here defined the SheetName which you will duplicate form original
            /// Worksheet
            /// </summary>
            private string _SheetName = "MyTargetWorksheet";
    
            /// <summary>
            /// This part stored Text data of the Sheet
            /// </summary>
            private SharedStringTablePart _SSTP = null;
    
            /// <summary>
            /// Is variable is the Worksheet which your want to copy.
            /// </summary>
            private WorksheetPart _WSP = null;
    
            public void Action()
            {
                // We warp some code which have few relationship with your issue
                // via HandleOpenSpreadsheetDocument please, the key operation is in
                // OriginalHandler method
                HandleOpenSpreadsheetDocument(OriginalHandler);
            }
    
            // We will get the target worksheet in this method and copy it to other
            // destination.
            private bool OriginalHandler(SpreadsheetDocument SSD)
            {
                bool result = false;
                WorkbookPart WBP = SSD.WorkbookPart;
                Workbook WB = WBP.Workbook;
                Sheet S = WB.Descendants<Sheet>().Where(SH => SH.Name == _SheetName)
                    .FirstOrDefault();
                _WSP = WBP.GetPartById(S.Id) as WorksheetPart;
                _SSTP = WBP.SharedStringTablePart;
                // We will paste it to destination workbook.
                HandleOpenSpreadsheetDocument(PasteWorksheet, true);
                result = true;
                return result;
            }
    
            // We will copy sheet to destination workbook
            private bool PasteWorksheet(SpreadsheetDocument SSD)
            {
                bool result = false;
                WorkbookPart WBP = SSD.WorkbookPart;
                WorksheetPart newWSP = WBP.AddNewPart<WorksheetPart>();
                Worksheet nWS = new Worksheet();
                SheetData nSD = new SheetData();
                Worksheet _WS = _WSP.Worksheet;
                SheetData _SD = _WS.Descendants<SheetData>().FirstOrDefault();
                foreach (Row _R in _SD.Descendants<Row>().ToList())
                {
                    Row nR = new Row()
                    {
                        RowIndex = _R.RowIndex
                    };
                    foreach (Cell _C in _R.Descendants<Cell>().ToList())
                    {
                        Cell nC = new Cell()
                        {
                            CellReference = _C.CellReference
                        };
                        if (_C.DataType == CellValues.SharedString)
                        {
                            nC.DataType = CellValues.InlineString;
                            string Value = CheckValue(_C);
                            InlineString ILS = new InlineString();
                            Text T = new Text() { Text = Value };
                            ILS.Append(T);
                            nC.Append(ILS);
                        }
                        else
                        {
                            nC.DataType = _C.DataType;
                            CellValue nCV = new CellValue();
                            nCV.Text = _C.CellValue.Text;
                            nC.Append(nCV);
                        }
                        nR.Append(nC);
                    }
                    nSD.Append(nR);
                }
                nWS.Append(nSD);
                newWSP.Worksheet = nWS;
                Workbook WB = WBP.Workbook;
                Sheets Ss = WB.Sheets;
                int Count = WB.Descendants<Sheet>().Count();
                Count++;
                Sheet S = new Sheet()
                {
                    Name = _SheetName,
                    Id = WBP.GetIdOfPart(newWSP),
                    SheetId = (UInt32Value)(UInt32)Count
                };
                Ss.Append(S);
                WB.Save();
                return result;
            }
    
            // retriev string form original sheet
            private string CheckValue(Cell _C)
            {
                string result = null;
                List<SharedStringItem> collection = _SSTP
                    .SharedStringTable.Descendants<SharedStringItem>().ToList();
                result = collection[int.Parse(_C.CellValue.Text)].Text.Text;
                return result;
            }
        }
    }

    I hope it can help you.

    Have a good day,

    Tom


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


    Thursday, November 1, 2012 3:56 AM
    Moderator
  • Hi thanks for giving reply 

    actually what i want to do is i have some sheets like test1,test1,test2 in workbook1(workbook1.xlsx).

    then  in 2nd workbook(workbook2.xlsx) have the sheets called  test4,test5,test6.

    i want to copy the sehets(test2,test3) of workbook(workbook1.xlsx) to  workbook2.xlsx

    after copying the workbook2.xslx should contain the (test4,test5,test6,test2,test3)

    i am  using following code

    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Office;
    using DocumentFormat.OpenXml.Spreadsheet;

               using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(filepath, true))

                {
                    WorkbookPart sworkbookPart = spreadSheet.WorkbookPart;
                    for (int workbok = 0; workbok < Files.Length; workbok++)
                    {
                        if (Files[workbok] != string.Empty)
                        {
                            using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(Files[workbok], true))
                            {
                                WorkbookPart workbookPart = mySpreadsheet.WorkbookPart;
                                //Get the source sheet to be copied
                                WorksheetPart sourceSheetPart = GetWorkSheetPart(workbookPart);
                                //Take advantage of AddPart for deep cloning
                                SpreadsheetDocument tempSheet = SpreadsheetDocument.Create(new MemoryStream(), spreadSheet.DocumentType);
                                WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();
                                WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart<WorksheetPart>(sourceSheetPart);
                                //Add cloned sheet and all associated parts to workbook
                                WorksheetPart clonedSheet = sworkbookPart.AddPart<WorksheetPart>(tempWorksheetPart);

                                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);

                                Sheets sheets = sworkbookPart.Workbook.GetFirstChild<Sheets>();
                                Sheet copiedSheet = new Sheet();
                                copiedSheet.Name = "Sheets";
                                copiedSheet.Id = sworkbookPart.GetIdOfPart(clonedSheet);
                                copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;
                                sheets.Append(copiedSheet);
                                //Save Changes
                                sworkbookPart.Workbook.Save();
                            }
                        }
                    }
                }

     static WorksheetPart GetWorkSheetPart(WorkbookPart workbookPart)
        {
            //Get the relationship id of the sheetname
            string relId = workbookPart.Workbook.Descendants<Sheet>()
            .Where(s => s.Name.Value.Equals("test2"))
            .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();
            }
        }

    Note:  filepath is DestinationFilepath and, List  Files contains list of Source files

    please help me on this




    Friday, November 2, 2012 6:52 AM
  • Hi prakiparkash,

    I think the code which I provided can solve your issue. Please pay more attention at OriginalHandler, PasteWorksheet and CheckValue.I just omitted the function which use to select and open file in this code(HandleOpenSpreadsheetDocument will do it and this method have a delegate parameter to subscribe the customized Operation into this method to finish the business). you can do it via yourself and recall the method to approach you goal.

    Have a good day,

    Tom


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

    Monday, November 5, 2012 6:56 AM
    Moderator