private string ParseCell(IExcelDataReader reader, int columnToParse)
{
//GetFieldType() returns the type of a value in the current row.Always one of the types supported by Excel:
//double, int, bool, DateTime, TimeSpan, string, or null if there is no value.
var fieldType = reader.GetFieldType(columnToParse);
if (fieldType == null)
{
return(null);
}
string result = null;
if (fieldType == typeof(double))
{
var value = reader.GetDouble(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(int))
{
var value = reader.GetInt32(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(bool))
{
var value = reader.GetBoolean(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(DateTime))
{
var value = reader.GetDateTime(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(TimeSpan))
{
Console.WriteLine("TimeSpan parsing is not implemented");
//var value = reader.GetTimeS(0);
//data[key] = value;
}
else if (fieldType == typeof(string))
{
var value = reader.GetString(columnToParse);
result = value;
}
else
{
Console.WriteLine($"unknown type from Excel field: {0}", fieldType);
}
return(result);
}
private static string CreateInsertQueryFromRow(IExcelDataReader reader, List <string> ListOfHeaders)
{
var values = "";
//reads through each columns
for (int i = 0; i < reader.FieldCount; i++)
{
string value = "";
if (ListOfHeaders.ElementAt(i).Contains(STRING_IDENTIFIER))
{
value = reader.GetString(i);
value = value.Replace("'", "\"");
value = "'" + value + "'";
}
else if (ListOfHeaders.ElementAt(i).Contains(BOOL_IDENTIFIER))
{
value = reader.GetBoolean(i).ToString();
}
else if (ListOfHeaders.ElementAt(i).Contains(INT_IDENTIFIER))
{
value = reader.GetDouble(i).ToString();
if (value.Equals("-1") || LIST_OF_DIRECTION_COLUMNS.Contains(ListOfHeaders.ElementAt(i)))
{
value = "null";
}
}
values += value + ",";
}
if (!string.IsNullOrEmpty(values))
{
values = values.Substring(0, values.Length - 1);
string SqlInsert = SQL_INSERT_BEFORE + reader.Name + SQL_INSERT_MIDDLE + values + SQL_INSERT_END;
return(SqlInsert);
}
return("");
}
private void FillPropertyFromParsedField(IExcelDataReader reader, object newObject, int col, PropertyInfo targetProperty)
{
var fieldType = reader.GetFieldType(col);
if (fieldType == typeof(string))
{
ParseString(reader.GetString(col), newObject, targetProperty);
}
else if (fieldType == typeof(double))
{
ParseDouble(reader.GetDouble(col), newObject, targetProperty);
}
else if (fieldType == typeof(int))
{
ParseInt(reader.GetInt32(col), newObject, targetProperty);
}
else if (fieldType == typeof(bool))
{
ParseBool(reader.GetBoolean(col), newObject, targetProperty);
}
// Only other possibility is that type is null. Do nothing in this case.
}
public bool GetBoolean(int i) => _reader.GetBoolean(i);
public DataTable GetSheetData(IExcelDataReader excelReader)
{
if (excelReader == null)
{
return(null);
}
// Create the table with the spreadsheet name
DataTable table = new DataTable(excelReader.Name);
table.Clear();
string value = null;
bool rowIsEmpty;
while (excelReader.Read())
{
DataRow row = table.NewRow();
rowIsEmpty = true;
for (int i = 0; i < excelReader.FieldCount; i++)
{
// If the column is null and this is the first row, skip
// to next iteration (do not want to include empty columns)
if (excelReader.IsDBNull(i) &&
(excelReader.Depth == 1 || i > table.Columns.Count - 1))
{
continue;
}
//value = excelReader.IsDBNull(i) ? "" : excelReader.GetString(i);
if (excelReader.GetFieldType(i).ToString() == "System.Double")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetDouble(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.Int")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetInt32(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.Bool")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetBoolean(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.DateTime")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetDateTime(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.TimeSpan")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetDateTime(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.String")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetString(i).ToString();
}
if (excelReader.Depth == 0)
{
table.Columns.Add(value);
}
else
{
row[table.Columns[i]] = value;
}
if (!string.IsNullOrEmpty(value))
{
rowIsEmpty = false;
}
}
if (excelReader.Depth != 1 && !rowIsEmpty)
{
table.Rows.Add(row);
}
}
return(table);
}
public IActionResult ReadFile()
{
var files = Request.Form.Files;
int row = 1, totalRows = 0;
var errorList = new List <ErrorListModel>();
var list = new List <Object>();
for (int i = 0; i < files.Count; i++)
{
bool firstRow = false;
var file = files[i];
using (var stream = new MemoryStream())
{
file.CopyTo(stream);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
while (excelReader.Read())
{
int columnNumber = 0;
if (firstRow)
{
totalRows++;
try
{
columnNumber = 1;
var Column1 = excelReader.GetString(0).ToString();
columnNumber = 2;
var Column2 = excelReader.GetString(1).ToString();
columnNumber = 3;
var Column3 = excelReader.GetDouble(2);
columnNumber = 4;
var Column4 = excelReader.GetBoolean(3);
// Run db command here...
// Not recommended for a huge collection
// Or
// Create another collection and submit that to the db
// Using a UDT or XML
list.Add(new { c1 = Column1, c2 = Column2, c3 = Column3, c4 = Column4 });
}
catch (NullReferenceException ex)
{
errorList.Add(new ErrorListModel {
Column = columnNumber, Row = row, Message = $"No Data Found", Exception = ex.Message
});
}
catch (Exception ex)
{
errorList.Add(new ErrorListModel {
Column = columnNumber, Row = row, Message = $"Incorrect Data Type", Exception = ex.Message
});
}
}
else
{
firstRow = true;
}
row++;
}
excelReader.Close();
}
}
if (errorList.Count > 0)
{
return(View("Error", errorList));
}
return(View("Success", list));
}