Welcome to FoxLearn
In this video, I'll show you how to generate an excel report using ClosedXml.Report in C#
After you finish creating a new Windows Forms project,
you need to open your form designer,
and then drag the Button, TextBox,
Label and DataGridView controls from the Visual Studio toolbox into your form designer
Next, You need to install the excel library from Nuget into your project
On the right side of the window, select your project
Next, right-click and select Manage Nuget Packages,
then search for ClosedXml.Report
This is a great excel library for reading,
manipulating and writing Excel 2007 files
It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API
After you finish installing the ClosedXml.Report library,
you can add an ADO.NET Entity Data Model to help you retrieve data from SQL database
In this tutorial, I will use the Northwind database to demo the lesson,
if you don't have the Northwind database in your SQL Server yet.
You can watch the video I uploaded on how to download and install the Northwind database into SQL Server
Next, Rebuild your project, then add a bindingsource to your DataGridView
The BindingSource component is designed to simplify the process of binding controls to an underlying data source
The BindingSource component acts as the data source for some or all of the controls on the form
You can bind the BindingSource component to both simple data sources,
like a single property of an object or a basic collection like ArrayList, and complex data sources, like a database table
Next, Double-click your form designer, then add a Form_Load event handler that allows you to retrieve customer data from the Northwind database
Next, Add a click event handler to the Browse button that allows you to get the template file name
Finally, Add a click event handler to the Generate button
that allows you to fill data from the row you select in the DataGridView into the excel template file
You can use the SaveFileDialog class to help you open a save file dialog,
then you can enter the filename you want to save
And don't forget to use try catch to catch errors if something goes wrong,
then write your code in this try catch block
We will use the XLTemplate class to read the excel template file,
then generate an excel report with the data based on the row we select from the DataGridView
You can easily create a simple excel template with data fields that correspond to the columns in the customers table
Thank you for watching this video and don't forget to subscribe to my channel