none
Update cell value RRS feed

  • Question

  • Hi,
    I want to put text into one cell, while I know Cell reference is like B2. How to adjust this

     public static void InsertText(string docName, string text)
    {
        // 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("A", 1, worksheetPart);
    
            // Set the value of cell A1.
            cell.CellValue = new CellValue(index.ToString());
            cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
    
            // Save the new worksheet.
            worksheetPart.Worksheet.Save();
        }
    }



    to update specific cell to given value?

    Many Thanks & Best Regards, Hua Min


    • Edited by Jackson_1990 Thursday, November 10, 2016 4:09 AM
    Saturday, October 8, 2016 4:55 AM

Answers

  • Hi Huamin chen,

    This is the code that I have modified and working on my side. it add the value to sheet with out any error to your file.

    now you can use this and again modify it based on your requirement.

            private void button1_Click(object sender, EventArgs e)
            {
                InsertText(@"C:\Users\v-padee\Desktop\Copy of 115_Copy2.xlsm", "demovalue","Sheet1","A",2);
    
            }
            public static void InsertText(string docName, string text, string SheetName0, 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 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 Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();
    
                Sheets sheets = workbookPart.Workbook.GetFirstChild<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;
            }
    
            // 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)
            {
                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;
                }
            }
    
    
    
    

    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 Thursday, November 10, 2016 4:09 AM
    Wednesday, November 9, 2016 9:45 AM
    Moderator

All replies

  • Hi Huamin Chen,

    you can create a Console Application to run the above mentioned code.

    I adjust the code and its working and I mentioned it below.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Linq;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    namespace ConsoleApplication7
    {
        class Program
        {
            static void Main(string[] args)
            {
                InsertText(@"C:\Users\v-padee\Desktop\demo1.xlsx", "Demo Text");
    
    
            }
            // Given a document name and text, 
            // inserts a new work sheet and writes the text to cell "A1" of the new worksheet.
    
            public static void InsertText(string docName, string text)
            {
                // 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("B", 2, worksheetPart);
    
                    // Set the value of cell A1.
                    cell.CellValue = new CellValue(index.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
    
                    // Save the new worksheet.
                    worksheetPart.Worksheet.Save();
                }
            }
    
            // 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;
            }
    
            // 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 Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();
    
                Sheets sheets = workbookPart.Workbook.GetFirstChild<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;
            }
    
            // 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)
            {
                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;
                }
            }
    
    
    
    
        }
    }
    

    after you add the code to console application you will see some errors in the code.

    so you need to add the reference in your project.

    (1) DocumentFormat.openXml

    (2) Windows.Base

    all error will be gone.

    then you just need to change the path of excel file in Main() Function and run the code.

    it will add the Demo text to B2 Cell in new sheet. like below.

    if you just want to add a test in cell then you can use VBA or VSTO. using that you can do this with just one line of code. no need to write this much long code.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 10, 2016 6:04 AM
    Moderator
  • Hi Huamin Chen,

    The code is set to run with in Console Application.

    I already informed you to create a console application in my last post.

    Again I repeat the steps to run the above mentioned code.

    (1) Create a C# Console Application.

    (2) Click on "Add Reference" then add reference to "DocumentFormat.openXml" and "Windows.Base".

    (3) Reference the assemblies mentioned below.

    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    (4)  Add the line of code mentioned below in "Void Main()" section.

     InsertText(@"C:\Users\v-padee\Desktop\demo1.xlsx", "Demo Text");
    

    (5) Add the code mentioned below after the "Void Main()" section.

    public static void InsertText(string docName, string text)
            {
                // 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("B", 2, worksheetPart);
    
                    // Set the value of cell A1.
                    cell.CellValue = new CellValue(index.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
    
                    // Save the new worksheet.
                    worksheetPart.Worksheet.Save();
                }
            }
    
            // 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;
            }
    
            // 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 Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();
    
                Sheets sheets = workbookPart.Workbook.GetFirstChild<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;
            }
    
            // 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)
            {
                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;
                }
            }

    (6) Run the code. then open the Excel File to view the output.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 13, 2016 4:21 AM
    Moderator
  • Hi,
    Sorry to that I did 'reuse' the similar codes on my project and have got this
    System.NullReferenceException was unhandled
      HResult=-2147467261
      Message=Object reference not set to an instance of an object.
      Source=WindowsFormsApplication2
      StackTrace:
           at WindowsFormsApplication2.Form1.CreateSpreadsheetWorkbook(String filepath, String SheetName0) in c:\App\WindowsFormsApplication9_4_0_1\WindowsFormsApplication2\Form1.cs:line 402
           at WindowsFormsApplication2.Form1.menuItem5_Click(Object sender, EventArgs e) in c:\App\WindowsFormsApplication9_4_0_1\WindowsFormsApplication2\Form1.cs:line 110
           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:\App\WindowsFormsApplication9_4_0_1\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 last 2nd line below
            public static Boolean CreateSpreadsheetWorkbook(string filepath, 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());
    


    How to resolve it?

    Many Thanks & Best Regards, Hua Min

    Monday, October 17, 2016 2:05 AM
  • Any help?

    Many Thanks & Best Regards, Hua Min

    Tuesday, October 18, 2016 4:45 AM
  • Hi huamin Chen,

    The workbook part you want to assign to worksheetpart is null.

    in the starting of the code you set the value Null to it.

    after that you did not assign a new value to it and directly pass to workbookpart.Addnewpart().

    you need to assign the value for it to solve the issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 18, 2016 4:58 AM
    Moderator
  • Hi Huamin Chen,

    can you tell me from where you get this code?

    because it just a part of code and not a full code.

    from the link that you had posted in original post I matched the code wit that.

    I find that it contains only Insert text method from that code.

    so I am not able to run this code on my side.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 18, 2016 6:32 AM
    Moderator
  • Hi Huamin chen,

    it is better if you give me the link from where you get this code.

    so that I can try to understand it properly and try to run it.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 18, 2016 7:01 AM
    Moderator
  • The codes are by me and I do not know why it does not copy all values from original Excel file into another Excel file, as expected.


    Many Thanks & Best Regards, Hua Min



    Tuesday, October 18, 2016 7:18 AM
  • Hi Huamin chen,

    is this your full code?

    or you have any other code too ?

    if yes please post the whole code.

    I try to see your excel file which is very large.

    I recommend you to test the code on small file with few data.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 18, 2016 7:58 AM
    Moderator
  • Hi Deepak,
    Yes, those are the codes I have. Here is the file for you to try with.


    Many Thanks & Best Regards, Hua Min

    Tuesday, October 18, 2016 8:23 AM
  • Hi huamin Chen,

    I will test it and reply you soon.

    Thanks for your understanding.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 20, 2016 8:14 AM
    Moderator
  • Hi,

    Any help?


    Many Thanks & Best Regards, Hua Min

    Tuesday, October 25, 2016 1:42 AM
  • Hi Huamin Chen,

    I try to run the code posted by you last time.

    when I call insert text method then I get error that file is used by another process.

    but I check that my file is not used by another process.

    also I try to create a new file then call the method and get same error.

    I also check in task manager if some process uses that file but no any other process uses that file.

    so I will try to look further to solve this error.

    if I get succeed then I will let you know about the 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.

    Tuesday, October 25, 2016 2:09 AM
    Moderator
  • Hi Deepak,

    Appreciated to you! Did you try with the Excel file I've provided to you?


    Many Thanks & Best Regards, Hua Min


    Tuesday, October 25, 2016 2:44 AM
  • Hi Deepak,

    Have you got any reason to this?


    Many Thanks & Best Regards, Hua Min

    Wednesday, October 26, 2016 10:07 AM
  • Hi Huamin chen,

    No, I will try again and let you inform.

    Thanks for your understanding

    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, October 27, 2016 2:52 AM
    Moderator
  • Hi Deepak,

    Is it hard to resolve this problem but this issue had been pending/outstanding for a very long time?


    Many Thanks & Best Regards, Hua Min




    Thursday, October 27, 2016 4:43 AM
  • Hi,

    Any other advice?

    Deepak,

    If you cannot resolve this, could you please pass this to Celeste Li, David JunFeng, to resolve?


    Many Thanks & Best Regards, Hua Min

    Monday, October 31, 2016 10:12 AM
  • Hi Huamin Chen,

    I did not reply you because of weekend holiday.

    I find that with the use of file stream we can solve this issue.

    also I find that you pass the wrong object in the insert text method.

    also I find that you open file multiple times.

    so please try to remove the file open code from your method and try to implement the code mentioned below.

     using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
                        {
                            WorkbookPart workbookPart = doc.WorkbookPart;
                           // SharedStringTablePart sstpart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
                           // SharedStringTable sst = sstpart.SharedStringTable;
    
                            WorksheetPart worksheetpart = workbookPart.WorksheetParts.First();
                            Worksheet sheet = worksheetpart.Worksheet;
    

    make sure you reference the correct object.

    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, October 31, 2016 11:36 PM
    Moderator
  • Hi,

    Thanks.

    I expect to pass doc name, sheet name and cell reference, to InsertText, to apply the change. If this is wrong, how to adjust it?


    Many Thanks & Best Regards, Hua Min

    Tuesday, November 1, 2016 1:46 AM
  • Any advice?

    Many Thanks & Best Regards, Hua Min

    Wednesday, November 2, 2016 3:11 AM
  • Hi Huamin chen,

    we use this code just to open the file.

    because we are not able to open the file.

    with the above mentioned code we are able to open the file without error.

    so rest of the code will be same as it is that you had write.

    just to take care about objects.

    and remove the duplicate file opening code because file is already open. no need to open it again.

    the reason is your code is little long and taking much time to test and making correct.

    so I recommend you to implement the suggestion and if any issue happens again then we will try to look it further.

    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, November 2, 2016 10:08 AM
    Moderator
  • Hi,
    As you said InsertText event is having some problem, how to adjust it?

    BTW, Celeste Li and David JunFung did help me a lot to the similar problem, and where are they now?


    Many Thanks & Best Regards, Hua Min


    Thursday, November 3, 2016 1:53 AM
  • Hi Huamin Chen,

    did you implement the suggestion?

    if not then first you can try to implement it and let us know about the results.

    so that we can move further.

    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, November 3, 2016 4:48 AM
    Moderator
  • Hi,
    >> because we are not able to open the file.
    1. Where can't you open the file?
    2. To my codes, I have no problem to this line.

    using (FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
    ...



    Many Thanks & Best Regards, Hua Min

    Thursday, November 3, 2016 6:24 AM
  • Hi Huamin Chen,

    then on which line you got error?

    when I try on my side I got the problem on the line which opens the file.

    it looks like you get the code from the link mentioned below.

    How to: Insert text into a cell in a spreadsheet document (Open XML SDK)

    you had mentioned that you also want to pass sheetname and cell address.

    just take 2 more parameters in the insert text method.

    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, November 4, 2016 6:38 AM
    Moderator
  • Hi,
    I run my codes (shown in above) and expect that it would write ALL details of original Excel file, into another Excel file but it does not. Where is the problem? This is why I have asked 'How to adjust InsertText event' to make it work as expected.

    Many Thanks & Best Regards, Hua Min


    Friday, November 4, 2016 6:56 AM
  • Hi Deepak,
    Can you please help to my current question raised several days before, which was also encountered originally by this thread?

    Many Thanks & Best Regards, Hua Min


    Tuesday, November 8, 2016 2:11 AM
  • Hi HuaMin  Chen,

    I will again try to test your above mentioned Insert Text ( ) method with your excel file and let you know about the result.

    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, November 9, 2016 12:12 AM
    Moderator
  • Hi,

    I need your suggestion to adjust InsertText to ensure it is working as expected.


    Many Thanks & Best Regards, Hua Min

    Wednesday, November 9, 2016 1:46 AM
  • Hi Huamin chen,

    This is the code that I have modified and working on my side. it add the value to sheet with out any error to your file.

    now you can use this and again modify it based on your requirement.

            private void button1_Click(object sender, EventArgs e)
            {
                InsertText(@"C:\Users\v-padee\Desktop\Copy of 115_Copy2.xlsm", "demovalue","Sheet1","A",2);
    
            }
            public static void InsertText(string docName, string text, string SheetName0, 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 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 Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();
    
                Sheets sheets = workbookPart.Workbook.GetFirstChild<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;
            }
    
            // 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)
            {
                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;
                }
            }
    
    
    
    

    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 Thursday, November 10, 2016 4:09 AM
    Wednesday, November 9, 2016 9:45 AM
    Moderator