protected static DataTable loadFromData(bool includeArchive = false, bool myData = false)
{
DataTable dt = WorkloadItem.WorkItemList_Get(workRequestID: 0
, showArchived: includeArchive ? 1 : 0
, columnListOnly: 0
, myData: myData);
HttpContext.Current.Session["MassChange_FromData"] = dt;
return(dt);
}
protected static DataTable GetWorkItems(int requestID = 0, int showArchived = 0, bool myData = false)
{
DataTable dt = null;
try
{
dt = WorkloadItem.WorkItemList_Get(workRequestID: requestID, showArchived: showArchived, columnListOnly: 0, myData: myData);
}
catch (Exception ex)
{
LogUtility.LogException(ex);
dt = null;
}
return(dt);
}
private void loadGridData(bool bind = true)
{
DataTable dtWork = null;
if (_refreshData || Session["dtWorkItem_QM"] == null)
{
dtWork = WorkloadItem.WorkItemList_Get(workRequestID: 0, showArchived: _includeArchive ? 1 : 0, columnListOnly: 0, myData: _myData);
HttpContext.Current.Session["dtWorkItem_QM"] = dtWork;
}
else
{
dtWork = (DataTable)HttpContext.Current.Session["dtWorkItem_QM"];
}
if (dtWork != null)
{
if (!ShowClosed)
{
dtWork.DefaultView.RowFilter = " STATUS NOT IN ('Closed', 'Approved/Closed') ";
dtWork = dtWork.DefaultView.ToTable();
}
dtWork.Columns["TITLE"].SetOrdinal(dtWork.Columns["ItemID"].Ordinal + 1);
dtWork.Columns["WORKREQUEST"].SetOrdinal(dtWork.Columns["TITLE"].Ordinal + 1);
spanRowCount.InnerText = dtWork.Rows.Count.ToString();
InitializeColumnData_WorkItem(ref dtWork);
dtWork.AcceptChanges();
using (DataTable dtTemp = dtWork.Clone())
{
this.DCC = dtTemp.Columns;
string json = JsonConvert.SerializeObject(DCC, Formatting.None);
Page.ClientScript.RegisterArrayDeclaration("_dcc", json);
}
}
if (_pageIndex > 0)
{
grdWorkload.PageIndex = _pageIndex;
}
grdWorkload.DataSource = dtWork;
if (bind)
{
grdWorkload.DataBind();
}
}
private bool ExportExcel(DataTable dt)
{
bool success = false;
string errorMsg = string.Empty;
try
{
Workbook wb = new Workbook(FileFormatType.Xlsx);
Worksheet ws = wb.Worksheets[0];
StyleFlag flag = new StyleFlag()
{
All = true
};
Aspose.Cells.Style style = new Aspose.Cells.Style();
Aspose.Cells.Style style2 = new Aspose.Cells.Style();
Aspose.Cells.Style style3 = new Aspose.Cells.Style();
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("#E6E6E6");
style2.Pattern = BackgroundType.Solid;
style2.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("LightGreen");
style3.Pattern = BackgroundType.Solid;
style3.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("LightBlue");
DataTable dtDrilldown = WorkloadItem.WorkItemList_Get();
DataTable dtExcel = new DataTable();
RemoveExcelColumns(ref dt);
RenameExcelColumns(ref dt);
dtExcel = dt.Clone();
for (int i = 0; i <= dtExcel.Columns.Count - 1; i++)
{
dtExcel.Columns[i].DataType = typeof(string);
dtExcel.Columns[i].MaxLength = 255;
dtExcel.Columns[i].AllowDBNull = true;
}
foreach (DataRow dr in dt.Rows)
{
dtExcel.ImportRow(dr);
if (dtDrilldown != null && dtDrilldown.Rows.Count > 0)
{
DataTable dtTemp = new DataTable();
dtTemp = dtDrilldown.Copy();
dtTemp.DefaultView.RowFilter = "WORKREQUESTID = " + dr["Request #"];
dtTemp = dtTemp.DefaultView.ToTable();
int count = 0;
foreach (DataRow drTemp in dtTemp.Rows)
{
if (count == 0)
{
dtExcel.Rows.Add("", "#", "System", "Status", "Description", "Allocation Assignment", "Production", "Version", "Priority", "Primary Analyst", "Primary Developer", "Assigned", "Progress", "");
}
dtExcel.Rows.Add("", drTemp["ItemID"].ToString(), drTemp["Websystem"], drTemp["STATUS"], drTemp["TITLE"], drTemp["ALLOCATION"], (drTemp["Production"].ToString().Trim().ToUpper() == "TRUE" ? "Yes" : "No"), drTemp["Version"], drTemp["PRIORITY"], drTemp["Primary_Analyst"], drTemp["Primary_Developer"], drTemp["Assigned"], drTemp["Progress"].ToString(), "");
int itemID = 0;
int.TryParse(drTemp["ItemID"].ToString(), out itemID);
if (itemID > 0)
{
DataTable dtDrilldownTask = WorkloadItem.WorkItem_GetTaskList(workItemID: itemID, showArchived: _showArchived);
if (dtDrilldownTask != null && dtDrilldownTask.Rows.Count > 0)
{
int countTask = 0;
foreach (DataRow drChild in dtDrilldownTask.Rows)
{
if (countTask == 0)
{
dtExcel.Rows.Add("", "", "Task #", "Title", "Planned Start", "Actual Start", "Planned Hours", "Actual Hours", "Actual End", "Assigned", "Status", "Progress");
}
dtExcel.Rows.Add("", "", drChild["WORKITEMID"].ToString() + " - " + drChild["TASK_NUMBER"].ToString(), drChild["Title"], String.Format("{0:M/d/yyyy}", drChild["ESTIMATEDSTARTDATE"].ToString()), String.Format("{0:M/d/yyyy}", drChild["ACTUALSTARTDATE"].ToString()), drChild["PLANNEDHOURS"].ToString(), drChild["ACTUALHOURS"].ToString(), String.Format("{0:M/d/yyyy}", drChild["ACTUALENDDATE"].ToString()), drChild["AssignedResource"], drChild["STATUS"], drChild["COMPLETIONPERCENT"].ToString());
countTask++;
}
}
}
count++;
}
}
}
string name = "Work Request";
ws.Cells.ImportDataTable(dtExcel, true, 0, 0, false, false);
for (int j = 0; j <= ws.Cells.Rows.Count - 1; j++)
{
if (ws.Cells.Rows[j][0].Value == "Request #")
{
Range range = ws.Cells.CreateRange(j, 0, 1, 15);
range.ApplyStyle(style, flag);
}
if (ws.Cells.Rows[j][1].Value == "#")
{
Range range = ws.Cells.CreateRange(j, 1, 1, 12);
range.ApplyStyle(style2, flag);
}
if (ws.Cells.Rows[j][2].Value == "Task #")
{
Range range = ws.Cells.CreateRange(j, 2, 1, 10);
range.ApplyStyle(style3, flag);
}
}
//WTSUtility.FormatWorkbookHeader(ref wb, ref ws, 0, 0, 1, dt.Columns.Count);
ws.AutoFitColumns();
MemoryStream ms = new MemoryStream();
wb.Save(ms, SaveFormat.Xlsx);
Response.ContentType = "application/xlsx";
Response.AddHeader("content-disposition", "attachment; filename=" + name + ".xlsx");
Response.BinaryWrite(ms.ToArray());
Response.End();
success = true;
}
catch (Exception ex)
{
success = false;
errorMsg += Environment.NewLine + ex.Message;
}
return(success);
}