none
Copy worksheet from one xlsx to another xlsx using openxml RRS feed

  • Question

  • We have to copy an "entire worksheet" from a xlsx file into a xlsm file. We are trying to use open xml for the same. Using openxml, we are able to copy a worksheet within same workboob (xlsx), but when we try to copy from one xlsx to another xlsx or xlsm file, it is corrupting the target file. If you have done this already using openxml, can you please provide code snippet. 

    Since we are in dire straits to resolve this ASAP, any quick help will be very much appreciated. Thanks in advance.

    Details:

    Office version: 2007

    VS version: VS 2008

    The sheet which we are trying to copy is a scorecard & has cell formatting, background color etc & hence is not in tabular format

    • Moved by Cindy Meister MVPModerator Sunday, January 8, 2012 7:19 AM It's an open XML format question (From:Visual Studio Tools for Office)
    Sunday, January 8, 2012 5:57 AM

Answers

  • Hi Balaji,

     

    Thanks for posting in the MSDN Forum.

     

    I will show you a snippet for you issue. It’s very simple, just copy digit and string values to the target workbook. However I think this can explain “How to” for you. I add some comments in the snippet to provide your more details for this issue. I hope it can help you.

     

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    
    namespace CopySheetViaOpenXml
    {
        public partial class Form1 : Form
        {
            private string SourceFilePath { set; get; }
            private string TargetFilePath { set; get; }
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                SourceFilePath = string.Empty;
                TargetFilePath = string.Empty;
            }
    
            /*
             * This method will iterate all of the worksheet and list it's name on
             * the combobox control. Then you can select the specifical sheet to 
             * copy.
             */
            private void button1_Click(object sender, EventArgs e)
            {
                OpenFileDialog objOpenFileDialog = new OpenFileDialog();
                objOpenFileDialog.Filter = "Excel File (*.xlsx)|*.xlsx";
                objOpenFileDialog.ShowDialog();
                SourceFilePath = objOpenFileDialog.FileName;
                if (SourceFilePath.Length > 0)
                {
                    using (SpreadsheetDocument objSpreadsheetDocument = 
                        SpreadsheetDocument.Open(SourceFilePath, false))
                    {
                        WorkbookPart objWorkbookPart = 
                            objSpreadsheetDocument.WorkbookPart;
                        Workbook objWorkbook = objWorkbookPart.Workbook;
                        List<Sheet> colSheets = 
                            objWorkbook.Descendants<Sheet>().ToList();
                        comboBox1.Items.Clear();
                        for (int i = 0; i < colSheets.Count; i++)
                        {
                            Sheet objSheet = colSheets[i];
                            comboBox1.Items.Add(objSheet.Name);
                        }
                        comboBox1.SelectedIndex = 0;
                    }
                }
            }
    
            /*
             * This method will copy the value in the source sheet to the target
             * target workbook's new sheet. As a example, here only show how to copy
             * digits and strings. If you want to copy image or chart of the sheet
             * you need to retrieve the DrawingsPart->ImagePart Of the WorksheetPart. 
             * If you want to copy chart of the sheet you need to retrieve 
             * DrawingsPart->ChartPart of the WorksheetPart. If you want to copy a 
             * PivotTable of the sheet, you need to retrieve PivotTablePart of the 
             * WorksheetPart and PivotTableCacheDefinitionPart of the WorkbookPart.
             */
            private void button2_Click(object sender, EventArgs e)
            {
                OpenFileDialog objOpenFileDialog = new OpenFileDialog();
                objOpenFileDialog.Filter = "Excel File (*.xlsx)|*.xlsx";
                objOpenFileDialog.ShowDialog();
                TargetFilePath = objOpenFileDialog.FileName;
                if (TargetFilePath.Length > 0 && SourceFilePath.Length > 0)
                {
                    using (SpreadsheetDocument objSourceDocument = 
                        SpreadsheetDocument.Open(SourceFilePath, false))
                    {
                        WorkbookPart objSourceWorkbookPart =
                            objSourceDocument.WorkbookPart;
                        Workbook objSourceWorkbook = objSourceWorkbookPart.Workbook;
                        Sheet objSheet = objSourceWorkbook.Descendants<Sheet>()
                            .Where(n => n.Name == comboBox1.Text).FirstOrDefault();
                        WorksheetPart objSourceWorksheetPart = objSourceWorkbookPart
                            .GetPartById(objSheet.Id) as WorksheetPart;
                        if (objSourceWorksheetPart != null)
                        {
                            Worksheet objSourceWorksheet = 
                                objSourceWorksheetPart.Worksheet;
                            /*
                             * Not all of the SpreadsheetDocument will have 
                             * SharedStringTablePart so you are able to add a 
                             * judgment statment here to ensure whether it exists.
                             * It's obviously exists on my side, so I omitted the 
                             * judgment here. It isn't a safely way to handle this
                             * issue.
                             */
                            SharedStringTablePart objSourceSharedStringTablePart =
                                objSourceWorkbookPart.SharedStringTablePart;
                            SharedStringTable objSourceSharedStringTable =
                                objSourceSharedStringTablePart.SharedStringTable;
                            SharedStringItem objSourceSharedStringItem =
    objSourceSharedStringTable.Descendants<SharedStringItem>().FirstOrDefault();
                            List<Text> lstTexts = objSourceSharedStringTable
                                .Descendants<Text>().ToList();
                            using (SpreadsheetDocument objTargetDocument = 
                                SpreadsheetDocument.Open(TargetFilePath, true))
                            {
                                /*
                                 * We will act in the following code. It's just a 
                                 * simple sample. Many options such as styles, 
                                 * duplicate names of the worksheet etc. will not
                                 * be considered in the following code.Yon are able
                                 * to complete those options by yourself.
                                 */
                                WorkbookPart objTargetWorkbookPart = 
                                    objTargetDocument.WorkbookPart;
                                Workbook objTargetWorkbook = 
                                    objTargetWorkbookPart.Workbook;
                                WorksheetPart newWorksheetPart = 
    objTargetWorkbookPart.AddNewPart<WorksheetPart>();
                                int iSheetCounter = objTargetWorkbook
                                    .Descendants<Sheet>().ToList().Count;
                                iSheetCounter++;
                                string strNewRid = objTargetWorkbookPart
                                    .GetIdOfPart(newWorksheetPart);
                                Sheet newSheet = new Sheet() 
                                { 
                                    Name = comboBox1.Text,
                                    SheetId = (UInt32Value)((uint)iSheetCounter),
                                    Id = strNewRid
                                };
                                objTargetWorkbook.Sheets.Append(newSheet);
                                Worksheet newWorksheet = new Worksheet();
                                SharedStringTablePart objTargetSharedStringTablePart
                                    = objTargetWorkbookPart.SharedStringTablePart;
                                SharedStringTable objTargetSharedStringTable = null;
                                /*
                                 * We use judgment statment to ensure whether this
                                 * part exists. This is a safely way in the process.
                                 */
                                if (objTargetSharedStringTablePart == null)
                                {
                                    objTargetSharedStringTable =
                                        new SharedStringTable() 
                                        { Count = (UInt32Value)0U };
                                    objTargetSharedStringTablePart = 
    objTargetWorkbookPart.AddNewPart<SharedStringTablePart>();
                                    objTargetSharedStringTablePart.SharedStringTable
                                        = objTargetSharedStringTable;
                                }
                                else
                                {
                                    objTargetSharedStringTable =
    objTargetSharedStringTablePart.SharedStringTable;
                                }
                                uint iTextCounter = objTargetSharedStringTable
                                    .Count.Value;
                                newWorksheetPart.Worksheet = newWorksheet;
                                SheetData newSheetData = new SheetData();
                                newWorksheet.Append(newSheetData);
                                List<Row> lstRows = objSourceWorksheet
                                    .Descendants<Row>().ToList();
                                for (int i = 0; i < lstRows.Count; i++)
                                {
                                    List<Cell> lstCells = lstRows[i]
                                        .Descendants<Cell>().ToList();
                                    Row newRow = new Row();
                                    newRow.RowIndex = lstRows[i].RowIndex;
                                    for (int j = 0; j < lstCells.Count; j++)
                                    {
                                        Cell objSourceCell = lstCells[j];
                                        CellValue objSourceCellValue = 
                                            objSourceCell.CellValue;
                                        Cell newCell = new Cell();
                                        newCell.CellReference = 
                                            objSourceCell.CellReference;
                                        newCell.DataType = objSourceCell.DataType;
                                        if (objSourceCell.DataType != null &&
                                            objSourceCell.DataType == 
                                            CellValues.SharedString)
                                        {
                                            int index =
                                                int.Parse(objSourceCellValue.Text);
                                            Text newText = new Text();
                                            newText.Text = lstTexts[index].Text;
    SharedStringItem objTargetSharedStringItem = new SharedStringItem();
                                            objTargetSharedStringItem
                                                .Append(newText);
    objTargetSharedStringTable.Append(objTargetSharedStringItem);
                                            objTargetSharedStringTable.Count =
                                                (UInt32Value)(iTextCounter + 1);
                                            CellValue newCellValue =
                                                new CellValue();
                                            newCellValue.Text =
                                                iTextCounter.ToString();
                                            iTextCounter++;
                                            newCell.Append(newCellValue);
                                        }
                                        else
                                        {
                                            CellValue newCellValue = 
                                                new CellValue();
                                            newCellValue.Text = 
                                                objSourceCellValue.Text;
                                            newCell.Append(newCellValue);
                                        }
                                        newRow.Append(newCell);
                                    }
                                    newSheetData.Append(newRow);
                                }
                                objTargetWorkbook.Save();
                                Close();
                            }
                        }
                    }
                }
            }
    
            private void button3_Click(object sender, EventArgs e)
            {
                Close();
            }
        }
    }
    

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, January 10, 2012 5:41 AM
    Moderator

All replies

  • Hi Balaji

    You might want to check this discussion, that does not yet have a resolution

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

    It may give you some ideas, and perhaps you can offer some hints to Patrick.


    Cindy Meister, VSTO/Word MVP
    Sunday, January 8, 2012 9:19 AM
    Moderator
  • Tried this already, but in vain. Couldn't find any solution so far.
    Tuesday, January 10, 2012 4:19 AM
  • Hi Balaji,

     

    Thanks for posting in the MSDN Forum.

     

    I will show you a snippet for you issue. It’s very simple, just copy digit and string values to the target workbook. However I think this can explain “How to” for you. I add some comments in the snippet to provide your more details for this issue. I hope it can help you.

     

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    
    namespace CopySheetViaOpenXml
    {
        public partial class Form1 : Form
        {
            private string SourceFilePath { set; get; }
            private string TargetFilePath { set; get; }
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                SourceFilePath = string.Empty;
                TargetFilePath = string.Empty;
            }
    
            /*
             * This method will iterate all of the worksheet and list it's name on
             * the combobox control. Then you can select the specifical sheet to 
             * copy.
             */
            private void button1_Click(object sender, EventArgs e)
            {
                OpenFileDialog objOpenFileDialog = new OpenFileDialog();
                objOpenFileDialog.Filter = "Excel File (*.xlsx)|*.xlsx";
                objOpenFileDialog.ShowDialog();
                SourceFilePath = objOpenFileDialog.FileName;
                if (SourceFilePath.Length > 0)
                {
                    using (SpreadsheetDocument objSpreadsheetDocument = 
                        SpreadsheetDocument.Open(SourceFilePath, false))
                    {
                        WorkbookPart objWorkbookPart = 
                            objSpreadsheetDocument.WorkbookPart;
                        Workbook objWorkbook = objWorkbookPart.Workbook;
                        List<Sheet> colSheets = 
                            objWorkbook.Descendants<Sheet>().ToList();
                        comboBox1.Items.Clear();
                        for (int i = 0; i < colSheets.Count; i++)
                        {
                            Sheet objSheet = colSheets[i];
                            comboBox1.Items.Add(objSheet.Name);
                        }
                        comboBox1.SelectedIndex = 0;
                    }
                }
            }
    
            /*
             * This method will copy the value in the source sheet to the target
             * target workbook's new sheet. As a example, here only show how to copy
             * digits and strings. If you want to copy image or chart of the sheet
             * you need to retrieve the DrawingsPart->ImagePart Of the WorksheetPart. 
             * If you want to copy chart of the sheet you need to retrieve 
             * DrawingsPart->ChartPart of the WorksheetPart. If you want to copy a 
             * PivotTable of the sheet, you need to retrieve PivotTablePart of the 
             * WorksheetPart and PivotTableCacheDefinitionPart of the WorkbookPart.
             */
            private void button2_Click(object sender, EventArgs e)
            {
                OpenFileDialog objOpenFileDialog = new OpenFileDialog();
                objOpenFileDialog.Filter = "Excel File (*.xlsx)|*.xlsx";
                objOpenFileDialog.ShowDialog();
                TargetFilePath = objOpenFileDialog.FileName;
                if (TargetFilePath.Length > 0 && SourceFilePath.Length > 0)
                {
                    using (SpreadsheetDocument objSourceDocument = 
                        SpreadsheetDocument.Open(SourceFilePath, false))
                    {
                        WorkbookPart objSourceWorkbookPart =
                            objSourceDocument.WorkbookPart;
                        Workbook objSourceWorkbook = objSourceWorkbookPart.Workbook;
                        Sheet objSheet = objSourceWorkbook.Descendants<Sheet>()
                            .Where(n => n.Name == comboBox1.Text).FirstOrDefault();
                        WorksheetPart objSourceWorksheetPart = objSourceWorkbookPart
                            .GetPartById(objSheet.Id) as WorksheetPart;
                        if (objSourceWorksheetPart != null)
                        {
                            Worksheet objSourceWorksheet = 
                                objSourceWorksheetPart.Worksheet;
                            /*
                             * Not all of the SpreadsheetDocument will have 
                             * SharedStringTablePart so you are able to add a 
                             * judgment statment here to ensure whether it exists.
                             * It's obviously exists on my side, so I omitted the 
                             * judgment here. It isn't a safely way to handle this
                             * issue.
                             */
                            SharedStringTablePart objSourceSharedStringTablePart =
                                objSourceWorkbookPart.SharedStringTablePart;
                            SharedStringTable objSourceSharedStringTable =
                                objSourceSharedStringTablePart.SharedStringTable;
                            SharedStringItem objSourceSharedStringItem =
    objSourceSharedStringTable.Descendants<SharedStringItem>().FirstOrDefault();
                            List<Text> lstTexts = objSourceSharedStringTable
                                .Descendants<Text>().ToList();
                            using (SpreadsheetDocument objTargetDocument = 
                                SpreadsheetDocument.Open(TargetFilePath, true))
                            {
                                /*
                                 * We will act in the following code. It's just a 
                                 * simple sample. Many options such as styles, 
                                 * duplicate names of the worksheet etc. will not
                                 * be considered in the following code.Yon are able
                                 * to complete those options by yourself.
                                 */
                                WorkbookPart objTargetWorkbookPart = 
                                    objTargetDocument.WorkbookPart;
                                Workbook objTargetWorkbook = 
                                    objTargetWorkbookPart.Workbook;
                                WorksheetPart newWorksheetPart = 
    objTargetWorkbookPart.AddNewPart<WorksheetPart>();
                                int iSheetCounter = objTargetWorkbook
                                    .Descendants<Sheet>().ToList().Count;
                                iSheetCounter++;
                                string strNewRid = objTargetWorkbookPart
                                    .GetIdOfPart(newWorksheetPart);
                                Sheet newSheet = new Sheet() 
                                { 
                                    Name = comboBox1.Text,
                                    SheetId = (UInt32Value)((uint)iSheetCounter),
                                    Id = strNewRid
                                };
                                objTargetWorkbook.Sheets.Append(newSheet);
                                Worksheet newWorksheet = new Worksheet();
                                SharedStringTablePart objTargetSharedStringTablePart
                                    = objTargetWorkbookPart.SharedStringTablePart;
                                SharedStringTable objTargetSharedStringTable = null;
                                /*
                                 * We use judgment statment to ensure whether this
                                 * part exists. This is a safely way in the process.
                                 */
                                if (objTargetSharedStringTablePart == null)
                                {
                                    objTargetSharedStringTable =
                                        new SharedStringTable() 
                                        { Count = (UInt32Value)0U };
                                    objTargetSharedStringTablePart = 
    objTargetWorkbookPart.AddNewPart<SharedStringTablePart>();
                                    objTargetSharedStringTablePart.SharedStringTable
                                        = objTargetSharedStringTable;
                                }
                                else
                                {
                                    objTargetSharedStringTable =
    objTargetSharedStringTablePart.SharedStringTable;
                                }
                                uint iTextCounter = objTargetSharedStringTable
                                    .Count.Value;
                                newWorksheetPart.Worksheet = newWorksheet;
                                SheetData newSheetData = new SheetData();
                                newWorksheet.Append(newSheetData);
                                List<Row> lstRows = objSourceWorksheet
                                    .Descendants<Row>().ToList();
                                for (int i = 0; i < lstRows.Count; i++)
                                {
                                    List<Cell> lstCells = lstRows[i]
                                        .Descendants<Cell>().ToList();
                                    Row newRow = new Row();
                                    newRow.RowIndex = lstRows[i].RowIndex;
                                    for (int j = 0; j < lstCells.Count; j++)
                                    {
                                        Cell objSourceCell = lstCells[j];
                                        CellValue objSourceCellValue = 
                                            objSourceCell.CellValue;
                                        Cell newCell = new Cell();
                                        newCell.CellReference = 
                                            objSourceCell.CellReference;
                                        newCell.DataType = objSourceCell.DataType;
                                        if (objSourceCell.DataType != null &&
                                            objSourceCell.DataType == 
                                            CellValues.SharedString)
                                        {
                                            int index =
                                                int.Parse(objSourceCellValue.Text);
                                            Text newText = new Text();
                                            newText.Text = lstTexts[index].Text;
    SharedStringItem objTargetSharedStringItem = new SharedStringItem();
                                            objTargetSharedStringItem
                                                .Append(newText);
    objTargetSharedStringTable.Append(objTargetSharedStringItem);
                                            objTargetSharedStringTable.Count =
                                                (UInt32Value)(iTextCounter + 1);
                                            CellValue newCellValue =
                                                new CellValue();
                                            newCellValue.Text =
                                                iTextCounter.ToString();
                                            iTextCounter++;
                                            newCell.Append(newCellValue);
                                        }
                                        else
                                        {
                                            CellValue newCellValue = 
                                                new CellValue();
                                            newCellValue.Text = 
                                                objSourceCellValue.Text;
                                            newCell.Append(newCellValue);
                                        }
                                        newRow.Append(newCell);
                                    }
                                    newSheetData.Append(newRow);
                                }
                                objTargetWorkbook.Save();
                                Close();
                            }
                        }
                    }
                }
            }
    
            private void button3_Click(object sender, EventArgs e)
            {
                Close();
            }
        }
    }
    

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, January 10, 2012 5:41 AM
    Moderator
  • Hi Balaji

    I've posted on that other thread, to avoid duplication of effort, could you please check that. If it turns out that your problem is different than Patrick's, we can follow up in detail here.


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

     

    Did you solve your issue? If it is, would you please share your experience here? It will be very beneficial for other community members who have similar questions to see how you solve your issue.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Friday, January 13, 2012 6:06 AM
    Moderator