How to Make Product Labels using Excel Templates in C#
Tags: webdev, devops, csharp, tutorial
Published: 2024-05-28 18:44:36

---
canonical_url: https://developer.mescius.com/blogs/how-to-make-product-labels-using-excel-templates-in-c-sharp
description: Learn how to make product labels using Excel templates in C#. See more from Document Solutions today.
---
**What You Will Need**
- Visual Studio
- Ds.Documents.Excel NuGet Package

**Controls Referenced**
- [Ds.Documents.Excel](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/overview.html)

**Tutorial Concept**

C# Excel Templates - Using a C# .NET Excel API, retail-style product labels can be created using Excel document templates.

---
Well-designed product labels promote brand image by grabbing consumers' attention.

An organized report effectively represents the data and ensures the information is displayed clearly. However, manually creating these reports can be time-consuming and lead to errors. That's where our [<span>Document Solutions for Excel</span>](https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/productlabel "https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/productlabel") (DsExcel) API comes in. With its [Templates](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/templates.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/templates.html") feature, generating high-quality, error-free reports becomes simple and efficient.

In this blog, we will demonstrate how to design an ideal product label report for mobile devices. Using the [<span>Product Label</span>](https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/productlabel "https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/productlabel") demo, we will create labels for each dataset from scratch. We will use the mobile data from the [attached JSON file](https://cdn.mescius.io/umb/media/f2debjgm/mobiledata.json) to design our report.

Follow the steps below to easily create product labels using Excel templates in C#:

*   [Create a Project with DsExcel Dependency](#Create)
*   [Define the Template Fields in the Sheet](#Define)
*   [Assign the Data Source to the Template](#Assign)
*   [Customize the Template Appearance](#Customize)

## <a id="Create"></a>Create a Project with DsExcel Dependency

Let's begin by setting up a new .NET 8 Console App that includes the DsExcel dependency by following these steps:

1.  Open Visual Studio and select **File** | **New** | **Project** to create a new **Console App**.
2.  Right-click on the project in Solution Explorer and choose **Manage NuGet Packages…**. from the context menu.
3.  Search for[**<span>Ds.Documents.Excel</span>**](https://www.nuget.org/packages/DS.Documents.Excel "https://www.nuget.org/packages/DS.Documents.Excel") in the NuGet Package Manager and click on **Install.**

![Excel Templates C#_Configure](//cdn.mescius.io/umb/media/wklh5btt/excel-templates-c_configure.png?rmode=max&width=549&height=334)

![Excel Templates C#_Manage](//cdn.mescius.io/umb/media/qshfju1f/excel-templates-c_manage.png?rmode=max&width=550&height=333)

![Excel Templates C#_Install](//cdn.mescius.io/umb/media/3xycmhmi/excel-templates-c_install.png?rmode=max&width=550&height=298)

Now that we've successfully set up the project, it is time to create a new [Workbook](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/ManageWorkbook.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/ManageWorkbook.html") <span>object to start developing our report. The DsExcel code to initialize the new Workbook is below:</span>

```
    // Create a new Workbook Object
    Workbook workbook = new Workbook();
    //Access the first sheet
    IWorksheet worksheet = workbook.Worksheets[0];
```

Next, we will define the template that determines the position of the data fields in the report.

## <a id="Define"></a>Define the Template Fields in the Sheet

The first step in generating the report is to define a template layout for the data fields in our JSON using the Workbook object. These template cells define the position of each data field in the report for every record.

In DsExcel, we use mustache braces `{{}}` to define template fields, including data fields, static values, and formulas. Link the template to our JSON properties designated as `{{ds.FieldName}}`, where 'ds' represents the alias for the data source, and place the template text in the cells as follows:

```
    worksheet.Range["C9"].Value = "{{ds.Description}}";
    worksheet.Range["C12"].Value = "Code        :";
    worksheet.Range["D12"].Value = "{{ds.ProductCode}}";
    worksheet.Range["C14"].Value = "Brand       :";
    worksheet.Range["D14"].Value = "{{ds.Brand}}";
    worksheet.Range["C16"].Value = "Category  :";
    worksheet.Range["D16"].Value = "{{ds.Category}}";
    worksheet.Range["G5"].Value = "{{ds.Barcode}}";
    worksheet.Range["C21"].Value = "{{ds.Price}}"; 
```

DsExcel also offers formulas to add <span>[barcodes in Excel files.](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/barcodes.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/barcodes.html")</span> <span>These barcodes not only enhance but also standardize the appearance of the product label. To include barcodes in the report, use the following formula template:</span>

```
    worksheet.Range["C18"].Value = "{{==BC_GS1_128"+"(G5,,,false)}}";
```

The labels that we are generating will be unique for each record, so let’s bind the product ID with the sheet name so that separate pages can be created for each product label. This feature of creating individual pages based on field names is known as _<span>sheet binding</span>_.

<span>The code to assign the ProductCode template to the sheet name is below:</span>

```
    worksheet.Name = "{{ds.ProductCode}}";
```

If we save the workbook to Excel, the final template appears as follows:

![Excel Templates C#_Final](//cdn.mescius.io/umb/media/cfnpc2oo/excel-templates-c_final.png?rmode=max&width=515&height=345)

## <a id="Assign"></a>Assign the Data Source to the Template

Now that we have designed the template, it is time to assign the JSON data to the template cells. The code to connect the template cells with the JSON data is as follows:

```
    // Extract the JSON data from a file named "MobileData.json"
    string jsonData = File.ReadAllText("MobileData.json");
    // Create a JsonDataSource
    var datasource = new JsonDataSource(jsonData);
    //Add data source
    workbook.AddDataSource("ds", datasource);
    //Invoke to process the template
    workbook.ProcessTemplate();
    //Save the report to Pdf
    workbook.Save("LabelReport.pdf");
```

After setting the data source, the report looks like this:

![Excel Templates C#_Report](//cdn.mescius.io/umb/media/vion54ay/excel-templates-c_report.png?rmode=max&width=392&height=483)

Now, we will apply formatting to our template cells to generate more appealing labels.

## <a id="Customize"></a>Customize the Template Appearance

Formatting makes data more visually interesting by adding colors, fonts, and shapes, making it easier for people to understand and remember.

To format our template, first, we will adjust the cells' font, alignment, and number format. Then, we will merge some cells to improve the appearance further. The code to implement this formatting is below:

```
    //Update Fonts, Number Formats and Alignments of Cells
    worksheet.Range["C9:D9"].Merge(true);
    worksheet.Range["C9:D9"].Style.HorizontalAlignment = HorizontalAlignment.Center;

    worksheet.Range["C9,C18"].Font.Size = 20;
    worksheet.Range["C9,C18"].Font.Bold = true;
    worksheet.Range["C9,C18"].Font.Name = "Calibri";

    worksheet.Range["C21"].Font.Size = 30;
    worksheet.Range["C21"].Font.Bold = true;
    worksheet.Range["C21"].Font.Name = "Calibri";

    worksheet.Range["C21:D21"].Merge(true);

    worksheet.Range["C21:D21"].NumberFormat = "$ #,###.00";
    worksheet.Range["C12:D12, C14:D14, C16:D16"].Font.Size = 14;
    worksheet.Range["C12:D12, C14:D14, C16:D16"].Font.Name = "Calibri";

    worksheet.Range["C18:D18"].Merge(true);
    worksheet.Range["C18:D19"].Style.HorizontalAlignment = HorizontalAlignment.Center;
    worksheet.Range["C18:D18"].Font.Size = 25;

    worksheet.Columns[2].ColumnWidth = 13;
```

Now that the text has been formatted, it is time to add some additional elements to customize the labels. First, we will add shapes to decorate the labels on our sheet. To add the shapes to the sheet, we need to add our desired shapes to the [<span>Shapes</span>](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IWorksheet~Shapes.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IWorksheet~Shapes.html") collection of the worksheet. Check out [the documentation](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/WorkWithShapeAndPicture.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/WorkWithShapeAndPicture.html") to learn more about adding Excel shapes with DsExcel.

<span>In our template, we will use the code below to add the shapes and adjust their transparency to make product details in the template cells easily visible.</span>

```
    //Add shapes in the Sheet
    worksheet.Shapes.AddShape(GrapeCity.Documents.Excel.Drawing.AutoShapeType.FlowchartOffpageConnector, 77, 47, 170, 320);
    worksheet.Shapes[0].Fill.Color.RGB = Color.FromArgb(24, 23, 23);
    worksheet.Shapes[0].Rotation = 180;
    worksheet.Shapes[0].Fill.Transparency = .90;
    worksheet.Shapes[0].Line.Color.RGB = Color.FromArgb(191, 191, 191);
    worksheet.Shapes[0].ZOrder(GrapeCity.Documents.Excel.Drawing.ZOrderType.SendToBack);

    worksheet.Shapes.AddShape(GrapeCity.Documents.Excel.Drawing.AutoShapeType.RoundedRectangle, 85, 150, 155, 156);
    worksheet.Shapes[1].Fill.Color.RGB = Color.FromArgb(255, 192, 0);

    worksheet.Shapes[1].Fill.Transparency = 0.70;
    worksheet.Shapes[1].Adjustments[0] = .1;
    worksheet.Shapes[1].Line.Color.RGB = Color.FromArgb(191, 191, 191);
    worksheet.Shapes[1].ZOrder(GrapeCity.Documents.Excel.Drawing.ZOrderType.SendBackward);

    worksheet.Shapes.AddShape(GrapeCity.Documents.Excel.Drawing.AutoShapeType.Oval, 152, 70, 20, 23);
    worksheet.Shapes[2].Fill.Color.RGB = Color.FromArgb(255, 255, 255);
    worksheet.Shapes[2].Line.Color.RGB = Color.FromArgb(166, 166, 166);
    worksheet.Shapes[2].ZOrder(GrapeCity.Documents.Excel.Drawing.ZOrderType.BringForward);

    worksheet.Shapes.AddShape(GrapeCity.Documents.Excel.Drawing.AutoShapeType.LineInverse, 77, 140, 170, 0);
    worksheet.Shapes[3].Line.DashStyle = GrapeCity.Documents.Excel.Drawing.LineDashStyle.RoundDot;
    worksheet.Shapes[3].Line.Style = GrapeCity.Documents.Excel.Drawing.LineStyle.Single;
    worksheet.Shapes[3].Line.Weight = 1;
    worksheet.Shapes[3].Line.Color.RGB = Color.FromArgb(0, 0, 0, 0);
    worksheet.Shapes[3].ZOrder(GrapeCity.Documents.Excel.Drawing.ZOrderType.BringForward);

    worksheet.Shapes.AddShape(GrapeCity.Documents.Excel.Drawing.AutoShapeType.LineInverse, 77, 320, 170, 0);
    worksheet.Shapes[4].Line.DashStyle = GrapeCity.Documents.Excel.Drawing.LineDashStyle.RoundDot;
    worksheet.Shapes[4].Line.Style = GrapeCity.Documents.Excel.Drawing.LineStyle.Single;

    worksheet.Shapes[4].Line.Weight = 1;
    worksheet.Shapes[4].Line.Color.RGB = Color.FromArgb(0, 0, 0, 0);
    worksheet.Shapes[4].ZOrder(GrapeCity.Documents.Excel.Drawing.ZOrderType.BringForward);
```

And that’s it! We have successfully designed our product label report for mobile devices.

![Excel Templates C#_Label](//cdn.mescius.io/umb/media/kasb4ccn/excel-templates-c_label.png?rmode=max&width=461&height=563)

## Conclusion

In this blog, we've learned how to use DsExcel to generate elevated product label reports in a few simple steps. DsExcel API makes creating, reading, and editing Excel documents using C# simple and efficient.  
Check out our demos for more [<span>report designs</span>](https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/templatesamples "https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/templatesamples") you can create using DsExcel.

You can [download the blog sample](https://cdn.mescius.io/umb/media/iv1mvgsw/labelreport.zip) to follow along.

More References:

*   [<span>Product Demo .NET</span>](https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/templates "https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/templates")
*   [<span>Documentation .NET</span>](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/templates.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/templates.html")
*   [<span>Product Demo JAVA</span>](https://developer.mescius.com/document-solutions/java-excel-api/demos/templates "https://developer.mescius.com/document-solutions/java-excel-api/demos/templates")
*   [<span>Documentation JAVA</span>](https://developer.mescius.com/document-solutions/java-excel-api/docs/online/templates.html "https://developer.mescius.com/document-solutions/java-excel-api/docs/online/templates.html")