Reading excel sheet without saving

Feb 3, 2014 at 3:01 PM
Hi Team,
      I want user to give option to browse and upload excel sheet and then load worksheet data into sql server table.
     Dev Server its fine, But in Prod.Server I don't have write access on any directory. So, I have to read it directly from the memory stream without saving it. Following code am executing with the help of ExcelData Reader dll, could you please suggest if am on the right track to avoid any write permission issue on web server?

    if (FileUploadControl.HasFile)
        {
            try
            {
                string fileExt = System.IO.Path.GetExtension(FileUploadControl.FileName);

                if (fileExt == ".xls")
                 {
                   Stream myStream = FileUploadControl.PostedFile.InputStream;

                    IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(myStream);

                    excelReader.IsFirstRowAsColumnNames = true;

                    DataSet ds1 = excelReader.AsDataSet();

                     excelReader.Close();
                   }
Developer
Feb 5, 2014 at 10:09 AM
You may need to read use Stream.CopyTo to read everything in the InputStream into a seekable MemoryStream. Otherwise you may have issue with the InputStream not being seekable.


Feb 6, 2014 at 2:17 PM
Ok thanks,
           modified code as per you suggested. Is this ok? 

            MemoryStream memStream = new MemoryStream();
            Stream myStream = FileUploadControl.PostedFile.InputStream;

            myStream.CopyTo(memStream);

           IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(memStream);
          excelReader.IsFirstRowAsColumnNames = true;
          DataSet ds1 = excelReader.AsDataSet();
          excelReader.Close();
Developer
Feb 6, 2014 at 5:00 PM
That looks about right. Does it work?


May 26, 2014 at 1:36 PM
Thanks for this.

I tried this (i.e. copy to memory stream) and yes, it does work.

This is very helpful for using this on Azure since we don't want to save files to local storage.
Jul 22, 2014 at 2:46 PM
This does not work for me....

I get a read timeout error?

also tried as per MS: Stream myStream = FileUpload1.FileContent;

on this I get an invalid signature

Anyone have this working ok?


Thanks
Jul 22, 2014 at 3:15 PM
Never mind me silly got it to work:


if (fileExt.ToUpper() == ".XLS" )
                {

                    excelReader =ExcelReaderFactory.CreateBinaryReader(memStream);
                }

                if (fileExt.ToUpper() == ".XLSX")
                {
                 excelReader =  ExcelReaderFactory.CreateOpenXmlReader(memStream);
                }

Much better :-)