Saving a ClosedXML spreadsheet as 2003 (.xls)

May 16, 2011 at 3:54 PM

I read on another topic that neither OpenXML or ClosedXML support 2003 (.xls) format.  If I generate a workbook using it, how can I save it as a 2003 .xls worksheet?  I'd like to do it using ClosedXML, but if I have to take the existing file and use InterOp or other libraries, I'll take that as success if I can just accomplish the task.  In a similar manner, can ClosedXML open and manipulate an Excel 2003 .xls file, and save it as .xlsx?

 

I've searched on many forums, but the answers are not clear- many other (older) projects seem to allow this, but they don't start with a template, so you have to fill in all of the data.  I haven't seen an example where an existing .xls file is opened.

 

Thanks- this is a very helpful project, especially all of the LINQ.

Coordinator
May 16, 2011 at 5:44 PM

ClosedXML only supports Excel 2007/2010 format so you can't read/write .xls files.

May 16, 2011 at 7:53 PM

Yes, I understand that from the 2nd link in the FAQ on Home page.  But if I have a 2010 .xlsx there must be a way to "Save As" 2003 .xls using C#.  I have seen some VBA code that does this, but I haven't done VBA for 15 years.  I'm looking for a C# .Net 4.0 way to do this in code, and then do the reverse if the file is uploaded so it will be in 2010 .xlsx.  Once I have it in 2010 I can use all the cool methods in ClosedXML.  But a lot of the users don't have 2007 or better on their machines.  If you can point me to a link or two where this "Save As" and version number are demonstrated, I would be grateful.

 

Many thanks

Coordinator
May 16, 2011 at 8:11 PM

I think you only have two options for converting a .xlsx to .xls and back:

  1. Use interop
  2. Use a 3rd party component like Spreadsheetgear, ComponentOne, ASPOSE, JetCell, etc.
May 16, 2011 at 9:22 PM

I haven't found a good example where InterOp uses an existing .xls or .xlsx.  Do you know of a good example? 

 

Thanks

Coordinator
May 16, 2011 at 9:59 PM

            var app = new Microsoft.Office.Interop.Excel.Application();
            var wb = app.Workbooks.Open("Office2007File.xlsx");
            wb.SaveAs(Filename: "Office2003File.xls", FileFormat: Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8);
            wb.Close();
            app.Quit();

May 17, 2011 at 3:51 PM

Thanks for the snippet- it works, given the compatibility issues.  Since I have this thread open, I'd like to ask about 2 other issues:

 

1. How do I re-size a named range, so that the validation on a dropdown will show the list whether it shrinks or grows ('skip blanks' has no effect)?

2. This one may be a potential bug.  I have a template, that is mostly filled out, with validation on the area I allow input on.  It has 3 worksheets, but when I enter values in column A, B and C on worksheet 1, the same values are repeated on worksheet 2, and vice versa.  This does not occur if I enter these values on the template without using ClosedXML to manipulate it, only after I save it.  I can't see any ranges I am creating, or any other reason the sheet should behave this way.  And then it will suddenly stop doing it.

What's the best course of action?  I can send you the code, or the final workbook that has the issue, or both.  Please respond, and let me know if you can help, and how to proceed.  Thanks

Coordinator
May 17, 2011 at 4:08 PM

Please create an entry on the Issue Tracker tab and attach the file you're using. I'll take a crack at both items later on.

Thanks for the feedback.

May 17, 2011 at 6:23 PM
Edited May 17, 2011 at 6:24 PM

Thanks for the quick response.  #2 above was difficult to find, but I re-built the template from scratch (original was made by another party), and I think it was being caused by ranges in the same column, though different name definition and worksheet, and possibly the scope.  It's also possible there was something going on in VBA code even though I could not find anything.  Whatever the cause, I have fixed it.

 

So the last items are # 1. from above.  I am changing the range for a list validation..  I had thought just setting the range name and the values would do it, but the dropdown keeps the same range even though I have set the values and thus the range to accomodate the list of items they should be using.  Sometimes it's larger, sometimes smaller.  So, do I need to re-set the range (as shown below- this is what I'm currently doing:)

ws.Range(115, y, 15).AddToNamed("ZipCodeReportPeriods");

- the default adds to the range if it exists, but does it shrink it?  And also change a property of the dropdown cell to update the new range?

 

Last, your snippet above worked well.  How do I modify it to convert up to Excel 2010 .xlsx?   The XlFileFomat enum does not have an option that matches 2010.

 

Thanks for all your help.

May 17, 2011 at 8:14 PM

Hi again.  I found that the code below works:  the key is that you should not define the name in advance, else it appears more than once in the NamedRanges collection, which throws one.

 

if (startDate != null && endDate != null)
                        {
                            {
                                for (var dt = startDate; dt <= endDate; dt = dt.AddMonths(1))
                                {
                                    ws.Cell(y, 15).Value = string.Format("{0:MMMM yyyy}", dt);
                                    y++;
 
                                }
                                
                                ws.Range(115, y-115).AddToNamed("ZipCodeReportPeriods");
                                
                            }
                        }
 
                    }
                    var dd = workbook.NamedRanges.NamedRange("ZipCodeReportPeriods");
                    ws.Cell(27).DataValidation.List(dd.Name);