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); }