Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done
   protected void lnkBuildExcelReport_Click(object sender, EventArgs e)
    {
 
        //---------------------------------------------------------------
        //
        //---------------------------------------------------------------
        long claimId = long.Parse(this.lblClaimId.Text);
 
        //---------------------------------------------------------------
        //
        //---------------------------------------------------------------
        DataSet ds = new DataSet();
 
        //---------------------------------------------------------------
        //
        //---------------------------------------------------------------
        ClaimsBLL objClaimsBLL = new ClaimsBLL();
        DataTable dt = objClaimsBLL.SelectByIdwithStyle(_id);
        ds.Tables.Add(dt);
        ds.Tables[ds.Tables.Count - 1].TableName = "Claim #" + this.lblClaimId.Text;
 
        //---------------------------------------------------------------
        //
        //---------------------------------------------------------------
        Claims_LogBLL objClaims_LogBLL = new Claims_LogBLL();
        dt = objClaims_LogBLL.GetDataByClaimId(_id);
        ds.Tables.Add(dt);
        ds.Tables[ds.Tables.Count - 1].TableName = "Claim Log";
 
        //---------------------------------------------------------------
        //write dataset to excel
        //---------------------------------------------------------------
        DsToExcel objDsToExcel = new DsToExcel();
        string path = Server.MapPath("~/Excel") + "\\";
        string fileName = "Claim" + _id.ToString() + ".xls";
        objDsToExcel.WriteDatasetToExcel(fileName, ds, path);
 
        //---------------------------------------------------------------
        //
        //---------------------------------------------------------------
        Response.Redirect("~/Excel/" + fileName);
 
 
    }
 
using System;
using System.IO;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Excel;
using System.Runtime.InteropServices;
 
///<summary>
/// Summary description for DsToExcel
///</summary>
public class DsToExcel
{
    public DsToExcel()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    public void WriteDatasetToExcel(string fileName, DataSet ds, string strCurrentDir)
    {
        //---------------------------------------------------------------
        //
        //---------------------------------------------------------------
        Application oXL;
        _Workbook oWB;
        _Worksheet oSheet;
        Range oRng;
        try
        {
            oXL = new Application();
            oXL.Visible = false;
            //Get a new workbook.
            oWB = (_Workbook)(oXL.Workbooks.Add(strCurrentDir + "\\template.xls"));
            oSheet = (_Worksheet)oWB.ActiveSheet;
            //System.Data.DataTable dtGridData=ds.Tables[0];
            int iRow = 2;
            //---------------------------------------------------------------
            //loop thru dataset tables
            //---------------------------------------------------------------
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                //---------------------------------------------------------------
                //wtite Table Name
                //---------------------------------------------------------------
                oSheet.Cells[iRow, 1] = ds.Tables[i].TableName;
                Excel.Range xlRange = (Excel.Range)oSheet.Cells[iRow, 1];
                // Bold the current row.
                xlRange.EntireRow.Font.Bold = true;
 
                //---------------------------------------------------------------
                //any records?
                //---------------------------------------------------------------
                iRow++;
                if (ds.Tables[i].Rows.Count < 1)
                {
                    //---------------------------------------------------------------
                    //<no records>
                    //---------------------------------------------------------------
                    oSheet.Cells[iRow, 1] = "<no records>";
                    iRow++;
                }
                else
                {
                    //---------------------------------------------------------------
                    //build column names row
                    //---------------------------------------------------------------
                    for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
                    {
                        oSheet.Cells[iRow, j + 1] = ds.Tables[i].Columns[j].ColumnName;
                        //oSheet.Cells[iRow, j + 1].Font.FontStyle = System.Drawing.FontStyle.Bold;
 
                        xlRange = (Excel.Range) oSheet.Cells[iRow, j + 1];
                        // Bold the current row.
                        xlRange.EntireRow.Font.Bold = true;
                    }
                   
                    iRow++;
                   
                    //---------------------------------------------------------------
                    //build data row
                    //loop thru dataset table rows
                    //---------------------------------------------------------------
                    for (int rowNo = 0; rowNo < ds.Tables[i].Rows.Count; rowNo++)
                    {
                        //---------------------------------------------------------------
                        //loop thru table columns
                        // print into cells the values of each column.
                        //---------------------------------------------------------------
                        for (int colNo = 0; colNo < ds.Tables[i].Columns.Count; colNo++)
                        {
                            oSheet.Cells[iRow, colNo + 1] = ds.Tables[i].Rows[rowNo][colNo].ToString();
                        }
                        iRow++;
                    }
                }
                //---------------------------------------------------------------
                //skip a row for next dataset table
                //---------------------------------------------------------------
                iRow++;
            }
 
            //---------------------------------------------------------------
            //style the sheet
            //---------------------------------------------------------------
            oRng = oSheet.get_Range("A1", "IV1");
            oRng.EntireColumn.AutoFit();
            oXL.Visible = false;
            oXL.UserControl = false;
            string duhSaveFile = strCurrentDir + fileName;
 
            //---------------------------------------------------------------
            //delete older file version
            //---------------------------------------------------------------
            try
            {
                File.Delete(duhSaveFile);
            }
            catch { }
 
            //---------------------------------------------------------------
            //save newer file version
            //---------------------------------------------------------------
            oWB.SaveAs(duhSaveFile, XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlShared, false, false, null, null, null);
            // Need all following code to clean up and remove all references!!!
            oWB.Close(null, null, null);
            oXL.Workbooks.Close();
            oXL.Quit();
 
            //---------------------------------------------------------------
            //ReleaseComObjects
            //---------------------------------------------------------------
            Marshal.ReleaseComObject(oRng);
            Marshal.ReleaseComObject(oXL);
            Marshal.ReleaseComObject(oSheet);
            Marshal.ReleaseComObject(oWB);
        }
 
        catch (Exception theException)
        {
            System.Diagnostics.Debug.WriteLine(theException.Message);
        }
 
    }
 
}
 
Posted on Thursday, August 2, 2007 7:12 AM Excel | Back to top


Comments on this post: convert dataset to excel for a WebForm

# re: convert dataset to excel for a WebForm
Requesting Gravatar...
it is really a nice refcod whihc help to develpoer
Left by krunal shah on Dec 19, 2007 1:00 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net