private IEnumerable <int> GetRowIndexesContainingDates(
IWorksheetReader worksheetReader,
ExcelCellCoordinates firstDateCellCoordinates,
int maxNumberOfEmptyRowsInBetween)
{
var firstDatesRowIndex = firstDateCellCoordinates.RowIndex;
var datesColumnIndex = firstDateCellCoordinates.ColumnIndex;
var worksheetIndex = firstDateCellCoordinates.WorksheetIndex;
var rowNumbersContainingDates = new HashSet <int>();
for (int currentRowIndex = firstDatesRowIndex; ; currentRowIndex++)
{
var currentRowCoordinates = new ExcelCellCoordinates(worksheetIndex, currentRowIndex, datesColumnIndex);
var nextRowsCoordinates = GetCellCoordinatesForNextNRows(currentRowCoordinates, n: maxNumberOfEmptyRowsInBetween);
var nextRowIndexesContainingDates = GetNextRowIndexesContainingDates(worksheetReader, nextRowsCoordinates);
if (nextRowIndexesContainingDates.Count() == 0)
{
break;
}
rowNumbersContainingDates.UnionWith(nextRowIndexesContainingDates.ToHashSet());
}
return(rowNumbersContainingDates);
}
private IEnumerable <int> GetNextRowIndexesContainingDates(
IWorksheetReader worksheetReader,
IEnumerable <ExcelCellCoordinates> nextRowsCoordinatesCollection)
{
if (nextRowsCoordinatesCollection.Count() == 0)
{
return(new int[0]);
}
var worksheetIndex = nextRowsCoordinatesCollection.First().WorksheetIndex;
var datesColumnIndex = nextRowsCoordinatesCollection.First().ColumnIndex;
return(nextRowsCoordinatesCollection
.Select(coordinates => coordinates.RowIndex)
.Where(rowIndex =>
{
try
{
var nextRowCoordinates = new ExcelCellCoordinates(worksheetIndex, rowIndex, datesColumnIndex);
return worksheetReader.GetCellDateTimeAsUTC(nextRowCoordinates) != default(DateTime);
}
catch
{
return false;
}
})
.ToArray());
}
public void CellReadingStringDataTest(int worksheetIndex, int rowIndex, int columnIndex, string expectedCellValue)
{
IWorksheetReader worksheetReader = new WorksheetReader(filepath);
var cellCoordinates = new ExcelCellCoordinates(worksheetIndex, rowIndex, columnIndex);
var cellValue = worksheetReader.GetCellText(cellCoordinates);
cellValue
.Should()
.Be(expectedCellValue);
}
private ExcelRange GetCellAtCoordinatesFrom(ExcelWorksheets worksheets, ExcelCellCoordinates cellCoodrinates)
{
ThrowErrorIfCoorinatesAreIncorrect(cellCoodrinates, worksheets);
var worksheetIndex = cellCoodrinates.WorksheetIndex;
var rowIndex = cellCoodrinates.RowIndex;
var columnIndex = cellCoodrinates.ColumnIndex;
var cellAtCoordinates = worksheets[worksheetIndex].Cells[rowIndex, columnIndex];
return(cellAtCoordinates);
}
public void TryingToGetCellDataFromIncorrectColumnIndexThrowsError(int columnIndex, Action <ExcelCellCoordinates> getCellDataAction)
{
IWorksheetReader worksheetReader = new WorksheetReader(filepath);
var coordinates = new ExcelCellCoordinates(worksheetIndex: 1, rowIndex: 1, columnIndex: columnIndex);
Action getCellTextAction = () => getCellDataAction(coordinates);
getCellTextAction
.Should()
.ThrowExactly <WorksheetReaderColumnLesserThanOneException>("column index must be larger than or equal 1")
.WithMessage($"WorksheetReader tried to read column at index: {columnIndex}");
}
public void TryingToGetCellDataFromNonExistingWorksheetThrowsError(int worksheetIndex, Action <ExcelCellCoordinates> getCellDataAction)
{
IWorksheetReader worksheetReader = new WorksheetReader(filepath);
var coordinates = new ExcelCellCoordinates(worksheetIndex: worksheetIndex, rowIndex: 1, columnIndex: 1);
Action getCellTextAction = () => getCellDataAction(coordinates);
getCellTextAction
.Should()
.ThrowExactly <WorksheetReaderNonExistingWorksheetException>("worksheet index cannot be less than zero or higher than worksheets count")
.WithMessage($"There is no worksheet at index: {worksheetIndex}");
}
public void TryingReadingDateTimeFromCellWithAnotherDataTypeTest(int worksheetIndex, int rowIndex, int columnIndex)
{
IWorksheetReader worksheetReader = new WorksheetReader(filepath);
var cellCoordinates = new ExcelCellCoordinates(worksheetIndex, rowIndex, columnIndex);
Action getCellDateTimeAction = () => worksheetReader.GetCellDateTimeAsUTC(cellCoordinates);
getCellDateTimeAction
.Should()
.ThrowExactly <WorksheetReaderCellValueTypeException>("trying to read cell with non-dateTime format should raise an exception")
.WithMessage($"*worksheet index: {cellCoordinates.WorksheetIndex},* " +
$"*row index: {cellCoordinates.RowIndex},*" +
$"*column index {cellCoordinates.ColumnIndex}*" +
$"*Expected type of cell's value: {typeof(DateTime)}*");
}
private IEnumerable <ExcelCellCoordinates> GetCellCoordinatesForNextNRows(ExcelCellCoordinates initialCoordinates, int n)
{
var firstRowIndex = initialCoordinates.RowIndex;
var worksheetIndex = initialCoordinates.WorksheetIndex;
var columnIndex = initialCoordinates.ColumnIndex;
var excelCellCoordinates = new List <ExcelCellCoordinates>(n);
for (int rowIndex = firstRowIndex; rowIndex < firstRowIndex + n; rowIndex++)
{
var newCellCoordinate = new ExcelCellCoordinates(worksheetIndex, rowIndex, columnIndex);
excelCellCoordinates.Add(newCellCoordinate);
}
return(excelCellCoordinates);
}
public IEnumerable <Spending> ParseData()
{
var datesRowNumbers = new List <Spending>();
var worksheetsCount = worksheetReader.Worksheets.Count();
for (int worksheetIndex = 0; worksheetIndex < worksheetsCount; worksheetIndex++)
{
var firstDateCellCoordinates = new ExcelCellCoordinates(worksheetIndex, firstDateRowIndex, datesColumnIndex);
var rowIndexesContainingDates = GetRowIndexesContainingDates(
worksheetReader,
firstDateCellCoordinates,
maxNumberOfEmptyRowsInBetween);
for (int rowIndex = firstDateRowIndex; rowIndex < firstDateRowIndex + rowIndexesContainingDates.Count(); rowIndex++)
{
datesRowNumbers.Add(new Spending());
}
}
return(datesRowNumbers);
}
public string GetCellText(ExcelCellCoordinates cellCoodrinates)
{
using (ExcelPackage package = new ExcelPackage(existingSpreadsheet))
{
var worksheets = package.Workbook.Worksheets;
var cell = GetCellAtCoordinatesFrom(worksheets, cellCoodrinates);
if (CellNumericFormatEqualsGeneral(cell) == false &&
CellAtCoordinatesContainsDateTime(cellCoodrinates) == false)
{
return(cell.Text);
}
if (CellAtCoordinatesContainsDateTime(cellCoodrinates))
{
var dateTimeUTC = GetCellDateTimeAsUTC(cellCoodrinates);
return(dateTimeUTC.ToString(dateTimeFormat, cultureInfo));
}
return(cell.GetValue <string>() ?? "");
}
}
public DateTime GetCellDateTimeAsUTC(ExcelCellCoordinates cellCoodrinates)
{
using (ExcelPackage package = new ExcelPackage(existingSpreadsheet))
{
var worksheets = package.Workbook.Worksheets;
var cell = GetCellAtCoordinatesFrom(worksheets, cellCoodrinates);
if (CellAtCoordinatesContainsDateTime(cellCoodrinates) == false)
{
throw new WorksheetReaderCellValueTypeException(cell.Text, cellCoodrinates, typeof(DateTime));
}
try
{
var dateLocal = DateTime.SpecifyKind(cell.GetValue <DateTime>(), DateTimeKind.Utc);
return(dateLocal);
}
catch (FormatException e)
{
throw new WorksheetReaderCellValueTypeException(cell.Text, cellCoodrinates, typeof(DateTime), e);
}
}
}
public WorksheetReaderCellValueTypeException(
string cellText, ExcelCellCoordinates cellCoordinates, Type expectedType, Exception inner) :
base(exceptionSpecificMessage(cellText, cellCoordinates, expectedType), inner)
{
}
private static string exceptionSpecificMessage(string cellText, ExcelCellCoordinates cellCoordinates, Type expectedType) =>
$"WorksheetReader tried to read row form worksheet index: {cellCoordinates.WorksheetIndex}, " +
$"row index: {cellCoordinates.RowIndex}, column index {cellCoordinates.ColumnIndex}\n" +
$"Expected type of cell's value: {expectedType}\n" +
$"Cell's text: {cellText}";
private void ThrowErrorIfCoorinatesAreIncorrect(ExcelCellCoordinates cellCoodrinates, ExcelWorksheets worksheets)
{
ThrowErrorIfRowIsLesserThanZero(cellCoodrinates.RowIndex);
ThrowErrorIfColumnIsLesserThanZero(cellCoodrinates.ColumnIndex);
ThrowErrorIfTriedToReadNonExistentWorksheet(cellCoodrinates.WorksheetIndex, worksheets.Count);
}
public ExcelSheetValue(ExcelCellCoordinates coordinates, string value)
{
Coordinates = coordinates;
Value = value;
}
public ExcelSheetValue(ExcelCellCoordinates coordinates, string value)
{
Coordinates = coordinates;
Value = value;
}
public bool CellAtCoordinatesContainsDateTime(ExcelCellCoordinates cellCoordinates)
{
using (ExcelPackage package = new ExcelPackage(existingSpreadsheet))
{
int worksheetIndex = cellCoordinates.WorksheetIndex;
int rowIndex = cellCoordinates.RowIndex;
int columnIndex = cellCoordinates.ColumnIndex;
var cell = package.Workbook.Worksheets[worksheetIndex].Cells[rowIndex, columnIndex];
var cellStringValue = cell.GetValue <string>();
var cellText = cell.Text;
if (cellStringValue == null && cellText == "")
{
return(false);
}
cellStringValue = cellStringValue.Replace(".", "");
cellText = cellText.Replace(".", "");
try
{
var dateLocal = DateTime.SpecifyKind(cell.GetValue <DateTime>(), DateTimeKind.Utc);
var cellDateTimeValue = cell.GetValue <DateTime>();
if (cellStringValue == cellText)
{
return(true);
}
var isCellStringValueOfDateTimeType = DateTime.TryParse(
cellStringValue,
cultureInfo,
DateTimeStyles.AssumeUniversal,
out DateTime datetimeStringValueParsedData);
var isCellTextOfDateTimeType = DateTime.TryParse(
cellText,
cultureInfo,
DateTimeStyles.AssumeLocal,
out DateTime datetimeTextParsedData);
bool cellValueAndCellTextAreNotDateTimeType = isCellStringValueOfDateTimeType == false &&
isCellTextOfDateTimeType == false;
if (cellValueAndCellTextAreNotDateTimeType)
{
return(false);
}
if ((cellStringValue.All(char.IsDigit) && datetimeTextParsedData == cellDateTimeValue) ||
BothDatesHaveSameSumOfYearMonthAndDay(datetimeTextParsedData, cellDateTimeValue))
{
return(true);
}
return(false);
}
catch
{
return(false);
}
}
}
