Web report development technology Topic 5: the shadows of Excel

Since Excel beat Lotus N years ago, it has dominated the spreadsheet software market. All table software cannot help but take full account of Excel and look for functional points that excel cannot take care. All table software must be considered to be compatible with Excel and be able to import and export Excel files. The actual result is that, as long as the software can be stained with the form, it is difficult to survive in the shadows of Excel. This is especially true for the Web report tool market in China. It is the best way to import and export Excel files.

In short, whether or not an Excel file can be imported and exported without losing sight is a major event that determines the success or failure of the Web report tool software. It is careless.

To import and export Excel files, you can use the following methods:

1Only export data, not export styles

2Call the COM component in Excel to export

3Use a pure C # program to read and write Excel files at the binary level

First approachIt is the simplest, but has the weakest function, and basically cannot meet user requirements. You cannot export styles.

Second approachI think it is one of the most widely used. Net platforms in China. The principle is to use a group of COM components in MS Excel software to directly control Excel files. Therefore, this approach relies heavily on Excel software. Using this method also seems like Microsoft's trap. In. net programs (such as C #), using this method to import and export Excel files has the following weaknesses:

AInstall MS Excel software.

BRequires the installation of some versions of MS Excel software, or the uniformity of Excel software versions.

CMS Excel COM components a large number of, memory and Other Resources consume too much.

DMS Excel is designed as a spreadsheet software with an interface, so it is much slower to run in the background.

EBecause. net program calls excel through COM components, while. the. NET program is a managed code that calls an unmanaged COM component (for calls.. Net) will increase some additional overhead.

FIf you use the Excel software on the server, the Excel process on the server cannot be completely disabled. I once saw a lot of articles on killing the Excel process on the server with tricks in the blog garden, but I felt that this problem could not be completely solved, in this way, I decided not to use this method to solve the problem of importing and exporting Excel files in Table E.

Third ApproachThis method does not rely on Excel software, that is, the client and server do not need to install Excel software, directly use. net program (such as C # program) to read and write Excel files, that is, first read the Excel files into the memory as a binary file stream, then, analyze the data by byte according to the Excel file format standard. The advantage of this method is to avoid the weakness of the second method, and the dependency on Excel software is very small. However, because the structure of an Excel file is complex, you need to use the code to analyze the Excel file from the beginning. The workload is very large. Unlike the Java platform, a general and open-source poi package can be used directly to solve this problem. On the. NET platform, in addition to some commercial DLL files outside of China to solve this problem, it seems that this kind of thing cannot be seen in China. I have also tried some foreign countries and found that most of them have Chinese Character problems.

Later, I thought about the Excel import/export function. Most of them are used in report tools (such as Crystal Reports) or the second method (that is, to call the COM component in Excel ). The third approach is too heavy, so everyone will avoid it. This is the current national condition. But Table E cannot avoid it. What should I do? The only difference is that the report designer, which is just a page-only one, is stuck.

Hosts file (for your convenience, click here to download this file ). Let's take a look at the beginning of this file ,:

 

 

I was so happy to find this file. With it, I can at least do it. Another look, over 200 pages, it's really awkward to turn it into a program. After reading a few more articles, I found that the formats are similar to the following:

 

It is nothing more than introducing the meaning of each byte.

Finally, the meaning of the byte is converted into an object, that is, an Excel file operation class library is obtained based on the binary stream of an Excel file. All of them are written in pure C # statements. The generated ebexcel. dll file is not large, and only 200 KB is used. There are a lot of source code, with nearly 200. CS files. As shown in:

 

This ebexcel. DLL provides a powerful and comprehensive class library for operating Excel files. It can be used to read and write Excel files of all versions without relying on the Excel software, you can read and write data, formulas, borders, styles, and images in an Excel file. This method has a small memory size, fast speed, easy integration, and clear function positioning. It is not only used in Web report tools, but can also be conveniently used in various scenarios where Excel files need to be read and written, for example, you need to automatically extract data from a large number of Excel files and save it to the physical table of the database.

With ebexcel. dll, e-tables can be easily imported into an Excel file without losing sight, which converts an Excel file into a report file in e-table format. You can also export the report calculation result to an Excel file. The most important thing is that you do not need to install the Excel software on the client or server. To use the Excel file reading and writing function, you only need to copy the ebexcel. dll file to the bin directory. Ebexcel. dll is included in the e-Table trial package on the Beijing Fangcheng website.

I think of fellow citizens who are still suffering from this problem, and I also thought about applying ebexcel. DLL is free and open-source for everyone, but considering the free and open-source experience of Zhou fangyong (net name: Changjiang Branch), let's wait!

To be continued...

Related Links
[Original] Web report development technology Topic 1: Sequence Number Problem
[Original] Web report development technology Topic 2: report tool core-dataset Transformation
Web report development technology Topic 3: irregular grouping and cross-row group operations
Web report development technology Topic 4: The story behind the report designer
Tabulation Efficiency Analysis of Web report tools