Export to excel mutiple sheet

Note We need to have a template on server for generating report in multiple sheet
as we do not had msoffice on server
moreover this require a batch job to delete excel file created by the method....
it creates 6 sheets
Expand|Select|Wrap|Line Numbers
 
# region Namespaces
 
using System;

using System.Data;

using System.Configuration;

using System.Collections;

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 System.Data.OleDb;

using System.IO;
 
using System.Text;

using System.Data.Common;
 
#endregion
 
public partial class BPNAdmin_DOMHistoryReport : System.Web.UI.Page

{

    #region Variable Declaration

    BPNReport report = new BPNReport();

    DataSet exoptSet = new DataSet();

    string xConnStr;

    OleDbConnection objXConn;

    OleDbCommand objCommand1;

    OleDbCommand objCommand2;

    OleDbCommand objCommand3;

    OleDbCommand objCommand4;

    OleDbCommand objCommand5;

    OleDbCommand objCommand6;

    DataTable dt;

    string FSE_Cnt;

    public string strFilePath;

    public string strfile;

    #endregion
 
    #region Event Handler
 
    protected void Page_Load(object sender, EventArgs e)

    {

        try

        {

            strfile = "DomainHistoryReport" + System.Guid.NewGuid().ToString() + ".xls";

            strFilePath = "/BPN/Report/" + strfile;

            File.Copy(ConfigurationManager.AppSettings["ExcelExportPath"].ToString() + "DomainHistoryReport.xls", ConfigurationManager.AppSettings["ExcelExportPath"].ToString() + strfile);

            strFilePath = ConfigurationManager.AppSettings["ExcelExportPath"].ToString() + strfile;

            ExcelConnection();

            strFilePath = "/BPN/Report/" + strfile;

            //Response.Write("<script language=javascript>window.open('http://dca-wb-94-vm:8080" + strFilePath + "');</script>");
 
        }

        catch (Exception ex)

        {

            HandleException(ex);

        }

        Response.Redirect(strFilePath);

    }
 
    #endregion
 
    #region UserDefined Function
 
    public void ExcelConnection()

    {

        try

        {

            int DomainId = Convert.ToInt32(Request.QueryString["DomainId"]);

            string strFVDescription = "";

            string strFDescription = "";

            string strDomain_Description = "";

            string strProcedureDescription = "";

            string strFtitle = "";

            string strCName = "";

            string strFValue = "";

            string strTDuration = "";

            string strLHours = "";

            string strPNumber = "";

            string strSEqp = "";
 
            exoptSet = report.DomainHistoryReport(DomainId);
 
            // Connect to the Excel Spreadsheet

            xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFilePath + ";" + "Extended Properties=Excel 8.0;";

            //  create your excel connection object using the connection string

            objXConn = new OleDbConnection(xConnStr);

            objXConn.Open();

            // Query each excel sheet.

            dt = objXConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
 
            if (dt != null)

            {
 
                String[] excelSheets = new String[dt.Rows.Count];

                int j = 0;

                foreach (DataRow row in dt.Rows)

                {

                    excelSheets[j] = row["TABLE_NAME"].ToString();

                    j++;

                }

                //exporting for sheet 1

                //Create the Domain DataTable.

                DataTable tbl = new DataTable("Domain");

                tbl.Columns.Add("Domain_Id", typeof(string));

                tbl.Columns.Add("Domain_Name", typeof(string));

                tbl.Columns.Add("Domain_Description", typeof(string));
 
                for (int i = 0, k = 2; i <= exoptSet.Tables[0].Rows.Count - 1; i++)

                {

                    //modifying domain description to reduce length and replace character so that it should fit properly in excel cell

                    strDomain_Description = "";

                    strDomain_Description = exoptSet.Tables[0].Rows[i][2].ToString().Replace("'", "`").Trim();

                    if (strDomain_Description.Length >= 250) { strDomain_Description = strDomain_Description.Substring(0, 250); }

                    //modification end
 
                    tbl.Rows.Add(new object[] { exoptSet.Tables[0].Rows[i][0].ToString(), exoptSet.Tables[0].Rows[i][1].ToString().Replace("'", "`"), strDomain_Description });

                    //objCommand1 = new OleDbCommand("INSERT INTO [" + excelSheets[k] + "](Domain_Name,Domain_Description) values('"+ tbl.Rows[i][1].ToString() + "','" + tbl.Rows[i][2].ToString() + "');");

                    objCommand1 = new OleDbCommand("INSERT INTO [" + excelSheets[k] + "](Domain_Id,Domain_Name,Domain_Description) values('" + tbl.Rows[i][0].ToString() + "','" + tbl.Rows[i][1].ToString() + "','" + tbl.Rows[i][2].ToString() + "');");

                    objCommand1.Connection = objXConn;

                    objCommand1.ExecuteNonQuery();

                }
 
                //exporting for sheet 2

                //symptom factor table.

                DataTable tb2 = new DataTable("Factor1");

                tb2.Columns.Add("FACTOR_ID", typeof(string));

                tb2.Columns.Add("CATEGORY_NAME", typeof(string));

                tb2.Columns.Add("FACTOR_TITLE", typeof(string));

                tb2.Columns.Add("FACTOR_DESCRIPTION", typeof(string));

                tb2.Columns.Add("FACTOR_VALUE", typeof(string));

                tb2.Columns.Add("FACTOR_VALUEDESC", typeof(string));
 
                for (int i = 0, k = 1; i <= exoptSet.Tables[1].Rows.Count - 1; i++)

                {

                    //category name modification

                    //strCName = "";

                    //strCName = exoptSet.Tables[1].Rows[i][1].ToString().Replace("'", "`").Trim();

                    ////category modification end
 
                    //Factor Title modification

                    strFtitle = "";

                    strFtitle = exoptSet.Tables[1].Rows[i][1].ToString().Replace("'", "`").Trim();

                    //Factor Title modification end
 
                    //modifying Factor description to reduce length and replace character so that it should fit properly in excel cell

                    strFDescription = "";

                    strFDescription = exoptSet.Tables[1].Rows[i][2].ToString().Replace("'", "`").Trim();

                    if (strFDescription.Length >= 250) { strFDescription = strFDescription.Substring(0, 250); }

                    //Factor decription modification end
 
                    // Factor Value modification

                    strFValue = "";

                    strFValue = exoptSet.Tables[1].Rows[i][3].ToString().Replace("'", "`").Trim();

                    //Factor Value modification end
 
                    //modifying Factor Value description to reduce length and replace character so that it should fit properly in excel cell

                    strFVDescription = "";

                    strFVDescription = exoptSet.Tables[1].Rows[i][4].ToString().Replace("'", "`").Trim();

                    if (strFVDescription.Length >= 250) { strFVDescription = strFVDescription.Substring(0, 250); }

                    //Factor Value modification end
 
                    tb2.Rows.Add(new object[] { exoptSet.Tables[1].Rows[i][0].ToString(), strCName, strFtitle, strFDescription, strFValue, strFVDescription });

                    //tb2.Rows.Add(new object[] {strFtitle, strFDescription, strFValue, strFVDescription });

                    //objCommand2 = new OleDbCommand("INSERT INTO [" + excelSheets[k] + "](FACTOR_ID,CATEGORY_NAME,FACTOR_TITLE,FACTOR_DESCRIPTION,FACTOR_VALUE,FACTOR_VALUEDESC) Values('" + tb2.Rows[i][0].ToString() + "','" + tb2.Rows[i][1].ToString() + "','" + tb2.Rows[i][2].ToString() + "','" + tb2.Rows[i][3].ToString() + "','" + tb2.Rows[i][4].ToString() + "','" + tb2.Rows[i][5].ToString() + "');");

                    objCommand2 = new OleDbCommand("INSERT INTO [" + excelSheets[1] + "](FACTOR_ID,FACTOR_TITLE,FACTOR_DESCRIPTION,FACTOR_VALUE,FACTOR_VALUEDESC) Values('" + tb2.Rows[i][0].ToString() + "','" + tb2.Rows[i][2].ToString() + "','" + tb2.Rows[i][3].ToString() + "','" + tb2.Rows[i][4].ToString() + "','" + tb2.Rows[i][5].ToString() + "');");

                    objCommand2.Connection = objXConn;

                    objCommand2.ExecuteNonQuery();

                }

                //export sheet 2 end
 
                //exporting for sheet 3

                //Create the configuration factor DataTable.

                DataTable tb3 = new DataTable("Factor2");

                tb3.Columns.Add("FACTOR_ID", typeof(string));

                tb3.Columns.Add("CATEGORY_NAME", typeof(string));

                tb3.Columns.Add("FACTOR_TITLE", typeof(string));

                tb3.Columns.Add("FACTOR_DESCRIPTION", typeof(string));

                tb3.Columns.Add("FACTOR_VALUE", typeof(string));

                tb3.Columns.Add("FACTOR_VALUEDESC", typeof(string));
 
                for (int i = 0, k = 2; i <= exoptSet.Tables[2].Rows.Count - 1; i++)

                {

                    //category name modification

                    strCName = "";

                    strCName = exoptSet.Tables[2].Rows[i][1].ToString().Replace("'", "`").Trim();

                    //category modification end
 
                    //Factor Title modification

                    strFtitle = "";

                    strFtitle = exoptSet.Tables[2].Rows[i][2].ToString().Replace("'", "`").Trim();

                    //Factor Title modification end
 
                    //modifying Factor description to reduce length and replace character so that it should fit properly in excel cell

                    strFDescription = "";

                    strFDescription = exoptSet.Tables[2].Rows[i][3].ToString().Replace("'", "`").Trim();

                    if (strFDescription.Length >= 250) { strFDescription = strFDescription.Substring(0, 250); }

                    //Factor decription modification end
 
                    // Factor Value modification

                    strFValue = "";

                    strFValue = exoptSet.Tables[2].Rows[i][4].ToString().Replace("'", "`").Trim();

                    //Factor Value modification end
 
                    //modifying Factor Value description to reduce length and replace character so that it should fit properly in excel cell

                    strFVDescription = "";

                    strFVDescription = exoptSet.Tables[2].Rows[i][5].ToString().Replace("'", "`").Trim();

                    if (strFVDescription.Length >= 250) { strFVDescription = strFVDescription.Substring(0, 250); }

                    //Factor Value modification end 
 
                    tb3.Rows.Add(new object[] { exoptSet.Tables[2].Rows[i][0].ToString(), strCName, strFtitle, strFDescription, strFValue, strFVDescription });

                    objCommand3 = new OleDbCommand("INSERT INTO [" + excelSheets[4] + "](FACTOR_ID,FACTOR_TITLE,FACTOR_DESCRIPTION,FACTOR_VALUE,FACTOR_VALUEDESC) Values('" + tb3.Rows[i][0].ToString() + "','" + tb3.Rows[i][2].ToString() + "','" + tb3.Rows[i][3].ToString() + "','" + tb3.Rows[i][4].ToString() + "','" + tb3.Rows[i][5].ToString().Trim() + "');");

                    objCommand3.Connection = objXConn;

                    objCommand3.ExecuteNonQuery();

                }
 
                //export sheet 3 end
 
                //exporting for sheet 4

                //Create the test factor DataTable.

                DataTable tb4 = new DataTable("Factor3");

                tb4.Columns.Add("FACTOR_ID", typeof(string));

                tb4.Columns.Add("CATEGORY_NAME", typeof(string));

                tb4.Columns.Add("FACTOR_TITLE", typeof(string));

                tb4.Columns.Add("FACTOR_DESCRIPTION", typeof(string));

                tb4.Columns.Add("FACTOR_VALUE", typeof(string));

                tb4.Columns.Add("FACTOR_VALUEDESC", typeof(string));
 
                for (int i = 0, k = 3; i <= exoptSet.Tables[3].Rows.Count - 1; i++)

                {

                    //category name modification

                    strCName = "";

                    strCName = exoptSet.Tables[3].Rows[i][1].ToString().Replace("'", "`").Trim();

                    //category modification end
 
                    //Factor Title modification

                    strFtitle = "";

                    strFtitle = exoptSet.Tables[3].Rows[i][2].ToString().Replace("'", "`").Trim();

                    //Factor Title modification end
 
                    //modifying Factor description to reduce length and replace character so that it should fit properly in excel cell

                    strFDescription = "";

                    strFDescription = exoptSet.Tables[3].Rows[i][3].ToString().Replace("'", "`").Trim();

                    if (strFDescription.Length >= 250) { strFDescription = strFDescription.Substring(0, 250); }

                    //Factor decription modification end
 
                    // Factor Value modification

                    strFValue = "";

                    strFValue = exoptSet.Tables[3].Rows[i][4].ToString().Replace("'", "`").Trim();

                    //Factor Value modification end
 
                    //modifying Factor Value description to reduce length and replace character so that it should fit properly in excel cell

                    strFVDescription = "";

                    strFVDescription = exoptSet.Tables[3].Rows[i][5].ToString().Replace("'", "`").Trim();

                    if (strFVDescription.Length >= 250) { strFVDescription = strFVDescription.Substring(0, 250); }

                    //Factor Value modification end
 
                    tb4.Rows.Add(new object[] { exoptSet.Tables[3].Rows[i][0].ToString(), strCName, strFtitle, strFDescription, strFValue, strFVDescription });

                    objCommand4 = new OleDbCommand("INSERT INTO [" + excelSheets[5] + "](FACTOR_ID,FACTOR_TITLE,FACTOR_DESCRIPTION,FACTOR_VALUE,FACTOR_VALUEDESC) Values('" + tb4.Rows[i][0].ToString() + "','" + tb4.Rows[i][2].ToString() + "','" + tb4.Rows[i][3].ToString().Replace("'", "`") + "','" + tb4.Rows[i][4].ToString() + "','" + tb4.Rows[i][5].ToString().Trim() + "');");

                    objCommand4.Connection = objXConn;

                    objCommand4.ExecuteNonQuery();

                }

                //export sheet 4 end
 
                //exporting for sheet 5

                //Create the Procedure DataTable.

                DataTable tb5 = new DataTable("Procedure");

                tb5.Columns.Add("PROCEDURE_ID", typeof(string));

                tb5.Columns.Add("PROCEDURE_DESCRIPTION", typeof(string));

                tb5.Columns.Add("TASK_DURATION", typeof(string));

                tb5.Columns.Add("LABOUR_HOURS", typeof(string));

                tb5.Columns.Add("FSE_COUNT", typeof(string));

                tb5.Columns.Add("PART_NUMBERS", typeof(string));

                tb5.Columns.Add("SPECIAL_EQUIPMENTS", typeof(string));

                tb5.Columns.Add("PROCEDURE_TITLE", typeof(string));

                //tb5 = exoptSet.Tables[4].Copy();
 
                for (int i = 0, k = 4; i <= exoptSet.Tables[4].Rows.Count - 1; i++)

                {

                    string strPTitle = "";

                    //modifying domain description to reduce length and replace character so that it should fit properly in excel cell

                    strProcedureDescription = "";

                    strProcedureDescription = exoptSet.Tables[4].Rows[i][1].ToString().Replace("'", "`").Trim();

                    if (strProcedureDescription.Length >= 250) { strProcedureDescription = strProcedureDescription.Substring(0, 250); }

                    //modification end
 
                    // Task Duration modification

                    strTDuration = "";

                    strTDuration = exoptSet.Tables[4].Rows[i][2].ToString().Replace("'", "`").Trim();

                    //Task Duration modification end
 
                    // Labour Hours modification

                    strLHours = "";

                    strLHours = exoptSet.Tables[4].Rows[i][3].ToString().Replace("'", "`").Trim();

                    //Labour Hours modification end
 
                    FSE_Cnt = exoptSet.Tables[4].Rows[i][4].ToString();
 
                    // Part Number modification

                    strPNumber = "";

                    strPNumber = exoptSet.Tables[4].Rows[i][5].ToString().Replace("'", "`").Trim();

                    //Part Number modification end
 
                    // Special Equipment modification

                    strSEqp = "";

                    strSEqp = exoptSet.Tables[4].Rows[i][6].ToString().Replace("'", "`").Trim();

                    //Special Equipment modification end
 
                    //procedure Title modification

                    strPTitle = "";

                    strPTitle = exoptSet.Tables[4].Rows[i][7].ToString().Replace("'", "`").Trim();

                    //Cause Title modification end
 
                    tb5.Rows.Add(new object[] { exoptSet.Tables[4].Rows[i][0].ToString(), strProcedureDescription, strTDuration, strLHours, FSE_Cnt, strPNumber, strSEqp, strPTitle });

                    objCommand5 = new OleDbCommand("INSERT INTO [" + excelSheets[3] + "](PROCEDURE_ID,PROCEDURE_DESCRIPTION,TASK_DURATION,LABOUR_HOURS,FSE_COUNT,PART_NUMBERS,SPECIAL_EQUIPMENTS,PROCEDURE_TITLE) Values('" + tb5.Rows[i][0].ToString() + "','" + tb5.Rows[i][1].ToString() + "','" + tb5.Rows[i][2].ToString() + "','" + tb5.Rows[i][3].ToString() + "','" + tb5.Rows[i][4].ToString() + "','" + tb5.Rows[i][5].ToString() + "','" + tb5.Rows[i][6].ToString() + "','" + tb5.Rows[i][7].ToString() + "');");

                    objCommand5.Connection = objXConn;

                    objCommand5.ExecuteNonQuery();

                }

                //export sheet 5 end
 
                //exporting for sheet 6

                //Create the cause DataTable.

                DataTable tb6 = new DataTable("Cause");

                tb6.Columns.Add("CAUSE_ID", typeof(string));

                tb6.Columns.Add("PROCEDURE_ID", typeof(string));

                tb6.Columns.Add("CAUSE_TITLE", typeof(string));

                tb6.Columns.Add("CAUSE_DESCRIPTION", typeof(string));

                tb6.Columns.Add("ROOT_CAUSE", typeof(string));

                tb6.Columns.Add("PROBLEM_STATEMENT", typeof(string));

                tb6.Columns.Add("FAILED_PART1", typeof(string));

                tb6.Columns.Add("FAILED_PART2", typeof(string));

                tb6.Columns.Add("FAILED_PART3", typeof(string));

                tb6.Columns.Add("PROCEDURE_DESCRIPTION", typeof(string));

                tb6.Columns.Add("TASK_DURATION", typeof(string));

                tb6.Columns.Add("LABOUR_HOURS", typeof(string));

                tb6.Columns.Add("FSE_COUNT", typeof(string));

                tb6.Columns.Add("PART_NUMBER", typeof(string));

                tb6.Columns.Add("SPECIAL_EQUIPMENT", typeof(string));

                for (int i = 0, k = 5; i <= exoptSet.Tables[5].Rows.Count - 1; i++)

                {

                    string strCTitle = "";

                    string strCDescription = "";

                    string strRCause = "";

                    string strPStmt = "";

                    string strFPart1 = "";

                    string strFPart2 = "";

                    string strFPart3 = "";
 
                    //Cause Title modification

                    strCTitle = "";

                    strCTitle = exoptSet.Tables[5].Rows[i][2].ToString().Replace("'", "`").Trim();

                    //Cause Title modification end
 
                    //modifying Cause Description to reduce length and replace character so that it should fit properly in excel cell

                    strCDescription = "";

                    strCDescription = exoptSet.Tables[5].Rows[i][3].ToString().Replace("'", "`").Trim();

                    if (strCDescription.Length >= 250) { strCDescription = strCDescription.Substring(0, 250); }

                    //modification end
 
                    //modifying Root Cause to reduce length and replace character so that it should fit properly in excel cell

                    strRCause = "";

                    strRCause = exoptSet.Tables[5].Rows[i][4].ToString().Replace("'", "`").Trim();

                    if (strRCause.Length >= 250) { strRCause = strRCause.Substring(0, 250); }

                    //modification end
 
                    //modifying Problem Statement to reduce length and replace character so that it should fit properly in excel cell

                    strPStmt = "";

                    strPStmt = exoptSet.Tables[5].Rows[i][5].ToString().Replace("'", "`").Trim();

                    if (strPStmt.Length >= 250) { strPStmt = strPStmt.Substring(0, 250); }

                    //modification end
 
                    //Failed Part 1 modification

                    strFPart1 = "";

                    strFPart1 = exoptSet.Tables[5].Rows[i][6].ToString().Replace("'", "`").Trim();

                    //Failed part 1 modification end
 
                    //Failed Part 2 modification

                    strFPart2 = "";

                    strFPart2 = exoptSet.Tables[5].Rows[i][7].ToString().Replace("'", "`").Trim();

                    //Failed part 2 modification end
 
                    //Failed Part 3 modification

                    strFPart3 = "";

                    strFPart3 = exoptSet.Tables[5].Rows[i][8].ToString().Replace("'", "`").Trim();

                    //Failed part 3 modification end
 
                    //modifying Procedure Description to reduce length and replace character so that it should fit properly in excel cell

                    strProcedureDescription = "";

                    strProcedureDescription = exoptSet.Tables[5].Rows[i][9].ToString().Replace("'", "`").Trim();

                    if (strProcedureDescription.Length >= 250) { strProcedureDescription = strProcedureDescription.Substring(0, 250); }

                    //modification end
 
                    // Task Duration modification

                    strTDuration = "";

                    strTDuration = exoptSet.Tables[5].Rows[i][10].ToString().Replace("'", "`").Trim();

                    //Task Duration modification end
 
                    // Labour Hours modification

                    strLHours = "";

                    strLHours = exoptSet.Tables[5].Rows[i][11].ToString().Replace("'", "`").Trim();

                    //Labour Hours modification end
 
                    FSE_Cnt = exoptSet.Tables[5].Rows[i][12].ToString();
 
                    // Part Number modification

                    strPNumber = "";

                    strPNumber = exoptSet.Tables[5].Rows[i][13].ToString().Replace("'", "`").Trim();

                    //Part Number modification end
 
                    // Special Equipment modification

                    strSEqp = "";

                    strSEqp = exoptSet.Tables[5].Rows[i][14].ToString().Replace("'", "`").Trim();

                    //Special Equipment modification end
 
                    tb6.Rows.Add(new object[] { exoptSet.Tables[5].Rows[i][0].ToString(), exoptSet.Tables[5].Rows[i][1].ToString(), strCTitle, strCDescription, strRCause, strPStmt, strFPart1, strFPart2, strFPart3, strProcedureDescription, strTDuration, strLHours, FSE_Cnt, strPNumber, strSEqp });

                    objCommand6 = new OleDbCommand("INSERT INTO [" + excelSheets[0] + "](CAUSE_ID,CAUSE_TITLE,CAUSE_DESCRIPTION,ROOT_CAUSE,PROBLEM_STATEMENT,FAILED_PART1,FAILED_PART2,FAILED_PART3,TASK_DURATION,LABOUR_HOURS,FSE_COUNT,PART_NUMBER,SPECIAL_EQUIPMENT) Values('" + tb6.Rows[i][0].ToString() + "','" + tb6.Rows[i][2].ToString() + "','" + tb6.Rows[i][3].ToString() + "','" + tb6.Rows[i][4].ToString() + "','" + tb6.Rows[i][5].ToString() + "','" + tb6.Rows[i][6].ToString() + "','" + tb6.Rows[i][7].ToString() + "','" + tb6.Rows[i][8].ToString() + "','" + tb6.Rows[i][10].ToString() + "','" + tb6.Rows[i][11].ToString() + "','" + tb6.Rows[i][12].ToString() + "','" + tb6.Rows[i][13].ToString() + "','" + tb6.Rows[i][14].ToString() + "');");

                    objCommand6.Connection = objXConn;

                    objCommand6.ExecuteNonQuery();
 
                    //export sheet 6 end
 
                }

                objXConn.Close();
 
            }

        }

        catch (Exception ex)

        {

            throw ex;

        }

    }
 
    protected void HandleException(Exception ex)

    {

        Common.WriteExceptionToFile(ex.ToString());

        int errorTypeCode = Common.VerifyException(ex.Message);

        //SQL Server Connection Exception

        if (errorTypeCode == 1001)

            Response.Redirect("../ErrorPage.aspx?ErrorType=S", true);

        //Applications Exception

        else if (errorTypeCode == 1)

            Response.Redirect("../ErrorPage.aspx?ErrorType=A", true);

    }
 
    #endregion
 
}