none
Replacing text and values in Excel spreadsheet RRS feed

  • Question

  • Hi

    We have created a text replacement webb service för Word documents and Excel spreadsheets.
    Regarding text it works fine.

    Now, we want to increase the capability to handle numeric values in Excel spreadsheets.

    Since the text string we handle today are all located in the SharedStringTable we can not use this approach to replace numeric values, because they end up as text.

    Also, we pass tag and value to the function and the tag has format [tagName]. I suspect that this approach makes the cell a "text cell" already from start.

    This is the code used to replace the text:

                                    Using calc As SpreadsheetDocument = SpreadsheetDocument.Open(tDocFilename, True)
                                        Dim bookPart As DocumentFormat.OpenXml.Packaging.WorkbookPart = calc.WorkbookPart
                                        Dim stringList As SharedStringTablePart = bookPart.SharedStringTablePart
                                        For Each strValue In stringList.SharedStringTable
                                            If strValue.InnerText = tTagg Then
                                                Dim newText As Spreadsheet.Text = strValue.Elements(Of Spreadsheet.Text)().FirstOrDefault
                                                newText.Text = tText
                                            End If
                                        Next
                                    End Using
    Does anybody has an idea how this can be modified to also handle numeric values that is regarded as numeric in Excel once the spreadsheet is opened.

    Thanks in advance


    Best Regards Peter Karlström Midrange AB, Sweden


    Monday, August 5, 2019 9:16 AM