Introduction
As Word mail Merge, Excel mark designer is used to export data from database to a specific template file. Because
of it, reports can be printed in bulk at one time and people can save much time
on exporting and formatting data by 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 database and the second one presents 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.
Firstly, I select the Products table in Northwind
databse. 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. Through presenting information of
products amounts in stock and on order, this chart can show sales information
according to Product ID.
Because there is too large amount of data in this
table, I will just export one part of them.
Template
Before generating the report, we need to create a
template.
- Data Sheet Title.
In first sheet, add title Product Information on the top of the first
sheet. Merge cells from A1 to G4. Set font style and color. Then add
background for title.
- Column Title. In 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.
- Placeholder. Add
placeholder under the column title row and set format for this row. The
placeholders offer location for data which will be exported. Placeholder
name in every column matches columns title. The placeholder in first column
have additional characters (add:styles), which is used to define style for
all data.
- Value Type. If there are date,
currency or percentage information contained in data, set corresponding
value types for them.
- Chart. Inset a blank column chart
in the second sheet. Set chart border and chart area background color.
- Worksheet Name. In order to
distinguish which contents worksheets contain, add worksheet name. Name
first as Product Info and second as Units Chart.
Template
Now, the template is completed, we can connect with the database to export the data and generate a chart.
Steps
In this method, I use a component:
Spire.XLS. Therefore, after creating my project, I add its DLL file as a reference firstly.
1. Load the template file and initialize
worksheet Product Info and Units Chart.
2. Connect to the Northwind database and get the data in 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 to
columns where corresponding placeholders occupies.
- Placeholder column is the beginning row to
insert data.
4. Select the chart data sources (range in
exported table). Generate chart and add chart title, value axis title and
category axis title for chart.
5. Save and launch 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 following.
Data Sheet
Chart Sheet
Conclusion
This article shows how to use mark designer to
export data to Excel and generate chart according to parts of data in different
worksheets to form a complete report.
Good News
Spire.XLS Store, E-iceblue luanches a Christmas Promation(5% - 25% off). Click here to learn more.
Merry Christmas and Happy New Year.