Stories about everyday life

ExportData Table to Excel using C#

// February 15th, 2010 // C#, Database, Programming

Here is little class that I wrote some time ago that helps you export data from DataTable into Excell xls file. It format fields in xls file based on data type from data table.

It’s not a perfect but it will help you to quickly save data from Data Table to Excel file.

Example of usage:

Common.Data.Export2Excel(dataTableWithData, "exported.xls");

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace Common.Data
{
	public class Export2Excel
	{
		public static bool Export(DataTable tbl, string fileName, bool createTable, bool overwriteFile)
		{
      string tableName = tbl.TableName.Replace(" ", "_");
			tableName = String.IsNullOrEmpty(tableName) ? "Sheet1" : tableName;
			try
			{
				System.Globalization.CultureInfo _infoEn = System.Globalization.CultureInfo.GetCultureInfo("en-GB");
				string sql = "";
				if (overwriteFile)
					if (File.Exists(fileName))
						File.Delete(fileName);

				using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=Yes'"))
				{
					con.Open();
					OleDbCommand cmdInsert;
					if (createTable || !File.Exists(fileName))
					{
						sql = "CREATE TABLE " + tableName + " (";
						for (int i = 0; i < tbl.Columns.Count; i++)
						{
							sql += tbl.Columns[i].ColumnName;
							if (i + 1 == tbl.Columns.Count) //Here we decide should we close insert command or appebd another create column command
								sql += " " + GetColumnType(tbl.Columns[i]) + ")"; //Close insert
							else
								sql += " " + GetColumnType(tbl.Columns[i]) + ","; //there is more columns to add
						}
					}
					if (!String.IsNullOrEmpty(sql))
					{
						cmdInsert = new OleDbCommand(sql, con);
						cmdInsert.ExecuteNonQuery();
					}
					foreach (DataRow row in tbl.Rows)
					{
						//Dodati parametre na comandu
						string values = "(";
						for (int i = 0; i < tbl.Columns.Count; i++)
						{
							if (i + 1 == tbl.Columns.Count)
							{
								if (tbl.Columns[i].DataType == System.Type.GetType("System.Decimal") ||
									 tbl.Columns[i].DataType == System.Type.GetType("System.Int64") ||
									 tbl.Columns[i].DataType == System.Type.GetType("System.Double"))
									values += String.IsNullOrEmpty(row[i].ToString()) ? "0)" : Convert.ToDecimal(row[i]).ToString("#0.00", _infoEn) + ")";
								else
									values += "'" + System.Security.SecurityElement.Escape(row[i].ToString()) + "')";
							}
							else
							{
								if (tbl.Columns[i].DataType == System.Type.GetType("System.Decimal") ||
									 tbl.Columns[i].DataType == System.Type.GetType("System.Int64") ||
									 tbl.Columns[i].DataType == System.Type.GetType("System.Double"))
									values += String.IsNullOrEmpty(row[i].ToString()) ? "0," : Convert.ToDecimal(row[i]).ToString("#0.00", _infoEn) + ",";
								else
									values += "'" + System.Security.SecurityElement.Escape(row[i].ToString()) + "',";
							}
						}
						string sqlInsert = String.Format("Insert into [{0}$] VALUES {1}", tableName, values);
						cmdInsert = new OleDbCommand(sqlInsert, con);

						cmdInsert.ExecuteNonQuery();
					}
				}
			}
			catch (Exception)
			{
				return false;
			}
				return true;
		}

		private static string GetColumnType(DataColumn dataColumn)
		{
			string t;
			if (dataColumn.DataType == System.Type.GetType("System.Decimal"))
				t = "decimal";
			else if (dataColumn.DataType == System.Type.GetType("System.Int64"))
				t = "INT";
			else if (dataColumn.DataType == System.Type.GetType("System.Double"))
				t = "double";
			else
				t = "VARCHAR(255)";
			return t;
		}
		public static bool Export(DataTable tbl, string fileName)
		{
			return Export(tbl, fileName, true, true);
		}
	}
}