none
Problem to write to Worksheet with InnerText RRS feed

  • Question

  • Hi,
    I cannot properly write to Worksheet with values by codes below, while there're values to vCO, vRO, vSI, and vIT (vIT is having value to InnerText). Why?
                            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(@Path0, true))
                            {
                                if (vIT != "")
                                {
                                    index = InsertSharedStringItem(vIT, shareStringPart);
                                }
                                IEnumerable<Sheet> sheets = spreadSheet.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == vSN);
                                // if the specific sheet doesnot exist, create new 
                                if (sheets.Count() == 0)
                                {
                                    InsertWorksheetCus(spreadSheet.WorkbookPart, vSN);
                                }
                                // get the worksheetPart
                                WorksheetPart worksheetPart2 = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(sheets.First().Id);
    
                                string columnName;
                                uint rowIndex;
                                if (vCO != "" && vRO != "")
                                {
                                    columnName = vCO + vRO;
                                    rowIndex = Convert.ToUInt32(vRO);
                                    Cell cell = InsertCellInWorksheet(columnName, rowIndex, worksheetPart2);
                                    if (vSI != "")
                                        cell.StyleIndex = Convert.ToUInt32(vSI);
    
                                    if (index != 0)
                                    {
                                        cell.CellValue = new CellValue(index.ToString());
                                        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                                    }
    
                                    worksheetPart2.Worksheet.Save();
                                }
                                ...
                                
            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;
                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;
                }
            }



    Many Thanks & Best Regards, Hua Min

    Friday, December 30, 2016 9:27 AM

Answers

  • Hi HuaMin  Chen,

    I find some mistakes in your edited code.

    below is your original code edited by you.

     IEnumerable<DocumentFormat.OpenXml.Spreadsheet.Sheets> sheets =
                   document.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().
                   Elements<DocumentFormat.OpenXml.Spreadsheet.Sheets>().Where(s => s.Name == sheetName);

    below is the corrected code by me.

    you pass "Sheets" everywhere so you got an error.

    Regards

    Deepak


    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 Monday, January 16, 2017 9:33 AM
    Monday, January 16, 2017 6:37 AM
    Moderator

All replies

  • Hi HuaMin  Chen,

    you had mentioned that,"I cannot properly write to Worksheet".

    did you got an error? or you got the undesired output.

    from your above description it looks like you have an issue with below lines of code.

    if (vIT != "")
    {
         index = InsertSharedStringItem(vIT, shareStringPart);
    }
    

    you did not mentioned that what are the values you had set of vCO, vRO, vSI, and vIT .

    it is possible that you have issue with "InsertSharedStringItem" method.

    but you did not post a "InsertSharedStringItem" method.

    instead of that you had post "InsertCellInWorksheet" method.

    do you have any issue with "InsertCellInWorksheet" method?

    please let us know about the above mentioned details so that we can try to reproduce the issue on our side.

    Regards

    Deepak


    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, January 2, 2017 1:07 AM
    Moderator
  • vCO is B, vRO is 1, vIT is MCU, and vSI is empty.

    Here is the event
            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;
            }


    Please now leave the other event.

    Many Thanks & Best Regards, Hua Min

    Monday, January 2, 2017 8:39 AM
  • Hi HuaMin  Chen,

    I try to check the there is nothing wrong with InsertSharedStringItem method.

    as you mentioned above I create variables. its looks like you want to add value of vIT in "B1" cell that stored with in vCO and vRO. below is a working example of that.

            static void Main(string[] args)
            {
                string Path;
                Path = @"C:\Users\v-padee\Desktop\xmldemo.xlsx";
                string vIT = "MCU";
                string vSL = "";
              
    
                if (vIT != "")
                    {
                        InsertText(Path, vIT);
                    }
    
            }
            public static void InsertText(string docName, string text)
            {
                string vCO = "B";
                uint vRO = 1;
               
                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);
    
                  
                    WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);
    
                  
                    Cell cell = InsertCellInWorksheet(vCO, vRO, worksheetPart);
    
                   
                    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 WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
            {
               
                WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();
    
                Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
    
              
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }
    
                string sheetName = "Sheet" + sheetId;
    
             
                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                sheets.Append(sheet);
                workbookPart.Workbook.Save();
    
                return newWorksheetPart;
            }
    
          
            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;
                }
            }
    
    

    Regards

    Deepak


    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, January 3, 2017 4:07 AM
    Moderator
  • I get problem like

    to run this project



    Many Thanks & Best Regards, Hua Min

    Tuesday, January 3, 2017 9:35 AM
  • Hi HuaMin  Chen,

    I try to check the project and I find that you had added so many library references that are not used anywhere in the code.

    so please try to remove it.

    Here below I post my full code. which is working on my side and adding a "MCU" value in B1 cell.

    I suggest you to create a new project.

    then just add reference for Open XML 2.5 and Windows.base.

    you need not to add any other library reference to run this code.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    namespace ConsoleApplication18
    {
        
        class Program
        {
        
            static void Main(string[] args)
            {
                string Path;
                Path = @"C:\Users\v-padee\Desktop\xmldemo.xlsx";
                string vIT = "MCU";
                string vSL = "";
              
    
                if (vIT != "")
                    {
                        InsertText(Path, vIT);
                    }
    
            }
            public static void InsertText(string docName, string text)
            {
                string vCO = "B";
                uint vRO = 1;
               
                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);
    
                  
                    WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);
    
                  
                    Cell cell = InsertCellInWorksheet(vCO, vRO, worksheetPart);
    
                   
                    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 WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
            {
               
                WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();
    
                Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
    
              
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }
    
                string sheetName = "Sheet" + sheetId;
    
             
                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                sheets.Append(sheet);
                workbookPart.Workbook.Save();
    
                return newWorksheetPart;
            }
    
          
            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;
                }
            }
    
    
        }
    }
    

    Regards

    Deepak


    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, January 4, 2017 8:43 AM
    Moderator
  • No, the question is, how to resolve this exception
    DocumentFormat.OpenXml.Packaging.OpenXmlPackageException was unhandled
      HResult=-2146233088
      Message=The specified package is invalid. The main part is missing.
      Source=DocumentFormat.OpenXml
      StackTrace:
           at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Load()
           at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.OpenCore(String path, Boolean readWriteMode)
           at DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(String path, Boolean isEditable, OpenSettings openSettings)
           at DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(String path, Boolean isEditable)
           at ConsoleApplication9_4_1_0.Program.InsertText(String docName, String text) in c:\dp13\General Application\ConsoleApplication9_4_1_3 Sample from MSDN\ConsoleApplication9_4_1_0\Program.cs:line 46
           at ConsoleApplication9_4_1_0.Program.Main(String[] args) in c:\dp13\General Application\ConsoleApplication9_4_1_3 Sample from MSDN\ConsoleApplication9_4_1_0\Program.cs:line 37
           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: 



    to these project, having the same codes you demonstrated!



    Many Thanks & Best Regards, Hua Min

    Thursday, January 5, 2017 2:48 AM
  • Hi HuaMin  Chen,

    yes , I know that your project only contains the same code suggested by me.

    but as I informed you in my last reply. I can see that you had added many library references that are not used in the code.

    you can see in the picture below.

    in References:

    you can see that it is giving me a warning on "Microsoft.Office.Core" is not available.

    because of this I am getting error.

    so simply right click on that and remove it.

    also remove other unused references.

    just like below.

    in references:

    it will solve the error. I running your project on my side without error.

    you can try to test on your side.

    I suggest you to create new project in my last reply. because if you create new project then you not need to remove these references and error will be solved. I thought it will be easy for you.

    anyway , you can try to remove Office.core and run the project.

    Regards

    Deepak 


    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, January 5, 2017 3:21 AM
    Moderator
  • I removed reference of ...Office.Core

    but I get this exception
    DocumentFormat.OpenXml.Packaging.OpenXmlPackageException was unhandled
      HResult=-2146233088
      Message=The specified package is invalid. The main part is missing.
      Source=DocumentFormat.OpenXml
      StackTrace:
           at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Load()
           at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.OpenCore(String path, Boolean readWriteMode)
           at DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(String path, Boolean isEditable, OpenSettings openSettings)
           at DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(String path, Boolean isEditable)
           at ConsoleApplication9_4_1_0.Program.InsertText(String docName, String text) in c:\dp13\General Application\ConsoleApplication9_4_1_5 Sample from MSDN - Copy\ConsoleApplication9_4_1_0\Program.cs:line 46
           at ConsoleApplication9_4_1_0.Program.Main(String[] args) in c:\dp13\General Application\ConsoleApplication9_4_1_5 Sample from MSDN - Copy\ConsoleApplication9_4_1_0\Program.cs:line 37
           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
    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))

    on this project.


    Many Thanks & Best Regards, Hua Min

    Thursday, January 5, 2017 4:18 AM
  • Hi HuaMin  Chen,

    may be I find the root cause of the issue.

    some days ago you had posted a below question on this forum.

    Problem to open Excel file

    in that case you had the same error.

    DocumentFormat.OpenXml.Packaging.OpenXmlPackageException was unhandled
      HResult=-2146233088
      Message=The specified package is invalid. The main part is missing.
      Source=DocumentFormat.OpenXml
    

    the reason was you are not creating file correctly.

    I think that you are using that same file or still using that same method to create file.

    that's why you are getting this error at your side and on my side code is working correctly.

    here I confirms that there is no any error or problem with the code. the problem is related with the file.

    to test, you can create excel file manually and save it.

    pass the path of that file in the above mentioned code.

    run the code.

    you will find that code will run without error.

    so you need to check with the file that you create with code.

    Regards

    Deepak


    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, January 5, 2017 5:50 AM
    Moderator
  • I did delete relevant Excel file and then re-run the project but I've got the same exception.

    Many Thanks & Best Regards, Hua Min

    Thursday, January 5, 2017 6:06 AM
  • Hi HuaMin  Chen,

    first confirm the following things, it will help me to understand the issue properly.

    you had mentioned that,"you delete the file and re run the project."

    after deleting the file. how you create a new file?

    using code? or using manually by using user interface of Excel Application?

    if still you are creating the file using code then it is possible that there is some mistake in your code that creates corrupted file.

    for testing purpose, please create the file using user interface of excel application and run the code.

    so please tell me the approach to create Excel file and let me know about your testing results.

    Regards

    Deepak 


    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, January 5, 2017 6:31 AM
    Moderator
  • Hi,
    I tested it fine by one manually created Excel file. I think there is A DIFFERENCE between your codes and my original project, as I did write to one specific Excel worksheet (with a GIVEN NAME). Can you please adjust your example to also show this?

    Many Thanks & Best Regards, Hua Min

    Thursday, January 5, 2017 6:48 AM
  • Hi HuaMin  Chen,

    Below is the updated code as per your requirement.

    it will add the text in existing sheet.

    you have to pass file name, sheet name, text , column, row.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using System.Xml;
    using System.Diagnostics;
    
    namespace ConsoleApplication20
    {
        class Program
        {
            static void Main(string[] args)
            {
                string Path;
                Path = @"C:\Users\v-padee\Desktop\xmldemo.xlsx";
                string vIT = "MCU";
                string vSL = "";
                string vCO = "B";
                uint vRO = 1;
                string shtname = "sheet1";
                InsertCell(Path, shtname, vIT, vRO, vCO);
               
            }
            public static void InsertCell(string docName,string sheetname, string text,
                uint rowIndex, string columnName)
            {
               
                using (SpreadsheetDocument spreadSheet =
                         SpreadsheetDocument.Open(docName, true))
                {
                    WorksheetPart worksheetPart =
                          GetWorksheetPartByName(spreadSheet, sheetname);
    
                    if (worksheetPart != null)
                    {
                        Cell cell = GetCell(worksheetPart.Worksheet,
                                                 columnName, rowIndex);
    
                        cell.CellValue = new CellValue(text);
                        cell.DataType =new EnumValue<CellValues>(CellValues.Number);
    
                        worksheetPart.Worksheet.Save();
                    }
                }
    
            }
    
            private static WorksheetPart
                 GetWorksheetPartByName(SpreadsheetDocument document,
                 string sheetName)
            {
                IEnumerable<Sheet> sheets =
                   document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
                   Elements<Sheet>().Where(s => s.Name == sheetName);
    
                if (sheets.Count() == 0)
                {
    
                    return null;
                }
    
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)
                     document.WorkbookPart.GetPartById(relationshipId);
                return worksheetPart;
    
            }
    
         
            private static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
            {
                Row row;
                string cellReference = columnName + rowIndex;
                if (worksheet.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
                    row = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
                else {
                    row = new Row() { RowIndex = rowIndex };
                    worksheet.Append(row);
                }
    
                if (row == null)
                    return null;
    
                if (row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).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,
                        StyleIndex = (UInt32Value)1U
    
                    };
                    row.InsertBefore(newCell, refCell);
                    worksheet.Save();
                    return newCell;
                }
            }
    
            private static Row GetRow(Worksheet worksheet, uint rowIndex)
            {
                return worksheet.GetFirstChild<SheetData>().
                  Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
        }
    
    }
    
    

    I have tested it and working fine on my side.

    Regards

    Deepak


    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, January 6, 2017 10:03 AM
    Moderator
  • Hi HuaMin  Chen,

    you had asked,"Why not you create Worksheet with specific name when Count() is 0 below?"

    Maybe I misunderstand your last post.

    I thought you want to insert data in the existing sheet.

    so I did not add a new worksheet there.

    so if you want to create a worksheet with specific name then you can modify the Sheet name in line below.

     string sheetName = "Sheet" + sheetId;
    

    this line is part of code which I provided you second last time(Wednesday, January 04, 2017 8:43 AM). which one work for you when you tested it with manually created file.

    you can give any name in the place of "Sheet" and remove "+ sheetId".

    if you want to add value in already existed sheet then you can use the code which I provided last time(Friday, January 06, 2017 10:03 AM).

    below is an another example of creating worksheet with specific name.

    Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.
    GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
    sheets.Append(sheet);
    

    Reference

    How to: Create a spreadsheet document by providing a file name (Open XML SDK)

    Regards

    Deepak


    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, January 9, 2017 6:29 AM
    Moderator
  • In your current example, within event

    InsertText

    is it possible to adjust the codes to ENABLE, the way to write to given Worksheet with a given name? If the worksheet does not exist, then create it!


    Many Thanks & Best Regards, Hua Min



    Monday, January 9, 2017 7:04 AM
  • Hi HuaMin  Chen,

    so it's like you want to check first that particular worksheet is exist or not. if worksheet is not available then you want to create a new worksheet with that name.

    you can use the code below to check whether worksheet is available or not.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    
    namespace ConsoleApplication22
    {
        class Program
        {
            const string DEMOFILE =
                   @"C:\Users\v-padee\Desktop\demosheet.xlsx";
    
            static void Main(string[] args)
            {
                var results = GetAllWorksheets(DEMOFILE);
                foreach (Sheet item in results)
                {
                    if (item.Name=="Sheet2")
                    {
                        Console.WriteLine("Sheet found");
                        Console.ReadLine();
                    }
                    
                   
                }
    
            }
            public static Sheets GetAllWorksheets(string fileName)
            {
                Sheets theSheets = null;
    
                using (SpreadsheetDocument document =
                    SpreadsheetDocument.Open(fileName, false))
                {
                    WorkbookPart wbPart = document.WorkbookPart;
                    theSheets = wbPart.Workbook.Sheets;
                }
                return theSheets;
            }
    
        }
    }
    

    "how to create new worksheet with particular name" and "how to insert data to particular worksheet " I already suggested you in my previous posts.

    implement this code in your code to achieve your requirement. 

    Regards

    Deepak


    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, January 10, 2017 5:38 AM
    Moderator
  • Hi,
    >> if worksheet is not available then you want to create a new worksheet with that name.
    Sorry Deepak, I do not see you create the relevant Worksheet, if IT DOES NOT exist, in the above codes.

    Many Thanks & Best Regards, Hua Min

    Tuesday, January 10, 2017 6:20 AM
  • Hi HuaMin  Chen,

    I think you did not view the last reply properly.

    I had already mentioned that,

    ("how to create new worksheet with particular name" and "how to insert data to particular worksheet " I already suggested you in my previous posts.

    implement this code in your code to achieve your requirement. )

    means I already provided a code for creating a worksheet. please find it in my previous posts and use it. because there is no meaning of posting the same code multiple times.

    you have to merge that code by yourself.

    Regards

    Deepak 


    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, January 10, 2017 6:34 AM
    Moderator
  • Sorry, within this event
            public static void InsertText(string docName, string text)
            {
                string vCO = "B";
                uint vRO = 1;
    
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                {
    
                    SharedStringTablePart shareStringPart;
                    if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                    {
                        shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                    }
                    ...


    if I want to update into one worksheet WITH ONE SPECIFIC NAME, what to change?

    Many Thanks & Best Regards, Hua Min


    Tuesday, January 10, 2017 7:05 AM
  • Hi HuaMin  Chen,

    you need to find for the worksheet part.

    the example of that method is described in the other code demo that I had mentioned above.

    if you find the worksheet part then you can use it to write data to that sheet.

    if you get null that means sheet is not available then you need to create sheet and then enter data in it.

    so you can use if..else to check that if worksheet part is there you can write to it.

    else you can execute the code to create a sheet with that name.

    Regards

    Deepak  


    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, January 11, 2017 6:54 AM
    Moderator
  • Deepak,

    This is really the place I got stuck to, and I really need your help to demonstrate your way properly!

    Many Thanks & Best Regards, Hua Min

    Wednesday, January 11, 2017 8:15 AM
  • Deepak,

    Can you please demonstrate the way properly per my request?


    Many Thanks & Best Regards, Hua Min

    Thursday, January 12, 2017 7:16 AM
  • Hi HuaMin  Chen,

    I try to change it as per your requirement.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    
    namespace ConsoleApplication23
    {
        
        class Program
        {
           
            static void Main(string[] args)
            {
                string sht = "DemoSheet";
                string Path;
                Path = @"C:\Users\v-padee\Desktop\xmldemo.xlsx";
                string vIT = "MCU";
                string vSL = "";
    
    
                if (vIT != "")
                {
                    InsertText(Path, vIT,sht);
                }
            }
            private static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document,string sheetName)
            {
                IEnumerable<Sheet> sheets =
                   document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
                   Elements<Sheet>().Where(s => s.Name == sheetName);
    
                if (sheets.Count() == 0)
                {
    
                    return null;
                }
    
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)
                     document.WorkbookPart.GetPartById(relationshipId);
                return worksheetPart;
    
            }
            public static void InsertText(string docName, string text,string sht)
            {
                string vCO = "B";
                uint vRO = 1;
    
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                {
    
    
                    WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, sht);
    
                    if (worksheetPart != null)
                    {
                        Cell cell = GetCell(worksheetPart.Worksheet,
                                                 vCO, vRO);
    
                        cell.CellValue = new CellValue(text);
                        cell.DataType = new EnumValue<CellValues>(CellValues.Number);
    
    
                       
    
    
                        worksheetPart.Worksheet.Save();
                    }
                    else
                    {
                        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);
    
    
                        WorksheetPart worksheetPart1 = InsertWorksheet(spreadSheet.WorkbookPart,sht);
    
    
                        Cell cell = InsertCellInWorksheet(vCO, vRO, worksheetPart1);
    
    
                        cell.CellValue = new CellValue(index.ToString());
                        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                    }
                }
            }
    
    
            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 WorksheetPart InsertWorksheet(WorkbookPart workbookPart,string sht)
            {
    
                WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();
    
                Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
    
    
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }
    
                string sheetName = sht;  //"Sheet" + sheetId;
    
    
                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                sheets.Append(sheet);
                workbookPart.Workbook.Save();
    
                return newWorksheetPart;
            }
    
    
            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 static Cell GetCell(Worksheet worksheet,
                     string columnName, uint rowIndex)
            {
                Row row = GetRow(worksheet, rowIndex);
    
                if (row == null)
                    return null;
    
                return row.Elements<Cell>().Where(c => string.Compare
                       (c.CellReference.Value, columnName +
                       rowIndex, true) == 0).First();
            }
            private static Row GetRow(Worksheet worksheet, uint rowIndex)
            {
                return worksheet.GetFirstChild<SheetData>().
                  Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
    
        }
    }
    
    

    still some modifications needed to do in code above.

    modification needs to be done in part when Sheet is already exist.

    Regards

    Deepak


    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, January 12, 2017 8:37 AM
    Moderator
  • Any advice to this error
    Error	3	'DocumentFormat.OpenXml.Spreadsheet.Sheets' does not contain a definition for 'Name' and no extension method 'Name' accepting a first argument of type 'DocumentFormat.OpenXml.Spreadsheet.Sheets' could be found (are you missing a using directive or an assembly reference?)	C:\App13\App13\Program.cs	45	83	App13



    due to this line
                IEnumerable<DocumentFormat.OpenXml.Spreadsheet.Sheets> sheets =
                   document.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().
                   Elements<DocumentFormat.OpenXml.Spreadsheet.Sheets>().Where(s => s.Name == sheetName);



    Many Thanks & Best Regards, Hua Min

    Thursday, January 12, 2017 9:46 AM
  • Hi HuaMin  Chen,

    did you make any changes in that line?

    Regards

    Deepak


    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, January 12, 2017 10:08 AM
    Moderator
  • Yes, due to some other problem, I use this "DocumentFormat.OpenXml.Spreadsheet.Sheets" instead.

    Many Thanks & Best Regards, Hua Min

    Thursday, January 12, 2017 10:11 AM
  • Hi HuaMin  Chen,

    what do you want to achieve by doing these changes?

    are you getting only these 2 errors? or more then 2 errors.

    can you tell me why you pass

    DocumentFormat.OpenXml.Spreadsheet.Sheets

    in line below.

    IEnumerable<DocumentFormat.OpenXml.Spreadsheet.Sheets> sheets =

    instead of "Sheet"?

    Regards

    Deepak


    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, January 13, 2017 6:44 AM
    Moderator
  • Only these 2 errors. There is compiled error if I use Sheet instead of DocumentFormat.OpenXml.Spreadsheet.Sheets.

    Many Thanks & Best Regards, Hua Min

    Friday, January 13, 2017 6:49 AM
  • Hi HuaMin  Chen,

    I find some mistakes in your edited code.

    below is your original code edited by you.

     IEnumerable<DocumentFormat.OpenXml.Spreadsheet.Sheets> sheets =
                   document.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().
                   Elements<DocumentFormat.OpenXml.Spreadsheet.Sheets>().Where(s => s.Name == sheetName);

    below is the corrected code by me.

    you pass "Sheets" everywhere so you got an error.

    Regards

    Deepak


    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 Monday, January 16, 2017 9:33 AM
    Monday, January 16, 2017 6:37 AM
    Moderator