0

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.

  • The below code worked: ` Stream theStream = fileUploadExcel.PostedFile.InputStream; string filename = Path.GetFileName(fileUploadExcel.FileName); DataTable dt = ReadExcel.ReadData(theStream, filename); objCommonProp.dtFromExcel = dt;` – MJaiswal Apr 28 '16 at 9:05
0
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;

    }
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.