0

I wanted to get data from a table in Excel, but I get them in the form

enter image description here

Instead of

enter image description here

Program code

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

static void ReadExcelFileDOM(string fileName)
    {
        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(@"PATH", false))
        {
            WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
            SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
            string text;
            foreach (Row r in sheetData.Elements<Row>())
            {
                foreach (Cell c in r.Elements<Cell>())
                {
                    text = c.CellValue.Text;
                    Console.Write(text + " ");
                }
            }
            Console.WriteLine();
            Console.ReadKey();
        }
    }

I need to get a table using the openxml library

adjutantee
  • 3
  • 2
  • "There is also an incomprehensible error "The program does not contain a static "Main" method suitable for the entry point," - that doesn't seem particularly incomprehensible to me, but we don't have enough context to help you solve it. I would suggest creating a [mcve] in a separate post for that part, and removing it from this question. – Jon Skeet Mar 25 '22 at 11:35
  • @JonSkeet You're right, I removed – adjutantee Mar 25 '22 at 11:38
  • I would suggest writing each row on a different line on the console, so you can at least check that the structure is coming across appropriately. – Jon Skeet Mar 25 '22 at 11:42
  • Additionally, see what happens if you log the `c.CellValue.OuterXml` for each cell... that will show you what the internal representation is. – Jon Skeet Mar 25 '22 at 11:43
  • As a side note, XLSX worksheets store references to 'strings' that are stored in a separate file. Copy your .XLSX to .ZIP, extract the files, and poke around with an XML editor to get a feel for how the data is stored. – Jay Buckman Mar 25 '22 at 11:47

1 Answers1

0
    using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
    {
        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
        {
            WorkbookPart workbookPart = doc.WorkbookPart;
            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
            SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

            SharedStringTablePart sstpart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
            SharedStringTable sst = sstpart.SharedStringTable;

            foreach (var row in sheetData.Elements<Row>())
            {
                foreach (var cell in row.Elements<Cell>())
                {
                    if (cell.DataType != null && cell.DataType == CellValues.SharedString)
                    {
                        int ssid = int.Parse(cell.CellValue.Text);
                        string str = sst.ChildElements[ssid].InnerText;

                        Console.WriteLine("Shared string {0}: {1}", ssid, str);
                    }
                    else
                    {
                        Console.WriteLine("Shared string {0}: {1}", );
                    }
                }
            }
adjutantee
  • 3
  • 2
  • Glad you came up with the answer to your question. Can you add some minor description of what you had to change to get to your answer? – Noel Mar 25 '22 at 22:01
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 25 '22 at 22:01
  • @Noel Found information about it here: https://stackoverflow.com/questions/23102010/open-xml-reading-from-excel-file – adjutantee Mar 28 '22 at 07:22