Export to excel mutiple sheet

hemantbasva

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
 
}

Curtis Rutland

What is your specific question? Keep in mind that we will not do your work for you, we can only help you fix specific coding problems you are having?

hemantbasva

My question was i have to create a scheduler to delete the files created by this code . is there any other way to optimize the code.

Export to excel mutiple sheet

Post your reply

Similar topics