private void FormatCells(TimeInterval db)
{
if (m_dataTable.Columns.Count == 0)
{
return;
}
// Default format
worksheet.UsedRange.Font.Color = SpreadsheetGear.Colors.Black;
worksheet.Range["A:A"].Interior.Color = SpreadsheetGear.Colors.LightGray;
worksheet.Range["A:A"].Font.Bold = true;
worksheet.Range["A:A"].Font.Italic = true;
worksheet.Range[0, 0, 0, m_dataTable.Columns.Count - 1].Interior.Color = SpreadsheetGear.Colors.LightGray;
worksheet.Range[0, 0, 0, m_dataTable.Columns.Count - 1].Font.Bold = true;
worksheet.Range[0, 0, 0, m_dataTable.Columns.Count - 1].Font.Italic = true;
if (db == TimeInterval.Irregular)
{
for (int i = 1; i < m_dataTable.Columns.Count; i += 2)
{
string dataColumn = SpreadsheetGearExcel.ReferenceFromIndex(i);
string flagColumn = SpreadsheetGearExcel.ReferenceFromIndex(i + 1);
ConditionalFormatting(dataColumn, flagColumn);
}
}
else if (db == TimeInterval.Monthly)
{
string dateRange = "A:A";// +m_dataTable.Rows.Count.ToString();
worksheet.Range[dateRange].NumberFormat = "mmm yyyy";
}
}
public void LargeImport()
{
Performance perf = new Performance();
string dataPath = TestData.DataPath;
string fn = @"T:\PN6200\Staff\KTarbet\PiscesSampleData\CraigAddley\Testout.txt";
Console.WriteLine(fn);
Assert.IsTrue(File.Exists(fn), "Missing file " + fn);
SpreadsheetGearExcel xls = new SpreadsheetGearExcel(fn);
perf.Report("done reading " + fn);
Series s = SpreadsheetGearSeries.ReadFromWorkbook(xls.Workbook, "sheet1", "DateTime", "flow", false, "cfs");
s.Read();
Assert.AreEqual(666282, s.Count);
//Series s = new ExcelDataReaderSeries(fn, "sheet1", "DateTime", "flow","cfs");
perf.Report("done importing excel file");
/*
* 666282 records
*
* SaveTable ==> 286 seconds
* InsertTable ==> 64 seconds
*/
// perform exceedance calculation
//ExplorerView v = new ExplorerView();
//Explorer explorer = new Explorer(v, db);
//explorer.MonthDayRange
}
public void MonthlyIntervalDetection()
{
string filename = TestData.DataPath + "\\unregulation calculations.xls";
SpreadsheetGearExcel db = new SpreadsheetGearExcel(filename);
SpreadsheetGearSeries s = new SpreadsheetGearSeries(filename, "Riverware returns", "A", "B");
s.Read();
Assert.AreEqual(TimeInterval.Monthly, s.TimeInterval);
}
public ImportExcelDatabase(string filename, string[] DBunits)
{
InitializeComponent();
db = new SpreadsheetGearExcel(filename);
this.label1.Text = Path.GetFileName(filename);
comboBoxSheetNames.Items.Clear();
comboBoxSheetNames.Items.AddRange(db.SheetNames);
comboBoxSheetNames.SelectedIndex = 0;
LoadList(comboBoxUnits, DBunits);
RefreshColumnNames();
}
public void ColumnNames()
{
string filename = TestData.DataPath + "\\ImportWithUpdate.xls";
SpreadsheetGearExcel db = new SpreadsheetGearExcel(filename);
string[] cols = db.ColumnNames("Duplicates");
foreach (var s in cols)
{
Console.WriteLine(s);
}
Assert.AreEqual(34, cols.Length);
}
private void FormatCells(TimeInterval db)
{
if (db == TimeInterval.Irregular)
{
for (int i = 1; i < m_dataTable.Columns.Count; i += 2)
{
string dataColumn = SpreadsheetGearExcel.ReferenceFromIndex(i);
string flagColumn = SpreadsheetGearExcel.ReferenceFromIndex(i + 1);
ConditionalFormatting(dataColumn, flagColumn);
}
}
else
if (db == TimeInterval.Monthly)
{
string dateRange = "A:A"; // +m_dataTable.Rows.Count.ToString();
worksheet.Range[dateRange].NumberFormat = "mmm yyyy";
}
}
public void ColumnReferenceNames()
{
int i = SpreadsheetGearSeries.ColumnIndexFromRef("A");
Assert.AreEqual(0, i);
i = SpreadsheetGearSeries.ColumnIndexFromRef("AA");
Assert.AreEqual(26, i);
i = SpreadsheetGearSeries.ColumnIndexFromRef("IV");
Assert.AreEqual(255, i);
string s = SpreadsheetGearExcel.ReferenceFromIndex(0);
Assert.AreEqual("A", s);
s = SpreadsheetGearExcel.ReferenceFromIndex(26);
Assert.AreEqual("AA", s);
s = SpreadsheetGearExcel.ReferenceFromIndex(255);
Assert.AreEqual("IV", s);
}
private static void ExportToExcel(string tmpXlsName, bool multiColumn,
Reclamation.TimeSeries.TimeSeriesDatabaseDataSet.RatingTableDataTable rt)
{
#if SpreadsheetGear
var xls = new SpreadsheetGearExcel(tmpXlsName);
string sheetName = multiColumn ? "Standard" : "simple";
IWorksheet sheet = (IWorksheet)xls.Workbook.Sheets[sheetName];
xls.Workbook.Sheets[!multiColumn ? "Standard" : "simple"].Delete();
IRange rng = sheet.Range[0, 0];
DataTable table = rt;
rng[0, 0].Value = rt.Name + " " + rt.EditDate;
if (multiColumn)
{
table = RatingTableUtility.ConvertToMultiColumn(table);
rng["A3"].CopyFromDataTable(table, SpreadsheetGear.Data.SetDataFlags.AllText);
for (int c = 1; c <= 10; c++)
{
rng[1, c].Value = rt.YUnits;
}
}
else
{
rng["A3"].CopyFromDataTable(table, SpreadsheetGear.Data.SetDataFlags.None);
rng[2, 0].Value = rt.XUnits;
rng[2, 1].Value = rt.YUnits;
}
xls.Save();
#else
#endif
}
/// <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);
}
