OpenXML - Create and Open Excel File without saving It

Question

I have a code that creates a new Excel file from Datatable, using OpenXML. When I'm done with creating this file, I want to open It for user but without saving It. Basically, what Excel does in this case is that It opens excel file as "Workbook1", and then If you wish you save It manually. I have this demand because users wants to check if data corresponds before saving file to disk.

That could be done in Interop by Visibility property (I have this solution allready, but problem is that Interop is very slow on huge data so users aren't satisfied with It), but I can't find a way to do same in OpenXML. If anyone has any suggestions, please let me know. Here is my code for creating Excel file:

  public void Export_To_Excel_stream(MemoryStream ms, DataTable dt)
        {
            using (SpreadsheetDocument dokument = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = dokument.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                worksheetPart.Worksheet = new Worksheet(sheetData);

                Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
                Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = dt.TableName };
                sheets.Append(sheet);

                //header row
                Row header = new Row();
                List<String> Cols = new List<string>();
                foreach (DataColumn col in dt.Columns)
                {
                    Cols.Add(col.ColumnName);

                    Cell cell = new Cell
                    {
                        DataType = CellValues.String,
                        CellValue = new CellValue(col.ColumnName)
                    };
                    header.AppendChild(cell);
                }
                sheetData.AppendChild(header);


                foreach (DataRow row in dt.Rows)
                {
                    Row new_row = new Row();
                    foreach (String col in Cols)
                    {
                        Cell cell = new Cell
                        {
                            DataType = CellValues.String,
                            CellValue = new CellValue(row[col].ToString())
                        };
                        new_row.AppendChild(cell);
                    }
                    sheetData.AppendChild(new_row);
                }
                workbookPart.Workbook.Save();

            }

It's either direct file manipulation with all the speed, but creating a file. Or slow interop without file. — Sinatr
Perhaps you could still use direct file manipulation, but do something on top of it to simulate what you want? Not sure, but check if you can reset saved flag or maybe copy file, or maybe open it in memory (from stream, etc). — Sinatr
@Sinatr, thanks for answer, at least now I know where I am at...But I'm not sure what I could do on top of It to simulate, what are you thinking ? — Lucy82
E.g. check this question. You create file and save it as you do already, then open it in MemoryStream and finally open that in excel. That will probably lead to file being opened as you want: with Workbook1 name and usaved flag. Maybe you can open from stream using OpenXml, investigate this possibility. — Sinatr
Another idea worth to check is if you can copy workspace. Have one excel instance loading file (don't show this one to user?) and copy workspace into a new instance. Or maybe just open workspace, then copy (using VBS scripts?) and close previous one (and delete file as well). — Sinatr

Lucy82 Lucy82 · Accepted Answer · 2019-04-15T12:08:48

Best thing I figured out is to open Excel file as process, and then delete It when process ends. However, my code for OpenXML is a .dll, so when I close app with allready opened Excel file, It doesn't delete automatically anymore:

public string file_path;

public void Export_To_Excel_stream(MemoryStream ms, DataTable dt)
{
    //...at the end, whe OPENXML file is created..        
    var open_Excel = Process.Start(file_path);
    open_Excel.EnableRaisingEvents = true;
    open_Excel.Exited += new EventHandler(open_excel_Exited);
 }

public void open_excel_Exited(object sender, EventArgs e)
{
     File.Delete(file_path);
}

I would be more than happy If anybody has a better solution :)

OpenXML - Create and Open Excel File without saving It

1 Answers