By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Export to excel mutiple sheet

hemantbasva
P: 12
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
  1. # region Namespaces
  2.  
  3. using System;
  4. using System.Data;
  5. using System.Configuration;
  6. using System.Collections;
  7. using System.Web;
  8. using System.Web.Security;
  9. using System.Web.UI;
  10. using System.Web.UI.WebControls;
  11. using System.Web.UI.WebControls.WebParts;
  12. using System.Web.UI.HtmlControls;
  13. using System.Data.OleDb;
  14. using System.IO;
  15.  
  16. using System.Text;
  17. using System.Data.Common;
  18.  
  19. #endregion
  20.  
  21. public partial class BPNAdmin_DOMHistoryReport : System.Web.UI.Page
  22. {
  23.     #region Variable Declaration
  24.     BPNReport report = new BPNReport();
  25.     DataSet exoptSet = new DataSet();
  26.     string xConnStr;
  27.     OleDbConnection objXConn;
  28.     OleDbCommand objCommand1;
  29.     OleDbCommand objCommand2;
  30.     OleDbCommand objCommand3;
  31.     OleDbCommand objCommand4;
  32.     OleDbCommand objCommand5;
  33.     OleDbCommand objCommand6;
  34.     DataTable dt;
  35.     string FSE_Cnt;
  36.     public string strFilePath;
  37.     public string strfile;
  38.     #endregion
  39.  
  40.     #region Event Handler
  41.  
  42.     protected void Page_Load(object sender, EventArgs e)
  43.     {
  44.         try
  45.         {
  46.             strfile = "DomainHistoryReport" + System.Guid.NewGuid().ToString() + ".xls";
  47.             strFilePath = "/BPN/Report/" + strfile;
  48.             File.Copy(ConfigurationManager.AppSettings["ExcelExportPath"].ToString() + "DomainHistoryReport.xls", ConfigurationManager.AppSettings["ExcelExportPath"].ToString() + strfile);
  49.             strFilePath = ConfigurationManager.AppSettings["ExcelExportPath"].ToString() + strfile;
  50.             ExcelConnection();
  51.             strFilePath = "/BPN/Report/" + strfile;
  52.             //Response.Write("<script language=javascript>window.open('http://dca-wb-94-vm:8080" + strFilePath + "');</script>");
  53.  
  54.         }
  55.         catch (Exception ex)
  56.         {
  57.             HandleException(ex);
  58.         }
  59.         Response.Redirect(strFilePath);
  60.     }
  61.  
  62.     #endregion
  63.  
  64.     #region UserDefined Function
  65.  
  66.     public void ExcelConnection()
  67.     {
  68.         try
  69.         {
  70.             int DomainId = Convert.ToInt32(Request.QueryString["DomainId"]);
  71.             string strFVDescription = "";
  72.             string strFDescription = "";
  73.             string strDomain_Description = "";
  74.             string strProcedureDescription = "";
  75.             string strFtitle = "";
  76.             string strCName = "";
  77.             string strFValue = "";
  78.             string strTDuration = "";
  79.             string strLHours = "";
  80.             string strPNumber = "";
  81.             string strSEqp = "";
  82.  
  83.             exoptSet = report.DomainHistoryReport(DomainId);
  84.  
  85.             // Connect to the Excel Spreadsheet
  86.             xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFilePath + ";" + "Extended Properties=Excel 8.0;";
  87.             //  create your excel connection object using the connection string
  88.             objXConn = new OleDbConnection(xConnStr);
  89.             objXConn.Open();
  90.             // Query each excel sheet.
  91.             dt = objXConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
  92.  
  93.             if (dt != null)
  94.             {
  95.  
  96.                 String[] excelSheets = new String[dt.Rows.Count];
  97.                 int j = 0;
  98.                 foreach (DataRow row in dt.Rows)
  99.                 {
  100.                     excelSheets[j] = row["TABLE_NAME"].ToString();
  101.                     j++;
  102.                 }
  103.                 //exporting for sheet 1
  104.                 //Create the Domain DataTable.
  105.                 DataTable tbl = new DataTable("Domain");
  106.                 tbl.Columns.Add("Domain_Id", typeof(string));
  107.                 tbl.Columns.Add("Domain_Name", typeof(string));
  108.                 tbl.Columns.Add("Domain_Description", typeof(string));
  109.  
  110.                 for (int i = 0, k = 2; i <= exoptSet.Tables[0].Rows.Count - 1; i++)
  111.                 {
  112.                     //modifying domain description to reduce length and replace character so that it should fit properly in excel cell
  113.                     strDomain_Description = "";
  114.                     strDomain_Description = exoptSet.Tables[0].Rows[i][2].ToString().Replace("'", "`").Trim();
  115.                     if (strDomain_Description.Length >= 250) { strDomain_Description = strDomain_Description.Substring(0, 250); }
  116.                     //modification end
  117.  
  118.                     tbl.Rows.Add(new object[] { exoptSet.Tables[0].Rows[i][0].ToString(), exoptSet.Tables[0].Rows[i][1].ToString().Replace("'", "`"), strDomain_Description });
  119.                     //objCommand1 = new OleDbCommand("INSERT INTO [" + excelSheets[k] + "](Domain_Name,Domain_Description) values('"+ tbl.Rows[i][1].ToString() + "','" + tbl.Rows[i][2].ToString() + "');");
  120.                     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() + "');");
  121.                     objCommand1.Connection = objXConn;
  122.                     objCommand1.ExecuteNonQuery();
  123.                 }
  124.  
  125.                 //exporting for sheet 2
  126.                 //symptom factor table.
  127.                 DataTable tb2 = new DataTable("Factor1");
  128.                 tb2.Columns.Add("FACTOR_ID", typeof(string));
  129.                 tb2.Columns.Add("CATEGORY_NAME", typeof(string));
  130.                 tb2.Columns.Add("FACTOR_TITLE", typeof(string));
  131.                 tb2.Columns.Add("FACTOR_DESCRIPTION", typeof(string));
  132.                 tb2.Columns.Add("FACTOR_VALUE", typeof(string));
  133.                 tb2.Columns.Add("FACTOR_VALUEDESC", typeof(string));
  134.  
  135.                 for (int i = 0, k = 1; i <= exoptSet.Tables[1].Rows.Count - 1; i++)
  136.                 {
  137.                     //category name modification
  138.                     //strCName = "";
  139.                     //strCName = exoptSet.Tables[1].Rows[i][1].ToString().Replace("'", "`").Trim();
  140.                     ////category modification end
  141.  
  142.                     //Factor Title modification
  143.                     strFtitle = "";
  144.                     strFtitle = exoptSet.Tables[1].Rows[i][1].ToString().Replace("'", "`").Trim();
  145.                     //Factor Title modification end
  146.  
  147.                     //modifying Factor description to reduce length and replace character so that it should fit properly in excel cell
  148.                     strFDescription = "";
  149.                     strFDescription = exoptSet.Tables[1].Rows[i][2].ToString().Replace("'", "`").Trim();
  150.                     if (strFDescription.Length >= 250) { strFDescription = strFDescription.Substring(0, 250); }
  151.                     //Factor decription modification end
  152.  
  153.                     // Factor Value modification
  154.                     strFValue = "";
  155.                     strFValue = exoptSet.Tables[1].Rows[i][3].ToString().Replace("'", "`").Trim();
  156.                     //Factor Value modification end
  157.  
  158.                     //modifying Factor Value description to reduce length and replace character so that it should fit properly in excel cell
  159.                     strFVDescription = "";
  160.                     strFVDescription = exoptSet.Tables[1].Rows[i][4].ToString().Replace("'", "`").Trim();
  161.                     if (strFVDescription.Length >= 250) { strFVDescription = strFVDescription.Substring(0, 250); }
  162.                     //Factor Value modification end
  163.  
  164.                     tb2.Rows.Add(new object[] { exoptSet.Tables[1].Rows[i][0].ToString(), strCName, strFtitle, strFDescription, strFValue, strFVDescription });
  165.                     //tb2.Rows.Add(new object[] {strFtitle, strFDescription, strFValue, strFVDescription });
  166.                     //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() + "');");
  167.                     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() + "');");
  168.                     objCommand2.Connection = objXConn;
  169.                     objCommand2.ExecuteNonQuery();
  170.                 }
  171.                 //export sheet 2 end
  172.  
  173.                 //exporting for sheet 3
  174.                 //Create the configuration factor DataTable.
  175.                 DataTable tb3 = new DataTable("Factor2");
  176.                 tb3.Columns.Add("FACTOR_ID", typeof(string));
  177.                 tb3.Columns.Add("CATEGORY_NAME", typeof(string));
  178.                 tb3.Columns.Add("FACTOR_TITLE", typeof(string));
  179.                 tb3.Columns.Add("FACTOR_DESCRIPTION", typeof(string));
  180.                 tb3.Columns.Add("FACTOR_VALUE", typeof(string));
  181.                 tb3.Columns.Add("FACTOR_VALUEDESC", typeof(string));
  182.  
  183.                 for (int i = 0, k = 2; i <= exoptSet.Tables[2].Rows.Count - 1; i++)
  184.                 {
  185.                     //category name modification
  186.                     strCName = "";
  187.                     strCName = exoptSet.Tables[2].Rows[i][1].ToString().Replace("'", "`").Trim();
  188.                     //category modification end
  189.  
  190.                     //Factor Title modification
  191.                     strFtitle = "";
  192.                     strFtitle = exoptSet.Tables[2].Rows[i][2].ToString().Replace("'", "`").Trim();
  193.                     //Factor Title modification end
  194.  
  195.                     //modifying Factor description to reduce length and replace character so that it should fit properly in excel cell
  196.                     strFDescription = "";
  197.                     strFDescription = exoptSet.Tables[2].Rows[i][3].ToString().Replace("'", "`").Trim();
  198.                     if (strFDescription.Length >= 250) { strFDescription = strFDescription.Substring(0, 250); }
  199.                     //Factor decription modification end
  200.  
  201.                     // Factor Value modification
  202.                     strFValue = "";
  203.                     strFValue = exoptSet.Tables[2].Rows[i][4].ToString().Replace("'", "`").Trim();
  204.                     //Factor Value modification end
  205.  
  206.                     //modifying Factor Value description to reduce length and replace character so that it should fit properly in excel cell
  207.                     strFVDescription = "";
  208.                     strFVDescription = exoptSet.Tables[2].Rows[i][5].ToString().Replace("'", "`").Trim();
  209.                     if (strFVDescription.Length >= 250) { strFVDescription = strFVDescription.Substring(0, 250); }
  210.                     //Factor Value modification end 
  211.  
  212.                     tb3.Rows.Add(new object[] { exoptSet.Tables[2].Rows[i][0].ToString(), strCName, strFtitle, strFDescription, strFValue, strFVDescription });
  213.                     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() + "');");
  214.                     objCommand3.Connection = objXConn;
  215.                     objCommand3.ExecuteNonQuery();
  216.                 }
  217.  
  218.                 //export sheet 3 end
  219.  
  220.                 //exporting for sheet 4
  221.                 //Create the test factor DataTable.
  222.                 DataTable tb4 = new DataTable("Factor3");
  223.                 tb4.Columns.Add("FACTOR_ID", typeof(string));
  224.                 tb4.Columns.Add("CATEGORY_NAME", typeof(string));
  225.                 tb4.Columns.Add("FACTOR_TITLE", typeof(string));
  226.                 tb4.Columns.Add("FACTOR_DESCRIPTION", typeof(string));
  227.                 tb4.Columns.Add("FACTOR_VALUE", typeof(string));
  228.                 tb4.Columns.Add("FACTOR_VALUEDESC", typeof(string));
  229.  
  230.  
  231.                 for (int i = 0, k = 3; i <= exoptSet.Tables[3].Rows.Count - 1; i++)
  232.                 {
  233.                     //category name modification
  234.                     strCName = "";
  235.                     strCName = exoptSet.Tables[3].Rows[i][1].ToString().Replace("'", "`").Trim();
  236.                     //category modification end
  237.  
  238.                     //Factor Title modification
  239.                     strFtitle = "";
  240.                     strFtitle = exoptSet.Tables[3].Rows[i][2].ToString().Replace("'", "`").Trim();
  241.                     //Factor Title modification end
  242.  
  243.                     //modifying Factor description to reduce length and replace character so that it should fit properly in excel cell
  244.                     strFDescription = "";
  245.                     strFDescription = exoptSet.Tables[3].Rows[i][3].ToString().Replace("'", "`").Trim();
  246.                     if (strFDescription.Length >= 250) { strFDescription = strFDescription.Substring(0, 250); }
  247.                     //Factor decription modification end
  248.  
  249.                     // Factor Value modification
  250.                     strFValue = "";
  251.                     strFValue = exoptSet.Tables[3].Rows[i][4].ToString().Replace("'", "`").Trim();
  252.                     //Factor Value modification end
  253.  
  254.                     //modifying Factor Value description to reduce length and replace character so that it should fit properly in excel cell
  255.                     strFVDescription = "";
  256.                     strFVDescription = exoptSet.Tables[3].Rows[i][5].ToString().Replace("'", "`").Trim();
  257.                     if (strFVDescription.Length >= 250) { strFVDescription = strFVDescription.Substring(0, 250); }
  258.                     //Factor Value modification end
  259.  
  260.                     tb4.Rows.Add(new object[] { exoptSet.Tables[3].Rows[i][0].ToString(), strCName, strFtitle, strFDescription, strFValue, strFVDescription });
  261.                     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() + "');");
  262.                     objCommand4.Connection = objXConn;
  263.                     objCommand4.ExecuteNonQuery();
  264.                 }
  265.                 //export sheet 4 end
  266.  
  267.                 //exporting for sheet 5
  268.                 //Create the Procedure DataTable.
  269.                 DataTable tb5 = new DataTable("Procedure");
  270.                 tb5.Columns.Add("PROCEDURE_ID", typeof(string));
  271.                 tb5.Columns.Add("PROCEDURE_DESCRIPTION", typeof(string));
  272.                 tb5.Columns.Add("TASK_DURATION", typeof(string));
  273.                 tb5.Columns.Add("LABOUR_HOURS", typeof(string));
  274.                 tb5.Columns.Add("FSE_COUNT", typeof(string));
  275.                 tb5.Columns.Add("PART_NUMBERS", typeof(string));
  276.                 tb5.Columns.Add("SPECIAL_EQUIPMENTS", typeof(string));
  277.                 tb5.Columns.Add("PROCEDURE_TITLE", typeof(string));
  278.                 //tb5 = exoptSet.Tables[4].Copy();
  279.  
  280.                 for (int i = 0, k = 4; i <= exoptSet.Tables[4].Rows.Count - 1; i++)
  281.                 {
  282.                     string strPTitle = "";
  283.                     //modifying domain description to reduce length and replace character so that it should fit properly in excel cell
  284.                     strProcedureDescription = "";
  285.                     strProcedureDescription = exoptSet.Tables[4].Rows[i][1].ToString().Replace("'", "`").Trim();
  286.                     if (strProcedureDescription.Length >= 250) { strProcedureDescription = strProcedureDescription.Substring(0, 250); }
  287.                     //modification end
  288.  
  289.                     // Task Duration modification
  290.                     strTDuration = "";
  291.                     strTDuration = exoptSet.Tables[4].Rows[i][2].ToString().Replace("'", "`").Trim();
  292.                     //Task Duration modification end
  293.  
  294.                     // Labour Hours modification
  295.                     strLHours = "";
  296.                     strLHours = exoptSet.Tables[4].Rows[i][3].ToString().Replace("'", "`").Trim();
  297.                     //Labour Hours modification end
  298.  
  299.  
  300.                     FSE_Cnt = exoptSet.Tables[4].Rows[i][4].ToString();
  301.  
  302.                     // Part Number modification
  303.                     strPNumber = "";
  304.                     strPNumber = exoptSet.Tables[4].Rows[i][5].ToString().Replace("'", "`").Trim();
  305.                     //Part Number modification end
  306.  
  307.                     // Special Equipment modification
  308.                     strSEqp = "";
  309.                     strSEqp = exoptSet.Tables[4].Rows[i][6].ToString().Replace("'", "`").Trim();
  310.                     //Special Equipment modification end
  311.  
  312.                     //procedure Title modification
  313.                     strPTitle = "";
  314.                     strPTitle = exoptSet.Tables[4].Rows[i][7].ToString().Replace("'", "`").Trim();
  315.                     //Cause Title modification end
  316.  
  317.                     tb5.Rows.Add(new object[] { exoptSet.Tables[4].Rows[i][0].ToString(), strProcedureDescription, strTDuration, strLHours, FSE_Cnt, strPNumber, strSEqp, strPTitle });
  318.                     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() + "');");
  319.                     objCommand5.Connection = objXConn;
  320.                     objCommand5.ExecuteNonQuery();
  321.                 }
  322.                 //export sheet 5 end
  323.  
  324.                 //exporting for sheet 6
  325.                 //Create the cause DataTable.
  326.                 DataTable tb6 = new DataTable("Cause");
  327.                 tb6.Columns.Add("CAUSE_ID", typeof(string));
  328.                 tb6.Columns.Add("PROCEDURE_ID", typeof(string));
  329.                 tb6.Columns.Add("CAUSE_TITLE", typeof(string));
  330.                 tb6.Columns.Add("CAUSE_DESCRIPTION", typeof(string));
  331.                 tb6.Columns.Add("ROOT_CAUSE", typeof(string));
  332.                 tb6.Columns.Add("PROBLEM_STATEMENT", typeof(string));
  333.                 tb6.Columns.Add("FAILED_PART1", typeof(string));
  334.                 tb6.Columns.Add("FAILED_PART2", typeof(string));
  335.                 tb6.Columns.Add("FAILED_PART3", typeof(string));
  336.                 tb6.Columns.Add("PROCEDURE_DESCRIPTION", typeof(string));
  337.                 tb6.Columns.Add("TASK_DURATION", typeof(string));
  338.                 tb6.Columns.Add("LABOUR_HOURS", typeof(string));
  339.                 tb6.Columns.Add("FSE_COUNT", typeof(string));
  340.                 tb6.Columns.Add("PART_NUMBER", typeof(string));
  341.                 tb6.Columns.Add("SPECIAL_EQUIPMENT", typeof(string));
  342.                 for (int i = 0, k = 5; i <= exoptSet.Tables[5].Rows.Count - 1; i++)
  343.                 {
  344.                     string strCTitle = "";
  345.                     string strCDescription = "";
  346.                     string strRCause = "";
  347.                     string strPStmt = "";
  348.                     string strFPart1 = "";
  349.                     string strFPart2 = "";
  350.                     string strFPart3 = "";
  351.  
  352.  
  353.                     //Cause Title modification
  354.                     strCTitle = "";
  355.                     strCTitle = exoptSet.Tables[5].Rows[i][2].ToString().Replace("'", "`").Trim();
  356.                     //Cause Title modification end
  357.  
  358.                     //modifying Cause Description to reduce length and replace character so that it should fit properly in excel cell
  359.                     strCDescription = "";
  360.                     strCDescription = exoptSet.Tables[5].Rows[i][3].ToString().Replace("'", "`").Trim();
  361.                     if (strCDescription.Length >= 250) { strCDescription = strCDescription.Substring(0, 250); }
  362.                     //modification end
  363.  
  364.                     //modifying Root Cause to reduce length and replace character so that it should fit properly in excel cell
  365.                     strRCause = "";
  366.                     strRCause = exoptSet.Tables[5].Rows[i][4].ToString().Replace("'", "`").Trim();
  367.                     if (strRCause.Length >= 250) { strRCause = strRCause.Substring(0, 250); }
  368.                     //modification end
  369.  
  370.                     //modifying Problem Statement to reduce length and replace character so that it should fit properly in excel cell
  371.                     strPStmt = "";
  372.                     strPStmt = exoptSet.Tables[5].Rows[i][5].ToString().Replace("'", "`").Trim();
  373.                     if (strPStmt.Length >= 250) { strPStmt = strPStmt.Substring(0, 250); }
  374.                     //modification end
  375.  
  376.                     //Failed Part 1 modification
  377.                     strFPart1 = "";
  378.                     strFPart1 = exoptSet.Tables[5].Rows[i][6].ToString().Replace("'", "`").Trim();
  379.                     //Failed part 1 modification end
  380.  
  381.                     //Failed Part 2 modification
  382.                     strFPart2 = "";
  383.                     strFPart2 = exoptSet.Tables[5].Rows[i][7].ToString().Replace("'", "`").Trim();
  384.                     //Failed part 2 modification end
  385.  
  386.                     //Failed Part 3 modification
  387.                     strFPart3 = "";
  388.                     strFPart3 = exoptSet.Tables[5].Rows[i][8].ToString().Replace("'", "`").Trim();
  389.                     //Failed part 3 modification end
  390.  
  391.                     //modifying Procedure Description to reduce length and replace character so that it should fit properly in excel cell
  392.                     strProcedureDescription = "";
  393.                     strProcedureDescription = exoptSet.Tables[5].Rows[i][9].ToString().Replace("'", "`").Trim();
  394.                     if (strProcedureDescription.Length >= 250) { strProcedureDescription = strProcedureDescription.Substring(0, 250); }
  395.                     //modification end
  396.  
  397.                     // Task Duration modification
  398.                     strTDuration = "";
  399.                     strTDuration = exoptSet.Tables[5].Rows[i][10].ToString().Replace("'", "`").Trim();
  400.                     //Task Duration modification end
  401.  
  402.                     // Labour Hours modification
  403.                     strLHours = "";
  404.                     strLHours = exoptSet.Tables[5].Rows[i][11].ToString().Replace("'", "`").Trim();
  405.                     //Labour Hours modification end
  406.  
  407.  
  408.                     FSE_Cnt = exoptSet.Tables[5].Rows[i][12].ToString();
  409.  
  410.                     // Part Number modification
  411.                     strPNumber = "";
  412.                     strPNumber = exoptSet.Tables[5].Rows[i][13].ToString().Replace("'", "`").Trim();
  413.                     //Part Number modification end
  414.  
  415.                     // Special Equipment modification
  416.                     strSEqp = "";
  417.                     strSEqp = exoptSet.Tables[5].Rows[i][14].ToString().Replace("'", "`").Trim();
  418.                     //Special Equipment modification end
  419.  
  420.                     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 });
  421.                     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() + "');");
  422.                     objCommand6.Connection = objXConn;
  423.                     objCommand6.ExecuteNonQuery();
  424.  
  425.                     //export sheet 6 end
  426.  
  427.  
  428.  
  429.                 }
  430.                 objXConn.Close();
  431.  
  432.             }
  433.         }
  434.         catch (Exception ex)
  435.         {
  436.             throw ex;
  437.         }
  438.     }
  439.  
  440.     protected void HandleException(Exception ex)
  441.     {
  442.         Common.WriteExceptionToFile(ex.ToString());
  443.         int errorTypeCode = Common.VerifyException(ex.Message);
  444.         //SQL Server Connection Exception
  445.         if (errorTypeCode == 1001)
  446.             Response.Redirect("../ErrorPage.aspx?ErrorType=S", true);
  447.         //Applications Exception
  448.         else if (errorTypeCode == 1)
  449.             Response.Redirect("../ErrorPage.aspx?ErrorType=A", true);
  450.     }
  451.  
  452.     #endregion
  453.  
  454. }
  455.  
  456.  
Nov 10 '08 #1
Share this Question
Share on Google+
2 Replies


Curtis Rutland
Expert 2.5K+
P: 3,256
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?
Nov 10 '08 #2

hemantbasva
P: 12
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.
Nov 11 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.