Microsoft Office Interop (Excel Automation) in C# and VB.NET
Microsoft Office Interop (Excel Automation) is an option when creating/reading Excel files (XLS, XLSX, CSV) from a C# or VB.NET application, but it has many drawbacks.
Some issues when using Microsoft Office Interop (Excel Automation) from C# or VB.NET are:
- It requires a license for Microsoft Office on every client machine.
- It requires all client machines to have the same version of Microsoft Excel installed.
- It works only on Windows operating systems.
- When using Automation, Microsoft Excel is loaded in the background, using a lot of RAM memory and loading many files and DLLs.
- Microsoft Office applications (including Excel) were designed as UI applications and, because of that, the API is very slow.
- Microsoft doesn't recommend using Excel Automation (or any Office Interop) on the server, see Considerations for server-side Automation of Office.
Because of that, we recommend using our Excel .NET library, GemBox.Spreadsheet, as one of the best alternatives for Microsoft Office Interop (Excel Automation). The following table lists the pros and cons of using the GemBox.Spreadsheet component over Microsoft Excel automation. When comparing and evaluating different XLSX, XLS, XLSB, ODS, CSV reading products and HTML, PDF, and XPS reporting products, don't forget the following considerations: We don't charge for additional server licenses. You can use our component for an unlimited number of projects (you don't need to purchase additional "OEM licenses"), and we don't force you to purchase subscription packages. Our licensing is very simple: every developer working with our component must have a developer license. We don't care if it is a Windows or web application, how many servers you use, or if you have just one or millions of customers. In the case of a desktop application, you don't want your users to wait +10 seconds for every single report. In the case of a web application, you want your server to simultaneously support as many users as possible. For comparison, our test uses a real-world-like Excel file containing 25 worksheets filled with tabular data, each sheet includes a mix of string and numeric values, along with a variety of cell formatting. The test simulates a common scenario: opening the Excel file, reading all allocated cells, and saving it back to disk. These were the results on our test machine: The performance example is included here, so you are free to do the test yourself and see how our component performs with the data that you require. GemBox.Spreadsheet is designed and developed to conform to Microsoft's standards for .NET libraries. Caching also enables you to access the worksheet more naturally. For example, changing cell text orientation would be one line in GemBox.Spreadsheet: and three lines in one of the competing products: The latter is a hassle and forces you to think about Excel's unique cell style limit. In other words, you must pay attention to not create the same cell style again in a more complex worksheet. GemBox.Spreadsheet works on Windows, Linux, macOS, Android, and iOS operating systems. Some similar products (and Microsoft Excel object) are old COM components with .NET RCW (Runtime Callable Wrapper). This brings many performance and interoperability disadvantages, as every method call you make goes through the wrapper until it reaches C++ code. On the other hand, our GemBox.Spreadsheet component is 100% managed, written entirely in C#, and designed to support both Visual Basic .NET and C# equally. You can test reading the content of your Excel files with the interactive example below. Just upload your file and click on Run Example. After clicking on Run example, the C#/VB.NET code will be compiled, and the content of your file will be read with only the .NET framework and the GemBox.Spreadsheet component. The following example loads the Excel file, iterates through its cells, and appends the content of the cells to StringBuilder.Better than Excel Automation
Microsoft Excel automation GemBox.Spreadsheet component Requires a license for Microsoft Excel on every client machine. Requires only the developer using our component to have one GemBox.Spreadsheet developer license, even if the developed application will be installed on thousands of client machines. Requires all client machines to have the same version of Microsoft Excel installed. Files generated with GemBox.Spreadsheet are compatible with any application that supports Excel file formats, including all versions of Microsoft Excel (starting from Excel 97), as well as alternatives like OpenOffice and LibreOffice. These applications don't have to be installed on the client machine. Works only on Windows operating systems. GemBox.Spreadsheet works on Windows, Linux, macOS, Android, and iOS operating systems. Microsoft Excel was designed as a UI application and, because of that, the API is very slow. GemBox.Spreadsheet is designed for processing large numbers of Excel files; it executes the same task(s) 266 times faster than Microsoft Excel. Microsoft Excel API is exposed as a COM object. This results in the same disadvantages as with calling any COM object from the managed code (type conversions, need for a COM wrapper, bad integration with the .NET Framework, etc.). GemBox.Spreadsheet is a pure .NET component, designed and developed to conform to Microsoft standards for .NET libraries. Outperforming competition
Plain and fair licensing
Performance
Clean and easy to use API
ws.Cells[6, 0].Style.Rotation = 30;
XLStyle someStyle = new XLStyle(book);
someStyle.Rotation = 30;
sheet[6, 0].Style = someStyle;
Portability
100% managed code
Test reading files with GemBox.Spreadsheet online
using System;
using System.Text;
using GemBox.Spreadsheet;
class Program
{
static void Main()
{
// If you are using the Professional version, enter your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load("%InputFileName%");
var sb = new StringBuilder();
// Iterate through all worksheets in an Excel workbook.
foreach (var worksheet in workbook.Worksheets)
{
sb.AppendLine();
sb.AppendFormat("{0} {1} {0}", new string('-', 25), worksheet.Name);
// Iterate through all rows in an Excel worksheet.
foreach (var row in worksheet.Rows)
{
sb.AppendLine();
// Iterate through all allocated cells in an Excel row.
foreach (var cell in row.AllocatedCells)
if (cell.ValueType != CellValueType.Null)
sb.Append(string.Format("{0} [{1}]", cell.Value, cell.ValueType).PadRight(25));
else
sb.Append(new string(' ', 25));
}
}
Console.WriteLine(sb.ToString());
}
}
Imports System
Imports System.Text
Imports GemBox.Spreadsheet
Module Program
Sub Main()
' If you are using the Professional version, enter your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook = ExcelFile.Load("%InputFileName%")
Dim sb = New StringBuilder()
' Iterate through all worksheets in an Excel workbook.
For Each worksheet In workbook.Worksheets
sb.AppendLine()
sb.AppendFormat("{0} {1} {0}", New String("-"c, 25), worksheet.Name)
' Iterate through all rows in an Excel worksheet.
For Each row In worksheet.Rows
sb.AppendLine()
' Iterate through all allocated cells in an Excel row.
For Each cell In row.AllocatedCells
If cell.ValueType <> CellValueType.Null Then
sb.Append(String.Format("{0} [{1}]", cell.Value, cell.ValueType).PadRight(25))
Else
sb.Append(New String(" "c, 25))
End If
Next
Next
Next
Console.WriteLine(sb.ToString())
End Sub
End Module