As Word mail merge, Excel mark designer can be used to export data to Excel template files. It is convenient for generating reports in bulk. In this post, I will show my method about how to use mark designer to export data to Excel.
At first, we need to prepare data and template. In my example, data shows VIP customers information which is saved in MS Access. There are five items, Name, Gender, Birthday, Email and Country.
For template, create a blank workbook. Then, enter item name in one row and format this row, such as setting text font, borders, cell fill and so on. Below this row, put placeholders. Each placeholder must match item above. At the end of the first placeholder, add (add:styles) to make sure that the following data will be formatted with styles placeholder row. Then format this row as well.
After completing data and template preparation, start with coding.
Step 1: add references. I use Spire.XLS in this method, so add its dll file as reference. Then, add system.data and system.data.oledb.
Step 2: load template I create and initialize the worksheet.
Step 3: connect database which stores data I need and get data.
Step 4: export data with mark designer to template file.
Step 5: save and launch file.
static void Main(string args)
Workbook workbook = new Workbook();
workbook.LoadFromFile(@”D:\VIP Customers.xlsx”, ExcelVersion.Version2010);
Worksheet sheet = workbook.Worksheets;
//Connect DataBase and Get Data
string connString = @”Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=D:\work\My Documents\VIP.mdb;Persist Security Info=False;”;
DataTable dataTable = new DataTable();
using (OleDbConnection conn = new OleDbConnection(connString))
string sql = “select Name,Gender,Birthday,Email,Number,Country from VIP”;
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, conn);
//Export Data with MarkDesigner
//Save and Launch File
workbook.SaveToFile(“Mark Designer.xlsx”, ExcelVersion.Version2010);
Click Here to LEARN MORE about Spire.XLS
Click Here to DOWNLOAD Spire.XLS