0

I wan to convert the worksheet into memory stream so that I can make my excel downloadable at browser.

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

            // Create empty workbook
            excel.Workbooks.Add();

            // Create Worksheet from active sheet
            Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;

            try
            {
                // ------------------------------------------------
                // Creation of header cells
                // ------------------------------------------------
                workSheet.Cells[1, "A"] = "GroupId";
                workSheet.Cells[1, "B"] = "Time";



                // ------------------------------------------------
                // Populate sheet with some real data from "cars" list
                // ------------------------------------------------
                int row = 2; // start row (in row 1 are header cells)
                foreach (var item in Result)
                {
                    workSheet.Cells[row, "A"] = item.MasterID;
                    workSheet.Cells[row, "B"] = item.LocalTime;  
                    row++;
                }

I know converting into memory stream but here

 using (MemoryStream exportData = new MemoryStream())
                {

                    //workSheet.Write(exportData);  //Worksheet doesn't have this Write method as compared to workbook.
                    Response.ContentEncoding = Encoding.GetEncoding("ISO-8859-1");
                    Response.Charset = Encoding.GetEncoding("ISO-8859-1").EncodingName;
                    Response.ContentType = "application/vnd.ms-excel"; //xls
                                                                       // For xlsx, use: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
                    Response.AddHeader("content-disposition", String.Format("attachment; filename={0}.xls", "yourFilename"));
                    Response.Clear();
                    Response.BinaryWrite(exportData.GetBuffer());
                    Response.End();
                }

How to convert my workSheet to Memory stream?

Breathing
  • 109
  • 1
  • 10
  • In short, write it to the filesystem and then use something like https://stackoverflow.com/questions/730699/how-can-i-present-a-file-for-download-from-an-mvc-controller . – mjwills Jun 08 '18 at 11:29
  • @mjwills using mvc, This is all under my action method in controller – Breathing Jun 08 '18 at 11:46
  • 1
    Possible duplicate of [How can I present a file for download from an MVC controller?](https://stackoverflow.com/questions/730699/how-can-i-present-a-file-for-download-from-an-mvc-controller) – mjwills Jun 08 '18 at 11:47
  • @mjwills But I want to convert the worksheet – Breathing Jun 08 '18 at 11:48
  • 1
    There is no way to serve up an xlsx file from a stream without writing it to the file system first. Thus the duplicate is your best bet (coupled with the content types from https://stackoverflow.com/questions/2937465/what-is-correct-content-type-for-excel-files). – mjwills Jun 08 '18 at 11:49

1 Answers1

0

Didn't find the solution for Microsoft.Office.Interop.Excel. Except, that the file should be saved on machine first, than red as stream.

Spire.xsl has a method

workbook.SaveToStream(memoryStream, FileFormat.Version2016);

note: spire.xsl free version has limits.

Maria
  • 21
  • 2