Package Description
$ dotnet add package CompuMaster.Excel.ExcelOpsA common API for several libraries to access and edit Excel files
In following a simplified comparison without any warranties. Subjects might change over time, too. Please contact the responsible manufacturerer especially for licensing issues.
| Engine | Pros | Cons | Note on licensing or support | Manufacturer website |
|---|
| Microsoft Excel | + 100% compatibility to Micrsoft Excel ;-) | - Speed - Dependency to windows platform only (and maybe MacOS) | * licensing per user (!) * NOT recommended/supported for software deployment, for servers or for similar automation 1) | www.microsoft.com |
| Epplus 5 or higher | + Speed | - no export of chart images - calculation issue when re-opening in MS Excel 2) - limited VBA/macro support | * Polyform license * limited community licensing * commercial licensing available | www.epplussoftware.com |
| Epplus 4 | + Speed | - no export of chart images - some (seldom-used) calculation functions not implemented - calculation issue when re-opening in MS Excel 2), but workaround AVAILABLE (in this project's fork) - limited VBA/macro support | * LGPL * "free license" * no manufacturer support (end of life) | www.github.com/JanKallman/EPPlus |
| Spire.Xls | + Speed + Charts export (windows platform only) | - calculation issue when re-opening in MS Excel 2) - limited VBA/macro support | * commercial licensing available | www.e-iceblue.com |
| FreeSpire.Xls | + Speed + Charts export (windows platform only) | - Limitations by manufacturer due to free edition - calculation issue when re-opening in MS Excel 2) - limited VBA/macro support | * "free license" * no official support by manufacturer | www.e-iceblue.com |
PLEASE NOTE:
1) 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
2) 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.
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
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
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
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