Saving Excel.Workbook to MemoryStream

[P.Bixam]

Hi All,

Is there any way to save Excel.Workbook into a MemoryStream or getting byte array.

I am generating an excel file dynamically based on my datatable.

later i need to save this excel file to a document library in SharePoint.

for this i need either a stream object or byte[] array.

Appreciate, if anybody could have done it earlier and share me the code.

Thanks,

Bixam

[RaM]

Hi All,

Excel objects cannot be serialized/deserialized.
So, its always better to convert them into collection of datatables(data set) and convert to stream of data.

 public DataSet GetExcel()//;string fileName)
        {
            Excel.Application oXL;
            //Workbook oWB;
            Worksheet oSheet;
            Range oRng;
            try
            {
                //  creat a Application object
                //oXL = new Excel.Application();
                ////   get   WorkBook  object
                //oWB = app.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                //                         Missing.Value,
                //                         Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                //                         Missing.Value, Missing.Value,
                //                         Missing.Value, Missing.Value);


                //   get   WorkSheet object
                oSheet = (Excel.Worksheet)Workbook.Sheets[1];
                System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                DataRow dr;


                StringBuilder sb = new StringBuilder();
                int jValue = oSheet.UsedRange.Cells.Columns.Count;
                int iValue = oSheet.UsedRange.Cells.Rows.Count;
                //  get data columns
                for (int j = 1; j <= jValue; j++)
                {
                    dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
                }


                //string colString = sb.ToString().Trim();
                //string[] colArray = colString.Split(':');


                //  get data in cell
                for (int i = 1; i <= iValue; i++)
                {
                    dr = ds.Tables["dtExcel"].NewRow();
                    for (int j = 1; j <= jValue; j++)
                    {
                        oRng = (Excel.Range)oSheet.UsedRange.Cells[i, j];
                        string strValue = oRng.Text.ToString();
                        dr["column" + j] = strValue;
                    }
                    ds.Tables["dtExcel"].Rows.Add(dr);
                }
                return ds;
            }
            catch (Exception ex)
            {
                //Label1.Text = "Error: ";
                //Label1.Text += ex.Message.ToString();
                return null;
            }
            //finally
            //{
            //    Dispose();
            //}
        }

      private Stream SerializeExcelData(DataSet ds)
        {
            FileStream flStream = new FileStream("myfile.dat", FileMode.OpenOrCreate, FileAccess.Write);
            try
            {
                BinaryFormatter binFormatter = new BinaryFormatter();
                binFormatter.Serialize(flStream, ds);
            }
            catch (Exception ex)
            {
                //Handle exception
            }
            finally
            {
                flStream.Close();
            }
            return flStream;
        }

        private DataSet DeSerializeExcelData()
        {
            BinaryFormatter binFormatter = new BinaryFormatter();
            FileStream flStream = new FileStream("myfile.dat",
            FileMode.Open, FileAccess.Read);
            DataSet ds;
            try
            {
                ds = (DataSet)binFormatter.Deserialize(flStream);
            }
            finally
            {
                flStream.Close();
            }
            return ds;
        }

check if this helps.

RaM