SharedStringTable Class

  • Article

Shared String Table. It is the root element of SharedStringTablePart.When the object is serialized out as xml, its qualified name is x:sst.

Inheritance Hierarchy

System.Object
  DocumentFormat.OpenXml.OpenXmlElement
    DocumentFormat.OpenXml.OpenXmlCompositeElement
      DocumentFormat.OpenXml.OpenXmlPartRootElement
        DocumentFormat.OpenXml.Spreadsheet.SharedStringTable

Namespace:  DocumentFormat.OpenXml.Spreadsheet
Assembly:  DocumentFormat.OpenXml (in DocumentFormat.OpenXml.dll)

Syntax

'Declaration
<ChildElementInfoAttribute(GetType(SharedStringItem))> _
<ChildElementInfoAttribute(GetType(ExtensionList))> _
Public Class SharedStringTable _
    Inherits OpenXmlPartRootElement
'Usage
Dim instance As SharedStringTable

[ChildElementInfoAttribute(typeof(SharedStringItem))]
[ChildElementInfoAttribute(typeof(ExtensionList))]
public class SharedStringTable : OpenXmlPartRootElement

Remarks

The following table lists the possible child types:

  • SharedStringItem <x:si>

  • ExtensionList <x:extLst>

[ISO/IEC 29500-1 1st Edition]

18.4.9 sst (Shared String Table)

This element is the root of the Shared String Table, which serves as a collection of individual String Items (si).

Parent Elements

Root element of SpreadsheetML Shared String Table part

Child Elements

Subclause

extLst (Future Feature Data Storage Area)

§18.2.10

si (String Item)

§18.4.8

Attributes

Description

count (String Count)

An integer representing the total count of strings in the workbook. This count does not include any numbers, it counts only the total of text strings in the workbook.

This attribute is optional unless uniqueCount is used, in which case it is required.

The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype.

uniqueCount (Unique String Count)

An integer representing the total count of unique strings in the Shared String Table. A string is unique even if it is a copy of another string, but has different formatting applied at the character level.

[Example:

World, World, and World.

The count would be 3, and the uniqueCount would be 2. Only one entry for "World" would show in the table because it is the same string, just with different formatting applied at the cell level (i.e., applied to the entire string in the cell). The "World" string would get a separate unique entry in the shared string table because it has different formatting applied to specific characters.

end example]

This attribute is optional unless count is used, in which case it is required.

The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype.

[Note: The W3C XML Schema definition of this element’s content model (CT_Sst) is located in §A.2. end note]

© ISO/IEC29500: 2008.

Examples

The following code example creates a new spreadsheet document and inserts a hyperlink in the cell "A2."

using System;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace SharedStringTableEx
{
    class Program
    {
        static void Main(string[] args)
        {
            string fileName = @"C:\Users\Public\Documents\SharedStringTableEx.xlsx";

            // Create a spreadsheet document.
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
                Create(fileName, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets to the Workbook.
            Sheets sheets =
                spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

            // Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet()
            { Id = spreadsheetDocument.WorkbookPart.
                GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "mySheet"
            };
            sheets.Append(sheet);

            Worksheet worksheet = new Worksheet();
            SheetData sheetData = new SheetData();

            Row row =
                new Row()
                { RowIndex = 2U,
                  Spans = new ListValue<StringValue>() { InnerText = "2:2" }
                };
            Cell cell =
                new Cell()
                { CellReference = "A2",
                  DataType = CellValues.String,
                  CellValue = new CellValue("Microsoft")
                };
            row.Append(cell);
            sheetData.Append(row);
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;

            workbookpart.Workbook.Save();

            // Close the document.
            spreadsheetDocument.Close();
            InsertHyperLink(fileName, "https://www.microsoft.com", "A2");

            Console.WriteLine("The hyperlink has been inserted.\nPress a key.");
            Console.ReadKey();
        }

        static public void InsertHyperLink(string fileName, string uri, string cellAddress)
        {
            // Open the document for editing.
            using (SpreadsheetDocument spreadsheetDocument =
                SpreadsheetDocument.Open(fileName, true))
            {
                // Get the first sheet in the workbook.
                Sheet sheet1 = spreadsheetDocument.WorkbookPart.Workbook.
                    Descendants<Sheet>().First();
                WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.
                    WorkbookPart.GetPartById(sheet1.Id);

                // Create a hyperlink relationship.
                HyperlinkRelationship hyperlinkRelationship =
                    worksheetPart.AddHyperlinkRelationship(new System.Uri
                    (uri, System.UriKind.Absolute), true);

                // Add a new shared string table part.
                SharedStringTablePart sharedStringTablePart1;
                if (spreadsheetDocument.WorkbookPart.GetPartsCountOfType
                    <SharedStringTablePart>() > 0)
                {
                    sharedStringTablePart1 =
                        spreadsheetDocument.WorkbookPart.GetPartsOfType
                        <SharedStringTablePart>().First();
                }
                else
                {
                    sharedStringTablePart1 =
                        spreadsheetDocument.WorkbookPart.AddNewPart
                        <SharedStringTablePart>();
                }

                // Add a new shared string table.
                SharedStringTable sharedStringTable1 =
                    new SharedStringTable() { Count = (UInt32Value)1U, UniqueCount =
                        (UInt32Value)1U };
                sharedStringTablePart1.SharedStringTable = sharedStringTable1;

                // Create the hyperlink object.
                Hyperlinks hyperlinks1 = new Hyperlinks();
                Hyperlink hyperlink1 =
                    new Hyperlink() { Reference = cellAddress, Id = 
                        hyperlinkRelationship.Id };

                // Append the hyperlink to the collection. 
                hyperlinks1.Append(hyperlink1);

                // Append the hyperlink to the worksheet.
                worksheetPart.Worksheet.Append(hyperlinks1);
            }
        }
    }
}
// Output:
// The hyperlink has been inserted.
// Press a key.

Imports System.Linq
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Module Module1
    Sub Main(ByVal args As String())
        Dim fileName As String = "C:\Users\Public\Documents\SharedStringTableEx.xlsx"

        ' Create a spreadsheet document.
        Dim spreadsheetDocument As SpreadsheetDocument = spreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)

        ' Add a WorkbookPart to the document.
        Dim workbookpart As WorkbookPart = spreadsheetDocument.AddWorkbookPart()
        workbookpart.Workbook = New Workbook()

        ' Add a WorksheetPart to the WorkbookPart.
        Dim worksheetPart As WorksheetPart = workbookpart.AddNewPart(Of WorksheetPart)()
        worksheetPart.Worksheet = New Worksheet(New SheetData())

        ' Add Sheets to the Workbook.
        Dim sheets As Sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(Of Sheets)(New Sheets())

        ' Append a new worksheet and associate it with the workbook.
        Dim sheet As New Sheet() With { _
         .Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), _
         .SheetId = 1, _
         .Name = "mySheet" _
        }
        sheets.Append(sheet)

        Dim worksheet As New Worksheet()
        Dim sheetData As New SheetData()

        Dim row As New Row() With { _
         .RowIndex = 2UI, _
         .Spans = New ListValue(Of StringValue)() With { _
          .InnerText = "2:2" _
         } _
        }
        Dim cell As New Cell() With { _
         .CellReference = "A2", _
         .DataType = CellValues.[String], _
         .CellValue = New CellValue("Microsoft") _
        }
        row.Append(cell)
        sheetData.Append(row)
        worksheet.Append(sheetData)
        worksheetPart.Worksheet = worksheet

        workbookpart.Workbook.Save()

        ' Close the document.
        spreadsheetDocument.Close()
        InsertHyperLink(fileName, "https://www.microsoft.com", "A2")

        Console.WriteLine("The hyperlink has been inserted." & vbLf & "Press a key.")
        Console.ReadKey()
    End Sub

    Public Sub InsertHyperLink(ByVal fileName As String, ByVal uri As String, ByVal cellAddress As String)
        ' Open the document for editing.
        Using spreadsheetDocument As SpreadsheetDocument = spreadsheetDocument.Open(fileName, True)
            ' Get the first sheet in the workbook.
            Dim sheet1 As Sheet = spreadsheetDocument.WorkbookPart.Workbook.Descendants(Of Sheet)().First()
            Dim worksheetPart As WorksheetPart = DirectCast(spreadsheetDocument.WorkbookPart.GetPartById(sheet1.Id), WorksheetPart)

            ' Create a hyperlink relationship.
            Dim hyperlinkRelationship As HyperlinkRelationship = worksheetPart.AddHyperlinkRelationship(New System.Uri(uri, System.UriKind.Absolute), True)

            ' Add a new shared string table part.
            Dim sharedStringTablePart1 As SharedStringTablePart
            If spreadsheetDocument.WorkbookPart.GetPartsCountOfType(Of SharedStringTablePart)() > 0 Then
                sharedStringTablePart1 = spreadsheetDocument.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().First()
            Else
                sharedStringTablePart1 = spreadsheetDocument.WorkbookPart.AddNewPart(Of SharedStringTablePart)()
            End If

            ' Add a new shared string table.
            Dim sharedStringTable1 As New SharedStringTable() With { _
              .Count = CType(1UI, UInt32Value), _
              .UniqueCount = CType(1UI, UInt32Value) _
            }
            sharedStringTablePart1.SharedStringTable = sharedStringTable1

            ' Create the hyperlink object.
            Dim hyperlinks1 As New Hyperlinks()
            Dim hyperlink1 As New Hyperlink() With { _
             .Reference = cellAddress, _
             .Id = hyperlinkRelationship.Id _
            }

            ' Append the hyperlink to the collection. 
            hyperlinks1.Append(hyperlink1)

            ' Append the hyperlink to the worksheet.
            worksheetPart.Worksheet.Append(hyperlinks1)
        End Using
    End Sub
End Module
' Output:
' The hyperlink has been inserted.
' Press a key.

Thread Safety

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

See Also

Reference

SharedStringTable Members

DocumentFormat.OpenXml.Spreadsheet Namespace