Obter um título de coluna em um documento de planilha

Este tópico mostra como usar as classes no SDK Do Open XML para Office para recuperar um título de coluna em um documento de planilha de forma programática.

Estrutura básica de um documento de planilha

A estrutura básica de um documento SpreadsheetML consiste nos elementos Sheets e Sheet, que referenciam as planilhas na pasta de trabalho. Um arquivo XML separado é criado para cada planilha. Por exemplo, o SpreadsheetML de uma Pasta de trabalho que tem duas planilhas chamadas MySheet1 e MySheet2 está localizado no arquivo Workbook.xml e é mostrado no exemplo de código a seguir.

    <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
    <workbook xmlns=https://schemas.openxmlformats.org/spreadsheetml/2006/main xmlns:r="https://schemas.openxmlformats.org/officeDocument/2006/relationships">
        <sheets>
            <sheet name="MySheet1" sheetId="1" r:id="rId1" /> 
            <sheet name="MySheet2" sheetId="2" r:id="rId2" /> 
        </sheets>
    </workbook>

Os arquivos XML da planilha contêm um ou mais elementos de nível de bloco, como sheetData , representa a tabela de células e contém um ou mais elementos row . Uma linha contém um ou mais elementos Cell. Cada célula contém um elemento CellValue que representa o valor da célula. Por exemplo, a PlanilhaML da primeira planilha em uma pasta de trabalho, que tem apenas o valor 100 na célula A1, está localizada no arquivo Sheet1.xml e é mostrada no exemplo de código a seguir.

    <?xml version="1.0" encoding="UTF-8" ?> 
    <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <sheetData>
            <row r="1">
                <c r="A1">
                    <v>100</v> 
                </c>
            </row>
        </sheetData>
    </worksheet>

Usando o SDK Do Open XML, você pode criar a estrutura de documentos e o conteúdo que usa classes fortemente tipdas que correspondem a elementos SpreadsheetML . Você pode encontrar essas classes no namespace DocumentFormat.OpenXML.Spreadsheet. A tabela a seguir lista os nomes das classes que correspondem aos elementos workbook, sheets, sheet, worksheet e sheetData.

Elemento SpreadsheetML	Classe SDK Open XML	Descrição
pasta de trabalho	DocumentFormat.OpenXML.Spreadsheet.Workbook	O elemento raiz para a parte do documento principal.
sheets	DocumentFormat.OpenXML.Spreadsheet.Sheets	O contêiner para as estruturas de nível de bloco, como sheet, fileVersion e outras indicadas na especificação ISO/IEC 29500.
sheet	DocumentFormat.OpenXml.Spreadsheet.Sheet	Uma planilha que aponta para um arquivo de definição de planilha.
planilha	DocumentFormat.OpenXML.Spreadsheet. Planilha	Um arquivo de definição de planilha que contém os dados de planilha.
sheetData	DocumentFormat.OpenXML.Spreadsheet.SheetData	A tabela de células, agrupadas por linhas.
row	DocumentFormat.OpenXml.Spreadsheet.Row	Uma linha na tabela de células.
c	DocumentFormat.OpenXml.Spreadsheet.Cell	Uma célula em uma linha.
v	DocumentFormat.OpenXml.Spreadsheet.CellValue	O valor de uma célula.

Como funciona o código de exemplo

O código neste how-to consiste em três métodos (funções no Visual Basic): GetColumnHeading, GetColumnName e GetRowIndex. Os dois últimos métodos são chamados de dentro do método GetColumnHeading .

O método GetColumnName usa o nome da célula como um parâmetro. Ele analisa o nome da célula para obter o nome da coluna criando uma expressão regular para corresponder à parte do nome da coluna do nome da célula. Para obter mais informações sobre expressões regulares, consulte Elementos de Linguagem de Expressão Regular.

C#

// Create a regular expression to match the column name portion of the cell name.
Regex regex = new Regex("[A-Za-z]+");
Match match = regex.Match(cellName);

return match.Value;

Visual Basic

' Create a regular expression to match the column name portion of the cell name.
Dim regex As Regex = New Regex("[A-Za-z]+")
Dim match As Match = regex.Match(cellName)

Return match.Value

O método GetRowIndex usa o nome da célula como um parâmetro. Ele analisa o nome da célula para obter o índice de linha criando uma expressão regular para corresponder à parte do índice de linha do nome da célula.

C#

// Create a regular expression to match the row index portion the cell name.
Regex regex = new Regex(@"\d+");
Match match = regex.Match(cellName);

return uint.Parse(match.Value);

Visual Basic

' Create a regular expression to match the row index portion the cell name.
Dim regex As Regex = New Regex("\d+")
Dim match As Match = regex.Match(cellName)

Return UInteger.Parse(match.Value)

O método GetColumnHeading usa três parâmetros, o caminho completo para o arquivo de planilha de origem, o nome da planilha que contém a coluna especificada e o nome de uma célula na coluna para a qual obter o título.

O código obtém o nome da coluna da célula especificada chamando o método GetColumnName . O código também obtém as células na coluna e as ordena por linha usando o método GetRowIndex .

C#

// Get the column name for the specified cell.
string columnName = GetColumnName(cellName);

// Get the cells in the specified column and order them by row.
IEnumerable<Cell> cells = worksheetPart.Worksheet.Descendants<Cell>().Where(c => string.Compare(GetColumnName(c.CellReference?.Value), columnName, true) == 0)
    .OrderBy(r => GetRowIndex(r.CellReference) ?? 0);

Visual Basic

' Get the column name for the specified cell.
Dim columnName As String = GetColumnName(cellName)

' Get the cells in the specified column and order them by row.
Dim cells As IEnumerable(Of Cell) = worksheetPart.Worksheet.Descendants(Of Cell)().Where(Function(c) _
    String.Compare(GetColumnName(c.CellReference.Value), columnName, True) = 0).OrderBy(Function(r) GetRowIndex(r.CellReference))

Se a coluna especificada existir, ela receberá a primeira célula na coluna usando o IEnumerable(T). Primeiro método. A primeira célula contém o título. Caso contrário, a coluna especificada não existe e o método retorna null / Nothing

C#

if (cells.Count() == 0)
{
    // The specified column does not exist.
    return null;
}

// Get the first cell in the column.
Cell headCell = cells.First();

Visual Basic

If (cells.Count() = 0) Then
    ' The specified column does not exist.
    Return Nothing
End If

' Get the first cell in the column.
Dim headCell As Cell = cells.First()

Se o conteúdo da célula for armazenado no objeto SharedStringTablePart, ele obterá os itens de cadeia de caracteres compartilhados e retornará o conteúdo do título da coluna usando o método M:System.Int32.Parse(System.String). Se o conteúdo da célula não estiver no objeto SharedStringTable , ele retornará o conteúdo da célula.

C#

// If the content of the first cell is stored as a shared string, get the text of the first cell
// from the SharedStringTablePart and return it. Otherwise, return the string value of the cell.
if (headCell.DataType is not null && headCell.DataType.Value == CellValues.SharedString && int.TryParse(headCell.CellValue?.Text, out int index))
{
    SharedStringTablePart shareStringPart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
    SharedStringItem[] items = shareStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray();

    return items[index].InnerText;
}
else
{
    return headCell.CellValue?.Text;
}

Visual Basic

' If the content of the first cell is stored as a shared string, get the text of the first cell
' from the SharedStringTablePart and return it. Otherwise, return the string value of the cell.
If (((headCell.DataType) IsNot Nothing) AndAlso (headCell.DataType.Value = CellValues.SharedString)) Then
    Dim shareStringPart As SharedStringTablePart = document.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().First()
    Dim items() As SharedStringItem = shareStringPart.SharedStringTable.Elements(Of SharedStringItem)().ToArray()
    Return items(Integer.Parse(headCell.CellValue.Text)).InnerText
Else
    Return headCell.CellValue.Text
End If

Código de exemplo

A seguir está o código de exemplo completo em C# e em Visual Basic.

C#

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;


// Given a document name, a worksheet name, and a cell name, gets the column of the cell and returns
// the content of the first cell in that column.
static string? GetColumnHeading(string docName, string worksheetName, string cellName)
{
    // Open the document as read-only.
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, false))
    {
        IEnumerable<Sheet>? sheets = document.WorkbookPart?.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);

        if (sheets is null || sheets.Count() == 0)
        {
            // The specified worksheet does not exist.
            return null;
        }

        string? id = sheets.First().Id;

        if (id is null)
        {
            // The worksheet does not have an ID.
            return null;
        }

        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart!.GetPartById(id);

        // Get the column name for the specified cell.
        string columnName = GetColumnName(cellName);

        // Get the cells in the specified column and order them by row.
        IEnumerable<Cell> cells = worksheetPart.Worksheet.Descendants<Cell>().Where(c => string.Compare(GetColumnName(c.CellReference?.Value), columnName, true) == 0)
            .OrderBy(r => GetRowIndex(r.CellReference) ?? 0);

        if (cells.Count() == 0)
        {
            // The specified column does not exist.
            return null;
        }

        // Get the first cell in the column.
        Cell headCell = cells.First();

        // If the content of the first cell is stored as a shared string, get the text of the first cell
        // from the SharedStringTablePart and return it. Otherwise, return the string value of the cell.
        if (headCell.DataType is not null && headCell.DataType.Value == CellValues.SharedString && int.TryParse(headCell.CellValue?.Text, out int index))
        {
            SharedStringTablePart shareStringPart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
            SharedStringItem[] items = shareStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray();

            return items[index].InnerText;
        }
        else
        {
            return headCell.CellValue?.Text;
        }
    }
}
// Given a cell name, parses the specified cell to get the column name.
static string GetColumnName(string? cellName)
{
    if (cellName is null)
    {
        return string.Empty;
    }

    // Create a regular expression to match the column name portion of the cell name.
    Regex regex = new Regex("[A-Za-z]+");
    Match match = regex.Match(cellName);

    return match.Value;
}

// Given a cell name, parses the specified cell to get the row index.
static uint? GetRowIndex(string? cellName)
{
    if (cellName is null)
    {
        return null;
    }

    // Create a regular expression to match the row index portion the cell name.
    Regex regex = new Regex(@"\d+");
    Match match = regex.Match(cellName);

    return uint.Parse(match.Value);
}

Visual Basic

Imports System.Text.RegularExpressions
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet


Module MyModule

    Sub Main(args As String())
        Console.WriteLine("The heading: {0}", GetColumnHeading(args(0), args(1), args(2)))
    End Sub

    ' Given a document name, a worksheet name, and a cell name, gets the column of the cell and returns
    ' the content of the first cell in that column.
    Public Function GetColumnHeading(ByVal docName As String, ByVal worksheetName As String, ByVal cellName As String) As String
        ' Open the document as read-only.
        Dim document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, False)

        Using (document)
            Dim sheets As IEnumerable(Of Sheet) = document.WorkbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = worksheetName)
            If (sheets.Count() = 0) Then
                ' The specified worksheet does not exist.
                Return Nothing
            End If

            Dim worksheetPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(sheets.First.Id), WorksheetPart)

            ' Get the column name for the specified cell.
            Dim columnName As String = GetColumnName(cellName)

            ' Get the cells in the specified column and order them by row.
            Dim cells As IEnumerable(Of Cell) = worksheetPart.Worksheet.Descendants(Of Cell)().Where(Function(c) _
                String.Compare(GetColumnName(c.CellReference.Value), columnName, True) = 0).OrderBy(Function(r) GetRowIndex(r.CellReference))

            If (cells.Count() = 0) Then
                ' The specified column does not exist.
                Return Nothing
            End If

            ' Get the first cell in the column.
            Dim headCell As Cell = cells.First()

            ' If the content of the first cell is stored as a shared string, get the text of the first cell
            ' from the SharedStringTablePart and return it. Otherwise, return the string value of the cell.
            If (((headCell.DataType) IsNot Nothing) AndAlso (headCell.DataType.Value = CellValues.SharedString)) Then
                Dim shareStringPart As SharedStringTablePart = document.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().First()
                Dim items() As SharedStringItem = shareStringPart.SharedStringTable.Elements(Of SharedStringItem)().ToArray()
                Return items(Integer.Parse(headCell.CellValue.Text)).InnerText
            Else
                Return headCell.CellValue.Text
            End If

        End Using
    End Function

    ' Given a cell name, parses the specified cell to get the column name.
    Private Function GetColumnName(ByVal cellName As String) As String

        ' Create a regular expression to match the column name portion of the cell name.
        Dim regex As Regex = New Regex("[A-Za-z]+")
        Dim match As Match = regex.Match(cellName)

        Return match.Value
    End Function

    ' Given a cell name, parses the specified cell to get the row index.
    Private Function GetRowIndex(ByVal cellName As String) As UInteger

        ' Create a regular expression to match the row index portion the cell name.
        Dim regex As Regex = New Regex("\d+")
        Dim match As Match = regex.Match(cellName)

        Return UInteger.Parse(match.Value)
    End Function
End Module

Confira também

Abrir referência de biblioteca de classes do SDK XML