Preface
In the project, you need to use C# to read and write Excel, and output data to the Excel table regularly every day.
After referring to many solutions, we found 4 commonly used solutions and provided some small demos.
For more functions and examples, please refer to the official documentation.
1. Microsoft.Office.Interop.Excel: Not recommended.
2. OpenXml: SDK officially provided by Microsoft.
3. NPOI: A third-party open source framework with a good reputation.
4. EPPlus: It can only be used to read and write Excel. I currently use it the most.
5. Spire.Office for .NET: Commercial solution with complete and powerful functions. The features of the free version are sufficient.
environment and tools
IDE: Visual Studio 2019 and Visual Studio 2022
Framework: .NET Framework 4.8 and .NET 6.0
Features
1.Microsoft.Office.Interop.Excel
The most primitive operation library has average compatibility, and occasionally has problems with memory leaks and files that cannot be unoccupied (maybe I am too good at it), so it is not recommended.
2.OpenXml
GitHub: github.com/OfficeDev/Open-XML-SDK
NuGet: nuget.org/packages/DocumentFormat.OpenXml
An SDK officially provided by Microsoft is used to read and write Office.
Official documentation: docs.microsoft.com/en-us/office/open-xml/open-xml-sdk
3. NPOI
In Java, there is an API such as Apache POI for operating Office.
Java POI: https://poi.apache.org/
In C#, there is open source and free NPOI. Although I don’t know what the relationship between the two is, they always feel very similar.
GitHub: https://github.com/nissl-lab/npoi
NuGet: https://www.nuget.org/packages/NPOI/
How to use
public void TestNPOI()
{
string sourceFile = @"D:\sourceFile.xlsx";
string targetFile = @"D:\targetFile.xlsx";
IWorkbook workbook = new XSSFWorkbook(sourceFile);
ISheet sheet1 = workbook.GetSheet("Sheet1");
sheet1.CreateRow(0).CreateCell(0).SetCellValue(1);
sheet1.CreateRow(1).CreateCell(0).SetCellValue(2);
sheet1.CreateRow(2).CreateCell(0).SetCellValue(3);
FileStream fs = new FileStream(targetFile, FileMode.Create);
workbook.Write(fs);
workbook.Close();
}
NPOI is a bit uncomfortable to use, especially when setting cell content, which is different from the traditional way of thinking.
4. EPPlus
Official website: https://epplussoftware.com/
GitHub: https://github.com/EPPlusSoftware
NuGet: https://www.nuget.org/packages/EPPlus/
EPPlus uses LicenseContext to distinguish commercial applications (Commercial) and non-commercial applications (NonCommercial).
Instructions
public void TestEPPlus()
{
string sourceFile = @"D:\sourceFile.xlsx";
string targetFile = @"D:\targetFile.xlsx";
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//Indicate non-commercial applications
ExcelPackage package = new ExcelPackage(sourceFile);//Load Excel workbook
ExcelWorksheet sheet1 = package.Workbook.Worksheets["Sheet1"];//Read the worksheet named "Sheet1" in the workbook
sheet1.Cells[1, 1].Value = "A";//Set cell content
sheet1.Cells[2, 2].Value = "B";
sheet1.Cells[3, 3].Value = "C";
sheet1.Cells[1, 2].Value = "1";
sheet1.Cells[2, 2].Value = "2";
sheet1.Cells[3, 2].Value = "3";
//package.Save();//Save changes to the original file
package.SaveAs(targetFile);//Save changes to a new file, similar to Save As
}
The framework I currently use the most is easy to operate and consistent with usage habits. However, EPPlus can only be used to read and write Excel, and cannot read and write other files such as Word.
5. Spire.Office for .NET
Official website: https://www.e-iceblue.com/
GitHub: https://github.com/eiceblue
NuGet: https://www.nuget.org/packages/FreeSpire.Office/
Spire.Office provides a complete set of Office solutions that can read, write, and display Word, Excel, PDF, etc. It is divided into paid version and free version.
Instructions
public void TestSpireOffice()
{
string sourceFile = @"D:\sourceFile.xlsx";
string targetFile = @"D:\targetFile.xlsx";
Workbook workbook = new Workbook();
workbook.LoadFromFile(sourceFile);//Load Excel workbook
Worksheet sheet1 = workbook.Worksheets["Sheet1"];//Read the worksheet named "Sheet1" in the workbook
sheet1.SetCellValue(1, 1, "A");//Set cell content
sheet1.SetCellValue(2, 1, "B");
sheet1.SetCellValue(3, 1, "C");
sheet1.SetCellValue(1, 2, "1");
sheet1.SetCellValue(2, 2, "2");
sheet1.SetCellValue(3, 2, "3");
workbook.Save();//Save changes to the original file
workbook.SaveToFile(targetFile);//Save changes to a new file, similar to save as
}
The paid version of Spire.Office can directly display the contents of Word, Excel, and PDF files in WinForm, but the free version cannot display Excel.
An alternative is to convert Excel to PDF first and then display the PDF, but this solution can only display the first three pages.
Summarize
Among the 5 solutions introduced above, the ones I use most often are EPPlus and Spire.Office. After all, it is commercial software, and the experience is indeed different. The free version features are available.
If you just need to read and write Excel, then EPPlus is very convenient and consistent with usage habits.
If you need to display in WinForm, then Spire.Office may be the only choice.
If you need to read and write a variety of Word, Excel and other Office files, OpenXml and NPOI are also good choices.
After writing the article, I found a website that also introduced many methods of reading and writing Office in C#. It was much more detailed than what I wrote. I recommend it to everyone.
OpenXml
https://www.thecodebuzz.com/read-excel-file-in-dotnet-core-2-1/
NPOI
https://www.thecodebuzz.com/read-and-write-excel-file-in-net-core-using-npoi/
EPPlus
https://www.thecodebuzz.com/read-write-excel-in-dotnet-core-epplus/
Reprinted from: Paper is short but love is long ZF
Link: blog.csdn.net/m0_49284219/article/details/121728799
Copyright Statement: This article is collected from netizens or contributed by netizens. It is only for learning and communication. If there is any infringement, please tell the editor or leave a message, and this public account will be deleted immediately.
-EOF-
Technology group: Add editor WeChat dotnet999
Public account: dotnet lecture hall