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

Question

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?

http://stackoverflow.com/questions/2624333/how-do-i-read-data-from-a-spreadsheet-using-the-openxml-format-sdk — Siddharth Rout, Jan 22 '13 at 08:04
@SiddharthRout I read this post actually, But I don't want to iterate on rows and cells only, I need to iterate the parts of any sheet, like I have 3 tables on sheet and need to iterate on them, means each table must be a separate object. — Saeid, Jan 22 '13 at 08:08
Did you see the 2nd link int he answer `Using LINQ to Query Tables in Excel 2007` — Siddharth Rout, Jan 22 '13 at 08:12
@SiddharthRout The 2nd link describe to read an excel file with two tables in two sheet so could separate them by sheet, but as I said I need two tables in one sheet, there isn't any explanation about it. — Saeid, Jan 22 '13 at 09:21

score 8 · Accepted Answer · answered Jan 22 '13 at 14:11

8

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.

answered Jan 22 '13 at 14:11

Vincent Tan

3,058
22
21

So how can I get values of cells Table by Table? is there any way? – Saeid Jan 23 '13 at 05:28
1

Like I said, the cell value is not in the Table, but in the SheetData object. Just search on Stack Overflow for how to do it. This is a start: http://stackoverflow.com/questions/13161056/reading-data-from-excel-cells-using-openxml-sdk-2-0 – Vincent Tan Jan 23 '13 at 13:03
2

Let's say from the Table object, you know the range is B2:D3. Then you'll have to iterate through cells B2 to D3 from the SheetData to get the cell value. It can be tedious. – Vincent Tan Jan 23 '13 at 13:05

score 2 · Answer 2 · answered Jan 14 '22 at 21:35

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 
}

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

2 Answers2