Use Aspose. cell Control to generate Excel difficult reports (3) Use Aspose. cell Control to generate Excel difficult reports (1) Use Aspose. cell Control to generate Excel difficult reports (2)

Document directory

• 1. Add basic objects for operations
• 2. generate a report header table
• 3. Enter the table content
• 4. Insert charts and export and open charts

In the previous articles, I introduced the use of the powerful Excel control Apsose. cell. The related articles are as follows:

Use the Aspose. Cell Control to generate difficult Excel reports (1) Use the Aspose. Cell Control to generate difficult Excel reports (2) use the Aspose. Cell Control to merge multiple Excel files

These articles are all about Apose. the Cell control is used to generate various Excel methods. The DataTable or IList operation is directly used to generate an Excel file, and various methods to implement Custom reports through templates are described, and how to merge multiple files.

This article continues to introduce the further use of this control. It also describes how to dynamically generate (without using template files) various cells, as well as the addition of charts and other functions, and introduces the completely custom generated tables, this report is a typical statistical report with illustrated text. Its final style is as follows.

In this report, table data is dynamically generated, and graphs are obtained directly from the chart controls of the form (such as the ZedGraph Chart Control) or PictureBox control, and written into the Excel document.

The main interface of the test program is as follows.

Is it the same as many other Excel operations controls (NPOI and Myxls), Apose. cell also provides the packaging of WorkBook, WorkSheet, Range, and Cell objects. Operations on these objects can basically meet our various complex needs and give us more flexible operations.

1. Add basic objects for operations

            Workbook workbook = new Workbook();            Worksheet worksheet = workbook.Worksheets[0];

These objects are required for Excel operations. If you need to set the generated Excel print preview parameters, perform some attribute settings, as shown below.

Workbook workbook = new Workbook (); Worksheet worksheet = workbook. worksheets [0]; worksheet. pageSetup. orientation = PageOrientationType. landscape; // print the worksheet horizontally. pageSetup. zoom = 100; // enable worksheet in 100% Zoom mode. pageSetup. paperSize = PaperSizeType. paperA4;

As we know, the report is completely manually generated, the report title, and the following lines of Instructions need to be generated, the operation is to merge some cells into a Range, assign values, and change the style. The effect and code shown below are as follows:

Range range; Cell cell; int colSpan = 4 + DeptNameList. count * 2; range = worksheet. cells. createRange (0, 0, 1, colSpan); range. merge (); range. rowHeight = 20; range. style = CreateTitleStyle (workbook); cell = range [0, 0]; cell. putValue ("disease statistics"); range = worksheet. cells. createRange (1, 0, 1, colSpan); range. merge (); range. rowHeight = 15; cell = range [0, 0]; cell. putValue ("A total of 1000 persons are in the selected part range. The query result is as follows:"); range = worksheet. cells. createRange (2, 0, 1, colSpan); range. merge (); range. rowHeight = 15; cell = range [0, 0]; cell. putValue ("from till now, a total of 500 people have a history of illness, with a total of 900 people. The following table lists the cases of illness :");

2. generate a report header table

The most complex part of a report is the generation of a table header. Because it is an irregular table header, You need to operate Cell and Range objects in detail to generate a complex table header (also common.

This is a slow job, so you need to be familiar with Cell and Range, and then assign them different columns to generate a standard header as follows.

Style headStyle = CreateStyle (workbook, true); Style normalStyle = CreateStyle (workbook, false); int startRow = 4; range = worksheet. cells. createRange (startRow, 0, 2, 1); range. merge (); range. style = headStyle; cell = range [0, 0]; cell. putValue ("Serial Number"); cell. style = headStyle; range = worksheet. cells. createRange (startRow, 1, 2, 1); range. merge (); range. style = headStyle; range. columnWidth = 40; cell = range [0, 0]; cell. putValue ("disease name"); cell. style = headStyle; int startCol = 2; foreach (string deptName in DeptNameList) {range = worksheet. cells. createRange (startRow, startCol, 1, 2); range. merge (); range. style = headStyle; cell = range [0, 0]; cell. putValue (deptName); cell = worksheet. cells [startRow + 1, startCol]; cell. putValue ("persons"); cell. style = headStyle; cell = worksheet. cells [startRow + 1, startCol + 1]; cell. putValue ("percentage"); cell. style = headStyle; startCol + = 2;} range = worksheet. cells. createRange (startRow, startCol, 1, 2); range. merge (); range. style = headStyle; cell = range [0, 0]; cell. putValue ("Total"); cell = worksheet. cells [startRow + 1, startCol]; cell. putValue ("persons"); cell. style = headStyle; cell = worksheet. cells [startRow + 1, startCol + 1]; cell. putValue ("percentage"); cell. style = headStyle; # endregion

3. Enter the table content

This is not complicated. You only need to traverse and generate the content to the cell.

// Write data to Excel startRow = startRow + 2; for (int I = 0; I <dt. rows. count; I ++) {startCol = 0; for (int j = 0; j <dt. columns. count; j ++) {DataRow dr = dt. rows [I]; cell = worksheet. cells [startRow, startCol]; cell. putValue (dr [j]); cell. style = normalStyle; startCol ++;} startRow ++ ;}

4. Insert charts and export and open charts

This Apose. the WorkSheet of the Cell Control provides worksheet. pictures. the Add method can be used to Add images, but the images are written in a stream. We can convert the Image object of the chart and create a memory stream. As shown below.

// Write the graph note startRow + = 1; // skip 1 row range = worksheet. cells. createRange (startRow ++, 0, 1, colSpan); range. merge (); range. rowHeight = 15; cell = range [0, 0]; cell. putValue ("bar chart:"); // Insert the image to the Excel file byte [] bytes = ImageHelper. imageToBytes (this. pictureBox1.Image); using (MemoryStream stream = new MemoryStream (bytes) {worksheet. pictures. add (startRow, 0, stream);} // Save the excel file. string saveFile = FileDialogHelper. SaveExcel ("rangecells.xls", "C: \"); if (! String. IsNullOrEmpty (saveFile) {workbook. Save (saveFile); if (MessageUtil. ShowYesNoAndTips ("saved successfully, do you want to open the file? ") = System. Windows. Forms. DialogResult. Yes) {System. Diagnostics. Process. Start (saveFile );}}

So far, based on Apose. another operation of Cell Custom reports is also fully implemented. To implement this simple example, it takes a lot of time to use it in projects, however, you can directly use their basic objects to generate such complex and Custom reports in the future;

For some common reports, you can use custom templates to generate them and then bind them to the data source. For example, if you are a two-dimensional table or an ILIst set, exporting Excel is easier. The above two types can be operated directly by using the encapsulated AsposeExcelTools. This generic class library can save the complexity of coding and improve efficiency.