none
Convert into Data Table issue from Excel Sheet Table RRS feed

  • Question

  • Hi 

    i've excel file where 10-12 sheets are available in that excel file.one of the sheet is named as summary.there are 8-10 tables(named as Table1,Table2,Table3....) in that summary sheet as well. 

    i've to actually get the Table1 from the summary sheet into the DataTable. 

    So,i've written a following method to read "summary" sheet and then i checked if the Table1 exist then break the loop.My problem is i've found Table1 but i am not able to convert it into the DataTable.Can anyone tell me how to do it.

    private void GetProjectTable(string ExcelFileUrl)
    {
                string sheetName = "Summary";
                string value = null;
    
                // Open the spreadsheet document for read-only access.
                try
                {
                    String siteURL = SPContext.Current.Site.Url;
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                    {
                        Stream dataStream = null;
                        using (SPSite site = new SPSite(siteURL))
                        {
                            using (SPWeb web = site.OpenWeb())
                            {
                                SPFile file = web.GetFile(ExcelFileUrl);
                                dataStream = file.OpenBinaryStream();
    
                                using (SpreadsheetDocument document =
                                      SpreadsheetDocument.Open(dataStream, false))
                                {
                                    // Retrieve a reference to the workbook part.
                                    WorkbookPart wbPart = document.WorkbookPart;
                                    Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
                                  Where(s => s.Name == sheetName).FirstOrDefault();
    
                                    // Throw an exception if there is no sheet.
                                    if (theSheet == null)
                                    {
                                        throw new ArgumentException("sheetName");
                                    }
    
                                    WorksheetPart wsPart =
                                    (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
    
                                    DocumentFormat.OpenXml.Spreadsheet.Table table = null;
                                    DataTable dt = null;
    
                                    foreach (TableDefinitionPart tdp in wsPart.TableDefinitionParts)
                                    {
                                        if (tdp.Table.DisplayName == "Table1")
                                        {
                                            table = tdp.Table;
    
                                            break;
                                        }
                                    }
                                }
                            }
                        }
                    });
                }
                catch (Exception ex)
                {
    
    
                }
    
    }

    Note: I am using this code in the Visual Web Part for SharePoint

    Thursday, February 12, 2015 3:25 PM

Answers

  • Hi hellofragrance,

    According to the description, you want to convert to the table in Excel to DataTable using Open XML SDK.

    As far as I know, there is no such API we can achieve the goal directly. However, we can write custom code to complete the job. Here are some major steps for your reference:
    1. Get the information of the table we wanted to convert. For example, column information and the range address for the table. And we can get the these information via the Table object of TableDefinitionPart Class

    2. Then we can create a DataTable via the column information we get from step1 and initialize the data table via read the data from range of table.

    In addition, here is helpful article about read the value of specific cell for your reference:
    How to: Retrieve the values of cells in a spreadsheet document (Open XML SDK)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, February 17, 2015 9:09 AM
    Moderator