C # use the third-party component Epplus to Operate Excel tables,

C # use the third-party component Epplus to Operate Excel tables,
1. What is Epplus

Epplus is an Open source component that uses Open Office XML file format and can read and write Excel2007/2010 files. You do not need to install office on your computer when exporting Excel files.

Among them, the Open Office XML document formats include our common xlsx, docx, pptx, etc. In other words, the common files in these formats are based on bundled XML files, the essence of Epplus operations is to operate xlsx by operating XML files. XML is an extensible markup language. In a computer, it indicates an information symbol that a computer can understand, it provides a unified way to describe and exchange structured data independent of applications. XML is used to provide the ability to create content for Excel workbooks and Word documents. XML also has a place in the. NET platform. The XML parser is provided by the System. XML Naming control in the. NET Framework.

We need to add an OpenOfficeXML namespace to operate xlsx using the Epplus component on the. NET platform. Epplus supports various basic Excel functions (such as importing and exporting data, charts, VBA, pivot tables, encryption, and data verification). The only drawback is that it does not support exporting Excel 2003.

2. Use Epplus to create an Excel file

First, add the Epplus dll file to the project, and add the OpenOfficeXML namespace in the program.

1. In the using statement, use ExcelPackage to create a ZIP package object, and you can input path parameters in the package.

              using (ExcelPackage package=new ExcelPackage(new FileInfo("D:\\test.xslx")))                {                }

Here, I want to explain in detail the Epplus ----- Open Office XML format components. The Open Office XML format is implemented based on XML and ZIP technologies. XML technology allows the code to perform operations on Excel workbooks and word documents. ZIP technology enables multi-File compression and archiving, so that today's Excel and word are still in the form of single documents. In other words, excel and word are actually ZIP packages bundled with XML files.

2. Use ExcelWorksheet to create a table object

using (ExcelPackage package=new ExcelPackage(new FileInfo("D:\\test.xlsx")))             {                 ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test1");                 worksheet = package.Workbook.Worksheets.Add("test2");             }

 

In the using statement block, we can create multiple worksheets, but the names of worksheet cannot be repeated. Otherwise, an error is returned.

Worksheet has two functions. One is to create a table object and generate a table in an Excel file;

Second, it provides an effective interface for modifying the attributes of Excel tables and implementing the operations.

3. Use Package. save () to save the Excel file

 using (ExcelPackage package=new ExcelPackage(new FileInfo("D:\\test.xlsx")))                {                       ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test1");                       worksheet = package.Workbook.Worksheets.Add("test2");                       package.Save();                }

 

Only when the package. Save () function is used will the Epplus component Save the excel file on the local disk according to the initialized path.

3. Use Epplus to operate an Excel table 1. Add data

In Epplus, assigning values to cells is very simple. There are two methods: (ps: the number of all rows and columns of Epplus starts with 1)

Worksheet. cells [1, 1]. value = "name"; // specify the number of rows and columns to assign a Value to worksheet. cells ["A1"]. value = "name"; // directly specify the cell for Value assignment

2. Table Style settings

Now, you can understand that all the operations performed by the Epplus component on Excel are completed within the Epplus component.

Epplus component workflow: first create the ZIP package object ExcelPackage, and then index the table object ExcelWorksheet in the package object through workbook. You can import or export data to or from a table, and set the table format.