/// <summary>
/// Update source DataTable
/// </summary>
/// <param name="rng"></param>
private void UpdateSourceDataTable(IRange rng)
{
// m_suspendUpdates = true;
try
{
IValues values = (IValues)worksheet;
var errorMessages = new List <string>();
int maxRowIndex = rng.Row + rng.RowCount;
int maxColumnIndex = rng.Column + rng.ColumnCount;
for (int r = rng.Row; r < maxRowIndex; r++)
{
for (int c = rng.Column; c < maxColumnIndex; c++)
{
// source DataTable is offset one row from spreadsheet because of header row
// only update where spreasheet and DataTable overlap
if (r <= m_dataTable.Rows.Count &&
c < m_dataTable.Columns.Count &&
r > 0 && // don't change column header
c > 0 // don't allow date column
)
{
// TO DO.. append rows to datatable?
// TO DO.. allow dates?
IValue val = values[r, c];
if (val == null)
{
m_dataTable.Rows[r - 1][c] = DBNull.Value;
}
else
if (m_dataTable.Columns[c].DataType == typeof(double))
{
double d = Reclamation.TimeSeries.Point.MissingValueFlag;
if (SpreadsheetGearExcel.TryReadingValue(val, out d))
{
object o = m_dataTable.Rows[r - 1][c];
if (o != DBNull.Value && Convert.ToDouble(o) == d)
{
continue; // not changed (HACK To fix dragging nub
}
m_dataTable.Rows[r - 1][c] = d;
if (AutoFlagDayFiles && interval == TimeInterval.Irregular &&
rng.ColumnCount == 1)
{
values.SetText(r, c + 1, "e");
//rng[r, c].Value = "e";
m_dataTable.Rows[r - 1][c + 1] = "e";
}
}
else
{
errorMessages.Add("Invalid number on row " + r + " " + val.ToString());
}
}
else
if (m_dataTable.Columns[c].DataType == typeof(string))
{
string txt = val.Text;
if (txt == null)
{
m_dataTable.Rows[r - 1][c] = DBNull.Value;
}
else
{
m_dataTable.Rows[r - 1][c] = txt;
}
}
}
}
}
if (errorMessages.Count > 0)
{
MessageBox.Show(String.Join("\n", errorMessages.ToArray()));
}
}
finally
{
// m_suspendUpdates = false;
}
// OnUpdateCompleted(EventArgs.Empty);
}
/// <summary>
/// Update source DataTable
/// </summary>
/// <param name="rng"></param>
private void UpdateSourceDataTable(IRange rng)
{
m_suspendUpdates = true;
try
{
IValues values = (IValues)worksheet;
var errorMessages = new List <string>();
int maxRowIndex = rng.Row + rng.RowCount;
int maxColumnIndex = rng.Column + rng.ColumnCount;
for (int r = rng.Row; r < maxRowIndex; r++)
{
for (int c = rng.Column; c < maxColumnIndex; c++)
{
// source DataTable is offset one row from spreadsheet because of header row
// only update where spreasheet and DataTable overlap
if (r <= m_dataTable.Rows.Count &&
c < m_dataTable.Columns.Count &&
r > 0 && // don't change column header
c >= 0 // don't allow date column
)
{
// TO DO.. append rows to datatable?
// TO DO.. allow dates?
IValue val = values[r, c];
if (val == null)
{
m_dataTable.Rows[r - 1][c] = DBNull.Value;
}
else
if (m_dataTable.Columns[c].DataType == typeof(double))
{
double d = Reclamation.TimeSeries.Point.MissingValueFlag;
if (SpreadsheetGearExcel.TryReadingValue(val, out d))
{
m_dataTable.Rows[r - 1][c] = d;
}
else
{
errorMessages.Add("Invalid number on row " + r + " " + val.ToString());
}
}
else
if (m_dataTable.Columns[c].DataType == typeof(int))
{
int i = 0;
if (SpreadsheetGearExcel.TryReadingValue(val, out i))
{
m_dataTable.Rows[r - 1][c] = i;
}
else
{
errorMessages.Add("Invalid number on row " + r + " " + val.ToString());
}
}
else
if (m_dataTable.Columns[c].DataType == typeof(string))
{
string txt = val.Text;
if (txt != null)
{
m_dataTable.Rows[r - 1][c] = txt;
}
else
{
if (val.Type == SpreadsheetGear.Advanced.Cells.ValueType.Number)
{
m_dataTable.Rows[r - 1][c] = val.Number.ToString();
}
else
{
m_dataTable.Rows[r - 1][c] = DBNull.Value;
}
}
}
else if (m_dataTable.Columns[c].DataType == typeof(DateTime))
{
DateTime t;
if (SpreadsheetGearExcel.TryReadingDate(workbook, val, out t))
{
m_dataTable.Rows[r - 1][c] = t;
}
else
{
m_dataTable.Rows[r - 1][c] = DBNull.Value;
}
}
}
}
}
if (errorMessages.Count > 0)
{
MessageBox.Show(String.Join("\n", errorMessages.ToArray()));
}
}
finally
{
m_suspendUpdates = false;
}
OnUpdateCompleted(EventArgs.Empty);
}