public override DataGrid Import()
{
DataGrid dataGrid = new DataGrid();
Columns.Where(match => !Excluded.Contains(match.ColumnName)).ForEach(c => dataGrid.Columns.Add(c));
dataGrid.Read(MediaConfiguration.Reader);
return dataGrid;
}
protected override void ProcessHeaders(DataGridColumns columns, DataGrid rawData)
{
if (rawData == null) throw new ArgumentNullException("rawData");
if (columns == null) throw new ArgumentNullException("columns");
if (!columns.Any()) throw new ArgumentException("No columns were specified.", "columns");
foreach (DataGridColumn dataGridColumn in columns.Expand())
{
if (dataGridColumn.CellValue == null)
{
Log.Info(String.Format("Column '{0}' of Type '{1}' at column position '{2}, {3}'."
, dataGridColumn
, dataGridColumn.ColumnType.FriendlyName()
, dataGridColumn.ColumnPosition
, dataGridColumn.ColumnSize));
}
else
{
Log.Info(String.Format("A cell column value was provided for Column '{0}' of Type '{1}'. Cells belonging to this column will inherit this value."
, dataGridColumn
, dataGridColumn.ColumnType.FriendlyName()));
}
if (!dataGridColumn.ColumnType.DerivesFromType(dataGridColumn.BindingType))
{
Log.Warning(String.Format("Column '{0}': Type mismatch between Column Type '{1}' and Binding Type {2}. Data may be lost during Type Casting."
, dataGridColumn
, dataGridColumn.ColumnType.FriendlyName()
, dataGridColumn.BindingType));
}
}
}
protected override DataGrid ReadRawData(DataGridColumns columns)
{
if (columns == null) throw new ArgumentNullException("columns");
Framework.Excel.Interop.Excel excel = null;
ExcelInterop.Worksheet worksheet = null;
try
{
Log.Info(String.Format("Reading data from Excel File '{0}'...", Settings.FilePath));
excel = Framework.Excel.Interop.Excel.Open(Settings.FilePath, Settings.Password);
worksheet = !Settings.Worksheet.IsNullOrBlank() ? excel.Worksheet(Settings.Worksheet) : excel.Worksheet(0);
ExcelInterop.Range range = worksheet.UsedRange;
object[,] cells = range.Value2;
DataGrid dataGrid = new DataGrid();
/*
* Headers
*/
var allColumns = columns.Expand();
int columnCount = range.Columns.Count;
for (int colIndex = 1; colIndex <= columnCount; colIndex++)
{
string columnName = Convert.ToString(cells[1, colIndex]);
if (columnName.IsNullOrBlank() || (columns.Any() && !allColumns.Any(c => c.ColumnName.Equals(columnName))))
{ continue; }
DataGridColumn column = new DataGridColumn(columnName) { OriginalSourceIndex = colIndex };
dataGrid.Columns.Add(column);
}
/*
* Data
*/
int rowCount;
if (Settings.RowCount > 0)
{
rowCount = (Settings.RowCount + 1 <= range.Rows.Count) ? Settings.RowCount + 1 : range.Rows.Count;
}
else if (Settings.RowCount < 0)
{
rowCount = range.Rows.Count + Settings.RowCount;
}
else
{
rowCount = range.Rows.Count;
}
for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
{
DataGridRow dataGridRow = dataGrid.Rows.New();
foreach (DataGridColumn column in dataGrid.Columns)
{
try
{
DataGridCell cell = dataGridRow.Cells.New();
cell.OriginalValue = cells[rowIndex, column.OriginalSourceIndex];
}
catch (Exception exception)
{
throw new Exception(String.Format("An error occurred while reading cell at Row '{0}', Column '{1}'."
, rowIndex
, column.OriginalSourceIndex)
, exception);
}
}
}
return dataGrid;
}
catch (ExcelInteropException e)
{
throw e;
}
catch (Exception e)
{
throw new ExcelInteropException(String.Format("An error occurred while Reading data from Excel file '{0}'.", Settings.FilePath), e);
}
finally
{
// Worksheet
if (worksheet != null)
{
Marshal.FinalReleaseComObject(worksheet);
}
// Excel
if (excel != null)
{ excel.Dispose(); }
}
}
protected override DataGrid ReadRawData(DataGridColumns columns)
{
if (columns == null) throw new ArgumentNullException("columns");
if (!columns.Any()) throw new ArgumentException("No columns were specified.", "columns");
Log.Info(String.Format("Reading data from Fixed-Length File '{0}'...", Settings.FilePath));
using (StreamReader reader = new StreamReader(Settings.FilePath))
{
if (reader.EndOfStream)
{ throw new AbortException(String.Format("File '{0}' is Empty.", Settings.FilePath)); }
DataGrid dataGrid = new DataGrid();
/*
* Headers
*/
var allColumns = columns.Expand().Select(c => new DataGridColumn(c.ColumnName));
dataGrid.Columns.AddRange(allColumns);
/*
* Data
*/
while (!reader.EndOfStream)
{
DataGridRow dataGridRow = dataGrid.Rows.New();
string dataLine = reader.ReadLine();
foreach (DataGridColumn column in dataGrid.Columns)
{
try
{
string cellValue = dataLine.Substring(column.ColumnPosition - 1, column.ColumnSize);
dataGridRow.Cells.Add(DataGridCell.StringCell(cellValue));
}
catch (Exception exception)
{
throw new Exception(String.Format("An error occurred while reading cell at Row: '{0}', Position: '{1}, {2}'."
, dataGridRow.Index() + 1
, column.ColumnPosition
, column.ColumnSize)
, exception);
}
}
}
return dataGrid;
}
}
