How to Make Product Labels using Excel Templates in C#

MESCIUS inc.

Published in

MESCIUS inc.

6 min readMay 7, 2024

What You Will Need
• Visual Studio
• Ds.Documents.Excel NuGet Package

Controls Referenced
• Ds.Documents.Excel

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 Document Solutions for Excel (DsExcel) API comes in. With its Templates 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 Product Label demo, we will create labels for each dataset from scratch. We will use the mobile data from the attached JSON file to design our report.

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

Create a Project with DsExcel Dependency
Define the Template Fields in the Sheet
Assign the Data Source to the Template
Customize the Template Appearance

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:

Open Visual Studio and select File | New | Project to create a new Console App.
Right-click on the project in Solution Explorer and choose Manage NuGet Packages…. from the context menu.
Search forDs.Documents.Excel in the NuGet Package Manager and click on Install.

Now that we’ve successfully set up the project, it is time to create a new Workbook object to start developing our report. The DsExcel code to initialize the new Workbook is below:

// 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.

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 barcodes in Excel files. 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:

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 sheet binding .

The code to assign the ProductCode template to the sheet name is below:

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

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

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:

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

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 collection of the worksheet. Check out the documentation to learn more about adding Excel shapes with DsExcel.

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.

//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.

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 report designs you can create using DsExcel.

You can download the blog sample to follow along.

More References:

Originally published at https://developer.mescius.com on May 7, 2024.