private void loginBtnClick(object sender, EventArgs e)
{
SecurityToken token = new SecurityToken();
CECMembershipProvider prov = (Membership.Providers["CECProvider"] as CECMembershipProvider);
try
{
string validation_errors = string.Empty;
if (String.IsNullOrWhiteSpace(usernameIn.Value))
{
validation_errors += "<p>Username cannot be blank</p>";
}
if (String.IsNullOrWhiteSpace(passwordIn.Value))
{
validation_errors += "<p>Password cannot be blank</p>";
}
if (!String.IsNullOrEmpty(validation_errors))
{
throw new Exception(validation_errors);
}
if (prov.ValidateUser(usernameIn.Value, passwordIn.Value, out token))
{
string local_userfiles_path = MapPath(String.Format("/user_files/{0}", token.userid));
if (!Directory.Exists(local_userfiles_path))
{
Directory.CreateDirectory(local_userfiles_path);
}
using (cec_publicservice.CECInputFormService websrv = new CECInputFormService())
{
// verify/handle user account conditions
UserData ud = websrv.GetUserInformationByUserID(token, token.userid);
using (cec_publicservice.CECHarmPublicService pubsrv = new CECHarmPublicService())
{
if (ud.account_lockout)
{
pubsrv.AuditLog_AddActivity(ud.userid, "login attempted; failed due to lockout");
throw new AccountLockedOutException(ud.email, ud.account_lockout_date);
}
// user was successfully authenticated, so set the auth cookie
FormsAuthentication.SetAuthCookie(token.email, false);
Session["UserSecurityToken"] = token;
Response.SetCookie(new HttpCookie("sessionid", token.session));
Response.SetCookie(new HttpCookie("uid", token.userid.ToString()));
Response.SetCookie(new HttpCookie("edit_mode", ""));
pubsrv.AuditLog_AddActivity(ud.userid, "login");
}
if (ud.password_reset_required || ud.password_expired)
{
Page.Response.Redirect("/userinfo.aspx?resetPassword", false);
}
else
{
Page.Response.Redirect("/input/bouncer.aspx", false);
}
//FormsAuthentication.RedirectFromLoginPage(token.email, false);
}
}
else
{
throw new Exception("Unknown login failure");
}
}
catch (Exception ex)
{
string script = "<script type=\"text/javascript\"> $(function() { $('#invalidlogin_msg').html('" + ex.Message + "').show(); $('#login_dialog').modal('show'); }); </script>";
Page.ClientScript.RegisterStartupScript(this.GetType(), "login_errors", script);
}
}
private string ExportDataGridToExcel(DataTable toExport, string savePath)
{
/// excel writer row index
int rowIndex = 0;
try
{
CECHarmPublicService ps =
new CECHarmPublicService();
NPOI.XSSF.UserModel.XSSFWorkbook wkbk =
new XSSFWorkbook();
NPOI.SS.UserModel.ISheet wkst = wkbk.CreateSheet();
wkbk.SetActiveSheet(0);
/// write header to excel
///
NPOI.SS.UserModel.IRow dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue(String.Format("Cohort Data Export Generated from the CEDCD Website ({0})", Request.Url.Authority));
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Table Name:");
dataRow.CreateCell(1).SetCellValue("Cancer Counts");
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Export Date:");
dataRow.CreateCell(1).SetCellValue(DateTime.Now.ToString("MM/dd/yyyy"));
// gender selections
string tAss = string.Empty;
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Selected Gender(s):");
foreach (string s in Genders)
{
tAss += String.Format("{0}, ", s);
}
tAss = (new CultureInfo("en-US").TextInfo.ToTitleCase(tAss.TrimEnd(new char[] { ' ', ',' })));
dataRow.CreateCell(1).SetCellValue(tAss);
// cancer category
tAss = string.Empty;
//dataRow = wkst.CreateRow(rowIndex++);
//dataRow.CreateCell(0).SetCellValue("Cancer Category:");
//dataRow.CreateCell(1).SetCellValue((new CultureInfo("en-US").TextInfo.ToTitleCase(CancerCategory)));
// cancer selections
tAss = string.Empty;
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Selected Cancer Type(s):");
foreach (string c in CancerTypes)
{
tAss += String.Format("{0}, ", select_cancer.GetCancerLabel(c));
}
tAss = (new CultureInfo("en-US").TextInfo.ToTitleCase(tAss.TrimEnd(new char[] { ' ', ',' })));
dataRow.CreateCell(1).SetCellValue(tAss);
// cohort selections
tAss = string.Empty;
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Selected Cohort(s):");
foreach (string s in CohortIDsToCompare)
{
tAss += String.Format("{0}, ", select_cohort.GetCohortAcronym(int.Parse(s)));
}
tAss = (new CultureInfo("en-US").TextInfo.ToTitleCase(tAss.TrimEnd(new char[] { ' ', ',' })));
dataRow.CreateCell(1).SetCellValue(tAss);
rowIndex += 2;
///--------------------------------------------------------
/// column headers
///
NPOI.SS.UserModel.IRow headerRow = wkst.CreateRow(rowIndex++);
for (int _c = 0; _c < toExport.Columns.Count; _c++)
{
ICell c = headerRow.GetCell(_c);
if (c == null)
{
c = headerRow.CreateCell(_c);
}
c.SetCellValue(toExport.Columns[_c].ColumnName);
}
if (toExport.Rows.Count > 1)
{
/// data rows
for (int _i = 0; _i < toExport.Rows.Count; _i++)
{
int colPos = 0;
/// create data row object then step through each cell to populate the excel row
dataRow = wkst.CreateRow(rowIndex++);
for (int _p = 0; _p < toExport.Columns.Count; _p++)
{
/// get first cell and check for null, if null create cell
ICell c = dataRow.GetCell(colPos);
if (c == null)
{
c = dataRow.CreateCell(colPos);
}
string cellVal = toExport.Rows[_i][_p].ToString();
/// position 0 is cancer type, position 1 is male/female
if (_p == 0)
{
c.SetCellValue(select_cancer.GetCancerLabel(cellVal));
}
else if (helper.IsStringEmptyWhiteSpace(cellVal) || cellVal == " " || cellVal == "-1")
{
/// ---------------------------------------
/// was N/P, per Amy 23-April, 0 is preferred
/// per Amy, 4-May, 0 should appear if the cohort has incident cancers--even for prevelant;
/// show N/P if incident and prevelant is not provided
if (String.IsNullOrWhiteSpace(cellVal) || cellVal == " ")
{
//if (CECWebSrv.CohortHasCancerIndicator(UserToken, toExport.Columns[_p].ColumnName))
cellVal = "0";
//else
// cellVal = "N/P";
}
else
{
cellVal = "N/P";
}
c.SetCellValue(cellVal);
}
else
{
c.SetCellValue(cellVal);
}
colPos++;
}
}
}
else
{
ICell c = wkst.CreateRow(rowIndex++).GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(String.Format("None of the select cohorts reported any 'todo' cancers"));
}
/// write output
FileStream fs = new FileStream(savePath, FileMode.Create);
wkbk.Write(fs);
fs.Close();
return(savePath);
}
catch (Exception ex)
{
throw ex;
}
}
private string GenerateExcelReport(int report, string savePath)
{
/// excel writer row index
int rowIndex = 0;
try
{
NPOI.XSSF.UserModel.XSSFWorkbook workbook =
new XSSFWorkbook();
NPOI.SS.UserModel.ISheet wkst = workbook.CreateSheet();
workbook.SetActiveSheet(0);
// sets up basic styles for Excel
NPOI.SS.UserModel.ICellStyle HeaderCellStyle = workbook.CreateCellStyle();
HeaderCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeaderCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeaderCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeaderCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeaderCellStyle.VerticalAlignment = VerticalAlignment.Center;
HeaderCellStyle.Alignment = HorizontalAlignment.Center;
HeaderCellStyle.FillPattern = FillPattern.SolidForeground;
HeaderCellStyle.FillForegroundColor = IndexedColors.LightCornflowerBlue.Index;
IFont headerFont = workbook.CreateFont();
headerFont.Boldweight = (short)FontBoldWeight.Bold;
HeaderCellStyle.SetFont(headerFont);
NPOI.SS.UserModel.ICellStyle DataCellStyle = workbook.CreateCellStyle();
DataCellStyle.VerticalAlignment = VerticalAlignment.Top;
DataCellStyle.WrapText = true;
ArrayList query_for_status = new ArrayList();
string query_columns = string.Empty,
ignore_columns = "id,cohort_id,status_timestamp,published",
column_headers = string.Empty;
switch (report)
{
case 0:
query_columns = "id,cohort_id,cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status,status_timestamp";
query_for_status.Add("pending");
query_for_status.Add("rejected");
query_for_status.Add("inprogress");
ignore_columns = "id,cohort_id";
column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Received,CEDCD Form Reviewed,Date CEDCD Form Published";
break;
case 1:
query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status,status_timestamp";
query_for_status.Add("pending");
query_for_status.Add("rejected");
query_for_status.Add("inprogress");
ignore_columns = string.Empty;
column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Current Status,Current Status Date";
break;
case 2:
query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status_timestamp";
query_for_status.Add("published");
ignore_columns = string.Empty;
column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Published";
break;
case 3:
query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status_timestamp [Date CEDCD Form was Unpublished]";
query_for_status.Add("unpublished");
ignore_columns = string.Empty;
column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Unpublished";
break;
case 4:
query_columns = "*";
query_for_status.Add("published");
query_for_status.Add("pending");
break;
default:
query_columns = "*";
query_for_status.Add("published");
break;
}
DataTable dt_records = CECWebSrv.GetCohortsWithStatusesWithColumns(UserToken, (string[])query_for_status.ToArray(typeof(string)), query_columns);
/// write header to excel
///
NPOI.SS.UserModel.IRow dataRow = wkst.CreateRow(rowIndex++);
//dataRow.CreateCell(0).SetCellValue(String.Format("Cohort Data Export Generated from the CEDCD Website ({0})", Request.Url.Authority));
//dataRow = wkst.CreateRow(rowIndex++);
//dataRow.CreateCell(0).SetCellValue("Export Date:");
//dataRow.CreateCell(1).SetCellValue(DateTime.Now.ToString("MM/dd/yyyy"));
//rowIndex += 2;
int colPos = 0;
///--------------------------------------------------------
/// column headers
///
NPOI.SS.UserModel.IRow headerRow = wkst.CreateRow(rowIndex++);
if (column_headers != string.Empty)
{
foreach (string s in column_headers.Split(','))
{
ICell c = headerRow.GetCell(colPos++, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(s);
c.CellStyle = HeaderCellStyle;
}
}
else
{
using (CECHarmPublicService ps = new CECHarmPublicService())
{
for (int _c = 0; _c < dt_records.Columns.Count; _c++)
{
if (ignore_columns.Contains(dt_records.Columns[_c].ColumnName))
{
continue;
}
ICell c = headerRow.GetCell(colPos);
if (c == null)
{
c = headerRow.CreateCell(colPos);
}
string column_name = ps.GetCohortWebFieldLabelByColumnName(UserToken, dt_records.Columns[_c].ColumnName);
if (String.IsNullOrWhiteSpace(column_name))
{
column_name = dt_records.Columns[_c].ColumnName;
}
c.SetCellValue(column_name);
c.CellStyle = HeaderCellStyle;
colPos++;
}
}
}
if (report != 0 && dt_records.Rows.Count >= 1)
{
/// data rows
for (int _i = 0; _i < dt_records.Rows.Count; _i++)
{
colPos = 0;
/// create data row object then step through each cell to populate the excel row
dataRow = wkst.CreateRow(rowIndex++);
for (int _p = 0; _p < dt_records.Columns.Count; _p++)
{
if (ignore_columns.Contains(dt_records.Columns[_p].ColumnName))
{
continue;
}
/// get first cell and check for null, if null create cell
ICell c = dataRow.GetCell(colPos);
if (c == null)
{
c = dataRow.CreateCell(colPos);
}
string cellVal = dt_records.Rows[_i][_p].ToString();
if (helper.IsStringEmptyWhiteSpace(cellVal) || cellVal == " " || cellVal == "-1")
{
cellVal = "N/P";
c.SetCellValue(cellVal);
}
else if (dt_records.Columns[_p].DataType == typeof(DateTime))
{
c.SetCellValue(DateTime.Parse(cellVal).ToString("MM/dd/yyyy"));
}
else if (dt_records.Columns[_p].ColumnName == "status")
{
switch (cellVal)
{
case "inprogress":
c.SetCellValue("Draft In Progress");
break;
case "pending":
c.SetCellValue("Under NCI Review");
break;
case "rejected":
c.SetCellValue("Returned to Cohort");
break;
default:
c.SetCellValue(cellVal);
break;
}
}
else
{
c.SetCellValue(cellVal);
}
c.CellStyle = DataCellStyle;
colPos++;
}
}
}
else if (report == 0 && dt_records.Rows.Count >= 1)
{
foreach (DataRow dr in dt_records.Rows)
{
dataRow = wkst.CreateRow(rowIndex++);
// cohort acronym
ICell c = dataRow.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["cohort_acronym"].ToString());
c.CellStyle = DataCellStyle;
// cohort name
c = dataRow.GetCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["cohort_name"].ToString());
c.CellStyle = DataCellStyle;
// pi name
c = dataRow.GetCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["pi_name_1"].ToString());
c.CellStyle = DataCellStyle;
// pi institution
c = dataRow.GetCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["pi_institution_1"].ToString());
c.CellStyle = DataCellStyle;
// pi email
c = dataRow.GetCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["pi_email_1"].ToString());
c.CellStyle = DataCellStyle;
// the more complicated stuff...
if (dr["status"].ToString() == "pending")
{
c = dataRow.GetCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(((DateTime)dr["status_timestamp"]).ToString("MM/dd/yyyy"));
c.CellStyle = DataCellStyle;
c = dataRow.GetCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue("No");
c.CellStyle = DataCellStyle;
using (DataTable dt_temp = CECWebSrv.GetCohortRecordById(UserToken, (int)dr["cohort_id"], false))
{
if (dt_temp.Rows.Count > 0)
{
c = dataRow.GetCell(7, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(((DateTime)dt_temp.Rows[0]["status_timestamp"]).ToString("MM/dd/yyyy"));
c.CellStyle = DataCellStyle;
}
}
}
else if (dr["status"].ToString() == "rejected" || dr["status"].ToString() == "inprogress")
{
c = dataRow.GetCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK);
if (dr["status"].ToString() == "rejected")
{
using (DataTable dt_temp = (new CECHarmPublicService()).AuditLog_GetActivities(UserToken, "submitted", (int)dr["id"]))
{
if (dt_temp.Rows.Count == 0)
{
c.SetCellValue(" ");
}
else
{
c.SetCellValue(((DateTime)dt_temp.Rows[0]["create_date"]).ToString("MM/dd/yyyy"));
}
}
}
else
{
c.SetCellValue(((DateTime)dr["status_timestamp"]).ToString("MM/dd/yyyy"));
}
c.CellStyle = DataCellStyle;
c = dataRow.GetCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue("Pending Revisions");
c.CellStyle = DataCellStyle;
using (DataTable dt_temp = CECWebSrv.GetCohortRecordById(UserToken, (int)dr["cohort_id"], false))
{
if (dt_temp.Rows.Count > 0)
{
c = dataRow.GetCell(7, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(((DateTime)dt_temp.Rows[0]["status_timestamp"]).ToString("MM/dd/yyyy"));
c.CellStyle = DataCellStyle;
}
}
}
}
}
else
{
ICell c = wkst.CreateRow(rowIndex++).GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(String.Format("Nothing to report"));
}
for (int _ic = 0; _ic <= headerRow.PhysicalNumberOfCells; _ic++)
{
wkst.AutoSizeColumn(_ic);
}
/// write output
FileStream fs = new FileStream(savePath, FileMode.Create);
workbook.Write(fs);
fs.Close();
return(savePath);
}
catch (Exception ex)
{
throw ex;
}
}
private string ExportDataGridToExcel(DataTable toExport, string savePath)
{
/// excel writer row index
int rowIndex = 0;
try
{
CECHarmPublicService ps =
new CECHarmPublicService();
NPOI.XSSF.UserModel.XSSFWorkbook wkbk =
new XSSFWorkbook();
NPOI.SS.UserModel.ISheet wkst = wkbk.CreateSheet();
wkbk.SetActiveSheet(0);
/// write header to excel
NPOI.SS.UserModel.IRow dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue(String.Format("Cohort Data Export Generated from the CEDCD Website ({0})", Request.Url.Authority));
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Table Name:");
dataRow.CreateCell(1).SetCellValue("Cohort Selection");
//string filter = String.Empty;
//foreach (CECFilteringOptions tree in FilterControls)
//{
// foreach (CheckBox ck in tree.GetCheckedBoxes())
// filter += String.Format("{0}; ", tree.GetCheckBoxLabel(ck.ID));
//}
//filter = filter.TrimEnd(new char[] { ';', ' ' });
//dataRow = wkst.CreateRow(rowIndex++);
//dataRow.CreateCell(0).SetCellValue("Web Filter Options:");
//dataRow.CreateCell(1).SetCellValue(filter);
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Export Date:");
dataRow.CreateCell(1).SetCellValue(DateTime.Now.ToString("MM/dd/yyyy"));
rowIndex += 2;
/// handle header row
dataRow = wkst.CreateRow(rowIndex++);
for (int _c = 0; _c < toExport.Columns.Count; _c++)
{
ICell c = dataRow.GetCell(_c);
if (c == null)
{
c = dataRow.CreateCell(_c);
}
c.SetCellValue(CECWebSrv.GetCohortWebFieldLabelByColumnName(UserToken, toExport.Columns[_c].ColumnName));
}
/// data rows
for (int _i = 0; _i < toExport.Rows.Count; _i++)
{
int colPos = 0;
/// create data row object then step through each cell to populate the excel row
dataRow = wkst.CreateRow(rowIndex++);
for (int _p = 0; _p < toExport.Columns.Count; _p++)
{
/// get first cell and check for null, if null create cell
ICell c = dataRow.GetCell(colPos);
if (c == null)
{
c = dataRow.CreateCell(colPos);
}
string cellVal = toExport.Rows[_i][_p].ToString();
if (helper.IsStringEmptyWhiteSpace(cellVal) || cellVal == " ")
{
c.SetCellValue(" ");
}
else
{
c.SetCellValue(cellVal);
}
colPos++;
}
}
/// write output
FileStream fs = new FileStream(savePath, FileMode.Create);
wkbk.Write(fs);
fs.Close();
return(savePath);
}
catch (Exception ex)
{
throw ex;
}
}
private string ExportDataGridToExcel(DataTable toExport, string savePath)
{
/// excel writer row index
int rowIndex = 0;
try
{
CECHarmPublicService ps =
new CECHarmPublicService();
NPOI.XSSF.UserModel.XSSFWorkbook wkbk =
new XSSFWorkbook();
NPOI.SS.UserModel.ISheet wkst = wkbk.CreateSheet();
wkbk.SetActiveSheet(0);
/// write header to excel
///
NPOI.SS.UserModel.IRow dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue(String.Format("Cohort Data Export Generated from the CEDCD Website ({0})", Request.Url.Authority));
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Table Name:");
dataRow.CreateCell(1).SetCellValue(String.Format("Cohort Overview Tab: {0}", CompareTabNames[SelectedTabID - 1]));
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Export Date:");
dataRow.CreateCell(1).SetCellValue(DateTime.Now.ToString("MM/dd/yyyy"));
rowIndex += 2;
///--------------------------------------------------------
/// column headers
///
NPOI.SS.UserModel.IRow headerRow = wkst.CreateRow(rowIndex++);
for (int _c = 0; _c < toExport.Columns.Count; _c++)
{
ICell c = headerRow.GetCell(_c);
if (c == null)
{
c = headerRow.CreateCell(_c);
}
c.SetCellValue(toExport.Columns[_c].ColumnName);
}
/// data rows
for (int _i = 0; _i < toExport.Rows.Count; _i++)
{
int colPos = 0;
/// create data row object then step through each cell to populate the excel row
dataRow = wkst.CreateRow(rowIndex++);
for (int _p = 0; _p < toExport.Columns.Count; _p++)
{
/// get first cell and check for null, if null create cell
ICell c = dataRow.GetCell(colPos);
if (c == null)
{
c = dataRow.CreateCell(colPos);
}
string cellVal = toExport.Rows[_i][_p].ToString();
if (helper.IsStringEmptyWhiteSpace(cellVal) || cellVal == " " || cellVal == "-1")
{
c.SetCellValue("N/P");
}
else if (!helper.IsNumerical(cellVal) && CECWebSrv.GetCohortWebFieldLabelByColumnName(UserToken, cellVal) != string.Empty)
{
c.SetCellValue(CECWebSrv.GetCohortWebFieldLabelByColumnName(UserToken, cellVal));
}
else
{
c.SetCellValue(cellVal);
}
colPos++;
}
}
/// write output
FileStream fs = new FileStream(savePath, FileMode.Create);
wkbk.Write(fs);
fs.Close();
return(savePath);
}
catch (Exception ex)
{
throw ex;
}
}
private string ExportDataGridToExcel(DataTable toExport, string savePath)
{
/// excel writer row index
int rowIndex = 0;
try
{
CECHarmPublicService ps =
new CECHarmPublicService();
NPOI.XSSF.UserModel.XSSFWorkbook wkbk =
new XSSFWorkbook();
NPOI.SS.UserModel.ISheet wkst = wkbk.CreateSheet();
wkbk.SetActiveSheet(0);
/// write header to excel
NPOI.SS.UserModel.IRow dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue(String.Format("Cohort Data Export Generated from the CEDCD Website ({0})", Request.Url.Authority));
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Table Name:");
dataRow.CreateCell(1).SetCellValue("Biospecimen Counts");
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Export Date:");
dataRow.CreateCell(1).SetCellValue(DateTime.Now.ToString("MM/dd/yyyy"));
// specimen type selections
string tAss = string.Empty;
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Specimen Type(s):");
foreach (string s in SelectedSpecimenTypes)
{
tAss += String.Format("{0}, ", select_specimenTypes.GetSpecimenTypeLabel(s));
}
tAss = (new CultureInfo("en-US").TextInfo.ToTitleCase(tAss.TrimEnd(new char[] { ' ', ',' })));
dataRow.CreateCell(1).SetCellValue(tAss);
// cancer type selections
tAss = string.Empty;
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Cancer Type(s):");
foreach (string s in SelectedCancers)
{
tAss += String.Format("{0}, ", select_cancer.GetCancerLabel(s));
}
tAss = (new CultureInfo("en-US").TextInfo.ToTitleCase(tAss.TrimEnd(new char[] { ' ', ',' })));
dataRow.CreateCell(1).SetCellValue(tAss);
// cohort selections
tAss = string.Empty;
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Selected Cohort(s):");
foreach (string s in CohortIDsToCompare)
{
tAss += String.Format("{0}, ", select_cohort.GetCohortAcronym(int.Parse(s)));
}
tAss = (new CultureInfo("en-US").TextInfo.ToTitleCase(tAss.TrimEnd(new char[] { ' ', ',' })));
dataRow.CreateCell(1).SetCellValue(tAss);
rowIndex += 2;
///--------------------------------------------------------
/// column headers
///
NPOI.SS.UserModel.IRow headerRow = wkst.CreateRow(rowIndex++);
for (int _c = 0; _c < toExport.Columns.Count; _c++)
{
ICell c = headerRow.GetCell(_c);
if (c == null)
{
c = headerRow.CreateCell(_c);
}
c.SetCellValue(toExport.Columns[_c].ColumnName);
}
/// data rows
for (int _i = 0; _i < toExport.Rows.Count; _i++)
{
int colPos = 0;
/// create data row object then step through each cell to populate the excel row
dataRow = wkst.CreateRow(rowIndex++);
for (int _p = 0; _p < toExport.Columns.Count; _p++)
{
/// get first cell and check for null, if null create cell
ICell c = dataRow.GetCell(colPos);
if (c == null)
{
c = dataRow.CreateCell(colPos);
}
string cellVal = toExport.Rows[_i][_p].ToString();
if (_p == 0)
{
c.SetCellValue(CECWebSrv.GetCohortWebFieldLabelByColumnName(UserToken, cellVal));
}
else if (_p == 1)
{
c.SetCellValue(select_cancer.GetCancerLabel(cellVal));
}
else if (helper.IsStringEmptyWhiteSpace(cellVal) || cellVal == " " || cellVal == "-1")
{
c.SetCellValue("N/P");
}
else if (!helper.IsNumerical(cellVal) && CECWebSrv.GetCohortWebFieldLabelByColumnName(UserToken, cellVal) != string.Empty)
{
c.SetCellValue(CECWebSrv.GetCohortWebFieldLabelByColumnName(UserToken, cellVal));
}
else
{
c.SetCellValue(cellVal);
}
colPos++;
}
}
/// write output
FileStream fs = new FileStream(savePath, FileMode.Create);
wkbk.Write(fs);
fs.Close();
return(savePath);
}
catch (Exception ex)
{
throw ex;
}
}
