protected void btnReport_Click(object sender, EventArgs e)
{
DataTable dt = CamperHoldingDA.GetData(Int32.Parse(ddlCampYear.SelectedItem.Text));
string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
string workFileDir = Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile excel = new ExcelFile();
excel.LoadXls(templateFile);
ExcelWorksheet ws = excel.Worksheets["Sheet1"];
ws.Name = "Campers Holding";
ws.InsertDataTable(dt, 0, 0, true);
CellStyle tableHeaderStyle = new CellStyle();
tableHeaderStyle.Font.Weight = ExcelFont.BoldWeight;
tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);
ws.Rows[0].Style = tableHeaderStyle;
ws.Columns[0].Width = 20 * 256;
ws.Columns[1].Width = 20 * 256;
ws.Columns[2].Width = 30 * 256;
ws.Columns[4].Width = 17 * 256;
ws.Columns[5].Width = 42 * 256;
ws.Columns[6].Width = 40 * 256;
ws.Columns[7].Width = 16 * 256;
ws.Columns[8].Width = 16 * 256;
// Save to a file on the local file system
string filename = String.Format("\\{0}{1}{2}{3}CamperHoldingReport.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
//Display excel spreadsheet
this.Response.Clear();
this.Response.Buffer = true;
this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.Charset = "";
if (newFile.Length == 0)
{
this.Response.Write("Error encountered - no spreadsheet to display");
}
else
{
this.Response.WriteFile(newFile);
}
Response.End();
}
protected void btnReport_Click(object sender, EventArgs e)
{
DataSet ds;
// Work sheet #1 - Data grouped by camp
// the data content section of the report
if (param.CamperOrg == CamperOrgType.CamperContactInfo)
{
ds = CamperApplicationBL.GetCamperContactInfoReportInBatch(UserRole, param.FedID, param.FedID_List, param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant);
}
else if (param.CamperOrg == CamperOrgType.CamperDetailReport)
{
ds = CamperApplicationBL.GetCamperDetailReportInBatch(UserRole, param.FedID, param.FedID_List, param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant);
}
else
{
ds = CamperApplicationBL.GetFJCCamperReportInBatch(param.CamperOrg, param.ProgramTypeID, param.FedID, param.CampYearID, param.CampID_HaveData_List, param.StatusID_List, param.TimesReceivedGrant);
}
string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
string workFileDir = Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile excel = new ExcelFile();
excel.LoadXls(templateFile);
ExcelWorksheet ws = excel.Worksheets["Sheet2"];
//We start at first row, because for ExcelLite control, the header row is not included
int BEGIN_COLUMN_INDEX;
if (param.CamperOrg == CamperOrgType.CamperContactInfo || param.CamperOrg == CamperOrgType.CamperDetailReport)
{
BEGIN_COLUMN_INDEX = 0;
}
else
{
BEGIN_COLUMN_INDEX = 1;
}
const int CAMP_NAME_MERGED_CELL_NUMBER = 6;
const int REPORT_HEADER_CELL_NUMBER = 6;
const int REPORT_SUB_HEADER_CELL_NUMBER = 4;
int iRow = 1;
// Global artistic seeting
ws.Columns[0].Width = 20 * 20;
// Create Report Header
CellStyle styleReportHeader = new CellStyle();
styleReportHeader.Font.Color = System.Drawing.Color.Blue;
styleReportHeader.Font.Size = 22 * 20;
styleReportHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
ReportHeader.Merged = true;
ReportHeader.Style = styleReportHeader;
var fileName = "report";
ReportHeader.Value = fileName = GetReportName(param.CamperOrg);
ws.Name = "By Camp";
//
iRow += 1;
// Create Report SubHeader
CellStyle styleSubHeader = new CellStyle();
styleSubHeader.Font.Size = 16 * 20;
styleSubHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
SubHeader.Merged = true;
SubHeader.Value = String.Format("Camp Year: {0} Generated on {1}", param.CampYear, DateTime.Now);
SubHeader.Style = styleSubHeader;
iRow += 2;
CellStyle cs = new CellStyle();
//cs.Font.Color = System.Drawing.Color.Red;
cs.Font.Size = 18 * 20;
cs.Font.Weight = ExcelFont.BoldWeight;
CellStyle csFirst = new CellStyle();
csFirst.FillPattern.SetSolid(Color.Yellow);
csFirst.Font.Weight = ExcelFont.BoldWeight;
csFirst.WrapText = true;
ws.Columns[0].Width = 20 * 256;
CellStyle tableHeaderStyle = new CellStyle();
tableHeaderStyle.Font.Weight = ExcelFont.BoldWeight;
tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);
CellStyle redText = new CellStyle();
redText.Font.Color = Color.Red;
foreach (DataTable dt in ds.Tables)
{
CellRange cr = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);
cr.Merged = true;
cr.Value = dt.Rows[0]["Camp Name"].ToString();
//if (param.CamperOrg == CamperOrgType.CamperContactInfo)
//{
// cr.Value = dt.Rows[0]["Camp Name"].ToString();
//}
//else
//{
// cr.Value = param.CampsThatHaveDataDict.ElementAt(ds.Tables.IndexOf(dt)).Value;
//}
cr.Style = cs;
ws.Rows[iRow].Height = 25 * 20;
iRow += 1;
ws.Rows[iRow].Height = 25 * 20;
CellRange crFirst = ws.Cells.GetSubrangeAbsolute(iRow, 0, iRow, 0);
if (param.CamperOrg != CamperOrgType.CamperContactInfo && param.CamperOrg != CamperOrgType.CamperDetailReport)
{
crFirst.Value = "Registered at Camp: Enter a Y / N here";
crFirst.Style = csFirst;
}
ws.InsertDataTable(dt, iRow, BEGIN_COLUMN_INDEX, true);
// 2012-10-28 Need to make allow contact emails stand out so admins won't contact campers who wish not to be contacted
if (param.CamperOrg == CamperOrgType.CamperContactInfo)
{
for (int i = 0; i <= dt.Rows.Count; i++)
{
if (ws.Cells[i + iRow, 14].Value.ToString() == "No")
{
ws.Cells[i + iRow, 14].Style = redText;
ws.Cells[i + iRow, 15].Style = redText;
ws.Cells[i + iRow, 16].Style = redText;
}
}
}
else
{
for (int i = 0; i <= dt.Rows.Count; i++)
{
//if (ws.Cells[i + iRow, 14].Value.ToString() == "No")
//{
// ws.Cells[i + iRow, 14].Style = redText;
// ws.Cells[i + iRow, 15].Style = redText;
//}
}
}
// loop through each column and 1.set the width of each colum, 2. set the header style of each column
for (int i = BEGIN_COLUMN_INDEX; i <= dt.Columns.Count; i++)
{
if (param.CamperOrg == CamperOrgType.CamperContactInfo)
{
ws.Cells[iRow, i].Style = tableHeaderStyle;
if (i == 2)
{
ws.Columns[i].Width = 22 * 256;
}
else if (i == 3)
{
ws.Columns[i].Width = 16 * 256;
}
else if (i == 4)
{
ws.Columns[i].Width = 16 * 256;
}
else if (i == 5)
{
ws.Columns[i].Width = 16 * 256;
}
else if (i == 7)
{
ws.Columns[i].Width = 23 * 256;
}
else if (i == 5)
{
ws.Columns[i].Width = 15 * 256;
}
else if (i == 11)
{
ws.Columns[i].Width = 26 * 256;
}
else if (i == 12)
{
ws.Columns[i].Width = 15 * 256;
}
else if (i == 13)
{
ws.Columns[i].Width = 10 * 256;
}
else if (i == 14)
{
ws.Columns[i].Width = 10 * 256;
}
else
{
ws.Columns[i].Width = 20 * 256;
}
}
else
{
ws.Cells[iRow, i].Style = tableHeaderStyle;
if (i == 3)
{
ws.Columns[i].Width = 30 * 256;
}
else if (i == 4)
{
ws.Columns[i].Width = 16 * 256;
}
else if (i == 5)
{
ws.Columns[i].Width = 16 * 256;
}
else if (i == 7)
{
ws.Columns[i].Width = 23 * 256;
}
else if (i == 5)
{
ws.Columns[i].Width = 15 * 256;
}
else if (i == 11)
{
ws.Columns[i].Width = 26 * 256;
}
else if (i == 12)
{
ws.Columns[i].Width = 15 * 256;
}
else
{
ws.Columns[i].Width = 20 * 256;
}
}
}
iRow += dt.Rows.Count + 2;
DataTable dtTotal;
if (param.CamperOrg == CamperOrgType.EnrollmentConfirmationFJC || param.CamperOrg == CamperOrgType.EnrollmentConfirmationPartner)
{
dtTotal = CreateTotalTablePerCamp(dt);
}
else if (param.CamperOrg == CamperOrgType.Synagogue)
{
dtTotal = CreateTotalTablePerSynag(dt);
}
else
{
dtTotal = null;
}
if (param.CamperOrg != CamperOrgType.CamperContactInfo && param.CamperOrg != CamperOrgType.CamperDetailReport)
{
ws.InsertDataTable(dtTotal, iRow, BEGIN_COLUMN_INDEX, true);
for (int i = BEGIN_COLUMN_INDEX; i <= dtTotal.Columns.Count; i++)
{
ws.Cells[iRow, i].Style = tableHeaderStyle;
}
iRow += dtTotal.Rows.Count + 3;
}
}
// ************************** second tab ********************************
// Second alternate sheet that list all rows as table format
// Camper Detail Report (for Program admin) doesn't need to show the whole list
if (param.CamperOrg != CamperOrgType.EnrollmentConfirmationFJC && param.CamperOrg != CamperOrgType.EnrollmentConfirmationPartner)
{
var ws2 = excel.Worksheets["Sheet1"];
ws2.Name = fileName;
DataTable dtAlternate;
if (param.CamperOrg == CamperOrgType.CamperContactInfo)
{
dtAlternate = CamperApplicationDA.GetCamperContactInfoReportInBatch(UserRole, param.FedID, param.FedID_List,
param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant);
}
else if (param.CamperOrg == CamperOrgType.CamperDetailReport)
{
dtAlternate = CamperApplicationDA.GetCamperDetailReportInBatch(UserRole, param.FedID, param.FedID_List,
param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant);
}
else
{
dtAlternate = CamperApplicationDA.GetFJCCamperReportInBatch(param.CamperOrg, param.ProgramTypeID,
param.FedID, param.CampYearID, param.CampID_HaveData_List, param.StatusID_List);
}
dtAlternate.Columns.RemoveAt(0); // We don't want the first column, which is CampID
iRow = 1;
ReportHeader = ws2.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
ReportHeader.Merged = true;
ReportHeader.Style = styleReportHeader;
ReportHeader.Value = GetReportName(param.CamperOrg);
ws2.Rows[iRow].Height = 25 * 20;
iRow += 1;
ws2.Rows[iRow].Height = 25 * 20;
// Create Report SubHeader
SubHeader = ws2.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
SubHeader.Merged = true;
SubHeader.Value = "Camp Year: " + param.CampYear;
SubHeader.Style = styleSubHeader;
iRow += 2;
ws2.InsertDataTable(dtAlternate, iRow, BEGIN_COLUMN_INDEX, true);
// 2012-10-28 Need to make allow contact emails stand out so admins won't contact campers who wish not to be contacted
if (param.CamperOrg == CamperOrgType.CamperContactInfo)
{
for (int i = 0; i <= dtAlternate.Rows.Count; i++)
{
if (ws2.Cells[i + iRow, 14].Value.ToString() == "No")
{
ws2.Cells[i + iRow, 14].Style = redText;
ws2.Cells[i + iRow, 15].Style = redText;
ws2.Cells[i + iRow, 16].Style = redText;
}
}
}
ws2.Rows[iRow].Style = tableHeaderStyle;
if (param.CamperOrg == CamperOrgType.CamperContactInfo)
{
ws2.Columns[BEGIN_COLUMN_INDEX].Width = 16 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 1].Width = 28 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 2].Width = 28 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 3].Width = 14 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 4].Width = 16 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 5].Width = 23 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 10].Width = 26 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 11].Width = 15 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 14].Width = 25 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 15].Width = 25 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 16].Width = 25 * 256;
}
else
{
// Camper Detail Report
ws2.Columns[BEGIN_COLUMN_INDEX].Width = 15 * 256; // CampID
ws2.Columns[BEGIN_COLUMN_INDEX + 1].Width = 30 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 2].Width = 25 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 3].Width = 25 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 4].Width = 30 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 5].Width = 18 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 6].Width = 30 * 256; // Camp Name
ws2.Columns[BEGIN_COLUMN_INDEX + 9].Width = 22 * 256; // Session Datea
ws2.Columns[BEGIN_COLUMN_INDEX + 10].Width = 18 * 256; // Timer
ws2.Columns[BEGIN_COLUMN_INDEX + 12].Width = 25 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 13].Width = 25 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 14].Width = 25 * 256;
}
}
else
{
// since there is no whole list report, we need to delete sheet1
excel.Worksheets[0].Delete();
}
excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
// Save to a file on the local file system
string filename = String.Format("\\{0}{1}{2}{3} {4}.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond, fileName);
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
//Display excel spreadsheet
this.Response.Clear();
this.Response.Buffer = true;
this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.Charset = "";
if (newFile.Length == 0)
{
this.Response.Write("Error encountered - no spreadsheet to display");
}
else
{
this.Response.WriteFile(newFile);
}
Response.End();
}
private void GenerateExcelReport()
{
string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
string workFileDir = Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile excel = new ExcelFile();
excel.LoadXls(templateFile);
ExcelWorksheet ws = excel.Worksheets["Sheet1"];
//We start at first row, because for ExcelLite control, the header row is not included
const int BEGIN_COLUMN_INDEX = 1;
const int REPORT_HEADER_CELL_NUMBER = 6;
const int REPORT_SUB_HEADER_CELL_NUMBER = 4;
int iRow = 1;
// Global artistic setting
ws.Columns[0].Width = 20 * 20; // make the first column smaller
// Create Report Header
CellStyle styleReportHeader = new CellStyle();
styleReportHeader.Font.Color = System.Drawing.Color.Blue;
styleReportHeader.Font.Size = 22 * 20;
styleReportHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
ReportHeader.Merged = true;
ReportHeader.Style = styleReportHeader;
ReportHeader.Value = "Parent Country of Origin (Online Data Only)";
ws.Rows[iRow].Height = 25 * 20;
iRow += 1;
// Create Report SubHeader - usually it's camp year and report generation time
CellStyle styleReportSubHeader = new CellStyle();
styleReportSubHeader.Font.Size = 16 * 20;
styleReportSubHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
SubHeader.Merged = true;
SubHeader.Style = styleReportSubHeader;
SubHeader.Value = string.Format("Camp Year: {0}. Generated on {1} {2}", ddlCampYear.SelectedItem.Text, DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString());
iRow += 2;
CellStyle styleReportSubHeaderNote = new CellStyle();
//styleReportSubHeader.Font.Size = 16 * 20;
//styleReportSubHeader.Font.Weight = ExcelFont.BoldWeight;
//styleReportSubHeaderNote.
CellRange SubHeaderNote = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, 11);
SubHeaderNote.Merged = true;
//SubHeaderNote.Style = styleReportSubHeaderNote;
SubHeaderNote.Value = "This report offers two different pieces of data. “Number Of Campers” who have at least one parent from the country(s) selected and the list of countries with the number of corresponding parents. Note: the";
//ws.Rows[iRow].Height = 25 * 20;
iRow += 1;
CellRange SubHeaderNote2 = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, 11);
SubHeaderNote2.Merged = true;
//SubHeaderNote.Style = styleReportSubHeaderNote;
SubHeaderNote2.Value = "number of campers will typically be less than the total number of parents in this report.";
//ws.Rows[iRow].Height = 25 * 20;
iRow += 2;
CellStyle styleTableTitle = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Center
};
styleTableTitle.Font.Size = 16 * 20;
styleTableTitle.FillPattern.SetSolid(Color.LightBlue);
styleTableTitle.Font.Weight = ExcelFont.BoldWeight;
CellStyle styleTableDataRow = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Center
};
CellStyle styleTableDataRowCampName = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Left
};
CellStyle styleTableHeaderColumns = new CellStyle();
styleTableHeaderColumns.Font.Weight = ExcelFont.BoldWeight;
styleTableHeaderColumns.HorizontalAlignment = HorizontalAlignmentStyle.Center;
styleTableHeaderColumns.FillPattern.SetSolid(Color.LightGray);
// Data Content of report
DataSet[] dsArray = GenerateDataSet();
//DataSet dsModified = dsArray[0];
//DataSet ds2 = dsArray[1];
int indexDataSet = 0; // serve to see which dataset we are running (either ByProgram or ByCamp
bool isRun = false;
string EntityName = "", ReportName = "";
foreach (DataSet dsModified in dsArray)
{
isRun = false;
if (chkByProgram.Checked && indexDataSet == 0)
{
EntityName = "Program";
ReportName = "By Program, By Parent Birth Country";
isRun = true;
}
if (chkByCamp.Checked && indexDataSet == 1)
{
EntityName = "CampName";
ReportName = "By Camp, By Parent Birth Country";
isRun = true;
}
if (isRun)
{
// Get rid of first columns for other tables except first one
for (int i = 1; i < dsModified.Tables.Count; i++)
{
dsModified.Tables[i].Columns.Remove(EntityName);
}
int table_column_count = dsModified.Tables[0].Columns.Count;
int times = 0, current_starting_column = 0;
// Table Title
CellStyle styleSubReportTitle = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Center
};
styleSubReportTitle.Font.Size = 16 * 20;
styleSubReportTitle.FillPattern.SetSolid(Color.Transparent);
styleSubReportTitle.Font.Weight = ExcelFont.BoldWeight;
CellRange SubReportTitle = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
SubReportTitle.Merged = true;
SubReportTitle.Value = ReportName;
SubReportTitle.Style = styleSubReportTitle;
iRow += 3;
foreach (DataTable dt in dsModified.Tables)
{
if (times == 1)
{
// Get the header location
current_starting_column = BEGIN_COLUMN_INDEX + 1 + times * table_column_count;
}
else
{
// Get the header location
current_starting_column = BEGIN_COLUMN_INDEX + times * table_column_count;
}
int temp_column = 0;
if (times == 0)
{
temp_column = current_starting_column + 1;
}
else
{
temp_column = current_starting_column;
}
// Table Title
CellRange TableTitle = ws.Cells.GetSubrangeAbsolute(iRow - 1, temp_column, iRow - 1, current_starting_column + dt.Columns.Count - 1);
TableTitle.Merged = true;
TableTitle.Value = dt.TableName;
TableTitle.Style = styleTableTitle;
// this creats the real table
ws.InsertDataTable(dt, iRow, current_starting_column, true);
// loop through each column and set style accordingly
for (int i = current_starting_column; i <= (dt.Columns.Count + current_starting_column - 1); i++)
{
ws.Cells[iRow, i].Style = styleTableHeaderColumns;
ws.Cells[iRow + dt.Rows.Count, i].Style = styleTableHeaderColumns;
ws.Columns[i].Width = 11 * 256;
// first column of first table, e.g. camp naem
if (times == 0)
{
if (i == current_starting_column)
{
ws.Columns[i].Width = 55 * 256; // camp/program name
}
else if (i == current_starting_column + 1)
{
ws.Columns[i].Width = 15 * 256;
}
}
else if (i == current_starting_column)
{
ws.Columns[i].Width = 15 * 256;
}
}
// Set the data row style
for (int j = iRow + 1; j < iRow + dt.Rows.Count; j++)
{
ws.Rows[j].Style = styleTableDataRow;
}
// left justify the camp names
if (times == 0)
{
for (int j = iRow + 1; j < iRow + dt.Rows.Count; j++)
{
ws.Cells[j, current_starting_column].Style = styleTableDataRowCampName;
}
}
times++;
}
iRow += dsModified.Tables[0].Rows.Count + 5;
}
indexDataSet += 1;
}
excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
// Save to a file on the local file system
string filename = String.Format("\\{0}{1}{2}{3}ParentCountryOfOrigin.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
//Display excel spreadsheet
this.Response.Clear();
this.Response.Buffer = true;
this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.Charset = "";
if (newFile.Length == 0)
{
this.Response.Write("Error encountered - no spreadsheet to display");
}
else
{
this.Response.WriteFile(newFile);
}
Response.End();
}
public static string GenerateExcelReport(DataTable dt)
{
string templateFile = HttpContext.Current.Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
string workFileDir = HttpContext.Current.Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile excel = new ExcelFile();
excel.LoadXls(templateFile);
ExcelWorksheet ws = excel.Worksheets["Sheet1"];
// The first row is decoration headers
ws.Rows[1].Height = 2 * 256;
CellStyle cs = new CellStyle();
cs.FillPattern.SetSolid(Color.FromArgb(204, 153, 255));
cs.Font.Weight = ExcelFont.BoldWeight;
cs.WrapText = true;
cs.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr = ws.Cells.GetSubrangeAbsolute(0, 0, 0, 10);
cr.Merged = true;
cr.Value = "Program Information";
cr.Style = cs;
CellStyle cs2 = new CellStyle();
cs2.FillPattern.SetSolid(Color.FromArgb(102, 255, 255));
cs2.Font.Weight = ExcelFont.BoldWeight;
cs2.WrapText = true;
cs2.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr2 = ws.Cells.GetSubrangeAbsolute(0, 11, 0, 15);
cr2.Merged = true;
cr2.Value = "Camp Information";
cr2.Style = cs2;
CellStyle cs3 = new CellStyle();
cs3.FillPattern.SetSolid(Color.FromArgb(255, 153, 204));
cs3.Font.Weight = ExcelFont.BoldWeight;
cs3.WrapText = true;
cs3.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr3 = ws.Cells.GetSubrangeAbsolute(0, 16, 0, 23);
cr3.Merged = true;
cr3.Value = "Basic Camper Information";
cr3.Style = cs3;
CellStyle cs4 = new CellStyle();
cs4.FillPattern.SetSolid(Color.FromArgb(204, 255, 204));
cs4.Font.Weight = ExcelFont.BoldWeight;
cs4.WrapText = true;
cs4.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr4 = ws.Cells.GetSubrangeAbsolute(0, 24, 0, 28);
cr4.Merged = true;
cr4.Value = "Camper Contact Information";
cr4.Style = cs4;
CellStyle cs5 = new CellStyle();
cs5.FillPattern.SetSolid(Color.FromArgb(255, 255, 153));
cs5.Font.Weight = ExcelFont.BoldWeight;
cs5.WrapText = true;
cs5.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr5 = ws.Cells.GetSubrangeAbsolute(0, 29, 0, 42);
cr5.Merged = true;
cr5.Value = "Parent Contact Information";
cr5.Style = cs5;
CellStyle cs6 = new CellStyle();
cs6.FillPattern.SetSolid(Color.FromArgb(150, 150, 150));
cs6.Font.Weight = ExcelFont.BoldWeight;
cs6.WrapText = true;
cs6.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr6 = ws.Cells.GetSubrangeAbsolute(0, 43, 0, 45);
cr6.Merged = true;
cr6.Value = "Application Information";
cr6.Style = cs6;
CellStyle cs7 = new CellStyle();
cs7.FillPattern.SetSolid(Color.FromArgb(255, 204, 153));
cs7.Font.Weight = ExcelFont.BoldWeight;
cs7.WrapText = true;
cs7.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr7 = ws.Cells.GetSubrangeAbsolute(0, 46, 0, 52);
cr7.Merged = true;
cr7.Value = "Marketing Source";
cr7.Style = cs7;
CellStyle cs8 = new CellStyle();
cs8.FillPattern.SetSolid(Color.FromArgb(153, 204, 255));
cs8.Font.Weight = ExcelFont.BoldWeight;
cs8.WrapText = true;
cs8.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr8 = ws.Cells.GetSubrangeAbsolute(0, 53, 0, 68);
cr8.Merged = true;
cr8.Value = "Demographic Information";
cr8.Style = cs8;
// this creats the real table
ws.InsertDataTable(dt, 1, 0, true);
CellStyle styleTableHeaderColumns = new CellStyle();
styleTableHeaderColumns.Font.Weight = ExcelFont.BoldWeight;
styleTableHeaderColumns.HorizontalAlignment = HorizontalAlignmentStyle.Center;
styleTableHeaderColumns.VerticalAlignment = VerticalAlignmentStyle.Center;
styleTableHeaderColumns.FillPattern.SetSolid(Color.LightGray);
styleTableHeaderColumns.WrapText = true;
ws.Columns[0].Width = 15 * 256;
ws.Columns[1].Width = 35 * 256;
ws.Columns[2].Width = 30 * 256;
ws.Columns[11].Width = 35 * 256; // Camp Name
ws.Columns[12].Width = 27 * 256;
ws.Columns[16].Width = 30 * 256; // Last Name
ws.Columns[17].Width = 30 * 256;
ws.Rows[1].Style = styleTableHeaderColumns;
ws.Rows[1].Height = 3 * 256;
CellStyle styleContent = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Left, VerticalAlignment = VerticalAlignmentStyle.Center
};
for (int i = 2; i < dt.Rows.Count; i++)
{
ws.Rows[i].Style = styleContent;
}
styleContent.WrapText = true;
excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
// Save to a file on the local file system
string filename = String.Format("\\{0}{1}{2}{3}DataExtract.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
return(strFileParts[strFileParts.Length - 1]);
}
private void GenerateExcelReport()
{
// Data Content of report
DateTime cutoffDate = GetCutoffDate();
List <int> yearList = GetYearList();
DataSet dsNormalized = GenerateDataTables(cutoffDate);
string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
string workFileDir = Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile excel = new ExcelFile();
excel.LoadXls(templateFile);
ExcelWorksheet ws = excel.Worksheets["Sheet1"];
//We start at first row, because for ExcelLite control, the header row is not included
int BEGIN_COLUMN_INDEX = 0;
const int CAMP_NAME_MERGED_CELL_NUMBER = 6;
const int REPORT_HEADER_CELL_NUMBER = 6;
const int REPORT_SUB_HEADER_CELL_NUMBER = 4;
int iRow = 1;
// Global artistic setting
// first column
ws.Columns[0].Width = 40 * 250;
// Create Report Header
CellStyle styleReportHeader = new CellStyle();
styleReportHeader.Font.Color = Color.Blue;
styleReportHeader.Font.Size = 22 * 20;
styleReportHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
ReportHeader.Merged = true;
ReportHeader.Style = styleReportHeader;
ReportHeader.Value = "Summary by Program";
iRow += 1;
// Create Report SubHeader - usually it's camp year
CellStyle styleSubHeader = new CellStyle();
styleSubHeader.Font.Size = 16 * 20;
styleSubHeader.Font.Weight = ExcelFont.BoldWeight;
styleSubHeader.Font.Color = Color.Red;
CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
SubHeader.Merged = true;
SubHeader.Value = cutoffDate.ToString(" MMM d, yyyy");
SubHeader.Style = styleSubHeader;
iRow += 2;
CellStyle cs = new CellStyle();
cs.Font.Size = 18 * 20;
cs.Font.Weight = ExcelFont.BoldWeight;
CellRange cr = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);
cr.Merged = true;
cr.Value = "";
cr.Style = cs;
ws.Rows[iRow].Height = 25 * 20;
iRow += 3;
int iColumn = 0;
int sameYearCount = 0;
if (chkAllTimers.Checked)
{
sameYearCount += 1;
}
if (chk1stTimers.Checked)
{
sameYearCount += 1;
}
if (chk2ndTimers.Checked)
{
sameYearCount += 1;
}
if (chk3rdTimers.Checked)
{
sameYearCount += 1;
}
int sameYearOffset = 1;
bool tempFlag = true;
foreach (DataTable dt in dsNormalized.Tables)
{
CellStyle styleTableHeader = new CellStyle();
styleTableHeader.Font.Size = 16 * 16;
styleTableHeader.Font.Weight = ExcelFont.BoldWeight;
string originalName = dt.TableName;
int year = ChangeTableNameForExcelReport(dt);
// only the first table shows the program name, the rest on the same row will have no first column
int currentColumn = iColumn;
if (sameYearOffset != 1)
{
dt.Columns.Remove("Name");
}
else
{
currentColumn = iColumn + 1;
// This is special code for if select only current year, then the tables layout are vertical, and just need to show year at once
if (yearList.Count > 0 || tempFlag)
{
tempFlag = false;
CellRange TableHeaderYear = ws.Cells.GetSubrangeAbsolute(iRow - 2, currentColumn, iRow - 2, currentColumn);
TableHeaderYear.Value = year.ToString();
styleTableHeader.Font.Color = Color.Black;
TableHeaderYear.Style = styleTableHeader;
}
}
styleTableHeader.Font.Color = Color.Red;
CellRange TableHeader = ws.Cells.GetSubrangeAbsolute(iRow - 1, currentColumn, iRow - 1, currentColumn);
TableHeader.Style = styleTableHeader;
TableHeader.Value = dt.TableName;
dt.TableName = originalName;
// this creats the real table
ws.InsertDataTable(dt, iRow, iColumn, true);
CellStyle tableHeaderStyle = new CellStyle();
tableHeaderStyle.Font.Weight = ExcelFont.BoldWeight;
tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);
CellStyle tableHeaderStyle2 = new CellStyle();
tableHeaderStyle2.Font.Weight = ExcelFont.BoldWeight;
tableHeaderStyle2.FillPattern.SetSolid(Color.Orange);
//Bold Style
CellStyle boldStyle = new CellStyle();
boldStyle.Font.Weight = ExcelFont.BoldWeight;
// adjust each column's width and header style
for (int i = iColumn; i <= (dt.Columns.Count + iColumn - 1); i++)
{
// make sure total row is bold
ws.Cells[iRow + dt.Rows.Count, i].Style = boldStyle;
// make column wide enough to see the column header
if (iColumn == 0)
{
// first table has program name, so has different i offset
if (i == 0 + iColumn)
{
ws.Columns[i].Width = 35 * 256;
}
else if (i == 1 + iColumn)
{
ws.Columns[i].Width = 10 * 256;
}
else if (i == 2 + iColumn)
{
ws.Columns[i].Width = 30 * 256;
}
else if (i == 4 + iColumn)
{
ws.Columns[i].Width = 22 * 256;
}
else
{
ws.Columns[i].Width = 17 * 256;
}
if (i == iColumn + 1)
{
// but Seth wants total column to be color of pink
ws.Cells[iRow, i].Style = tableHeaderStyle2;
}
else
{
// make sure header row has bgcolor and is bold
ws.Cells[iRow, i].Style = tableHeaderStyle;
}
}
else
{
if (i == iColumn)
{
ws.Columns[i].Width = 10 * 256;
}
else if (i == 1 + iColumn)
{
ws.Columns[i].Width = 30 * 256;
}
else if (i == 3 + iColumn)
{
ws.Columns[i].Width = 22 * 256;
}
else
{
ws.Columns[i].Width = 17 * 256;
}
if (iColumn == i)
{
// but Seth wants total column to be color of pink
ws.Cells[iRow, i].Style = tableHeaderStyle2;
}
else
{
// make sure header row has bgcolor and is bold
ws.Cells[iRow, i].Style = tableHeaderStyle;
}
}
}
// Make the same year tables on the same row
if (sameYearOffset < sameYearCount && yearList.Count > 0)
{
iColumn += dt.Columns.Count + 1;
sameYearOffset += 1;
}
else
{
iRow += dt.Rows.Count + 4;
iColumn = 0;
sameYearOffset = 1;
}
}
excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
// Save to a file on the local file system
string filename = String.Format("\\{0}{1}{2}{3}CamperCountByProgramAndStatus.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
//Display excel spreadsheet
Response.Clear();
Response.Buffer = true;
Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
if (newFile.Length == 0)
{
Response.Write("Error encountered - no spreadsheet to display");
}
else
{
Response.WriteFile(newFile);
}
Response.End();
}
public GemBoxExcelLiteManager()
{
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
}
private string MakeReport(DateTime aggStartDate, DateTime repDate, bool makeAggData, string xlsReportName)
{
//if the month is the aggStart use the blank template. Otherwise, use last months
string xlsBaseFileName;
if (aggStartDate == repDate)
{
xlsBaseFileName = "NNIN Data Blank.xls";
}
else
{
xlsBaseFileName = string.Format("AS{0:yyyyMM}_Report{1:yyyyMM}_{2}.xls", aggStartDate, repDate.AddMonths(-1), litCurrentThreshold.Text);
}
string xlsBaseFilePath = XlsFilePath + xlsBaseFileName;
string xlsReportPath = XlsFilePath + xlsReportName;
DateTime period = repDate;
DateTime sDate = period;
DateTime eDate = sDate.AddMonths(1);
DataSet ds = NNINDA.GetTablesWithMinimumMinutes(period, (int)ClientPrivilege.LabUser, makeAggData, Convert.ToDouble(litCurrentThreshold.Text) / 60);
ds.Tables[0].TableName = "TechnicalInterest_Hours";
ds.Tables[1].TableName = "OrgType_Hours";
ds.Tables[2].TableName = "TechnicalInterest_Users";
ds.Tables[3].TableName = "OrgType_Users";
ds.Tables[4].TableName = "TechnicalInterest_Fees";
ds.Tables[5].TableName = "OrgType_Fees";
DataColumn[] pk;
pk = new DataColumn[] { ds.Tables["TechnicalInterest_Fees"].Columns["TechnicalInterestID"] };
ds.Tables["TechnicalInterest_Fees"].PrimaryKey = pk;
pk = new DataColumn[] { ds.Tables["OrgType_Fees"].Columns["OrgTypeID"] };
ds.Tables["OrgType_Fees"].PrimaryKey = pk;
//get raw cumulative user data
DataTable dtCumUser = NNINDA.GetCumulativeUserAggregateData(aggStartDate, repDate);
DataTable dtReport = new DataTable();
dtReport.Columns.Add("BaseRow", typeof(int));
dtReport.Columns.Add("TotalRow", typeof(int));
dtReport.Columns.Add("RepType", typeof(string)); //fees, hours, cum,
dtReport.Columns.Add("ByType", typeof(string)); //orgtype, techid, none
dtReport.Columns.Add("DateOnly", typeof(bool)); //orgtype, techid, none
AddReportRow(dtReport, 10, 0, string.Empty, string.Empty, true);
AddReportRow(dtReport, 24, 43, "Hours", "TechnicalInterest", false);
AddReportRow(dtReport, 11, 43, "Hours", "OrgType", false);
AddReportRow(dtReport, 40, 0, string.Empty, string.Empty, true);
AddReportRow(dtReport, 69, 88, "Users", "TechnicalInterest", false);
AddReportRow(dtReport, 55, 88, "Users", "OrgType", false);
AddReportRow(dtReport, 85, 0, string.Empty, string.Empty, true);
AddReportRow(dtReport, 161, 180, "Fees", "TechnicalInterest", false);
AddReportRow(dtReport, 146, 180, "Fees", "OrgType", false);
AddReportRow(dtReport, 177, 0, string.Empty, string.Empty, true);
AddReportRow(dtReport, 116, 136, "CumUser", "TechnicalInterest", false);
AddReportRow(dtReport, 101, 136, "CumUser", "OrgType", false);
AddReportRow(dtReport, 133, 0, string.Empty, string.Empty, true);
AddReportRow(dtReport, 226, 0, "CumUser", "DemGender", false);
AddReportRow(dtReport, 232, 0, "CumUser", "DemEthnic", false);
AddReportRow(dtReport, 238, 0, "CumUser", "DemRace", false);
AddReportRow(dtReport, 247, 0, "CumUser", "DemDisability", false);
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile SpreadSheet = new ExcelFile();
SpreadSheet.LoadXls(xlsBaseFilePath);
ExcelWorksheet ws = SpreadSheet.Worksheets["UM SSEL"];
DataRow[] fdr;
int lastVal, useCol = 0, useRow;
double hardTotals; //for recording internal time without formula
foreach (DataRow drReport in dtReport.Rows)
{
//write dates
for (int j = 0; j < 12; j++)
{
ws.Cells[Convert.ToInt32(drReport["BaseRow"]), j + 1].Value = aggStartDate.AddMonths(j);
if (aggStartDate.AddMonths(j) == repDate)
{
useCol = j + 1;
}
}
if (!Convert.ToBoolean(drReport["DateOnly"]))
{
hardTotals = 0;
if (drReport["RepType"].ToString() == "CumUser")
{
fdr = dtCumUser.Select(string.Format("TableName = '{0}'", drReport["ByType"]));
for (int j = 0; j < fdr.Length; j++)
{
useRow = drReport.Field <int>("BaseRow") + fdr[j].Field <int>("Value");
if (aggStartDate == repDate)
{
ws.Cells[useRow, useCol].Value = fdr[j]["Count"];
}
else
{
lastVal = Convert.ToInt32(ws.Cells[useRow, 1].Value);
for (int i = 2; i < useCol; i++)
{
if (!string.IsNullOrEmpty(ws.Cells[useRow, i].Formula))
{
string form = ws.Cells[useRow, i].Formula;
lastVal += Convert.ToInt32(form.Substring(form.IndexOf("+") + 1));
}
}
string formula = "=" + Convert.ToChar(64 + useCol) + (useRow + 1).ToString() + "+" + (Convert.ToInt32(fdr[j]["Count"]) - lastVal).ToString();
ws.Cells[useRow, useCol].Formula = formula;
}
hardTotals += Convert.ToDouble(fdr[j]["Count"]);
}
if (Convert.ToInt32(drReport["TotalRow"]) != 0)
{
ws.Cells[Convert.ToInt32(drReport["TotalRow"]), useCol].Value = hardTotals;
}
}
else
{
foreach (DataRow dr in ds.Tables[string.Format("{0}_{1}", drReport["ByType"], drReport["RepType"])].Rows)
{
useRow = drReport.Field <int>("BaseRow") + dr.Field <int>(drReport["ByType"].ToString() + "ID");
double val = Convert.ToDouble(dr[drReport["RepType"].ToString()]); //must use Convert.ToDouble here because dr[...] may be different types depending on the rep type (e.g. double or int)
ws.Cells[useRow, useCol].Value = val;
hardTotals += val;
}
ws.Cells[Convert.ToInt32(drReport["TotalRow"]), useCol].Value = hardTotals;
}
}
}
SpreadSheet.SaveXls(xlsReportPath);
SpreadSheet = null;
GC.Collect();
return(xlsReportPath);
}
private void GenerateExcelReport()
{
string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
string workFileDir = Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile excel = new ExcelFile();
excel.LoadXls(templateFile);
ExcelWorksheet ws = excel.Worksheets["Sheet1"];
//We start at first row, because for ExcelLite control, the header row is not included
const int BEGIN_COLUMN_INDEX = 1;
const int CAMP_NAME_MERGED_CELL_NUMBER = 6;
const int REPORT_HEADER_CELL_NUMBER = 6;
const int REPORT_SUB_HEADER_CELL_NUMBER = 4;
int iRow = 1;
// Global artistic setting
ws.Columns[0].Width = 20 * 20;
// Create Report Header
CellStyle styleReportHeader = new CellStyle();
styleReportHeader.Font.Color = System.Drawing.Color.Blue;
styleReportHeader.Font.Size = 22 * 20;
styleReportHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
ReportHeader.Merged = true;
ReportHeader.Style = styleReportHeader;
ReportHeader.Value = "Number of Campers By Camp and Status (Online Data Only)";
//
iRow += 1;
// Create Report SubHeader - usually it's camp year
CellStyle styleSubHeader = new CellStyle();
styleSubHeader.Font.Size = 16 * 20;
styleSubHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
SubHeader.Merged = true;
SubHeader.Value = "Camp Year: " + ddlCampYear.SelectedItem.Text;
SubHeader.Style = styleSubHeader;
iRow += 2;
// Data Content of report
DataTable dt = GenerateDataTable();
CellStyle cs = new CellStyle();
cs.Font.Size = 18 * 20;
cs.Font.Weight = ExcelFont.BoldWeight;
CellRange cr = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);
cr.Merged = true;
cr.Value = "";
cr.Style = cs;
ws.Rows[iRow].Height = 25 * 20;
iRow += 1;
ws.InsertDataTable(dt, iRow, BEGIN_COLUMN_INDEX, true);
CellStyle tableHeaderStyle = new CellStyle();
tableHeaderStyle.Font.Weight = ExcelFont.BoldWeight;
tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);
for (int i = BEGIN_COLUMN_INDEX; i <= dt.Columns.Count; i++)
{
ws.Cells[iRow, i].Style = tableHeaderStyle;
ws.Cells[iRow + dt.Rows.Count, i].Style = tableHeaderStyle;
if (i == 1)
{
ws.Columns[i].Width = 30 * 256;
}
else
{
ws.Columns[i].Width = 20 * 256;
}
}
excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
// Save to a file on the local file system
string filename = "\\" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Millisecond.ToString() + "CamperCountByCampAndStatus.xls";
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
//Display excel spreadsheet
this.Response.Clear();
this.Response.Buffer = true;
this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.Charset = "";
if (newFile.Length == 0)
{
this.Response.Write("Error encountered - no spreadsheet to display");
}
else
{
this.Response.WriteFile(newFile);
}
Response.End();
}
private void GenerateExcelReport()
{
string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
string workFileDir = Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile excel = new ExcelFile();
excel.LoadXls(templateFile);
ExcelWorksheet ws = excel.Worksheets["Sheet1"];
//We start at first row, because for ExcelLite control, the header row is not included
int BEGIN_COLUMN_INDEX = 1;
const int REPORT_HEADER_CELL_NUMBER = 6;
const int REPORT_SUB_HEADER_CELL_NUMBER = 4;
int iRow = 1;
// Global artistic setting
ws.Columns[0].Width = 20 * 20; // make the first column smaller
// Create Report Header
CellStyle styleReportHeader = new CellStyle();
styleReportHeader.Font.Color = System.Drawing.Color.Blue;
styleReportHeader.Font.Size = 22 * 20;
styleReportHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
ReportHeader.Merged = true;
ReportHeader.Style = styleReportHeader;
ReportHeader.Value = "Camper By State By Camp";
ws.Rows[iRow].Height = 25 * 20;
iRow += 1;
// Create Report SubHeader - usually it's camp year and report generation time
CellStyle styleReportSubHeader = new CellStyle();
styleReportSubHeader.Font.Size = 16 * 20;
styleReportSubHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
SubHeader.Merged = true;
SubHeader.Style = styleReportSubHeader;
SubHeader.Value = string.Format("Camp Year: {0}. Generated on {1} {2}", ddlCampYear.SelectedItem.Text, DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString());
iRow += 4;
CellStyle styleTableTitle = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Center
};
styleTableTitle.Font.Size = 16 * 20;
styleTableTitle.FillPattern.SetSolid(Color.LightBlue);
styleTableTitle.Font.Weight = ExcelFont.BoldWeight;
CellStyle styleTableDataRow = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Center
};
CellStyle styleTableDataRowCampName = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Left
};
CellStyle styleTableHeaderColumns = new CellStyle();
styleTableHeaderColumns.Font.Weight = ExcelFont.BoldWeight;
styleTableHeaderColumns.HorizontalAlignment = HorizontalAlignmentStyle.Center;
styleTableHeaderColumns.FillPattern.SetSolid(Color.LightGray);
// Data Content of report
DataSet dsModified = GenerateDataSet();
for (int i = 1; i < dsModified.Tables.Count; i++)
{
dsModified.Tables[i].Columns.Remove("CampName");
}
int current_starting_column = 0, times = 0;
int last_table_column_count = 0, last_column_index = 0;
foreach (DataTable dt in dsModified.Tables)
{
// Get the header location
if (times == 0)
{
current_starting_column = BEGIN_COLUMN_INDEX;
}
else
{
current_starting_column = last_column_index + last_table_column_count;
}
// we need to know the last table column count to calculate the current starting column index
last_table_column_count = dt.Columns.Count;
last_column_index = current_starting_column + 1;
int temp_column = 0;
if (times == 0)
{
temp_column = current_starting_column + 1;
}
else
{
temp_column = current_starting_column;
}
CellRange TableTitle = ws.Cells.GetSubrangeAbsolute(iRow - 1, temp_column, iRow - 1, current_starting_column + dt.Columns.Count - 1);
TableTitle.Value = dt.TableName;
TableTitle.Merged = true;
TableTitle.Style = styleTableTitle;
// this creats the real table
ws.InsertDataTable(dt, iRow, current_starting_column, true);
for (int i = current_starting_column; i <= (dt.Columns.Count + current_starting_column - 1); i++)
{
ws.Cells[iRow, i].Style = styleTableHeaderColumns;
ws.Cells[iRow + dt.Rows.Count, i].Style = styleTableHeaderColumns;
ws.Columns[i].Width = 8 * 256;
// first column of first table, e.g. camp naem
if (times == 0)
{
if (i == current_starting_column)
{
ws.Columns[i].Width = 55 * 256; // camp/program name
}
}
}
// Set the data row style
for (int j = iRow + 1; j < iRow + dt.Rows.Count; j++)
{
ws.Rows[j].Style = styleTableDataRow;
}
// left justify the camp names
if (times == 0)
{
for (int j = iRow + 1; j < iRow + dt.Rows.Count; j++)
{
ws.Cells[j, current_starting_column].Style = styleTableDataRowCampName;
}
}
times++;
}
excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
// Save to a file on the local file system
string filename = String.Format("\\{0}{1}{2}{3}CamperReportByState.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
//Display excel spreadsheet
this.Response.Clear();
this.Response.Buffer = true;
this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.Charset = "";
if (newFile.Length == 0)
{
this.Response.Write("Error encountered - no spreadsheet to display");
}
else
{
this.Response.WriteFile(newFile);
}
Response.End();
}
private void GenerateExcelReport()
{
string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
string workFileDir = Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile excel = new ExcelFile();
excel.LoadXls(templateFile);
ExcelWorksheet ws = excel.Worksheets["Sheet1"];
//We start at first row, because for ExcelLite control, the header row is not included
int BEGIN_COLUMN_INDEX = 1;
const int CAMP_NAME_MERGED_CELL_NUMBER = 6;
const int REPORT_HEADER_CELL_NUMBER = 6;
const int REPORT_SUB_HEADER_CELL_NUMBER = 4;
int iRow = 1;
// Global artistic setting
ws.Columns[0].Width = 20 * 20;
// Create Report Header
CellStyle styleReportHeader = new CellStyle();
styleReportHeader.Font.Color = System.Drawing.Color.Blue;
styleReportHeader.Font.Size = 22 * 20;
styleReportHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
ReportHeader.Merged = true;
ReportHeader.Style = styleReportHeader;
ReportHeader.Value = "Number of Campers By Program, Status and Summer";
iRow += 1;
// Create Report SubHeader - usually it's camp year
CellStyle styleSubHeader = new CellStyle();
styleSubHeader.Font.Size = 16 * 20;
styleSubHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
SubHeader.Merged = true;
SubHeader.Value = string.Format("Camp Year: {0}. Generated on {1} {2}", ddlCampYear.SelectedItem.Text, DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString());
SubHeader.Style = styleSubHeader;
iRow += 2;
// 2010-11-26 no longer needed
//AddSubTotalRowsTable(dsNormalized);
CellStyle cs = new CellStyle();
cs.Font.Size = 18 * 20;
cs.Font.Weight = ExcelFont.BoldWeight;
CellRange cr = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);
cr.Merged = true;
cr.Value = "";
cr.Style = cs;
ws.Rows[iRow].Height = 25 * 20;
iRow += 2;
// Data Content of report
DataSet ds = GenerateDataTable();
DataSet dsNormalized = NormalizeTables(ds);
for (int i = 1; i < ds.Tables.Count; i++)
{
dsNormalized.Tables[i].Columns.Remove("Name");
}
int times = 0;
int table_column_count = ds.Tables[0].Columns.Count;
int current_column = 0;
foreach (DataTable dt in dsNormalized.Tables)
{
if (times == 1)
{
BEGIN_COLUMN_INDEX += 1;
}
// Get the header location
current_column = BEGIN_COLUMN_INDEX + times * table_column_count;
CellRange TableHeader = ws.Cells.GetSubrangeAbsolute(iRow - 1, current_column, iRow - 1, current_column);
TableHeader.Value = dt.TableName;
TableHeader.Style = styleSubHeader;
// this creats the real table
ws.InsertDataTable(dt, iRow, current_column, true);
CellStyle tableHeaderStyle = new CellStyle();
tableHeaderStyle.Font.Weight = ExcelFont.BoldWeight;
tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);
for (int i = current_column; i <= (dt.Columns.Count + current_column - 1); i++)
{
ws.Cells[iRow, i].Style = tableHeaderStyle;
ws.Cells[iRow + dt.Rows.Count, i].Style = tableHeaderStyle;
if (i == current_column)
{
// first column of every table
if (times >= 1)
{
ws.Columns[i].Width = 20 * 256;
}
else
{
ws.Columns[i].Width = 35 * 256;
}
}
else
{
ws.Columns[i].Width = 20 * 256; // non-first column
}
}
//if (times == 1)
// BEGIN_COLUMN_INDEX -= 1;
times++;
}
excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
// Save to a file on the local file system
string filename = String.Format("\\{0}{1}{2}{3}CamperCountByProgramAndStatus.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
//Display excel spreadsheet
this.Response.Clear();
this.Response.Buffer = true;
this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.Charset = "";
if (newFile.Length == 0)
{
this.Response.Write("Error encountered - no spreadsheet to display");
}
else
{
this.Response.WriteFile(newFile);
}
Response.End();
}
private void GenerateExcelReport(DataTable dtAllCamps, IList <int> campIdList)
{
var templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReportPrintingFormatted.xls");
var workFileDir = Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
var excel = new ExcelFile();
excel.LoadXls(templateFile);
var summaryTable = new DataTable();
summaryTable.Columns.Add("CampName", typeof(string));
summaryTable.Columns.Add("1st/2nd/3rd Time", typeof(int));
summaryTable.Columns.Add("Day School", typeof(int));
summaryTable.Columns.Add("Total Dollars", typeof(double));
foreach (var campId in campIdList)
{
IEnumerable <DataRow> enumByCamp = from camperApp in dtAllCamps.AsEnumerable()
where camperApp.Field <int>("CampID") == campId
select camperApp;
if (enumByCamp.Any())
{
var sum = enumByCamp.Sum(x => x.Field <double>("GrantAmount"));
var countDS = (from camperApp in enumByCamp
where camperApp.Field <string>("Day School") == "Y"
select camperApp).Count();
var countAll = enumByCamp.Count();
DataTable dt = enumByCamp.CopyToDataTable();
var campName = dt.Rows[0]["CampName"].ToString();
var dr = summaryTable.NewRow();
dr["CampName"] = campName;
dr["1st/2nd/3rd Time"] = countAll;
dr["Day School"] = countDS;
dr["Total Dollars"] = sum;
summaryTable.Rows.Add(dr);
if (campName.Contains(":"))
{
campName = campName.Replace(":", "-");
}
//We start at first row, because for ExcelLite control, the header row is not included
const int BEGIN_COLUMN_INDEX = 0;
const int REPORT_HEADER_CELL_NUMBER = 5;
const int REPORT_SUB_HEADER_CELL_NUMBER = 5;
const int CAMP_NAME_MERGED_CELL_NUMBER = 5;
int iRow = 1;
var ws = excel.Worksheets.AddCopy(campName, excel.Worksheets["Sheet1"]);
// Global artistic setting
ws.Columns[0].Width = 20 * 20; // make the first column smaller
// Create Report Header
var styleReportHeader = new CellStyle
{
Font = { Color = Color.Blue, Size = 22 * 20, Weight = ExcelFont.BoldWeight }
};
CellRange reportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
reportHeader.Merged = true;
reportHeader.Style = styleReportHeader;
reportHeader.Value = "One Happy Camper Payment Report";
ws.Rows[iRow].Height = 25 * 20;
iRow += 1;
// Create Report SubHeader - usually it's camp year and report generation time
var styleReportSubHeader = new CellStyle {
Font = { Size = 16 * 20, Weight = ExcelFont.BoldWeight }
};
CellRange subHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow,
REPORT_SUB_HEADER_CELL_NUMBER);
subHeader.Merged = true;
subHeader.Style = styleReportSubHeader;
subHeader.Value = string.Format("Camp Year: {0} Generated on {1} {2}", ddlCampYear.SelectedItem.Text,
DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString());
// Create Federation Name row
iRow += 2;
var cs = new CellStyle {
Font = { Size = 18 * 20, Weight = ExcelFont.BoldWeight }
};
CellRange fedNameRange = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow,
CAMP_NAME_MERGED_CELL_NUMBER);
fedNameRange.Merged = true;
fedNameRange.Style = cs;
fedNameRange.Value = ddlFed.SelectedItem.Text;
// admin name
iRow += 1;
//var cs = new CellStyle { Font = { Size = 18 * 20, Weight = ExcelFont.BoldWeight } };
CellRange adminNameRange = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow,
CAMP_NAME_MERGED_CELL_NUMBER);
adminNameRange.Merged = true;
//fedNameRange.Style = cs;
adminNameRange.Value = String.Format("Report Generated by: {0} {1}", Session["FirstName"],
Session["LastName"]);
// report type name
iRow += 2;
var reportTypeStyle = new CellStyle {
Font = { Size = 10 * 20, Weight = ExcelFont.BoldWeight }
};
var reportTypeText = "";
if (isFinal)
{
reportTypeStyle.FillPattern.SetSolid(Color.ForestGreen);
reportTypeStyle.Font.Color = Color.White;
reportTypeText = "FINAL REPORT. ALL RECORDS HAVE BEEN UPDATED TO PAYMENT REQUESTED.";
}
else
{
reportTypeStyle.FillPattern.SetSolid(Color.Yellow);
reportTypeStyle.Font.Color = Color.Red;
reportTypeText = "IMPORTANT: This is a Preliminary Report. Once data confirmed, run a FINAL report.";
}
CellRange reportTypeNameRange = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);
reportTypeNameRange.Merged = true;
reportTypeNameRange.Style = reportTypeStyle;
reportTypeNameRange.Value = reportTypeText;
iRow += 1;
CellRange campNameRange = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);
campNameRange.Merged = true;
campNameRange.Style = cs;
campNameRange.Value = campName;
iRow += 1;
// this creats the real table
dt.Columns.Remove("CampID");
dt.Columns.Remove("CampName");
dt.Columns.Remove("StatusID");
ws.InsertDataTable(dt, iRow, BEGIN_COLUMN_INDEX, true);
// decorate the header of content table
// loop through each column and 1.set the width of each colum, 2. set the header style of each column
var tableHeaderStyle = new CellStyle {
Font = { Weight = ExcelFont.BoldWeight }
};
tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);
for (int i = BEGIN_COLUMN_INDEX; i < dt.Columns.Count; i++)
{
ws.Cells[iRow, i].Style = tableHeaderStyle;
if (i == 3)
{
ws.Columns[i].Width = 30 * 256;
}
else if (i == 4)
{
ws.Columns[i].Width = 8 * 256;
}
else if (i == 5)
{
ws.Columns[i].Width = 28 * 256;
}
else if (i == 7)
{
ws.Columns[i].Width = 16 * 256;
}
else
{
ws.Columns[i].Width = 18 * 256;
}
}
var totalGrantAmount = dt.AsEnumerable().Sum(x => x.Field <double>("GrantAmount"));
var totalCamperCount = dt.Rows.Count;
iRow += dt.Rows.Count + 2;
var boldTextStyle = new CellStyle {
Font = { Weight = ExcelFont.BoldWeight }
};
ws.Cells[iRow, BEGIN_COLUMN_INDEX + 7].Value = "Total Dollars";
ws.Cells[iRow, BEGIN_COLUMN_INDEX + 7].Style = boldTextStyle;
ws.Cells[iRow, BEGIN_COLUMN_INDEX + 8].Value = totalGrantAmount;
ws.Cells[iRow, BEGIN_COLUMN_INDEX + 8].Style = boldTextStyle;
iRow += 1;
ws.Cells[iRow, BEGIN_COLUMN_INDEX + 7].Value = "# of campers";
ws.Cells[iRow, BEGIN_COLUMN_INDEX + 7].Style = boldTextStyle;
ws.Cells[iRow, BEGIN_COLUMN_INDEX + 8].Value = totalCamperCount;
ws.Cells[iRow, BEGIN_COLUMN_INDEX + 8].Style = boldTextStyle;
}
}
if (dtAllCamps.Rows.Count > 0)
{
// ******** Summary worksheet ***************
IEnumerable <DataRow> enumSummary = from rows in summaryTable.AsEnumerable()
select rows;
var sumTimer = enumSummary.Sum(x => x.Field <int>("1st/2nd/3rd Time"));
var sumDS = enumSummary.Sum(x => x.Field <int>("Day School"));
var sumTotal = enumSummary.Sum(x => x.Field <double>("Total Dollars"));
var dr = summaryTable.NewRow();
dr["CampName"] = "TOTAL";
dr["1st/2nd/3rd Time"] = sumTimer;
dr["Day School"] = sumDS;
dr["Total Dollars"] = sumTotal;
summaryTable.Rows.Add(dr);
const int BEGIN_COLUMN_INDEX = 0;
const int REPORT_HEADER_CELL_NUMBER = 5;
const int REPORT_SUB_HEADER_CELL_NUMBER = 5;
const int CAMP_NAME_MERGED_CELL_NUMBER = 5;
int iRow = 1;
var ws = excel.Worksheets.AddCopy("Summary", excel.Worksheets["Sheet1"]);
// Global artistic setting
ws.Columns[0].Width = 20 * 20; // make the first column smaller
// Create Report Header
var styleReportHeader = new CellStyle
{
Font = { Color = Color.Blue, Size = 22 * 20, Weight = ExcelFont.BoldWeight }
};
CellRange reportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow,
REPORT_HEADER_CELL_NUMBER);
reportHeader.Merged = true;
reportHeader.Style = styleReportHeader;
reportHeader.Value = "One Happy Camper Payment Report";
ws.Rows[iRow].Height = 25 * 20;
iRow += 1;
// Create Report SubHeader - usually it's camp year and report generation time
var styleReportSubHeader = new CellStyle {
Font = { Size = 16 * 20, Weight = ExcelFont.BoldWeight }
};
CellRange subHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow,
REPORT_SUB_HEADER_CELL_NUMBER);
subHeader.Merged = true;
subHeader.Style = styleReportSubHeader;
subHeader.Value = string.Format("Camp Year: {0} Generated on {1} {2}", ddlCampYear.SelectedItem.Text,
DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString());
// Create Federation Name row
iRow += 2;
var cs = new CellStyle {
Font = { Size = 18 * 20, Weight = ExcelFont.BoldWeight }
};
CellRange fedNameRange = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow,
CAMP_NAME_MERGED_CELL_NUMBER);
fedNameRange.Merged = true;
fedNameRange.Style = cs;
fedNameRange.Value = ddlFed.SelectedItem.Text;
// admin name
iRow += 1;
//var cs = new CellStyle { Font = { Size = 18 * 20, Weight = ExcelFont.BoldWeight } };
CellRange adminNameRange = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow,
CAMP_NAME_MERGED_CELL_NUMBER);
adminNameRange.Merged = true;
//fedNameRange.Style = cs;
adminNameRange.Value = String.Format("Report Generated by: {0} {1}", Session["FirstName"],
Session["LastName"]);
// report type name
iRow += 2;
var reportTypeStyle = new CellStyle {
Font = { Size = 10 * 20, Weight = ExcelFont.BoldWeight }
};
var reportTypeText = "";
if (isFinal)
{
reportTypeStyle.FillPattern.SetSolid(Color.ForestGreen);
reportTypeStyle.Font.Color = Color.White;
reportTypeText = "FINAL REPORT. ALL RECORDS HAVE BEEN UPDATED TO PAYMENT REQUESTED.";
}
else
{
reportTypeStyle.FillPattern.SetSolid(Color.Yellow);
reportTypeStyle.Font.Color = Color.Red;
reportTypeText = "IMPORTANT: This is a Preliminary Report. Once data confirmed, run a FINAL report.";
}
CellRange reportTypeNameRange = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);
reportTypeNameRange.Merged = true;
reportTypeNameRange.Style = reportTypeStyle;
reportTypeNameRange.Value = reportTypeText;
iRow += 1;
// this creats the real table
ws.InsertDataTable(summaryTable, iRow, BEGIN_COLUMN_INDEX, true);
// decorate the header of content table
// loop through each column and 1.set the width of each colum, 2. set the header style of each column
var tableHeaderStyle = new CellStyle {
Font = { Weight = ExcelFont.BoldWeight }
};
tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);
var boldTextStyle = new CellStyle {
Font = { Weight = ExcelFont.BoldWeight }
};
for (int i = BEGIN_COLUMN_INDEX; i < summaryTable.Columns.Count; i++)
{
ws.Cells[iRow, i].Style = tableHeaderStyle;
ws.Cells[iRow + summaryTable.Rows.Count, i].Style = boldTextStyle;
if (i == BEGIN_COLUMN_INDEX)
{
ws.Columns[i].Width = 30 * 256;
}
else if (i == BEGIN_COLUMN_INDEX + 3)
{
ws.Columns[i].Width = 20 * 256;
}
else
{
ws.Columns[i].Width = 18 * 256;
}
}
// ******** End of Summary worksheet ********
excel.Worksheets[0].Delete();
excel.Worksheets[0].Delete();
excel.Worksheets[0].Delete();
excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
}
// Save to a file on the local file system
string filename = String.Format("\\{0}{1}{2}{3}-Payment.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
//Display excel spreadsheet
Response.Clear();
Response.Buffer = true;
Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
if (newFile.Length == 0)
{
Response.Write("Error encountered - no spreadsheet to display");
}
else
{
Response.WriteFile(newFile);
}
Response.End();
}
private void GenerateExcelReport()
{
string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
string workFileDir = Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile excel = new ExcelFile();
excel.LoadXls(templateFile);
ExcelWorksheet ws = excel.Worksheets["Sheet1"];
// Data Content of report
DataTable dt = GenerateDataSet();
// The first row is decoration headers
ws.Rows[1].Height = 2 * 256;
int columnPointer = 9;
CellStyle cs = new CellStyle();
cs.FillPattern.SetSolid(Color.FromArgb(204, 153, 255));
cs.Font.Weight = ExcelFont.BoldWeight;
cs.WrapText = true;
cs.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr = ws.Cells.GetSubrangeAbsolute(0, 0, 0, columnPointer);
cr.Merged = true;
cr.Value = "Program Information";
cr.Style = cs;
CellStyle cs2 = new CellStyle();
cs2.FillPattern.SetSolid(Color.FromArgb(102, 255, 255));
cs2.Font.Weight = ExcelFont.BoldWeight;
cs2.WrapText = true;
cs2.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr2 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 5);
cr2.Merged = true;
cr2.Value = "Camp Information";
cr2.Style = cs2;
CellStyle cs3 = new CellStyle();
cs3.FillPattern.SetSolid(Color.FromArgb(255, 153, 204));
cs3.Font.Weight = ExcelFont.BoldWeight;
cs3.WrapText = true;
cs3.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr3 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 8);
cr3.Merged = true;
cr3.Value = "Basic Camper Information";
cr3.Style = cs3;
CellStyle cs4 = new CellStyle();
cs4.FillPattern.SetSolid(Color.FromArgb(204, 255, 204));
cs4.Font.Weight = ExcelFont.BoldWeight;
cs4.WrapText = true;
cs4.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr4 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 5);
cr4.Merged = true;
cr4.Value = "Camper Contact Information";
cr4.Style = cs4;
CellStyle cs5 = new CellStyle();
cs5.FillPattern.SetSolid(Color.FromArgb(255, 255, 153));
cs5.Font.Weight = ExcelFont.BoldWeight;
cs5.WrapText = true;
cs5.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr5 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 14);
cr5.Merged = true;
cr5.Value = "Parent Contact Information";
cr5.Style = cs5;
CellStyle cs6 = new CellStyle();
cs6.FillPattern.SetSolid(Color.FromArgb(150, 150, 150));
cs6.Font.Weight = ExcelFont.BoldWeight;
cs6.WrapText = true;
cs6.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr6 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 3);
cr6.Merged = true;
cr6.Value = "Application Information";
cr6.Style = cs6;
CellStyle cs7 = new CellStyle();
cs7.FillPattern.SetSolid(Color.FromArgb(255, 204, 153));
cs7.Font.Weight = ExcelFont.BoldWeight;
cs7.WrapText = true;
cs7.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr7 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 8);
cr7.Merged = true;
cr7.Value = "Marketing Source";
cr7.Style = cs7;
CellStyle cs8 = new CellStyle();
cs8.FillPattern.SetSolid(Color.FromArgb(153, 204, 255));
cs8.Font.Weight = ExcelFont.BoldWeight;
cs8.WrapText = true;
cs8.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr8 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 16);
cr8.Merged = true;
cr8.Value = "Demographic Information";
cr8.Style = cs8;
// Toronto questions
CellStyle cs9 = new CellStyle();
cs9.FillPattern.SetSolid(Color.FromArgb(233, 19, 210));
cs9.Font.Weight = ExcelFont.BoldWeight;
cs9.WrapText = true;
cs9.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr9 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 2, 0, columnPointer += 7);
cr9.Merged = true;
cr9.Value = "FJC ONLY";
cr9.Style = cs9;
// Grandfather questions
var cs10 = new CellStyle();
cs10.FillPattern.SetSolid(Color.FromArgb(133, 19, 110));
cs10.Font.Weight = ExcelFont.BoldWeight;
cs10.WrapText = true;
cs10.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr10 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 4);
cr10.Merged = true;
cr10.Value = "Grandfather Policy";
cr10.Style = cs10;
// Chicago Siblings
var cs11 = new CellStyle();
cs11.FillPattern.SetSolid(Color.FromArgb(133, 119, 110));
cs11.Font.Weight = ExcelFont.BoldWeight;
cs11.WrapText = true;
cs11.HorizontalAlignment = HorizontalAlignmentStyle.Center;
CellRange cr11 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 2);
cr11.Merged = true;
cr11.Value = "Chicago Siblings";
cr11.Style = cs11;
// this creats the real table
ws.InsertDataTable(dt, 1, 0, true);
CellStyle styleTableHeaderColumns = new CellStyle();
styleTableHeaderColumns.Font.Weight = ExcelFont.BoldWeight;
styleTableHeaderColumns.HorizontalAlignment = HorizontalAlignmentStyle.Center;
styleTableHeaderColumns.VerticalAlignment = VerticalAlignmentStyle.Center;
styleTableHeaderColumns.FillPattern.SetSolid(Color.LightGray);
styleTableHeaderColumns.WrapText = true;
ws.Columns[0].Width = 15 * 256;
ws.Columns[1].Width = 35 * 256;
ws.Columns[2].Width = 30 * 256;
ws.Columns[11].Width = 35 * 256; // Camp Name
ws.Columns[12].Width = 27 * 256;
ws.Columns[16].Width = 30 * 256; // Last Name
ws.Columns[17].Width = 30 * 256;
ws.Rows[1].Style = styleTableHeaderColumns;
ws.Rows[1].Height = 3 * 256;
CellStyle styleContent = new CellStyle();
styleContent.HorizontalAlignment = HorizontalAlignmentStyle.Left;
styleContent.VerticalAlignment = VerticalAlignmentStyle.Center;
for (int i = 2; i < dt.Rows.Count; i++)
{
ws.Rows[i].Style = styleContent;
}
styleContent.WrapText = true;
excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
// Save to a file on the local file system
string filename = String.Format("\\{0}{1}{2}{3}DataExtract.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
//Display excel spreadsheet
this.Response.Clear();
this.Response.Buffer = true;
this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.Charset = "";
if (newFile.Length == 0)
{
this.Response.Write("Error encountered - no spreadsheet to display");
}
else
{
this.Response.WriteFile(newFile);
}
Response.End();
}
private string MakeReport(DateTime aggStartDate, DateTime repDate, bool makeAggData, string xlsReportName)
{
//if the month is the aggStart use the blank template. Otherwise, use last months
string xlsBaseFileName;
if (aggStartDate == repDate)
{
xlsBaseFileName = "NNIN Data Blank.xls";
}
else
{
xlsBaseFileName = "AS" + aggStartDate.ToString("yyyyMM") + "_Report" + repDate.AddMonths(-1).ToString("yyyyMM") + ".xls";
}
string xlsBaseFilePath = xlsFilePath + xlsBaseFileName;
string xlsReportPath = xlsFilePath + xlsReportName;
DateTime period = repDate;
DateTime sDate = period;
DateTime eDate = sDate.AddMonths(1);
Compile compile = new Compile();
//this gets the aggregated costs for rooms and tools
DataSet dsCostTables = NNINDA.GetCostTables(period);
DataTable dtRoomCost = dsCostTables.Tables[0];
DataTable dtToolCost = dsCostTables.Tables[1];
//get Client info
DataSet ds = DataCommand()
.Param("Action", "GetAllTables")
.Param("Period", period)
.Param("Privs", (int)ClientPrivilege.LabUser)
.Param("MakeCumUser", makeAggData)
.FillDataSet("dbo.NNIN_Select");
ds.Tables[0].TableName = "ClientTechInt";
ds.Tables[1].TableName = "AcctOrgType";
ds.Tables[2].TableName = "TechnicalInterest_Hours";
ds.Tables[3].TableName = "OrgType_Hours";
ds.Tables[4].TableName = "TechnicalInterest_Users";
ds.Tables[5].TableName = "OrgType_Users";
ds.Tables[6].TableName = "TechnicalInterest_Fees";
ds.Tables[7].TableName = "OrgType_Fees";
DataColumn[] pk;
pk = new DataColumn[] { ds.Tables["TechnicalInterest_Fees"].Columns["TechnicalInterestID"] };
ds.Tables["TechnicalInterest_Fees"].PrimaryKey = pk;
pk = new DataColumn[] { ds.Tables["OrgType_Fees"].Columns["OrgTypeID"] };
ds.Tables["OrgType_Fees"].PrimaryKey = pk;
//calc fees by tech interest and orgtype
DataRow drTI, drOT;
DataRow[] fdr;
foreach (DataTable dt in dsCostTables.Tables)
{
foreach (DataRow dr in dt.Rows)
{
fdr = ds.Tables["ClientTechInt"].Select(string.Format("ClientID = {0}", dr["ClientID"]));
if (fdr.Length > 0)
{
drTI = ds.Tables["TechnicalInterest_Fees"].Rows.Find(fdr[0]["TechnicalInterestID"]);
drTI["Fees"] = drTI.Field <double>("Fees") + dr.Field <double>("TotalCalcCost");
fdr = ds.Tables["AcctOrgType"].Select(string.Format("AccountID = {0}", dr["AccountID"]));
if (fdr.Length > 0)
{
drOT = ds.Tables["OrgType_Fees"].Rows.Find(fdr[0]["OrgTypeID"]);
drOT["Fees"] = drOT.Field <double>("Fees") + dr.Field <double>("TotalCalcCost");
}
}
}
}
//get raw cumulative user data
DataCommand()
.Param("Action", "Aggregate")
.Param("sDate", aggStartDate)
.Param("eDate", repDate)
.FillDataSet(ds, "dbo.CumUser_Select", "CumUser");
DataTable dtReport = new DataTable();
dtReport.Columns.Add("BaseRow", typeof(int));
dtReport.Columns.Add("TotalRow", typeof(int));
dtReport.Columns.Add("RepType", typeof(string)); //fees, hours, cum
dtReport.Columns.Add("ByType", typeof(string)); //orgtype, techid, none
dtReport.Columns.Add("DateOnly", typeof(bool)); //orgtype, techid, none
AddReportRow(dtReport, new object[] { 10, 0, string.Empty, string.Empty, true });
AddReportRow(dtReport, new object[] { 24, 43, "Hours", "TechnicalInterest", false });
AddReportRow(dtReport, new object[] { 11, 43, "Hours", "OrgType", false });
AddReportRow(dtReport, new object[] { 40, 0, string.Empty, string.Empty, true });
AddReportRow(dtReport, new object[] { 69, 88, "Users", "TechnicalInterest", false });
AddReportRow(dtReport, new object[] { 55, 88, "Users", "OrgType", false });
AddReportRow(dtReport, new object[] { 85, 0, string.Empty, string.Empty, true });
AddReportRow(dtReport, new object[] { 161, 180, "Fees", "TechnicalInterest", false });
AddReportRow(dtReport, new object[] { 146, 180, "Fees", "OrgType", false });
AddReportRow(dtReport, new object[] { 177, 0, string.Empty, string.Empty, true });
AddReportRow(dtReport, new object[] { 116, 136, "CumUser", "TechnicalInterest", false });
AddReportRow(dtReport, new object[] { 101, 136, "CumUser", "OrgType", false });
AddReportRow(dtReport, new object[] { 133, 0, string.Empty, string.Empty, true });
AddReportRow(dtReport, new object[] { 226, 0, "CumUser", "DemGender", false });
AddReportRow(dtReport, new object[] { 232, 0, "CumUser", "DemEthnic", false });
AddReportRow(dtReport, new object[] { 238, 0, "CumUser", "DemRace", false });
AddReportRow(dtReport, new object[] { 247, 0, "CumUser", "DemDisability", false });
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile SpreadSheet = new ExcelFile();
SpreadSheet.LoadXls(xlsBaseFilePath);
ExcelWorksheet ws = SpreadSheet.Worksheets["UM SSEL"];
int lastVal, useCol = 0, useRow;
double hardTotals; //for recording internal time without formula
foreach (DataRow drReport in dtReport.Rows)
{
//write dates
for (int j = 0; j < 12; j++)
{
ws.Cells[Convert.ToInt32(drReport["BaseRow"]), j + 1].Value = aggStartDate.AddMonths(j);
if (aggStartDate.AddMonths(j) == repDate)
{
useCol = j + 1;
}
}
if (!Convert.ToBoolean(drReport["DateOnly"]))
{
hardTotals = 0;
if (drReport["RepType"].ToString() == "CumUser")
{
fdr = ds.Tables["CumUser"].Select(string.Format("TableName = '{0}'", drReport["ByType"]));
for (int j = 0; j < fdr.Length; j++)
{
useRow = drReport.Field <int>("BaseRow") + fdr[j].Field <int>("Value");
if (aggStartDate == repDate)
{
ws.Cells[useRow, useCol].Value = fdr[j]["Count"];
}
else
{
lastVal = Convert.ToInt32(ws.Cells[useRow, 1].Value);
for (int i = 2; i < useCol; i++)
{
string form = ws.Cells[useRow, i].Formula;
if (!string.IsNullOrEmpty(form))
{
lastVal += Convert.ToInt32(form.Substring(form.IndexOf("+") + 1));
}
}
ws.Cells[useRow, useCol].Formula = "=" + Convert.ToChar(64 + useCol) + (useRow + 1).ToString() + "+" + (Convert.ToInt32(fdr[j]["Count"]) - lastVal).ToString();
}
hardTotals += fdr[j].Field <double>("Count");
}
if (Convert.ToInt32(drReport["TotalRow"]) != 0)
{
ws.Cells[Convert.ToInt32(drReport["TotalRow"]), useCol].Value = hardTotals;
}
}
else
{
foreach (DataRow dr in ds.Tables[string.Format("{0}_{1}", drReport["ByType"], drReport["RepType"])].Rows)
{
useRow = drReport.Field <int>("BaseRow") + dr.Field <int>(string.Format("{0}ID", drReport["ByType"]));
ws.Cells[useRow, useCol].Value = dr[drReport["RepType"].ToString()];
hardTotals += Convert.ToDouble(dr[drReport["RepType"].ToString()]);
}
ws.Cells[Convert.ToInt32(drReport["TotalRow"]), useCol].Value = hardTotals;
}
}
}
SpreadSheet.SaveXls(xlsReportPath);
SpreadSheet = null;
System.GC.Collect();
return(xlsReportPath);
}
