public static void Main(string[] args)
{
Console.WriteLine("Starting Retrieving data from Excel...");
Console.WriteLine("");
// Grab file and convert to byte array
string filePath = @"C:\TestFile.xlsx";
byte[] bytes = System.IO.File.ReadAllBytes(filePath);
var importFileData = new ImportFileData
{
FileData = bytes,
FileName = "Test File"
};
var modelState = new ModelStateDictionary();
var import = ImportService.ImportFile(importFileData, modelState);
Console.WriteLine(import);
Console.WriteLine("");
Console.WriteLine("Press any key to exit...");
Console.ReadKey();
}
public static async Task <ImportResult> ImportFile(ImportFileData importData, ModelStateDictionary modelState)
{
if ((importData.FileData.Length / 1024.0F / 1024.0F) > 1.1f)
{
AddModelError(
modelState,
"File exceeds maximum size of 1MB",
0
);
return(null);
}
#region Variable Initialization
IList <ImportRow> rows;
IList <ComicBookInventory> newComicBookInventory = new List <ComicBookInventory>();
var expectedColumns = new[]
{
"issuenumber",
"title",
"description",
"flag",
"datepublished",
};
#endregion
using (var document = ExcelParsingUtil.OpenDocumentFromByteArray(importData.FileData, true))
{
var worksheetPart = ExcelParsingUtil.GetWorksheetPartFromDocument(document, "");
if (!ExcelParsingUtil.HasAnyRows(worksheetPart))
{
AddModelError(
modelState,
"File is empty",
0
);
return(null);
}
#region Data Converter
ImportRow DataConverter(IList <string> rowData, IList <string> columnNames,
int rowNumber)
{
if (expectedColumns.Except(columnNames).Any())
{
var exception = new Exception("Missing expected columns in data");
exception.Data.Add("rowNumber", rowNumber);
throw exception;
}
var importRow = new ImportRow
{
IssueNumber = rowData[columnNames.IndexOf("issuenumber")].Trim(),
Title = rowData[columnNames.IndexOf("title")].Trim(),
Description = rowData[columnNames.IndexOf("description")].Trim(),
Flag = rowData[columnNames.IndexOf("flag")].Trim(),
DatePublished = DateTime.FromOADate(double.Parse(rowData[columnNames.IndexOf("datepublished")].Trim())),
ItemGuid = Guid.NewGuid(),
RowNumber = rowNumber,
};
ValidateImportRow(importRow, modelState);
return(importRow);
}
#endregion
#region Parse File and Validate
try
{
rows = ExcelParsingUtil.GetDataToList(
document,
DataConverter
);
}
catch (Exception e)
{
var rowNumber = 0;
if (e.Data.Keys.Count > 0)
{
int.TryParse(e.Data["rowNumber"].ToString(), out rowNumber);
}
AddModelError(
modelState,
e.Message,
rowNumber
);
return(null);
}
if (!rows.Any())
{
AddModelError(
modelState,
"No rows",
0
);
return(null);
}
if (TooManyErrors(modelState))
{
return(null);
}
#endregion
#region Loop Rows for each Sample Result for Review
foreach (var row in rows)
{
if (row == null)
{
continue;
}
try
{
ComicBookInventory currentComicBookInventory = null;
currentComicBookInventory = ProcessOneComicBook(row);
if (modelState.ErrorCount == 0)
{
newComicBookInventory.Add(currentComicBookInventory);
}
}
catch (Exception e)
{
AddModelError(
modelState,
e.Message,
row.RowNumber
);
}
if (!TooManyErrors(modelState))
{
continue;
}
break;
}
if (modelState.ErrorCount > 0)
{
return(null);
}
#endregion
#region Persist Sample Results for Review objects
// Using a repository save these in a database
// await repository.AddMultiple(newSampleResultReview);
// await DbContext.SaveChangesAsync();
#endregion
}
#region Create Import Stats for Front-end
var importStats = new ImportResult
{
NumberOfRows = rows.Count,
FileName = importData.FileName
};
#endregion
return(importStats);
}
