Tutorial 22 Exporting C1List to Excel

Show All

Hide All

The following steps demonstrate exporting the data from C1List to an Excel file:

Create a new WinForms application and add the following references by right-clicking References in the Solution Explorer and selecting Add Reference from the list:
- C1.Win.C1List.dll
- C1.C1Excel.dll
Add a C1List control to the Form by performing a drag-and-drop operation.
Locate the C1Excel control in your Toolbox and double-click to add the control to the application. The control will be added to the component tray below the form.
Locate the WinForms Button and Checkbox controls in the Toolbox and add them to the form below the C1List control. Select the Button control to show the available Properties in the Properties window.
Locate the Text property in the list and enter "Export" in the Text textbox.
Select the Checkbox control on your Form to show the available Properties in the Properties window.
Locate the Text property in the list and enter "Export Headers" in the Text textbox.

Double-click the C1List control to add a FormLoad() event to the code. The event will resemble the following code:

To write code in Visual Basic

Visual Basic	Copy Code
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

To write code in C#

C#	Copy Code
private void Form1_Load(object sender, System.EventArgs e)

While still on the Form1.cs page, add the following statement to bind the C1List control to a Data Source:

To write code in Visual Basic

Visual Basic	Copy Code
C1List1.DataSource = getTable()

To write code in C#

C#	Copy Code
C1List1.DataSource = getTable();

Add the following function directly after the binding statement to create the DataTable to which the List is bound:

To write code in Visual Basic

Visual Basic	Copy Code
Public Function getTable() As DataTable Dim dTable As New DataTable dTable.Columns.Add(New DataColumn("FName")) dTable.Columns.Add(New DataColumn("LName")) dTable.Columns.Add(New DataColumn("Date of Birth", GetType(DateTime))) dTable.Columns.Add(New DataColumn("Hire Date", GetType(DateTime))) Dim dr As DataRow = dTable.NewRow dr.ItemArray = New String() {"John", "Smith", Now.AddYears(-55), Now.AddYears(-25)} dTable.Rows.Add(dr) dr = dTable.NewRow dr.ItemArray = New String() {"Dave", "Richardson", Now.AddYears(-32), Now.AddYears(-5)} dTable.Rows.Add(dr) dr = dTable.NewRow dr.ItemArray = New String() {"Tom", "Franklin", Now.AddYears(-16), Now.AddYears(-43), Now.AddYears(-16)} dTable.Rows.Add(dr) dr = dTable.NewRow dr.ItemArray = New String() {"Jason", "Lee", Now.AddYears(-30), Now.AddYears(-5)} dTable.Rows.Add(dr) dr = dTable.NewRow dr.ItemArray = New String() {"Henry", "Mckinley", Now.AddYears(-19), Now.AddYears(-1)} dTable.Rows.Add(dr) Return dTable End Function

To write code in C#

C#	Copy Code
{ DataTable dTable = new DataTable(); dTable.Columns.Add(new DataColumn("FName")); dTable.Columns.Add(new DataColumn("LName")); dTable.Columns.Add(new DataColumn("Date of Birth", typeof(DateTime))); dTable.Columns.Add(new DataColumn("Hire Date", typeof(DateTime))); DataRow dr = dTable.NewRow(); dr.ItemArray = new object[] {"John", "Smith", DateTime.Now.AddYears(-55), DateTime.Now.AddYears(-25) }; dTable.Rows.Add(dr); dr = dTable.NewRow(); dr.ItemArray = new object[] { "Dave", "Richardson", DateTime.Now.AddYears(-32), DateTime.Now.AddYears(-5) }; dTable.Rows.Add(dr); dr = dTable.NewRow(); dr.ItemArray = new object[] { "Tom", "Franklin", DateTime.Now.AddYears(-43), DateTime.Now.AddYears(-16) }; dTable.Rows.Add(dr); dr = dTable.NewRow(); dr.ItemArray = new object[] { "Jason", "Lee", DateTime.Now.AddYears(-30), DateTime.Now.AddYears(-5) }; dTable.Rows.Add(dr); dr = dTable.NewRow(); dr.ItemArray = new object[] { "Henry", "Mckinley", DateTime.Now.AddYears(-19), DateTime.Now.AddYears(-1) }; dTable.Rows.Add(dr); return dTable; }

Add the following code below the code creating the Data Table to handle exporting the C1List content to an Excel file:

To write code in Visual Basic

Visual Basic	Copy Code
Public Sub ExportData(ByVal exportHeaderCondition As Boolean) Dim excelSheet As C1.C1Excel.XLSheet = C1XLBook1.Sheets(0) If excelSheet IsNot Nothing Then Dim excelRow As Integer = 0 If exportHeaderCondition = True Then For i As Integer = 0 To C1List1.Columns.Count - 1 excelSheet(excelRow, i).Value = C1List1.Columns(i).Caption Next excelRow += 1 End If For row As Integer = 0 To C1List1.ListCount - 1 For col As Integer = 0 To C1List1.Columns.Count - 1 Dim cellValue As Object = C1List1.GetDisplayText(row, col) excelSheet(excelRow, col).Value = cellValue Next excelRow += 1 Next End If C1XLBook1.Save("C:\GeneratedReport.xls") Process.Start("C:\GeneratedReport.xls") End Sub

To write code in C#

C#	Copy Code
public void ExportData(bool exportHeaderCondition) { C1.C1Excel.XLSheet excelSheet = C1XLBook1.Sheets[0]; if (excelSheet != null) { int excelRow = 0; if ((exportHeaderCondition == true)) { for (int i = 0; (i <= (C1List1.Columns.Count - 1)); i++) { excelSheet[excelRow, i].Value = C1List1.Columns[i].Caption; } excelRow++; for (int row = 0; (row <= (C1List1.ListCount - 1)); row++) { for (int col = 0; (col <= (C1List1.Columns.Count - 1)); col++) { object cellValue = C1List1.GetDisplayText(row, col); excelSheet[excelRow, col].Value = cellValue; } excelRow++; } } C1XLBook1.Save("C:\\GeneratedReport.xls"); Process.Start("C:\\GeneratedReport.xls"); }

Next, you will add the Button_Click() event to allow the Export button to export the list data. Add the following code to handle the Button_Click() event:

To write code in Visual Basic

Visual Basic	Copy Code
Private Sub exportButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exportButton.Click If exportHeaderCheckBox.Checked Then ExportData(True) Else ExportData(False) End If End Sub

To write code in C#

Press F5 to run your application. The application should appear as follows:

Note that you can export the Headers along with the rest of the data from the List by checking the Export Headers Checkbox .