private void button_GetData_Click(object sender, EventArgs e)
{
double XAxisMin_Real = double.Parse(textBox__Xaxismin.Text);
double XAxisMax_Real = double.Parse(textBox__Xaxismax.Text);
double YAxisMax_Real = double.Parse(textBox__Yaxismax.Text);
double YAxisMin_Real = double.Parse(textBox__Yaxismin.Text);
Point_Num = DataX.Count;
double[,] Data = new double[Point_Num, 2];
for (int i = 0; i < Point_Num; i++)
{
Data[i, 0] = (Convert.ToDouble(DataX[i]) - XAxisMin.X) / (XAxisMax.X - XAxisMin.X) * (XAxisMax_Real - XAxisMin_Real) + XAxisMin_Real;
Data[i, 1] = (Convert.ToDouble(DataY[i]) - YAxisMin.Y) / (YAxisMax.Y - YAxisMin.Y) * (YAxisMax_Real - YAxisMin_Real) + YAxisMin_Real;
}
Microsoft.Office.Tools.Excel.Worksheet worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
string s = "Serise" + Convert.ToString(Series_Num) + "_X";
((Excel.Range)worksheet.Cells[1, (Series_Num - 1) * 2 + 1]).Value2 = s;
s = "Serise" + Convert.ToString(Series_Num) + "_Y";
((Excel.Range)worksheet.Cells[1, (Series_Num - 1) * 2 + 2]).Value2 = s;
Excel.Range c1 = (Excel.Range)worksheet.Cells[1 + 1, (Series_Num - 1) * 2 + 1];
Excel.Range c2 = (Excel.Range)worksheet.Cells[1 + Point_Num, (Series_Num - 1) * 2 + 2];
Excel.Range range = worksheet.get_Range(c1, c2);
range.Value = Data;
drawing = false;
}
private void chkNamedRange_Click(object sender, RibbonControlEventArgs e)
{
Microsoft.Office.Tools.Excel.Worksheet worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]);
string name = "chkNamedRange";
if (((RibbonCheckBox)sender).Checked)
{
Excel.Range selection = Globals.ThisAddIn.Application.Selection as Excel.Range;
if (selection != null)
{
worksheet.Controls.AddNamedRange(selection, name);
}
}
else
{
worksheet.Controls.Remove(name);
}
}
public DensityCurve()
{
InitializeComponent();
Bandwidth = double.Parse(textBox_Bandwidth.Text);
Graphic.RangeData(ref str, ref rows, ref cols);
worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell;
start_col = activecells.Column;
start_row = activecells.Row;
Max_Value = Double.MinValue;
Min_Value = Double.MaxValue;
for (int i = 1; i < rows; i++)
{
if (double.Parse(str[i, 0]) > Max_Value)
{
Max_Value = double.Parse(str[i, 0]);
}
if (double.Parse(str[i, 0]) < Min_Value)
{
Min_Value = double.Parse(str[i, 0]);
}
}
double Step = Bandwidth / Nstep;
int Nrows = Convert.ToInt32((Max_Value - Min_Value) / Step + 2);
double[,] data = new double[Nrows, 1];
double[,] x = new double[Nrows, 1];
double tempx;
for (int i = 0; i < Nrows; i++)
{
x[i, 0] = i * Step + Min_Value;
for (int j = 1; j < rows; j++)
{
tempx = (x[i, 0] - double.Parse(str[j, 0])) / Bandwidth;
data[i, 0] = data[i, 0] + 1 / ((rows - 1) * Bandwidth) * Math.Exp(-tempx * tempx / 2) / Math.Sqrt(2 * 3.124259);
}
}
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "X axis";
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).Value2 = "Y axis";
Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1];
Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 1];
Excel.Range range = worksheet.get_Range(c1, c2);
range.Value = x;
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2];
range = worksheet.get_Range(c1, c2);
range.Value = data;
string ChartOrder = "DensityCurve" + Convert.ToString(Nchart);
chart = worksheet.Controls.AddChart(300, 50, 450, 400, ChartOrder);
Nchart = Nchart + 1;
c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols + 1];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2];
chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns);
chart.ChartType = Excel.XlChartType.xlArea;
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Series Sseries2 = series.Item(2);
Sseries2.Delete();
Excel.Series Sseries = series.Item(1);
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 1];
Sseries.XValues = worksheet.get_Range(c1, c2);
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2];
Sseries.Values = worksheet.get_Range(c1, c2);
chart.HasLegend = false;
chart.HasTitle = false;
worksheet.Activate();
}
private async Task TransferWorksheetToDatabase()
{
var dictionary = new Dictionary <string, Tuple <int, int, Type> >();
Microsoft.Office.Tools.Excel.Worksheet worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
var excelApplication = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Excel.Application;
worksheet.CalculateMethod();
Range selectedRange = (excelApplication.Selection as Range);
var areas = selectedRange.Areas;
for (int i = 1; i <= areas.Count; i++)
{
if (areas.Item[i].Rows.Count > 1 && !areas.Item[i].MergeCells)
{
MessageBox.Show("Only one cell per column allowed!", null, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
var dataTable = new System.Data.DataTable((Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet).Name);
var cellCount = selectedRange.Cells.Count > selectedRange.EntireColumn.Areas.Count ? selectedRange.Cells.Count : selectedRange.EntireColumn.Areas.Count;
var areaCount = areas.Count;
bool useArea;
useArea = areaCount > 1 ? true : false;
int loops = useArea ? areaCount : cellCount;
for (int i = 1; i <= loops; i++)
{
string columnName = String.Empty;
int columnNameRow;
int columnNameColumn;
if (useArea)
{
if (areas.Item[i].MergeCells)
{
columnName = (areas.Item[i].Cells[1, 1]).Value;
}
else
{
columnName = areas.Item[i].Value;
}
columnNameRow = areas.Item[i].Row;
columnNameColumn = areas.Item[i].Column;
}
else
{
columnNameColumn = (selectedRange.Cells[i] as Range).Column;
columnNameRow = (selectedRange.Cells[i] as Range).Row;
columnName = (selectedRange.Cells[i] as Range).Value;
}
var dataType = ResolveType(worksheet.Cells[rowCorrection, columnNameColumn].Value, columnName);
if (dataType == null)
{
return;
}
try
{
dictionary.Add(columnName, new Tuple <int, int, Type>(columnNameRow, columnNameColumn, dataType));
}
catch (ArgumentException)
{
string newColumnName = columnName + "2";
int counter = 2;
while (dictionary.ContainsKey(newColumnName))
{
newColumnName = columnName + counter;
counter++;
}
dictionary.Add(newColumnName, new Tuple <int, int, Type>(columnNameRow, columnNameColumn, dataType));
columnName = newColumnName;
}
dataTable.Columns.Add(new DataColumn()
{
DataType = dataType,
ColumnName = columnName,
Caption = columnName,
AutoIncrement = false,
ReadOnly = false,
Unique = false
});
}
_dataBaseSettings.TableName = (Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet).Name;
try
{
excelApplication.Cursor = XlMousePointer.xlWait;
await _databaseService.CreateTable(dictionary);
for (int k = rowCorrection; k <= worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row; k++)
{
var row = dataTable.NewRow();
for (int i = 0; i < dictionary.Count; i++)
{
var cellValue = Parse(worksheet.Cells[k, dictionary.ElementAt(i).Value.Item2].Value, dictionary.ElementAt(i).Value.Item3);
row[dictionary.ElementAt(i).Key] = cellValue;
}
dataTable.Rows.Add(row);
await _databaseService.Insert(dataTable);
dataTable.Clear();
rowCounterLlabel.Label = k.ToString();
}
excelApplication.Cursor = XlMousePointer.xlDefault;
}
catch (Exception exception)
{
excelApplication.Cursor = XlMousePointer.xlDefault;
MessageBox.Show(exception.Message, null, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
private void Лист2_Startup(object sender, System.EventArgs e)
{
sheet = this.Base;
LoadOptions();
}
