none
Problem to ObjectDisposedException RRS feed

  • Question

  • Hi,
    I get
    System.ObjectDisposedException was unhandled
      HResult=-2146232798
      Message=Cannot access a disposed object. Object name: 'SpreadsheetDocument'  Source=DocumentFormat.OpenXml
      ObjectName=SpreadsheetDocument
      StackTrace:
           at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.ThrowIfObjectDisposed()
           at DocumentFormat.OpenXml.Packaging.OpenXmlPartContainer.GetSubPartOfType[T]()
           at DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.get_WorkbookPart()
           at WindowsFormsApplication2.Form1.InsertText(SpreadsheetDocument spreadSheet, String SheetName0, String text, String CellRowRef0, UInt32 CellColRef0) in c:\dp13\General Application\WindowsFormsApplication9_4_0_4\WindowsFormsApplication2\Form1.cs:line 659
           at WindowsFormsApplication2.Form1.menuItem5_Click(Object sender, EventArgs e) in c:\dp13\General Application\WindowsFormsApplication9_4_0_4\WindowsFormsApplication2\Form1.cs:line 198
           at System.Windows.Forms.MenuItem.OnClick(EventArgs e)
           at System.Windows.Forms.MenuItem.MenuItemData.Execute()
           at System.Windows.Forms.Command.Invoke()
           at System.Windows.Forms.Command.DispatchID(Int32 id)
           at System.Windows.Forms.Control.WmCommand(Message& m)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
           at System.Windows.Forms.Form.WndProc(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.Run(Form mainForm)
           at WindowsFormsApplication2.Program.Main() in c:\dp13\General Application\WindowsFormsApplication9_4_0_4\WindowsFormsApplication2\Program.cs:line 19
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: 



    due to this line
                if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)



    below. How to use IsDisposed property to avoid this problem?

            public static void InsertText(SpreadsheetDocument spreadSheet, string SheetName0, string text, string CellRowRef0, uint CellColRef0)
            {
                //using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                // Open the document for editing.
                //Using(SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                //{
                // Get the SharedStringTablePart. If it does not exist, create a new one.
                SharedStringTablePart shareStringPart;
                if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                {
                    shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                }
                else
                {
                    shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
                }



    Many Thanks & Best Regards, Hua Min

    Friday, November 18, 2016 6:38 AM

Answers

  • Hi,

    There is a Code Sample Team to write some samples. I have asked for the sample for copying workbooks using Open XML.

    Now you could try the project I created. https://1drv.ms/u/s!AkcxDWH1nFmJpQMScnSMJlhRtKNy

    It could copy and write the some cell values with style except column width.

    Create a workbook with some value with some styles and then click in the following order.

    CreateDoc_Click -> CopyStylePart_Click -> CopyRowNodes_Click -> GetStyleIndex_Click

    If there are any issues to use the code above, I suggest you post new threads.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jackson_1990 Friday, December 9, 2016 9:19 AM
    Friday, December 9, 2016 5:47 AM
    Moderator

All replies

  • Hi,

    In Open XML library, there is no IsDisposed property for any objects.

    Control.IsDisposed Property belongs to winform controls.

    How do you call the method in your winform application?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 21, 2016 5:09 AM
    Moderator
  • The exception is due to if line below
            public static void InsertText(SpreadsheetDocument spreadSheet, string SheetName0, string text, string CellRowRef0, uint CellColRef0)
            {
                SharedStringTablePart shareStringPart;
                if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                ...



    and I call the event like
                                                            InsertText(doc1, sheet.Name, cell.InnerText, Col0, Convert.ToUInt32(Row0));



    Many Thanks & Best Regards, Hua Min


    Monday, November 21, 2016 6:52 AM
  • The exception represents the spreadSheet(doc1) has been disposed/closed before you call the method.

    Try to add line: using (SpreadsheetDocument doc1= SpreadsheetDocument.Open(fileName, true)) to open or using (doc1) if it has been opened.

    If it does not work, I suggest you share us the code before this line. 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 21, 2016 12:14 PM
    Moderator
  • I still get same problem to if line below                
            public static void InsertText(SpreadsheetDocument spreadSheet, string SheetName0, string text, string CellRowRef0, uint CellColRef0)
            {
                SharedStringTablePart shareStringPart;
                using (spreadSheet)
                {
                    if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                    {
                        shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                    }
                    else
                    {
                        shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
                    }
                    ...


    Here is how I call the event
                                            foreach (Cell cell in worksheetPart.Worksheet.Descendants<Cell>())
                                            {
                                                if (cell != null)
                                                {
                                                    using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                                    {
                                                        if (cell.DataType == null)
                                                        {
                                                            Col0 = ColumnPart(cell.CellReference); Row0 = RowPart(cell.CellReference);
    
                                                            InsertText(doc1, sheet.Name, cell.InnerText, Col0, Convert.ToUInt32(Row0));
                                                        }
                                                        else
                                                        {
                                                            switch (cell.DataType.Value)
                                                            {
                                                                case CellValues.SharedString:
    
                                                                    var stringTable =
                                                                        workbookPart.GetPartsOfType<SharedStringTablePart>()
                                                                        .FirstOrDefault();
    
                                                                    if (stringTable != null)
                                                                    {
                                                                        Col0 = ColumnPart(cell.CellReference); Row0 = RowPart(cell.CellReference);
                                                                        InsertText(doc1, sheet.Name, cell.InnerText, Col0, Convert.ToUInt32(Row0));
                                                                    }



    Many Thanks & Best Regards, Hua Min

    Tuesday, November 22, 2016 1:38 AM
  • I mean

    using (doc1)

    {                                       

     InsertText(doc1, sheet.Name, cell.InnerText, Col0, Convert.ToUInt32(Row0));

    }

    I try the code in Problem to slow process, but failed to reproduce your issue.

    I suggest you share the whole exact code you are using now, and clarify the variables like @File1.

    I don’t understand the line using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode)).  What is file1 and what do you want to write into it?

    In my opinion, it should not be the Excel file because we are writing the content using method InsertText. 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 22, 2016 9:40 AM
    Moderator
  • Hi,
    Please see these
                    var ExcelObj = new Excel.Application();
                    //Excel.Workbook Book0 = ExcelObj.Workbooks.Open(openFileDialog1.FileName, 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
                    ExcelObj.ScreenUpdating = false;
    
                    Excel.Worksheet Worksheet0;
                    string Folder0 = Path.GetDirectoryName(openFileDialog1.FileName);
                    //string Path0 = Folder0 + "\\New0.xlsx";
                    string Path0 = System.IO.Path.Combine(Folder0, "New0.xlsx");
                    CreateSpreadsheetWorkbook3(Path0);
                    string Col0 = "", Row0 = "";
    
                    using (FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                    {
                        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
                        {
                            using (FileStream fs1 = new FileStream(Path0, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
                            {
                                using (SpreadsheetDocument doc1 = SpreadsheetDocument.Open(fs1, true))
                                {
                                    WorkbookPart workbookPart = doc.WorkbookPart;
    
                                    /*Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().
                                        Where(s => s.Name == "Sheet Overall").FirstOrDefault();*/
                                    Sheet theSheet;
    
                                    int sheetIndex = 0;
                                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbookPart.Workbook.Sheets;
                                    //foreach (WorksheetPart wsPart in workbookPart.WorksheetParts)
                                    foreach (Sheet sheet in sheets)
                                    {
                                        Boolean b = CreateSpreadsheetWorkbook(doc1, sheet.Name);
                                        //WorkbookStylesPart wstylePart = workbookPart.WorkbookStylesPart;
                                        //Stylesheet ss = wstylePart.Stylesheet;
                                        string sheetPartType = workbookPart.GetPartById(sheet.Id).GetType().Name;
                                        using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                        {
                                            //sw.WriteLine("Sheet name is {0},Sheet ID is {1},sheetId is {2},sheetPartType is {3}", sheet.Name, sheet.Id, sheet.SheetId, sheetPartType);
                                        }
    
                                        if (sheetPartType != "WorksheetPart")
                                        {
                                            //Console.WriteLine("{0} is not a worksheet", sheet.Name);
                                            using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                            {
                                                //sw.WriteLine("{0} is not a worksheet", sheet.Name);
                                            }
                                        }
                                        else
                                        {
                                            WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
                                            WorkbookStylesPart wstylePart = workbookPart.WorkbookStylesPart;
                                            Stylesheet ss = wstylePart.Stylesheet;
    
                                            foreach (Cell cell in worksheetPart.Worksheet.Descendants<Cell>())
                                            {
                                                if (cell != null)
                                                {
                                                    using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                                    {
                                                        if (cell.DataType == null)
                                                        {
                                                            //sw.WriteLine("Cell reference is {0}; Cell value is {1}", cell.CellReference, cell.InnerText);
                                                            Col0 = ColumnPart(cell.CellReference); Row0 = RowPart(cell.CellReference);
    
                                                            using (doc1)
                                                            {
                                                                InsertText(doc1, sheet.Name, cell.InnerText, Col0, Convert.ToUInt32(Row0));
                                                            }
                                                        }
                                                        else
                                                        {
                                                            switch (cell.DataType.Value)
                                                            {
                                                                case CellValues.SharedString:
    
                                                                    var stringTable =
                                                                        workbookPart.GetPartsOfType<SharedStringTablePart>()
                                                                        .FirstOrDefault();
    
                                                                    if (stringTable != null)
                                                                    {
                                                                        //sw.WriteLine("Cell reference is {0}; Cell value is {1}", cell.CellReference, stringTable.SharedStringTable.ElementAt(int.Parse(cell.InnerText)).InnerText);
                                                                        Col0 = ColumnPart(cell.CellReference); Row0 = RowPart(cell.CellReference);
                                                                        using (doc1)
                                                                        {
                                                                            InsertText(doc1, sheet.Name, cell.InnerText, Col0, Convert.ToUInt32(Row0));
                                                                        }
                                                                    }
                                                                    break;
                                                            }
                                                        }
                                                    }
                                                }
    
                                                if (cell.StyleIndex != null)
                                                {
                                                    int styleIndex = Convert.ToInt32(cell.StyleIndex.Value);
                                                    CellFormats cellFormats = ss.CellFormats;
                                                    DocumentFormat.OpenXml.Spreadsheet.CellFormat cellFormat = cellFormats.Descendants<DocumentFormat.OpenXml.Spreadsheet.CellFormat>().ElementAt(styleIndex);
                                                    try
                                                    {
                                                        DocumentFormat.OpenXml.Spreadsheet.Border border = wstylePart.Stylesheet.Elements<DocumentFormat.OpenXml.Spreadsheet.Borders>().First().Elements<DocumentFormat.OpenXml.Spreadsheet.Border>().ElementAt((int)(uint)cellFormat.BorderId);
                                                        using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                                        {
                                                            //if (border.LeftBorder.Style != null)
                                                                //sw.WriteLine("Left Border Style is {0}", border.LeftBorder.Style);
    
                                                            //if (border.LeftBorder.Elements<DocumentFormat.OpenXml.Spreadsheet.Color>().FirstOrDefault() != null)
                                                                ////sw.WriteLine("Left Border Color is {0}", (uint)border.LeftBorder.Elements<DocumentFormat.OpenXml.Spreadsheet.Color>().First().Indexed);
                                                                //sw.WriteLine("Left Border Color is {0}", border.LeftBorder.InnerXml);
    
                                                            //if (border.RightBorder.Style != null)
                                                                //sw.WriteLine("Right Border Style is {0}", border.RightBorder.Style);
    
                                                            //if (border.RightBorder.Elements<DocumentFormat.OpenXml.Spreadsheet.Color>().FirstOrDefault() != null)
                                                                ////sw.WriteLine("Right Border Color is {0}", (uint)border.RightBorder.Elements<DocumentFormat.OpenXml.Spreadsheet.Color>().First().Indexed);
                                                                //sw.WriteLine("Right Border Color is {0}", border.RightBorder.InnerXml);
    
                                                            //if (border.TopBorder.Style != null)
                                                                //sw.WriteLine("Top Border Style is {0}", border.TopBorder.Style);
    
                                                            //if (border.TopBorder.Elements<DocumentFormat.OpenXml.Spreadsheet.Color>().FirstOrDefault() != null)
                                                                ////sw.WriteLine("Top Border Color is {0}", (uint)border.TopBorder.Elements<DocumentFormat.OpenXml.Spreadsheet.Color>().First().Indexed);
                                                                //sw.WriteLine("Top Border Color is {0}", border.TopBorder.InnerXml);
    
                                                            //if (border.BottomBorder.Style != null)
                                                                //sw.WriteLine("Bottom Border Style is {0}", border.BottomBorder.Style);
    
                                                            //if (border.BottomBorder.Elements<DocumentFormat.OpenXml.Spreadsheet.Color>().FirstOrDefault() != null)
                                                                ////sw.WriteLine("Bottom Border Color is {0}", (uint)border.BottomBorder.Elements<DocumentFormat.OpenXml.Spreadsheet.Color>().First().Indexed);
                                                                //sw.WriteLine("Bottom Border Color is {0}", border.BottomBorder.InnerXml);
                                                        }
                                                    }
                                                    catch (Exception e2)
                                                    {
                                                        using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                                        {
                                                            //sw.WriteLine("Exception: " + e2.Message + " " + e2.StackTrace);
                                                        }
                                                    }
    
                                                    // cell foregroundcolor/ backgroundcolor
                                                    int fillId = Convert.ToInt32(cellFormat.FillId.Value);
                                                    Fill fill = ss.Descendants<Fill>().ElementAt(fillId);
                                                    if (fill.PatternFill.ForegroundColor != null)
                                                    {
                                                        if (fill.PatternFill.ForegroundColor.Rgb != null)
                                                        {
                                                            //Console.WriteLine("{0}: {1} ForegroundColor is Rgb {2}", sheet.Name, cell.CellReference.Value, fill.PatternFill.ForegroundColor.Rgb.Value);
                                                            using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                                            {
                                                                //sw.WriteLine("{0}: {1} ForegroundColor is Rgb {2}", sheet.Name, cell.CellReference.Value, fill.PatternFill.ForegroundColor.Rgb.Value);
                                                            }
    
                                                        }
                                                        else if (fill.PatternFill.ForegroundColor.Theme != null)
                                                        {
                                                            //Console.WriteLine("{0}: {1} ForegroundColor is Theme {2}", sheet.Name, cell.CellReference.Value, fill.PatternFill.ForegroundColor.Theme.Value);
                                                            using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                                            {
                                                                //sw.WriteLine("{0}: {1} ForegroundColor is Theme {2}", sheet.Name, cell.CellReference.Value, fill.PatternFill.ForegroundColor.Theme.Value);
                                                            }
    
                                                        }
                                                    }
                                                    if (fill.PatternFill.BackgroundColor != null)
                                                    {
                                                        if (fill.PatternFill.BackgroundColor.Rgb != null)
                                                        {
                                                            //Console.WriteLine("{0}: {1} BackgroundColor is Rgb {2}", sheet.Name, cell.CellReference.Value, fill.PatternFill.BackgroundColor.Rgb.Value);
                                                            using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                                            {
                                                                //sw.WriteLine("{0}: {1} BackgroundColor is Rgb {2}", sheet.Name, cell.CellReference.Value, fill.PatternFill.BackgroundColor.Rgb.Value);
                                                            }
                                                        }
                                                        else if (fill.PatternFill.BackgroundColor.Theme != null)
                                                        {
                                                            //Console.WriteLine("{0}: {1} BackgroundColor is Theme {2}", sheet.Name, cell.CellReference.Value, fill.PatternFill.BackgroundColor.Theme.Value);
                                                            using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                                            {
                                                                //sw.WriteLine("{0}: {1} BackgroundColor is Theme {2}", sheet.Name, cell.CellReference.Value, fill.PatternFill.BackgroundColor.Theme.Value);
                                                            }
                                                        }
                                                    }
                                                    // fontsize                  
                                                    int fontId = Convert.ToInt32(cellFormat.FontId.Value);
                                                    DocumentFormat.OpenXml.Spreadsheet.Font font = ss.Descendants<DocumentFormat.OpenXml.Spreadsheet.Font>().ElementAt(fontId);
                                                    //Console.WriteLine("{0}: {1} fontSize is {2}", sheet.Name, cell.CellReference.Value, font.FontSize.Val.ToString());
                                                    using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                                    {
                                                        //sw.WriteLine("{0}: {1} fontSize is {2}", sheet.Name, cell.CellReference.Value, font.FontSize.Val.ToString());
                                                    }
    
                                                    // fontcolor
                                                    if (font.Color != null)
                                                    {
                                                        if (font.Color.Rgb != null)
                                                        {
                                                            //Console.WriteLine("{0}: {1} fontColor is Rgb {2}", sheet.Name, cell.CellReference.Value, font.Color.Rgb.Value);
                                                            using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                                            {
                                                                //sw.WriteLine("{0}: {1} fontColor is Rgb {2}", sheet.Name, cell.CellReference.Value, font.Color.Rgb.Value);
                                                            }
                                                        }
                                                        else if (font.Color.Theme != null)
                                                        {
                                                            //Console.WriteLine("{0}: {1} fontColor is Theme {2}", sheet.Name, cell.CellReference.Value, font.Color.Theme.Value);
                                                            using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                                            {
                                                                //sw.WriteLine("{0}: {1} fontColor is Theme {2}", sheet.Name, cell.CellReference.Value, font.Color.Theme.Value);
                                                            }
                                                        }
                                                    }
    
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                    /*releaseObject(Worksheet0);
                    releaseObject(Book0);
                    releaseObject(ExcelObj);*/
                    using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                    {
                        //sw.WriteLine("Time elapsed to binary file: " + Convert.ToString(stopwatch.Elapsed));
                    }
                }
    
            }
            public static int GetColumnNumber(string name)
            {
                Regex regex = new Regex("[A-Za-z]+");
                Match match = regex.Match(name);
                name = match.Value;
                int number = 0;
                int pow = 1;
                for (int i = name.Length - 1; i >= 0; i--)
                {
                    number += (name[i] - 'A' + 1) * pow;
                    pow *= 26;
                }
    
                return number;
            }        
            private static int GetColumnIndex(string cellReference)
            {
                //remove digits
                string columnReference = Regex.Replace(cellReference.ToUpper(), @"[\d]", string.Empty);
    
                int columnNumber = -1;
                int mulitplier = 1;
    
                //working from the end of the letters take the ASCII code less 64 (so A = 1, B =2...etc)
                //then multiply that number by our multiplier (which starts at 1)
                //multiply our multiplier by 26 as there are 26 letters
                foreach (char c in columnReference.ToCharArray().Reverse())
                {
                    columnNumber += mulitplier * ((int)c - 64);
    
                    mulitplier = mulitplier * 26;
                }
    
                //the result is zero based so return columnnumber + 1 for a 1 based answer
                //this will match Excel's COLUMN function
                return columnNumber + 1;
            }
            static string GetCellValue(Cell theCell, WorkbookPart wbPart)
            {
                string value = null;
                // If the cell does not exist, return an empty string.
                if (theCell != null)
                {
                    value = theCell.InnerText;
    
                    if (theCell.DataType != null)
                    {
                        switch (theCell.DataType.Value)
                        {
                            case CellValues.SharedString:
    
                                var stringTable =
                                    wbPart.GetPartsOfType<SharedStringTablePart>()
                                    .FirstOrDefault();
    
                                if (stringTable != null)
                                {
                                    value =
                                        stringTable.SharedStringTable
                                        .ElementAt(int.Parse(value)).InnerText;
                                }
                                break;
    
                            case CellValues.Boolean:
                                switch (value)
                                {
                                    case "0":
                                        value = "FALSE";
                                        break;
                                    default:
                                        value = "TRUE";
                                        break;
                                }
                                break;
                        }
                    }
                }
                return value;
            }
            public static Boolean CreateSpreadsheetWorkbook(SpreadsheetDocument spreadsheetDocument, string SheetName0)
            {
                //SpreadsheetDocument spreadsheetDocument = null;
                WorkbookPart workbookpart = null;
                WorksheetPart worksheetPart = null;
                Sheet sh = null;
                /*
                if (System.IO.File.Exists(filepath) == false)
                {
                    // Create a spreadsheet document by supplying the filepath.
                    // By default, AutoSave = true, Editable = true, and Type = xlsx.
                    spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
    
                    // Add a WorkbookPart to the document.
                    workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                }
                else
                {
                    spreadsheetDocument = SpreadsheetDocument.Open(filepath, true);*/
                    workbookpart = spreadsheetDocument.WorkbookPart;
                //}
    
                // Add a WorksheetPart to the WorkbookPart.
                worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new SheetData());
    
                try
                {
                    sh = workbookpart.Workbook.Sheets.Elements<Sheet>().FirstOrDefault(s => s.Name.HasValue && s.Name.Value == SheetName0);
                }
                catch (Exception) { }
    
                if (sh == null)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbookpart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
    
                    // Add Sheets to the Workbook.
                    if (sheets == null)
                    {
                        sheets = workbookpart.Workbook.AppendChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
                    }
    
                    String relationshipId = workbookpart.GetIdOfPart(worksheetPart);
    
                    // Get a unique ID for the new worksheet.
                    uint sheetId = 1;
                    if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheets>().Count() > 0)
                    {
                        sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }
    
                    // Give the new worksheet a name.
                    String sheetName = SheetName0;
    
                    // Append the new worksheet and associate it with the workbook.
                    Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                    sheets.Append(sheet);
    
                    workbookpart.Workbook.Save();
    
                    // Close the document.
                    spreadsheetDocument.Close();
    
                    return true;
                }
                else
                {
                    workbookpart.Workbook.Save();
    
                    // Close the document.
                    spreadsheetDocument.Close();
    
                    return false;
                }
            }
            // Given a WorkbookPart, inserts a new worksheet.
            private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
            {
                // Add a new worksheet part to the workbook.
                WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();
    
                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
    
                // Get a unique ID for the new sheet.
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }
    
                string sheetName = "Sheet" + sheetId;
    
                // Append the new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                sheets.Append(sheet);
                workbookPart.Workbook.Save();
    
                return newWorksheetPart;
            }
            public static string ColumnPart(string ParCellRef)
            {
                char[] CharList = ParCellRef.Trim().ToCharArray();
                string Ret0 = "";
                Boolean Char_Appear = false;
                int Val0;
                for (int i = 0; i <= ParCellRef.Trim().Length - 1; i++)
                {
                    Val0 = (int)CharList[i];
                    if (Val0 >= 48 && Val0 <= 57 || Val0 >= 65 && Val0 <= 90 || Val0 >= 97 && Val0 <= 122)
                    {
                        if (Val0 >= 48 && Val0 <= 57)
                        {
                            if (!Char_Appear)
                                return "";
                            else
                                return Ret0;
                        }
                        if (Val0 >= 65 && Val0 <= 90 || Val0 >= 97 && Val0 <= 122)
                        {
                            Ret0 = Ret0 + CharList[i];
                            Char_Appear = true;
                        }
                    }
                    else
                        return "";
    
                }
                return Ret0;
            }
            public static string RowPart(string ParCellRef)
            {
                char[] CharList = ParCellRef.Trim().ToCharArray();
                string Ret0 = "";
                Boolean Char_Appear = false;
                int Val0;
                for (int i = 0; i <= ParCellRef.Trim().Length - 1; i++)
                {
                    Val0 = (int)CharList[i];
                    if (Val0 >= 48 && Val0 <= 57 || Val0 >= 65 && Val0 <= 90 || Val0 >= 97 && Val0 <= 122)
                    {
                        if (Val0 >= 65 && Val0 <= 90 || Val0 >= 97 && Val0 <= 122)
                        {
                            Char_Appear = true;
                        }
                        if (Val0 >= 48 && Val0 <= 57)
                        {
                            if (!Char_Appear)
                                return "";
    
                            Ret0 = Ret0 + CharList[i];
                        }
                    }
                    else
                        return "";
    
                }
                return Ret0;
            }
            public static void InsertText(SpreadsheetDocument spreadSheet, string SheetName0, string text, string CellRowRef0, uint CellColRef0)
            {
                //using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                // Open the document for editing.
                //Using(SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                //{
                // Get the SharedStringTablePart. If it does not exist, create a new one.
                SharedStringTablePart shareStringPart;
                if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                {
                    shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                }
                else
                {
                    shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
                }
    
                // Insert the text into the SharedStringTablePart.
                // int index = InsertSharedStringItem(text, shareStringPart);
    
                // Insert a new worksheet.
                WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);
    
                // Insert cell A1 into the new worksheet.
                Cell cell = InsertCellInWorksheet(CellRowRef0, CellColRef0, worksheetPart);
    
                // Set the value of cell A1.
                cell.CellValue = new CellValue(text);
                cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
    
                // Save the new worksheet.
                worksheetPart.Worksheet.Save();
                //}
            }
    
            // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
            // If the cell already exists, returns it. 
            private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
            {
                DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet;
                SheetData sheetData = worksheet.GetFirstChild<SheetData>();
                string cellReference = columnName + rowIndex;
    
                // If the worksheet does not contain a row with the specified row index, insert one.
                Row row;
                if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
                {
                    row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
                }
                else
                {
                    row = new Row() { RowIndex = rowIndex };
                    sheetData.Append(row);
                }
    
                // If there is not a cell with the specified column name, insert one.  
                if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
                {
                    return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
                }
                else
                {
                    // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                    Cell refCell = null;
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                        {
                            refCell = cell;
                            break;
                        }
                    }
    
                    Cell newCell = new Cell() { CellReference = cellReference };
                    row.InsertBefore(newCell, refCell);
    
                    worksheet.Save();
                    return newCell;
                }
            }
            // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
            // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
            private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
            {
                // If the part does not contain a SharedStringTable, create one.
                if (shareStringPart.SharedStringTable == null)
                {
                    shareStringPart.SharedStringTable = new SharedStringTable();
                }
    
                int i = 0;
    
                // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
                foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
                {
                    if (item.InnerText == text)
                    {
                        return i;
                    }
    
                    i++;
                }
    
                // The text does not exist in the part. Create the SharedStringItem and return its index.
                shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
                shareStringPart.SharedStringTable.Save();
    
                return i;
            }



    Many Thanks & Best Regards, Hua Min

    Tuesday, November 22, 2016 9:52 AM
  • Hi,

    Any advice to this?


    Many Thanks & Best Regards, Hua Min

    Thursday, November 24, 2016 8:39 AM
  • Hi,

    Using the code you shared, I could reproduce your issue.

    I find that the document is closed when your create the worksheet for the new workbook, in the method: Boolean b = CreateSpreadsheetWorkbook(doc1, sheet.Name);

    So in the method CreateSpreadsheetWorkbook, you need to remove the line  spreadsheetDocument.Close();

    However, I failed to copy the content into the new workbook with the whole code.

    Does the code work for you? 

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, November 24, 2016 11:14 AM
    Moderator
  • Hi,

    How to ensure it would copy everything into new Workbook? Do I need one other thread?


    Many Thanks & Best Regards, Hua Min

    Thursday, November 24, 2016 3:29 PM

  • However, I failed to copy the content into the new workbook with the whole code.

    Does the code work for you? 

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Hi,
    I adjust the event like
            public static Boolean CreateSpreadsheetWorkbook(SpreadsheetDocument spreadsheetDocument, string SheetName0)
            {
                //SpreadsheetDocument spreadsheetDocument = null;
                WorkbookPart workbookpart = null;
                WorksheetPart worksheetPart = null;
                Sheet sh = null;
                /*
                if (System.IO.File.Exists(filepath) == false)
                {
                    // Create a spreadsheet document by supplying the filepath.
                    // By default, AutoSave = true, Editable = true, and Type = xlsx.
                    spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
    
                    // Add a WorkbookPart to the document.
                    workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                }
                else
                {
                    spreadsheetDocument = SpreadsheetDocument.Open(filepath, true);*/
                    workbookpart = spreadsheetDocument.WorkbookPart;
                //}
    
                // Add a WorksheetPart to the WorkbookPart.
                worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new SheetData());
    
                try
                {
                    sh = workbookpart.Workbook.Sheets.Elements<Sheet>().FirstOrDefault(s => s.Name.HasValue && s.Name.Value == SheetName0);
                }
                catch (Exception) { }
    
                if (sh == null)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbookpart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
    
                    // Add Sheets to the Workbook.
                    if (sheets == null)
                    {
                        sheets = workbookpart.Workbook.AppendChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
                    }
    
                    String relationshipId = workbookpart.GetIdOfPart(worksheetPart);
    
                    // Get a unique ID for the new worksheet.
                    uint sheetId = 1;
                    if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheets>().Count() > 0)
                    {
                        sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }
    
                    // Give the new worksheet a name.
                    String sheetName = SheetName0;
    
                    // Append the new worksheet and associate it with the workbook.
                    Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                    sheets.Append(sheet);
    
                    workbookpart.Workbook.Save();
    
                    // Close the document.
                    //spreadsheetDocument.Close();
    
                    return true;
                }
                else
                {
                    workbookpart.Workbook.Save();
    
                    // Close the document.
                    //spreadsheetDocument.Close();
    
                    return false;
                }
            }



    But I still get the same exception due to this line
    if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)

    Many Thanks & Best Regards, Hua Min

    Friday, November 25, 2016 1:45 AM
  • Hi,

    Do you have any other change in this project?

    I suggest you check the code again and see if there any code to close the document which need to use next.

    Do you have several version projects?

    Because you could run the similar code to copy successfully in the Problem to slow process. However you receive the exception in this thread.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Sunday, November 27, 2016 8:40 AM
    Moderator
  • No, that thread (Problem to slow process) is definitely the same process but I now do get such exception and how to resolve it?

    Many Thanks & Best Regards, Hua Min


    Sunday, November 27, 2016 3:41 PM
  • Hi Celeste,

    Any other advice?


    Many Thanks & Best Regards, Hua Min

    Tuesday, November 29, 2016 4:40 AM
  • Hi,

    I think nested using SpreadsheetDocument causes the issue.

    My suggestion is to open and close every time. You could test the project and see what I have adjust: https://1drv.ms/u/s!AkcxDWH1nFmJpQMScnSMJlhRtKNy

    Besides, there is no need to create an Excel application instance when you using Open XML Library. 

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 29, 2016 5:52 AM
    Moderator
  • Hi,
    Should I use CopySheet event to duplicate one Sheet from one Workbook into another one? I see that you're only calling such event upon one button click.

    Many Thanks & Best Regards, Hua Min

    Tuesday, November 29, 2016 6:35 AM
  • No. It can only copy sheets in the same workbook. It is unhelpful for your issue to copy into the new one.

    Copying sheets into the new workbook is more complicated.

    If you adjust your code, could you successfully copy the worksheet into a new workbook?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 29, 2016 6:53 AM
    Moderator
  • Hi,
    Is there one way to one sheet from one Workbook into another one?

    Many Thanks & Best Regards, Hua Min

    Tuesday, November 29, 2016 6:59 AM
  • Hi,

    You could refer to code below.

    The logic is: create the new workbook, copy the stylesheet into the new, then retrieve the cell value and styleindex from the old and then insert it into the new.

    The code below shows how to copy the stylesheet and insert to the new workbook. The part to retrieve the cell value and styleindex has been finished like the code you are using now.

    You could create a winform app and add three button: CreateDoc /CopyStylePart/InsertVal. Please test if it works for you.

    Regards,

    Celeste

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    namespace WinFormApp
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void CreateDoc_Click(object sender, EventArgs e)
            {
                CreateSpreadsheetWorkbook(@"E:\test\test.xlsx", "testsheet");
                MessageBox.Show("Done");
            }
            public static void CreateSpreadsheetWorkbook(string filepath, string sheetName)
            {
                if (System.IO.File.Exists(filepath))
                {
                    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filepath, true))
                    {
                        WorksheetPart newWorksheetPart =
                            spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>();
                        newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                        Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                        string relationshipId =
                            spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart);
                        uint sheetId = 1;
                        if (sheets.Elements<Sheet>().Count() > 0)
                        {
                            sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                        }
                        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                        sheets.Append(sheet);
                    }
                }
                else
                {
                    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
                    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();
                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());
                    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                        AppendChild<Sheets>(new Sheets());
                    Sheet sheet = new Sheet()
                    {
                        Id = spreadsheetDocument.WorkbookPart.
                        GetIdOfPart(worksheetPart),
                        SheetId = 1,
                        Name = sheetName
                    };
                    sheets.Append(sheet);
                    workbookpart.Workbook.Save();
                    spreadsheetDocument.Close();
                }
            }
            private void InsertVal_Click(object sender, EventArgs e)
            {
                InsertText(@"E:\test\test.xlsx", "testsheet", "B", 1,1, "test1");
                MessageBox.Show("Done");
            }
    
            public static void InsertText(string docName, string sheetName, string columnName, uint rowIndex, uint styleIndex, string text)
            {
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                {
                    SharedStringTablePart shareStringPart;
                    if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                    {
                        shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                    }
                    else
                    {
                        shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
                    }
                    int index = InsertSharedStringItem(text, shareStringPart);
                    IEnumerable<Sheet> sheets = spreadSheet.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
                    if (sheets.Count() == 0)
                    {
                        return;
                    }
                    WorksheetPart worksheetPart = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(sheets.First().Id);
                    Cell cell = InsertCellInWorksheet(columnName, rowIndex, worksheetPart);
                    cell.StyleIndex = styleIndex;
                    cell.CellValue = new CellValue(index.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                    worksheetPart.Worksheet.Save();
                }
            }
            private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
            {
                if (shareStringPart.SharedStringTable == null)
                {
                    shareStringPart.SharedStringTable = new SharedStringTable();
                }
    
                int i = 0;
                foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
                {
                    if (item.InnerText == text)
                    {
                        return i;
                    }
    
                    i++;
                }
                shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
                shareStringPart.SharedStringTable.Save();
                return i;
            }
            private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
            {
                Worksheet worksheet = worksheetPart.Worksheet;
                SheetData sheetData = worksheet.GetFirstChild<SheetData>();
                string cellReference = columnName + rowIndex;
                Row row;
                if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
                {
                    row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
                }
                else
                {
                    row = new Row() { RowIndex = rowIndex };
                    sheetData.Append(row);
                }
                if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
                {
                    return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
                }
                else
                {
                    Cell refCell = null;
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                        {
                            refCell = cell;
                            break;
                        }
                    }
                    Cell newCell = new Cell() { CellReference = cellReference };
                    row.InsertBefore(newCell, refCell);
                    worksheet.Save();
                    return newCell;
                }
            }
            private void CopyStylePart_Click(object sender, EventArgs e)
            {
                copyStyle(@"E:\test\1.xlsx", @"E:\test\test.xlsx");
                MessageBox.Show("Done");
            }
            private static void copyStyle(string docName, string targetdocName)
            {
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                {
                    using (SpreadsheetDocument targetspreadSheet = SpreadsheetDocument.Open(targetdocName, true))
                    {
                        WorkbookPart workbookPart = spreadSheet.WorkbookPart;
                        WorkbookStylesPart workbookstylePart = workbookPart.WorkbookStylesPart;
                        Stylesheet stylesheet = workbookstylePart.Stylesheet;
                        Fonts fonts = stylesheet.Fonts;
                        Fills fills = stylesheet.Fills;
                        Borders borders = stylesheet.Borders;
                        CellStyleFormats cellstyleFormats = stylesheet.CellStyleFormats;
                        CellFormats cellFormats = stylesheet.CellFormats;
    
                        WorkbookPart targetworkbookPart = targetspreadSheet.WorkbookPart;
                        WorkbookStylesPart targetworkbookstylePart = targetworkbookPart.AddNewPart<WorkbookStylesPart>();
                        Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac x16r2" } };
                        stylesheet1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
                        stylesheet1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
                        stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
                        stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
                        stylesheet1.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
                        stylesheet1.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                        stylesheet1.Append(fonts.CloneNode(true));
                        stylesheet1.Append(fills.CloneNode(true));
                        stylesheet1.Append(borders.CloneNode(true));
                        stylesheet1.Append(cellstyleFormats.CloneNode(true));
                        stylesheet1.Append(cellFormats.CloneNode(true));
                        targetworkbookstylePart.Stylesheet = stylesheet1;
                    }
                }
            }    
        }
    }
    

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 30, 2016 9:50 AM
    Moderator
  • Sorry, how to resolve this

    Error	3	Argument 1: cannot convert from 'DocumentFormat.OpenXml.Packaging.SpreadsheetDocument' to 'string'	C:\WinApp5\WindowsFormsApplication2\Form1.cs	186	72	WindowsFormsApplication2
    


    due to last line below?

                                using (SpreadsheetDocument doc1 = SpreadsheetDocument.Open(fs1, true))
                                ...
                                                            using (doc1)
                                                            {
                                                                InsertText(doc1, sheet.Name, Col0, Convert.ToUInt32(Row0), styleIndex, cell.InnerText);
                                                                ...



    Many Thanks & Best Regards, Hua Min

    Friday, December 2, 2016 2:40 AM
  • Hi,

    Any help/advice?


    Many Thanks & Best Regards, Hua Min

    Monday, December 5, 2016 2:39 AM
  • Hi,

    You need to use the method like InsertText(fileName, sheet.Name, Col0, Convert.ToUInt32(Row0), styleIndex, cell.InnerText);

    The underline part is a string. So pass file name as a string. Or you could adjust the method.

    Since your requirement to copy sheets to another workbook is complicated, I have asked for a code sample. If there are any update, I would post here.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 5, 2016 5:27 AM
    Moderator
  • Appreciated.
    >> I have asked for a code sample.
    Do you mean other team is further checking this problem?

    Many Thanks & Best Regards, Hua Min

    Monday, December 5, 2016 7:54 AM
  • Hi,

    There is a Code Sample Team to write some samples. I have asked for the sample for copying workbooks using Open XML.

    Now you could try the project I created. https://1drv.ms/u/s!AkcxDWH1nFmJpQMScnSMJlhRtKNy

    It could copy and write the some cell values with style except column width.

    Create a workbook with some value with some styles and then click in the following order.

    CreateDoc_Click -> CopyStylePart_Click -> CopyRowNodes_Click -> GetStyleIndex_Click

    If there are any issues to use the code above, I suggest you post new threads.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jackson_1990 Friday, December 9, 2016 9:19 AM
    Friday, December 9, 2016 5:47 AM
    Moderator
  • Hi Celeste,
    Many thanks. Is the project the same as the one you provided to me, within recent two weeks? How would you further inform me, if you've got reply from other code team?

    Many Thanks & Best Regards, Hua Min


    Friday, December 9, 2016 8:38 AM
  • Hi,

    The project I upload on OneDrive is updated. It now could copy cells value with styles like font color/ background color/ bold etc. You could download and have a test.

    If there is any update for the code sample, I would post here.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 9, 2016 8:51 AM
    Moderator