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;
}
}
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());
}
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");
}