Aspdotnet-Sharepoint: Excel

Showing posts with label Excel. Show all posts

Wednesday, March 14, 2012

Excel Data into grid view in asp.net

Title:
How to import Excel data to grid view in asp.net using c#

Description:
Reporting is the key features of any application.Because the end user wants the see the data what they want.
Some time we might need to import the data from files(Excel) ,then insert into data base.So i would like to explain one example for importing

Example:
The recent recent post have given the process of exporting.how to export grid view data to excel,Export Grid view Data to Word Document in asp.net,Export Grid view data to PDF document in asp.net,Import XML data to Grid View.Here i will show how to Import the Excel data to Grid view.For this i have used OLEDB connection to connect the excel database.Then get the data of sheet1 in to Data Adapter and fill the data set.Finally this data is bind to Grid view Excel to grid view in asp.net process:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GvImpOrder"  AllowPaging="True" Runat="server" AutoGenerateColumns="False">
<asp:Button ID="btnImportOrders" runat="server" Text="Import excel Data" 
onclick="btnImportOrders_Click" /></div>
</form>
</body>
</html>

Code behind:

using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;

public partial class ImportExcelToGidview : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnImportOrders_Click(object sender, EventArgs e)
{
String Con = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=D:\\ImportGidview\OrderReport.xls;" +
"Extended Properties=Excel 8.0;";
OleDbConnection objCn = new OleDbConnection(con);
DataSet ds = new DataSet();
OleDbDataAdapter Oda = new OleDbDataAdapter("select * from[Sheet1$]", Con); 
Oda.Fill(ds);
GVImpOrder.DataSource = ds.Tables[0].DefaultView;
GVImpOrder.DataBind();
}
}

Friday, December 30, 2011

export gridview to excel in asp.net

Title:How to Export data from grid view to excel in c#

Description:
As per previous articles we have gone through some examples like Export grid view data to word document,Export grid view to PDF,Import excel data to Asp.net Grid view ,Export data to excel in asp.net.Here i would like to share how to export grid view data to Excel sheet in asp.net using c#.net.It can be done in two steps.First is render to HTML from grid view then the present HTML to Excel.

Example:

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GdvMainmasters" Runat="server" DataSourceID="OrdrDb">
<Columns>
<asp:BoundField DataField="OrderID" HeaderText="OrderID" 
SortExpression="OrderID" />
<asp:BoundField DataField="OrderName" HeaderText="OrderName" 
SortExpression="OrderName" />
<asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />
<asp:BoundField DataField="Address" HeaderText="Address" 
SortExpression="Address" />
<asp:BoundField DataField="Amount" HeaderText="Amount" 
SortExpression="Amount" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="OrdrDb" runat="server" 
ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" 
SelectCommand="SELECT * FROM [Orders]"></asp:SqlDataSource>
<asp:Button ID="btnexportOrderstoWord" runat="server" Text="ExportExcel" 
onclick="btnexportOrderstoWord_Click" /></div>
</asp:GridView>
<asp:Lable id="LblMsg" runat="server"/>
<asp:Button id="BtnSendtoExcel" Text="Export" runat="server" OnClick="BtnSendtoExcel_Click"/>
</div>
</form>
</body>
</html>

CodeBehind:

protected void BtnSendtoExcel_Click(object sender, EventArgs e)
{
try
{
Excelbtn = "ABC";
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",attachment;filename=OrderDetails.xls");
Response.ContentType = "application/ms-excel";
StringWriter swr = new StringWriter();
HtmlTextWriter hwr = new HtmlTextWriter(sw);
GdvMainmasters.AllowPaging = false;
GdvMainmasters.AutoGenerateSelectButton = false;
GdvMainmasters.Enabled = false;
GdvMainmasters.DataBind();
GdvMainmasters.HeaderRow.Cells[0].BorderStyle = BorderStyle.Inset;
GdvMainmasters.HeaderRow.Cells[1].BorderStyle = BorderStyle.Inset;
GdvMainmasters.HeaderRow.Cells[2].BorderStyle = BorderStyle.Inset;
GdvMainmasters.HeaderRow.Cells[3].BorderStyle = BorderStyle.Inset;
GdvMainmasters.HeaderRow.Cells[3].BorderStyle = BorderStyle.Inset;
for (int j = 0; j < GdvMainmasters.Rows.Count; j++)
{
GridViewRow row = GdvMainmasters.Rows[j];
GdvMainmasters.RenderControl(hwr);
Response.Write(swr.ToString());
Response.Flush();
Response.End();
}
catch (Exception e3)
{
LblMsg.Text = e3.Message;
}
}

Note:While run the above code we may get the some errors RegisterForEventValidation can only be called during Render.If you get an error then need to override the control.

Tuesday, November 29, 2011

Export data to excel in c#

Title:How to export grid view data to excel in asp.net using c#.net

Description:
Basically we can export the data to excel in two ways in asp.net with c#. One is using Download Format and Other one is using Microsoft.Office.Interop.Excel .In the Download format ,it will render the grid view and export in XLST format.It is used when the data is export from grid view to excel.In Interop.Eexcel will create excel file dynamically and load the data into it.Exporting data to excel its pretty simple. Let's see how this can be done.For this i have taken data from database first then fill the existing data set.The following name space i have used for excel properties
Asp.Net Export to excel:

using Microsoft.Office.Interop.Excel;

Create Excel document:

Excel.Application App;
Excel.Workbook WorkBook;
Excel.Worksheet WorkSheet;
object misValue = System.Reflection.Missing.Value;
App = new Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Excel.Worksheet)WorkBook.Worksheets.get_Item(1);

Load the data into Excel file:
Here i will get the data into data set then it will load in to excel file using the following iteration.

for (int col = 0; col < dsreportdata.Tables[0].Columns.Count; col++)
{
for (int row = 0; row < dsreportdata.Tables[0].Rows.Count; row++)
{
WorkSheet.Cells[row + 12, col + 3] = dsreportdata.Tables[0].Rows[row].ItemArray[col].ToString();
}
}

Aspdotnet-Sharepoint

Top menu