Hi! I want export data from SQL to Excel by using Asp.Net. After click button show below error.
USE [SKL_KOM_INST]
GO
/****** Object: Table [dbo].[EmployeeDetails] Script Date: 02/03/2017 17:55:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeDetails](
[EmpName] [nchar](10) NULL,
[Mobile] [nchar](10) NULL,
[PresentAddress] [nchar](10) NULL,
[Email] [nchar](10) NULL
) ON [PRIMARY]
GO
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="ExportToExcel.Styles.WebForm1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<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 id="Button1" 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>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat;
namespace ExportToExcel.Styles
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void ExportToExcel(object sender, EventArgs e)
{
string sCon = "Data Source=169.168.110.156;Persist Security Info=False;Integrated Security=SSPI;" +
"Initial Catalog=SKL_KOM_INST;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))
{
Directory.CreateDirectory(path);
}
File.Delete(path + "EmployeeDetails.xlsx");
Excel.Application xlAppToExport = new Excel.Application();
xlAppToExport.Workbooks.Add("");
Excel.Worksheet xlWorkSheetToExport = default(Excel.Worksheet);
xlWorkSheetToExport = (Excel.Worksheet)xlAppToExport.Sheets["Sheet1"];
int iRowCnt = 4;
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;
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;
}
Excel.Range range1 = xlAppToExport.ActiveCell.Worksheet.Cells[4, 1] as Excel.Range;
range1.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList3);
xlWorkSheetToExport.SaveAs(path + "EmployeeDetails.xlsx");
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;
}
}
}
}
protected void ViewData(object sender, System.EventArgs e)
{
string path = Server.MapPath("exportedfiles\\");
try
{
if (Directory.Exists(path))
{
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) { }
}
}
}
