none
Issue in Insert value in sharedstringtable RRS feed

  • Question

  • Hi,

       I try to insert following string in one of the cell in my excel sheet through open xml, i am facing the issue in display the string value

    input string value = "    Hello World"

    Expected Result ="    Hello World"

    Actual Result  = "Hello World"

    Here issue is left hand side of the string is trim automatically, how i can solve the problem, please help me.

    This is the code i am using to insert the string value in sharedstringtable, i guess the problem is in inserting the value in sharedstringtable.

    int index = InsertSharedStringItem(wbPart,"    Hello World")

    private int InsertSharedStringItem(WorkbookPart wbPart, string value)
            {
                int index = 0;
                bool found = false;
                var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault() ??
                                      wbPart.AddNewPart<SharedStringTablePart>();
    
                // If the shared string table is missing, something's wrong.
                // Just return the index that you found in the cell.
                // Otherwise, look up the correct text in the table.
    
                var stringTable = stringTablePart.SharedStringTable ?? new SharedStringTable();
    
                // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
                foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
                {
                    if (item.InnerText == value)
                    {
                        found = true;
                        break;
                    }
                    index += 1;
                }
    
                if (!found)
                {
                    stringTable.AppendChild(new SharedStringItem(new Text(value)));
                    stringTable.Save();
                }
    
                return index;
            }
    

    Monday, November 3, 2014 1:17 PM

Answers

  • Hello udayakumar,

    Yes the SharedString will trim the string by default when inserted into the worksheet. To preserve the spaces ahead of the string, you could use InlineString instead, check this sample code:

    Text text1 = new Text() { Space = SpaceProcessingModeValues.Preserve };
                    text1.Text = "  Text with space";
                    cell.InlineString = new InlineString(text1);
                    cell.DataType = new EnumValue<CellValues>(CellValues.InlineString);


    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, November 4, 2014 8:44 AM
    Moderator

All replies

  • Hello udayakumar,

    Yes the SharedString will trim the string by default when inserted into the worksheet. To preserve the spaces ahead of the string, you could use InlineString instead, check this sample code:

    Text text1 = new Text() { Space = SpaceProcessingModeValues.Preserve };
                    text1.Text = "  Text with space";
                    cell.InlineString = new InlineString(text1);
                    cell.DataType = new EnumValue<CellValues>(CellValues.InlineString);


    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, November 4, 2014 8:44 AM
    Moderator
  • Thanks Caillen,

         It's working fine.

    Tuesday, November 4, 2014 9:56 AM