C# - Create Excel Report (Chart Contained) With Mark Designer

Introduction

 

Similar to Word mail merge, Excel Mark Designer is used to export data from a database to a specific template file. Using it, reports can be printed in bulk at one time and people can save substantial time exporting and formatting data using Mark Designer. 

 

This article focuses on how to use the Mark Designer to generate a report which includes two sheets. The first one shows data from a database and the second one presents a column chart about data information in the first sheet.

 

Data Preparation

 

In my example, I get data from the Northwind database, which is provided by Microsoft. You can download it from here: http://www.microsoft.com/download/en/details.aspx?id=23654.

 

First, I select the Products table in the Northwind database. It presents product information according to Product ID. Then, choose which columns to export, including Product ID, Product Name, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, and RecordLevel.

 

The chart data sources are Product ID, UnitsInStock, and UnitsOnOrder. By presentation of information about products amounts in stock and on order, this chart can show sales information by Product ID.

 

Since there is such a large amount of data in this table, I will just export one part of it.

 

Template

 

Before generating the report, we need to create a template, as in:

1. Data Sheet Title. In the first sheet, add the title Product Information on the top of the first sheet. Merge cells from A1 to G4. Set the font style and color. Then add the background for the title.
2. Column Title. In the Data Preparation part, I have shown the columns I will export to Excel. Add these column titles at the seventh row in order. Then format cell styles for column title.
3. Placeholder. Add a placeholder under the column title row and set the format for this row. The placeholders offer a location for data to be exported. The placeholder name in every column matches the column's title. The placeholder in the first column has additional characters (add:styles), which is used to define the style for all data.
4. Value Type. If there are date, currency, or percentage information contained in the data, set corresponding value types for them.
5. Chart. Inset a blank column chart in the second sheet. Set chart border and chart area background color.
6. Worksheet Name. To distinguish which contents worksheets contain, add a worksheet name. Name first as Product Info and second as Units Chart.

 

Template

 

Now, the template is done, we can connect with the database to export the data and generate a chart.

 

Procedure

 

In this method, I use the component: Spire.XLS. Therefore, after creating my project, I add its DLL file as a  reference first.

 

1.    Load the template file and initialize the worksheets Product Info and Units Chart.

          

 

2.    Connect to the Northwind database and get the data from the Products table.

 

 

3.    Use the Mark Designer function to export data in the products table into the Product Info sheet in the template file.

• Data in the Products table will be inserted into columns occupied by corresponding placeholders.
• Placeholder column is the beginning row to insert data.
   

4.    Select the chart data sources (range in the exported table). Generate the chart and add the chart title, value axis title, and category axis title for the chart. 

 

         

 

 5.    Save and launch the file:

 

Coding

using System.Data;
using System.Data.SqlClient;

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

namespace ExcelMarkDesigner
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load File
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\ProductInfo.xlsx", ExcelVersion.Version2010);
            Worksheet sheet1 = workbook.Worksheets[0];
            Worksheet sheet2 = workbook.Worksheets[1];

            //Connect DataBase and Get Data in Product
            string connString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True";
            string orderSql = "SELECT ProductID,ProductName,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel FROM products";

            DataTable product = new DataTable();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter(orderSql, conn);
                sda.Fill(product);
            }

            //Export Data with MarkDesigner
            workbook.MarkerDesigner.AddDataTable("Product", product);
            workbook.MarkerDesigner.Apply();

            //Generate Chart in Sheet2
            Chart chart = sheet2.Charts[0];
            chart.DataRange = sheet1.Range["E8:F30"];
            chart.SeriesDataFromRange = false;

            //Chart title
            chart.ChartTitle = "Product Information";
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size = 16;

            //Category Axis Format
            chart.PrimaryCategoryAxis.Title = "ProductID";
            chart.PrimaryCategoryAxis.Font.IsBold = true;
            chart.PrimaryCategoryAxis.TitleArea.IsBold = true;

            //Value Axis Format
            chart.PrimaryValueAxis.Title = "Units";
            chart.PrimaryValueAxis.HasMajorGridLines = false;
            chart.PrimaryValueAxis.MinValue = 0;
            chart.PrimaryValueAxis.TitleArea.IsBold = true;
            chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;

            //Get Chart DataSource and Set Column Series Name
            ChartSerie cs1 = chart.Series[0];
            cs1.CategoryLabels = sheet1.Range["A8:A30"];
            cs1.Name = "UnitsInStock";
            chart.Series[1].Name = "UnitsOnOrder";

            //Legend Positon
            chart.Legend.Position = LegendPositionType.Right;

            //Save and Launch File
            workbook.SaveToFile(@"..\..\sample.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(@"..\..\sample.xlsx");
        }
    }
}

After running, we can get a report as follows.

 

Data Sheet

 

 

Chart Sheet

 

 

Conclusion

 

This article shows how to use Mark Designer to export data to Excel and generate charts from portions of data in multiple worksheets to form a complete report.