ComponentOne Excel for .NET
In This Topic
    Importing and Exporting OpenXml Files
    In This Topic

    Excel for .NET can now read and write Microsoft Excel 2007 OpenXml files. OpenXml is an open, standards-based format introduced by Microsoft in Office 2007. OpenXml files are easier to manipulate by applications because OpenXml is based on XML and is publicly documented, as opposed to proprietary binary formats, such as BIFF8. OpenXml files contain a number of XML files compressed using Zip compression. Because they are compressed, OpenXml files are usually much smaller than traditional document files, such as .doc and .xls files.

    Excel for .NET can load and save data and formatting information in OpenXml files; however, formulas are not loaded or saved. They are copied in BIFF format as opaque. If you load files containing formulas and then save them, the formulas will be removed. This is in contrast to the traditional .xls, or BIFF8, format, which preserves the formulas.

    To support the OpenXml format, the C1XLBook Load and Save methods received overloads that take a FileFormat parameter that is used to specify the file format to use when loading or saving files.

    If the file name is not specified, then Excel for .NET infers the file format from the file name extension: files with an "XLSX" and "ZIP" extension are loaded and saved as OpenXml files, by default. Other files are loaded and saved as BIFF8, or .xls, format.

    For example:

    To write code in C#

    C#
    Copy Code
    //load and save relying on file extension
    c1Excel1.Load("somefile.xls"); // load biff 8 file
    c1Excel1.Save("somefile.xlsx"); // save file as OpenXml
    c1Excel1.Save("somefile.zip"); // save file as OpenXml
    // load and save specifying the FileFormat
    c1Excel1.Load("somefile.xls", FileFormat.Biff8);
    c1Excel1.Save("somefile.xlsx", FileFormat.OpenXml);
    

    You can also specify the format when loading or saving files to and from streams. If the FileFormat is not specified, then Excel for .NET uses the BIFF8 format as a default.

    Note that there is a small behavior change implied here. Consider the statement below:

    C1Excel1.Save("somefile.xlsx");
    

    In previous versions of Excel for .NET, this would save a BIFF8 file (with the wrong extension). Now, this will save an OpenXml file (with the correct extension). If you have code like this in your applications, you should change it to the following when upgrading:

    To write code in C#

    C#
    Copy Code
    // deliberately save file with wrong extension
    C1Excel1.Save("somefile.xlsx", FileFormat.Biff8);

    To export a book to an OpenXml file, complete the following steps:

    1. Load an existing book:

      To write code in Visual Basic

      Visual Basic
      Copy Code

      Dim wb As New C1XLBook()
      wb.Load("C:\test.xlsx")
      ' or
      Dim wb As New C1XLBook()
      wb.Load("C:\test.xlsx", C1.C1Excel.FileFormat.OpenXml)

      To write code in C#

      C#
      Copy Code

      C1XLBook wb = new C1XLBook();
      wb.Load(@"C:\test.xlsx");
      // or
      C1XLBook wb = new C1XLBook();
      wb.Load(@"C:\test.xlsx", C1.C1Excel.FileFormat.OpenXml);

    2. Export the book to an OpenXml Format file:

      To write code in Visual Basic

      Visual Basic
      Copy Code
      Dim wb As New C1XLBook()
      ' Add some content
      Dim sheet As XLSheet = wb.Sheets(0)
      Dim i As Integer
      For i = 0 To 9
              sheet(i,0).Value = i + 1
      Next i
      ' Export to OpenXml Format file
      wb.Save("C:\test.xlsx")
      ' or
      ' Export to OpenXml Format file
      wb.Save("C:\test.xlsx", C1.C1Excel.FileFormat.OpenXml)
      

      To write code in C#

      C#
      Copy Code
      C1XLBook wb = new C1XLBook();
      // Add some content
      XLSheet sheet = wb.Sheets[0];
      for (int i = 0; i <= 9; i++)
              {
              sheet[i,0].Value = i + 1;
              }
      // Export to OpenXml Format file
      wb.Save(@"C:\test.xlsx");
      // or
      // Export to OpenXml Format file
      wb.Save(@"C:\test.xlsx", C1.C1Excel.FileFormat.OpenXml);