creating an excel reader by a stream of the first of the file , not the whole

Apr 25, 2013 at 12:02 PM
Hi All,
i wanna get the name and count of the columns of user excel files, but i do not to send whole the file to the server, but just some first bytes of it, to just read the first rows. how can i do that? is there any way not to send all of the file?
i check the source of ExcelReaderFactory, since it get a stream to create an excel reader, i hope to do it same as below, but it didn't work!
public List<string> Getheader()
        {
            List<string> headers = new List<string>();
            byte[] hdrToRead = System.Text.Encoding.UTF8.GetBytes(remainedXlsStr);
            MemoryStream stream = new MemoryStream(hdrToRead);
            IExcelDataReader excelReader = null;

            switch (currentFileType)
            {
                case (XlsType.xlsx):
                    {
                        excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                        break;
                    }
                case (XlsType.xls):
                    {
                        excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                        break;
                    }
                case (XlsType.notSupported):
                    {
                        break;
                    }
            }

            if (excelContent.HasHeader)
                excelReader.IsFirstRowAsColumnNames = true;

            if (excelReader.Read())
            { // first row
                for (int i = 0; i < excelReader.FieldCount; i++)
                {
                    // fieldType = excelReader.GetValue(i).GetType();
                    headers.Add(excelReader.GetValue(i).ToString());
                }
            }
            return headers;
        }
Apr 25, 2013 at 12:14 PM
I don't think you are going to be able to do it without sending the whole file. This is because:

a) If you are using xlsx then it is essentially a zip so it is all or nothing
b) If you are using xls then the file does not necessarily contain data sequentially. The xls header contains information on where the data can be found but that data can be spread all over the file.

Regards
Ian
Apr 26, 2013 at 7:53 AM
ok :(
so you means there is no way at all , or if i use excelReader i cant do that ?
I really appreciate you taking time to answer my question :)


Apr 26, 2013 at 2:50 PM
I don't think there is any way at all.
Apr 26, 2013 at 5:58 PM
thanks
Apr 27, 2013 at 8:04 AM
to use less memory when Converting big Excel files to DB , it differ to use AsDataSet() or using code same as below? by using excelReader.Read() ,and creating small DataTables ...,
 public DataTable ConvertXlsBlockToDataTable(IExcelDataReader excelReader, int RowCount)
        {
            DataTable xlsDt = new DataTable();
            string xlsFieldValue = string.Empty;
            int columnNumber = 0;
            var row = xlsDt.NewRow();
          
            for (int i = 0; i < RowCount; i++)
                {
                    if (excelReader.Read())
                    {
                        for (int fl = 0; fl < excelReader.FieldCount; fl++)
                        {
                            
                            if (excelReader.GetValue(fl) != null)
                             xlsFieldValue = excelReader.GetValue(fl).ToString();
                             row[columnNumber] =  xlsFieldValue;
                             columnNumber++;
                        }

                      
                        columnNumber = 0;
                        row = xlsDt.NewRow();
           
                    }
                    else
                    {
                        if (excelReader.NextResult())
                        {
                            if (excelReader.Read())
                            {
                                ...