Write data from memory stream to excel file.

Hi Lalitha,

Please use below one which will help you out,

using System;
using System.IO;
using Microsoft.Office.Interop.Excel;

class Program
{
    static void Main()
    {
        // Sample data to write to Excel
        var data = new[]
        {
            new { Name = "Joydeep", Age = 30, PhoneNumber ="987654321" },
            new { Name = "Raman", Age = 25, PhoneNumber ="987653434" },
            new { Name = "Deep", Age = 35,PhoneNumber ="987634534" }
        };

      
        Application excelApp = new Application();
        excelApp.Visible = false; 
        // Create a new workbook

        Workbook workbook = excelApp.Workbooks.Add(Type.Missing);

        // Create a new worksheet
        Worksheet worksheet = (Worksheet)workbook.Worksheets[1];

        // Populate Excel worksheet with data from MemoryStream
        int rowIndex = 1;
        foreach (var item in data)
        {
            worksheet.Cells[rowIndex, 1] = item.Name;
            worksheet.Cells[rowIndex, 2] = item.Age;
            worksheet.Cells[rowIndex, 3] = item.PhoneNumber;
            rowIndex++;
        }

        // Save the workbook to a MemoryStream
        MemoryStream memoryStream = new MemoryStream();
        workbook.SaveAs(memoryStream);

        // Release Excel resources
        workbook.Close(false, Type.Missing, Type.Missing);
        excelApp.Quit();
        Marshal.ReleaseComObject(workbook);
        Marshal.ReleaseComObject(excelApp);

        // Save the MemoryStream to an Excel file
        File.WriteAllBytes("output.xlsx", memoryStream.ToArray());

        Console.WriteLine("Excel file created successfully.");
    }
}

You can try leveraging the DocumentConverter class to convert to Excel which does support passing a MemoryStream. Here is an example that can get you started.

static void Main(string[] args)
{
   var inBytes = File.ReadAllBytes(@"C:\LEADTOOLS22\Resources\Images\Leadtools.pdf");
   var outBytes = ConvertStreams(new MemoryStream(inBytes)); // or use your existing stream
   File.WriteAllBytes("D:\\Desktop\\test.xls", outBytes.ToArray());
}

static MemoryStream ConvertStreams(Stream inputStream)
{
   var ms = new MemoryStream();
   using var document = DocumentFactory.LoadFromStream(inputStream, new LoadDocumentOptions());
   using var documentConverter = new DocumentConverter();
   var jobData = new DocumentConverterJobData
   {
      Document = document,
      DocumentFormat = DocumentFormat.Xls,
      OutputDocumentStream = ms
   };

   var job = documentConverter.Jobs.CreateJob(jobData);
   documentConverter.Jobs.RunJob(job);

   if (job.Status == DocumentConverterJobStatus.Success)
      return ms;
   else
      return null;
}

https://www.leadtools.com/help/sdk/tutorials/dotnet-console-convert-files-with-the-document-converter.html

Thank you Leon.

My data is in string builder, how to convert that data into stream - please help me on this, I am really stuck with this issue.

I tried to convert from string to stream using below code

byte[] byteArray = Encoding.ASCII.GetBytes(sb.ToString());
MemoryStream stream = new MemoryStream(byteArray);

Workbook workbook = new Workbook();
workbook.LoadFromStream(stream);

workbook.SaveToFile("D:/ReadStream.xlsx", ExcelVersion.Version2013);

while writing into excel it is throwing "Cannot read that as a zip file" error.

Thanks,

Lalitha.C

You can install free spire.xls through nuget (https://www.nuget.org/packages/FreeSpire.XLS/), then refer to the below code sample to write data from the memory stream to an Excel file:

Workbook workbook = new Workbook();
workbook.LoadFromStream(stream);

workbook.SaveToFile("ReadStream.xlsx", ExcelVersion.Version2013);