Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Exporting SQL Server table data to Excel

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 277
    Comment on it

    Hi Readers !
    In this Blog we will find how we can Export SQL Server table data to Excel using .net code .

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data.SqlClient;
    using Microsoft.Office.Interop.Excel ;
    using Microsoft.Office.Interop;
    using System.IO;
    using System.Data;
    
    namespace ExportToExcel
    {
        public partial class ExportToExcel : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                //PAGE LOAD EVENT
            }
    
            //Here  we are using C#( ASP.Net) code and Microsoft Office original DLL file in order to extract data from a table from SQL Server and export it to standard Excel file.
            protected void ExportSQLTableDataToExcel(object sender, EventArgs e)
            {
               String strSqlConn;
               SqlConnection sqlConn = new SqlConnection("Server=localhost;Initial Catalog=SSIS_DB;Integrated security=SSPI"); // CONNECTION  USED TO OPEN SQL SERVER DATABASE
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                System.Data.DataTable dataTableToExport=new System.Data.DataTable();
                try
                {
                    strSqlConn = "SELECT * FROM dbo.EMP"; // SELECT QUERY FOR FETCHING DATA TO BE EXPORTED
    
                    sqlConn.Open(); 
                    SqlCommand sqlCommand = new SqlCommand(strSqlConn, sqlConn); // SQL STATEMENT TO EXECUTE AGAINST A SQL SERVER DATABASE
                    sqlDataAdapter.SelectCommand = sqlCommand;
    
                    // ADDING A WORKBOOK USING THE EXCEL APPLICATION.
                    Microsoft.Office.Interop.Excel.Application appToExportToxls = new Microsoft.Office.Interop.Excel.Application();
                    appToExportToxls.Workbooks.Add("");
    
                    // ADDING A WORKSHEET.
                    Microsoft.Office.Interop.Excel.Worksheet workSheetToExportToxls = default(Microsoft.Office.Interop.Excel.Worksheet);
                    workSheetToExportToxls = (Microsoft.Office.Interop.Excel.Worksheet)appToExportToxls.Sheets["Sheet1"];
    
    
                    sqlDataAdapter.Fill(dataTableToExport);
                    sqlConn.Close();
    
    
                    string xlsFilePath = Server.MapPath("ExportedFileFolder\\");
    
                    if (!Directory.Exists(xlsFilePath))   // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
                            {
                                Directory.CreateDirectory(xlsFilePath);
                            }
                    File.Delete(xlsFilePath + "EmpDetails.xlsx"); // DELETE THE FILE BEFORE CREATING A NEW ONE.
    
    
                    //Export the Columns to excel file
                    for (int irowIndex = 0; irowIndex < dataTableToExport.Columns.Count; irowIndex++)
                    {
                        workSheetToExportToxls.Cells[1, irowIndex + 1] = dataTableToExport.Columns[irowIndex].ColumnName;
                    }
    
    
    
                    //Export the rows to excel file
                    for (int irowIndex = 0; irowIndex < dataTableToExport.Rows.Count; irowIndex++)
                   {
                       for (int iColumnIndex = 0; iColumnIndex < dataTableToExport.Columns.Count; iColumnIndex++)
                        {
                            workSheetToExportToxls.Cells[irowIndex + 2, iColumnIndex + 1] = dataTableToExport.Rows[irowIndex][dataTableToExport.Columns[iColumnIndex]];
                        }
                   }
    
                    workSheetToExportToxls.Columns.AutoFit();
    
                    // SAVE THE FILE TO THE FOLDER.
                    workSheetToExportToxls.SaveAs(xlsFilePath + "EmpDetails.xlsx");
    
                            // CLEAR ALL OBJECTS
                            appToExportToxls.Workbooks.Close();
                            appToExportToxls.Quit();
                            appToExportToxls = null;
                            workSheetToExportToxls = null;
                }
                catch (System.Exception ex)
                {
                    // HANDLE EXCEPTION
                }
                finally
                {
                    if (sqlConn.State == ConnectionState.Open)
                    {
                        sqlConn.Close();
                    }
                }
            }
        }
    }
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: