In this video, we will convert the C# DataTable to Excel with automatic column filter. We will be using the example that we have started in previous session. Please watch before proceeding.
Suggested Video:
Create web application
1. Start Visual Studio:2. Select File => New Project
3. Select the Programming language you want to use from Installed Templates section, in the New Project dialog box. Out of the box, you can either use C# or Visual Basic to develop ASP.NET web applications.
4. Select ASP.NET Web Application from the middle section of the New Project dialog box.
5. Provide a meaningful name to your project and solution. In this case "DotNetConcept"
6. Select the location, where you want the solution to be created.
7. Finally click OK
Now:
Step-1 Add New Web Form in the created application
Step-2 Provide name “ExportInExcel”
Step-3 Paste the below given code in design of that page
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExportInExcel.aspx.cs" Inherits="DotNetConcept.ExportInExcel" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" />
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Export In Excel" />
</div>
</form>
</body>
</html>
Step-4 Now Open the Code view of that page
Step-5 Paste the below code in class file
using System;
using Microsoft.Office.Interop.Excel;
public partial class ExportInExcel : System.Web.UI.Page
{
System.Data.DataTable table = null;
protected void Page_Load(object sender, EventArgs e)
{
table = new System.Data.DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Columns.Add("Drug", typeof(string));
table.Columns.Add("Patient", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
table.Rows.Add(25, "Indocin", "Sam", DateTime.Now);
table.Rows.Add(27, "Indocin", "David", DateTime.Now);
table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
table.Rows.Add(23, "Combivent", "steav", DateTime.Now);
table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
GridView1.DataSource = table;
GridView1.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
ExportTableDataToExcel(this.Page);
}
/// <summary>
/// This method exports the table data to excelsheet
/// </summary>
public void ExportTableDataToExcel(System.Web.UI.Page _page)
{
if (table != null && table.Rows.Count > 0)
{
string GridViewDataExportedFileInfo = "CSharpeDataTableExportedToExcel_" + DateTime.Now.ToUniversalTime() + ".xls";
GridViewDataExportedFileInfo =
GridViewDataExportedFileInfo.Replace(":", "_").Replace("/", "_").Replace(" ", "_");
GridViewDataExportedFileInfo.Replace(":", "_").Replace("/", "_").Replace(" ", "_");
#region Export In Documents Folder
object misValue = System.Reflection.Missing.Value;
Application application = new Application();
application.Visible = false;
Workbook workbook = application.Workbooks.Add(misValue);
Worksheet worksheet = (Worksheet)workbook.Worksheets.get_Item(1);
worksheet.Name = "CSharpeDataTableExportedToExcel";
worksheet.Cells.Font.Size = 12;
AddColumnInSheet(worksheet, table);
worksheet.Activate();
for (int j = 1; j <= table.Rows.Count; j++)
{
for (int i = 1; i <=
table.Columns.Count; i++)
table.Columns.Count; i++)
{
string data = table.Rows[j
- 1].ItemArray[i - 1].ToString();
- 1].ItemArray[i - 1].ToString();
worksheet.Cells[j + 1,
i] = data;
i] = data;
}
}
Range usedRange = worksheet.get_Range("A1", System.Type.Missing);
usedRange.AutoFilter(1, System.Reflection.Missing.Value, XlAutoFilterOperator.xlFilterValues, Type.Missing, true);
workbook.SaveAs(GridViewDataExportedFileInfo,
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue,
misValue);
workbook.Close(true, misValue, misValue);
application.Quit();
_page.ClientScript.RegisterStartupScript(this.GetType(), "GridView
Data Exported Alert Box.", "alert('Data File Exported with name " + GridViewDataExportedFileInfo + " available under your system Libraries ->
Documents.');", true);
#endregion
}
else if (_page != null)
{
_page.ClientScript.RegisterStartupScript(this.GetType(), "No
Data Alert Box.", "alert('There are no records to Download.');", true);
}
}
public void AddColumnInSheet(Worksheet worksheet, System.Data.DataTable TempDT)
{
for (int i = 1; i <= TempDT.Columns.Count; i++)
{
string data = TempDT.Columns[i - 1].ToString();
worksheet.Cells[1, i] = data;
}
}
}
Step-7 Run your Solution
Step-7 Click on “Export In Excel” Button
No comments:
Post a Comment
Your feedback is important.
Visit www.techwebdots.in