question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked JackJJun-MSFT answered

How to export datatable to memory stream ?

I working on csharp and i need to replace file path
by memory stream
function below export data from data table to file path

but i need to export data table to memory stream

  public void Export(DataTable dt, string module, string FilePath)
         {
    
             FileInfo file = new FileInfo(FilePath);
             using (ExcelPackage pck = new ExcelPackage(file))
             {
                 string sheetname = null;
    
                 sheetname = pck.Workbook.Worksheets.FirstOrDefault().Name;
                 if (sheetname == "Sheet1")
                 {
                     ExcelWorksheet ws = pck.Workbook.Worksheets.FirstOrDefault();
                     ws.Name = module;
                     ws.Cells["A1"].LoadFromDataTable(dt, false);
                     pck.Save();
                 }
                 else
                 {
                     if (module.Length > 30)
                         module = module.Substring(module.Length - 30, 30);
    
                     ExcelWorksheet ws = pck.Workbook.Worksheets.Add(module);
    
                     ws.Cells["A1"].LoadFromDataTable(dt, false);
                     pck.Save();
    
                 }
             }
    
    
         }

so how to do that please

dotnet-csharp
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JackJJun-MSFT avatar image
0 Votes"
JackJJun-MSFT answered

@ahmedsalah-1628 ,

can you please tell me how to implement that point below please

ExcelPackage has a special constructor ExcelPackage(Stream) that accepts a Stream as a parameter. You can create MemoryStream and pass that to the constructor.

If you want to pass a stream as a parameter to the Constructor, you could refer to the following code.

         ExcelPackage.LicenseContext = LicenseContext.Commercial;
         ExcelPackage package = new ExcelPackage(memstream);
         var ws = package.Workbook.Worksheets.First();
         Console.WriteLine(ws.Name);


Based on your original question, please try the following code to convert the datatable to MemoryStream. We often use byte array to indicate the MemoryStream.

   public static byte[] GetBytesFromDatatable(DataTable table)
         {
             byte[] data = null;
             using (MemoryStream stream = new MemoryStream())
             {
                 IFormatter bf = new BinaryFormatter();
                 table.RemotingFormat = SerializationFormat.Binary;
                 bf.Serialize(stream, table);
                 data = stream.ToArray();
             }
             return data;
         }

Also, you could use the following code to check if the convert is correct.

  public static DataTable ByteArrayToDatable(byte[] arrBytes)
         {
             MemoryStream memStream = new MemoryStream();
             BinaryFormatter binForm = new BinaryFormatter();
             memStream.Write(arrBytes, 0, arrBytes.Length);
             memStream.Seek(0, SeekOrigin.Begin);
             DataTable table = (DataTable)binForm.Deserialize(memStream);
    
             return table;
         }

Regards,

Jack


If the response is helpful, please click "Accept Answer" and upvote it.


Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ArtemiyMorozUA avatar image
0 Votes"
ArtemiyMorozUA answered ahmedsalah-1628 commented

I assume you use EPPlus software. The documentation states that ExcelPackage has a special constructor ExcelPackage(Stream) that accepts a Stream as a parameter. You can create MemoryStream and pass that to the constructor.

But I advise you to switch to OpenXML sdk. While it has a slightly higher entry entry threshold, you won't be dependent on third-party software.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

can you please tell me how to implement that point below please

ExcelPackage has a special constructor ExcelPackage(Stream) that accepts a Stream as a parameter. You can create MemoryStream and pass that to the constructor.

0 Votes 0 ·