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

admin管理员组

文章数量:1336289

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.

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.

• c#
• excel
• stream
• office-interop

Share Follow edited Nov 19, 2024 at 20:00 Roland asked Nov 19, 2024 at 18:23 RolandRoland 5,23277 gold badges5757 silver badges8787 bronze badges 4

• 2 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 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 Commented Nov 19, 2024 at 18:54
• 2 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 Commented Nov 19, 2024 at 18:59
• 1 This might help: stackoverflow/questions/61240059/… – Flydog57 Commented Nov 20, 2024 at 1:25

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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/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/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.

本文标签： how to open Excel and show data from C without using a disk fileStack Overflow