private XLWorkbook BuildEmptyWorkBook(ReportDataCredential reportData, string sheet = "")
{
DataTable dt = reportData.ReportDataTable;
SelectedCriteriaCrd selectedCriteria = reportData.ReportSelectedCriteria;
XLWorkbook workbook = new XLWorkbook();
int colCount;
var ws = workbook.Worksheets.Add(sheet ?? "Sheet1");
// set the header size and alignment
IXLRows headerRow = ws.Rows(1, 1);
if (headerRow != null)
{
headerRow.Height = 20;
headerRow.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
headerRow.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
headerRow.Style.Font.SetBold();
}
colCount = 1; //reset columns
foreach (DataColumn column in dt.Columns)
{
ws.Cell(1, colCount).Value = column.ColumnName;
ws.Cell(1, colCount).Style.Font.FontColor = XLColor.White;
ws.Cell(1, colCount).Style.Fill.BackgroundColor = XLColor.BlueBell;
ws.Column(colCount).AdjustToContents();
colCount++;
}
return workbook;
}
private XLWorkbook BuildWorkBook(ReportDataCredential reportData, string sheet = "")
{
DataTable dt = reportData.ReportDataTable;
SelectedCriteriaCrd selectedCriteria = reportData.ReportSelectedCriteria;
XLWorkbook workbook = new XLWorkbook();
if (dt.Rows.Count > 0)
{
var ws = workbook.Worksheets.Add(sheet ?? "Sheet1");
// set the header size and alignment
IXLRows headerRow = ws.Rows(1, 1);
if (headerRow != null)
{
headerRow.Height = 20;
headerRow.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
headerRow.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
headerRow.Style.Font.SetBold();
}
int colCount;
//Write second rows first so that we can calculate width of for the headers
var rowCount = 2;
foreach (DataRow row in dt.Rows) // Loop over the rows.
{
colCount = 1;
foreach (var item in row.ItemArray) // Loop over the items.
{
string theCellValue = item.ToString();
//if (colCount == 1)
//{
// ws.Cell(rowCount, colCount).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
// ws.Cell(rowCount, colCount).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
// ws.Cell(rowCount, colCount).Style.Alignment.WrapText = true;
//}
ws.Cell(rowCount, colCount).Value = theCellValue;
colCount++;
}
rowCount++;
}
colCount = 1; //reset columns
foreach (DataColumn column in dt.Columns)
{
ws.Cell(1, colCount).Value = column.ColumnName;
ws.Cell(1, colCount).Style.Font.FontColor = XLColor.White;
ws.Cell(1, colCount).Style.Fill.BackgroundColor = XLColor.BlueBell;
ws.Column(colCount).AdjustToContents();
colCount++;
}
return workbook;
}
return null;
}
public void ExportEmptyExcel(ReportDataCredential reportData)
{
// Create the workbook
XLWorkbook workbook = BuildEmptyWorkBook(reportData, CredentialTrackingReport);
//Prepare the response
System.Web.HttpBrowserCapabilities browser = System.Web.HttpContext.Current.Request.Browser;
Response.Clear();
Response.Buffer = true;
using (MemoryStream memoryStream = new MemoryStream())
{
if (browser.Platform.IndexOf("WinNT", StringComparison.InvariantCultureIgnoreCase) >= 0)
{
workbook.SaveAs(memoryStream);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=" + CredentialTrackingReport_NoSpaces + ".xlsx");
Response.BinaryWrite(memoryStream.ToArray());
}
else
{
Response.ClearHeaders();
Response.ClearContent();
Response.ContentType = "text/csv";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + CredentialTrackingReport_NoSpaces + ".csv");
byte[] csv = ExportToCSV.ConvertXLToCSV(workbook);
Response.BinaryWrite(csv);
}
Response.End();
}
}
private void FormatColumnsForExcel(SelectedCriteriaCrd selectedCriteria)
{
string selectedViewBy = selectedCriteria.SelectedViewBy;
DataTable localDataTable = GridDataTable.Copy();
switch (selectedViewBy)
{
case "District":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns.Remove("LevelName");
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
case "School":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "School";
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
case "Teacher":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "Teacher";
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
case "Class":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "Class";
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
case "Group":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "Group";
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
case "Student":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "Student";
localDataTable.Columns.Remove("CredentialID");
// Need to modify for yes/no value.
//localDataTable.Columns["EarnedDate"].DataType = typeof(String);
DataColumn EarnedCol = localDataTable.Columns.Add("Earned", typeof(String));
foreach (DataRow dr in localDataTable.Rows)
{
string date = dr["EarnedDate"].ToString().Trim();
dr["Earned"] = String.IsNullOrEmpty(date) ? "No" : "Yes";
}
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns.Remove("StudentCount");
break;
case "Demographics":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "Demographics";
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
case "Alignment":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "Alignment";
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
case "Year":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "Year";
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
}
ReportDataCredential theReportData = new ReportDataCredential
{
ReportDataTable = localDataTable,
ReportSelectedCriteria = selectedCriteria
};
SessionObject.CredentialTracking_ReportData = theReportData;
}