How to export datatable to memory stream ?

Question

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

Answer 1

@ahmed salah ,

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.

Answer 2

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.