Importing and Exporting OpenXml Files (WPF)

Excel for WPF 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 WPF 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, which is not supported by the OpenXML format at this time. 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 WPF 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:

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

Copy Code

    // load and save relying on file extension
    book.Load("somefile.xls");    // load biff 8 file
    book.Save("somefile.xlsx");   // save file as OpenXml
    book.Save("somefile.zip");    // save file as OpenXml
    // load and save specifying the FileFormat
    book.Load("somefile.xls", FileFormat.Biff8);
    book.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 WPF uses the BIFF8 format as a default.

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

book.Save("somefile.xlsx");

In previous versions of Excel for WPF, 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:

// deliberately save file with wrong extension book.Save("somefile.xlsx", FileFormat.Biff8);

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

Load an existing book:

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.WPF.Excel.FileFormat.OpenXml)

C#	Copy Code
C1XLBook wb = new C1XLBook(); wb.Load(@"C:\test.xlsx"); // or C1XLBook wb = new C1XLBook(); wb.Load(@"C:\test.xlsx", C1.WPF.Excel.FileFormat.OpenXml);

Export the book to an OpenXml Format file:

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.WPF.Excel.FileFormat.OpenXml)

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.WPF.Excel.FileFormat.OpenXml)

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.WPF.Excel.FileFormat.OpenXml);

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.WPF.Excel.FileFormat.OpenXml);