ASP.net Export to excel.

Hello everyone,
 
Today I  am explaining about different approaches for exporting data from ASP.Net web page to Excel.
 
So, there are 3 approaches by which we can export data to excel.

1. Export using HTML writer.
2. Export With the help of Telerik controls.
3. Export using Microsoft.Office.Interop.Excel.

 

1. Export using HTML writer: very simple approach but the problem is you can export, but you can not import using OLEDB connecting. If you just want to export data and you are sure that it is never going to be imported than you can use this approach.

2. Export With the help of Telerik controls:
This approach is also very good approach, but limitation is, If you want more than one sheet in out file then as per my understanding it is not possible.
  

 3.Export using Microsoft.Office.Interop.Excel:

This approach works always but need little hard work.

 

 

 

 

For more details on this you can ask questions in comment section. 

 

Related code will post soon on this blog Keep tune :)

Read Excel Cell value using OLEDB connection.

string documentPath = ConfigurationManager.AppSettings["SiaTempPath"] + "SIATemp.xlsb";

  string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + documentPath +

";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";

OleDbConnection connection = new OleDbConnection(connectionString); 
// open connection to excel file
connection.Open();

OleDbCommand cmd = new OleDbCommand();

OleDbDataAdapter adapter = new OleDbDataAdapter();

DataSet ds = new DataSet();



// webconfig entry: <add key="SiaCellCoordinates" value="D19" /> string siaCoordinate = ConfigurationManager.AppSettings["SiaCellCoordinates"].ToString();

cmd.CommandText = "SELECT * FROM [SIA$" + siaCoordinate + ":" + siaCoordinate + "]";



cmd.Connection = connection;
// code to assign command for OleDbDataAdapter



adapter.SelectCommand = cmd;
ds.Tables.Add("xlsImport", "Excel");

adapter.Fill(ds, "xlsImport");

//Required Value
ds.Tables["xlsImport"].Rows[0][0].ToString()


______________________________________________________________
Enjoy Coding ;-)

File upload control as read only

I want my File upload control as read only, so that no one can edit file upload text box. However I could select file using browse button. Write code

<asp:fileupload id="FileUpload1" runat="server" onkeydown="javascript:return false;" xmlns:asp="#unknown" />

TELERIK: Search filter water mark fix.

C# CODE:

protected void gvProjectsList_OnItemDataBound(object sender, GridItemEventArgs e)
{
if (e.Item is GridFilteringItem)
{
GridFilteringItem filerItem = (GridFilteringItem)e.Item;

TextBox textItem = (TextBox)filerItem["ProjectNameFilter"].Controls[0];

if (string.IsNullOrEmpty(textItem.Text))
{
textItem.Text = @"Search project Name...";

textItem.Attributes.Add("onBlur", "return SetMessage(" + textItem.ClientID + ");");

}                textItem.Attributes.Add("onFocus", "return ClearMessage(" + textItem.ClientID + ");");
}

}

 
SCRIPT CODE:

<script language="javascript" type="text/javascript">

//code to hide/show water mark on search box.

function ClearMessage(obj) {

if (obj.value == "Search project Name...")

obj.value = "";
}

function SetMessage(obj) {

if (obj.value == "")

obj.value = "Search project Name...";

}
 </script>

       

       





 

TELERIK: Search with dropddown option.

GRID BOUND COLUMN: 


<telerik:GridBoundColumn UniqueName="emname" DataField="EM" HeaderText="EM"

HeaderStyle-Width="70px">

<FilterTemplate>

<telerik:RadComboBox ID="RadComboBoxEM" DataSourceID="sdsEmList" DataTextField="emname"

DataValueField="emname" Height="200px" Width="90" AppendDataBoundItems="true" SelectedValue='<%# ((GridItem)Container).OwnerTableView.GetColumn("emname").CurrentFilterValue %>'

runat="server" OnClientSelectedIndexChanged="TitleIndexChangedEM" HeaderStyle-Width="60px">

<Items>

<telerik:RadComboBoxItem Text="All" />

</Items>

</telerik:RadComboBox>

<telerik:RadScriptBlock ID="RadScriptBlockEM" runat="server">

<script type="text/javascript">

function TitleIndexChangedEM(sender, args) {

var tableView = $find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");

tableView.filter("emname", args.get_item().get_value(), "EqualTo");



}

</script>

</telerik:RadScriptBlock>

</FilterTemplate>

</telerik:GridBoundColumn>









DATA SOURCE:

<asp:SqlDataSource ID="sdsEmList" runat="server" ConnectionString="<%$ ConnectionStrings:MGSIProcessConnectionString %>" SelectCommand="select distinct em.employeename as emname,em.EmployeeId from Project p
join Employee em on em.EmployeeId=p.emid
order by emname"></asp:SqlDataSource> 
 

 

TELERIK: make telerik search case sensitive

<telerik:RadGrid ID="gvProjectsList" runat="server" MasterTableView-ShowHeadersWhenNoRecords="true" AllowMultiRowSelection="false" AutoGenerateColumns="false" EnableViewState="true" MasterTableView-
ImagesPath="~/Skins/Vista/Grid" OnItemCommand="GvProjectsList_ItemCommand">

<GroupingSettings CaseSensitive="false" />

<PagerStyle Mode="NextPrevAndNumeric"></PagerStyle>

Master Page Error

Problem:
Master Page Error
The Master Page file '~/MyProject.Master' cannot be loaded.
Correct the problem in Code View


Solution:
First closed all files in the project and closed the Visual Studio.
Then run devenv.exe /resetsettings at the command line. :)

SSRS datatype conversion

http://msdn.microsoft.com/en-us/library/cc281083(v=sql.100).aspx

Export SSRS to Excel using C# code.

<asp:LinkButton ID="linkProcess" runat="server" OnClick="linkProcess_Click"><b>Export to Excel</b></asp:LinkButton>

/// <summary>
/// method to export report into excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void linkProcess_Click(object sender, EventArgs e)
{

Byte[] returnValue;

returnValue = rvManagementReports.ServerReport.Render("EXCEL");

Response.Buffer = true;

Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

Response.AddHeader("content-disposition", "attachment ; filename=filename." + "xls");

Response.BinaryWrite(returnValue);
Response.Flush();
Response.End();
}




for any language character use

/// <summary>
/// method to export report into excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void linkProcess_Click(object sender, EventArgs e)

{

Response.ClearContent();
  Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", "YTDReviewsData"));

Response.ContentType = "application/ms-excel";

Response.ContentEncoding = System.Text.Encoding.Unicode;

Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());

System.IO.StringWriter stringWrite = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);



gvDealDetails.RenderControl(htmlWrite);

Response.Write(stringWrite.ToString());

Response.End();

}