C # using third-party component Epplus to manipulate Excel tables

Epplus Operations Excel Basics 1. What is Epplus

Epplus is an open source component that uses the open Office XML file format to read and write excel2007/2010 files, and you do not need to have Office installed on your computer when you export Excel.

Where the Open Office XML document format includes our common xlsx, docx, pptx, etc., in other words, our common format files are based on bundled XML files, the essence of using the epplus operation is to manipulate the XML file to manipulate xlsx. XML, an Extensible Markup language, marks the information symbols that a computer can understand in a computer, providing a uniform way to describe and exchange structured data that is independent of the application. Use XML to provide the ability to create content for Excel workbooks and Word documents. XML also has a place in the. NET platform, which is provided by the System.Xml named control in the. NET Framework.

We are based on. NET platform uses the Epplus component to manipulate xlsx we need to add the Openofficexml namespace. Epplus can implement a variety of basic features on Excel (such as import and export data, charts, VBA, PivotTable reports, encryption, data validation, and so on), the only drawback is that exporting Version 2003 Excel is not supported.

2. Create Excel using Epplus

First, add the epplus DLL file to the project, and in the program, add the Openofficexml namespace.

1. In the using statement, use Excelpackage to create the Zip package object, and you can pass in the path parameter in the packages.

              using (Excelpackage package=new excelpackage (new FileInfo ("d:\\test.xslx" ) )))                {                }

here, I need to give you a detailed explanation of Epplus-----Open Office XML format components. The Open Office XML format itself is implemented based on XML technology and ZIP technology. XML technology, which enables the code to operate on Excel workbooks and Word documents, and zip technology, which enables multiple file compression archives, so that today's Excel and Word are in single document form. In other words, after the 07 version of today, Excel and Word are essentially a ZIP package bundled with an XML file.

2. Create a Table object using Excelworksheet

using (Excelpackage package=new excelpackage (new FileInfo ("d:\\test.xlsx" ) ))              {                 = package. WORKBOOK.WORKSHEETS.ADD ("test1");                  = Package. WORKBOOK.WORKSHEETS.ADD ("test2");             }

In the using statement block, we can create multiple worksheet, but the name of worksheet cannot be duplicated, otherwise it will be an error.

Worksheet here have two functions, one, create a Table object, in the Excel file, generated a table;

Second, it provides an effective interface for the subsequent changes to the various properties of the Excel table and the method of operation implementation.

3. Save Excel using Package.save ()

using (Excelpackage package=new excelpackage (new FileInfo ("d:\\test.xlsx" ) ))                 {                       = package. WORKBOOK.WORKSHEETS.ADD ("test1");                        = Package. WORKBOOK.WORKSHEETS.ADD ("test2");                       Package. Save ();                }

only use, package. Save () function, the Epplus component saves Excel on the local disk by initializing the incoming path.

3. Working with Epplus on Excel table 1. Add Data

It is very simple to assign values to cells in Epplus, two methods: (All the rows of Ps:epplus are starting with 1)

         Worksheet. cells[11" name ";   assigns a value worksheet directly to the number of rows specified         . cells["A1 " " name ";  assign values directly to a cell

2. Table Style settings

in this case, you should be able to understand that all the operations of the Epplus component to Excel are done within the Epplus component.

Epplus Component Workflow: First create a Zip package object excelpackage, and then excelworksheet through the Workbook Index Table object in the package object. The data import or export to the table, as well as the formatting of the table, are set or manipulated for worksheet.

C # using third-party component Epplus to manipulate Excel tables