public CellInfo(ExcelDocument document)
{
BackColor = ExcelColor.Automatic;
ForeColor = ExcelColor.Automatic;
Font = document.DefaultFont;
this.Document = document;
}
public bool ImportExcel(string strNotificationFrequencyId, string strNotificationFrequency, string NotificationTime, string BillingCurrencyId, string strBillingCurrency, string ContractTypeId, string ContractType, string DepartmentId, string Department)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xls")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xls"));
}
string[] ListOfExportFieldType = new string[] { "Auto Generated from system", "Auto Generated from system", "Auto Generated from system", "Auto Generated from system", "Auto Generated from system"
, "Auto Generated from system","Auto Generated from system","Auto Generated from system","Auto Generated from system","Manual Entry Field","Manual Entry Field"
,"Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"
,"Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"
,"Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"};
string[] ListOfExportFieldName = new string[] { "NotificationFrequencyId", "Notification Frequency", "Notification Time", "BillingCurrencyId"
, "Billing Currency","ContractTypeId","Contract Type","ClientContactDepartmentId","Client Contact Department","Client Name","1st Line of the Address",
"2nd Line of the Address","City","County","Postcode","Company Number", "VAT Number","Client Bank","Sort Code","Client Bank A/c No","End Date",
"Client Contact Start Date","Client Contact First Name","Client Contact Last Name","Client Contact 1st Line of the Address","Client Contact 2nd Line of the Address",
"Client Contact City","Client Contact County","Client Contact Postcode","Client Contact Telephone Number","Client Contact Mobile Number","Client Contact Email Address"};
int icol = 0;
int irow = 2;
ExcelDocument document = new ExcelDocument();
document.UserName = "******";
document.CodePage = CultureInfo.CurrentCulture.TextInfo.ANSICodePage;
foreach (string strItem in ListOfExportFieldType)
{
document[0, icol].Value = strItem;
document[0, icol].Font = new System.Drawing.Font("Calibri", 12, System.Drawing.FontStyle.Bold);
document[0, icol].ForeColor = ExcelColor.White;
document[0, icol].Alignment = Alignment.Centered;
document[0, icol].BackColor = ExcelColor.Gray;
document.ColumnWidth(icol, 220);
icol++;
}
icol = 0;
foreach (string strItem in ListOfExportFieldName)
{
document[1, icol].Value = strItem;
document[1, icol].Font = new System.Drawing.Font("Calibri", 12, System.Drawing.FontStyle.Bold);
document[1, icol].ForeColor = ExcelColor.Black;
document[1, icol].Alignment = Alignment.Centered;
icol++;
}
DataTable dt = GetRecordFromSiteMaster(strNotificationFrequencyId, strNotificationFrequency, NotificationTime, BillingCurrencyId, strBillingCurrency, ContractTypeId, ContractType, DepartmentId, Department);
for (int i = 0; i < dt.Rows.Count; i++)
{
document[irow, 0].Value = Convert.ToString(dt.Rows[i]["NotificationFrequencyId"]);
document[irow, 0].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 0].ForeColor = ExcelColor.Black;
document[irow, 0].Alignment = Alignment.Centered;
document[irow, 1].Value = Convert.ToString(dt.Rows[i]["Notification Frequency"]);
document[irow, 1].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 1].ForeColor = ExcelColor.Black;
document[irow, 1].Alignment = Alignment.Centered;
document[irow, 2].Value = Convert.ToString(dt.Rows[i]["Notification Time"]);
document[irow, 2].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 2].ForeColor = ExcelColor.Black;
document[irow, 2].Alignment = Alignment.Centered;
document[irow, 3].Value = Convert.ToString(dt.Rows[i]["BillingCurrencyId"]);
document[irow, 3].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 3].ForeColor = ExcelColor.Black;
document[irow, 3].Alignment = Alignment.Centered;
document[irow, 4].Value = Convert.ToString(dt.Rows[i]["Billing Currency"]);
document[irow, 4].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 4].ForeColor = ExcelColor.Black;
document[irow, 4].Alignment = Alignment.Centered;
document[irow, 5].Value = Convert.ToString(dt.Rows[i]["ContractTypeId"]);
document[irow, 5].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 5].ForeColor = ExcelColor.Black;
document[irow, 5].Alignment = Alignment.Centered;
document[irow, 6].Value = Convert.ToString(dt.Rows[i]["Contract Type"]);
document[irow, 6].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 6].ForeColor = ExcelColor.Black;
document[irow, 6].Alignment = Alignment.Centered;
document[irow, 7].Value = Convert.ToString(dt.Rows[i]["ClientContactDepartmentId"]);
document[irow, 7].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 7].ForeColor = ExcelColor.Black;
document[irow, 7].Alignment = Alignment.Centered;
document[irow, 8].Value = Convert.ToString(dt.Rows[i]["Client Contact Department"]);
document[irow, 8].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 8].ForeColor = ExcelColor.Black;
document[irow, 8].Alignment = Alignment.Centered;
irow++;
}
FileStream stream = new FileStream(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xls"), FileMode.Create);
document.Save(stream);
stream.Close();
return true;
}
internal Cell(int row, int column, ExcelDocument document)
{
this.document = document;
cellInfo = document.GetCellInfo(row, column);
}
public bool ImportExcel(string strSiteid, string strSupplierID, string strSupplyType, string strNotificationId, string strNotification, string CategoryID, string Category, string strReadingInterval)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xls")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xls"));
}
string[] ListOfExportFieldType = new string[] { "Auto Generated from system", "Auto Generated from system", "Auto Generated from system", "Auto Generated from system", "Auto Generated from system"
, "Auto Generated from system","Auto Generated from system","Auto Generated from system","Auto Generated from system","Auto Generated from system"
,"Auto Generated from system","Auto Generated from system","Auto Generated from system"
,"Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"
,"Manual Entry Field","Manual Entry Field"};
string[] ListOfExportFieldName = new string[] { "ClientId", "Client", "NetworkId", "Network"
, "PropertyId","Property Address","SupplyTypeId","Supply Type","ReadFrequencyId","Read Frequency","MeterCategoryId","Meter Category","Reading Interval",
"Start Date","End Date","Meter Serial","Device ID","Meter Start Date","Meter End Date","Opening Read","Offset Value",
"Warrenty Date"};
int icol = 0;
int irow = 2;
ExcelDocument document = new ExcelDocument();
document.UserName = "******";
document.CodePage = CultureInfo.CurrentCulture.TextInfo.ANSICodePage;
foreach (string strItem in ListOfExportFieldType)
{
document[0, icol].Value = strItem;
document[0, icol].Font = new System.Drawing.Font("Calibri", 12, System.Drawing.FontStyle.Bold);
document[0, icol].ForeColor = ExcelColor.White;
document[0, icol].Alignment = Alignment.Centered;
document[0, icol].BackColor = ExcelColor.Gray;
document.ColumnWidth(icol, 220);
icol++;
}
icol = 0;
foreach (string strItem in ListOfExportFieldName)
{
document[1, icol].Value = strItem;
document[1, icol].Font = new System.Drawing.Font("Calibri", 12, System.Drawing.FontStyle.Bold);
document[1, icol].ForeColor = ExcelColor.Black;
document[1, icol].Alignment = Alignment.Centered;
icol++;
}
DataTable dt = GetRecordFromSiteMaster(strSiteid, strSupplierID, strSupplyType, strNotificationId, strNotification, CategoryID, Category, strReadingInterval);
for (int i = 0; i < dt.Rows.Count; i++)
{
document[irow, 0].Value = Convert.ToString(dt.Rows[i]["ClientId"]);
document[irow, 0].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 0].ForeColor = ExcelColor.Black;
document[irow, 0].Alignment = Alignment.Centered;
document[irow, 1].Value = Convert.ToString(dt.Rows[i]["Client"]);
document[irow, 1].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 1].ForeColor = ExcelColor.Black;
document[irow, 1].Alignment = Alignment.Centered;
document[irow, 2].Value = Convert.ToString(dt.Rows[i]["NetworkId"]);
document[irow, 2].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 2].ForeColor = ExcelColor.Black;
document[irow, 2].Alignment = Alignment.Centered;
document[irow, 3].Value = Convert.ToString(dt.Rows[i]["Network"]);
document[irow, 3].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 3].ForeColor = ExcelColor.Black;
document[irow, 3].Alignment = Alignment.Centered;
document[irow, 4].Value = Convert.ToString(dt.Rows[i]["PropertyId"]);
document[irow, 4].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 4].ForeColor = ExcelColor.Black;
document[irow, 4].Alignment = Alignment.Centered;
document[irow, 5].Value = Convert.ToString(dt.Rows[i]["Property Address"]);
document[irow, 5].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 5].ForeColor = ExcelColor.Black;
document[irow, 5].Alignment = Alignment.Centered;
document[irow, 6].Value = Convert.ToString(dt.Rows[i]["SupplyTypeId"]);
document[irow, 6].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 6].ForeColor = ExcelColor.Black;
document[irow, 6].Alignment = Alignment.Centered;
document[irow, 7].Value = Convert.ToString(dt.Rows[i]["Supply Type"]);
document[irow, 7].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 7].ForeColor = ExcelColor.Black;
document[irow, 7].Alignment = Alignment.Centered;
document[irow, 8].Value = Convert.ToString(dt.Rows[i]["ReadFrequencyId"]);
document[irow, 8].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 8].ForeColor = ExcelColor.Black;
document[irow, 8].Alignment = Alignment.Centered;
document[irow, 9].Value = Convert.ToString(dt.Rows[i]["Read Frequency"]);
document[irow, 9].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 9].ForeColor = ExcelColor.Black;
document[irow, 9].Alignment = Alignment.Centered;
document[irow, 10].Value = Convert.ToString(dt.Rows[i]["MeterCategoryId"]);
document[irow, 10].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 10].ForeColor = ExcelColor.Black;
document[irow, 10].Alignment = Alignment.Centered;
document[irow, 11].Value = Convert.ToString(dt.Rows[i]["Meter Category"]);
document[irow, 11].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 11].ForeColor = ExcelColor.Black;
document[irow, 11].Alignment = Alignment.Centered;
document[irow, 12].Value = Convert.ToString(dt.Rows[i]["Reading Interval"]);
document[irow, 12].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 12].ForeColor = ExcelColor.Black;
document[irow, 12].Alignment = Alignment.Centered;
irow++;
}
FileStream stream = new FileStream(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xls"), FileMode.Create);
document.Save(stream);
stream.Close();
return true;
}
public bool ImportExcel(string strSiteid)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "OwnerTemplate.xls")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "OwnerTemplate.xls"));
}
string[] ListOfExportFieldType = new string[] { "Auto Generated from system", "Auto Generated from system", "Auto Generated from system", "Auto Generated from system", "Auto Generated from system"
, "Auto Generated from system","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"
,"Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"
,"Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"};
string[] ListOfExportFieldName = new string[] { "ClientId", "Client", "NetworkId", "Network"
, "PropertyId","Property Address","First Name","Last Name","Start Date","Bank Name","Bank Sort Code","Correspondence Address",
"City","County","Post Code","E-mail Address","Mail Service","Telephone Number","Mobile Number","Managing Agent",
"Managing Telephone Number","Managing Email","Notes","Client Reference Number"};
int icol = 0;
int irow = 2;
ExcelDocument document = new ExcelDocument();
document.UserName = "******";
document.CodePage = CultureInfo.CurrentCulture.TextInfo.ANSICodePage;
foreach (string strItem in ListOfExportFieldType)
{
document[0, icol].Value = strItem;
document[0, icol].Font = new System.Drawing.Font("Calibri", 12, System.Drawing.FontStyle.Bold);
document[0, icol].ForeColor = ExcelColor.White;
document[0, icol].Alignment = Alignment.Centered;
document[0, icol].BackColor = ExcelColor.Gray;
document.ColumnWidth(icol, 220);
icol++;
}
icol = 0;
foreach (string strItem in ListOfExportFieldName)
{
document[1, icol].Value = strItem;
document[1, icol].Font = new System.Drawing.Font("Calibri", 12, System.Drawing.FontStyle.Bold);
document[1, icol].ForeColor = ExcelColor.Black;
document[1, icol].Alignment = Alignment.Centered;
icol++;
}
DataTable dt = GetRecordFromSiteMaster(strSiteid);
for (int i = 0; i < dt.Rows.Count; i++)
{
document[irow, 0].Value = Convert.ToString(dt.Rows[i]["ClientId"]);
document[irow, 0].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 0].ForeColor = ExcelColor.Black;
document[irow, 0].Alignment = Alignment.Centered;
document[irow, 1].Value = Convert.ToString(dt.Rows[i]["Client"]);
document[irow, 1].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 1].ForeColor = ExcelColor.Black;
document[irow, 1].Alignment = Alignment.Centered;
document[irow, 2].Value = Convert.ToString(dt.Rows[i]["NetworkId"]);
document[irow, 2].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 2].ForeColor = ExcelColor.Black;
document[irow, 2].Alignment = Alignment.Centered;
document[irow, 3].Value = Convert.ToString(dt.Rows[i]["Network"]);
document[irow, 3].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 3].ForeColor = ExcelColor.Black;
document[irow, 3].Alignment = Alignment.Centered;
document[irow, 4].Value = Convert.ToString(dt.Rows[i]["PropertyId"]);
document[irow, 4].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 4].ForeColor = ExcelColor.Black;
document[irow, 4].Alignment = Alignment.Centered;
document[irow, 5].Value = Convert.ToString(dt.Rows[i]["Property Address"]);
document[irow, 5].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 5].ForeColor = ExcelColor.Black;
document[irow, 5].Alignment = Alignment.Centered;
irow++;
}
FileStream stream = new FileStream(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "OwnerTemplate.xls"), FileMode.Create);
document.Save(stream);
stream.Close();
return true;
}
public bool ImportExcel(string strSiteid, string strNotificationId, string DepartmentId)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "SiteTemplate.xls")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "SiteTemplate.xls"));
}
string[] ListOfExportFieldType = new string[] { "Auto Generated from system", "Auto Generated from system", "Auto Generated from system", "Auto Generated from system", "Auto Generated from system"
, "Auto Generated from system","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"
,"Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"
,"Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"};
string[] ListOfExportFieldName = new string[] { "ClientId", "Client", "BillingFrequencyId", "Billing Frequency"
, "ConsumerDepartmentId","Consumer Department","Network Name","Billing Upto","Billing Date","Fixed Bill Notice","Bank Name","Bank Sort Code",
"Bank Account Number","OIN / SUN Number","Manual Review of Bill","Contact Name","Telephone Number","Mobile Number","Email Address","Number of Domestic Properties",
"Number of Commercial Properties","Number of Bulk Supplies","End Date","Client Reference Number"};
int icol = 0;
int irow = 2;
ExcelDocument document = new ExcelDocument();
document.UserName = "******";
document.CodePage = CultureInfo.CurrentCulture.TextInfo.ANSICodePage;
foreach (string strItem in ListOfExportFieldType)
{
document[0, icol].Value = strItem;
document[0, icol].Font = new System.Drawing.Font("Calibri", 12, System.Drawing.FontStyle.Bold);
document[0, icol].ForeColor = ExcelColor.White;
document[0, icol].Alignment = Alignment.Centered;
document[0, icol].BackColor = ExcelColor.Gray;
document.ColumnWidth(icol, 220);
icol++;
}
icol = 0;
foreach (string strItem in ListOfExportFieldName)
{
document[1, icol].Value = strItem;
document[1, icol].Font = new System.Drawing.Font("Calibri", 12, System.Drawing.FontStyle.Bold);
document[1, icol].ForeColor = ExcelColor.Black;
document[1, icol].Alignment = Alignment.Centered;
icol++;
}
DataTable dt = GetRecordFromSiteMaster(strSiteid, strNotificationId, DepartmentId);
for (int i = 0; i < dt.Rows.Count; i++)
{
document[irow, 0].Value = Convert.ToString(dt.Rows[i]["ClientId"]);
document[irow, 0].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 0].ForeColor = ExcelColor.Black;
document[irow, 0].Alignment = Alignment.Centered;
document[irow, 1].Value = Convert.ToString(dt.Rows[i]["Client"]);
document[irow, 1].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 1].ForeColor = ExcelColor.Black;
document[irow, 1].Alignment = Alignment.Centered;
document[irow, 2].Value = Convert.ToString(dt.Rows[i]["BillingFrequencyId"]);
document[irow, 2].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 2].ForeColor = ExcelColor.Black;
document[irow, 2].Alignment = Alignment.Centered;
document[irow, 3].Value = Convert.ToString(dt.Rows[i]["Billing Frequency"]);
document[irow, 3].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 3].ForeColor = ExcelColor.Black;
document[irow, 3].Alignment = Alignment.Centered;
document[irow, 4].Value = Convert.ToString(dt.Rows[i]["ConsumerDepartmentId"]);
document[irow, 4].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 4].ForeColor = ExcelColor.Black;
document[irow, 4].Alignment = Alignment.Centered;
document[irow, 5].Value = Convert.ToString(dt.Rows[i]["Consumer Department"]);
document[irow, 5].Font = new System.Drawing.Font("Calibri", 12);
document[irow, 5].ForeColor = ExcelColor.Black;
document[irow, 5].Alignment = Alignment.Centered;
irow++;
}
FileStream stream = new FileStream(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "SiteTemplate.xls"), FileMode.Create);
document.Save(stream);
stream.Close();
return true;
}
