Last updated: 29th April 2024
Using Microsoft’s Interop namespace, we can export data to any version of Excel. In addition, I'll show you how to autofomat an excel sheet dynamically in Asp.Net.I have added Code for C#. In-addition, I have added a feature to Download Excel file after exporting the data. Thank you Keith Jackson for your suggestion on download (see message below).
👉 Windows Forms - Export Data to an Excel Sheet with AutoFormat Feature using C#
Interop namespace
Imports Excel = Microsoft.Office.Interop.Excel
Create a new website and add few controls on your web page. I'll keep this example short and simple. We need a button to export the data and two more buttons to view or donwload the excel file, once we have exported the data to excel.
The Markup
<!DOCTYPE html>
<html>
<head>
<title>Export to Excel in Asp.Net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<!-- BUTTON CONTROL TO EXPORT DATA TO EXCEL. -->
<p><input type="button" onserverclick="ExportToExcel"
value="Export data to Excel" runat="server" /></p>
<!-- SHOW MESSAGE. -->
<p><asp:Label ID="lblConfirm" Text="" runat="server"></asp:Label></p>
<div>
<!-- VIEW BUTTON WILL OPEN THE EXCEL FILE FOR VIEWING. -->
<div style="float:left;padding-right:10px;">
<input type="button" onserverclick="ViewData"
id="btView" value="View Data" runat="server"
style="display:none;" />
</div>
<!--DOWNLOAD EXCEL FILE. -->
<div style="float:left;">
<asp:Button ID="btDownLoadFile" Text="Download"
OnClick="DownLoadFile" runat="server" style="display:none;" />
</div>
</div>
</div>
</form>
</body>
</html>Before starting with the code behind procedure, create the Employee Details table in your database. We will extract data from this table and export it to an excel file, dynamically.
Add a COM reference: "Microsoft Excel 12.0 Object Library"
Code behind (C#)
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat;
public partial class _Default : System.Web.UI.Page
{
protected void ExportToExcel(object sender, EventArgs e)
{
// SET THE CONNECTION STRING.
string sCon = "Data Source=DNA;Persist Security Info=False;Integrated Security=SSPI;" +
"Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=;Connect Timeout=30;";
using (SqlConnection con = new SqlConnection(sCon))
{
using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.EmployeeDetails"))
{
SqlDataAdapter sda = new SqlDataAdapter();
try
{
cmd.Connection = con;
con.Open();
sda.SelectCommand = cmd;
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
string path = Server.MapPath("exportedfiles\\");
if (!Directory.Exists(path)) // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
{
Directory.CreateDirectory(path);
}
File.Delete(path + "EmployeeDetails.xlsx"); // DELETE THE FILE BEFORE CREATING A NEW ONE.
// ADD A WORKBOOK USING THE EXCEL APPLICATION.
Excel.Application xlAppToExport = new Excel.Application();
xlAppToExport.Workbooks.Add("");
// ADD A WORKSHEET.
Excel.Worksheet xlWorkSheetToExport = default(Excel.Worksheet);
xlWorkSheetToExport = (Excel.Worksheet)xlAppToExport.Sheets["Sheet1"];
// ROW ID FROM WHERE THE DATA STARTS SHOWING.
int iRowCnt = 4;
// SHOW THE HEADER.
xlWorkSheetToExport.Cells[1, 1] = "Employee Details";
Excel.Range range = xlWorkSheetToExport.Cells[1, 1] as Excel.Range;
range.EntireRow.Font.Name = "Calibri";
range.EntireRow.Font.Bold = true;
range.EntireRow.Font.Size = 20;
xlWorkSheetToExport.Range["A1:D1"].MergeCells = true; // MERGE CELLS OF THE HEADER.
// SHOW COLUMNS ON THE TOP.
xlWorkSheetToExport.Cells[iRowCnt - 1, 1] = "Employee Name";
xlWorkSheetToExport.Cells[iRowCnt - 1, 2] = "Mobile No.";
xlWorkSheetToExport.Cells[iRowCnt - 1, 3] = "PresentAddress";
xlWorkSheetToExport.Cells[iRowCnt - 1, 4] = "Email Address";
int i;
for (i = 0; i <= dt.Rows.Count - 1; i++)
{
xlWorkSheetToExport.Cells[iRowCnt, 1] = dt.Rows[i].Field<string>("EmpName");
xlWorkSheetToExport.Cells[iRowCnt, 2] = dt.Rows[i].Field<string>("Mobile");
xlWorkSheetToExport.Cells[iRowCnt, 3] = dt.Rows[i].Field<string>("PresentAddress");
xlWorkSheetToExport.Cells[iRowCnt, 4] = dt.Rows[i].Field<string>("Email");
iRowCnt = iRowCnt + 1;
}
// FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
Excel.Range range1 = xlAppToExport.ActiveCell.Worksheet.Cells[4, 1] as Excel.Range;
range1.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList3);
// SAVE THE FILE IN A FOLDER.
xlWorkSheetToExport.SaveAs(path + "EmployeeDetails.xlsx");
// CLEAR.
xlAppToExport.Workbooks.Close();
xlAppToExport.Quit();
xlAppToExport = null;
xlWorkSheetToExport = null;
lblConfirm.Text = "Data Exported Successfully";
lblConfirm.Attributes.Add("style", "color:green; font: normal 14px Verdana;");
btView.Attributes.Add("style", "display:block");
btDownLoadFile.Attributes.Add("style", "display:block");
}
}
catch (Exception ex)
{
lblConfirm.Text = ex.Message.ToString();
lblConfirm.Attributes.Add("style", "color:red; font: bold 14px/16px Sans-Serif,Arial");
}
finally
{
sda.Dispose();
sda = null;
}
}
}
}
// VIEW THE EXPORTED EXCEL DATA.
protected void ViewData(object sender, System.EventArgs e)
{
string path = Server.MapPath("exportedfiles\\");
try
{
// CHECK IF THE FOLDER EXISTS.
if (Directory.Exists(path))
{
// CHECK IF THE FILE EXISTS.
if (File.Exists(path + "EmployeeDetails.xlsx"))
{
// SHOW (NOT DOWNLOAD) THE EXCEL FILE.
Excel.Application xlAppToView = new Excel.Application();
xlAppToView.Workbooks.Open(path + "EmployeeDetails.xlsx");
xlAppToView.Visible = true;
}
}
}
catch (Exception ex)
{
//
}
}
// DOWNLOAD THE FILE.
protected void DownLoadFile(object sender, EventArgs e)
{
try
{
string sPath = Server.MapPath("exportedfiles\\");
Response.AppendHeader("Content-Disposition", "attachment; filename=EmployeeDetails.xlsx");
Response.TransmitFile(sPath + "EmployeeDetails.xlsx");
Response.End();
}
catch (Exception ex) { }
}
}
Code behind (VB)
Option Explicit On Imports System.Data ' FOR "DataTable" Imports System.Data.SqlClient Imports System.IO ' FOR FILE ACCESS. Imports Excel = Microsoft.Office.Interop.Excel Imports ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat Partial Class _Default Inherits System.Web.UI.Page Protected Sub btExport_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles btExport.ServerClick ' SET THE CONNECTION STRING. Dim sCon As String = "Data Source=dna;Persist Security Info=False;Integrated Security=SSPI;" & _ "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=demo;Connect Timeout=30;" Using con As SqlConnection = New SqlConnection(sCon) Using cmd As SqlCommand = New SqlCommand("SELECT *FROM dbo.EmployeeDetails") Dim sda As SqlDataAdapter = New SqlDataAdapter Try cmd.Connection = con : con.Open() sda.SelectCommand = cmd Dim dt As DataTable = New DataTable sda.Fill(dt) If dt.Rows.Count > 0 Then Dim path As String = Server.MapPath("exportedfiles\") ' CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER. If Not Directory.Exists(path) Then Directory.CreateDirectory(path) End If File.Delete(path & "EmployeeDetails.xlsx") ' DELETE THE FILE BEFORE CREATING A NEW ONE. ' ADD A WORKBOOK USING THE EXCEL APPLICATION. Dim xlAppToExport As New Excel.Application xlAppToExport.Workbooks.Add() ' ADD A WORKSHEET. Dim xlWorkSheetToExport As Excel.Worksheet xlWorkSheetToExport = xlAppToExport.Sheets("Sheet1") ' ROW ID FROM WHERE THE DATA STARTS SHOWING. Dim iRowCnt As Integer = 4 With xlWorkSheetToExport ' SHOW THE HEADER. .Cells(1, 1).value = "Employee Details" .Cells(1, 1).FONT.NAME = "Calibri" .Cells(1, 1).Font.Bold = True .Cells(1, 1).Font.Size = 20 .Range("A1:D1").MergeCells = True ' MERGE CELLS OF THE HEADER. ' SHOW COLUMNS ON THE TOP. .Cells(iRowCnt - 1, 1).value = "Employee Name" .Cells(iRowCnt - 1, 2).value = "Mobile No." .Cells(iRowCnt - 1, 3).value = "PresentAddress" .Cells(iRowCnt - 1, 4).value = "Email Address" For i = 0 To dt.Rows.Count - 1 .Cells(iRowCnt, 1).value = dt.Rows(i).Item("EmpName") .Cells(iRowCnt, 2).value = dt.Rows(i).Item("Mobile") .Cells(iRowCnt, 3).value = dt.Rows(i).Item("PresentAddress") .Cells(iRowCnt, 4).value = dt.Rows(i).Item("Email") iRowCnt = iRowCnt + 1 Next ' FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION. xlAppToExport.ActiveCell.Worksheet.Cells(4, 1).AutoFormat( _ ExcelAutoFormat.xlRangeAutoFormatList3) End With ' SAVE THE FILE IN A FOLDER. xlWorkSheetToExport.SaveAs(path & "EmployeeDetails.xlsx") ' CLEAR. xlAppToExport.Workbooks.Close() : xlAppToExport.Quit() xlAppToExport = Nothing : xlWorkSheetToExport = Nothing lblConfirm.Text = "Data Exported Sucessfully" lblConfirm.Attributes.Add("style", "color:green; font: normal 14px Verdana;") btView.Attributes.Add("style", "display:block") btDownLoadFile.Attributes.Add("style", "display:block") End If Catch ex As Exception lblConfirm.Text = ex.Message lblConfirm.Attributes.Add("style", "color:red; font: bold 14px/16px Sans-Serif,Arial") Finally sda.Dispose() : sda = Nothing End Try End Using End Using End Sub ' VIEW THE EXPORTED EXCEL DATA. Protected Sub btView_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles btView.ServerClick Dim path As String = Server.MapPath("exportedfiles\") Try If Directory.Exists(path) Then ' CHECK IF THE FOLDER EXISTS. If File.Exists(path & "EmployeeDetails.xlsx") Then ' CHECK IF THE FILE EXISTS. ' SHOW (NOT DOWNLOAD) THE EXCEL FILE. Dim xlAppToView As New Excel.Application xlAppToView.Workbooks.Open(path & "EmployeeDetails.xlsx") xlAppToView.Visible = True End If End If Catch ex As Exception ' End Try End Sub ' DOWNLOAD THE FILE. Protected Sub DownLoadFile(ByVal sender As Object, ByVal e As EventArgs) Try Dim sPath As String = Server.MapPath("exportedfiles\") Response.AppendHeader("Content-Disposition", "attachment;filename=EmployeeDetails.xlsx") Response.TransmitFile(sPath & "EmployeeDetails.xlsx") Response.[End]() Catch ex As Exception End Try End Sub End Class
Now, you can also export data from Paging enabled GridView to Excel in Asp.Net. Check out this article.
There are so many ways you can use this format rich data once exported to excel. Email it, print it or share the file online on Skype etc.
Conclusion
Now you know how to export data into an excel file, particularly its new versions. However, it is not a recommend procedure for obvious reasons; there is no harm in knowing and learning this technique. You might use it in your personal project or even for you client, if they approve. In addition we have seen how to autoformat the excel sheet, dynamically, once you have exported the data. You can experiment with other available autoformat options.