private void btnOpen_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;
var inputs = sourceMatrix.Submatrix(0, sourceMatrix.GetLength(0) - 1, 0, 2);
CreateScatterplot(graphInput, inputs);
}
}
}
}
}
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 = @"..\..\..\Accord.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 testTables(ExcelReader target)
{
DataSet set = target.GetWorksheet();
Assert.AreEqual(4, set.Tables.Count);
Assert.AreEqual("Plan1", set.Tables[0].TableName);
Assert.AreEqual("Plan2", set.Tables[1].TableName);
Assert.AreEqual("Plan3", set.Tables[2].TableName);
Assert.AreEqual("Sheet1", set.Tables[3].TableName);
}
public void ConstructorExcel8Test()
{
string path = @"..\..\..\Accord.Tests\Accord.Tests.Statistics\Resources\sample.xls";
ExcelReader target = new ExcelReader(path);
testWorksheets(target);
testColumns(target, false);
testTables(target);
}
public void ConstructorExcel10Test()
{
// If a 64-bit ACE is installed, this test requires a 64-bit process to run correctly.
string path = @"..\..\..\Accord.Tests\Accord.Tests.Statistics\Resources\sample.xlsx";
ExcelReader target = new ExcelReader(path);
testWorksheets(target);
testColumns(target, true);
testTables(target);
}
public void MixtureWeightsFitTest2()
{
MemoryStream stream = new MemoryStream(Resources.CircleWithWeights);
ExcelReader reader = new ExcelReader(stream, xlsx: false);
DataTable table = reader.GetWorksheet("Sheet1");
double[,] matrix = table.ToMatrix();
double[][] points = matrix.Submatrix(null, 0, 1).ToArray();
double[] weights = matrix.GetColumn(2);
// Randomly initialize some mixture components
MultivariateNormalDistribution[] components = new MultivariateNormalDistribution[2];
components[0] = new MultivariateNormalDistribution(new double[] { 0, 1 }, Matrix.Identity(2));
components[1] = new MultivariateNormalDistribution(new double[] { 1, 0 }, Matrix.Identity(2));
// Create an initial mixture
var mixture = new MultivariateMixture<MultivariateNormalDistribution>(components);
mixture.Fit(points, weights);
// Our model will be:
double mean00 = mixture.Components[0].Mean[0];
double mean01 = mixture.Components[0].Mean[1];
double mean10 = mixture.Components[1].Mean[0];
double mean11 = mixture.Components[1].Mean[1];
Assert.AreEqual(-0.11704994950834195, mean00, 1e-10);
Assert.AreEqual(0.11603470123007256, mean01, 1e-10);
Assert.AreEqual(0.11814483652855159, mean10, 1e-10);
Assert.AreEqual(-0.12029275652994373, mean11, 1e-10);
}
public void GaussianMixtureModelTest5()
{
Accord.Math.Tools.SetupGenerator(0);
MemoryStream stream = new MemoryStream(Resources.CircleWithWeights);
ExcelReader reader = new ExcelReader(stream, xlsx: false);
DataTable table = reader.GetWorksheet("Sheet1");
double[,] matrix = table.ToMatrix();
double[][] points = matrix.Submatrix(null, 0, 1).ToArray();
double[] weights = matrix.GetColumn(2);
GaussianMixtureModel gmm = new GaussianMixtureModel(2);
gmm.Compute(points, new GaussianMixtureModelOptions()
{
Weights = weights
});
Assert.AreEqual(-0.010550720353814949, gmm.Gaussians[0].Mean[0]);
Assert.AreEqual(0.40799698773355553, gmm.Gaussians[0].Mean[1]);
Assert.AreEqual(0.011896812071918696, gmm.Gaussians[1].Mean[0]);
Assert.AreEqual(-0.40400708592859663, gmm.Gaussians[1].Mean[1]);
}
private static void testColumns(ExcelReader target, bool xlsx)
{
string[] cols = target.GetColumnsList("Plan1");
Assert.AreEqual(3, cols.Length);
Assert.AreEqual("Header1", cols[0]);
Assert.AreEqual("Header2", cols[1]);
Assert.AreEqual("Header3", cols[2]);
cols = target.GetColumnsList("Plan2");
Assert.AreEqual(3, cols.Length);
if (target.Provider == "Microsoft.Jet.OLEDB.4.0")
{
Assert.AreEqual("F1", cols[0]);
Assert.AreEqual("F2", cols[1]);
Assert.AreEqual("F3", cols[2]);
}
else
{
Assert.AreEqual("1", cols[0]);
Assert.AreEqual("2", cols[1]);
Assert.AreEqual("3", cols[2]);
}
cols = target.GetColumnsList("Plan3");
Assert.AreEqual(2, cols.Length);
Assert.AreEqual("A", cols[0]);
Assert.AreEqual("B", cols[1]);
cols = target.GetColumnsList("Sheet1");
Assert.AreEqual(1, cols.Length);
Assert.AreEqual("F1", cols[0]);
}
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]);
}
/// <summary>
/// Импорт .xls
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void MenuFileOpen_Click(object sender, EventArgs e)
{
#region загрузка пользователем XLS
//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("Недостаточно данных");
// }
// else
// {
// this.dgvLearningSource.DataSource = tableSource;
// this.dgvTestingSource.DataSource = tableSource.Copy();
// // CreateScatterplot(graphInput, sourceMatrix);
// }
// }
// }
//}
#endregion
#region быстрая загрузка
string filename = ".//Resources//3.xls";
string extension = Path.GetExtension(filename);
ExcelReader db = new ExcelReader(filename, true, false);
//ЛИСТЫ
string[] sd = db.GetWorksheetList();
TableSelectDialog t = new TableSelectDialog(sd);
if (t.ShowDialog(this) == DialogResult.OK)
{
//заставка импорт
System.Windows.SplashScreen splashScreen = new System.Windows.SplashScreen("2.png");
splashScreen.Show(true);
DataTable tableSource = db.GetWorksheet(t.Selection);
double[,] sourceMatrix = tableSource.ToMatrix(out sourceColumns);
if (sourceMatrix.GetLength(1) == 2)
{
MessageBox.Show("Недостаточно данных");
}
else
{
this.dgvLearningSource.DataSource = tableSource;
this.dgvTestingSource.DataSource = tableSource.Copy();
// CreateScatterplot(graphInput, sourceMatrix);
}
}
# endregion
}
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;
}
}
}
private void openFileDialog1_FileOk(object sender, System.ComponentModel.CancelEventArgs e)
{
TrainingSample[] samples = null;
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)
{
var sampleTable = db.GetWorksheet(t.Selection);
samples = new TrainingSample[sampleTable.Rows.Count];
for (int i = 0; i < samples.Length; i++)
{
samples[i] = new TrainingSample();
samples[i].Sequence = new double[(sampleTable.Columns.Count - 1) / 2][];
for (int j = 0; j < samples[i].Sequence.Length; j++)
{
samples[i].Sequence[j] =
new double[]
{
(double)sampleTable.Rows[i][j] * 50,
(double)sampleTable.Rows[i][j+1] * 50
};
}
samples[i].Output = (int)(double)sampleTable.Rows[i][sampleTable.Columns.Count - 1] - 1;
}
}
}
else if (extension == ".xml")
{
using (var stream = openFileDialog.OpenFile())
{
XmlSerializer serializer = new XmlSerializer(typeof(TrainingSample[]));
samples = (TrainingSample[])serializer.Deserialize(stream);
}
}
dataGridView1.Rows.Clear();
for (int i = 0; i < samples.Length; i++)
{
var sequence = samples[i].Sequence;
var label = samples[i].Output + 1;
var bitmap = ToBitmap(sequence);
var row = dataGridView1.Rows.Add(bitmap, label, null);
dataGridView1.Rows[row].Tag = sequence;
}
}
