Test: Use Spire. XLS to generate automated reports!

Download C # project-7.1 KBIntroductionIn my programming blog, I often compare the performance characteristics of different algorithms or principles. I often output performance logs (such as time-consuming logs) to the console or text files, and then copy them to a workbook for analysis. But recently, I found a new method: I can useSpire. XSLClass Library to generate the final workbook-including all tables and charts! Next I will talk about how to use this class to complete various automated tasks.CaseOur goal is to create a benchmark test program to test three different sorting algorithms. We count the time spent by different algorithms. The following is a simple code:

Abstract class PerfTestBase {public double ElapsedTimeSec {get; protected set;} public string Name {get; protected set;} public abstract void run (int n);} class BubbleSortPerfTest: perfTestBase {public BubbleSortPerfTest () {Name = "Bubble Sort";} public override void run (int n) {// some ElapsedTimeSec = X;} class MergeSortPerfTest: perfTestBase {public MergeSortPerfTest () {Name = "Merge Sort";} public override void run (int n) {// some ElapsedTimeSec = X;} class QuickSortPerfTest: perfTestBase {public QuickSortPerfTest () {Name = "Quick Sort";} public override void run (int n) {// some ElapsedTimeSec = X ;}}

The algorithm is ready. Now we need to run them and add different startup parameters.

List <PerfTestBase> perfTests = new List <PerfTestBase> {new BubbleSortPerfTest (), new MergeSortPerfTest (), new QuickSortPerfTest ()}; // N is increased from 10 to 200, each increase of 10 var res = runAllTests (perfTests, 10,200, 10); printResults (res );

RunAllTests this function can easily perform loop iteration by setting N values and call the. run (N) method. The most interesting part is the printResults method. Which code can automatically generate reports and valuable results?

Simplest solution

At the very beginning, we can print it to the console. You can even copy the results to the workbook in CSV format.

N; Bubble Sort; Merge Sort; Quick Sort;

10;

20; 48;

After a while, when you constantly modify your algorithm code, copying the results will become boring. Of course, there must be a faster and better method. What if we generate an excel file instead of a CSV file? Now we should introduce the Spire. XLS class library.

Introduction to Spire. XLS class library

E-IceBlueSpire. XSLIs a powerful and easy-to-use class library. It also makes it very easy to automate office operations. Many Fortune 500 companies are using this brand, which is well-known and respected.

Brief Introduction: first, addSpire. XLS.Next, you can create, open, modify, and run all computing tasks without installing excel or office.

This type of database is fully compatible with Excel 97/2003, 2007 and 2010.

In addition,Spire. XLSIt can also protect and encrypt files. Even more powerful, it can also convert the format. For example, you can convert your file into a PDF, image, or HTML file.

This solution makes it very easy to create valuable automation programs.

Use Spire. XSL in the code

In our example, we only use 1% of the powerful class library function. Even so, it can save a lot of time to generate reports.

 Basic usage

Add reference:

using Spire.Xls; using Spire.Xls.Charts; 

Four lines of code create a 'hello world' workbook:

Workbook wb = new Workbook(); Worksheet sheet = wb.Worksheets[0]; sheet.Range["A1"].Text = "Hello,World!"; wb.SaveToFile("Sample.xls", ExcelVersion.Version2007); 

The above Code allows us to understand how to use this type of library. You can easily operate workbooks, worksheets, and cells.

Improved Solution

Let's go back to the problem described at the beginning. Our new solution will retain the output to the console, but we will save the result directly as an excel file. In addition, you can create charts. This method saves a lot of time-you do not need to copy and generate charts over and over again... The following code snippet is used to save data:

Worksheet sheet = workbook. worksheets [0]; sheet. name = "Perf Test"; sheet. range ["A1"]. text = "Elapsed Time for sorting... "; sheet. range ["A1"]. style. font. isBold = true; // Column Title: sheet. range ["C3"]. text = "N"; sheet. range ["C3"]. style. font. isBold = true; sheet. range ["C3"]. style. horizontalAlignment = HorizontalAlignType. center; char col = 'D'; foreach (var n in res. map. keys) {sheet. range [col + "3"]. text = n; sheet. range [col + "3"]. style. font. isBold = true; sheet. range [col + "3"]. style. horizontalAlignment = HorizontalAlignType. center; col ++;} // insert data into rows...

The following code generates the chart:

Chart chart = sheet. charts. add (); // set chart data area chart. dataRange = workbook. worksheets [0]. range [range]; chart. seriesDataFromRange = false; // you can specify the chart position. leftColumn = 2; chart. topRow = 2; chart. rightColumn = 12; chart. bottomRow = 30; // chart title chart. chartTitle = "Sorting Time... "; chart. chartTitleArea. isBold = true; chart. chartTitleArea. size = 12 ;//... chart. legend. position = LegendPositionType. bottom; chart. chartType = ExcelChartType. scatterSmoothedLineMarkers;

Look, it's that simple! One thing I particularly like is that we can get a cell or the entire area. Let's see how easy it is to change the style of a cell. Is the final excel file, of course, automatically generated:

There are also charts:

 

Advantages of using Spire. XLS

• There are many ways to automate your tasks-no need to install office
• Good and complete documentation
• Easy to use: you only need to download and modify your code. There is no problem during installation and compilation.
• The free version is sufficient.
• Used by more than 500 enterprises

Summary

In this article, I show that we can use programs to automatically generate performance reports. By using Spire. XLS, programmers can create and operate Excel files without installing the office. This is a very powerful class library and is very easy to use. We can create reports automatically using just a few lines of code. In addition, Spire. DOC or Spire. PDF is also a very good class library.

Notes and links 

• Performance: here we can see their performance comparison tests with OLE. It seems that Spire. XLS is much better than OLE.
• Here is their Quick Start manual
• This type of library is designed for. net, but the same solution can be used in local code. When I need to test, we can create a "bridge" and call the. net class library from the C ++ program. The C ++ program can run perfectly, but all reports are generated by calling Spire. XLS through the. net module.

Original article: http://www.codeproject.com/Articles/783414/Automated-Reports-with-Spire-XLS