Using Aspose.cell controls to generate Excel difficult reports (iii)

In the previous articles, we introduced the use of Apsose.cell, a powerful Excel manipulation control, related to the following articles:

Using Aspose.cell controls to generate Excel difficult reports (i) using Aspose.cell controls to generate Excel difficult reports (ii) merging multiple Excel files using the Aspose.cell control

These articles, all of Apose.cell this control to generate a variety of Excel, the way to directly put a DataTable or IList to generate Excel, the way to implement custom reports by template mode, and the way to merge multiple files is introduced.

This article continues to introduce the control to further use, but also mainly describes how to dynamically generate (without using template files) various cells, as well as the addition of charts and other functions, describes the generated table fully customizable, this report when a typical illustrated statistical report, its final style is as follows.

In this report, the tabular data is generated dynamically, and the graph is obtained directly from the form's chart control (such as the Zedgraph Chart control) or the PictureBox control, written to the Excel document.

The main interface of the test program is shown below.

As with many other Excel manipulation controls (Npoi, Myxls), Apose.cell also provides a wrapper for workbook, WorkSheet, Range, cell, and manipulating these objects to meet our complex needs, Give us a more flexible operation.

1. Adding basic objects for operation

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

These few objects are required to manipulate Excel, and if you need to set the parameters for the generated Excel print preview, make some property settings as follows.

            Workbook Workbook = new Workbook ();            Worksheet Worksheet = workbook. Worksheets[0];            Worksheet. pagesetup.orientation = pageorientationtype.landscape;//transverse print            worksheet. Pagesetup.zoom = 100;//Opens worksheet in 100% zoom mode            . Pagesetup.papersize = PAPERSIZETYPE.PAPERA4;

We know that because the report is completely hand-generated, the title of the report, and the following lines of text, but also needs to be generated, the operation is actually to merge some cells into an area (range), and then assign the value, change the style can be, as shown below the effect and code

            Range range; Cell cell;            int ColSpan = 4 + deptnamelist.count * 2;            Range = worksheet. Cells.createrange (0, 0, 1, colSpan);            Range. Merge ();            Range. RowHeight =;            Range. Style = Createtitlestyle (workbook);            Cell = range[0, 0];            Cell. Putvalue ("Statistics of Illness");            Range = worksheet. Cells.createrange (1, 0, 1, colSpan);            Range. Merge ();            Range. RowHeight =;            Cell = range[0, 0];            Cell. Putvalue ("Within the selected department, a total of 1000 persons, the results of the query are as follows:");            Range = worksheet. Cells.createrange (2, 0, 1, colSpan);            Range. Merge ();            Range. RowHeight =;            Cell = range[0, 0];            Cell. Putvalue ("Since 2007-1-1 to now, a total of 500 people have a history of disease, the cumulative number of 900, the prevalence of the following table:");

2. Generate Report Header table

The most complex part of a report is the generation of a header, because it is an irregular header, so you need to manipulate the cell and Range objects very carefully to achieve complex header (and very common) builds.

This is a slow work out deliberately, need to be familiar with the cell and range, and then assign them a different column can be generated by a standard below the table header.

            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 ("Number of passengers"); 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 ("Number of passengers"); Cell.            Style = Headstyle; Cell = worksheet.            Cells[startrow + 1, Startcol + 1]; Cell.            Putvalue ("percentage"); Cell.             Style = Headstyle; #endregion

3. Fill in the contents of the form

This is not complicated, just traverse and then 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 chart and Export open operation

The worksheet of this Apose.cell control provides the Worksheet.Pictures.Add method to add a picture, but the picture is written by stream, and we convert the image object of the chart to create a memory stream. as shown below.

//Write graph note startrow + = 1;//Skip 1 Lines range = worksheet.            Cells.createrange (startrow++, 0, 1, colSpan); Range.            Merge (); Range.            RowHeight = 15;            Cell = range[0, 0]; Cell.            Putvalue ("as shown in the column chart:");            Insert picture into Excel inside 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 ("Save succeeded, do you want to open the file?") ") = = System.Windows.Forms.DialogResult.Yes) {System.Diagnostics.Process.Start (saveFile                ); }            }

At this point, another operation based on the Apose.cell custom report has been implemented, in order to achieve this simple example, in order to use in the project, it took a lot of time, but later for the generation of such complex and custom reports, can directly use their underlying objects to operate it;

If you have a regular report, you can build it by using a custom template, and then bind the data source; If you are a two-dimensional table, or an IList collection, it is easier to export Excel. Both of the above can be directly using the encapsulated Asposeexceltools to operate, this common class library, you can save every time to write code cumbersome, improve efficiency.

Using Aspose.cell controls to generate Excel difficult reports (iii)