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 static List <RegionDeal> GetRegionDeals(string filePath)
{
FileStream stream = System.IO.File.Open(filePath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
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;
// var dataSet = excelReader.AsDataSet();
//5. Data Reader methods
var deals = new List <RegionDeal>();
var isFirstRow = true;
while (excelReader.Read())
{
if (isFirstRow)
{
isFirstRow = false;
continue;
}
var value = excelReader.GetDecimal(7);
if (value < 0)
{
value = 0;
}
deals.Add(new RegionDeal {
Region = excelReader.GetString(6), Value = value
});
}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
excelReader.Dispose();
// var deals = new List<RegionDeal>();
// foreach (DataRow row in dataSet.Tables[0].Rows)
// {
// var value = row[7] is DBNull ? 0 : Convert.ToDecimal(row[7]);
// deals.Add(new RegionDeal {Region = row[6].ToString(), Value = value});
// }
var sums = deals.GroupBy(d => d.Region)
.Select(x => new RegionDeal {
Value = x.Sum(d => d.Value), Region = x.First().Region
})
.OrderBy(x => x.Region)
.ToList();
return(sums);
}
public void Open()
{
FileStream fs = new FileStream(m_kartotek_dat, FileMode.Open, FileAccess.Read, FileShare.None);
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(fs);
excelReader.IsFirstRowAsColumnNames = false;
tblwfak recwFak = new tblwfak
{
sk = "S",
dato = DateTime.Today,
konto = 100032,
};
while (excelReader.Read())
{
tblwfaklin recwfaklin = new tblwfaklin
{
varenr = "",
tekst = excelReader.GetString(0),
konto = 1000,
momskode = "S25",
antal = 1,
enhed = "stk",
pris = excelReader.GetDecimal(1),
rabat = 0,
moms = excelReader.GetDecimal(1) / 4,
nettobelob = excelReader.GetDecimal(1),
bruttobelob = excelReader.GetDecimal(1) + excelReader.GetDecimal(1) / 4,
};
recwFak.tblwfaklins.Add(recwfaklin);
}
excelReader.Close();
Program.dbDataTransSumma.tblwfaks.InsertOnSubmit(recwFak);
Program.dbDataTransSumma.SubmitChanges();
}
public VendorProduct GetNextProduct()
{
var product = new VendorProduct();
var hasData = false;
while (!hasData)
{
try
{
if (!_excelReader.Read() || _excelReader.GetString(_config.SKU) == null)
{
return(null);
}
product.VendorId = _config.VendorId;
product.SKU = _excelReader.GetString(_config.SKU);
product.Name = _config.Name == null ? null : _excelReader.GetString(_config.Name ?? 0);
product.Description = _config.Name == null ? null : _excelReader.GetString(_config.Description ?? 0);
product.ShortDescription = _config.Name == null ? null : _excelReader.GetString(_config.ShortDescription ?? 0);
product.Category = _config.Name == null ? null : _excelReader.GetString(_config.Category ?? 0);
product.UPCCode = _config.Name == null ? null : _excelReader.GetString(_config.UPCCode ?? 0);
product.Cost = _config.Name == null ? -1 : _excelReader.GetDecimal(_config.Cost ?? 0);
product.Quantity = _config.Name == null ? -1 : _excelReader.GetInt16(_config.Quantity ?? 0);
product.ResultDate = _config.ResultDate;
// set the flag to true
hasData = true;
}
catch (Exception ex)
{
hasData = false;
Console.Error.WriteLine("Error in parsing file {0} at row number: {1}", _fileInfo.FullName, _records);
Logger.LogError(this.GetType().Name, string.Format("Error in parsing file {0} at row number: {1}", _fileInfo.FullName, _records), ex.StackTrace);
throw new Exception(ex.Message);
}
finally
{
// increment the record counter
_records++;
}
}
return(product);
}
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();
}
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 decimal GetDecimal(int i) => _reader.GetDecimal(i);