how to open Excel and show data from C# without using a disk file?

Question

What I have is an Excel document created by ClosedXML which I can write to a perfectly valid XLSX file. However, what I am looking for is to show the document in Excel without writing it to a disk file. The user can view the contents in a user-friendly way, and then decide whether to save to disk or not. This would be much more effective than first writing to a disk file, which needs a Save As dialog, and then showing it in Excel, and then, if the user does not need the output, having him or her delete that file.

Here is code that I cobbled together from various sources for opening an Excel file in a running instance of the Excel application, which will stay open even after my C# app closes:

using Excel = Microsoft.Office.Interop.Excel;
//needs ref to COM: Microsoft Excel Object Library

public void ShowFile()
{
    Excel.Application _ExcelApp = new Excel.Application();
    _ExcelApp.Visible = true;
    _ExcelApp.Workbooks.Open(@"C:\temp\us1.xlsx");
}

What I am missing is a way to Open from a MemoryStream. In many other projects, there exists a Open methods for either file or stream objects, but here I could not find it.

Another route would be to not use Open(Stream), if any, but to use that library to build one or more workbooks using OpenXml or ClosedXml libraries, because those libs can load from a stream, but that did not work. I found some code for conversion from ClosedXml to OpenXml. But I did not really found code to build a workbook that is compatible to the above fragment.

Any help is appreciated.

I do not understand why not saving into temporary file in temp directory and set read only flag and then open via Excel. I don't think that stream approach will be available for you if Excel API do not support it. — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Nov 19, 2024 at 18:46
@SvyatoslavDanyliv a temp file is certainly an option if a stream is not possible, but first I am trying to find out if it is (not) possible — Roland Kwee
– Roland Kwee, Commented Nov 19, 2024 at 18:54
Do it with temporary file and save your time. Out of Process communication is terrible complex thing. Your Stream is in your process memory space but Excel is another process, sharing memory between processes is tricky Window API calls (in case if Excel supports such sharing) and I don't think you will have serious performance benefits. — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Nov 19, 2024 at 18:59

Roland Kwee · Accepted Answer · 2024-11-19 21:23:06Z

0

Here is a partial answer. This is an invitation for others to find a better answer.

First, there is DDE, Dynamic Data Exchange. I actually used this some 30 years ago to interface Excel with data coming from a Dec VAX machine using XLisp an ASCII terminal, and a Windows C++ application. Yes, programming is fun!

Then, there is a method to generate Excel data in C# and pass it to Excel without using a file, by expanding on the code in the Question, using info from this site:

https://learn.microsoft.com/en-us/dotnet/csharp/advanced-topics/interop/how-to-access-office-interop-objects

This actually works:

using Excel = Microsoft.Office.Interop.Excel;
//needs ref to COM: Microsoft Excel Object Library

public void ShowData()
{
    Excel.Application excelApp = new Excel.Application();
    excelApp.Visible = true;
    excelApp.Workbooks.Add();
    Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
    workSheet.Cells[1, "A"] = "ID Number";
    workSheet.Cells[1, "B"] = "Current Balance";
    Random rnd_a = new Random();
    Random rnd_b = new Random();
    for (int row = 2; row < 11; row++)
    {
        double val = (rnd_b.NextDouble() - 0.1)*10000;
        workSheet.Cells[row, "A"] = rnd_a.Next(110, 999);
        workSheet.Cells[row, "B"] = val;
    }
    // column width
    workSheet.Columns[1].AutoFit();
    workSheet.Columns[2].AutoFit();
    // number format
    Excel.Range col_b = workSheet.Range["B2", "B10"];
    col_b.NumberFormat = "0.00";
    // borders
    col_b.Borders.Color = ColorTranslator.ToOle(Color.Red); // = 0x0000FF; // an RGB value in hex
    col_b.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; // .xlDot  .xlLineStyleNone
    col_b.Borders.Weight = Excel.XlBorderWeight.xlThick;
    // colors
    col_b.Interior.Color = Excel.XlRgbColor.rgbGreen; //background color
    col_b.Font.Color = Excel.XlRgbColor.rgbWhite; // or: ColorTranslator.ToOle(Color.White);
}

Now I "just" need to experiment to see if it is also possible to set colors, borders, and formulae to the cells.

Some useful links:

Cell color changing in Excel using C#

https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.border.weight

Then I need a conversion from a ClosedXml document object, in memory or stream, to the above document object.

The method of Office.Interop / COM has even options to copy a range of cells to the clipboard, and paste it into a Word document.

edited Nov 19, 2024 at 21:23

answered Nov 19, 2024 at 19:55

Roland Kwee

5,3027 gold badges59 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Blindy Over a year ago

Just a warning, that will be EXTREMELY slow, we're talking minutes for a dozen or so lines, hours for more, especially if you continue with this repeated COM object creation and deletion pattern. It will also be extremely brittle, even a sneeze in the wrong room will make both Excel and your program crash, and then Excel will remain in the background frozen and trying to relaunch your app will crash because the Excel server isn't responding. Also 32/64 bit issues, in 2025, so your application will be of really high quality.

Roland Kwee Over a year ago

@Blindy Thanks for warning. I will try more and be cautious for performance. But if DDE worked great with lots of data on a PC of 30 years ago (about 1992), I will give it a chance.

Blindy Over a year ago

This is not DDE, what you're talking about (Office Automation) is COM.

rotabor Over a year ago

I suggest you use a template. Also, for bulky data write the whole array at once:

var itm = new object[] { 1, "Hello, World!", DateTime.Now }; oSheet.get_Range(oSheet.Cells[4, 1], oSheet.Cells[4, itm.Length]).Value = itm;

You can write 1D or 2D arrays. "Microsoft.Office.Interop.Excel" provides very good performance for those who are able to program.

rotabor Over a year ago

Just tested. Write to Excel file some information and 100000 values in 1 s:

var itma = new object[100000, 1]; for (int i = 0; i < 100000; itma[i++, 0] = 3); 	oSheet.Range["G1"].Resize[100000, 1].Value = itma;

|

Collectives™ on Stack Overflow

how to open Excel and show data from C# without using a disk file?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related