Share on twitter
Share on facebook
Share on linkedin
Export data to predefined Excel template in C-sharp

Export Data to a Predefined Excel Template in C#

Excel templates are spreadsheets that have predesigned formats. Data exported to such spreadsheets will be formatted according to the template files. In this blog, we are going to see how to export data to a predefined Excel template in C# using a template marker in Essential XlsIO.

Template marker

template marker is a special marker symbol used in the worksheet cells of an Excel template file. It binds to the class objects and fills multiple records from various data sources such as data tables, collection objects, arrays, and nested data into a worksheet.

For example, the following screenshot shows how to use the marker variable. The symbol “%” is used as marker for the class SalesList and its properties SalesPerson and SalesJulyDec. The template file has some cell formatting.

Input template with markers
Input template with markers

Syntax

Each marker starts with a prefix character (by default, “%”). The marker is followed by a variable name and then properties, which are delimited by a character (by default, “;”.)

%<MarkerVariable>.<Property>

For example: %Customers.CompanyName

Where, “Customers” is marker variable name and CompanyName is the property name

For the previous Excel template with markers applied, the exported data will be as shown in this screenshot.

Output file with data exported using markers
Output file with data exported using markers

It is that simple to export data to a predefined Excel template. In this sample, I just read an XML file, prepared a data table, and exported the data with three lines of code. Of course, you need to write a few lines of other code to create an Excel workbook. The code example is given here.

using Syncfusion.XlsIO;
using System.Data;
using System.IO;
using System.Reflection;

namespace TemplateMarker
{
    class Program
    {
        static void Main(string[] args)
        {
            //Code to read XML data to create a DataTable
            Assembly assembly = typeof(Program).GetTypeInfo().Assembly;
            Stream dataStream = assembly.GetManifestResourceStream("TemplateMarker.Data.customers.xml");
            DataSet customersDataSet = new DataSet();
            customersDataSet.ReadXml(dataStream, XmlReadMode.ReadSchema);
            DataTable northwindDt = customersDataSet.Tables[0];

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2016;

                //Open an existing spreadsheet, which will be used as a template for generating the new spreadsheet.
                //After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
                IWorkbook workbook;

                //Open existing Excel template
                Stream cfFileStream = assembly.GetManifestResourceStream("TemplateMarker.Data.TemplateMarker.xlsx");
                workbook = excelEngine.Excel.Workbooks.Open(cfFileStream);

                //The first worksheet in the workbook is accessed.
                IWorksheet worksheet = workbook.Worksheets[0];

                //Create Template Marker processor.
                //Apply the marker to export data from datatable to worksheet.
                ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
                marker.AddVariable("SalesList", northwindDt);
                marker.ApplyMarkers();

                //Saving and closing the workbook
                workbook.SaveAs("TemplateMarkerOutput.xlsx");

                //Close the workbook
                workbook.Close();
            }
        }
    }
}

Note: You can change the marker prefix and delimiter characters with the MarkerPrefix and ArgumentSeparator properties of the ITemplateMarkersProcessor instance respectively.

How does a template marker work?

Markers are applied in an Excel template to the required cells. This includes the data source name and the field name of interest. During data binding, a search is conducted for the data source name and the field name in the Excel workbook and the corresponding data from the data source is bound to the marker. Cells in the worksheet can be filled with a single data source or with multiple records. The format of this data can be changed using the arguments of the markers. Let me explain what I mean when I say arguments, and what types there are.

What is an argument in a template marker?

The syntax of a template marker has three parts: marker variable, property, and arguments. Arguments are separated by semi colon (;). The following are the arguments are supported in template markers:

Let’s see these arguments in brief with screenshots that illustrate the output.

vertical

The vertical argument specified in a template marker fills data in vertically. By default, the data-fill direction is vertical. So, it is not necessary to give a vertical argument.

The following screenshot shows the input template with a vertical argument.

Input template with vertical argument
Input template with vertical argument

The next screenshot shows the output with a vertical argument.

Data filled with vertical argument
Data filled with vertical argument

horizontal

The horizontal argument specified in a template marker fills data in horizontally. The following screenshot shows the input template with a horizontal argument.

Input template with horizontal marker
Input template with horizontal marker

The next screenshot shows the output with a horizontal argument.

Data filled with horizontal argument
Data filled with horizontal argument

insert

The insert argument inserts new rows or columns while exporting. This option is useful if data is to be exported between other rows.

For example, in the following screenshot there is content in the seventh row. But we need to export data from the fifth row. Without the insert argument, if data is exported, the content in the seventh row will overlap. To avoid that, the insert argument exports data by inserting new rows, which moves the content from the seventh row to the bottom. By default, rows will be inserted vertically.

Input template with insert argument
Input template with insert argument

In addition, the insert argument enables two parameters copystyles and copymerges. These parameters will copy existing cells styles and merged cells into the new rows that are being inserted while exporting.

The following screenshot shows the output after export with the insert argument along with the copystyles and copymerges parameters.

Output of data exported with row insertion
Output of data exported with row insertion

Export data horizontally with column insertion

You can also insert columns if data is being exported horizontally. The following screenshot shows the input template with insert and horizontal arguments.

Input template with insert and horizontal arguments
Input template with insert and horizontal arguments
Output of data exported horizontally and with column insertion
Output of data exported horizontally and with column insertion

jump

If you want to export data to alternative rows or columns, or by skipping certain rows or columns, the jump argument will be useful.

In this case, the cell references must be added to the jump argument, and can be a relative reference or an absolute reference. You must know the differences between these references, because we will be using a relative reference in the following sections of the article.

Relative ReferenceAbsolute Reference
· RC – Current row and current column

· RC[1] – Current row and next column

· R[1]C – Next row and current column

·R[-1]C – Previous row and current column

· RC[-1] – Current row and previous column

i) R2C1 – Second row and first column

 

The data will be exported to the rows and columns according to the row and column indexes.The data will be exported only for the specified cells.

The following screenshot shows the input template with the jump argument and with relative reference, which exports data into alternative rows. Initially, the data will be exported to the fourth row, then to the sixth row, and then to the eighth row, etc.  There won’t be any change in the column level, as there is no index given to the jump.

Input template with jump argument
Input template with jump argument

The output of data export with the jump argument.

Output file with jump argument
Output file with jump argument

Note: It is mandatory to provide a jump argument to all the rows and columns. If not provided, data in that row or column will be exported as usual.

copyrange

The copyrange argument copies specified cells into all other rows while exporting. In this case, the data in the cells to be copied can be added in the template itself. So, while exporting the data, these cells will be copied to the rows that are exported.

The next screenshot shows the input template with data in the cells D4 and E4. The copyrange argument at cell A4 has the relative reference to copy the fourth and fifth column values of the current row into other rows.

Note: To learn more about relative and absolute references, you can refer to the table mentioned in the jump argument section.

Input template with CopyRange argument
Input template with CopyRange argument

The following screenshot shows the output, where the data in the cells D4 and E4 are copied to all the rows.

Output file with CopyRange argument
Output file with CopyRange argument

size, position, and fittocell

There are ways to export data with images. While exporting with images, set the image size to fit to the cell, to a specified size, or at a position. To fulfill that requirement, XlsIO provides these arguments:

  • size
  • position
  • fittocell
size

The size argument specified in a template marker applies to images that are exported to worksheets. The actual size of images that are exported may differ. To export them into cells to fit for clear visualization, this argument helps in setting the height and width of images.

Here, the height parameter is optional. The value of width is applied when height is not specified.

The following screenshot shows the input template with the size argument at the cell A4.

Input template with size argument
Input template with size argument

The next screenshot shows the output with images exported in column A with the specified size.

Data filled with size argument
Data filled with size argument
position

The position argument sets alignment of images to the left, center, right, top, middle, or bottom while exporting. If a position is not given to the images column, it will be considered top-left by default.

The following screenshot shows the input template with the position argument at the cell A4.

Input template with position argument
Input template with position argument

The next screenshot shows the output with images exported in column A as right aligned.

Data filled with position argument
Data filled with position argument
fittocell

The fittocell argument fits images within cells while exporting. Here, the image height and width are not necessary to include.

The following screenshot shows the input template with the fittocell argument.

Input template with fittocell argument
Input template with fittocell argument

The next screenshot shows the output with images fit to the cells.

Data filled with fittocell argument
Data filled with fittocell argument

Advanced features of template markers

We have seen how to use template markers and their arguments to customize data export to any desired layout. This is not all template markers can do. Template markers have more advanced features to highlight, where you can export data with:

Let’s see these advanced features one by one with code examples.

Export data to Excel template with formulas

Be it the simple addition of a long row of numbers, a sales quote, or complex financial modeling, formulas play a vital role in computing data. You can also use formulas to build columns with your exported data.

I am going to have a column that will add two of my exported columns. Let’s see the steps to achieve this.

Step 1: Add the following argument to any column that is a part of the computation. The RC4 here refers to the range (current row and fourth column) where the formula is given and to be repeated in subsequent rows.

%NumbersTable.Column2;copyrange:RC4
Input template with markers to import data with images using template markers
Input template with markers to import data with images using template markers

Step 2: All set! Now export the data.

//Create Template Marker Processor
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

//Add marker variable
marker.AddVariable("NumbersTable", GetTable());

//Process the markers in the template
marker.ApplyMarkers();

Now, after exporting, the final output will be like this one.

Data exported into Excel worksheet with formulas
Data exported into Excel worksheet with formulas

A complete working example along with the input file can be downloaded from export-data-with-formulas.zip.

If you are interested in learning more about how to work with formulas using XlsIO, you can go through the working with formulas documentation.

Export data to Excel template with images

You can bind image data from your data sources. You can not only import images but their specified sizes and positions, too.

Let’s see the steps to achieve this.

Step 1: Add a marker variable with a bit of additional information, like size and position of image.

Input template with markers to export data with images using template markers
Input template with markers to export data with images using template markers

Step 2: Now export the data.

//Create Template Marker Processor
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

//Get first 5 items in the collection and bind them to the marker
marker.AddVariable("SalesList", GetCustomerAsObjects().Take<Customer>(5).ToArray());

//Process the markers in the template
marker.ApplyMarkers();

Here is the exported data in a neatly formatted Excel worksheet using a template marker.

Data exported with images in a formatted layout using template markers
Data exported with images in a formatted layout using template markers

A complete working example along with the input file can be downloaded from export-data-with-images.zip.

Go through the documentation to learn more on importing data with images using template markers in XlsIO.

Export data to Excel template with conditional formatting

Conditional formatting provides another way to figure out patterns in data, which is difficult from examining the raw information, similar to charts or sparklines.

For example, I have a sales report with two sets of six-months’ sales figures, Jan-June and July-Dec, in two different columns. It would be difficult to compare the sales for different sales reps without analysis. We can add conditional formatting to show the data variance in the sales.

Let’s see the steps to achieve this.

Step 1: Add marker variable to bind columns.

Input template with markers and conditional formats
Input template with markers and conditional formats

Step 2: Apply conditional formatting to the marker variable rows and export.

//Create Template Marker Processor
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

//Applying conditional formats
#region Data Bar
IConditionalFormats conditions = marker.CreateConditionalFormats(worksheet["C5"]);
IConditionalFormat condition = conditions.AddCondition();

//Set data bar and icon set for the same cell
//Set the format type
condition.FormatType = ExcelCFType.DataBar;
IDataBar dataBar = condition.DataBar;

//Set the constraint
dataBar.MinPoint.Type = ConditionValueType.LowestValue;
dataBar.MaxPoint.Type = ConditionValueType.HighestValue;

//Set color for Bar
dataBar.BarColor = Color.FromArgb(156, 208, 243);

//Hide the value in data bar
dataBar.ShowValue = false;
#endregion

#region Color Scale
conditions = marker.CreateConditionalFormats(worksheet["D5"]);
condition = conditions.AddCondition();

condition.FormatType = ExcelCFType.ColorScale;
IColorScale colorScale = condition.ColorScale;

//Sets 3 - color scale
colorScale.SetConditionCount(3);

colorScale.Criteria[1].FormatColorRGB = Color.FromArgb(244, 210, 178);
colorScale.Criteria[1].Type = ConditionValueType.Percentile;
colorScale.Criteria[1].Value = "50";

colorScale.Criteria[2].FormatColorRGB = Color.FromArgb(245, 247, 171);
colorScale.Criteria[2].Type = ConditionValueType.Percentile;
colorScale.Criteria[2].Value = "100";
#endregion

//Add marker variable
marker.AddVariable("SalesList", GetCustomerAsObjects());

//Process the markers in the template
marker.ApplyMarkers();

Here is how the data would look after the export.

Exported data with conditional formatting using template markers
Exported data with conditional formatting using template markers

A complete working example along with the input file can be downloaded from export-data-with-conditional-formats.zip.

To learn more about applying other advanced conditional formats, please go through the template marker with conditional formatting documentation.

Export data to Excel template with nested collection objects

You may handle hierarchical data in nested collection objects but may not be able to view the data in its structure to analyze it. Syncfusion Excel Library lets you export data from nested collections to an Excel worksheet with more flexible options to analyze data in its hierarchical structure. Here, the template marker plays a grand role in exporting such nested data in different layouts and grouping the exported data.

Data import can be done with these layout options:

  • Default: Parent records imported in the first row of its collection.
  • Merge: Parent records imported in merged rows.
  • Repeat: Parent records imported in all the rows.

The following steps shows how to export in these layout options.

Step 1: Opens the input Excel template.

Step 2: Reads the XML data to get the hierarchical data.

Step 3: Assigns the values read from XML file to respective collection objects.

Step 4: Applies the marker to the Excel worksheet.

Step 5: Saves the workbook.

using Syncfusion.XlsIO;
using System.Collections.Generic;
using System.IO;
using System.Xml.Serialization;

namespace Template_Marker_Nested_Collection
{
    class Program
    {
        static void Main(string[] args)
        {
            ApplyLayout();
        }

        static void ApplyLayout()
        {
            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2016;
                IWorkbook workbook = excelEngine.Excel.Workbooks.Open("../../Data/NestedColl-Template-Default.xlsx");
                IWorksheet worksheet = workbook.Worksheets[0];

                //Create Template Marker Processor
                ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

                //Add collection to the marker variables where the name should match with input template
                marker.AddVariable("BrandObject", GetVehicleDetails());

                //Process the markers in the template
                marker.ApplyMarkers();

                string fileName = @"ExportData-DefaultLayout.xlsx";
                workbook.SaveAs(fileName);
                workbook.Close();
            }
        }

        #region Helper Methods
        //Helper Method
        static private IList<Brand> GetVehicleDetails()
        {
            XmlSerializer deserializer = new XmlSerializer(typeof(BrandObjects));
            //Read data from XML file.
            TextReader textReader = new StreamReader(@"..\..\Data\ExportData.xml");
            BrandObjects brands = (BrandObjects)deserializer.Deserialize(textReader);

            //Initialize parent collection to add data from XML file.
            List<Brand> list = new List<Brand>();
            string brandName = brands.BrandObject[0].BrandName;
            string vehicleType = brands.BrandObject[0].VahicleType;
            string modelName = brands.BrandObject[0].ModelName;

            //Parent class
            Brand brand = new Brand(brandName);
            brand.VehicleTypes = new List<VehicleType>();

            VehicleType vehicle = new VehicleType(vehicleType);
            vehicle.Models = new List<Model>();
            Model model = new Model(modelName);

            brand.VehicleTypes.Add(vehicle);
            list.Add(brand);

            foreach (BrandObject brandObj in brands.BrandObject)
            {
                if (brandName == brandObj.BrandName)
                {
                    if (vehicleType == brandObj.VahicleType)
                    {
                        vehicle.Models.Add(new Model(brandObj.ModelName));
                        continue;
                    }
                    else
                    {
                        vehicle = new VehicleType(brandObj.VahicleType);
                        vehicle.Models = new List<Model>();
                        vehicle.Models.Add(new Model(brandObj.ModelName));
                        brand.VehicleTypes.Add(vehicle);
                        vehicleType = brandObj.VahicleType;
                    }
                    continue;
                }
                else
                {
                    brand = new Brand(brandObj.BrandName);
                    vehicle = new VehicleType(brandObj.VahicleType);
                    vehicle.Models = new List<Model>();
                    vehicle.Models.Add(new Model(brandObj.ModelName));
                    brand.VehicleTypes = new List<VehicleType>();
                    brand.VehicleTypes.Add(vehicle);
                    vehicleType = brandObj.VahicleType;
                    list.Add(brand);
                    brandName = brandObj.BrandName;
                }
            }
            textReader.Close();
            return list;
        }
        #endregion
    }
}

Let’s see these options in detail along with input templates and output screenshots.

Layout options
Default layout option

This option exports parent records in the first row of its collection.

Input template for default layout option
Input template for default layout option

The following screenshot represents the output document with the default layout option.

Spreadsheet output with default layout option
Spreadsheet output with default layout option
Merge layout option

This option exports parent records with merged rows.

Input template for merge layout option
Input template for merge layout option

The following screenshot represents the output document with the merge layout option.

Spreadsheet output with merge layout option
Spreadsheet output with merge layout option
Repeat layout option

This option repeats the parent records exported in all the rows.

Input template for repeat layout option
Input template for repeat layout option

The following screenshot represents the output document with the repeat layout option.

Spreadsheet output with repeat layout option
Spreadsheet output with repeat layout option
Grouping options
Import data with grouping option

Hierarchical data imported into Excel worksheets must be shown in its structure to be analyzed more flexibly. In addition, if the data is grouped according to its level, it is easier to analyze. XlsIO lets you import hierarchical data from nested collections and group them while exporting data using template markers.

The following are the options supported to group on import:

Expand: Imported data will be grouped and expanded.

Collapse: Imported data will be grouped and collapsed at first level, by default.

In addition, CollapseLevel will group and collapse the levels, up to the maximum of eight levels.

Input template for default layout and group options
Input template for default layout and group options

The following screenshot represents the output document of grouped data imported from a nested collection and collapsed at level two.

Spreadsheet output with default layout and group options
Spreadsheet output with default layout and group options

GitHub samples

You can download the examples of exporting data to Excel in C# here.

Conclusion

As you can see, Syncfusion Excel Library (XlsIO) provides various features to export data to a predefined Excel template using template markers in C#. Use them effectively to generate Excel reports with high performance and to process large data. Take a moment to peruse the documentation, where you’ll find other options and features, all with accompanying code samples. Using the library, you can also export Excel data to PDFs, images, data tables, CSVTSV, HTML, collections of objectsODS file format, and more.

If you are new to our Excel Library, it is highly recommended that you follow our Getting Started guide.

Are you already a Syncfusion user? You can download the product setup here. If you’re not yet a Syncfusion user, you can download a free, 30-day trial here.

If you have any questions about these features, please let us know in the comments below. You can also contact us through our support forum or Direct-Trac or feedback portal. We are happy to assist you!

Related blogs

Tags:

Share this post:

Share on twitter
Share on facebook
Share on linkedin

Comments (2)

Does this work if the running PC/Server doesn’t have office installed? One thing we try to avoid is having office installed on the app server, as we don’t want office causing issues.

Hi Bill,

Yes, it works without Office installed. If you want to view the output file, you need to have the Microsoft Excel application. Otherwise, it is not necessary.

Essential XlsIO allows you to create, read, and edit Excel documents programmatically without Microsoft Office dependencies. You can refer to the following links to know more about the product.

https://help.syncfusion.com/file-formats/xlsio/overview
https://www.syncfusion.com/excel-framework/net

Leave a comment

Popular Now

Be the first to get updates

Subscribe RSS feed
Scroll To Top