What else can't be done? -- I will talk about reading and writing an Excel series in C #.

Writing this seriesArticleBefore that, I had to declare one thing, that is, from the very beginning we started to use. Net in our ownProgramAnd Excel, such as reading data display reports from Excel and exporting data from the program to excel. This seems common, and I believe many people have written or read similar programs. In earlier versions of. net, we need to execute a command in Windows (for too long I don't quite remember what this command is called) to turn the Office COM component into a hostedCodeSo that it can be called in the. NET program. Later, the. NET Framework can fully support the call of the office COM component, but you need to add references to the project. But in any case, we operate the Office COM component in the program, which will cause a lot of inconvenience, whether in terms of program execution efficiency or code editing, it will make you feel awkward.

What's the difference now?

I mean, what if the environment where your program runs is not installed, or you have not registered the Office COM component in the running environment?

You may say that this is not a problem with the program itself. We can even ask the customer to provide the office COM component. However, please note that if your program requires cross-platform use, and the office COM component is not supported on the Target Platform (Why is there such a strange demand ?!), You may have some trouble. At this time, we can consider providing an alternative option in the program to disable the corresponding functions, so as to avoid the program mistakenly using the non-existing office COM component.

However, isn't it exciting to have an effective solution to all problems? Can we directly operate the file itself by bypassing the Office COM component? This idea is somewhat exaggerated. in earlier versions of Excel files (probably before Excel 2007), it was almost impossible, but it became feasible after Excel 2007.

In Versions later than Excel 2007, The suffix of the file name is changed to XLSX. In fact, this is also spreadsheetxl. There are three ways to operate on it: write your own parser to analyze the content in the file, which may be troublesome; the second is to use the openxml SDK provided by Microsoft to Operate Excel files, the openxml SDK should have two versions so far, with rich functions. The third is to directly use the self. net Framework 3.0 and later provide new functions to directly operate Excel files.

First, we will introduce what the XLSX file is. You can see the figure below.

The XLSX file itself is a compressed package containing all the data and styles in the Excel file, including some resource files (parts, links, etc ), almost all these files are stored in different folders in the compressed package in XML format. As long as we are familiar with the structure of these XML files and the usage of different folders, we can easily operate XLSX files in the form of zip compressed files. The figure below shows the content after extracting the XLSX file:

Later, I will introduce how to use XLSX files and various applications in different ways. But before that, I would like to talk about the rest Application of Excel. Here are several links to help you understand what an Excel rest application is.

Http://msdn.microsoft.com/en-us/library/ee556820.aspx

Http://www.cnblogs.com/dwang/archive/2010/12/08/1899983.html

Http://msdn.microsoft.com/en-us/library/hh124646.aspx

Excel rest is a service provided by Sharepoint Excel services to access Excel files. We only need a simple URL to obtain data in Excel files, which is very convenient to use. In addition, we can use this URL to modify the data of some cells in Excel, or even obtain or modify charts. For detailed usage and examples, you can refer to msdn, which will be described in detail above.

When Excel rest service is used to return range data, what is the URL if the corresponding worksheet has a custom name? Here are several examples:

• Read the student table, ranging from A1 to G5. Note that % 7C is the colon after escaping.

Http: // <servername>/_ vti_bin/excelrest. aspx/exceltemplates/sampleworkbook.xlsx/model/ranges ('student! A1 % 7cg5 ')? $ Format = html

• Read the table teacher and student from D5 to cg27. Note that spaces in the table name may need to be escaped. The table name is enclosed in two single quotes.

Http: // <servername>/_ vti_bin/excelrest. aspx/exceltemplates/sampleworkbook.xlsx/model/ranges ('''teacher % 20and % 20student ''! D5 % 7ccg27 ')? $ Format = html

• Read the teacher table in the range of A10 cells. Note that the table name is enclosed in two single quotes.

Http: // <servername>/_ vti_bin/excelrest. aspx/exceltemplates/sampleworkbook.xlsx/model/ranges ('''teacher ''! A10 % 7ca10 ')? $ Format = html

in Excel, the custom table name can be enclosed by two single quotes. There are three types of data returned by Excel rest services: HTML, atom, and XML. Note that atom is the RSS format. If the returned data type does not comply with the RSS specification, it cannot be displayed.