Building Reliable Excel Export in WinForms with a .NET Spreadsheet

Microsoft Excel compatibility is an important requirement for .NET applications that use Spread.NET to create, edit, and export spreadsheet data. While Spread.NET fully supports Excel import and export, Spread.NET supports some additional Spread-specific features that are not supported in Excel. As a result, some features or settings may be handled differently when a workbook is exported and opened in Excel.

Spread.NET provides Excel export options that allow developers to control how spreadsheet data and settings are written to Excel files. Understanding these options, as well as how Spread.NET features are represented in Excel, can help developers create more reliable Excel export workflows.

In this article, we will look at several practical considerations for improving Excel compatibility when exporting Spread.NET for WinForms workbooks, including choosing appropriate export options, handling Spread.NET-specific cell types, preserving worksheet formatting, improving Excel-like rendering in Spread.NET, and validating the resulting Excel workbook.

.NET Developers Guide to Improving Excel XLSX Exporting Compatibility in WinForms Apps

• Exporting Spread.NET Workbooks to Excel
• Handling Cell Types for Excel Compatibility
• Preserving Formatting When Copying Worksheet Content
• Improving Excel-like border rendering in Spread.NET
• Validate the Exported Workbook
• Troubleshooting Excel Export Issues
• Conclusion

Download the Latest Release of Spread.NET Today!

Exporting Spread.NET Workbooks to Excel

Spread.NET for WinForms provides the FpSpread.SaveExcel() method for saving spreadsheet data to an Excel-formatted file.

To create an XLSX file, use the ExcelSaveFlags.UseOOXMLFormat option:

fpSpread1.SaveExcel(
    "Workbook.xlsx",
    FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat);

The UseOOXMLFormat option saves the workbook using the Excel Open XML (.xlsx) format.

The Exchangeable option saves additional Spread.NET extension data in the Excel file so that Spread-specific information can be restored when the workbook is later opened in Spread.NET using ExcelOpenFlags.Exchangeable.

fpSpread1.SaveExcel(
    "Workbook.xlsx",
    FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat |
    FarPoint.Excel.ExcelSaveFlags.Exchangeable);

The intended use of the exported workbook is therefore important when selecting export options. A workbook intended primarily for use in Microsoft Excel may have different requirements from a workbook that will be exported and later imported back into Spread.NET.

Handling Cell Types for Excel Compatibility

When exporting a workbook to Excel, it is important to consider how Spread.NET cell types are represented in the resulting Excel file. Some Spread.NET cell types may need to be converted to an Excel-supported representation to maintain their functionality after export.

One example is ComboBoxCellType. Spread.NET provides the ExcelSaveFlags.ComboAsDataValidation option to export combo box cells as Excel data validation.

The ComboAsDataValidation option is supported with Spread.NET's flat-style mode, where LegacyBehaviors.Style is not enabled. The following example combines this option with UseOOXMLFormat when exporting an XLSX file:

fpSpread1.SaveExcel(
    "Workbook.xlsx",
    FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat |
    FarPoint.Excel.ExcelSaveFlags.ComboAsDataValidation);

With ComboAsDataValidation, ComboBoxCellType cells are exported using Excel data validation rather than relying on the Spread.NET cell type representation.

This is useful when the exported workbook is intended to be used directly in Microsoft Excel because it maps the combo box behavior to an Excel-supported spreadsheet feature.

When Excel interoperability is the primary goal, consider how Spread.NET specific cell types and features will be represented in Excel and use the available export options when an Excel-compatible representation is provided.

Preserving Formatting When Copying Worksheet Content

When worksheet content is copied before being exported to Excel, it is important to ensure that the formatting required by the workbook is preserved.

Spread.NET for WinForms provides the SheetView.CopyRange() method for copying a range of cells to another location. The method can copy cell data, formulas, notes, and formatting. For example:

fpSpread1.ActiveSheet.CopyRange(
    0, 0,     // source row and column
    0, 5,     // destination row and column
    10, 5,    // number of rows and columns
    false);   // copy data and formatting

When the dataOnly parameter is set to false, formatting is copied along with the cell data. This can help preserve the worksheet's appearance when preparing content for Excel export.

Before exporting the resulting workbook, verify formatting that is important to the Excel file, including number formats, cell styles, and text wrapping.

Improving Excel-Like Border Rendering in Spread.NET

When an application both imports Excel workbooks and displays them in Spread.NET, visual fidelity can also be important. BorderCollapse.Enhanced option provides Excel-compatible border rendering behavior, which can help complex border layouts appear more consistently when viewed in Spread.NET.

fpSpread1.LegacyBehaviors &=
    ~FarPoint.Win.Spread.LegacyBehaviors.Style;
fpSpread1.BorderCollapse =
    FarPoint.Win.Spread.BorderCollapse.Enhanced;

BorderCollapse.Enhanced works with the flat style mode, so LegacyBehaviors.Style must not be enabled. Using this configuration can help maintain more consistent border and gridline rendering when Excel compatibility is important.

Validate the Exported Workbook

After exporting a workbook, verify that the Excel file contains the data, formatting, and functionality required by your application.

Spread.NET supports a very wide range of Excel features, but a few of Spread.NET and Excel features are represented differently during import and export. The Spread.NET Import and Export Reference provide details about how workbook, sheet, row, column, cell, cell type, and other settings are handled during Excel import and export.

For an Excel-focused workflow, verify the parts of the workbook that are important to your application, such as:

• Data and formulas
• Number and cell formatting
• Cell types that are mapped to Excel features
• Borders and visual appearance

For applications that export files for use directly in Microsoft Excel, opening the generated XLSX file in Excel is also a useful final check of the exported workbook.

Refer to the Spread.NET Excel-Formatted Files Export reference for details about which settings are preserved during export.

Troubleshooting Excel Export Issues

If an Excel export produces an unexpected workbook, collecting the relevant application and workbook information can help isolate the cause of the issue.

When troubleshooting an Excel export, consider the following information:

• The Spread.NET version used by the application
• The .NET version
• The ExcelSaveFlags used during export
• The source workbook, when applicable
• The complete exception and stack trace if the export fails

Creating a small reproducible sample that isolates the export workflow can also help determine whether the behavior is related to the workbook content, export configuration, application environment, or Spread.NET version.

Conclusion

Reliable Excel export in Spread.NET starts with understanding how Spread.NET features are represented in Microsoft Excel. Choosing the appropriate export options, using Excel-compatible representations for supported cell types, and preserving formatting before export can help produce more predictable Excel workbooks. Excel-compatible rendering settings can also improve visual consistency when Excel workbooks are displayed in Spread.NET.

Because some Spread.NET features do not have a direct equivalent in Excel, developers should also review the Spread.NET Import and Export Reference when working with features that are important to their application and verify the resulting workbook in Excel.

By considering Excel compatibility throughout the development workflow rather than only at the point of export, developers can build more reliable spreadsheet experiences for users who work across both Spread.NET and Microsoft Excel.

Download the Latest Release of Spread.NET Today!