CODEWITHSUNDEEP
c#excelc#-4.0openxmlopenxml-sdk
Saeid
Saeid

Reputation: 13582

Get Tables (workparts) of a sheet of excel by OpenXML SDK

I have 3 tables in a sheet of excel file, and I use OpenXML SDK to read the Excel file, like this:

SpreadSheetDocument document = SpreadSheetDDocument.open(/*read it*/);
foreach(Sheet sheet in document.WorkbookPart.Workbook.Sheets)
{
   //I need each table or work part of sheet here
}

So as you see I can get each sheet of Excel, but how can I get workparts in each sheet, like my 3 tables I should can iterate on these tables, does any one know about this? any suggestion?

Upvotes: 3

Views: 6439

Answers (2)

Amir
Amir

Reputation: 2022

You can get the specific table from excel. Adding more to the answer of @Vincent

using (SpreadsheetDocument document= SpreadsheetDocument.Open("yourfile.xlsx", true))
{
    var workbookPart = document.WorkbookPart;
    var relationsShipId = workbookPart.Workbook.Descendants<Sheet>()
                    .FirstOrDefault(s => s.Name.Value.Trim().ToUpper() == "your sheetName")?.Id;

    var worksheetPart = (WorksheetPart)workbookPart.GetPartById(relationsShipId);

    TableDefinitionPart tableDefinitionPart = worksheetPart.TableDefinitionParts
                                                                       .FirstOrDefault(r =>
                                                                         r.Table.Name.Value.ToUpper() =="your Table Name");

    QueryTablePart queryTablePart = tableDefinitionPart.QueryTableParts.FirstOrDefault();

    Table excelTable = tableDefinitionPart.Table;

    var newCellRange = excelTable.Reference;
    var startCell = newCellRange.Value.Split(':')[0]; // you can have your own logic to find out row and column with this values
    var endCell = newCellRange.Value.Split(':')[1];// Then you can use them to extract values using regular open xml 
}

Upvotes: 2

Vincent Tan
Vincent Tan

Reputation: 3166

Does this help?

// true for editable
using (SpreadsheetDocument xl = SpreadsheetDocument.Open("yourfile.xlsx", true))
{
    foreach (WorksheetPart wsp in xl.WorkbookPart.WorksheetParts)
    {
        foreach (TableDefinitionPart tdp in wsp.TableDefinitionParts)
        {
            // for example
            // tdp.Table.AutoFilter = new AutoFilter() { Reference = "B2:D3" };
        }
    }
}

Note that the actual cell data is not in the Table object, but in SheetData (under Worksheet of the WorksheetPart). Just so you know.

Upvotes: 8

Related Questions