CompuMaster.Excel.ExcelOps 2025.12.24

CompuMaster.Excel.ExcelOps

A common API for several libraries to access and edit Excel files

Why this project?

• Provides the common API for all following Excel engines
• Sometimes there is the need to switch the Excel engine under the hood, because the standard MS Excel engine (via COM interop) is too slow for many operations.
• Depending on your custom project or developer license, you might want/require to use a specific alternative Excel engine
• Allow parallel use of several Excel engines in your project
  • at least for some work items, you need Excel engine A, while for some other work item you need Excel engine B because of its feature set, bugs, etc.
  • allow step-by-step-migrations instead of one big migration task

One common API for several Excel engines

• CompuMaster.Excel.MicrosoftExcel
  • Use Microsoft.Office.Interop.Excel v15 (MS Office 2013) or higher, for solutions targetting .NET Framework 4.8 or .NET 6 or higher
• CompuMaster.Excel.EpplusFreeFixCalcsEdition
  • Use Epplus 4.5 with LGPL license for solutions targetting .NET Framework 4.8 or .NET 6 or higher
• CompuMaster.Excel.EpplusPolyformEdition
  • Use latest Epplus with Epplus Software's polyform license for solutions targetting .NET Framework 4.8 or .NET 6 or higher
• CompuMaster.Excel.FreeSpireXls
  • Use FreeSpire.Xls for solutions targetting .NET Framework 4.8 or .NET 6 or higher

Quick & dirty engine comparison / why you shouldn't use MS Excel for all situations

In following a simplified comparison without any warranties. Subjects might change over time, too. Please contact the responsible manufacturerer especially for licensing issues.

PLEASE NOTE:

¹⁾ A great article on Microsoft Excel for automation, inclusing licensing issues, is available at https://support.microsoft.com/en-us/topic/considerations-for-server-side-automation-of-office-48bcfe93-8a89-47f1-0bce-017433ad79e2

²⁾ calculation issue when re-opening in MS Excel: after Excel file has been written to disk, cell formulas are usually calculated and their results are buffered in the saved Excel file. In certain cases, MS Excel is not able to refresh calculated cell values when they depend (indirectly) on other cells which have changed.

• This issue applies for all 3rd-party engines (as far as I know)
• Available workaround in MS Excel: enter each single cell manually (and in correct dependency order!) and confirm its formula (and sorry, F9 for full automatic recalculation doesn't work)
• Available workaorund in Epplus 4 special edition (provided within this project, see CompuMaster.Excel.EpplusFreeFixCalcsEdition): Clear all cached values from cells with formulas to enforce MS Excel to recalculate them (without depending of any caches)

Licensing

• Please see license file in project directory
• Pay attention to required licensing of the 3rd party components (commercial vs. community licensing, user licensing, etc.)

Examples

Epplus 4 (LGPL)

<details> <summary>Quick-Start: Create a workbook and put some values and formulas, then output the result to console</summary>

using CompuMaster.Excel.ExcelOps;

string FirstSheetName;
TextTable formulasOrValues;
TextTable values;

//Create a workbook and put some values and formulas
ExcelDataOperationsBase workbook = new EpplusFreeExcelDataOperations(null, ExcelDataOperationsBase.OpenMode.CreateFile, new ExcelDataOperationsOptions());
FirstSheetName = workbook.SheetNames()[0];
workbook.WriteCellValue<int>(FirstSheetName, 0, 0, 123);
workbook.WriteCellValue<double>(new ExcelCell(FirstSheetName, "B1", ExcelCell.ValueTypes.All), 456.123);
workbook.WriteCellFormula(FirstSheetName, 0, 2, @"SUM(A1:B1)", true);

//Output table with formulas or alternatively with formatted cell value
formulasOrValues = workbook.SheetContentMatrix(FirstSheetName, ExcelDataOperationsBase.MatrixContent.FormulaOrFormattedText);
System.Console.WriteLine(formulasOrValues.ToUIExcelTable());

//Output table with calculated or static values
values = workbook.SheetContentMatrix(FirstSheetName, ExcelDataOperationsBase.MatrixContent.StaticOrCalculatedValues);
System.Console.WriteLine(values.ToUIExcelTable());

leads to following output

# |A  |B      |C
--+---+-------+-----------
1 |123|456,123|=SUM(A1:B1)

# |A  |B      |C
--+---+-------+-------
1 |123|456,123|579,123

</details>

Epplus (Polyform license edition)

<details> <summary>Quick-Start: Create a workbook and put some values and formulas, then output the result to console</summary>

using CompuMaster.Excel.ExcelOps;

string FirstSheetName;
TextTable formulasOrValues;
TextTable values;

//Assign required license context for Epplus component
EpplusPolyformExcelDataOperations.LicenseContext = new EpplusPolyformExcelDataOperations.EpplusLicenseActivator(OfficeOpenXml.EPPlusLicenseType.NonCommercialPersonal, "Your Name");

//Create a workbook and put some values and formulas
workbook = new EpplusPolyformExcelDataOperations(null, ExcelDataOperationsBase.OpenMode.CreateFile, new ExcelDataOperationsOptions());
FirstSheetName = workbook.SheetNames()[0];
workbook.WriteCellValue<int>(FirstSheetName, 0, 0, 123);
workbook.WriteCellValue<double>(new ExcelCell(FirstSheetName, "B1", ExcelCell.ValueTypes.All), 456.123);
workbook.WriteCellFormula(FirstSheetName, 0, 2, @"SUM(A1:B1)", true);

//Output table with formulas or alternatively with formatted cell value
formulasOrValues = workbook.SheetContentMatrix(FirstSheetName, ExcelDataOperationsBase.MatrixContent.FormulaOrFormattedText);
System.Console.WriteLine(formulasOrValues.ToUIExcelTable());

//Output table with calculated or static values
values = workbook.SheetContentMatrix(FirstSheetName, ExcelDataOperationsBase.MatrixContent.StaticOrCalculatedValues);
System.Console.WriteLine(values.ToUIExcelTable());

leads to following output

# |A  |B      |C
--+---+-------+-----------
1 |123|456,123|=SUM(A1:B1)

# |A  |B      |C
--+---+-------+-------
1 |123|456,123|579,123

</details>

FreeSpire.Xls

<details> <summary>Quick-Start: Create a workbook and put some values and formulas, then output the result to console</summary>

using CompuMaster.Excel.ExcelOps;

string FirstSheetName;
TextTable formulasOrValues;
TextTable values;

//Create a workbook and put some values and formulas
workbook = new FreeSpireXlsDataOperations(null, ExcelDataOperationsBase.OpenMode.CreateFile, new ExcelDataOperationsOptions());
FirstSheetName = workbook.SheetNames()[0];
workbook.WriteCellValue<int>(FirstSheetName, 0, 0, 123);
workbook.WriteCellValue<double>(new ExcelCell(FirstSheetName, "B1", ExcelCell.ValueTypes.All), 456.123);
workbook.WriteCellFormula(FirstSheetName, 0, 2, @"SUM(A1:B1)", true);

//Output table with formulas or alternatively with formatted cell value
formulasOrValues = workbook.SheetContentMatrix(FirstSheetName, ExcelDataOperationsBase.MatrixContent.FormulaOrFormattedText);
System.Console.WriteLine(formulasOrValues.ToUIExcelTable());

//Output table with calculated or static values
values = workbook.SheetContentMatrix(FirstSheetName, ExcelDataOperationsBase.MatrixContent.StaticOrCalculatedValues);
System.Console.WriteLine(values.ToUIExcelTable());

leads to following output

# |A  |B      |C
--+---+-------+-----------
1 |123|456,123|=SUM(A1:B1)

# |A  |B      |C
--+---+-------+-------
1 |123|456,123|579,123

</details>

Microsoft Excel (via COM interop)

<details> <summary>Quick-Start: Create a workbook and put some values and formulas, then output the result to console</summary>

using CompuMaster.Excel.ExcelOps;

string FirstSheetName;
TextTable formulasOrValues;
TextTable values;

//Create a workbook and put some values and formulas
workbook = new MsExcelDataOperations(null, ExcelDataOperationsBase.OpenMode.CreateFile, new ExcelDataOperationsOptions());
FirstSheetName = workbook.SheetNames()[0];
workbook.WriteCellValue<int>(FirstSheetName, 0, 0, 123);
workbook.WriteCellValue<double>(new ExcelCell(FirstSheetName, "B1", ExcelCell.ValueTypes.All), 456.123);
workbook.WriteCellFormula(FirstSheetName, 0, 2, @"SUM(A1:B1)", true);

//Output table with formulas or alternatively with formatted cell value
formulasOrValues = workbook.SheetContentMatrix(FirstSheetName, ExcelDataOperationsBase.MatrixContent.FormulaOrFormattedText);
System.Console.WriteLine(formulasOrValues.ToUIExcelTable());

//Output table with calculated or static values
values = workbook.SheetContentMatrix(FirstSheetName, ExcelDataOperationsBase.MatrixContent.StaticOrCalculatedValues);
System.Console.WriteLine(values.ToUIExcelTable());

leads to following output

# |A  |B      |C
--+---+-------+-----------
1 |123|456,123|=SUM(A1:B1)

# |A  |B      |C
--+---+-------+-------
1 |123|456,123|579,123

</details>