コード例 #1
0
    private void GenerateJobSheet(Project.ProjectDataTable p)
    {
        Project.ProjectRow row = p.Rows[0] as Project.ProjectRow;
        TxtJobCode.Text = "";
        TxtManager.Text = "";
        TxtDetails.Text = "";
        TxtAddress.Text = "";
        TxtJobCode.Text = row.Project_Code;
        if (!row.IsContactNull())
        {
            TxtContact.Text = row.Contact;
        }
        TxtDate.Text = DateTime.Today.ToShortDateString();
        if (!row.IsDescriptionNull())
        {
            TxtDetails.Text = row.Description;
        }

        if (!row.IsAddressNull())
        {
            TxtAddress.Text = row.Address;
        }
        if (!row.IsCityNull())
        {
            TxtAddress.Text += "\n" + row.City;
        }
        if (!row.IsProjectManagerNull())
        {
            TxtManager.Text = row.ProjectManager;
        }
    }
コード例 #2
0
 private List <string> GetAddressFieldNames(string prefix, Project.ProjectRow row)
 {
     return(new string[] {
         prefix + "CompanyName",
         prefix + "AddressLine1",
         prefix + "AddressLine2",
         prefix + "TownOrCity",
         prefix + "County",
         prefix + "Postcode"
     }
            .Select(field => row[field].ToString())
            .Where(field => !string.IsNullOrWhiteSpace(field))
            .ToList());
 }
コード例 #3
0
    protected void BtnSheet_Click(object sender, EventArgs e)
    {
        string ProjectID = Request.QueryString["projectID"];

        Project.ProjectDataTable p   = projectBLL.GetDataByID(ProjectID);
        Project.ProjectRow       row = p.Rows[0] as Project.ProjectRow;

        String originalJobSheetPath = Server.MapPath("JobSheet_2018-12-09.xls");
        String modifiedJobSheetPath = Server.MapPath("newJobsheet1.xls");

        File.Copy(originalJobSheetPath, modifiedJobSheetPath, true);

        String conn        = String.Format(ExcelConnectionString, modifiedJobSheetPath);
        String updateQuery = "update [Project Sheet${0}:{0}] set F1 = \"{1}\"";

        using (OleDbConnection connection = new OleDbConnection(conn))
        {
            connection.Open();

            OleDbCommand cmd = new OleDbCommand
            {
                Connection = connection
            };

            if (!row.IsProject_CodeNull())
            {
                cmd.CommandText = String.Format(updateQuery, "G5", row.Project_Code);
                cmd.ExecuteNonQuery();
            }

            var introducer = row["Introducer"].ToString();

            if (!string.IsNullOrWhiteSpace(introducer))
            {
                cmd.CommandText = String.Format(updateQuery, "G7", introducer);
                cmd.ExecuteNonQuery();
            }

            var sectors = row["SectorList"].ToString();

            if (!string.IsNullOrWhiteSpace(sectors))
            {
                cmd.CommandText = String.Format(updateQuery, "G8", sectors);
                cmd.ExecuteNonQuery();
            }

            var startDate = row["StartDate"].ToString();

            if (!string.IsNullOrWhiteSpace(startDate))
            {
                var formattedStartDate = Convert.ToDateTime(startDate).ToShortDateString();
                cmd.CommandText = String.Format(updateQuery, "G10", formattedStartDate);
                cmd.ExecuteNonQuery();
            }

            if (!row.IsStatusNull())
            {
                cmd.CommandText = String.Format(updateQuery, "G11", row.Status.Trim());
                cmd.ExecuteNonQuery();
            }

            if (!row.IsDescriptionNull())
            {
                cmd.CommandText = String.Format(updateQuery, "G13", row.Description);
                cmd.ExecuteNonQuery();
            }

            var clientCompanyName = row["ClientCompanyName"].ToString();

            if (!string.IsNullOrWhiteSpace(clientCompanyName))
            {
                cmd.CommandText = String.Format(updateQuery, "G18", clientCompanyName);
                cmd.ExecuteNonQuery();
            }

            var clientType = row["ClientType"].ToString();

            if (!string.IsNullOrWhiteSpace(clientType))
            {
                cmd.CommandText = String.Format(updateQuery, "G20", clientType);
                cmd.ExecuteNonQuery();
            }

            if (!row.IsContactNull())
            {
                cmd.CommandText = String.Format(updateQuery, "A23", row.Contact);
                cmd.ExecuteNonQuery();
            }

            var clientAddressFields = GetAddressFieldNames("Client", row);
            var rowIndex            = 26;

            foreach (var field in clientAddressFields)
            {
                cmd.CommandText = String.Format(updateQuery, "A" + rowIndex.ToString(), field);
                cmd.ExecuteNonQuery();

                rowIndex++;
            }

            var invoiceContact = row["InvoiceContact"].ToString();

            if (!string.IsNullOrWhiteSpace(invoiceContact))
            {
                cmd.CommandText = String.Format(updateQuery, "G23", invoiceContact);
                cmd.ExecuteNonQuery();
            }

            var invoiceAddressFields = GetAddressFieldNames("Invoice", row);
            rowIndex = 26;

            foreach (var field in invoiceAddressFields)
            {
                cmd.CommandText = String.Format(updateQuery, "G" + rowIndex.ToString(), field);
                cmd.ExecuteNonQuery();

                rowIndex++;
            }

            connection.Close();
        }

        Response.Redirect("newJobsheet1.xls");
    }