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
On Sunday, July 7, 2013 6:55:57 PM UTC+5:30, Bixam wrote:
>
> 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
> *
>
--
--
You received this message because you are subscribed to the Google
Groups "DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML
Web Services,.NET Remoting" group.
To post to this group, send email to dotnetdevelopment@googlegroups.com
To unsubscribe from this group, send email to
dotnetdevelopment+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/dotnetdevelopment?hl=en?hl=en
or visit the group website at http://megasolutions.net
---
You received this message because you are subscribed to the Google Groups
"DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML Web
Services,.NET Remoting" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to dotnetdevelopment+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
