public void DataReader_NextResult_Test()
{
IExcelDataReader r = ExcelReaderFactory.CreateOpenXmlReader(Helper.GetTestWorkbook("xTestMultiSheet"));
Assert.AreEqual(3, r.ResultsCount);
var table = new DataTable();
table.Columns.Add("c1", typeof(int));
table.Columns.Add("c2", typeof(int));
table.Columns.Add("c3", typeof(int));
table.Columns.Add("c4", typeof(int));
int fieldCount = -1;
while (r.Read())
{
fieldCount = r.FieldCount;
table.Rows.Add(r.GetInt32(0), r.GetInt32(1), r.GetInt32(2), r.GetInt32(3));
}
Assert.AreEqual(12, table.Rows.Count);
Assert.AreEqual(4, fieldCount);
Assert.AreEqual(1, table.Rows[11][3]);
r.NextResult();
table.Rows.Clear();
table.TableName = r.Name;
while (r.Read())
{
fieldCount = r.FieldCount;
table.Rows.Add(r.GetInt32(0), r.GetInt32(1), r.GetInt32(2), r.GetInt32(3));
}
Assert.AreEqual(12, table.Rows.Count);
Assert.AreEqual(4, fieldCount);
Assert.AreEqual(2, table.Rows[11][3]);
r.NextResult();
table.TableName = r.Name;
table.Rows.Clear();
while (r.Read())
{
fieldCount = r.FieldCount;
table.Rows.Add(r.GetInt32(0), r.GetInt32(1));
}
Assert.AreEqual(5, table.Rows.Count);
Assert.AreEqual(2, fieldCount);
Assert.AreEqual(3, table.Rows[4][1]);
Assert.AreEqual(false, r.NextResult());
r.Close();
}
public void Handle(StartBatchUpload message)
{
Log.Info("Received StartBatchUpload with batch id: {0}, path: {1}", message.BatchId, message.BatchSourcePath);
FileInfo info = new FileInfo(message.BatchSourcePath);
if (info == null)
{
return;
}
using (FileStream stream = new FileStream(info.FullName, FileMode.Open, FileAccess.Read))
{
//ES - ResultsCount isn't reliable on ExcelDataReader, so do a
//pass through the results to get the count:
int registrationCount = 0;
using (IExcelDataReader reader = GetDataReader(info, stream))
{
if (!IsValid(reader))
{
SendErrorNotification(message);
return;
}
while (reader.Read())
{
registrationCount++;
}
}
//index is 1-based as may be published:
int registrationIndex = 1;
using (IExcelDataReader reader = GetDataReader(info, stream))
{
while (reader.Read())
{
//specify the queue so we can use BizTalk as a replacement subscriber:
Bus.Send <AddProduct>("ExcelUpload.AddProductService.1.InputQueue", m =>
{
m.BatchId = message.BatchId;
m.BatchSourcePath = message.BatchSourcePath;
m.RegistrationsInBatch = registrationCount;
m.RegistrationIndex = registrationIndex++;
m.OriginatorDestination = Bus.SourceOfMessageBeingHandled;
m.Name = (string)reader.GetString(0);
m.ProductNumber = (string)reader.GetString(1);
m.SafetyStockLevel = (int)reader.GetInt32(2);
m.ReorderPoint = (int)reader.GetInt32(3);
m.StandardCost = (decimal)reader.GetDecimal(4);
m.ListPrice = (decimal)reader.GetDecimal(5);
m.DaysToManufacture = (int)reader.GetInt32(6);
m.SellStartDate = DateTime.FromOADate(reader.GetDouble(7));
});
}
}
stream.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
const string FilePath = @"C:\mongodb\bin\TrafficStopsSample.xls";
FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream))
{
//...
//////2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
////IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//////3. DataSet - The result of each spreadsheet will be created in the result.Tables
////DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
////excelReader.IsFirstRowAsColumnNames = true;
////DataSet result = excelReader.AsDataSet();
//this.textBox1.Text = string.Format("Rows imported: {0}", result.Tables[0].Rows.Count);
int i = 0;
//////5. Data Reader methods
var stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
excelReader.Read(); //to get past header row
while (excelReader.Read())
{
var data = new CaryData
{
Id = i++,
CAD_Call = excelReader.GetString(0),
Call_Type = excelReader.GetString(1),
Address = excelReader.GetString(2),
Dt = new DateTime(2012, excelReader.GetInt32(5), excelReader.GetInt32(6)),
Time = excelReader.GetString(4),
Month = Int32.Parse(excelReader.GetString(5)),
Day = Int32.Parse(excelReader.GetString(6)),
Disposition = excelReader.GetString(7),
Streetno = excelReader.GetString(8),
Streetonly = excelReader.GetString(9),
Location = new[] { excelReader.GetDouble(10), excelReader.GetDouble(11) }
};
var col = database.GetCollection <CaryData>("trafficstops");
col.Save(data);
}
stopwatch.Stop();
this.textBox1.Text = "Done! Operation took " + stopwatch.Elapsed.TotalSeconds + " seconds.";
}
//6. Free resources (IExcelDataReader is IDisposable)
//excelReader.Close();
}
public static float GetFloat(int i)
{
var type = reader.GetFieldType(i);
if (type == typeof(double))
{
return (float)reader.GetDouble(i);
}
if (type == typeof(float))
{
return reader.GetFloat(i);
}
if (type == typeof(int))
{
return reader.GetInt32(i);
}
if (float.TryParse(reader.GetValue(i)?.ToString(), out float value))
{
return value;
}
throw Exception("Can't parse number from column " + (i + 1));
}
private int Integerize(IExcelDataReader reader, int position)
{
int result = 0;
var fileType = reader.GetFieldType(position);
if (fileType == null)
{
return(0);
}
if (fileType.Equals(typeof(System.Int32)))
{
return(reader.GetInt32(position));
}
if (fileType.Equals(typeof(System.Double)))
{
return(Convert.ToInt32(reader.GetDouble(position)));
}
if (fileType.Equals(typeof(System.String)))
{
int.TryParse(reader.GetString(position), out result);
return(result);
}
return(0);
}
private static HoldingList ReadHoldingList(IExcelDataReader reader, Book book, int target)
{
HoldingList holdingList = new HoldingList();
holdingList.LibraryId = DataRepository.Library.GetName(Constant.TargetLibraries[target]).LibraryId;
holdingList.BookId = DataRepository.Book.GetbyISBN(book.ISBN).BookId;
holdingList.Count = reader.GetInt32(10);
holdingList.ReceiptDate = reader.GetString(12).CleanNULL();
holdingList.Classification = book.KDCId == "K1000" ? true : false;
return(holdingList);
}
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);
}
public void DataReader_Read_Test()
{
IExcelDataReader r =
ExcelReaderFactory.CreateOpenXmlReader(Helper.GetTestWorkbook("xTest_num_double_date_bool_string"));
var table = new DataTable();
table.Columns.Add(new DataColumn("num_col", typeof(int)));
table.Columns.Add(new DataColumn("double_col", typeof(double)));
table.Columns.Add(new DataColumn("date_col", typeof(DateTime)));
table.Columns.Add(new DataColumn("boo_col", typeof(bool)));
int fieldCount = -1;
while (r.Read())
{
fieldCount = r.FieldCount;
table.Rows.Add(r.GetInt32(0), r.GetDouble(1), r.GetDateTime(2), r.IsDBNull(4));
}
r.Close();
Assert.AreEqual(6, fieldCount);
Assert.AreEqual(30, table.Rows.Count);
Assert.AreEqual(1, int.Parse(table.Rows[0][0].ToString()));
Assert.AreEqual(1346269, int.Parse(table.Rows[29][0].ToString()));
//double + Formula
Assert.AreEqual(1.02, double.Parse(table.Rows[0][1].ToString()));
Assert.AreEqual(4.08, double.Parse(table.Rows[2][1].ToString()));
Assert.AreEqual(547608330.24, double.Parse(table.Rows[29][1].ToString()));
//Date + Formula
Assert.AreEqual(new DateTime(2009, 5, 11).ToShortDateString(),
DateTime.Parse(table.Rows[0][2].ToString()).ToShortDateString());
Assert.AreEqual(new DateTime(2009, 11, 30).ToShortDateString(),
DateTime.Parse(table.Rows[29][2].ToString()).ToShortDateString());
}
private void GetQarterlyDeals(string filePath, object[,] table)
{
FileStream stream = System.IO.File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
var isFirstRow = true;
while (excelReader.Read())
{
if (isFirstRow)
{
isFirstRow = false;
continue;
}
var value = excelReader.GetDecimal(7);
if (value < 100)
{
continue;
}
var numAdvisors = excelReader.GetInt32(12);
if (numAdvisors < 1)
{
continue;
}
var date = excelReader.GetDateTime(0);
var quarter = GetQuarter(date);
var tableRow = (date.Year - 2009) * quarter;
var tableCol = GetValueColumn(value);
((List <int>)table[tableRow, tableCol]).Add(numAdvisors);
}
excelReader.Close();
excelReader.Dispose();
}
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 void Issue_4031_NullColumn()
{
IExcelDataReader excelReader =
ExcelReaderFactory.CreateOpenXmlReader(Helper.GetTestWorkbook("xTest_Issue_4031_NullColumn"));
//DataSet dataSet = excelReader.AsDataSet(true);
excelReader.Read();
Assert.IsNull(excelReader.GetValue(0));
Assert.AreEqual("a", excelReader.GetString(1));
Assert.AreEqual("b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetValue(3));
Assert.AreEqual("d", excelReader.GetString(4));
excelReader.Read();
Assert.IsNull(excelReader.GetValue(0));
Assert.IsNull(excelReader.GetValue(1));
Assert.AreEqual("Test", excelReader.GetString(2));
Assert.IsNull(excelReader.GetValue(3));
Assert.AreEqual(1, excelReader.GetInt32(4));
excelReader.Close();
}
public VendorProduct GetNextVendorProduct()
{
var product = new VendorProduct();
try
{
if (!_excelReader.Read() || _excelReader.GetString((int)_task.SKU) == null)
{
return(null);
}
product.VendorId = _task.VendorId;
product.SupplierSKU = _excelReader.GetString((int)_task.SKU);
product.SupplierPrice = _task.SupplierPrice == null ? -1 : _excelReader.GetDecimal(_task.SupplierPrice ?? 0);
product.Quantity = _task.Quantity == null ? -1 : _excelReader.GetInt16(_task.Quantity ?? 0);
product.Name = _task.ProductName == null ? null : _excelReader.GetString(_task.ProductName ?? 0);
product.Description = _task.Description == null ? null : _excelReader.GetString(_task.Description ?? 0);
product.Category = _task.Category == null ? null : _excelReader.GetString(_task.Category ?? 0);
product.UPC = _task.UPC == null ? null : _excelReader.GetString(_task.UPC ?? 0);
product.MinPack = _task.MinPack == null ? -1 : _excelReader.GetInt32(_task.MinPack ?? 0);
// set the flag to true
product.HasInvalidData = false;
}
catch (Exception ex)
{
product.HasInvalidData = true;
Logger.LogError(LogEntryType.FileInventoryTaskService, string.Format("Error in parsing vendor product file {0} at row number: {1}", _fileInfo.FullName, _records), ex.StackTrace);
}
finally
{
// increment the record counter
_records++;
}
return(product);
}
public static void DoOpenOfficeTest(IExcelDataReader excelReader)
{
Assert.IsTrue(excelReader.IsValid);
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual("column a", excelReader.GetString(0));
Assert.AreEqual(" column b", excelReader.GetString(1));
Assert.AreEqual(" column b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetString(3));
Assert.AreEqual("column e", excelReader.GetString(4));
Assert.AreEqual(" column b", excelReader.GetString(5));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(2, excelReader.GetInt32(0));
Assert.AreEqual("b", excelReader.GetString(1));
Assert.AreEqual("c", excelReader.GetString(2));
Assert.AreEqual("d", excelReader.GetString(3));
Assert.AreEqual(" e ", excelReader.GetString(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(3, excelReader.GetInt32(0));
Assert.AreEqual(2, excelReader.GetInt32(1));
Assert.AreEqual(3, excelReader.GetInt32(2));
Assert.AreEqual(4, excelReader.GetInt32(3));
Assert.AreEqual(5, excelReader.GetInt32(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(4, excelReader.GetInt32(0));
Assert.AreEqual(new DateTime(2012, 10, 13), excelReader.GetDateTime(1));
Assert.AreEqual(new DateTime(2012, 10, 14), excelReader.GetDateTime(2));
Assert.AreEqual(new DateTime(2012, 10, 15), excelReader.GetDateTime(3));
Assert.AreEqual(new DateTime(2012, 10, 16), excelReader.GetDateTime(4));
for (int i = 4; i < 34; i++)
{
excelReader.Read();
Assert.AreEqual(i + 1, excelReader.GetInt32(0));
Assert.AreEqual(i + 2, excelReader.GetInt32(1));
Assert.AreEqual(i + 3, excelReader.GetInt32(2));
Assert.AreEqual(i + 4, excelReader.GetInt32(3));
Assert.AreEqual(i + 5, excelReader.GetInt32(4));
}
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
//test dataset
DataSet result = excelReader.AsDataSet(true);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual(6, result.Tables[0].Columns.Count);
Assert.AreEqual(33, result.Tables[0].Rows.Count);
Assert.AreEqual("column a", result.Tables[0].Columns[0].ColumnName);
Assert.AreEqual(" column b", result.Tables[0].Columns[1].ColumnName);
Assert.AreEqual(" column b_1", result.Tables[0].Columns[2].ColumnName);
Assert.AreEqual("Column3", result.Tables[0].Columns[3].ColumnName);
Assert.AreEqual("column e", result.Tables[0].Columns[4].ColumnName);
Assert.AreEqual(" column b_2", result.Tables[0].Columns[5].ColumnName);
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[0][0]));
Assert.AreEqual("b", result.Tables[0].Rows[0][1]);
Assert.AreEqual("c", result.Tables[0].Rows[0][2]);
Assert.AreEqual("d", result.Tables[0].Rows[0][3]);
Assert.AreEqual(" e ", result.Tables[0].Rows[0][4]);
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][0]));
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[1][1]));
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][2]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[1][3]));
Assert.AreEqual(5, Convert.ToInt32(result.Tables[0].Rows[1][4]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[2][0]));
Assert.AreEqual(new DateTime(2012, 10, 13), result.Tables[0].Rows[2][1]);
Assert.AreEqual(new DateTime(2012, 10, 14), result.Tables[0].Rows[2][2]);
Assert.AreEqual(new DateTime(2012, 10, 15), result.Tables[0].Rows[2][3]);
Assert.AreEqual(new DateTime(2012, 10, 16), result.Tables[0].Rows[2][4]);
for (int i = 4; i < 33; i++)
{
Assert.AreEqual(i + 2, Convert.ToInt32(result.Tables[0].Rows[i][0]));
Assert.AreEqual(i + 3, Convert.ToInt32(result.Tables[0].Rows[i][1]));
Assert.AreEqual(i + 4, Convert.ToInt32(result.Tables[0].Rows[i][2]));
Assert.AreEqual(i + 5, Convert.ToInt32(result.Tables[0].Rows[i][3]));
Assert.AreEqual(i + 6, Convert.ToInt32(result.Tables[0].Rows[i][4]));
}
excelReader.Close();
}
public int GetInt32(int i) => _reader.GetInt32(i);
public static void DoOpenOfficeTest(IExcelDataReader excelReader)
{
Assert.IsTrue(excelReader.IsValid);
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual("column a", excelReader.GetString(0));
Assert.AreEqual(" column b", excelReader.GetString(1));
Assert.AreEqual(" column b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetString(3));
Assert.AreEqual("column e", excelReader.GetString(4));
Assert.AreEqual(" column b", excelReader.GetString(5));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(2, excelReader.GetInt32(0));
Assert.AreEqual("b", excelReader.GetString(1));
Assert.AreEqual("c", excelReader.GetString(2));
Assert.AreEqual("d", excelReader.GetString(3));
Assert.AreEqual(" e ", excelReader.GetString(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(3, excelReader.GetInt32(0));
Assert.AreEqual(2, excelReader.GetInt32(1));
Assert.AreEqual(3, excelReader.GetInt32(2));
Assert.AreEqual(4, excelReader.GetInt32(3));
Assert.AreEqual(5, excelReader.GetInt32(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(4, excelReader.GetInt32(0));
Assert.AreEqual(new DateTime(2012, 10, 13), excelReader.GetDateTime(1));
Assert.AreEqual(new DateTime(2012, 10, 14), excelReader.GetDateTime(2));
Assert.AreEqual(new DateTime(2012, 10, 15), excelReader.GetDateTime(3));
Assert.AreEqual(new DateTime(2012, 10, 16), excelReader.GetDateTime(4));
for (int i = 4; i < 34; i++)
{
excelReader.Read();
Assert.AreEqual(i + 1, excelReader.GetInt32(0));
Assert.AreEqual(i + 2, excelReader.GetInt32(1));
Assert.AreEqual(i + 3, excelReader.GetInt32(2));
Assert.AreEqual(i + 4, excelReader.GetInt32(3));
Assert.AreEqual(i + 5, excelReader.GetInt32(4));
}
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
//test dataset
DataSet result = excelReader.AsDataSet(true);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual(6, result.Tables[0].Columns.Count);
Assert.AreEqual(33, result.Tables[0].Rows.Count);
Assert.AreEqual("column a", result.Tables[0].Columns[0].ColumnName);
Assert.AreEqual(" column b", result.Tables[0].Columns[1].ColumnName);
Assert.AreEqual(" column b_1", result.Tables[0].Columns[2].ColumnName);
Assert.AreEqual("Column3", result.Tables[0].Columns[3].ColumnName);
Assert.AreEqual("column e", result.Tables[0].Columns[4].ColumnName);
Assert.AreEqual(" column b_2", result.Tables[0].Columns[5].ColumnName);
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[0][0]));
Assert.AreEqual("b", result.Tables[0].Rows[0][1]);
Assert.AreEqual("c", result.Tables[0].Rows[0][2]);
Assert.AreEqual("d", result.Tables[0].Rows[0][3]);
Assert.AreEqual(" e ", result.Tables[0].Rows[0][4]);
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][0]));
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[1][1]));
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][2]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[1][3]));
Assert.AreEqual(5, Convert.ToInt32(result.Tables[0].Rows[1][4]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[2][0]));
Assert.AreEqual(new DateTime(2012, 10, 13), result.Tables[0].Rows[2][1]);
Assert.AreEqual(new DateTime(2012, 10, 14), result.Tables[0].Rows[2][2]);
Assert.AreEqual(new DateTime(2012, 10, 15), result.Tables[0].Rows[2][3]);
Assert.AreEqual(new DateTime(2012, 10, 16), result.Tables[0].Rows[2][4]);
for (int i = 4; i < 33; i++)
{
Assert.AreEqual(i + 2, Convert.ToInt32(result.Tables[0].Rows[i][0]));
Assert.AreEqual(i + 3, Convert.ToInt32(result.Tables[0].Rows[i][1]));
Assert.AreEqual(i + 4, Convert.ToInt32(result.Tables[0].Rows[i][2]));
Assert.AreEqual(i + 5, Convert.ToInt32(result.Tables[0].Rows[i][3]));
Assert.AreEqual(i + 6, Convert.ToInt32(result.Tables[0].Rows[i][4]));
}
excelReader.Close();
}
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 static ImportDataModel GetImportModels(string path)
{
try
{
var model = new ImportDataModel();
var stream = File.OpenRead(path);
IExcelDataReader excelReader = null;
//Reading from a binary Excel file ('97-2003 format; *.xls)
if (Path.GetExtension(path).Equals(".xls", StringComparison.InvariantCultureIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
//Reading from a OpenXml Excel file (2007 format; *.xlsx)
if (Path.GetExtension(path).Equals(".xlsx", StringComparison.InvariantCultureIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (excelReader == null)
{
return(model);
}
var pageIndex = 0;
do
{
pageIndex++;
var items = new List <ImportModel>();
var category = (CategoryCode)pageIndex;
var rowIndex = 0;
while (excelReader.Read())
{
rowIndex++;
if (rowIndex < StartRowIndex)
{
continue;
}
if (excelReader.IsDBNull(3))
{
continue;
}
var item = new ImportModel();
item.V1 = excelReader.IsDBNull(0) ? (int?)null : excelReader.GetInt32(0);
item.V2 = excelReader.GetString(1);
item.V3 = excelReader.GetString(2);
item.V4 = excelReader.GetString(3);
item.V5 = excelReader.GetString(4);
item.V6 = excelReader.GetString(5);
item.V7 = excelReader.GetString(6);
item.V8 = excelReader.GetString(7);
item.V9 = excelReader.GetString(8);
item.V10 = excelReader.GetString(9);
item.V11 = excelReader.IsDBNull(10) ? (DateTime?)null : excelReader.GetDateTime(10);
item.V12 = excelReader.IsDBNull(11) ? (double?)null : excelReader.GetDouble(11);
item.V13 = excelReader.IsDBNull(12) ? (double?)null : excelReader.GetDouble(12);
item.V14 = excelReader.IsDBNull(13) ? (double?)null : excelReader.GetDouble(13);
item.V15 = excelReader.IsDBNull(14) ? (double?)null : excelReader.GetDouble(14);
item.V16 = excelReader.IsDBNull(15) ? (double?)null : excelReader.GetDouble(15);
item.V17 = excelReader.IsDBNull(16) ? (double?)null : excelReader.GetDouble(16);
item.V18 = excelReader.IsDBNull(17) ? (double?)null : excelReader.GetDouble(17);
item.V19 = excelReader.IsDBNull(18) ? (double?)null : excelReader.GetDouble(18);
item.V20 = excelReader.IsDBNull(19) ? (double?)null : excelReader.GetDouble(19);
item.V21 = excelReader.IsDBNull(20) ? (double?)null : excelReader.GetDouble(20);
item.V22 = excelReader.IsDBNull(21) ? (double?)null : excelReader.GetDouble(21);
item.V23 = excelReader.IsDBNull(22) ? (double?)null : excelReader.GetDouble(22);
item.V24 = excelReader.IsDBNull(23) ? (double?)null : excelReader.GetDouble(23);
item.V25 = excelReader.IsDBNull(24) ? (DateTime?)null : excelReader.GetDateTime(24);
item.V26 = excelReader.IsDBNull(25) ? (double?)null : excelReader.GetDouble(25);
item.V27 = excelReader.IsDBNull(26) ? (DateTime?)null : excelReader.GetDateTime(26);
item.V28 = excelReader.IsDBNull(27) ? (DateTime?)null : excelReader.GetDateTime(27);
item.V29 = excelReader.IsDBNull(28) ? (DateTime?)null : excelReader.GetDateTime(28);
item.V30 = excelReader.IsDBNull(29) ? (DateTime?)null : excelReader.GetDateTime(29);
item.V31 = excelReader.IsDBNull(30) ? (DateTime?)null : excelReader.GetDateTime(30);
item.V32 = excelReader.IsDBNull(31) ? (DateTime?)null : excelReader.GetDateTime(31);
item.V33 = excelReader.IsDBNull(32) ? (DateTime?)null : excelReader.GetDateTime(32);
item.V34 = excelReader.GetString(33);
item.V35 = excelReader.GetString(34);
item.V36 = excelReader.GetString(35);
item.V37 = excelReader.IsDBNull(36) ? (DateTime?)null : excelReader.GetDateTime(36);
item.V38 = excelReader.GetString(37);
item.V39 = excelReader.IsDBNull(38) ? (DateTime?)null : excelReader.GetDateTime(38);
item.V40 = excelReader.IsDBNull(39) ? (double?)null : excelReader.GetDouble(39);
items.Add(item);
}
model.Items.Add(category, items);
model.PageRows.Add(category, rowIndex);
} while (excelReader.NextResult());
excelReader.Close();
return(model);
}
catch (Exception ex)
{
throw ex;
}
}