0

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();

            }
Lucy82
  • 654
  • 2
  • 12
  • 32
  • It's either direct file manipulation with all the speed, but creating a file. Or slow interop without file. – Sinatr Apr 08 '19 at 08:10
  • 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 Apr 08 '19 at 08:13
  • @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 Apr 08 '19 at 08:17
  • E.g. check [this question](https://stackoverflow.com/q/560435/1997232). 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 Apr 08 '19 at 08:28
  • 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 Apr 08 '19 at 08:38
  • @Sinatr, all this suggestions are way above my knowledge. Maybe you could post some sample code to give me a clue....But currently I'm trying to use Interop with code above, I'm trying to pass memorystream to new Interop.Excel.Application() and then just open file. – Lucy82 Apr 08 '19 at 08:44
  • Is this file intended to be served back to the user via a web interface? – Taterhead Apr 09 '19 at 07:45
  • @Taterhead, no It's C# desktop app, export to Excel comes from Datagridview control. Unfortunally users use this quite often, so that's why they want solution like previous one (Interop), without saving file. But they are not satisfied with speed of export. Bump. – Lucy82 Apr 10 '19 at 04:52
  • is this to be run on a normal modern windows 8 or 10 desktop? not in a VDI or thin client situation, correct? – Taterhead Apr 10 '19 at 13:16
  • @Taterhead, It's intended only for Windows 7-10. – Lucy82 Apr 10 '19 at 13:19
  • Hi Lucy - this might be a duplicate question - please see: https://stackoverflow.com/a/29950024/819019. Let me know if this works and we can close down this as a duplicate. – Taterhead Apr 19 '19 at 13:02
  • @Taterhead, I've allready seen this answer and I tried before posting here, but I couldn't make It work. This answer is intended for web apps though. – Lucy82 Apr 20 '19 at 17:05

1 Answers1

0

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 :)

Lucy82
  • 654
  • 2
  • 12
  • 32