How can I download an Excel file to the user's

问题:

In a C#, asp.net 4.0 project, I've created an Excel file using Microsoft.Office.Interop.Excel. The file gets created correctly and is placed in a folder on the server. However, I've been unable to come up with a way to display the file to the client. Can someone help me?

Some background: I'm trying to work around the Microsoft change that is blocking Excel files from opening. Our web application uses a Telerik grid and that grid is exported to an Excel file using the ExportToExcel function. The file downloads (goes to the user's downloads file), but when the user tries to open it, they get a blank Excel application. There are work arounds such as uninstalling the patch that created this problem, turning off Excel security options, and clicking unblock in the file's properties; however, our client doesn't want to do any of these. So I'm rewriting exports for 40+ grids.

I'm got the data from the radGrid to a datatable and written it to Excel using this code which works:

            Microsoft.Office.Interop.Excel.Application m_objExcel = null;
            Microsoft.Office.Interop.Excel.Workbooks m_objBooks = null;
            Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
            Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
            Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
            object m_objOpt = System.Reflection.Missing.Value;

            m_objExcel = new Microsoft.Office.Interop.Excel.Application();
            m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
            m_objBook = (Microsoft.Office.Interop.Excel._Workbook)(m_objBooks.Add(m_objOpt));

            m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
            m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));

            int colcount = 1;
            foreach (DataColumn col in dt.Columns)
            {
                m_objSheet.Cells[1, colcount] = col.ColumnName;
                colcount++;
            }

            int rowcount = 2;
            foreach (DataRow row in dt.Rows)
            {
                for (int i = 1; i < dt.Columns.Count; i++)
                {
                    m_objSheet.Cells[rowcount, i] = row[i - 1].ToString();
                }
                rowcount++;
            }

            string currentDateTime = DateTime.Now.ToString("yyyyMMddHHmmss");

            m_objBook.SaveAs("C:\\Temp\\MD" + currentDateTime + ".xlsx");
            m_objBook.Close();

            m_objExcel.DisplayAlerts = false;
            m_objExcel.Quit();

My problem comes when I try to get the user to download the file. I tried the code below, but I get an error saying "The process cannot access the file 'C:\Temp\MD20160802161458.xlsx' because it is being used by another process." Can anyone explain how to either unblock the file after it's created by Excel or show me another way to download the file to the user?

 string fileName = "C:\\Temp\\MD" + currentDateTime + ".xlsx", myStringWebResource = null;
 WebClient myWebClient = new WebClient();
 myStringWebResource = fileName;
 myWebClient.DownloadFile(myStringWebResource, fileName);

I also tried the code below to open the Excel file. The file is created on the server, but it never opens. When I try to open the file on the server, Excel crashes. I suspect this is because I have Excel 2013 on my development machine and Excel 2007 is on the server. This raises another issue because I can't guarantee what version of Excel would be on eventual production server. Any suggestions would be appreciated.

            var excelApp = new Microsoft.Office.Interop.Excel.Application();
            excelApp.Visible = true;
            excelApp.Workbooks.Open("C:\\Temp\\MD" + currentDateTime + ".xlsx");
            m_objExcel.Quit();

回答1:

Just following up on DVKs answer, I had nothing but problems using the Excel interop stuff, but if you don't need to produce older .XLS files and are fine with the .XLSX files I suggest using the OpenXML SDK (its free). There are a number of examples online on producing Excel workbooks using it. Here is one:

http://www.codeproject.com/Articles/670141/Read-and-Write-Microsoft-Excel-with-Open-XML-SDK

回答2:

The first thing to check is whether or not the Excel process is still running on the server. Microsoft strongly recommends against using the Microsoft Interop components in a production server environment due to a multitude of reasons. They were never meant for that purpose. This is the most recent article I could (quickly) find:

https://support.microsoft.com/en-us/kb/257757

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

I've tried this route (due to customer requirements) on a number of occasions, and all resulted in problems. The biggest of those is that the Excel interop component has a VERY nasty habit of not terminating the Excel process, meaning that Excel may still have your file open when you attempt to open it and send it to the client. A workaround is to (literally) write code that enumerates the running processes on the machine and closes any instances of Excel, but then of course you are running the risk of closing an instance that is performing actual work.

There are many affordable components out there that work much more reliably (such as ComponentPro - no affiliation with them but I've used their components on many occasions) and can be implemented with very little effort.

Edit - If you must use interop and force Excel to close

This StackOverflow article has a lot of different mechanisms for doing so. Depending on what you are doing exactly, you may have to try multiple routes. Releasing the COM object seems the cleanest to me.