472,783 Members | 993 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,783 software developers and data experts.

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
  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
2 6300
Curtis Rutland
3,256 Expert 2GB
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
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

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

Similar topics

1
by: Steven Stewart | last post by:
I have a user who has been using Excel for a while to keep statistics and print reports. She finds using it cumbersome because of long formulas and a lot of copying and pasting. I have designed...
4
by: Anthony Cuttitta Jr. | last post by:
I'm working on some procedures where Access queries are exported to Excel, and then later on, those same workbooks are openned, and I need to target a specific original sheet. Sometimes there will...
3
by: Mads Petersen | last post by:
I'm stuck in this code. Hope you can and will help me. I launch it from excel. I have made the following code work, but not as i whant. I need the ranges to be working with something like xlDown....
6
by: Elena | last post by:
I'm trying to export data to an Excel worksheet. I can export the data in the cell values perfectly. I need the code to change a header and footer for the worksheet, not for the columns. Is...
13
by: Hemant Sipahimalani | last post by:
The following piece of code is being used to export HTML to excel. HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"...
7
by: Vanessa | last post by:
hi Everyone, I have two questions on exporting data to Excel using ASP (w/o converting formatted excel file into web page and then plug in the dynamic data): 1. Can we export data into...
5
by: Simon | last post by:
Dear reader, With the export command you can export a query to Excel. By activate this command a form pop's up with the following text:
2
by: VitorCastro | last post by:
Hello... First, sorry for my bad english... i have generated many pivot tables in Access 2003. and i want the code to export all of them to one single excel sheet and specific cell. i have...
3
by: yovation | last post by:
Hi, I have a 3 table database. 1 parent 1 child 1 child of child I would like to get the data into excel as 1 sheet (similar to a grouped report).
0
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.