How to export Gridview data to Excel using devexpress XtraGrid

November 28, 2015

To export Gridview data to Excel using devexpress XtraGrid have a look at the sample below.

Samples

C# Sample

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Columns;

namespace de.Fesslersoft.ExportXtraGridToExcel
{
    public partial class ExportForm : Form
    {
        private DataTable _dataTable;

        public ExportForm()
        {
            InitializeComponent();
        }
        
        private void ExportForm_Load(object sender, EventArgs e)
        {
            FillDataTable();
            gridViewExport.OptionsBehavior.AutoPopulateColumns = true;
            gridViewExport.OptionsBehavior.ReadOnly = true;
            gridViewExport.OptionsBehavior.Editable = false;
            gridControlExport.DataSource = _dataTable;
        }

        private void FillDataTable()
        {
            //https://documentation.devexpress.com/#WindowsForms/CustomDocument2240
            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder
            {
                DataSource = "localhost",
                IntegratedSecurity = true,
                InitialCatalog = "Northwind"
            };
            using (SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString()))
            {
                connection.Open();
                string queryCommand = "SELECT TOP 100 [ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued] FROM [Northwind].[dbo].[Products]";
                using (SqlDataAdapter adapter = new SqlDataAdapter(queryCommand, connection))
                {
                    _dataTable = new DataTable();
                    adapter.Fill(_dataTable);
                }
            }
        }

        private void ExportToExcel()
        {
            //https://www.devexpress.com/Support/Center/Question/Details/Q430217
            //https://documentation.devexpress.com/#WindowsForms/CustomDocument1874
            //storing current layout
            gridViewExport.SaveLayoutToXml(@"C:\Users\mf\Desktop\Neuer Ordner\tempLayout.xml");
            foreach (GridColumn column in gridViewExport.Columns)
            {
                //make all export columns visible
                column.Visible = true;
            }
            gridViewExport.ExportToXls(@"C:\Users\mf\Desktop\Neuer Ordner\export.xls");
            //restoring the layout, the layout file needs to be deleted manually
            gridViewExport.RestoreLayoutFromXml(@"C:\Users\mf\Desktop\Neuer Ordner\tempLayout.xml");
        }

        private void buttonExportToExcel_Click(object sender, EventArgs e)
        {
            ExportToExcel();
            XtraMessageBox.Show("Export successfull!", this.Text, MessageBoxButtons.OK,MessageBoxIcon.Information,MessageBoxDefaultButton.Button1);
        }
    }
}

using System;

using System.Data;

using System.Data.SqlClient;

using System.Windows.Forms;

using DevExpress.XtraEditors;

using DevExpress.XtraGrid.Columns;

namespace de.Fesslersoft.ExportXtraGridToExcel

{

public partial class ExportForm : Form

{

private DataTable _dataTable;

public ExportForm()

{

InitializeComponent();

}

private void ExportForm_Load(object sender, EventArgs e)

{

FillDataTable();

gridViewExport.OptionsBehavior.AutoPopulateColumns = true;

gridViewExport.OptionsBehavior.ReadOnly = true;

gridViewExport.OptionsBehavior.Editable = false;

gridControlExport.DataSource = _dataTable;

}

private void FillDataTable()

{

//https://documentation.devexpress.com/#WindowsForms/CustomDocument2240

SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder

{

DataSource = "localhost",

IntegratedSecurity = true,

InitialCatalog = "Northwind"

};

using (SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString()))

{

connection.Open();

string queryCommand = "SELECT TOP 100 [ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued] FROM [Northwind].[dbo].[Products]";

using (SqlDataAdapter adapter = new SqlDataAdapter(queryCommand, connection))

{

_dataTable = new DataTable();

adapter.Fill(_dataTable);

}

private void ExportToExcel()

{

//https://www.devexpress.com/Support/Center/Question/Details/Q430217

//https://documentation.devexpress.com/#WindowsForms/CustomDocument1874

//storing current layout

gridViewExport.SaveLayoutToXml(@"C:\Users\mf\Desktop\Neuer Ordner\tempLayout.xml");

foreach (GridColumn column in gridViewExport.Columns)

{

//make all export columns visible

column.Visible = true;

}

gridViewExport.ExportToXls(@"C:\Users\mf\Desktop\Neuer Ordner\export.xls");

//restoring the layout, the layout file needs to be deleted manually

gridViewExport.RestoreLayoutFromXml(@"C:\Users\mf\Desktop\Neuer Ordner\tempLayout.xml");

}

private void buttonExportToExcel_Click(object sender, EventArgs e)

{

ExportToExcel();

XtraMessageBox.Show("Export successfull!", this.Text, MessageBoxButtons.OK,MessageBoxIcon.Information,MessageBoxDefaultButton.Button1);

}

VB.NET Sample

Imports System.Data.SqlClient
Imports DevExpress.XtraEditors
Imports DevExpress.XtraGrid.Columns

Public Partial Class ExportForm
    Inherits Form
    Private _dataTable As DataTable

    Public Sub New()
        InitializeComponent()
    End Sub

    Private Sub ExportForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        FillDataTable()
        gridViewExport.OptionsBehavior.AutoPopulateColumns = True
        gridViewExport.OptionsBehavior.[ReadOnly] = True
        gridViewExport.OptionsBehavior.Editable = False
        gridControlExport.DataSource = _dataTable
    End Sub

    Private Sub FillDataTable()
        'https://documentation.devexpress.com/#WindowsForms/CustomDocument2240
        Dim connectionStringBuilder As New SqlConnectionStringBuilder() With { _
                .DataSource = "localhost", _
                .IntegratedSecurity = True, _
                .InitialCatalog = "Northwind" _
                }
        Using connection As New SqlConnection(connectionStringBuilder.ToString())
            connection.Open()
            Dim queryCommand As String = "SELECT TOP 100 [ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued] FROM [Northwind].[dbo].[Products]"
            Using adapter As New SqlDataAdapter(queryCommand, connection)
                _dataTable = New DataTable()
                adapter.Fill(_dataTable)
            End Using
        End Using
    End Sub

    Private Sub ExportToExcel()
        'https://www.devexpress.com/Support/Center/Question/Details/Q430217
        'https://documentation.devexpress.com/#WindowsForms/CustomDocument1874
        'storing current layout
        gridViewExport.SaveLayoutToXml("C:\Users\mf\Desktop\Neuer Ordner\tempLayout.xml")
        For Each column As GridColumn In gridViewExport.Columns
            'make all export columns visible
            column.Visible = True
        Next
        gridViewExport.ExportToXls("C:\Users\mf\Desktop\Neuer Ordner\export.xls")
        'restoring the layout, the layout file needs to be deleted manually
        gridViewExport.RestoreLayoutFromXml("C:\Users\mf\Desktop\Neuer Ordner\tempLayout.xml")
    End Sub

    Private Sub buttonExportToExcel_Click(sender As Object, e As EventArgs) Handles SimpleButton1.Click
        ExportToExcel()
        XtraMessageBox.Show("Export successfull!", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
    End Sub
End Class

Imports System.Data.SqlClient

Imports DevExpress.XtraEditors

Imports DevExpress.XtraGrid.Columns

Public Partial Class ExportForm

Inherits Form

Private _dataTable As DataTable

Public Sub New()

InitializeComponent()

End Sub

Private Sub ExportForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load

FillDataTable()

gridViewExport.OptionsBehavior.AutoPopulateColumns = True

gridViewExport.OptionsBehavior.[ReadOnly] = True

gridViewExport.OptionsBehavior.Editable = False

gridControlExport.DataSource = _dataTable

End Sub

Private Sub FillDataTable()

'https://documentation.devexpress.com/#WindowsForms/CustomDocument2240

Dim connectionStringBuilder As New SqlConnectionStringBuilder() With { _

.DataSource = "localhost", _

.IntegratedSecurity = True, _

.InitialCatalog = "Northwind" _

}

Using connection As New SqlConnection(connectionStringBuilder.ToString())

connection.Open()

Dim queryCommand As String = "SELECT TOP 100 [ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued] FROM [Northwind].[dbo].[Products]"

Using adapter As New SqlDataAdapter(queryCommand, connection)

_dataTable = New DataTable()

adapter.Fill(_dataTable)

End Using

End Sub

Private Sub ExportToExcel()

'https://www.devexpress.com/Support/Center/Question/Details/Q430217

'https://documentation.devexpress.com/#WindowsForms/CustomDocument1874

'storing current layout

gridViewExport.SaveLayoutToXml("C:\Users\mf\Desktop\Neuer Ordner\tempLayout.xml")

For Each column As GridColumn In gridViewExport.Columns

'make all export columns visible

column.Visible = True

gridViewExport.ExportToXls("C:\Users\mf\Desktop\Neuer Ordner\export.xls")

'restoring the layout, the layout file needs to be deleted manually

gridViewExport.RestoreLayoutFromXml("C:\Users\mf\Desktop\Neuer Ordner\tempLayout.xml")

End Sub

Private Sub buttonExportToExcel_Click(sender As Object, e As EventArgs) Handles SimpleButton1.Click

ExportToExcel()

XtraMessageBox.Show("Export successfull!", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)

End Sub

End Class

If you have any questions or suggestions feel free to rate this snippet, post a comment or Contact Us via Email.

Related links:

How to select an element with its name attribute in jQuery

Administrator

November 26, 2015

8 Comments

To select an element with its name attribute in jQuery you can use the snippet below.

Samples

Javascript Sample

$('div[name="de.Fesslersoft.Testname"]').css({"border-color": "#000000", "border-width":"15px", "border-style":"solid"});

1	$('div[name="de.Fesslersoft.Testname"]').css({"border-color": "#000000", "border-width":"15px", "border-style":"solid"});

if you remove the div in div[name=”de.Fesslersoft.Testname”] you will get all elements with the specific name.

You can also have a look at this JSFiddle.

If you have any questions or suggestions feel free to rate this snippet, post a comment or Contact Us via Email.

Related links:

Attribute Equals Selector [name=”value”]

XML Formatter

Administrator

November 25, 2015

0 Comments

We have just released another tool in our new Tools section. It’s a online XML Formatter, which uses HTML, AngularJs and Bootstrap.

XML Formatter

If you have any questions or suggestions feel free to post a comment or Contact Us via Email.

UUID / GUID generator

Administrator

November 24, 2015

0 Comments

We have just released another tool in our new Tools section. It’s a online UUID/GUID generator, which uses HTML, AngularJs and Bootstrap.

UUID/GUID Generator

If you have any questions or suggestions feel free to post a comment or Contact Us via Email.

How to catch specific MS-SQL SQLExceptions in C# and VB.NET

Administrator

November 20, 2015

0 Comments

To catch specific SQLExceptions using Microsoft.NET and Microsoft Sequel Server you can use the snippet below. You also need to look for the specific error numbers/codes you want to catch. To do this you can either execute the following query in the SQL Management Studio or you can use this
List of SQLException Numbers/Codes (Direct-DL around 15mb).

Samples

T-SQL Sample

SELECT * FROM master.dbo.sysmessages ORDER BY error ASC

1	SELECT * FROM master.dbo.sysmessages ORDER BY error ASC

C# Sample

private static void MySqlMethod()
{
	SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder
	{
		DataSource = "localhost",
		InitialCatalog = "Northwind",
		IntegratedSecurity = true
	};

	try
	{
		using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
		{
			connection.Open();
			using (SqlCommand command = connection.CreateCommand())
			{
				//inserting invalid sql (for example: column not exists)
				command.CommandText = "SELECT InvalidColumnName FROM Customers";
				command.ExecuteNonQuery();
			}
		}
	}
	catch (SqlException ex)
	{
		// Log the original exception here
		foreach (SqlError sqlError in ex.Errors)
		{
			switch (sqlError.Number)
			{
				case 207: // 207 = InvalidColumn
					//do your Stuff here
					break;
				case 547: // 547 = (Foreign) Key violation
					//do your Stuff here
					break;
				case 2601: // 2601 = (Primary) key violation
					//do your Stuff here
					break;
				default:
					//do your Stuff here
					break;
			}
		}
	}
}

private static void MySqlMethod()

{

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder

{

DataSource = "localhost",

InitialCatalog = "Northwind",

IntegratedSecurity = true

};

try

{

using (SqlConnection connection = new SqlConnection(builder.ConnectionString))

{

connection.Open();

using (SqlCommand command = connection.CreateCommand())

{

//inserting invalid sql (for example: column not exists)

command.CommandText = "SELECT InvalidColumnName FROM Customers";

command.ExecuteNonQuery();

}

catch (SqlException ex)

{

// Log the original exception here

foreach (SqlError sqlError in ex.Errors)

{

switch (sqlError.Number)

{

case 207: // 207 = InvalidColumn

//do your Stuff here

break;

case 547: // 547 = (Foreign) Key violation

//do your Stuff here

break;

case 2601: // 2601 = (Primary) key violation

//do your Stuff here

break;

default:

//do your Stuff here

break;

}

VB.NET Sample

Private Sub MySqlMethod()
	Dim builder As New SqlConnectionStringBuilder() With { _
		.DataSource = "localhost", _
		.InitialCatalog = "Northwind", _
		.IntegratedSecurity = True _
	}

	Try
		Using connection As New SqlConnection(builder.ConnectionString)
			connection.Open()
			Using command As SqlCommand = connection.CreateCommand()
				'inserting invalid sql (for example: column not exists)
				command.CommandText = "SELECT InvalidColumnName FROM Customers"
				command.ExecuteNonQuery()
			End Using
		End Using
	Catch ex As SqlException
		' Log the original exception here
		For Each sqlError As SqlError In ex.Errors
			Select Case sqlError.Number
				Case 207
					' 207 = InvalidColumn
					'do your Stuff here
					Exit Select
				Case 547
					' 547 = (Foreign) Key violation
					'do your Stuff here
					Exit Select
				Case 2601
					' 2601 = (Primary) key violation
					'do your Stuff here
					Exit Select
				Case Else
					'do your Stuff here
					Exit Select
			End Select
		Next
	End Try
End Sub

Private Sub MySqlMethod()

Dim builder As New SqlConnectionStringBuilder() With { _

.DataSource = "localhost", _

.InitialCatalog = "Northwind", _

.IntegratedSecurity = True _

}

Try

Using connection As New SqlConnection(builder.ConnectionString)

connection.Open()

Using command As SqlCommand = connection.CreateCommand()

'inserting invalid sql (for example: column not exists)

command.CommandText = "SELECT InvalidColumnName FROM Customers"

command.ExecuteNonQuery()

End Using

Catch ex As SqlException

' Log the original exception here

For Each sqlError As SqlError In ex.Errors

Select Case sqlError.Number

Case 207

' 207 = InvalidColumn

'do your Stuff here

Exit Select

Case 547

' 547 = (Foreign) Key violation

'do your Stuff here

Exit Select

Case 2601

' 2601 = (Primary) key violation

'do your Stuff here

Exit Select

Case Else

'do your Stuff here

Exit Select

End Select

End Try

End Sub

Compatibility:

.NET 2.0

.NET 3.0

.NET 3.5

.NET 4.0

.NET 4.5

.NET 4.6

If you have any questions or suggestions feel free to rate this snippet, post a comment or Contact Us via Email.

Related links:

MD5 Hash creator

Administrator

November 12, 2015

0 Comments

We have just released another tool in our new Tools section. It’s a online MD5 Hash Creator, which uses HTML, AngularJs and Bootstrap.

MD5 Hash Creator

If you have any questions or suggestions feel free to post a comment or Contact Us via Email.

How to extract a password protected zip file using DotNetZip in C# and VB.NET

Administrator

November 12, 2015

0 Comments

To extract (unzip) a password protected zip file using the Ionic DotNetZip Library, you can use the snippet below.

Samples

[tab name=”C# Sample”]

string zipFile = @"C:\Users\Fesslersoft\Desktop\ZipTest\Test.zip";
string targetDirectory = @"C:\Users\Fesslersoft\Desktop\ZipTest\";

using (Ionic.Zip.ZipFile zip = Ionic.Zip.ZipFile.Read(zipFile))
{
	zip.Password = "1234";
	zip.ExtractAll(targetDirectory, Ionic.Zip.ExtractExistingFileAction.DoNotOverwrite);
}

Console.WriteLine("Zip file has been successfully extracted.");
Console.Read();

string zipFile = @"C:\Users\Fesslersoft\Desktop\ZipTest\Test.zip";

string targetDirectory = @"C:\Users\Fesslersoft\Desktop\ZipTest\";

using (Ionic.Zip.ZipFile zip = Ionic.Zip.ZipFile.Read(zipFile))

{

zip.Password = "1234";

zip.ExtractAll(targetDirectory, Ionic.Zip.ExtractExistingFileAction.DoNotOverwrite);

}

Console.WriteLine("Zip file has been successfully extracted.");

Console.Read();

[/tab]
[tab name=”VB.NET Sample”]

Dim zipFile As String = "C:\Users\Fesslersoft\Desktop\ZipTest\Test.zip"
Dim targetDirectory As String = "C:\Users\Fesslersoft\Desktop\ZipTest\" 

Using zip As Ionic.Zip.ZipFile = Ionic.Zip.ZipFile.Read(zipFile)
	zip.Password = "1234"
	zip.ExtractAll(targetDirectory, Ionic.Zip.ExtractExistingFileAction.DoNotOverwrite)
End Using

Console.WriteLine("Zip file has been successfully extracted.")
Console.Read()

Dim zipFile As String = "C:\Users\Fesslersoft\Desktop\ZipTest\Test.zip"

Dim targetDirectory As String = "C:\Users\Fesslersoft\Desktop\ZipTest\"

Using zip As Ionic.Zip.ZipFile = Ionic.Zip.ZipFile.Read(zipFile)

zip.Password = "1234"

zip.ExtractAll(targetDirectory, Ionic.Zip.ExtractExistingFileAction.DoNotOverwrite)

End Using

Console.WriteLine("Zip file has been successfully extracted.")

Console.Read()

[/tab]

[end_tabset]

If you have any questions or suggestions feel free to rate this snippet, post a comment or Contact Us via Email.

Related links:

DotNetZip – Zip and Unzip in C#, VB, any .NET language

Base64 encoder and decoder

Administrator

November 11, 2015

0 Comments

We have just released the first tool in our new Tools section. It’s a unicode compatible base64 encoder/decoder, which uses HTML, AngularJs and Bootstrap.

Base64 Encoder/Decoder

If you have any questions or suggestions feel free post a comment or Contact Us via Email.

How to use a specific encoding using XDocument in C# and VB.NET

Administrator

November 10, 2015

0 Comments

To use a specific encoding when working with XDocument, you can create an XDeclaration.

Samples

C# Sample

XDocument xDoc = new XDocument();
xDoc.Declaration = new XDeclaration("1.0", "ISO-8859-1", "");

1 2	XDocument xDoc = new XDocument(); xDoc.Declaration = new XDeclaration("1.0", "ISO-8859-1", "");

VB.NET Sample

Dim xDoc As New XDocument()
xDoc.Declaration = New XDeclaration("1.0", "ISO-8859-1", "")

1 2	Dim xDoc As New XDocument() xDoc.Declaration = New XDeclaration("1.0", "ISO-8859-1", "")

If you have any questions or suggestions feel free to rate this snippet, post a comment or Contact Us via Email.

Related links:

CODE SNIPPETS

CODESNIPPETS FOR .NET, SQL, JAVASCRIPT, JQUERY and more

Month: November 2015

How to export Gridview data to Excel using devexpress XtraGrid

Samples

C# Sample

VB.NET Sample

How to select an element with its name attribute in jQuery

Samples

Javascript Sample

XML Formatter

UUID / GUID generator

MD5 Hash creator

How to extract a password protected zip file using DotNetZip in C# and VB.NET

Samples

Base64 encoder and decoder

How to use a specific encoding using XDocument in C# and VB.NET

Samples

C# Sample

VB.NET Sample