public void ExcelPackageHelper_SetColumnByIndex_GetColumnByIndex()
{
//arrange
ExcelPackage package = new ExcelPackage();
string name = "Title";
ExcelWorksheet worksheet = ExcelPackageHelper.AddWorksheet(package, name);
List <object> col1 = new List <object>()
{
null, 123, "abc", "", new DateTime(1999, 3, 2)
};
List <object> col2 = new List <object>()
{
456, "", null, null, null, null, 789
};
//act
ExcelPackageHelper.SetColumnByIndex(worksheet, 1, col1, skipFirstRow: false);
ExcelPackageHelper.SetColumnByIndex(worksheet, 2, col2, skipFirstRow: false);
List <object> result1 = ExcelPackageHelper.GetColumnByIndex(worksheet, 1, skipFirstRow: false);
List <object> result2 = ExcelPackageHelper.GetColumnByIndex(worksheet, 2, skipFirstRow: false);
//assert
Assert.AreEqual(2, ExcelPackageHelper.CountColumns(worksheet));
Assert.AreEqual(Math.Max(col1.Count, col2.Count), ExcelPackageHelper.CountRows(worksheet));
for (int i = 0; i < col1.Count; i++)
{
Assert.AreEqual(col1[i], result1[i]);
}
for (int i = 0; i < col2.Count; i++)
{
Assert.AreEqual(col2[i], result2[i]);
}
}
public void ExcelPackageHelper_CountColumns_FromColumns()
{
//arrange
ExcelPackage package = new ExcelPackage();
string name = "Title";
ExcelWorksheet worksheet = ExcelPackageHelper.AddWorksheet(package, name);
List <object> colB = new List <object>()
{
"ColA", "ColB", "ColC"
};
List <object> colD = new List <object>()
{
"ColA"
};
List <object> colF = new List <object>()
{
"ColA", "ColB", "ColC", "ColD", "ColE"
};
//act
ExcelPackageHelper.SetColumnByChar(worksheet, "B", colB, skipFirstRow: false);
ExcelPackageHelper.SetColumnByChar(worksheet, "D", colD, skipFirstRow: false);
ExcelPackageHelper.SetColumnByChar(worksheet, "F", colF, skipFirstRow: false);
int count = ExcelPackageHelper.CountColumns(worksheet);
//assert
Assert.AreEqual(6, count);
}
public void ExcelPackageHelper_CountColumns_FromRows()
{
//arrange
ExcelPackage package = new ExcelPackage();
string name = "Title";
ExcelWorksheet worksheet = ExcelPackageHelper.AddWorksheet(package, name);
List <object> rowA = new List <object>()
{
"ColA", "ColB", "ColC"
};
List <object> rowB = new List <object>()
{
"ColA"
};
List <object> rowC = new List <object>()
{
"ColA", "ColB", "ColC", "ColD", "ColE"
};
//act
ExcelPackageHelper.AppendRow(worksheet, rowA);
ExcelPackageHelper.AppendRow(worksheet, rowB);
ExcelPackageHelper.AppendRow(worksheet, rowC);
int count = ExcelPackageHelper.CountColumns(worksheet);
//assert
Assert.AreEqual(5, count);
}
public void ExcelPackageHelper_CountRows_FromColumns()
{
//arrange
ExcelPackage package = new ExcelPackage();
string name = "Title";
ExcelWorksheet worksheet = ExcelPackageHelper.AddWorksheet(package, name);
List <object> colA = new List <object>()
{
"ColA", "ColB", "ColC"
};
List <object> colB = new List <object>()
{
"ColA"
};
List <object> colC = new List <object>()
{
"ColA", "ColB", "ColC", "ColD", "ColE"
};
//act
ExcelPackageHelper.SetColumnByIndex(worksheet, 1, colA, skipFirstRow: false);
ExcelPackageHelper.SetColumnByIndex(worksheet, 2, colB, skipFirstRow: false);
ExcelPackageHelper.SetColumnByIndex(worksheet, 3, colC, skipFirstRow: false);
int count = ExcelPackageHelper.CountRows(worksheet);
//assert
Assert.AreEqual(5, count);
}
private List <Task> LoadTasks(ExcelWorksheet worksheet)
{
List <string> ids = ExcelPackageHelper.GetColumnByChar(worksheet, columnLayout.IdColumn, skipFirstRow: true).Cast <string>().ToList();
List <string> descriptions = ExcelPackageHelper.GetColumnByChar(worksheet, columnLayout.DescriptionColumn, skipFirstRow: true).Cast <string>().ToList();
List <string> statuses = ExcelPackageHelper.GetColumnByChar(worksheet, columnLayout.StatusColumn, skipFirstRow: true).Cast <string>().ToList();
List <string> categories = ExcelPackageHelper.GetColumnByChar(worksheet, columnLayout.CategoryColumn, skipFirstRow: true).Cast <string>().ToList();
List <string> createDates = ExcelPackageHelper.GetColumnByChar(worksheet, columnLayout.CreateDateColumn, skipFirstRow: true).Cast <string>().ToList();
List <string> doneDates = ExcelPackageHelper.GetColumnByChar(worksheet, columnLayout.DoneDateColumn, skipFirstRow: true).Cast <string>().ToList();
List <Task> tasks = new List <Task>();
for (int i = 0; i < descriptions.Count; i++)
{
tasks.Add(new Task()
{
Id = (i < ids.Count && !String.IsNullOrEmpty(ids[i])) ? Int32.Parse(ids[i]) : 0,
Description = descriptions[i],
Status = (i < statuses.Count) ? statuses[i] : "",
Category = (i < categories.Count) ? categories[i] : "",
CreateDate = (i < createDates.Count && !String.IsNullOrEmpty(createDates[i])) ? DateTime.Parse(createDates[i]) : DateTime.Now,
DoneDate = (i < doneDates.Count && !String.IsNullOrEmpty(doneDates[i])) ? DateTime.Parse(doneDates[i]) : (DateTime?)null
});
}
return(tasks);
}
public void ExcelPackageHelper_AppendRow_GetRow()
{
//arrange
ExcelPackage package = new ExcelPackage();
string name = "Title";
ExcelWorksheet worksheet = ExcelPackageHelper.AddWorksheet(package, name);
List <object> row1 = new List <object>()
{
null, 123, "abc", "", new DateTime(1999, 3, 2)
};
List <object> row2 = new List <object>()
{
456, "", null, null, null, null, 789
};
//act
ExcelPackageHelper.AppendRow(worksheet, row1);
ExcelPackageHelper.AppendRow(worksheet, row2);
List <object> result1 = ExcelPackageHelper.GetRow(worksheet, 1);
List <object> result2 = ExcelPackageHelper.GetRow(worksheet, 2);
//assert
Assert.AreEqual(2, ExcelPackageHelper.CountRows(worksheet));
Assert.AreEqual(Math.Max(row1.Count, row2.Count), ExcelPackageHelper.CountColumns(worksheet));
for (int i = 0; i < row1.Count; i++)
{
Assert.AreEqual(row1[i], result1[i]);
}
for (int i = 0; i < row2.Count; i++)
{
Assert.AreEqual(row2[i], result2[i]);
}
}
private void AddDefaultConfigSheet(ExcelPackage excelPackage)
{
ExcelWorksheet configSheet = ExcelPackageHelper.AddWorksheet(excelPackage, SHEET_NAME);
SetDefaultStatuses();
SetDefaultCategories();
MaxId = DEFAULT_ID;
WriteConfigSheet(configSheet);
}
public void WriteTo(ExcelPackage package)
{
ExcelWorksheet worksheet = ExcelPackageHelper.AddWorksheet(package, GetSheetName());
ColumnLayout.WriteTaskHeaders(worksheet, isActive);
foreach (Task task in Tasks)
{
columnLayout.WriteTask(worksheet, task, isActive);
}
}
public void ExcelPackageHelper_GetWorksheet_Null()
{
//arrange
ExcelPackage package = new ExcelPackage();
string name = "Title";
//act
ExcelWorksheet worksheet = ExcelPackageHelper.GetWorksheet(package, name);
//assert
Assert.AreEqual(null, worksheet);
}
public void ExcelPackageHelper_GetWorksheet()
{
//arrange
ExcelPackage package = new ExcelPackage();
string name = "Title";
ExcelWorksheet worksheetA = ExcelPackageHelper.AddWorksheet(package, name);
//act
ExcelWorksheet worksheetB = ExcelPackageHelper.GetWorksheet(package, name);
//assert
Assert.AreEqual(worksheetA, worksheetB);
}
public TaskSheet(ExcelPackage excelPackage, string name, bool isActive)
{
this.isActive = isActive;
ExcelWorksheet sheet = ExcelPackageHelper.GetWorksheet(excelPackage, name);
columnLayout = (sheet == null) ? new ColumnLayout() : new ColumnLayout(sheet);
if (!columnLayout.ValidLayout)
{
MessageBox.Show("Worksheet layout not recognized. Cannot load tasks.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); //todo this is model level, should just throw exception to higher view level for message display
return;
}
Tasks = LoadTasks(sheet);
}
public ColumnLayout(ExcelWorksheet sheet)
{
IdColumn = ExcelPackageHelper.GetColumnCharForHeader(sheet, ID_HEADER);
DescriptionColumn = ExcelPackageHelper.GetColumnCharForHeader(sheet, DESCRIPTION_HEADER);
if (DescriptionColumn == null)
{
DescriptionColumn = ExcelPackageHelper.GetColumnCharForHeader(sheet, OLD_DESCRIPTION_HEADER);
}
StatusColumn = ExcelPackageHelper.GetColumnCharForHeader(sheet, STATUS_HEADER);
CategoryColumn = ExcelPackageHelper.GetColumnCharForHeader(sheet, CATEGORY_HEADER);
CreateDateColumn = ExcelPackageHelper.GetColumnCharForHeader(sheet, CREATE_DATE_HEADER);
DoneDateColumn = ExcelPackageHelper.GetColumnCharForHeader(sheet, DONE_DATE_HEADER);
}
public void ExcelPackageHelper_AddWorksheet()
{
//arrange
ExcelPackage package = new ExcelPackage();
string name = "Title";
//act
ExcelWorksheet worksheet = ExcelPackageHelper.AddWorksheet(package, name);
//assert
Assert.AreEqual(name, worksheet.Name);
Assert.AreEqual(1, ExcelPackageHelper.CountWorksheets(package));
Assert.AreEqual(0, ExcelPackageHelper.CountRows(worksheet));
Assert.AreEqual(0, ExcelPackageHelper.CountColumns(worksheet));
}
public ConfigSheet(ExcelPackage excelPackage)
{
ExcelWorksheet configSheet = ExcelPackageHelper.GetWorksheet(excelPackage, SHEET_NAME);
if (configSheet == null)
{
AddDefaultConfigSheet(excelPackage);
return;
}
List <object> statuses = ExcelPackageHelper.GetColumnByHeader(configSheet, STATUS_NAME);
List <object> actives = ExcelPackageHelper.GetColumnByHeader(configSheet, ACTIVE_NAME);
if (statuses.Count > 0)
{
Statuses = new List <Status>();
for (int i = 0; i < statuses.Count; i++)
{
bool isActive = true;
if (actives.Count > i)
{
isActive = (actives[i].ToString() == IS_ACTIVE);
}
Statuses.Add(new Status(statuses[i].ToString(), isActive));
}
}
else
{
SetDefaultStatuses();
}
Categories = ExcelPackageHelper.GetColumnByHeader(configSheet, CATEGORY_NAME).Cast <string>().ToList();
if (Categories.Count == 0)
{
SetDefaultCategories();
}
List <object> ids = ExcelPackageHelper.GetColumnByHeader(configSheet, ID_NAME);
if (ids.Count > 0)
{
MaxId = (int)ids[0];
}
else
{
MaxId = DEFAULT_ID;
}
WriteConfigSheet(configSheet); //standardize format
}
public void ExcelPackageHelper_CountWorksheets()
{
//arrange
ExcelPackage package = new ExcelPackage();
string name = "Title";
//act
ExcelPackageHelper.AddWorksheet(package, name + 1);
ExcelPackageHelper.AddWorksheet(package, name + 2);
ExcelPackageHelper.AddWorksheet(package, name + 3);
int count = ExcelPackageHelper.CountWorksheets(package);
//assert
Assert.AreEqual(3, count);
}
private void WriteConfigSheet(ExcelWorksheet worksheet)
{
ExcelPackageHelper.Clear(worksheet);
ExcelPackageHelper.AppendRow(worksheet, new List <object>()
{
STATUS_NAME, ACTIVE_NAME, "", CATEGORY_NAME, "", ID_NAME
});
worksheet.Cells["A1:F1"].Style.Font.Bold = true;
ExcelPackageHelper.SetColumnByChar(worksheet, "A", Statuses.Select(s => (object)s.Name).ToList(), skipFirstRow: true);
ExcelPackageHelper.SetColumnByChar(worksheet, "B", Statuses.Select(s => (object)(s.Active ? IS_ACTIVE : IS_INACTIVE)).ToList(), skipFirstRow: true);
ExcelPackageHelper.SetColumnByChar(worksheet, "D", Categories.Select(c => (object)c).ToList(), skipFirstRow: true);
ExcelPackageHelper.SetColumnByChar(worksheet, "F", new List <object>()
{
(object)MaxId
}, skipFirstRow: true);
}
public void WriteTask(ExcelWorksheet worksheet, Task task, bool active)
{
List <object> values = new List <object>()
{
task.Id,
task.Description,
task.Status,
task.Category,
task.CreateDateString
};
if (!active)
{
values.Add(task.DoneDateString);
}
ExcelPackageHelper.AppendRow(worksheet, values);
}
public static void WriteTaskHeaders(ExcelWorksheet worksheet, bool active)
{
List <object> values = new List <object>()
{
ID_HEADER,
DESCRIPTION_HEADER,
STATUS_HEADER,
CATEGORY_HEADER,
CREATE_DATE_HEADER
};
if (!active)
{
values.Add(DONE_DATE_HEADER);
}
ExcelPackageHelper.AppendRow(worksheet, values);
worksheet.Cells["A1:F1"].Style.Font.Bold = true;
}
public void ExcelPackageHelper_ColumnChar()
{
//arrange
Dictionary <int, string> expectedValues = new Dictionary <int, string>()
{
{ 1, "A" },
{ 2, "B" },
{ 25, "Y" },
{ 26, "Z" },
{ 27, "AA" },
{ 28, "AB" }
};
//act
//assert
foreach (KeyValuePair <int, string> expectedValue in expectedValues)
{
Assert.AreEqual(expectedValue.Value, ExcelPackageHelper.ColumnChar(expectedValue.Key));
}
}
public void ExcelPackageHelper_CountRows_FromRows()
{
//arrange
ExcelPackage package = new ExcelPackage();
string name = "Title";
ExcelWorksheet worksheet = ExcelPackageHelper.AddWorksheet(package, name);
List <string> row = new List <string>()
{
"ColA", "ColB", "ColC"
};
//act
ExcelPackageHelper.AppendRow(worksheet, row);
ExcelPackageHelper.AppendRow(worksheet, row);
ExcelPackageHelper.AppendRow(worksheet, row);
ExcelPackageHelper.AppendRow(worksheet, row);
int count = ExcelPackageHelper.CountRows(worksheet);
//assert
Assert.AreEqual(4, count);
}
public void ExcelPackageHelper_SetColumnByHeader_GetColumnByHeader()
{
//arrange
ExcelPackage package = new ExcelPackage();
string name = "Title";
ExcelWorksheet worksheet = ExcelPackageHelper.AddWorksheet(package, name);
List <string> headers = new List <string>()
{
"ColA", "ColB"
};
List <object> col1 = new List <object>()
{
null, 123, "abc", "", new DateTime(1999, 3, 2)
};
List <object> col2 = new List <object>()
{
456, "", null, null, null, null, 789
};
//act
ExcelPackageHelper.AppendRow(worksheet, headers);
ExcelPackageHelper.SetColumnByHeader(worksheet, headers[0], col1);
ExcelPackageHelper.SetColumnByHeader(worksheet, headers[1], col2);
List <object> result1 = ExcelPackageHelper.GetColumnByHeader(worksheet, headers[0]);
List <object> result2 = ExcelPackageHelper.GetColumnByHeader(worksheet, headers[1]);
//assert
Assert.AreEqual(headers[0], worksheet.Cells["A1"].Value.ToString());
Assert.AreEqual(headers[1], worksheet.Cells["B1"].Value.ToString());
Assert.AreEqual(2, ExcelPackageHelper.CountColumns(worksheet));
Assert.AreEqual(Math.Max(col1.Count, col2.Count) + 1, ExcelPackageHelper.CountRows(worksheet));
for (int i = 0; i < col1.Count; i++)
{
Assert.AreEqual(col1[i], result1[i]);
}
for (int i = 0; i < col2.Count; i++)
{
Assert.AreEqual(col2[i], result2[i]);
}
}
public void ExcelPackageHelper_GetColumnCharForHeader()
{
//arrange
ExcelPackage package = new ExcelPackage();
string name = "Title";
ExcelWorksheet worksheet = ExcelPackageHelper.AddWorksheet(package, name);
List <string> headers = new List <string>()
{
"ColA", "ColB", "ColC"
};
//act
ExcelPackageHelper.AppendRow(worksheet, headers);
string a = ExcelPackageHelper.GetColumnCharForHeader(worksheet, headers[0]);
string b = ExcelPackageHelper.GetColumnCharForHeader(worksheet, headers[1]);
string c = ExcelPackageHelper.GetColumnCharForHeader(worksheet, headers[2]);
string d = ExcelPackageHelper.GetColumnCharForHeader(worksheet, "Other");
//assert
Assert.AreEqual("A", a);
Assert.AreEqual("B", b);
Assert.AreEqual("C", c);
Assert.IsNull(d);
}
public void WriteTo(ExcelPackage package)
{
ExcelWorksheet worksheet = ExcelPackageHelper.AddWorksheet(package, SHEET_NAME);
WriteConfigSheet(worksheet);
}