how to get excel data imported using fileupload control into a data table by csom

Question

how to get excel(xlsx) data that i uploaded using fileupload control into a data table in csom - O365

data from fileupload.filecontent to a data table.

I am able to achieve that using EPPLUS, but i want to achieve it without using any 3rd party dll.

Answer 1

public class ReadExcel
{
    #region readExcel
    public static DataTable ReadData(Stream theStream, string sheetName)
    {
        DataTable dt = new DataTable();
        try
        {
            using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(theStream, false))
            {

                WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart; IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart = null;
                worksheetPart = (WorksheetPart)workbookPart.GetPartById(relationshipId);
                Worksheet workSheet = worksheetPart.Worksheet;
                SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                IEnumerable<Row> rows = sheetData.Descendants<Row>();
                foreach (Cell cell in rows.ElementAt(0))
                {
                    dt.Columns.Add(GetCellValue(spreadSheetDocument, cell).Trim());
                }
                foreach (Row row in rows) //this will also include your header row...
                {
                    DataRow tempRow = dt.NewRow();
                    int columnIndex = 0;
                    foreach (Cell cell in row.Descendants<Cell>())
                    {
                        // Gets the column index of the cell with data
                        int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
                        cellColumnIndex--; //zero based index
                        if (columnIndex < cellColumnIndex)
                        {
                            do
                            {
                                tempRow[columnIndex] = ""; //Insert blank data here;
                                columnIndex++;
                            }
                            while (columnIndex < cellColumnIndex);
                        }
                        tempRow[columnIndex] = GetCellValue(spreadSheetDocument, cell);

                        columnIndex++;
                    }
                    dt.Rows.Add(tempRow);
                }
            }
            dt.Rows.RemoveAt(0); //...so i'm taking it out here.


        }
        catch (Exception ex)
        {
        }
        return dt;

    }