Reading .xlsx in C# OpenXML

Question

I'm trying to see if it's possible to read a .xlsx file via OpenXML into a gridview with C#

I have done it via the OleDB connection but I want to experiment with using OpenXML. Is gridview the way to go with this or is there some otherway I should be handling the data it gives out?

Also if anyone has any further reading material on Importing with OpenXML that would be handy as the web seems pretty sparse at the moment.

Editing:

So after the link below I have come back with this:

  using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(xlsxFilePath, true))
            {
                //Access the main Workbook part, which contains data

                WorkbookPart workbookPart = myWorkbook.WorkbookPart;

                WorksheetPart worksheetPart = null;

                if (!string.IsNullOrEmpty(sheetName))
                {
                    Sheet ss = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>();

                    worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
                }
                else
                {
                    worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();
                }

                SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;

                if (worksheetPart != null)
                {
                    Row lastRow = worksheetPart.Worksheet.Descendants<Row>().LastOrDefault();

                    Row firstRow = worksheetPart.Worksheet.Descendants<Row>().FirstOrDefault();

                    if (firstRow != null)
                    {
                        foreach (Cell c in firstRow.ChildElements)
                        {
                            string value = GetValue(c, stringTablePart);

                            dt.Columns.Add(value);
                        }
                    }

                    if (lastRow != null)
                    {
                        for (var i = 2; i <= lastRow.RowIndex; i++)
                        {
                            DataRow dr = dt.NewRow();

                            var empty = true;

                            Row row = worksheetPart.Worksheet.Descendants<Row>().Where(r => i == r.RowIndex).FirstOrDefault();

                            var j = 0;

                            if (row != null)
                            {
                                foreach (Cell c in row.ChildElements)
                                {
                                    //Get cell value

                                    string value = GetValue(c, stringTablePart);

                                    if (!string.IsNullOrEmpty(value) && value != "")
                                    {
                                        empty = false;
                                    }

                                    dr[j] = value;

                                    j++;

                                    if (j == dt.Columns.Count)
                                    {
                                        break;
                                    }
                                }

                                if (empty)
                                {
                                    break;
                                }

                                dt.Rows.Add(dr);
                            }
                        }
                    }
                }
            }

            return dt;
        }

The problem that I am having is that I have to specify the sheet name - this may be beyond the remit of what's possible but is there an option to get the current sheet names and then have the user click which sheet they want the program to read - for example a pop-up combobox?

Answer 1

to manipulate openxml in C# you can use the openxml SDK (available here: https://msdn.microsoft.com/en-us/library/office/bb448854.aspx).

• Some more introduction to reading a file: https://msdn.microsoft.com/en-us/library/dd440953(v=office.12).aspx
• And some more about Excel: https://msdn.microsoft.com/en-us/library/office/hh180830(v=office.14).aspx

And for your problem, I don't think there is an embedded solution "Excel2Gridview" but manipulation shouldn't be very difficult. With a quick look on the net, you find the reverse question "gridview to Excel". So if you take a solution like this one https://social.msdn.microsoft.com/Forums/office/en-US/20d517d0-fb70-4879-b595-71b6d57be0ae/export-grid-view-to-excel-mysql?forum=exceldev apply the reverse method to translate your excel to gridview

update

To retrieve sheets names, take a look at: https://msdn.microsoft.com/en-us/library/office/bb507946.aspx

To list sheetnames to users, you can create a form like:

• Dialogbox to open the excel file
• Retrieve sheetnames
• Propose the user a combobox with names
• If "ok", execute your function with the chosen sheetname