Learn the basics of working with a BindingList and BindSource where the objective is to export stong typed to Excel formated and unformated along with some ideas for better interacting with data in a DataGridView plus revealing the Missing BindingNavigator in Visual Studio 2022.
The included repository is for showing several important things, properly working with data in a DataGridView using BindingList and BindingSource and easily exporting data to Excel which in the provided code samples are from a DataGridView. Also promotes using class projects for reusable code rather than copy and pasting code from project to projects.
Some might wonder why this is done in Windows Form projects. Although today web projects are the dominate project types there are still use cases for working with Window Forms such as the need to quickly put together side projects for web application as utility projects along with there are still countless developers who are still writing desktop applications.
When using a BindingList and a BindingSource there is zero need to touch cells in a DataGridView as the combination provides rich functionality from finding data, editing, adding and deleting data. Typically a new developer tends to not load strong type data to a DataGridView DataSource but instead adds rows which is not strongly typed which frankly makes working with data cumbersome while working with a BindingList and a BindingSource simplifies all operations. The exception to this is using DataTable or DataSets components which is one step below using a BindingList and a BindingSource.
For exporting a DataGridView, no Excel automation is used. In one example an extension method for a DataGridView is used which accepts a file name to create a .csv file and a parameter to specify a value when a cell value is null.
public static void ExportRowsRaw(this DataGridView sender, string fileName, string defaultNullValue = "(empty)")
{
File.WriteAllLines(fileName, sender.Rows.Cast<DataGridViewRow>()
.Where(row => !row.IsNewRow)
.Select(row => new RowRecord(
row, string.Join(",", Array.ConvertAll(row.Cells.Cast<DataGridViewCell>()
.ToArray(), cell => (cell.Value == null) ?
defaultNullValue :
cell.Value.ToString()))
))
.Select(@row => @row.RowItem));
}Usage
dataGridView1.ExportRowsRaw("contacts.csv");Using the above extension has no cell formatting.
The second example is tied to a specific model and format cell values and the header row using NuGet package SpreadSheetLight. Yes there are many libraries out there but for a free library this one does nicely for formatting and exporting data.
public class CustomersForExcel
{
public int Id { get; set; }
public string CompanyName { get; }
public string Country { get; }
public string Title { get; }
public string Contact { get; }
public string Phone { get; }
public DateTime Modified { get; set; }
public override string ToString() => CompanyName;
public CustomersForExcel(string companyName, string country, string title, string contact, string phone, DateTime? modifiedDateTime, int id)
{
CompanyName = companyName;
Country = country;
Title = title;
Contact = contact;
Phone = phone;
if (modifiedDateTime != null) Modified = modifiedDateTime.Value;
Id = id;
}
}To export a DataGridView from a List to SpreadSheetLight we need to convert the list to a DataTable using NuGet package FastMember.
Given a list List<CustomersForExcel> list, create a new DataTable DataTable table = new(); creates an instance of ObjectReader from FastMember followed by populating the DataTable table.Load(reader);.
Next create and instance of SLDocument which represents the root object from SpreadSheetLight to interact with a new or existing Excel file.
Note On the home page for SpreadSheetLight you can download a extensive help file.
using var document = new SLDocument();Create a SLStyle (SpreadSheetLight class for styling cells) for the header row.
public static SLStyle HeaderStye(SLDocument document)
{
SLStyle headerStyle = document.CreateStyle();
headerStyle.Font.Bold = true;
headerStyle.Font.FontColor = Color.White;
headerStyle.Fill.SetPattern(
PatternValues.LightGray,
SLThemeColorIndexValues.Accent1Color,
SLThemeColorIndexValues.Accent5Color);
return headerStyle;
}Create a variable for the style.
var headerStyle = HeaderStye(document);Next, there is a Date property/column which needs formatting. We create a style for the column.
SLStyle dateStyle = document.CreateStyle();
dateStyle.FormatCode = "mm-dd-yyyy";Next, use SpreadSheetLight ImportDataTable method.
document.ImportDataTable(1, SLConvert.ToColumnIndex("A"), table, true);- Parameter 1, row to start on
- Parameter 2, cell to start on
- Parameter 3. the DataTable to import
- Parameter 4, should header be included
For this example, hide the column for the primary key
document.HideColumn(7, 7);Set the column style for the Date column.
document.SetColumnStyle(dateColumnIndex, dateStyle);Auto size columns
for (int columnIndex = 1; columnIndex < table.Columns.Count; columnIndex++)
{
document.AutoFitColumn(columnIndex);
}
document.AutoFitColumn(dateColumnIndex + 1);Set first row style
document.SetCellStyle(1, 1, 1, 6, headerStyle);Want a specific cell focused?
document.SetActiveCell("A2");Ensure header is visible when scrolling down
document.FreezePanes(1,6);Save to a new file.
document.SaveAs(fileName);Calling the above method, use a try-catch
- On sucess indicate this to the user
- Check if someone currently had the file open
- Check for general exceptions.
private void ExportButton_Click(object sender, EventArgs e)
{
try
{
NorthWindOperations.CustomersToExcel(_bindingList.ToList(), Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Customers.xlsx"));
MessageBox.Show("Done");
}
catch (Exception exception) when (exception.Message.Contains("The process cannot access the file"))
{
MessageBox.Show("Hey you have the spreadsheet open, can not save!!!");
}
catch (Exception exception)
{
MessageBox.Show($"Something went wrong '{exception.Message}'");
}
}Finished product
In the screenshot above, the sort is on Company but if the user sorts on another column the Excel WorkSheet will be sorted on the sort column of the DataGridView.
Note In regards to sorting, using a standard BindingList does not handle sorting. To Get sorting see BindingListLibrary.SortableBindingList class.
As menetioned earlier, a new developer adds data by rows/cells which makes it hard to access, edit and find data.
When coupling a BindingSource with a BindingList, to find data without touching the DataGridView is easy. Here we want to find a specific first name from a TextBox and here update to a static value.
private void FindAndUpdateButton_Click(object sender, EventArgs e)
{
if (!string.IsNullOrWhiteSpace(FindByFirstNameTextBox.Text))
{
Contact contact = _bindingSource
.List
.OfType<Contact>()
.ToList()
.Find(x => x.FirstName == FindByFirstNameTextBox.Text);
contact.FirstName = "Karen";
}
}The above can also work against other columns for instance find a item by primary key.
Another common operation is to get values from the current row of a DataGridView. This is done by indexing into a BindingList using Position property of a BindingSource where Position is the current row of the DataGridView.
Contact contact = _bindingList[_bindingSource.Position];Now it may be possible that there is no current row so its wise to assert for a current row.
if (_bindingSource.Current is not null)
{
Contact contact = _bindingList[_bindingSource.Position];
}You can use the BindingNavigator control to create a standardized means for users to search and change data on a Windows Form. You frequently use BindingNavigator with the BindingSource component to enable users to move through data records on a form and interact with the records.
Sadly in Visual Studio 2022 the BindingNavigator component is missing from Visual Studio toolbox.
To include it use CoreBindingNavigator found in the project WindowsFormControlsLibrary.
Once WindowsFormControlsLibrary is added as a reference and build the project the BindingNavigator will show up in Visual Studio's toolbox.
In the project DataGridViewExport1 CoreBindingNavigator is used plus a button is added to mirror a standard button to export the DataGridView to Excel.
-
By using strongly typed data in a DataGridView
- There is no need to convert objects to a given type.
- Locating and filtering data is easier
- Work with pre-done components e.g. DataGridView Filter popup.
-
By using BindingList/BindingSource
- You are not limited to just DataGridView, bindings can be done on TextBoxes for instance.
-
Provides several simple examples for exporting DataGridView to Excel
| Project | Description
|:------------- |:-------------|:-------------|
| BindingListLibrary | Contains helpful extensions and a sortable BindingList component |
| DataGridViewLibrary | Contains extensions for exporting a DataGridView to Excel without formatting, a method to quickly convert a generic list to a DataTable. Extension method to auto-size DataGridView columns and extension method to auto-size specific columns rather than all columns|
| NorthWind2020Library | Access to NorthWind database which is used in project DataGridViewExport1 |
| DataGridViewExport | Windows form project which shows how to export a DataGridView data to a .csv file suitable for opening in Excel with no cell formatting |
| DataGridViewExport1 | Windows form project which exports NorthWind data to Excel with cell formatting for Excel |
| SpreadSheetLightImportDataTable | Provides code to export a DataTable which was originally a list to Excel. Also has useful methods and extension methods for working with Excel using NuGet package SpreadSheetLight |
| SpreadsheetLightLibrary | Helpful methods for NuGet package SpreadSheetLight |
| WindowFormsControlsLibrary | Library for Windows Forms controls, in this case has a class to bring back a BindingNavigator missing from Visual Studio 2022 |