private void MenuFileOpen_Click(object sender, EventArgs e)
{
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
{
string filename = openFileDialog.FileName;
string extension = Path.GetExtension(filename);
if (extension == ".xls" || extension == ".xlsx")
{
ExcelReader db = new ExcelReader(filename, true, false);
TableSelectDialog t = new TableSelectDialog(db.GetWorksheetList());
if (t.ShowDialog(this) == DialogResult.OK)
{
this.sourceTable = db.GetWorksheet(t.Selection);
this.dgvAnalysisSource.DataSource = sourceTable;
this.cbTimeName.Items.Clear();
this.cbEventName.Items.Clear();
this.checkedListBox1.Items.Clear();
foreach (DataColumn col in sourceTable.Columns)
{
this.cbTimeName.Items.Add(col.ColumnName);
this.cbEventName.Items.Add(col.ColumnName);
this.checkedListBox1.Items.Add(col.ColumnName);
}
this.cbTimeName.SelectedIndex = 0;
}
}
}
}
public void ExcelReaderConstructorTest()
{
string path = @"..\..\..\..\Unit Tests\Accord.Tests.Statistics\Resources\sample.xls";
// Create a new reader, opening a given path
ExcelReader reader = new ExcelReader(path);
// Afterwards, we can query the file for all
// worksheets within the specified workbook:
string[] sheets = reader.GetWorksheetList();
// Finally, we can request an specific sheet:
DataTable table = reader.GetWorksheet(sheets[1]);
// Now, we have loaded the Excel file into a DataTable. We
// can go further and transform it into a matrix to start
// running other algorithms on it:
double[,] matrix = table.ToMatrix();
// We can also do it retrieving the name for each column:
string[] columnNames; matrix = table.ToMatrix(out columnNames);
// Or we can extract specific columns into single arrays:
double[] column = table.Columns[0].ToArray();
// PS: you might need to import the Accord.Math namespace in
// order to be able to call the ToMatrix extension methods.
Assert.AreEqual(6, matrix.Length);
Assert.AreEqual(3, columnNames.Length);
Assert.AreEqual(2, column.Length);
}
private static void testData1(ExcelReader withHeader, ExcelReader sansHeader)
{
var sheets1 = withHeader.GetWorksheetList();
var sheets2 = sansHeader.GetWorksheetList();
Assert.AreEqual(1, sheets1.Length);
Assert.AreEqual(1, sheets2.Length);
Assert.AreEqual("testnormal", sheets1[0]);
Assert.AreEqual("testnormal", sheets2[0]);
var dataWithHeader = withHeader.GetWorksheet("testnormal");
var dataSansHeader = sansHeader.GetWorksheet("testnormal");
Assert.AreEqual(119, dataWithHeader.Rows.Count);
Assert.AreEqual(17, dataWithHeader.Columns.Count);
Assert.AreEqual(120, dataSansHeader.Rows.Count);
Assert.AreEqual(17, dataSansHeader.Columns.Count);
var firstRowWithHeader = dataWithHeader.Rows[0].ItemArray;
Assert.AreEqual("90", firstRowWithHeader[0]);
Assert.AreEqual("0", firstRowWithHeader[1]);
Assert.AreEqual("0", firstRowWithHeader[2]);
Assert.AreEqual("0", firstRowWithHeader[3]);
Assert.AreEqual("0", firstRowWithHeader[4]);
var firstRowSansHeader = dataSansHeader.Rows[0].ItemArray;
Assert.AreEqual("1", firstRowSansHeader[0]);
Assert.AreEqual("2", firstRowSansHeader[1]);
Assert.AreEqual("3", firstRowSansHeader[2]);
Assert.AreEqual("4", firstRowSansHeader[3]);
Assert.AreEqual("5", firstRowSansHeader[4]);
}
public ExcelFileWrapper(string filePath, bool hasHeaders = true,
bool hasMixedData = false)
{
string ext = Path.GetExtension(filePath);
if (!ext.Equals(".xls", StringComparison.OrdinalIgnoreCase)
&& !ext.Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
{
throw new ArgumentException(Lib.ExcelFileExpected, "filePath");
}
this.filePath = filePath;
excelReader = new ExcelReader(filePath, hasHeaders, hasMixedData);
worksheetNamesCached = excelReader.GetWorksheetList();
}
private void MenuFileOpen_Click(object sender, EventArgs e)
{
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
{
string filename = openFileDialog.FileName;
string extension = Path.GetExtension(filename);
if (extension == ".xls" || extension == ".xlsx")
{
ExcelReader db = new ExcelReader(filename, true, false);
TableSelectDialog t = new TableSelectDialog(db.GetWorksheetList());
if (t.ShowDialog(this) == DialogResult.OK)
{
DataTable tableSource = db.GetWorksheet(t.Selection);
double[,] sourceMatrix = tableSource.ToMatrix(out sourceColumns);
// Detect the kind of problem loaded.
if (sourceMatrix.GetLength(1) == 2)
{
MessageBox.Show("Missing class column.");
}
else
{
this.dgvLearningSource.DataSource = tableSource;
this.dgvTestingSource.DataSource = tableSource.Copy();
CreateScatterplot(graphInput, sourceMatrix);
// enable "Start" button
startButton.Enabled = true;
}
}
}
else if (extension == ".csv")
{
DataTable tableSource = CSVReader.ReadCSVFile(filename.Replace("\\", "//"), true);
double[,] sourceMatrix = tableSource.ToMatrix(out sourceColumns);
// Detect the kind of problem loaded.
if (sourceMatrix.GetLength(1) == 2)
{
MessageBox.Show("Missing class column.");
}
else
{
this.dgvLearningSource.DataSource = tableSource;
this.dgvTestingSource.DataSource = tableSource.Copy();
CreateScatterplot(graphInput, sourceMatrix);
// enable "Start" button
startButton.Enabled = true;
}
}
}
}
private void MenuFileOpen_Click(object sender, EventArgs e)
{
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
{
string filename = openFileDialog.FileName;
string extension = Path.GetExtension(filename);
if (extension == ".xls" || extension == ".xlsx")
{
ExcelReader db = new ExcelReader(filename, true, false);
TableSelectDialog t = new TableSelectDialog(db.GetWorksheetList());
if (t.ShowDialog(this) == DialogResult.OK)
{
DataTable tableSource = db.GetWorksheet(t.Selection);
double[,] sourceMatrix = tableSource.ToMatrix(out columnNames);
// Detect the kind of problem loaded.
if (sourceMatrix.GetLength(1) == 2)
{
MessageBox.Show("Missing class column.");
}
else
{
this.dgvLearningSource.DataSource = tableSource;
this.dgvTestingSource.DataSource = tableSource.Copy();
CreateScatterplot(graphInput, sourceMatrix);
}
}
}
}
lbStatus.Text = "Switch to the Machine Creation tab to create a learning machine!";
}
private void MenuFileOpen_Click(object sender, EventArgs e)
{
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
{
string filename = openFileDialog.FileName;
string extension = Path.GetExtension(filename);
if (extension == ".xls" || extension == ".xlsx")
{
ExcelReader db = new ExcelReader(filename, true, false);
TableSelectDialog t = new TableSelectDialog(db.GetWorksheetList());
if (t.ShowDialog(this) == DialogResult.OK)
{
this.sourceTable = db.GetWorksheet(t.Selection);
this.dgvSource.DataSource = sourceTable;
}
}
else if (extension == ".xml")
{
DataTable dataTableAnalysisSource = new DataTable();
dataTableAnalysisSource.ReadXml(openFileDialog.FileName);
this.sourceTable = dataTableAnalysisSource;
this.dgvSource.DataSource = sourceTable;
}
}
}
public void RevertTest3()
{
string path = @"..\..\..\..\Unit Tests\Accord.Tests.Statistics\Resources\examples.xls";
// Create a new reader, opening a given path
ExcelReader reader = new ExcelReader(path);
// Afterwards, we can query the file for all
// worksheets within the specified workbook:
string[] sheets = reader.GetWorksheetList();
// Finally, we can request an specific sheet:
DataTable table = reader.GetWorksheet("Wikipedia");
// Now, we have loaded the Excel file into a DataTable. We
// can go further and transform it into a matrix to start
// running other algorithms on it:
double[,] matrix = table.ToMatrix();
IKernel kernel = new Polynomial(2);
// Create analysis
KernelPrincipalComponentAnalysis target = new KernelPrincipalComponentAnalysis(matrix,
kernel, AnalysisMethod.Center, centerInFeatureSpace: true);
target.Compute();
double[,] forward = target.Result;
double[,] reversion = target.Revert(forward);
Assert.IsTrue(!reversion.HasNaN());
}
public void SpreadsheetNames_Success()
{
string path = Path.Combine("Resources", "excel", "spreadsheet_names.xls");
// Create a new reader, opening a given path
ExcelReader reader = new ExcelReader(path);
string[] sheets = reader.GetWorksheetList();
Assert.AreEqual(4, sheets.Length);
Assert.AreEqual("Example 1", sheets[0]);
Assert.AreEqual("Example 2", sheets[1]);
Assert.AreEqual("Example 3", sheets[2]);
Assert.AreEqual("References", sheets[3]);
// Finally, we can request an specific sheet:
DataTable table = reader.GetWorksheet(sheets[1]);
Assert.AreEqual(2, table.Columns.Count);
Assert.AreEqual(42, table.Rows.Count);
}
private static void testWorksheets(ExcelReader target)
{
string[] list = target.GetWorksheetList();
Assert.AreEqual(4, list.Length);
Assert.AreEqual("Plan1", list[0]);
Assert.AreEqual("Plan2", list[1]);
Assert.AreEqual("Plan3", list[2]);
Assert.AreEqual("Sheet1", list[3]);
}
public void ExcelReaderConstructorTest()
{
string path = Path.Combine("Resources", "sample.xls");
// Create a new reader, opening a given path
ExcelReader reader = new ExcelReader(path);
// Afterwards, we can query the file for all
// worksheets within the specified workbook:
string[] sheets = reader.GetWorksheetList();
// Finally, we can request an specific sheet:
DataTable table = reader.GetWorksheet(sheets[1]);
// Now, we have loaded the Excel file into a DataTable. We
// can go further and transform it into a matrix to start
// running other algorithms on it:
Assert.AreEqual(4, sheets.Length);
Assert.AreEqual("Plan1", sheets[0]);
Assert.AreEqual("Plan2", sheets[1]);
Assert.AreEqual("Plan3", sheets[2]);
Assert.AreEqual("Sheet1", sheets[3]);
Assert.AreEqual(3, table.Columns.Count);
Assert.AreEqual(2, table.Rows.Count);
}
public void RevertTest2_new_method()
{
string path = @"Resources\examples.xls";
// Create a new reader, opening a given path
ExcelReader reader = new ExcelReader(path);
// Afterwards, we can query the file for all
// worksheets within the specified workbook:
string[] sheets = reader.GetWorksheetList();
// Finally, we can request an specific sheet:
DataTable table = reader.GetWorksheet("Wikipedia");
// Now, we have loaded the Excel file into a DataTable. We
// can go further and transform it into a matrix to start
// running other algorithms on it:
double[][] matrix = table.ToArray();
IKernel kernel = new Gaussian(5);
// Create analysis
var target = new KernelPrincipalComponentAnalysis(kernel)
{
Method = PrincipalComponentMethod.Center,
Center = true // Center in feature space
};
var regression = target.Learn(matrix);
double[][] forward = regression.Transform(matrix);
double[][] reversion = target.Revert(forward);
Assert.IsTrue(!reversion.HasNaN());
}
public static DataTable Load(Stream stream, TableFormat format)
{
switch (format)
{
case TableFormat.SerializedXml:
{
XmlSerializer serializer = new XmlSerializer(typeof(DataTable));
return((DataTable)serializer.Deserialize(stream));
}
case TableFormat.SerializedBin:
{
BinaryFormatter serializer = new BinaryFormatter();
return((DataTable)serializer.Deserialize(stream));
}
case TableFormat.OctaveMatFile:
{
MatReader reader = new MatReader(stream);
return(reader.Fields.First().Value.GetValue <double[, ]>().ToTable());
}
case TableFormat.OpenDocument:
{
ExcelReader reader = new ExcelReader(stream, true);
string ws = reader.GetWorksheetList().First();
return(reader.GetWorksheet(ws));
}
case TableFormat.OlderExcel:
{
ExcelReader reader = new ExcelReader(stream, false);
string ws = reader.GetWorksheetList().First();
return(reader.GetWorksheet(ws));
}
case TableFormat.Csv:
{
CsvReader reader = new CsvReader(stream, true);
return(reader.ToTable());
}
case TableFormat.Tsv:
{
CsvReader reader = new CsvReader(stream, true, '\t');
return(reader.ToTable());
}
case TableFormat.LibSVM:
{
SparseReader reader = new SparseReader(stream);
return(reader.ToTable());
}
case TableFormat.Idx:
{
IdxReader reader = new IdxReader(stream);
return(reader.ReadToEndAsVectors().ToTable());
}
case TableFormat.CSharp:
throw new NotSupportedException();
}
}
public static DataTable Load(Stream stream, TableFormat format)
{
switch (format)
{
case TableFormat.SerializedXml:
{
XmlSerializer serializer = new XmlSerializer(typeof(DataTable));
return (DataTable)serializer.Deserialize(stream);
}
case TableFormat.SerializedBin:
{
BinaryFormatter serializer = new BinaryFormatter();
return (DataTable)serializer.Deserialize(stream);
}
case TableFormat.OctaveMatFile:
{
MatReader reader = new MatReader(stream);
return reader.Fields.First().Value.GetValue<double[,]>().ToTable();
}
case TableFormat.OpenDocument:
{
ExcelReader reader = new ExcelReader(stream, true);
string ws = reader.GetWorksheetList().First();
return reader.GetWorksheet(ws);
}
case TableFormat.OlderExcel:
{
ExcelReader reader = new ExcelReader(stream, false);
string ws = reader.GetWorksheetList().First();
return reader.GetWorksheet(ws);
}
case TableFormat.Csv:
{
CsvReader reader = new CsvReader(stream, true);
return reader.ToTable();
}
case TableFormat.Tsv:
{
CsvReader reader = new CsvReader(stream, true, '\t');
return reader.ToTable();
}
case TableFormat.LibSVM:
{
SparseReader reader = new SparseReader(stream);
return reader.ToTable();
}
case TableFormat.Idx:
{
IdxReader reader = new IdxReader(stream);
return reader.ReadToEndAsVectors().ToTable();
}
case TableFormat.CSharp:
throw new NotSupportedException();
}
}
