Use DSO framer OCX + Excel in B/S to solve complicated report problems

In actual enterprise applications, it is easy to handle complicated reports. Because reports are generally relatively complex, it is inconvenient to use common forms + fields, how can we make it easier?

We know that report data is usually processed by complicated processing of the data in the database. These processing operations directly interact with the data itself and have little to do with our system logic. Therefore, it can be stored in the stored procedure without much concern for logical processing. In this way, our stored procedure is equivalent to a black box. If you need report data, you can directly run it by passing in the relevant parameters. It is responsible for returning the expected dataset.

Although the problem of data reading has been solved, how can this problem be presented? We know that it is not easy to present complex multi-dimensional data in the bsstructure. Moreover, in general, reports must not only be displayed, but also be modified and archived to interact with users, if we use the traditional table + field method, we can imagine how much work is needed. Experience tells us that Microsoft Excel is widely used and has unique convenience in data processing. Why not use Excel to display data? But the problem comes again, excel is faced with two problems. On the one hand, CS is processed in the bsstructure.ProgramIt is not very convenient. On the other hand, it is how to fill the data in Excel. So solve these problems one by one ^_^.

To integrate these problems, the following figure shows a solution:

This solution is described as follows:

First question (DSO framer problems ):

Microsoft actually made a good ActiveX Control for us. The name of DSO framer is probably the Document Object framework, which means that our processing programs can be loaded based on our document objects, this control can open frequently-used office documents. The specific supported documents must refer to the Office components installed on the customer's machine. The other software only supports frequently-used office documents, such as Visio, you can take a look at the details. Isn't this a good free tool? We should try it if you are OK about Microsoft.

Use of this control: This control not only provides full source code (vc6), but also provides a demo, we need to use the demo in reference, but there are several points to note:

1. to test the methods in ActiveX controls, you can use. net ActiveX control test container tool, which is available in the tool menu. For example, you can find the answer here to shield menu items.

2. the latest version of this control should be version 1.2. This version still has a problem, mainly because the function saved to the server cannot be unlocked and thus cannot be saved to the server. Don't worry, I can provide the modified version. This is the solution I found in the circle, and I changed the original file menu in English to Chinese. I will provide the source code later.

3. you must open the file in different ways when creating and editing the file. when creating the file, that is, when there is no file on the disk, we can save the file, however, when we open the file for the second time, we should open the file in read-only mode, because the file will not be locked in read-only mode, there is no problem that the file cannot be overwritten because the file is locked, so that we will not cause conflicts when saving the file.

If you have any questions about this control, please reply below.

 

Second question (about operating Excel files on the server)

To operate Excel files on the server, of course the server must install Office Excel. After installing this software, we can see that a COM component has been registered, this component is Microsoft Excel 11.0 object labrary (taking office2003 as an example). After adding a reference, it is how to process the Excel document.

Only the Excel documents can be processed and researched separately, because this problem is more complicated, involving com references, filling technology, process elimination, and so on. I will not elaborate here, let's just talk about my general ideas.

As we can know, there are a wide variety of report formats. We must make corresponding templates to adapt to various report styles, so we must make a matching template for each report. How to fill it out? We can note that the filling template can be processed using the dataset returned after the stored procedure is executed. we can fill in the specified datatable for the specified place, this requires us to provide the fill coordinate item in our configuration program, so we can fill the datatable returned by the stored procedure according to the coordinate order, and the configuration needs to specify the coordinates of the start point.

Note that when filling in an Excel file, the system opens an Excel process to process the Excel file. After processing, the process does not exit automatically, when we need to process the second time, the system will open up new processes, resulting in more and more processes and eventually consume the system resources. There are two solutions: one is to close the process immediately after filling, which needs to be forcibly closed. The second method is to create a process pool. When necessary, retrieve the existing process from the pool. After processing, you only need to return the process to the process pool. The first method can ensure the effective use of system resources, while the second method can improve the execution efficiency. Of course, it is also a complicated processing method. The two methods I have provided are optional, I don't know if there is any better way.

The demo provides the following functions:

A) dataset directly fills in the Excel template. If the corresponding filled coordinate array is specified, it will automatically fill in the corresponding position.

B) pagination is supported to ensure automatic paging and automatic paging information appears when there is a large amount of data.

C) The filling speed should be optimized as much as possible. The speed should be acceptable if not especially big data.

D) supports Excel process pools to effectively improve filling speed

E) You can easily learn how to use it by running the demo.

F) This demo is well-developed based on the demo provided by bloggers.

This is not detailed enough. There are too many things to talk about. If you are interested, you can talk more.

[Download dsoframer to modify the version]

[Download the improved version of excelhelper]