protected void btnQATImport_Click(object sender, EventArgs e)
{
var QATlist = new List <QATEntity>(); // NEED 1 (first)
if (txtImportDate.Text != "" && txtImportDate.Text != null)
{
var center = ddlCenterName.SelectedValue.ToString();
var projectQAT = new QAT().FindByImportedDate(GeneralUtility.ConvertMonthYearStringFormat(txtImportDate.Text.Trim()), center);
if (projectQAT.Count() == 0)
{
if (FileUpload1.HasFile)
{
if (Path.GetExtension(FileUpload1.FileName) == ".xlsx")
{
ExcelPackage package = new ExcelPackage(FileUpload1.FileContent); // NEED 2 (first)
ExcelWorksheet workSheet = package.Workbook.Worksheets.First(); // NEED 2 (first)
// workSheet.DeleteRow(1); // NEED 3 (first)
QAT_BindBusiness(QATlist, workSheet, center);
}
}
#region Save Probes (first)
QAT itemBusiness = new QAT();
using (TransactionScope Scope = new TransactionScope())
{
try
{
foreach (var v in QATlist)
{
itemBusiness.Entity = v;
itemBusiness.Save();
}
itemBusiness.ReplaceQAT(center);
Scope.Complete();
MessageBox.MessageShow(this.GetType(), "QAT Import Successfully!", ClientScript);
}
catch (Exception ex)
{
Response.Redirect("error.aspx");
throw ex;
}
}
#endregion
}
else
{
MessageBox.MessageShow(this.GetType(), "This Excel File has already been Imported!", ClientScript);
}
}
else
{
MessageBox.MessageShow(this.GetType(), "Please Choose Import Date!.", ClientScript);
}
}
/// <summary>
/// A Component that can only live under a QAT Component Root.
/// </summary>
/// <param name="qat">The QAT that this component is in</param>
/// <param name="id">The ID of this component</param>
/// <param name="title">The title of this component</param>
/// <param name="description">The description of this component</param>
public QATComponent(QAT qat, string id, string title, string description)
: base(qat, id, title, description)
{
}
/// <summary>
/// A Component that can only live under a QAT Component Root.
/// </summary>
/// <param name="qat">The QAT that this component is in</param>
/// <param name="id">The ID of this component</param>
/// <param name="title">The title of this component</param>
/// <param name="description">The description of this component</param>
public QATComponent(QAT qat, string id, string title, string description)
: base(qat, id, title, description)
{
}
protected void btnExport_Click(object sender, EventArgs e)
{
if (txtMonth.Text != "" && txtMonth.Text != null)
{
#region "QAT"
var center = ddlCenterName.SelectedValue.ToString();
var QATList = new QAT().AllQAT();
DataTable attTbl = new DataTable();
attTbl.Clear();
attTbl.Columns.Clear();
var result = (from dd in QATList.Where(x => x.Center == this.ddlCenterName.SelectedValue).ToList()
orderby dd.QAT
select dd).ToList();
// Convert to DataTable.
DataTable table = ConvertToDataTable(result);
table.Columns.Remove("ID");
table.Columns.Remove("Createdby");
table.Columns.Remove("Month");
table.Columns.Remove("CreatedDate");
table.Columns.Remove("TableName");
var yrm = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim());
int yr = Convert.ToInt32(yrm.Substring(0, 4).ToString());
int mth = Convert.ToInt32(yrm.Substring(4, 2).ToString());
DateTime date = new DateTime(yr, mth, 1);
var mm = date.ToString("MMMM");
var yy = date.ToString("yy");
if (result.Count().Equals(0))
{
MessageBox.MessageShow(GetType(), "No Export Data.!", ClientScript);
}
else
{
var fileName = "QATList" + mm + "'" + yy + ".xlsx";
int count = 0;
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("Probes_List_Export.xlsx", System.Text.Encoding.UTF8));
this.Response.AddHeader(
"content-disposition",
string.Format("attachment; filename={0}", fileName));
ExcelPackage pkg = new ExcelPackage();
using (pkg)
{
ExcelWorksheet ws = pkg.Workbook.Worksheets.Add("QAT");
ws.Cells["A1"].LoadFromDataTable(table, true);
#region "No need region"
// using (ExcelRange rng = ws.Cells["A1:W1"])
// using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
using (ExcelRange rng = ws.Cells[1, 1, 1, table.Columns.Count])
{
rng.Style.Font.Bold = true;
//Set Pattern for the background to Solid
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
//Set color to dark blue
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));
// rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(122,160,205));
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
#endregion
if (result.Count() > 0)
{
count = result.Count() + 2;
}
pkg.Workbook.Worksheets.FirstOrDefault().DefaultColWidth = 20;
pkg.Workbook.Worksheets.FirstOrDefault().Row(1).Height = 25;
var modelTable = pkg.Workbook.Worksheets.FirstOrDefault().Cells[ws.Dimension.Start.Row, 1, ws.Dimension.Start.Row + table.Rows.Count, table.Columns.Count]; //+ (count - 1)
var border = modelTable.Style.Border.Top.Style = modelTable.Style.Border.Left.Style = modelTable.Style.Border.Right.Style = modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
pkg.Workbook.Properties.Title = "Attempts";
this.Response.BinaryWrite(pkg.GetAsByteArray());
this.Response.End();
}
} // End Export Block
#endregion // End Probes
}
else
{
MessageBox.MessageShow(this.GetType(), "Please Choose Export Date!.", ClientScript);
}
}