public void WhenCloneCell_ThenCellIsCloned()
{
string outputPath = Path.ChangeExtension(Path.Combine("TestData", MethodBase.GetCurrentMethod().Name), ".xlsx");
byte[] templateData = File.ReadAllBytes(@"TestData\StudentProfileExportTemplate.xltx");
using (MemoryStream stream = new MemoryStream())
{
stream.Write(templateData, 0, (int)templateData.Length);
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
{
spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
Worksheet worksheet = spreadsheetDoc.WorkbookPart.WorksheetParts.First().Worksheet;
ExcelUtility.CopyCell(worksheet, "D", 2, "E", 2);
}
File.WriteAllBytes(outputPath, stream.ToArray());
}
using (XLWorkbook workbook = new XLWorkbook(outputPath))
{
IXLWorksheet worksheet = workbook.Worksheet(1);
IXLCell originalCell = worksheet.Cell(2, "D");
IXLCell clonedCell = worksheet.Cell(2, "E");
Assert.AreEqual(originalCell.DataType, clonedCell.DataType);
Assert.AreEqual(originalCell.FormulaA1, clonedCell.FormulaA1);
Assert.AreEqual(originalCell.FormulaR1C1, clonedCell.FormulaR1C1);
Assert.AreEqual(originalCell.RichText.Text, clonedCell.RichText.Text);
Assert.AreEqual(originalCell.ShareString, clonedCell.ShareString);
Assert.AreEqual(originalCell.Style, clonedCell.Style);
Assert.AreEqual(originalCell.Value, clonedCell.Value);
Assert.AreEqual(originalCell.ValueCached, clonedCell.ValueCached);
}
}
private bool IsValidServiceOffering(EducationSecurityPrincipal user, int index, string studentSISId, Student student, DataTable dataTable, out DateTime?startDate, out DateTime?endDate, ServiceUploadModel model)
{
startDate = endDate = null;
if (studentSISId == null || student == null)
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "Malformed Student Id on row {0}", index + 1), model);
return(false);
}
else if (!GrantUserAccessToSchedulingAnOffering(user, student))
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "You do not have permission to interact with the referenced student on row {0}", index + 1), model);
return(false);
}
else if (!ExcelUtility.TryGetOADate(dataTable.Rows[index][2].ToString(), out startDate))
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "Malformed Start Date on row {0}", index + 1), model);
return(false);
}
else if (!ExcelUtility.TryGetOADate(dataTable.Rows[index][3].ToString(), out endDate))
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "Malformed End Date on row {0}", index + 1), model);
return(false);
}
return(true);
}
private bool IsValidServiceOffering(EducationSecurityPrincipal user, int index, string studentSISId, Student student, DataTable dataTable, out DateTime?dateAttended, out int duration, ServiceUploadModel model, Subject subject)
{
dateAttended = null;
duration = 0;
string subjectName = dataTable.Rows[index][3].ToString();
if (studentSISId == null || student == null)
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "Malformed Student Id on row {0}", index + 1), model);
return(false);
}
else if (!GrantUserAccessToSchedulingAnOffering(user, student))
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "You do not have permission to interact with the referenced student on row {0}", index + 1), model);
return(false);
}
else if (!ExcelUtility.TryGetOADate(dataTable.Rows[index][2].ToString(), out dateAttended))
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "Malformed Date Attended on row {0}", index + 1), model);
return(false);
}
else if (subject == null)
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "Malformed Subject on row {0}", index + 1), model);
return(false);
}
else if (dataTable.Rows[index][4].ToString() != string.Empty && !int.TryParse(dataTable.Rows[index][4].ToString(), out duration))
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "Malformed Duration on row {0}", index + 1), model);
return(false);
}
return(true);
}
public void AppendErrorRows(string sheetName, IWorksheetWriter worksheetWriter)
{
if (worksheetWriter == null)
{
throw new ArgumentNullException("worksheetWriter");
}
if (FileContentStream == null)
{
throw new InvalidOperationException(string.Format("Cannot {0} prior to initializing from a template.", MethodBase.GetCurrentMethod().Name));
}
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(FileContentStream, true))
{
//Access the main Workbook part, which contains data
WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart;
Sheet ss = ExcelUtility.FindSheet(sheetName, workbookPart);
if (ss == null)
{
throw new InvalidOperationException("Cannot find sheet named '" + sheetName + "' in workbook.");
}
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
if (worksheetPart != null)
{
worksheetWriter.CreateErrorRows(worksheetPart);
worksheetPart.Worksheet.Save();
}
}
}
public void Given53_WhenGetColumnNameFromIndex_ThenBA()
{
string expected = "BA";
string actual = ExcelUtility.GetColumnNameFromIndex(53);
Assert.AreEqual(expected, actual);
}
public void Given52_WhenGetColumnNameFromIndex_ThenAZ()
{
string expected = "AZ";
string actual = ExcelUtility.GetColumnNameFromIndex(52);
Assert.AreEqual(expected, actual);
}
public void Given27_WhenGetColumnNameFromIndex_ThenAA()
{
string expected = "AA";
string actual = ExcelUtility.GetColumnNameFromIndex(27);
Assert.AreEqual(expected, actual);
}
public void CreateHeader(WorksheetPart worksheetPart)
{
if (worksheetPart == null)
{
throw new ArgumentNullException("worksheetPart");
}
ExcelUtility.SetStringCell(worksheetPart.Worksheet, "B", 2, ServiceOffering.Id.ToString());
ExcelUtility.SetStringCell(worksheetPart.Worksheet, "C", 2, ServiceOffering.Name);
}
public void CreateErrorRows(WorksheetPart worksheetPart)
{
for (int rowIndex = 0; rowIndex < ErrorRows.Count; rowIndex++)
{
for (int i = 0; i < ErrorRows[rowIndex].RowErrors.Count; i++)
{
ExcelUtility.SetStringCell(worksheetPart.Worksheet, CellDictionary[i], (uint)rowIndex + 4, ErrorRows[rowIndex].RowErrors[i]);
}
}
}
private void WriteDataValue(uint rowIndex, uint columnIndex, object value)
{
string columnName = ExcelUtility.GetColumnNameFromIndex(columnIndex);
IEnumerable <object> listValue = value as IEnumerable <object>;
if (listValue != null)
{
value = string.Join(", ", listValue.Where(item => item != null).Select(item => item.ToString()));
}
ExcelUtility.SetSharedStringCell(Document, Worksheet, columnName, rowIndex, value.ToString());
}
private void WriteColumnHeading(uint columnIndex, string headerName)
{
string columnName = ExcelUtility.GetColumnNameFromIndex(columnIndex);
ExcelUtility.EnsureColumn(Worksheet, columnIndex);
if (columnIndex > 1)
{
ExcelUtility.CopyCell(Worksheet, "A", HeaderRowIndex, columnName, HeaderRowIndex);
}
ExcelUtility.SetSharedStringCell(Document, Worksheet, columnName, HeaderRowIndex, headerName);
}
public void GivenWorksheet_WhenCreateHeader_ThenCorrectHeaderCreated()
{
byte[] byteArray = File.ReadAllBytes(Path.GetFullPath(ConfigurationManager.AppSettings["FileUploadTemplatePath"] + "Templates/" + ServiceOfferingController.TemplateFile));
MemoryStream stream = new MemoryStream(byteArray);
WorksheetPart actual;
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
{
actual = ExcelUtility.GetWorksheetPartByName(spreadsheetDoc, Target.SheetName);
Target.CreateHeader(actual);
}
Assert.AreEqual(GetCell(actual.Worksheet, "B", 2).CellValue.InnerText, ServiceOffering.Id.ToString());
Assert.AreEqual(GetCell(actual.Worksheet, "C", 2).CellValue.InnerText, ServiceOffering.Name);
}
public void GivenWorksheetWithFourColumns_WhenCreateErrorRows_ThenCorrectRowsAreCreated()
{
byte[] byteArray = File.ReadAllBytes(Path.GetFullPath(ConfigurationManager.AppSettings["FileUploadTemplatePath"] + "Templates/" + ServiceOfferingController.TemplateFile));
MemoryStream stream = new MemoryStream(byteArray);
WorksheetPart actual;
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
{
actual = ExcelUtility.GetWorksheetPartByName(spreadsheetDoc, Target.SheetName);
Target.ErrorRows.Add(new FileRowModel {
RowErrors = new List <string> {
"1", "1/1/1900", "1/1/1999", "Notes"
}
});
Target.CreateErrorRows(actual);
}
Assert.AreEqual(GetCell(actual.Worksheet, "B", 4).CellValue.InnerText, "1");
Assert.AreEqual(GetCell(actual.Worksheet, "C", 4).CellValue.InnerText, "1/1/1900");
Assert.AreEqual(GetCell(actual.Worksheet, "D", 4).CellValue.InnerText, "1/1/1999");
Assert.AreEqual(GetCell(actual.Worksheet, "E", 4).CellValue.InnerText, "Notes");
}
public void InitializeFrom(string templatePath, IWorksheetWriter worksheetWriter)
{
if (worksheetWriter == null)
{
throw new ArgumentNullException("worksheetWriter");
}
byte[] byteArray = File.ReadAllBytes(templatePath);
MemoryStream stream = new MemoryStream(byteArray);
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
{
// Change from template type to workbook type
spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
WorksheetPart worksheetPart = ExcelUtility.GetWorksheetPartByName(spreadsheetDoc, worksheetWriter.SheetName);
if (worksheetPart != null)
{
worksheetWriter.CreateHeader(worksheetPart);
}
}
FileContentStream = stream;
}
public static DataTable ExtractExcelSheetValues(Stream excelStream, string sheetName)
{
DataTable dt = new DataTable();
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(excelStream, true))
{
//Access the main Workbook part, which contains data
WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart;
Sheet ss = ExcelUtility.FindSheet(sheetName, workbookPart);
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;
if (worksheetPart != null)
{
Row lastRow = worksheetPart.Worksheet.Descendants <Row>().LastOrDefault();
Row headerRow = worksheetPart.Worksheet.Descendants <Row>().Skip(1).First();
if (headerRow != null)
{
foreach (Cell c in headerRow.ChildElements)
{
string value = GetValue(c, stringTablePart);
dt.Columns.Add(value);
}
}
if (lastRow != null)
{
for (int i = 1; i <= lastRow.RowIndex; i++)
{
DataRow dr = dt.NewRow();
bool empty = true;
Row row = worksheetPart.Worksheet.Descendants <Row>().Where(r => i == r.RowIndex).FirstOrDefault();
int j = 0;
if (row != null)
{
foreach (Cell c in row.ChildElements)
{
//Get cell value
string value = GetValue(c, stringTablePart);
if (!string.IsNullOrEmpty(value) && value != " ")
{
empty = false;
}
var col = ConvertColumnLettering(c.CellReference.Value[0]);
if (col >= 0)
{
dr[col] = value;
}
j++;
if (j == dt.Columns.Count)
{
break;
}
}
if (empty)
{
break;
}
dt.Rows.Add(dr);
}
}
}
}
}
return(dt);
}
public void GivenNullDocument_WhenGetWorksheetPartByName_ThenThrowException()
{
TestExtensions.ExpectException <ArgumentNullException>(() => ExcelUtility.GetWorksheetPartByName(null, ""));
}