Converting Table to Excel with Header of table
This Code given below is about we have a data in our database where we should give it to other users by excel then we don't want to do those process manual. Just the below code helps you by processing the table content to excel with its header included.
Converting Table to Excel with Header of table
C# .Net Coding:
Here we can see how to convert a Sql Server table to excel using c#.net coding. To this process we need to add Microsoft office interop reference version 12.0 for 2007 format excel. And we declare our connection string in web config file. we should create an object for our excel workbook and for excel worksheet. The below coding will explain this concept.
using System;
using Microsoft.VisualBasic;
using System.Collections.Generic;
using System.Configuration;
using System.Collections;
using System.Data;
using System.Diagnostics;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
using System.Xml;
using System.IO;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Runtime.InteropServices;
public partial class UsingToolkit_Default : System.Web.UI.Page
{
//Add InteropServices to your References.
public string ConStr = (string)(ConfigurationManager.AppSettings["ConnectionString"]);
// ConnectionString from web config file.
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook wb ;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misvalue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Range range;
protected void Button1_Click(object sender, EventArgs e)
{
string ResultTable = null;
ResultTable = "EarnedIRR_ResultExcel";
SqlConnection sqlConn = null;
sqlConn = new SqlConnection(ConStr);
sqlConn.Open();
SqlCommand RdCommand = new SqlCommand("USP_CreateTextFile", sqlConn);
RdCommand.CommandTimeout = 0;
RdCommand.CommandType = CommandType.StoredProcedure;
SqlParameter myParm1 = RdCommand.Parameters.Add("@TableName", SqlDbType.VarChar, 500);
myParm1.Value = "UnoReports.dbo." + ResultTable;
SqlParameter myParm2 = RdCommand.Parameters.Add("@TextFileName", SqlDbType.VarChar, 50);
myParm2.Value = ResultTable;
RdCommand.ExecuteNonQuery();
//-------------------------------------------------------------------------------------------
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook wb;
Microsoft.Office.Interop.Excel.Workbooks wbs;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
wbs = xlApp.Workbooks;
object missing = System.Reflection.Missing.Value;
//----------------------------------------------------------------------------------------------
//To open an new excel sheet for your data to be placed inside it.
wb = xlApp.Workbooks.Open("Z:\\" + ResultTable + ".txt", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//The Z drive is nothing but you should configure your server to your system in this drive where the excel should be placed.
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
//If you have datetime column use this formet.
range = xlWorkSheet.get_Range("C:C", Type.Missing);
range.Select();
range.NumberFormat = "dd-mmm-yyyy;@";
range = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1,1];
Microsoft.Office.Interop.Excel.Range row = range.EntireRow;
row.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, false);
range = xlWorkSheet.get_Range("A1", "C1");
range.Select();
range.Font.Bold=true;//Will change the header row to Bold.
//The below code is to write your header name of your table in excel sheet.
xlWorkSheet.Cells[1, 1] = "COLUMN1";
xlWorkSheet.Cells[1, 2] = "COLUMN2";
xlWorkSheet.Cells[1, 3] = "COLUMN3";
//To Save your excel in server path.
wb.SaveAs("Z:\\" + ResultTable + ".xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
xlApp = null;
//To clear the memory.
GC.Collect();
}
}Stored Procedure:
And we use one stored procedure above to convert our table to text so that we can retrive easily from text to excel and the Stored Procedure is given below
--Exec [USP_CreateTextFile] 'Table_to_Text','Table_to_Text'
CREATE PROCEDURE [dbo].[USP_CreateTextFile]
(
@TableName VARCHAR(500),
@TextFileName VARCHAR(50)
)
AS
BEGIN
Declare @Qry VARCHAR(500)
SET @Qry = 'bcp "SELECT * from '+@TableName +'"' +' queryout "D:\Share\' + @TextFileName + '.txt" -c -T -x'
-- The above path should be in your server not in your local machine.
PRINT @Qry
EXEC master..xp_cmdshell @Qry
END