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.png
 
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.
          
load.png
 
2.    Connect to the Northwind database and get the data from the Products table.
 
connect.png
 
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.
     
    md.png
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. 
 
chart1.png
         
chart2.png
 
 5.    Save and launch the file:

save.png
 
Coding
  1. using System.Data;  
  2. using System.Data.SqlClient;  
  3.   
  4. using Spire.Xls;  
  5. using Spire.Xls.Charts;  
  6.   
  7. namespace ExcelMarkDesigner  
  8. {  
  9.     class Program  
  10.     {  
  11.         static void Main(string[] args)  
  12.         {  
  13.             //Load File  
  14.             Workbook workbook = new Workbook();  
  15.             workbook.LoadFromFile(@"..\..\ProductInfo.xlsx", ExcelVersion.Version2010);  
  16.             Worksheet sheet1 = workbook.Worksheets[0];  
  17.             Worksheet sheet2 = workbook.Worksheets[1];  
  18.   
  19.             //Connect DataBase and Get Data in Product  
  20.             string connString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True";  
  21.             string orderSql = "SELECT ProductID,ProductName,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel FROM products";  
  22.   
  23.             DataTable product = new DataTable();  
  24.             using (SqlConnection conn = new SqlConnection(connString))  
  25.             {  
  26.                 conn.Open();  
  27.                 SqlDataAdapter sda = new SqlDataAdapter(orderSql, conn);  
  28.                 sda.Fill(product);  
  29.             }  
  30.   
  31.             //Export Data with MarkDesigner  
  32.             workbook.MarkerDesigner.AddDataTable("Product", product);  
  33.             workbook.MarkerDesigner.Apply();  
  34.   
  35.             //Generate Chart in Sheet2  
  36.             Chart chart = sheet2.Charts[0];  
  37.             chart.DataRange = sheet1.Range["E8:F30"];  
  38.             chart.SeriesDataFromRange = false;  
  39.   
  40.             //Chart title  
  41.             chart.ChartTitle = "Product Information";  
  42.             chart.ChartTitleArea.IsBold = true;  
  43.             chart.ChartTitleArea.Size = 16;  
  44.   
  45.             //Category Axis Format  
  46.             chart.PrimaryCategoryAxis.Title = "ProductID";  
  47.             chart.PrimaryCategoryAxis.Font.IsBold = true;  
  48.             chart.PrimaryCategoryAxis.TitleArea.IsBold = true;  
  49.   
  50.             //Value Axis Format  
  51.             chart.PrimaryValueAxis.Title = "Units";  
  52.             chart.PrimaryValueAxis.HasMajorGridLines = false;  
  53.             chart.PrimaryValueAxis.MinValue = 0;  
  54.             chart.PrimaryValueAxis.TitleArea.IsBold = true;  
  55.             chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;  
  56.   
  57.             //Get Chart DataSource and Set Column Series Name  
  58.             ChartSerie cs1 = chart.Series[0];  
  59.             cs1.CategoryLabels = sheet1.Range["A8:A30"];  
  60.             cs1.Name = "UnitsInStock";  
  61.             chart.Series[1].Name = "UnitsOnOrder";  
  62.   
  63.             //Legend Positon  
  64.             chart.Legend.Position = LegendPositionType.Right;  
  65.   
  66.             //Save and Launch File  
  67.             workbook.SaveToFile(@"..\..\sample.xlsx", ExcelVersion.Version2010);  
  68.             System.Diagnostics.Process.Start(@"..\..\sample.xlsx");  
  69.         }  
  70.     }  
After running, we can get a report as follows.
 
Data Sheet

 ProductInfo.png

 
Chart Sheet
 
chart.png
 

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.