Friday, May 4, 2012

Export data to excel work book using InteropServices

Exporting data to an excel workbook is become a common application functionality now. By using the below code we can export data to  multiple sheets in an excel work book. In the below code it will export data to an excel workbook and place that excel workbook in the root folder. Here one table data will placed in one sheet.

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

<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="btnExport" runat="server" onclick="btnExport_Click"
            Text="Export"  />
    </div>
    </form>
</body>
</html>



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Runtime.InteropServices;

public partial class ExportToWorkbook : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnExport_Click(object sender, EventArgs e)
    {
        GenerateData();
    }

    private void GenerateData()
    {
        System.Data.DataSet rescDS = new System.Data.DataSet();

        System.Data.DataSet studiesDS = new System.Data.DataSet();

        // Get data

        rescDS = GenerateDataTable();

        studiesDS = GenerateSecondDataTable();
        // Create Excel Application, Workbook, and WorkSheets
        Microsoft.Office.Interop.Excel.Application xlExcel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbooks xlBooks = default(Microsoft.Office.Interop.Excel.Workbooks);
                Microsoft.Office.Interop.Excel.Workbook xlBook = default(Microsoft.Office.Interop.Excel.Workbook);
        //Microsoft.Office.Interop.Excel.Workbook xlBook = new Microsoft.Office.Interop.Excel.Workbook();

        //Microsoft.Office.Interop.Excel.Sheets xlSheets = default(Excel.Sheets);
        Microsoft.Office.Interop.Excel.Sheets xlSheets = default(Microsoft.Office.Interop.Excel.Sheets);
        Microsoft.Office.Interop.Excel.Worksheet stdSheet = default(Microsoft.Office.Interop.Excel.Worksheet);
        Microsoft.Office.Interop.Excel.Range xlCells = default(Microsoft.Office.Interop.Excel.Range);

        string sFile = null;

        string sTemplate = null;

        Microsoft.Office.Interop.Excel.Worksheet rescSheet = default(Microsoft.Office.Interop.Excel.Worksheet);

        //string filename=strin

        //sFile = Server.MapPath(Request.ApplicationPath) + "\\Excel.xls";
        sFile = Path.Combine(Server.MapPath(Request.ApplicationPath), string.Format("{0}_{1}.xls", "ExcelWorkBook", DateTime.Now.ToShortDateString()));
        //File.CreateText(Server.MapPath(Request.ApplicationPath)
        // Formatted template the way you want.

        // If you want to change the format, change this template

        // sTemplate = Server.MapPath(Request.ApplicationPath) + "\\XLTemplate.xls";

        xlExcel.Visible = false;
        xlExcel.DisplayAlerts = false;
        // Get all workbooks and open first workbook
        xlBooks = xlExcel.Workbooks;
        xlBooks.Open(Server.MapPath(Request.ApplicationPath) + "\\XLTemplate.xls");
        //xlBook = xlBooks.Item(1);
        xlBook = xlBooks.Item[1];
        // Get all sheets available in first book
        xlSheets = xlBook.Worksheets;
        // Get first sheet, change its name and get all cells
        stdSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Item[1];
        stdSheet.Name = "First Sheet";
        xlCells = stdSheet.Cells;
        // Fill all cells with data
        GenerateExcelFile(studiesDS.Tables[0], xlCells); 
        //Fill in the data
        // Get second sheet, change its name and get all cells
        rescSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Item[2];
        rescSheet.Name = "Second Sheet ";
        xlCells = rescSheet.Cells;
        // Fill all cells with data
        GenerateExcelFile(rescDS.Tables[0], xlCells);
        // Save created sheets as a file
        xlBook.SaveAs(sFile);
        // Make sure all objects are disposed
        xlBook.Close();
        xlExcel.Quit();
        Marshal.ReleaseComObject(xlCells);
        Marshal.ReleaseComObject(stdSheet);
        Marshal.ReleaseComObject(xlSheets);
        Marshal.ReleaseComObject(xlBook);
        Marshal.ReleaseComObject(xlBooks);
        Marshal.ReleaseComObject(xlExcel);
        xlExcel = null;
        xlBooks = null;
        xlBook = null;
        xlSheets = null;
        stdSheet = null;
        xlCells = null;
        rescSheet = null;
        // Let GC know about it
        //GC.Collect();
        // Export Excel for download
        try
        {
            Response.Redirect(sFile, false);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }


    private void GenerateExcelFile(System.Data.DataTable table, Microsoft.Office.Interop.Excel.Range xlCells)
    {

        DataRow dr = null;
        object[] ary = null;
        int iRow = 0;
        int iCol = 0;
        //Output Column Headers
        xlCells.Font.Bold = true;
        for (iCol = 0; iCol <= table.Columns.Count - 1; iCol++)
        {
            //xlCells.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
            xlCells[1, iCol + 1] = table.Columns[iCol].ToString();          
        }
        //Output Data       
        for (iRow = 0; iRow <= table.Rows.Count - 1; iRow++)
        {
            dr = table.Rows[iRow];

            ary = dr.ItemArray;

            //xlCells.Font.Bold = false;
            for (iCol = 0; iCol < ary.Length; iCol++)
            {
                xlCells[iRow + 2, iCol + 1] = ary[iCol].ToString();

                Response.Write(ary[iCol].ToString() + System.Environment.NewLine);

            }

        }

    }

    private DataSet GenerateDataTable()
    {
        DataSet ds = new DataSet();
        System.Data.DataTable dt = new System.Data.DataTable();
        dt.Columns.Add("SNo");
        dt.Columns.Add("Name");
        for (int i = 0; i < 10; i++)
        {
            DataRow dr = dt.NewRow();
            dr["SNo"] = i;
            dr["Name"] = "Sheet1" + i;
            dt.Rows.Add(dr);
        }
        ds.Tables.Add(dt);
        return ds;
    }

    private DataSet GenerateSecondDataTable()
    {
        DataSet ds = new DataSet();
        System.Data.DataTable dt = new System.Data.DataTable();
        dt.Columns.Add("SNo");
        dt.Columns.Add("Name");
        for (int i = 0; i < 100; i++)
        {
            DataRow dr = dt.NewRow();
            dr["SNo"] = i;
            dr["Name"] = "Sheet2" + i;
            dt.Rows.Add(dr);
        }
        ds.Tables.Add(dt);
        return ds;
    }
}
at Posted by

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)