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
- }