Export to Excel new way:c#,sql stored procedure

Create a windows project
add a class named as ExcelExportUtility:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Xml;

namespace ExcelExport
{
///

/// Summary description for ExcelExportUtility.
///

public class ExcelExportUtility
{
private int sheetNameColumnOrdinal = -1;
private string defaultFilename = "ExcelExportRequest";
private string mFilePath = "";
public ExcelExportUtility(string fileName, string filePath)
{
mFilePath = filePath;
defaultFilename = fileName;
}

///

/// Main method for exporting to Excel.
///

///
public void Export(DataSet data)
{
string outputFile;
outputFile = mFilePath + defaultFilename + ".xls";

XmlWriterSettings settings = new XmlWriterSettings();
settings.Indent = true;

using (XmlWriter xw = XmlWriter.Create(outputFile,settings))
{
//Required namespaces used for SpreadsheetML standard.
xw.WriteStartDocument();
xw.WriteProcessingInstruction("mso-application", "Excel.Sheet");
xw.WriteStartElement("Workbook", "urn:schemas-microsoft-com:office:Spreadsheet");
xw.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
xw.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
xw.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:Spreadsheet");
xw.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40");

WriteHeaderInfo(xw);

//Iterate the tables in the dataset.
//Each table will become a tab or sheet in the workbook.
foreach (DataTable dt in data.Tables)
{
//Default the tab name to either the table name
//or pull tab name from first column of each resultset.
string sheetName;
if (dt.Rows.Count > 0)
sheetName = dt.Rows[0][sheetNameColumnOrdinal].ToString();
else
sheetName = dt.TableName;

//Start of a tab
xw.WriteStartElement("Worksheet");
xw.WriteAttributeString("ss", "Name", null, sheetName);

xw.WriteStartElement("Table");
xw.WriteAttributeString("ss", "DefaultColumnWidth", null, "100");

//Write out header data
xw.WriteStartElement("Row");
//Format column headings
foreach (DataColumn dc in dt.Columns)
{
if (dc.Ordinal != sheetNameColumnOrdinal)
{
xw.WriteStartElement("Cell");
xw.WriteAttributeString("ss", "StyleID", null, "Header");
xw.WriteStartElement("Data");
xw.WriteAttributeString("ss", "Type", null, "String");
xw.WriteString(dc.ColumnName);
xw.WriteEndElement(); //End Data
xw.WriteEndElement(); //End Cell
}
}
xw.WriteEndElement(); //End Row

//Write out row data
foreach (DataRow dr in dt.Rows)
{
xw.WriteStartElement("Row");
foreach (DataColumn dc in dt.Columns)
{
if (dc.Ordinal != sheetNameColumnOrdinal)
{
string dataType;
string style;
string output;

//Set appropriate styling of each cell based on datatype
//This depends on how sql server ends up reporting the datatype.
switch (dc.DataType.ToString())
{
case "System.DateTime":
dataType = "DateTime";
style = "Date";

try
{
output = DateTime.Parse(dr[dc].ToString()).ToString("yyyy-MM-dd");
}
catch (FormatException fe) //date is null or empty in dataset
{
output = "";
}

break;
case "System.Decimal":
case "System.Double":
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
dataType = "Number";
style = "Data";
output = dr[dc].ToString().Trim();
break;
default:
dataType = "String";
style = "Data";
output = dr[dc].ToString().Trim();
break;
}

//if no data then write empty cell node
xw.WriteStartElement("Cell");
xw.WriteStartAttribute("StyleID", "");
xw.WriteString(style);
xw.WriteEndAttribute(); //End Style Attribute
if (output != "")
{
xw.WriteStartElement("Data");
xw.WriteAttributeString("ss", "Type", null, dataType);
xw.WriteString(output);
xw.WriteEndElement(); //End Data

}
xw.WriteEndElement(); //End Cell
}
}
xw.WriteEndElement(); //End Row
}

xw.Flush();
xw.WriteEndElement(); //End Table
xw.WriteEndElement(); //End Worksheet
}

xw.WriteEndElement(); //End Workbook
xw.Flush();
}

}

///

/// Used to set up column headers and data type styling.
///

///
public void WriteHeaderInfo(XmlWriter xw)
{
xw.WriteStartElement("Styles");

//Default styling
xw.WriteStartElement("Style");
xw.WriteAttributeString("ss", "ID", null, "Default");
xw.WriteAttributeString("ss", "Name", null, "Normal");
xw.WriteStartElement("Alignment");
xw.WriteAttributeString("ss", "Vertical", null, "Bottom");
xw.WriteEndElement(); //End Alignment
xw.WriteElementString("Borders","");
xw.WriteElementString("Font", "");
xw.WriteElementString("Interior","");
xw.WriteElementString("NumberFormat","");
xw.WriteElementString("Protection", "");
xw.WriteEndElement(); //End Style

//Header styling
xw.WriteStartElement("Style");
xw.WriteAttributeString("ss", "ID", null, "Header");
xw.WriteStartElement("Borders");
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Bottom");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "2");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Left");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "2");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Right");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "2");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Top");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "2");
xw.WriteEndElement(); //End Border
xw.WriteEndElement(); //End Borders
xw.WriteStartElement("Font");
xw.WriteAttributeString("ss", "Bold",null,"1");
xw.WriteEndElement(); //End Font
xw.WriteStartElement("Interior");
xw.WriteAttributeString("ss", "Color", null, "#C0C0C0");
xw.WriteAttributeString("ss", "Pattern", null, "Solid");
xw.WriteEndElement(); //End Interior
xw.WriteEndElement(); //End Style

//Data styling
xw.WriteStartElement("Style");
xw.WriteAttributeString("ss", "ID", null, "Data");
xw.WriteStartElement("Alignment");
xw.WriteAttributeString("ss", "Vertical",null,"Bottom");
xw.WriteAttributeString("ss", "WrapText", null, "0");
xw.WriteEndElement(); //End Alignment
xw.WriteStartElement("Borders");
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Bottom");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Left");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Right");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Top");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteEndElement(); //End Borders
xw.WriteEndElement(); //End Style

//Date styling
xw.WriteStartElement("Style");
xw.WriteAttributeString("ss", "ID", null, "Date");
xw.WriteStartElement("Alignment");
xw.WriteAttributeString("ss", "Vertical", null, "Bottom");
xw.WriteAttributeString("ss", "WrapText", null, "1");
xw.WriteEndElement(); //End Alignment
xw.WriteStartElement("NumberFormat");
xw.WriteAttributeString("ss", "Format", null, "Short Date");
xw.WriteEndElement(); //End NumberFormat
xw.WriteStartElement("Borders");
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Bottom");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Left");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Right");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Top");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteEndElement(); //End Borders
xw.WriteEndElement(); //End Style

xw.WriteEndElement(); //End Styles
xw.Flush();
}

///

/// Provides the column. ordinal number to use for the sheet name.
/// If not set then the default table names are used.
///

public int SheetNameColumnOrdinal
{
get
{
return sheetNameColumnOrdinal;
}
set
{
sheetNameColumnOrdinal = value;
}
}
///

/// Set default filename. Do not specify extension.
///

public string DefaultFilename
{
get
{
return defaultFilename;
}
set
{
defaultFilename = value;
}
}

public string FilePath
{
get
{
return mFilePath;
}
set
{
mFilePath = value;
}
}
}
}


now open the default form1 and rename as "frmExcelExp". Put two text boxes and one for filename and other for folder path. put the folderbrowserpath control and set the selected path for the folderpath text.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using ExcelExport;
using System.Configuration;
namespace WindowsFormsApplication1
{
public partial class frmExcelExp : Form
{
public frmExcelExp()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
try
{
if (txtfilename.Text.Trim() == "")
{
MessageBox.Show("");
return;
}
if (txtfilepath.Text.Trim() == "")
{
MessageBox.Show("");
return;
}
string constring = Properties.Settings.Default.connstr;
DataSet exportData = new DataSet();
SqlConnection conn = new SqlConnection(constring);

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.CommandText = "MyExcelDemo";
cmd.Parameters.Add("@DateIn", SqlDbType.VarChar);
cmd.Parameters.Add("@DateOut", SqlDbType.VarChar);
cmd.Parameters["@DateIn"].Value = "01/01/2006";
cmd.Parameters["@DateOut"].Value = "01/01/2009";


SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(exportData);

ExcelExportUtility exportUtil = new ExcelExportUtility(txtfilename.Text.Trim(), txtfilepath.Text.Trim());
//This allows for flexible naming of the tabs in the workbook
exportUtil.SheetNameColumnOrdinal = 0;
exportUtil.Export(exportData);
conn.Close();
}
catch (Exception oe)
{
MessageBox.Show(oe.Message);
}

}

private void button2_Click(object sender, EventArgs e)
{

folderBrowserDialog1.ShowDialog( );
txtfilepath.Text = folderBrowserDialog1.SelectedPath;

}
}
}

now create the sql storedprocedure

CREATE PROCEDURE MyExcelDemo(@DateIn varchar(10),@DateOut varchar(10))
AS
BEGIN

SET DATEFORMAT dmy
--HERE 'SheetTest' IS THE EXCEL TAB NAME
SELECT 'SheetTest',* FROM yourtable WHERE convert(datetime,[DateTime],103) BETWEEN convert(datetime,@DateIn,103) AND convert(datetime,@DateOut,103)
END

Now build the project and run it

Exporting to Excel Using a CLR Stored Procedure:SQL,C#

Introduction

Like most of my jobs, my current one requires a lot of simple reports to Excel. Nothing fancy, no serious formatting requirements beyond looking good which in most cases means good column headings and correctly aligned data. DTS and SSIS has most of the time served me well in doing this, but can be rather cumbersome for just a simply exporting the results of a stored procedure.

The solution discussed here has been tested on both 32-bit SQL 2005 and 64-bit SQL 2008, however any scripts are for SQL 2008 so if there are any differences I apologize.
Solution

During a discussion about Excel one of the C# developers I work with, Nick Hanson, he mentioned that he had found an easy way to write results from C# to Excel. We then looked into what was required to make this code into a CLR stored procedure for SQL. Turned out this was really easy and he quickly had a test solution for me. The code to this is available for download. The solution is a CLR stored procedure that takes a stored procedure as a parameter, and puts the output of the stored procedure passed in as a parameter to an Excel file, the only modification needed to use this compared to a regular stored procedure is that the results in the first column becomes the name of the spreadsheet.

The attached DLL will do the work, but the source code is also included to compile it yourself. Visual Studio 2008 was used for this project.

Copy the DLL to whatever directory you want your CLR DLL's to be stored in, in my case that is C:\CLR

The following steps will set this up in a database. First enable CLR on the server:

sp_configure'clr',1
reconfigure

Next step is to set the database to TRUSTWORTHY. Be sure you understand the ramifications of doing this, it should not be take lightly changing this setting.

ALTER DATABASE ProdReports2008 SET TRUSTWORTHY ON

Then we will create the assembly:

CREATE ASSEMBLY ExportToExcel
FROM 'C:\CLR\ExcelExport.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

Associate a stored procedure name with that assembly:

CREATE PROCEDURE[dbo].[prc_ExportToExcel]
@proc [nvarchar](100),
@path [nvarchar](200),
@filename [nvarchar](100),
@params xml
AS
EXTERNAL NAME [ExportToExcel].[StoredProcedures].[ExportToExcel]

At this point you have a stored procedure that can be called, that will export to Excel. The prototype call for this procedure is:

Declare @params xml
Set @params = ''
exec prc_ExportToExcel 'procname',‘Drive:\Directory\', 'Filename', @params

Note that filename is without XLS at the end, XLS will be added on automatically.

The parameters here are what you would normally use in the stored procedure passed into prc_ExportToExcel, each parameter has to be defined in the XML variable @params. The parameters to pass into the stored procedure is in XML, at a minimum that line needs to have empty in it:

Set @params ='' -- this will work for a proc with no parameters

For example:

exec prc_AndersDemo @FirstName = 'Anders'

When called to export to Excel would look like this:

Declare @paramsxml
Set @params=''
exec prc_ExportToExcel 'prc_AndersDemo', ‘Drive:\Directory\', 'Filename', @params

To be able to change the Tab name for the worksheets in Excel, the first column in the stored procedure must hold the name you want on the Tab. Currently we do not have a way to NOT do this, so if you do not put on one it will take whatever is in the first record and make the column header.

I wrote a quick stored procedure to demo this:

create procedureAndersExcelDemo
as
begin
select 'sysobjects',*fromsys.objects
select 'syscolumns',*fromsys.columns
end

The call to create an Excel file for my directory structure, note that the directory this is saved to is local seen from the SQL Server it is executed as, but it will work to network shares as long as the account SQL is running under has the appropriate permissions. The below code will create a file on the SQL Server it is executed on in the C:\Anders directory called AndersDemo.XLS.

declare @params XML
set @params=''
exec prodreports2008.dbo.prc_ExportToExcel 'AndersTest.dbo.AndersExcelDemo', 'C:\Anders\', 'AndersDemo', @params

The resulting Excel file looks like this:

Data exported to Excel

Note how it creates 2 spreadsheets, one corresponding to each result set in the stored procedures. I have not tested what the limits is for how many can be created, but one I have in production is running fine with 15.

I hope this can be of use to some of you. Comments and suggestions for improvements are welcome.

By Anders Pedersen, 2009/12/02

How to access storedprocedure with parameter : c#

public bool InsertACustomer(string custName, string address1, string tp, string fax)
{

SqlParameter [] prams = new SqlParameter[5];

prams[0] = new SqlParameter("@name", custName);
prams[1] = new SqlParameter("@address1", address1);
prams[2] = new SqlParameter("@tp", tp);
prams[3] = new SqlParameter("@fax", fax);
prams[4] = new SqlParameter("@id",SqlDbType.Int);
prams[4].Direction = ParameterDirection.Output;


bool isTrue = base.CallSP("AddCustomer", prams);

if (isTrue)
{
string id = prams[4].Value.ToString();

return true;
}
else
{
return false;
}


}

How to use datareader with Storedprocedure:C#

using System;
using System.Data.SqlClient;
using System.Data;

namespace DataAccess
{
public class DBConnection
{


string sqlConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

SqlDataReader rdr = null;
SqlConnection con = null;
SqlCommand cmd = null;

public DataTable ReadData(string qry)
{
try
{

DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = qry;
cmd.CommandType = CommandType.Text;

con.Open();
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(rdr);

return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
if(rdr != null)
rdr.Close();
//con.Close();
}
}

public bool CallSP(string spName, SqlParameter[] param)
{
con = new SqlConnection(sqlConnectionString);
bool toReturn;

try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);

con.Open();
cmd.ExecuteNonQuery();
toReturn = true;

}
catch (Exception ex)
{
toReturn = false;
}
finally
{
con.Close();
}

return toReturn;

}

public DataTable CallSP(string spName, SqlParameter[] param, bool isReturn)
{
DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);

try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);

con.Open();
rdr = cmd.ExecuteReader();
dt.Load(rdr);
return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
con.Close();
}
}


public DataTable CallSP(string spName, bool isReturn)
{
DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);

try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;


con.Open();
rdr = cmd.ExecuteReader();
dt.Load(rdr);
return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
con.Close();
}
}


}
}

Out Parameter in SQL:SQL server 2005

CREATE PROCEDURE [dbo].[AddCustomer]
-- Add the parameters for the stored procedure here
@name nvarchar(50),
@address1 nvarchar(50),
@tp nvarchar(12),
@fax nvarchar(12),
@id int output

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO Customer
([Name], Address1, TP, FAX)
VALUES (@name, @address1, @tp, @fax)


SET @id = (SELECT SCOPE_IDENTITY())


END