static void WriteRows(int rows, int cols, bool openXML)
{
Stopwatch timer = Stopwatch.StartNew();
IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = workbook.Worksheets[0];
IValues values = (IValues)worksheet;
for (int row = 0; row < rows; row++)
{
values.SetText(row, 0, "Row " + (row + 1));
for (int col = 1; col < cols; col++)
{
values.SetNumber(row, col, row * cols + col);
}
}
string filename = @"c:\tmp\WriteRows" + (openXML ? ".xlsx" : ".xls");
FileFormat fileFormat = openXML ? FileFormat.OpenXMLWorkbook : FileFormat.Excel8;
workbook.SaveAs(filename, fileFormat);
Console.WriteLine("Created {0} with {1}x{2} rows / cols in {3} seconds", filename, rows, cols, timer.Elapsed.TotalSeconds);
}
/// <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);
}
