private string getDataType(Range cell)
{
string result = "";
WorksheetFunction func = cell.Application.WorksheetFunction;
if (func.IsLogical(cell))
{
result = "boolean";
}
else if (func.IsNumber(cell))
{
try
{
Convert.ToDateTime(cell.Text);
result = "date";
}
catch
{
result = "decimal";
}
}
else //if (func.IsText(cell))
{
result = "string"; //default type is string
}
return(result);
}
public static void ExportResultsExcel(List <Result> results, List <Submission> submissions, double maxPoint,
int numOfQuestion)
{
//Open Excel
try
{
var excelApp = new Application();
object missing = Missing.Value;
try
{
excelApp.Visible = true;
excelApp.EnableAnimations = false;
//Open Workbook
var book = excelApp.Workbooks.Add(missing);
_wsf = excelApp.WorksheetFunction;
//Add Result Sheet
AddResultSheet(book.Worksheets[1], results, maxPoint, numOfQuestion);
//Add Detail Sheet
AddDetailSheet(book.Sheets.Add(After: book.Sheets[book.Sheets.Count]), results, numOfQuestion);
//Add Answer Sheet
AddAnswerPathSheet(book.Sheets.Add(After: book.Sheets[book.Sheets.Count]), submissions,
numOfQuestion);
//Add Analyze Sheet
AddDataAnalyzeSheet(book.Sheets.Add(After: book.Sheets[book.Sheets.Count]), book.Worksheets[1]);
//Saving file to location
book.Sheets[1].Select(missing);
excelApp.EnableAnimations = true;
}
// Should get by type of exception but chua co thoi gian research and debug. -> 2.0
catch (Exception e)
{
throw new Exception("Export failed.\n " + e.Message);
}
}
catch (Exception ex)
{
throw new Exception("You must install Office to export.\n" + ex.Message);
}
}
/// <summary>
/// Прогнозирование временного ряда алгоритмом ETS
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ForecastBt_Click(object sender, EventArgs e)
{
Application xl = new Application();
WorksheetFunction wsf = xl.WorksheetFunction;
dgv.DataSource = GetData(monthCalendar1).DefaultView;
if (dgv.Rows.Count == 0)
{
MessageBox.Show("Не загружены данные из базы", "Предупреждение", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
// если есть данные
var _OTS = GetData(monthCalendar2);
int ch = 0, ch1 = 1;
int count = dgv.RowCount;
// интервалы выборки
double[] x = new double[count];
// значения выборки
double[] y = new double[count];
_x = new double[24 * (monthCalendar2.SelectionRange.End.Subtract(monthCalendar2.SelectionRange.Start).Days + 1)];
_y = new double[_x.Length];
double[] otsFact = new double[_x.Length];
for (int k = 0; k < count; k++)
{
x[k] = Convert.ToDouble(dgv.Rows[k].Index + 1);
y[k] = Convert.ToDouble(dgv.Rows[k].Cells[1].Value);
ch++;
}
// получаем интервалы в часах для прогнозных точек
for (int i = 0; i < _x.Length; i++)
{
_x[i] = ch1;
ch1++;
}
// запускаем алгоритм прогнозирования ETS
for (int i = 0; i < _y.Length; i++)
{
_y[i] = Math.Round(wsf.Forecast_ETS(_x[i] + Convert.ToDouble(x.Length), y, x, 1, 1), 3);
otsFact[i] = Convert.ToDouble(_OTS.Rows[i].Field <Decimal>("OTS"));
Diagram.Series[0].Points.Add(otsFact[i]);
Diagram.Series[1].Points.Add(_y[i]);
}
}
public ExcelWorkSheetFuncions()
{
xl = new Application();
wsf = xl.WorksheetFunction;
}
