what is DataGridView?
The control provides a powerful and flexible way to display data in a tabular format. You can use the DataGridView control to show read-only views of a small amount of data, or you can scale it to show editable views of very large sets of data. You can extend the DataGridView control in a number of ways to build custom behaviors into your applications.
For example, you can programmatically specify your own sorting algorithms, and you can create your own types of cells. You can easily customize the appearance of the DataGridView control by choosing among several properties. Many types of data stores can be used as a data source, or the DataGridView control can operate with no data source bound to it.
what is Excel?
Excel is a spreadsheet program from Microsoft and a component of its Office product group for business applications. Microsoft Excel enables users to format, organize and calculate data in a spreadsheet by organizing data using software like Excel, data analysts and other users can make information easier to view as data is added or changed.
Excel contains a large number of boxes called cells that are ordered in rows and columns. Data is placed in these cells. Excel is a part of the Microsoft Office and Office 365 suites and is compatible with other applications in the Office suite. The spreadsheet software is available for Windows, macOS, Android, and iOS platforms.
DataGridView to excel c#
in this article, I will explain with an example, how to export DataGridView data to an Excel file using C#.DataGridView cannot be exported directly to an Excel file and hence need to generate a DataTable dt and export the DataTable to an Excel file. The DataTable will be exported to a formatted Excel file using the ClosedXml library which is a wrapper of OpenXml.for this tutorial, I have already created the database and all the data is in datagridview.
Step # 1: Create Visual Studio Project:
Open Visual Studio. I am using Visual Studio 2019.
Click on Create New Project.
Now, Select Windows Form App from Template, and Press Next, Following Window will appear. Write Project Name. I have written exporting datagridview to excel.
Now, Click Next, Following WIndow will appear.
Select .Net Core 3.1 from Dropdown Menu. Click on the "Create" Button, Project will be created.
Step # 2: Install Nuget Package DocumentFormat.OpenXml and ClosedXml Libraries
for this, we need a document open XML sdk and closedXMl library. You can download the libraries using the following download locations.https://github.com/ClosedXML/ClosedXML
Step # 3: Design Window Forms and writes code
The design form will look like this I have created a windows form and connected that form with the database and all the data is loaded into the windows forms application.
Importing data to datagridview.
You will need to import the following namespaces before starting the code.
DataGridView
Inside the Form Load event, the DataGridView is populated with records from the table.
Export DataGridView to Excel
Inside the Button Click event handler, first, a DataTable is created with columns same as that of the DataGridView, and a loop is executed over the DataGridView rows and all the data is added to the DataTable.Then a Workbook object is created to which the DataTable is added as Worksheet using the Add method which accepts DataTable and the name of the Sheet as parameters.
Once the DataTable is added as a Worksheet to the Workbook, the formatting is done by first setting the Header row background color and then applying background colors to the rows and the alternating rows of the Excel file.
Finally, the export data to and export data WorkBook is saved to the specified location on the disk. we can use that workbook as a normal excel documents or workbook. we can also create or fill and export large excel files and normal excel sheets and we can set the filename as well to set the name we have to set the name of the project and then save the name of the file as a string.
Before you write source code, you must add a reference to the Microsoft Excel object library. Right-click on your project and select Add Reference menu. After that go to the COM tab and select and add Microsoft Excel 12.0 object library. Now here is my Button click event handler where I create Excel object sender or object obj and documents, get data from DataGridView, and add rows and columns to the document.
Export data source code sample
Excel files
after writing the complete source code when we press the export data button the excel file will be generate and when we open the excel file we will get a look like this. all the data of database will be in excel file
This is the completion of the guide. I hope it was easy for you to follow and understand. So, what are you waiting for? 30 Days Free Trial You can obtain the License here and begin straightaway. If you are not yet an IronPDF customer, you can try 30-day free trial to check out their available features. If you buy the complete Iron Suite, you will get all 5 Products for the Price of 2. For further details about the licensing, please follow this link to Purchase the complete Package.
You can download the software product from this link.
Top comments (1)
hi. that very useful code and I appreciate it.
I have just 1 question : how can I find cell range:
wb.Worksheet(1).Cell("{0}1:{1}1").Style.Fill.BackgroundColor = XLColor.DarkGreen;
you have set a1:c1 but datatcolumns change in each table