public byte[] GenerateExcel(SLExcelData data)
{
var stream = new MemoryStream();
var document = SpreadsheetDocument
.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = document.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
var sheet = new Sheet()
{
Id = document.WorkbookPart
.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = data.SheetName ?? "Sheet 1"
};
sheets.AppendChild(sheet);
// Add header
uint rowIdex = 0;
var row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
var cellIdex = 0;
foreach (var header in data.Headers)
{
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++),
rowIdex, header ?? string.Empty));
}
if (data.Headers.Count > 0)
{
// Add the column configuration if available
if (data.ColumnConfigurations is {})
public void GetVisitLecture(int subjectId, int groupId)
{
var data = new SLExcelData();
var headerData = LecturerManagementService.GetLecturesScheduleVisitings(subjectId);
var rowsData = LecturerManagementService.GetLecturesScheduleMarks(subjectId, groupId);
data.Headers.Add("Студент");
data.Headers.AddRange(headerData);
data.DataRows.AddRange(rowsData);
var file = (new SLExcelWriter()).GenerateExcel(data);
Response.Clear();
Response.Charset = "ru-ru";
Response.HeaderEncoding = Encoding.UTF8;
Response.ContentEncoding = Encoding.UTF8;
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", "attachment; filename=LectureVisiting.xlsx");
Response.BinaryWrite(file);
Response.Flush();
Response.End();
}
public SLExcelData ReadExcel(HttpPostedFileBase file)
{
var data = new SLExcelData();
// Check if the file is excel
if (file.ContentLength <= 0)
{
data.Status.Message = "You uploaded an empty file";
return data;
}
if (file.ContentType
!= "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
data.Status.Message
= "Please upload a valid excel file of version 2007 and above";
return data;
}
// Open the excel document
WorkbookPart workbookPart; List<Row> rows;
try
{
var document = SpreadsheetDocument.Open(file.InputStream, false);
workbookPart = document.WorkbookPart;
var sheets = workbookPart.Workbook.Descendants<Sheet>();
var sheet = sheets.First();
data.SheetName = sheet.Name;
var workSheet = ((WorksheetPart)workbookPart
.GetPartById(sheet.Id)).Worksheet;
var columns = workSheet.Descendants<Columns>().FirstOrDefault();
data.ColumnConfigurations = columns;
var sheetData = workSheet.Elements<SheetData>().First();
rows = sheetData.Elements<Row>().ToList();
}
catch (Exception e)
{
data.Status.Message = "Unable to open the file";
return data;
}
// Read the header
if (rows.Count > 0)
{
var row = rows[0];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
data.Headers.Add(text);
}
}
// Read the sheet data
if (rows.Count > 1)
{
for (var i = 1; i < rows.Count; i++)
{
var dataRow = new List<string>();
data.DataRows.Add(dataRow);
var row = rows[i];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
dataRow.Add(text);
}
}
}
return data;
}
public SLExcelData ReadExcel(HttpPostedFileBase file)
{
var data = new SLExcelData();
// Check if the file is excel
if (file.ContentLength <= 0)
{
data.Status.Message = "You uploaded an empty file";
return(data);
}
if (file.ContentType
!= "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
data.Status.Message
= "Please upload a valid excel file of version 2007 and above";
return(data);
}
// Open the excel document
WorkbookPart workbookPart; List <Row> rows;
try
{
var document = SpreadsheetDocument.Open(file.InputStream, false);
workbookPart = document.WorkbookPart;
var sheets = workbookPart.Workbook.Descendants <Sheet>();
var sheet = sheets.First();
data.SheetName = sheet.Name;
var workSheet = ((WorksheetPart)workbookPart
.GetPartById(sheet.Id)).Worksheet;
var columns = workSheet.Descendants <Columns>().FirstOrDefault();
data.ColumnConfigurations = columns;
var sheetData = workSheet.Elements <SheetData>().First();
rows = sheetData.Elements <Row>().ToList();
}
catch (Exception e)
{
data.Status.Message = "Unable to open the file";
return(data);
}
// Read the header
if (rows.Count > 0)
{
var row = rows[0];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
data.Headers.Add(text);
}
}
// Read the sheet data
if (rows.Count > 1)
{
for (var i = 1; i < rows.Count; i++)
{
var dataRow = new List <string>();
data.DataRows.Add(dataRow);
var row = rows[i];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
dataRow.Add(text);
}
}
}
return(data);
}
public byte[] GenerateExcel(SLExcelData data)
{
var stream = new MemoryStream();
var document = SpreadsheetDocument
.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = document.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
var sheet = new Sheet()
{
Id = document.WorkbookPart
.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = data.SheetName ?? "Sheet 1"
};
sheets.AppendChild(sheet);
// Add header
UInt32 rowIdex = 0;
var row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
var cellIdex = 0;
foreach (var header in data.Headers)
{
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++),
rowIdex, header ?? string.Empty));
}
if (data.Headers.Count > 0)
{
// Add the column configuration if available
if (data.ColumnConfigurations != null)
{
var columns = (Columns)data.ColumnConfigurations.Clone();
worksheetPart.Worksheet
.InsertAfter(columns, worksheetPart
.Worksheet.SheetFormatProperties);
}
}
// Add sheet data
foreach (var rowData in data.DataRows)
{
cellIdex = 0;
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
foreach (var callData in rowData)
{
var cell = CreateTextCell(ColumnLetter(cellIdex++),
rowIdex, callData ?? string.Empty);
row.AppendChild(cell);
}
}
workbookpart.Workbook.Save();
document.Close();
return(stream.ToArray());
}
public byte[] GenerateExcel(SLExcelData data)
{
var stream = new MemoryStream();
var document = SpreadsheetDocument
.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = document.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
var sheet = new Sheet()
{
Id = document.WorkbookPart
.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = data.SheetName ?? "Sheet 1"
};
sheets.AppendChild(sheet);
// Add header
UInt32 rowIdex = 0;
var row = new Row { RowIndex = ++rowIdex };
sheetData.AppendChild(row);
var cellIdex = 0;
foreach (var header in data.Headers)
{
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++),
rowIdex, header ?? string.Empty));
}
if (data.Headers.Count > 0)
{
// Add the column configuration if available
if (data.ColumnConfigurations != null)
{
var columns = (Columns)data.ColumnConfigurations.Clone();
worksheetPart.Worksheet
.InsertAfter(columns, worksheetPart
.Worksheet.SheetFormatProperties);
}
}
// Add sheet data
foreach (var rowData in data.DataRows)
{
cellIdex = 0;
row = new Row { RowIndex = ++rowIdex };
sheetData.AppendChild(row);
foreach (var callData in rowData)
{
var cell = CreateTextCell(ColumnLetter(cellIdex++),
rowIdex, callData ?? string.Empty);
row.AppendChild(cell);
}
}
workbookpart.Workbook.Save();
document.Close();
return stream.ToArray();
}
public void GetResultsExcel(int groupId, int subjectId)
{
TestResultItemListViewModel[] results = TestPassingService.GetPassTestResults(groupId, subjectId).Select(TestResultItemListViewModel.FromStudent).OrderBy(res => res.StudentName).ToArray();
var data = new SLExcelData();
var rowsData = new List<List<string>>();
foreach (var result in results)
{
var datas = new List<string>();
datas.Add(result.StudentName);
datas.AddRange(result.TestPassResults.Select(e => e.Points != null ? string.Format("{0}({1}%)", e.Points, e.Percent) : string.Empty));
if (result.TestPassResults.Count(e => e.Points != null) > 0)
{
var pointsSum = Math.Round((decimal)result.TestPassResults.Sum(e => e.Points).Value / result.TestPassResults.Count(e => e.Points != null), 0, MidpointRounding.AwayFromZero);
var percentSum = Math.Round((decimal)result.TestPassResults.Sum(e => e.Percent).Value / result.TestPassResults.Count(e => e.Percent != null), 0);
//datas.Add(pointsSum + " (" + percentSum + "%)");
datas.Add(pointsSum.ToString());
}
rowsData.Add(datas);
}
var index = 0;
var total = new List<string>()
{
"Средний процен за тест",
};
foreach (var testResultItemListViewModel in results[0].TestPassResults)
{
var count = 0;
decimal sum = 0;
decimal sumPoint = 0;
foreach (var resultItemListViewModel in results)
{
if (resultItemListViewModel.TestPassResults[index].Points != null)
{
count += 1;
sumPoint += resultItemListViewModel.TestPassResults[index].Points.Value;
}
if (resultItemListViewModel.TestPassResults[index].Percent != null)
{
sum += resultItemListViewModel.TestPassResults[index].Percent.Value;
}
}
index += 1;
//total.Add((int)Math.Round(sumPoint/count, 0, MidpointRounding.AwayFromZero) + " (" + Math.Round(sum / count, 0) + "%)");
total.Add(Math.Round(sum / count, 0) + "%");
}
data.Headers.Add("Студент");
data.Headers.AddRange(results[0].TestPassResults.Select(e => e.TestName));
data.DataRows.AddRange(rowsData);
data.DataRows.Add(total);
var file = (new SLExcelWriter()).GenerateExcel(data);
Response.Clear();
Response.Charset = "ru-ru";
Response.HeaderEncoding = Encoding.UTF8;
Response.ContentEncoding = Encoding.UTF8;
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", "attachment; filename=TestResult.xlsx");
Response.BinaryWrite(file);
Response.Flush();
Response.End();
}