How to export dataset to excel in C#?

random

How to export dataset to excel in C#?

Export dataset to the excel sheet in asp.net c#

Many times we need to export reports in excel format. So here I will explain how to export Dataset or Datatable in Excel file. Follow the below instructions:

1. Add reference to Microsoft.Office.Interop.Excel.dll

2. using System.Runtime.InteropServices;

Source:


<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
        <br />
       
        <asp:Button ID="btnExportExcel" runat="server" Text="Export To Excel"
            onclick="btnExportExcel_Click" />
    </div>
    </form>
</body>
</html>



export dataset,export datatable, export dataset to excel,asp.net,c#


Code Behind:


using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Runtime.InteropServices;

public partial class _Default : System.Web.UI.Page
{
    DataTable productsDataTable = new DataTable();
  
    protected void Page_Load(object sender, EventArgs e)
    {
        // create DataTable object   

        // auto increment column for productsDataTable
        DataColumn dataColumn = new DataColumn("AutoID", System.Type.GetType("System.Int32"));
        dataColumn.AutoIncrement = true;
        dataColumn.AutoIncrementSeed = 1;
        dataColumn.AutoIncrementStep = 1;
        dataColumn.Unique = true;
        productsDataTable.Columns.Add(dataColumn);

        // product id column
        dataColumn = new DataColumn("ProductID", System.Type.GetType("System.Int32"));
        productsDataTable.Columns.Add(dataColumn);

        // product name column
        dataColumn = new DataColumn("ProductName", System.Type.GetType("System.String"));
        productsDataTable.Columns.Add(dataColumn);

        // quantity column
        dataColumn = new DataColumn("ProductQuantity", System.Type.GetType("System.Int32"));
        productsDataTable.Columns.Add(dataColumn);

        // product price column
        dataColumn = new DataColumn("ProductPrice", System.Type.GetType("System.Decimal"));
        productsDataTable.Columns.Add(dataColumn);


        // After Creating columns you can add data rows to store the data dynamically

        DataRow dataRow = productsDataTable.NewRow();
        dataRow["ProductID"] = 43;
        dataRow["ProductName"] = "Ipoh Coffee";
        dataRow["ProductQuantity"] = 1;
        dataRow["ProductPrice"] = 46.00;
        productsDataTable.Rows.Add(dataRow);

        dataRow = productsDataTable.NewRow();
        dataRow["ProductID"] = 40;
        dataRow["ProductName"] = "Boston Crab Meat";
        dataRow["ProductQuantity"] = 1;
        dataRow["ProductPrice"] = 18.40;
        productsDataTable.Rows.Add(dataRow);

        GridView1.DataSource = productsDataTable;
        GridView1.DataBind();
    }
    protected void btnExportExcel_Click(object sender, EventArgs e)
    {
        try
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(productsDataTable);
            ExportToExcel(ds, "test");
        }
        catch (Exception ex)
        {
            throw;
        }
    }

    // Function to export dataset to excel file
    public void ExportToExcel(DataSet dataset, string DestFileName)
    {
        try
        {
            if (System.IO.File.Exists(DestFileName))
            {
                System.IO.File.Delete(DestFileName);
            }
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook excelWorkBook = default(Microsoft.Office.Interop.Excel.Workbook);
            excelWorkBook = excelApp.Workbooks.Add(Type.Missing);
            int sheetIndex = 0;
            int col = 0;
            int row = 0;
            Microsoft.Office.Interop.Excel.Worksheet excelSheet = default(Microsoft.Office.Interop.Excel.Worksheet);
            foreach (System.Data.DataTable dt1 in dataset.Tables)
            {
                sheetIndex += 1;
                object[,] rawData = new object[dt1.Rows.Count + 1, dt1.Columns.Count];
                for (col = 0; col <= dt1.Columns.Count - 1; col++)
                {
                    rawData[0, col] = dt1.Columns[col].ColumnName;
                }
                for (col = 0; col <= dt1.Columns.Count - 1; col++)
                {
                    for (row = 0; row <= dt1.Rows.Count - 1; row++)
                    {
                        rawData[row + 1, col] = dt1.Rows[row].ItemArray[col];
                    }
                }
                string finalColLetter = string.Empty;
                string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
                int colCharsetLen = colCharset.Length;
                if (dt1.Columns.Count > colCharsetLen)
                {
                    finalColLetter = colCharset.Substring((dt1.Columns.Count - 1) / colCharsetLen - 1, 1);
                }
                finalColLetter += colCharset.Substring((dt1.Columns.Count - 1) % colCharsetLen, 1);
                excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets.get_Item(sheetIndex);
                excelSheet.Name = dt1.TableName;
                string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt1.Rows.Count + 1);
                excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;

                //int colcount = excelSheet.Cells.CurrentRegion.Columns.Count;
                string excelRange1 = string.Format("A1:{0}{1}", finalColLetter, 1);
                excelSheet.get_Range(excelRange1, Type.Missing).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                excelSheet.get_Range(excelRange1, Type.Missing).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
                excelSheet.get_Range(excelRange1, Type.Missing).Font.Bold = true;
                excelSheet = null;
            }
            string e = System.IO.Path.GetExtension(DestFileName);
            if (e == ".csv")
            {
                excelWorkBook.SaveAs(DestFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVMSDOS, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
            }
            else if (e == ".xls")
            {
                excelWorkBook.SaveAs(DestFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
            }

            //use this commented code for auto save file
            //else if (e == "")
            //{
            //    excelWorkBook.SaveAs(DestFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing,
            //    Type.Missing, Type.Missing);
            //}

            GC.Collect();
            GC.WaitForPendingFinalizers();
            excelWorkBook.Close(trueType.Missing, Type.Missing);
            Marshal.FinalReleaseComObject(excelWorkBook);
            excelApp.Quit();
            Marshal.FinalReleaseComObject(excelApp);
        }
        catch (Exception ex)
        {
            throw;
        }
    }
   
}

To download the sample code click here. I hope this will help you out.
How to export dataset to excel in C#? Reviewed by Ravi Kumar on 12:26 AM Rating: 5

No comments:

All Rights Reserved by Etechpulse © 2012 - 2017

Contact Form

Name

Email *

Message *

Powered by Blogger.