C# DataTable to Excel with column filter

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(" ""_");
                #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++)
                    {
                        string data = table.Rows[j
- 1].ItemArray[i - 1].ToString();
                        worksheet.Cells[j + 1,
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