How to Use Mark Desinger in Excel to Generate Table with C#

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.

Template

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.

Coding:

using System;
using System.Data;
using System.Data.OleDb;
using Spire.Xls;

namespace MarkDesigner
{
    class ExcelMD
    {
        static void Main(string[] args)
        {

            //Load File
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\VIP Customers.xlsx”, ExcelVersion.Version2010);
            Worksheet sheet = workbook.Worksheets[0];

            //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))
            {
                conn.Open();
                string sql = “select Name,Gender,Birthday,Email,Number,Country from VIP”;
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, conn);
                dataAdapter.Fill(dataTable);
            }

            //Export Data with MarkDesigner
            workbook.MarkerDesigner.AddDataTable(“VIP”, dataTable);
            workbook.MarkerDesigner.Apply();

            //Save and Launch File
            workbook.SaveToFile(“Mark Designer.xlsx”, ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Result:

____________________________________________________________________________________

Click Here to LEARN MORE about Spire.XLS

Click Here to DOWNLOAD Spire.XLS

Advertisements

Author: janewdaisy

.NET Program Beginner. Share methods about how to use C#/VB.NET to export data, operate Word, Excel, PDF and other useful skills.

4 thoughts on “How to Use Mark Desinger in Excel to Generate Table with C#”

  1. A actually great submit by you my friend. We have bookmarked this page and will appear back following several days to examine for any new posts that you simply make.

    1. Thanks very much. Your interest on my blog encourages me. However, I don’t know what the problem about my rss is. I will try to find the problem as soon as possible. Thanks again.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s