Export to Excel from GridView Control with Paging and Localization

Hi there,

Here, I am Going to Show you Export To Excel from GridView Control. This will work under Localization because throgh this method you can export Localized Text Data Also In Excel. This Example will work with Paging enabled GridView Control also.

Here, your Button Click event is written: in which data export into excel logic is written. When you use Paging Then that time you have to fetch fresh data from database and fill this dataset; means you will export all data without paging into excel.

Code Block

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
try
{
//fetch data from DB for Exporting Excel File
dstMain = GetDataFromDataSource();//With this method you can fill your DataSet
if (dstMain == null)
return;
if (dstMain.Tables[0].Rows.Count == 0)
return;

dstMain = GetFormattedDataSet(dstMain);

GridView grv2 = new GridView();
grv2.DataSource = dstMain;
grv2.DataBind();
Response.AddHeader("content-disposition", "attachment;filename=MAUnpaid_CopyInstr_Details.xls");
Response.ContentEncoding = Encoding.UTF7;
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
grv2.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
catch (Exception exc)
{
//Error Message
}
finally
{
dstMain.Dispose();
}
}

When you apply Paging and Localization in your GridView Control that time this method will format your dataset with localized data. When you use Paging Then that time you have to fetch fresh data from database and fill this dataset.

Code Block
public DataSet GetFormattedDataSet(DataSet ds)
{        
DataSet dsTmp = new DataSet();        
DataTable dtTmp = new DataTable("NewDataTableName");        
DataColumn dc1 = new DataColumn(Resources.IIPControlResource.Common_lblColumn1.ToString());        
DataColumn dc2 = new DataColumn(Resources.IIPControlResource.Common_lblColumn2.ToString());        
DataColumn dc3 = new DataColumn(Resources.IIPControlResource.Common_lblColumn3.ToString());        
DataColumn dc4 = new DataColumn(Resources.IIPControlResource.Common_lblColumn4.ToString());        
DataColumn dc5 = new DataColumn(Resources.IIPControlResource.Common_lblColumn5.ToString());        
dtTmp.Columns.Add(dc1);        
dtTmp.Columns.Add(dc2);        
dtTmp.Columns.Add(dc3);        
dtTmp.Columns.Add(dc4);        
dtTmp.Columns.Add(dc5);        
DataRow dr;        
 
for (int i = 0; i <>
{            
dr = dtTmp.NewRow();            
dr[Resources.IIPControlResource.Common_lblColumn1.ToString()] = ds.Tables[0].Rows[i]["DBColumn1"];            
dr[Resources.IIPControlResource.Common_lblColumn2.ToString()] = ds.Tables[0].Rows[i]["DBColumn2"];            
dr[Resources.IIPControlResource.Common_lblColumn3.ToString()] = ds.Tables[0].Rows[i]["DBColumn3"];            
dr[Resources.IIPControlResource.Common_lblColumn4.ToString()] = ds.Tables[0].Rows[i]["DBColumn4"];            
dr[Resources.IIPControlResource.Common_lblColumn5.ToString()] = ds.Tables[0].Rows[i]["DBCOlumn5"];            
dtTmp.Rows.Add(dr);        
}        
dsTmp.Tables.Add(dtTmp);        
return dsTmp;
}

SQL input by XML String

From Application you have to pass a XML string to a Stored Procedure and it will insert all data into table using that XML.

Example SP.

CREATE PROCEDURE StoredProcName
(
@strXML varchar(8000)
)
AS
Declare @intPointer int
exec sp_xml_preparedocument @intPointer output, @strXML
INSERT into tbl_plnd_insertion
SELECT Column1, Column2, Column3, Column4, Column5
FROM OpenXml(@intPointer,'/root/tbl_plnd_insertion',2)
WITH (Column1 varchar(20) '@Column1' , Column2 varchar(20) '@Column2', Column3 varchar(20) '@Column3' , Column4 varchar(50) '@Column4', Column5 varchar(50) '@Column5')
exec sp_xml_removedocument @intPointer