Saving Excel 2007-2010 (.xlsx) to 2003 (.xls) with NetOffice?

Jul 5, 2011 at 7:47 PM
Edited Jul 5, 2011 at 8:27 PM

Has anyone attempted this using NetOffice?  I am using ClosedXML to do all of the manipulation of a .xlsx file, and giving the user an option to save the file in 2003 (.xls).  I am able to save this file to a folder, by trying to open it, and "SaveAs" using the Interop assemblies just hangs the application (ASP.Net).  I have tried every example I can find to close the file, and close the instance of Excel, but it will not be killed. 

The .xlsx file I'm saving already exists- is there an example of how you open an exisitng file?

Also, if NetOffice is used, does Excel or Office have to be installed on the server?

 

Thanks!

Coordinator
Jul 6, 2011 at 10:55 AM

Hello there,

First: NetOffice is an version independent access wrapper for office applications. If you want to use you have to install ms-office in any version on target system.
you told me you use interop assemblies. netoffice has the same syntax, maybe the following sample is helpfull for you.

Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
Excel.Workbook workBook = excelApplication.Workbooks.Open(@"C:\myWorkbook.xlsx");
// do something here
workBook.SaveAs((@"C:\myWorkbook.xlsx", Type.Missing, Type.Missing, Type.Missing,Type.Missing,Type.Missing, XlSaveAsAccessMode.xlExclusive);
excelApplication.Quit();
excelApplication.Dispose();
// excel is removed from process list now
Jul 6, 2011 at 3:18 PM

Sebastian,

Thanks for the reply.  I have this working perfect on my DEV machine (local), but the SaveAs will not complete on the server when it's published.

The image shows that it's trying to create a .xls file (why it's named 9CC45D10, I have no idea), but it never completes, and will hang forever.  I have to get our DBA to kill the process.  I can't believe that someone else hasn't had this as a requirement, and found a way to get it to work.

(We have installed MS Excel on the server).

Here is the code I'm using:

LateBindingApi.Core.Factory.Initialize();
                           var fileName = Path.GetFileName(filepath);
                           var filePathName = Server.MapPath("~/temp"+ "\\";
 
                           var workBook = excelApplication.Workbooks.Open(@filepath);
                           fileName = Path.GetFileNameWithoutExtension(filepath) + ".xls";
                           workBook.SaveAs(@filePathName + fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive);
                           excelApplication.Quit();
                           excelApplication.Dispose();
                           Response.Redirect("~/temp/" + fileName);
Jul 6, 2011 at 3:27 PM

Sebastian,

 

Other than MS Office/Excel, is there any other requirements that need to be installed on our server to make this work, such as the PIA?  We have Microsoft.Office.Interop.Excel referenced in the project.

Coordinator
Jul 6, 2011 at 4:14 PM

Hello again,
first: What do I have to do to deliver my application with NetOffice?
netoffice supports 'xcopy deplyoment', copy the assemblies to your target system and thats all.
the other one: i can't see the image. your code looks good. is another excel version installed on the server?
btw: if you have excel 2007 or higher and want save a .xlsx workbook to old format .xls its not helpfull to rename the extension. its still a .xlsx.
you have to change more SaveAs parameter to convert the file.
are you make sure is not a problem with the filepath or folder, invalid xml or something like that?
i do a convert test immediately.

Jul 6, 2011 at 5:16 PM

There is only one version (Office 2010) installed on out server, and that was not advised by our DBA.  The 2nd param should be the FileFormat, but I do not see a proper enumeration;  what should it be to properly SaveAs 2003 .xls?

In any case, I have used a few different API from CodePlex, and most seem to hang.  Another I tried told me that my object instance was not set, even though I captured various properties from it, so I know it was instantiated.

There must be a better way.

Coordinator
Jul 6, 2011 at 5:55 PM

the following convert code with XlFileFormat enum as 2.param in SaveAs works fine in my test.
please try to run this code as normal winform application on your server, after them try to put the code on your ASP.Net application.



           // Initialize Api COMObject Support
            LateBindingApi.Core.Factory.Initialize();

            // start excel and turn off msg boxes
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts = false;

            // add a new workbook
            string oldFileName = @"c:\book1.xlsx";
            Excel.Workbook workBook = excelApplication.Workbooks.Open(oldFileName);
            
            // save the book to xls
            string newFileName = @"c:\book1.xls";
            workBook.SaveAs(@"c:\book1.xls", XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                                            NetOffice.ExcelApi.Enums.XlSaveAsAccessMode.xlExclusive);
            
            // close excel and dispose reference
            excelApplication.Quit();
            excelApplication.Dispose();

            FinishDialog fDialog = new FinishDialog("Workbook saved.", newFileName);
            fDialog.ShowDialog(this);
Jul 6, 2011 at 6:37 PM

Thanks for your help.  I have this working for 2007-2010, using a response to HTTP to output the stream.  Your code above work perfect if I run it on my local machine. It hangs forever on the server.  I just don't see the difference.  Did you test the above code on a server?  How would you modify it to get the correct path to save to, if the destination is ~/temp folder just beneath the root?  I'm only asking that because I want to verify if I have the syntax right.  I believe I do, but would like another opinion.

If I could save 2003 to HTTP response, I think this would work fine.

Thanks

Coordinator
Jul 6, 2011 at 6:43 PM

have you try to run the code as a test with hardcoded filenames as simple winforms application on your server?
have you try to open the xlsx file by hand and re save as xls and see excel signals an error?

Jul 6, 2011 at 7:37 PM

I have not run it as a WinForms App; I generally don't have rights to do that (separation of concerns is policy).  The 2007-2010 version saves fine, so the pathing is OK.  This also opens, and allows me to Save As 2003 .xls using Excel, so it is the code that is failing, not the installation of office.

I am going to try saving with simple names as you suggest, although the names are exactly the same except for the extension, I have sometimes seen invalid characters or other errors on servers that do not have the same issues on local drives

Coordinator
Jul 6, 2011 at 7:43 PM
Edited Jul 6, 2011 at 7:44 PM

what happens exactly? SaveAs freeze your app completly or needs a long time?
your local test is also a asp.net application? you know the thread type from your asp.net app is calling SaveAs?

Jul 6, 2011 at 8:53 PM

Yes, the app freezes.  Both local and server are ASP.Net 4.0 Web Apps, which use the AspNetworker thread.

I've tried the following Excel.XlFileFormat, with results shown:


xlWorkbookNormal - fail, hangs,locks .xlsx
xlExcel7 = 39, fail, hangs,locks  .xlsx
xlExcel5 = 39, fail, hangs,locks .xlsx
xlExcel9795 = 43, fail, hangs,locks .xlsx
xlXMLSpreadsheet = 46, fails, page not found, no file created
xlExcel12 = 50,fail, hangs
xlOpenXMLWorkbook = 51,fail, hangs
xlWorkbookDefault = 51, - fail, hangs
xlExcel8 = 56, fail, hangs

Here is my save command:

oWB.SaveAs(newFileName, Excel.XlFileFormat.xlOpenXMLWorkbook, nullnullfalsefalse,
                           Excel.XlSaveAsAccessMode.xlShared, falsefalsenullnullnull);
Coordinator
Jul 6, 2011 at 9:12 PM

detailed exception info in case of fail are helpfull.

hmm..just any other ideas to test.... can you tell me more about the server- is different to your local op-system?
maybe you have 32bit and the server is 64 with 64bit office or something like that? 
maybe i can create a better test scenario to reproduce these error.

Jul 6, 2011 at 9:57 PM

Sebastian,

 

Thanks for your patience.  I finally found out what the problem is, and it is not my code.  For some reason, there is an extra layer of permissions when you want to Save As.  That is why the Save worked, but the Save As did not, at least via the AspNet worker thread.  This has been strictly a permissions issue all along.  My DBA changed one little thing, and it works perfectly.  Cheers.

 

Steve