public void LoadData()
{
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
try
{
Microsoft.Office.Interop.Excel.Workbook ObjWorkBook = ObjExcel.Workbooks.Open(Directory.GetCurrentDirectory() + @"\" + "" + path + "", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet;
ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];
var lastCell = ObjWorkSheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
//MessageBox.Show(lastCell.Column.ToString());//Столбцы
//MessageBox.Show(lastCell.Row.ToString());//Строки
//strok = lastCell.Row;
//arr = new string[lastCell.Column, lastCell.Row];
//arr = new string[2, lastCell.Row];
int iLastRow = ObjWorkSheet.Cells[ObjWorkSheet.Rows.Count, "A"].End[Microsoft.Office.Interop.Excel.XlDirection.xlUp].Row; //последняя заполненная строка в столбце А
//MessageBox.Show(iLastRow.ToString());
arr = new string[2, iLastRow];
strok = iLastRow;
var arrData = (object[, ])ObjWorkSheet.Range["A1:B" + iLastRow].Value;
//MessageBox.Show(arrData.Length.ToString());
string a1 = "";
string a2 = "";
for (int d = 1; d < iLastRow + 1; d++)
{
for (int i = 1; i < 3; i++)
{
//MessageBox.Show(arrData.GetValue(d, i).ToString());
// MessageBox.Show(arrData.GetValue(d, i).ToString().Length.ToString());
arr[i - 1, d - 1] = arrData.GetValue(d, i).ToString();
if (i == 1)
{
a1 = arrData.GetValue(d, i).ToString();
}
else
{
a2 = arrData.GetValue(d, i).ToString();
}
}
//MessageBox.Show(a1 + " " + a2);
}
ObjExcel.Quit();
messageBlock.Text = "Loading complete! Number of loading items: " + ((arr.Length / 2)).ToString();
ShowMeCards();
}
catch (Exception ex)
{
ObjExcel.Quit();
MessageBox.Show(ex.Message.ToString());
}
finally
{
ObjExcel.Quit();
}
}
public void Dispose()
{
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
public async void ListToExcel(List <FullTimeTable> list, string SelectedGroup)
{
List <DataTableFromDB> dataTableFromDBs = new List <DataTableFromDB>();
var responce = await client.GetAsync("GroupsTimetable/");
var LoadTimeTableCourse = responce.ResultAs <List <DataTableFromDB> >();
LoadTimeTableCourse = LoadTimeTableCourse.GroupBy(a => a.Group).Select(g => g.First()).ToList();
Microsoft.Office.Interop.Excel.Application exApp = new Microsoft.Office.Interop.Excel.Application();
exApp.Workbooks.Add();
for (int i = 0; i < LoadTimeTableCourse.Count; i++)
{
if (LoadTimeTableCourse[i].Group == SelectedGroup)
{
dataTableFromDBs = LoadTimeTableCourse;
}
}
Worksheet workSheet = (Worksheet)exApp.ActiveSheet;
workSheet.Cells[1, "A"] = "№ пары";
for (int i = 0; i < 6; i++)
{
workSheet.Cells[i + 2, "A"] = i;
}
workSheet.Cells[1, "B"] = "Monday";
workSheet.Cells[1, "C"] = "Tuedsday";
workSheet.Cells[1, "D"] = "Wendesday";
workSheet.Cells[1, "E"] = "Thursday";
workSheet.Cells[1, "F"] = "Friday";
for (int i = 0; i < list.Count; i++)
{
workSheet.Cells[i + 2, "B"] = list[i].Monday;
workSheet.Cells[i + 2, "C"] = list[i].Tuesday;
workSheet.Cells[i + 2, "D"] = list[i].Wednesday;
workSheet.Cells[i + 2, "E"] = list[i].Thursday;
workSheet.Cells[i + 2, "F"] = list[i].Friday;
}
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.InitialDirectory = saveFileDialog.FileName;
workSheet.SaveAs(saveFileDialog.InitialDirectory + SelectedGroup + "TimeTable.xlsx");
MessageBox.Show("Данные были загружены.\nСохранено в C:/Users/USERNAME/Documents");
exApp.Quit();
}
private void button1_Click(object sender, EventArgs e)
{
Application excelApplication = new Excel.Application
{
Visible = true,
ScreenUpdating = true,
DecimalSeparator = ".",
ThousandsSeparator = ",",
UseSystemSeparators = false
};
_Workbook workbook = excelApplication.Workbooks.Add();
_Worksheet sheet = workbook.Worksheets[1];
Range range = sheet.Range["A1"];
range.Formula = "1,234,567.89";
// re-set
excelApplication.UseSystemSeparators = true;
excelApplication.Quit();
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
#region OPEN
Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open(ofd.FileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Worksheet sheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
Excel.Range range = sheet.UsedRange;
#endregion
double[,] arrExel = new double[range.Rows.Count, range.Columns.Count];
arrCountry = new string[range.Columns.Count];
#region PROCESS
int rex = 2;
for (int row = 1; row <= range.Rows.Count - 1; row++)
{
for (int col = 1; col <= range.Columns.Count; col++)
{
if (row == 1)
{
string country = (range.Cells[1, col + 1] as Excel.Range).Value;
arrCountry[col - 1] = country;
}
double num = (range.Cells[rex, col] as Excel.Range).Value2;
arrExel[row - 1, col - 1] = num;
}
rex++;
}
#endregion
int rows = range.Rows.Count - 1;
int columns = range.Columns.Count - 1;
#region RELEASE
workbook.Close(true, null, null);
app.Quit();
releaseObject(sheet);
releaseObject(workbook);
releaseObject(app);
#endregion
arrYears = new int[rows];
for (int i = 0; i < rows; i++)
{
arrYears[i] = (int)arrExel[i, 0];
}
//Вспомогательный массив для дальнейшего нахождения вероятностных цепочек
double[] sum = new double[rows];
for (int i = 0; i < rows; i++)
{
for (int j = 1; j < columns + 1; j++)
{
sum[i] += arrExel[i, j];
}
;
}
;
//Массив arrPi- Pkt, т. е. вероятности
//Массив состоит из вероятностных цепочек по 8 странам
//в каждый из моментов времени t
double[,] arrPi = new double[rows, columns];
for (int i = 0; i < rows; i++)
{
int k = 1;
for (int j = 0; j < columns; j++)
{
arrPi[i, j] = arrExel[i, k] / sum[i];
k++;
}
}
//Массив arrZ-Zkt=Pkt/P1t. Фиксируем скорость прироста
//по отношению к первой стране
double[,] arrZ = new double[rows, columns];
for (int i = 0; i < rows; i++)
{
for (int j = 1; j < columns; j++)
{
arrZ[i, j] = arrPi[i, j] / arrPi[i, 0];
}
}
//Массив arrMul- (Zkt+1)*(Zkt)
double[,] arrMul = new double[rows, columns];
for (int j = 1; j < columns; j++)
{
for (int i = 0; i < rows - 1; i++)
{
arrMul[i, j] = arrZ[i, j] * arrZ[i + 1, j];
}
}
//Массив arrMul2- Zkt^2
double[,] arrMul2 = new double[rows, columns];
for (int j = 1; j < columns; j++)
{
for (int i = 0; i < rows - 1; i++)
{
arrMul2[i, j] = arrZ[i, j] * arrZ[i, j];
}
}
//Массив sumMul- суммы (Zkt+1)*(Zkt) по странам
double[] sumMul = new double[columns];
for (int j = 1; j < columns; j++)
{
for (int i = 0; i < rows; i++)
{
sumMul[j] = sumMul[j] + arrMul[i, j];
}
}
//Массив sumMul2- суммы Zkt^2 по странам
double[] sumMul2 = new double[columns];
for (int j = 1; j < columns; j++)
{
for (int i = 0; i < rows; i++)
{
sumMul2[j] = sumMul2[j] + arrMul2[i, j];
}
}
//Массив arrY- Yk
double[] arrY = new double[columns];
arrY[0] = 1;// пример за стандарт первую территорию/популяцию
for (int j = 1; j < columns; j++)
{
arrY[j] = sumMul[j] / sumMul2[j];
}
//Массив arrIJ- Матрица взаимного влияния
double[,] arrIJ = new double[columns, columns];
for (int i = 0; i < columns; i++)
{
for (int j = 0; j < columns; j++)
{
if (i == j)
{
arrIJ[i, j] = 0;
}
else
{
arrIJ[i, j] = 1 - arrY[j] / arrY[i];
}
}
}
//Массив arrSumZ - сумма Zkt
double[] arrSumZ = new double[columns];
for (int j = 1; j < columns; j++)
{
for (int i = 0; i < rows; i++)
{
{
arrSumZ[j] = arrSumZ[j] + arrZ[i, j];
}
}
}
//Массив arrMulZ - arrSumZ[j]*arrY^11
double[] arrMulZ = new double[columns];
for (int j = 1; j < columns; j++)
{
arrMulZ[j] = arrSumZ[j] * System.Math.Pow(arrY[j], rows);
}
//Массив arrMultiplier
double[] arrMultiplier = new double[columns];
for (int j = 1; j < columns; j++)
{
arrMultiplier[j] = (1 - arrY[j] * arrY[j]) / (1 - System.Math.Pow(arrY[j], rows * 2));
}
//Массив arrZk0- Zk0
double[] arrZk0 = new double[columns];
for (int j = 1; j < columns; j++)
{
arrZk0[j] = arrMultiplier[j] * arrMulZ[j];
}
//P10 для нахождения начального состояния системы в терминах долей популяции
double P10 = 0;
for (int j = 1; j < columns; j++)
{
P10 = P10 + arrZk0[j];
}
P10 = 1 / (1 + P10);
//MessageBox.Show(P1t.ToString());
//Начальное состояние системы в терминах долей популяции Pk0
double[] arrPk0 = new double[columns];
for (int j = 1; j < columns; j++)
{
arrPk0[j] = P10 * arrZk0[j];
}
//Интерполяция P1t
double[] arrP1t = new double[rows + 16];
for (var i = 0; i < rows + 16; i++)
{
arrP1t[i] = 0;
for (var j = 1; j < columns; j++)
{
arrP1t[i] = arrP1t[i] + arrZk0[j] * System.Math.Pow(arrY[j], i);
}
arrP1t[i] = 1 / (1 + arrP1t[i]);
}
double[,] arrInterp = new double[rows + 16, columns];
string res1 = " ";
for (int j = 0; j < columns; j++)
{
for (int i = 0; i < rows + 16; i++)
{
if (j != 0)
{
arrInterp[i, j] = arrP1t[i] * arrZk0[j] * System.Math.Pow(arrY[j], i);
}
else
{
arrInterp[i, j] = arrP1t[i];
}
res1 = res1 + " " + arrInterp[i, j];
}
res1 = res1 + "\n" + "\n";
}
//const string template = "template.xlsm";
// Открываем книгу
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ObjWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet;
//Книга.
ObjWorkBook = ObjExcel.Workbooks.Add(System.Reflection.Missing.Value);
//Таблица.
ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];
//Значения [y - строка,x - столбец]
int key = 1;
int loop = 1;
for (int j = 0; j < columns; j++)
{
for (int i = 0; i < rows + 16; i++)
{
ObjWorkSheet.Cells[key, loop] = arrInterp[i, j];
key++;
}
key = 1;
loop++;
}
ObjExcel.Visible = true;
ObjExcel.UserControl = true;
// MessageBox.Show(res1.ToString());
double res = 0;
for (int j = 0; j < columns; j++)
{
res = res + ' ' + arrInterp[1, j];
}
// MessageBox.Show(res.ToString());
points = new List <MyPoint> [columns];
for (int i = 0; i < columns; i++)
{
points[i] = new List <MyPoint>();
}
float miny = float.MaxValue, maxy = float.MinValue;
for (int i = 0; i < rows + 16; i++)
{
for (int j = 0; j < columns; j++)
{
if ((float)arrInterp[i, j] < miny)
{
miny = (float)arrInterp[i, j];
}
if ((float)arrInterp[i, j] > maxy)
{
maxy = (float)arrInterp[i, j];
}
}
}
size = new List <Size> [1];
size[0] = new List <Size>();
size[0].Add(new Size(miny, maxy));
float сoeffX = 500 / ((float)rows + 16);
float сoeffY = 240 / maxy;
for (int i = 0; i < rows + 16; i++)
{
points[0].Add(new MyPoint(35 + i * сoeffX, 270 - (float)(arrP1t[i]) * сoeffY));
}
//float ex = 270 - (float)(arrP1t[15]) * сoeffY;
//MessageBox.Show(ex.ToString());
for (int j = 1; j < columns; j++)
{
for (int i = 0; i < rows + 16; i++)
{
points[j].Add(new MyPoint(35 + i * сoeffX, 270 - (float)(arrInterp[i, j] * сoeffY)));
}
}
flag = true;
Invalidate();
}
}
private void button1_Click(object sender, EventArgs e)
{
KillProcess("excel");
OpenFileDialog oFile = new OpenFileDialog();
oFile.Filter = @"Word文件(*.doc,*.docx)|*.doc;*.docx";
if (oFile.ShowDialog() == DialogResult.OK)
{
object oFileName = oFile.FileName;
//object oFileName = @"D:\图片\test.docx";
object oReadOnly = true;
object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Word._Document oDoc;
Microsoft.Office.Interop.Word.Application oWord = new Microsoft.Office.Interop.Word.Application();
oWord.Visible = true;//只是为了方便观察
oDoc = oWord.Documents.Open(ref oFileName, ref oMissing, ref oReadOnly, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing, ref oMissing);
string tableMessage = "";
string tmp;
string path = AppDomain.CurrentDomain.BaseDirectory + "生成的excel文件";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string oExcelName = path + "\\" + "生成的excel" + ".xlsx";
object oRead = false;
object oMiss = System.Reflection.Missing.Value;
var oExcel = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel.Workbook xBook = oExcel.Workbooks.Open(oExcelName, oMiss, oRead, oMiss, oMiss, oMiss, oMiss, oMiss, oMiss, oMiss, oMiss, oMiss,oMiss, oMiss, oMiss);
Microsoft.Office.Interop.Excel.Workbook xBook = oExcel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xBook.ActiveSheet;
//Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets.get_Item(oDoc.Tables.Count);
// Microsoft.Office.Interop.Excel._Worksheet xSt1 = (Microsoft.Office.Interop.Excel._Worksheet)xBook.Sheets.Add(Missing.Value, ws, Missing.Value, Missing.Value);
if (oDoc.Tables.Count <= 0)
{
return;
}
if (oDoc.Tables.Count > 1)
{
ws = xBook.Sheets.Add(Missing.Value, ws, oDoc.Tables.Count - 1, Missing.Value);
}
Microsoft.Office.Interop.Excel.Sheets shs = oExcel.Sheets;
for (int tablePos = 1; tablePos <= oDoc.Tables.Count; tablePos++)
{
if (oDoc.Tables.Count <= 1)
{
}
else
{
ws = xBook.Sheets[tablePos];;
}
Microsoft.Office.Interop.Word.Table nowTable = oDoc.Tables[tablePos];
int n = oDoc.Tables[tablePos].Rows.Count;
for (int rowPos = 1; rowPos <= nowTable.Rows.Count; rowPos++)
{
for (int columPos = 1; columPos <= nowTable.Columns.Count; columPos++)
{
Microsoft.Office.Interop.Excel.Range rang = (Microsoft.Office.Interop.Excel.Range)ws.Cells[rowPos, columPos];
//if ((bool)rang.MergeCells==false)
//{
tableMessage = nowTable.Cell(rowPos, columPos).Range.Text.Trim();
tmp = tableMessage.Replace("\r\a", "");
ws.Cells[rowPos, columPos] = tmp;
//}
}
}
}
ws.SaveAs(oExcelName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
MessageBox.Show(oExcelName + @"已成功生成" + oDoc.Tables.Count.ToString() + @"个表格");
oWord.Quit(ref oMissing, ref oMissing, ref oMissing);
oExcel.Quit();
}
}
public IActionResult Result(double TempVhod, double FeOVhod, double TempExit, double Osnovnoct, double FeOExit, int dsfsdfdsfdsf)
{
ViewData["Message"] = "Your application description page.";
RashetShlak cls = new RashetShlak(TempVhod, FeOVhod, TempExit, Osnovnoct, FeOExit);
ViewBag.rashet = cls;
// Расчеты
#region расчеты СЛАУ
string[] str = new string[4];
int p = 0;
XmlReader reader = XmlReader.Create(@"phones_.xml");
XmlDocument doc = new XmlDocument();
XmlReaderSettings settings = new XmlReaderSettings();
//settings.
//doc = new XmlReader();
{
//doc.Load("phones_.xml");
doc.Load(reader);
foreach (XmlNode node in doc.SelectNodes("phones"))
{
foreach (XmlNode child in node.ChildNodes)
{
str[p] = (string.Format(child.InnerText)).ToString();
p++;
}
}
}
Char delimiter = '/';
double[] x = new double[6];
double[,] y = new double[3, 6];
for (int u = 0; u < 4; u++)
{
String[] subn = str[u].Split(delimiter);
if (u == 0)
{
for (int j = 0; j < 6; j++)
{
x[j] = double.Parse(subn[j]);
}
}
else
{
for (int j = 0; j < 6; j++)
{
y[u - 1, j] = double.Parse(subn[j]);
}
}
}
double[,] results = new double[3, 6];
SolverContext context = SolverContext.GetContext();
for (int i = 0; i < y.GetLength(0); i++)
{
Decision a = new Decision(Domain.Real, "a");
Decision b = new Decision(Domain.Real, "b");
Decision c = new Decision(Domain.Real, "c");
Decision d = new Decision(Domain.Real, "d");
Decision e = new Decision(Domain.Real, "e");
Decision f = new Decision(Domain.Real, "f");
Microsoft.SolverFoundation.Services.Model model = context.CreateModel();
model.AddDecisions(a, b, c, d, e, f);
model.AddConstraint("eqA1", y[i, 0] == ((Math.Pow(x[0], 1)) * a + (Math.Pow(x[0], 2)) * b + (Math.Pow(x[0], 3)) * c + (Math.Pow(x[0], 4)) * d + (Math.Pow(x[0], 5)) * e + f));
model.AddConstraint("eqA2", y[i, 1] == ((Math.Pow(x[1], 1)) * a + (Math.Pow(x[1], 2)) * b + (Math.Pow(x[1], 3)) * c + (Math.Pow(x[1], 4)) * d + (Math.Pow(x[1], 5)) * e + f));
model.AddConstraint("eqA3", y[i, 2] == ((Math.Pow(x[2], 1)) * a + (Math.Pow(x[2], 2)) * b + (Math.Pow(x[2], 3)) * c + (Math.Pow(x[2], 4)) * d + (Math.Pow(x[2], 5)) * e + f));
model.AddConstraint("eqA4", y[i, 3] == ((Math.Pow(x[3], 1)) * a + (Math.Pow(x[3], 2)) * b + (Math.Pow(x[3], 3)) * c + (Math.Pow(x[3], 4)) * d + (Math.Pow(x[3], 5)) * e + f));
model.AddConstraint("eqA5", y[i, 4] == ((Math.Pow(x[4], 1)) * a + (Math.Pow(x[4], 2)) * b + (Math.Pow(x[4], 3)) * c + (Math.Pow(x[4], 4)) * d + (Math.Pow(x[4], 5)) * e + f));
model.AddConstraint("eqA6", y[i, 5] == ((Math.Pow(x[5], 1)) * a + (Math.Pow(x[5], 2)) * b + (Math.Pow(x[5], 3)) * c + (Math.Pow(x[5], 4)) * d + (Math.Pow(x[5], 5)) * e + f));
Solution solution = context.Solve();
string result = solution.GetReport().ToString();
results[i, 0] = a.ToDouble();
results[i, 1] = b.ToDouble();
results[i, 2] = c.ToDouble();
results[i, 3] = d.ToDouble();
results[i, 4] = e.ToDouble();
results[i, 5] = f.ToDouble();
context.ClearModel();
}
double[] temps = { cls.TempVhod, cls.TempExit };
double[,] koefs = new double[2, 3];
for (int i = 0; i < 2; i++)
{
Decision a = new Decision(Domain.Real, "a");
Decision b = new Decision(Domain.Real, "b");
Decision c = new Decision(Domain.Real, "c");
Microsoft.SolverFoundation.Services.Model model = context.CreateModel();
model.AddDecisions(a, b, c);
double ta = results[0, 5] +
results[0, 0] * Math.Pow(temps[i], 1) +
results[0, 1] * Math.Pow(temps[i], 2) +
results[0, 2] * Math.Pow(temps[i], 3) +
results[0, 3] * Math.Pow(temps[i], 4) +
results[0, 4] * Math.Pow(temps[i], 5);
double tb = results[1, 5] +
results[1, 0] * Math.Pow(temps[i], 1) +
results[1, 1] * Math.Pow(temps[i], 2) +
results[1, 2] * Math.Pow(temps[i], 3) +
results[1, 3] * Math.Pow(temps[i], 4) +
results[1, 4] * Math.Pow(temps[i], 5);
double tc = results[2, 5] +
results[2, 0] * Math.Pow(temps[i], 1) +
results[2, 1] * Math.Pow(temps[i], 2) +
results[2, 2] * Math.Pow(temps[i], 3) +
results[2, 3] * Math.Pow(temps[i], 4) +
results[2, 4] * Math.Pow(temps[i], 5);
//MessageBox.Show(ta.ToString() + "..." + tb.ToString() + "..." + tc.ToString());
model.AddConstraint("eqA1", ta == 0 * a + 0 * b + c);
model.AddConstraint("eqA2", tb == 6.0 * a + 36.0 * b + c);
model.AddConstraint("eqA3", tc == 12.0 * a + 144.0 * b + c);
Solution solution = context.Solve();
// string result = solution.GetReport().ToString();
koefs[i, 0] = a.ToDouble();
koefs[i, 1] = b.ToDouble();
koefs[i, 2] = c.ToDouble();
context.ClearModel();
}
#endregion
cls.VisVhod = koefs[0, 2] + koefs[0, 0] * cls.FeOVhod + koefs[0, 1] * cls.FeOVhod * cls.FeOVhod;
cls.VisExit = koefs[1, 2] + koefs[1, 0] * cls.FeOExit + koefs[1, 1] * cls.FeOExit * cls.FeOExit;
cls.VisOcnVhod = cls.VisVhod + 196 * (cls.Osnovnoct - 1);
cls.VisOsnExit = cls.VisExit + 4 * (cls.Osnovnoct - 1);
cls.KoffB = (Math.Log10(Math.Log10(cls.VisOsnExit)) - Math.Log10(Math.Log10(cls.VisOcnVhod))) / (cls.TempExit - cls.TempVhod);
cls.KoffA = Math.Log10(Math.Log10(cls.VisOcnVhod)) - cls.TempVhod * cls.KoffB;
cls.Vis1180 = Math.Pow(10, Math.Pow(10, cls.KoffA + cls.KoffB * cls.TempVhod));
cls.Vis1200 = Math.Pow(10, Math.Pow(10, cls.KoffA + cls.KoffB * 1200));
cls.Vis1250 = Math.Pow(10, Math.Pow(10, cls.KoffA + cls.KoffB * 1250));
cls.Vis1275 = Math.Pow(10, Math.Pow(10, cls.KoffA + cls.KoffB * 1275));
cls.Vis1300 = Math.Pow(10, Math.Pow(10, cls.KoffA + cls.KoffB * 1300));
cls.TempKristal = (cls.KoffA - Math.Log10(Math.Log10(25))) / ((-1) * cls.KoffB);
#region Excel
// Открываем приложение
application = new Application
{
DisplayAlerts = false
};
// Файл шаблона
const string template = "shlak2.xlsx";
// Открываем книгу
workBook = application.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, template));
// Получаем активную таблицу
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];//.get_Item(2);
// Записываем данные
worksheet.Range["C22"].Value = cls.TempVhod;
worksheet.Range["C23"].Value = cls.FeOVhod;
worksheet.Range["C24"].Value = cls.TempExit;
worksheet.Range["C25"].Value = cls.FeOExit;
worksheet.Range["C26"].Value = cls.Osnovnoct;
//ВВОД данных и р-т
double VisVhodEX = double.Parse(worksheet.Range["C27"].Value.ToString());
double VisExitEX = double.Parse(worksheet.Range["C28"].Value.ToString());
double VisOcnVhodEX = double.Parse(worksheet.Range["C29"].Value.ToString());
double VisOsnExitEX = double.Parse(worksheet.Range["C30"].Value.ToString());
double KoffAEX = double.Parse(worksheet.Range["C31"].Value.ToString());
double KoffBEX = double.Parse(worksheet.Range["C32"].Value.ToString());
double Vis1180EX = double.Parse(worksheet.Range["C33"].Value.ToString());
double Vis1200EX = double.Parse(worksheet.Range["C34"].Value.ToString());
double Vis1250EX = double.Parse(worksheet.Range["C35"].Value.ToString());
double Vis1275EX = double.Parse(worksheet.Range["C36"].Value.ToString());
double Vis1300EX = double.Parse(worksheet.Range["C37"].Value.ToString());
double TempKristalEX = double.Parse(worksheet.Range["C38"].Value.ToString());
// Показываем приложение
workBook.Save();
cls.pogr = Math.Round((((Math.Abs(((Math.Abs(VisVhodEX) - Math.Abs(cls.VisVhod)) / Math.Abs(VisVhodEX))) +
Math.Abs(((Math.Abs(VisExitEX) - Math.Abs(cls.VisExit)) / Math.Abs(VisExitEX))) +
Math.Abs(((Math.Abs(VisOcnVhodEX) - Math.Abs(cls.VisOcnVhod)) / Math.Abs(VisOcnVhodEX))) +
Math.Abs(((Math.Abs(VisOsnExitEX) - Math.Abs(cls.VisOsnExit)) / Math.Abs(VisOsnExitEX))) +
Math.Abs(((Math.Abs(Vis1250EX) - Math.Abs(cls.Vis1250)) / Math.Abs(Vis1250EX))) +
Math.Abs(((Math.Abs(Vis1275EX) - Math.Abs(cls.Vis1275)) / Math.Abs(Vis1275EX))) +
Math.Abs(((Math.Abs(Vis1300EX) - Math.Abs(cls.Vis1300)) / Math.Abs(Vis1300EX))) +
Math.Abs(((Math.Abs(TempKristalEX) - Math.Abs(cls.TempExit)) / Math.Abs(TempKristalEX))) +
Math.Abs(((Math.Abs(KoffAEX) - Math.Abs(cls.KoffA)) / Math.Abs(KoffAEX))) +
Math.Abs(((Math.Abs(KoffBEX) - Math.Abs(cls.KoffB)) / Math.Abs(KoffBEX))) +
Math.Abs(((Math.Abs(Vis1180EX) - Math.Abs(cls.Vis1180)) / Math.Abs(Vis1180EX))) +
Math.Abs(((Math.Abs(Vis1200EX) - Math.Abs(cls.Vis1200)) / Math.Abs(Vis1200EX))))) / 12) * 100, 2);
#endregion
workBook.Close(false, Type.Missing, Type.Missing);
application.Quit();
return(View());
}
private static void FindExcel(Excel.Application oXL, string Wfile)
{
Microsoft.Office.Interop.Excel.Workbook oWB = null;
Microsoft.Office.Interop.Excel.Worksheet oSheet = null;
Application _excelApp = new Application();
Workbook workBook = _excelApp.Workbooks.Open(Wfile);
int numSheets = workBook.Sheets.Count;
//until a the file is added or until it runs out of sheets to check
while (numSheets > 0 && filesThatConstainSSN.Contains(Wfile) != true)
{
try
{
object missing = System.Reflection.Missing.Value;
oWB = oXL.Workbooks.Open(Wfile, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing);
//used for number of worksheets
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[numSheets];
//gets range of cells where format is similar
Microsoft.Office.Interop.Excel.Range oRng = GetSpecifiedRange(oSheet);
//checks ti see if anything exists, makes a string then checks exact format
if (oRng != null)
{
string str = oRng.Text.ToString();
FindTextDoc(str, Wfile);
}
}
//closes up if there is an exception
catch (Exception)
{
oXL.DisplayAlerts = false;
workBook.Close(null, null, null);
oXL.Workbooks.Close();
oXL.Quit();
if (oSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
}
if (oXL != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
}
oSheet = null;
workBook = null;
oXL = null;
GC.Collect();
}
numSheets--;
}
//final cleanup
oXL.DisplayAlerts = false;
workBook.Close(null, null, null);
oXL.Workbooks.Close();
oXL.Quit();
if (oSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
}
if (oXL != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
}
oSheet = null;
workBook = null;
oXL = null;
GC.Collect();
}
public void GenerateExcel(DataTable DtIN, string path, string name)
{
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.DisplayAlerts = false;
excel.Visible = false;
workBook = excel.Workbooks.Add(Type.Missing);
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
workSheet.Name = "Reporte Pedidos";
System.Data.DataTable tempDt = DtIN;
//dgExcel.ItemsSource = tempDt.DefaultView;
workSheet.Cells.Font.Size = 11;
int rowcount = 1;
for (int i = 1; i <= tempDt.Columns.Count; i++) //taking care of Headers.
{
string colname = "";
switch (tempDt.Columns[i - 1].ColumnName)
{
case "Name": colname = "Nombre"; break;
case "Items": colname = "Lista de Productos"; break;
case "Status": colname = "Estado"; break;
case "Total": colname = "Total en Pesos"; break;
case "Date": colname = "Fecha"; break;
case "Time": colname = "Hora"; break;
case "dateTime": colname = "Generado al: "; break;
//Parte para item popular
case "Description": colname = "Descripcion del Producto"; break;
case "Price": colname = "Precio en Pesos"; break;
case "TotalInPedidos": colname = "Total Veces encontrado en Pedidos"; break;
default: colname = tempDt.Columns[i - 1].ColumnName; break;
}
workSheet.Cells[1, i] = colname; //tempDt.Columns[i - 1].ColumnName;
}
foreach (System.Data.DataRow row in tempDt.Rows) //taking care of each Row
{
rowcount += 1;
for (int i = 0; i < tempDt.Columns.Count; i++) //taking care of each column
{
workSheet.Cells[rowcount, i + 1] = row[i].ToString();
}
}
cellRange = workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[rowcount, tempDt.Columns.Count]];
cellRange.EntireColumn.AutoFit();
string[] paths = { string.Format(@"{0}", path), name };
workBook.SaveAs(System.IO.Path.Combine(paths));
//workBook.SaveAs(System.IO.Path.Combine(@"C:\InventoryApp\","Excel book Name "));
workBook.Close();
excel.Quit();
}
catch (Exception ex)
{
MessageBox.Show("Error creando el report: " + ex.Message);
}
}
public void ExportSurveryData()
{
double pos = 1;
//创建Excel文件
string filePath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Desktop) + "\\长沙商业查勘数据.xlsx";
if (!File.Exists(filePath))
{
Excel.Application excel = new Excel.Application(); //Execl的操作类
Excel.Workbook bookDest = excel.Workbooks.Add(Missing.Value);
excel.Application.Workbooks.Add(true);
bookDest.SaveAs(filePath);
excel.Quit();
excel = null;
GC.Collect();//垃圾回收
}
else
{
MessageBox.Show("已存在文件：" + filePath);
return;
}
//table表头
DataTable dt = new DataTable();
dt.Columns.Add("标的物");
//头部行的字段名称
DataRow drHead = dt.NewRow();
dt.Rows.InsertAt(drHead, 1);
List<decimal> lstFormId = exportData.ExportSurveryData().Select(s => s.INSTANCE_ID).Distinct().ToList();
foreach (var formId in lstFormId)
{
if (formId == 0)
continue;
List<SurveryDataModel> lst = exportData.ExportSurveryData().Where(s => s.INSTANCE_ID == formId).ToList();
FormViewModel formInstance = exportData.GetFormInstanceById((int)formId);
//DataTable dt = new DataTable();
//表头
//dt.Columns.Add("标的物");
foreach (var formLabelDTO in formInstance.LABLELIST)
{
if (!dt.Columns.Contains(formLabelDTO.FORM_LABEL_ID.ToString()))
{
dt.Columns.Add(formLabelDTO.FORM_LABEL_ID.ToString());
drHead[formLabelDTO.FORM_LABEL_ID.ToString()] = formInstance.LABLELIST.Where(f => f.FORM_LABEL_ID == Convert.ToDecimal(formLabelDTO.FORM_LABEL_ID)).FirstOrDefault().LABEL_NAME_CHS;
}
}
//数据
foreach (var surveryDataModel in lst)
{
DataRow dr = dt.NewRow();
List<LabelDataDTO> lstLabelData = JsonTools.JsonToObject2<List<LabelDataDTO>>(surveryDataModel.CONTENTS);
foreach (var labelDataDto in lstLabelData)
{
if (dt.Columns.Contains(labelDataDto.NAME))
dr[labelDataDto.NAME] = labelDataDto.VALUE;
}
dr["标的物"] = surveryDataModel.OBJECT_NAME;
dt.Rows.Add(dr);
}
//DoExport(dt,filePath, formId.ToString());
//bool flag= DoExport(dt);
//SetTextMessage(pos/lstFormId.Count);
//pos++;
//richTextBox1.Text += "表单ID：" + formId + " 数据导出完成.\n";
}
DoExport(dt, filePath, "长沙商业查勘");
MessageBox.Show("导出成功！");
//progressBar1.Value = 100;
//头部信息
//DataRow drHead = dt.NewRow();
//foreach (DataColumn dc in dt.Columns)
//{
// if (dc.ColumnName != "标的物")
// {
// drHead[dc.Caption] =
// formInstance.LABLELIST.Where(f => f.FORM_LABEL_ID == Convert.ToDecimal(dc.Caption)).FirstOrDefault().LABEL_NAME_CHS;
// }
// else
// {
// drHead[dc.Caption] = "标的物";
// }
//}
//dt.Rows.InsertAt(drHead, 0);
}
private void DoExport(DataTable dt, string toFileName, string strSheetName)
{
Excel.Application excel = new Excel.Application(); //Execl的操作类
//读取保存目标的对象
Excel.Workbook bookDest = excel.Workbooks._Open(toFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//打开要导出到的Execl文件的工作薄。--ps:关于Missing类在这里的作用，我也不知道...囧
Excel.Worksheet sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;//给工作薄添加一个Sheet
sheetDest.Name = strSheetName;//自己定义名字O(∩_∩)O哈哈~
int rowIndex = 1;
int colIndex = 0;
excel.Application.Workbooks.Add(true);//这句不写不知道会不会报错
//foreach (DataColumn col in dt.Columns)
//{
// colIndex++;
// sheetDest.Cells[1, colIndex] = col.ColumnName;//Execl中的第一列,把DataTable的列名先导进去
//}
//导入数据行
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
sheetDest.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
bookDest.Saved = true;
bookDest.Save();
excel.Quit();
excel = null;
GC.Collect();//垃圾回收
}
public void WriteExcelFile()
{
Application xlApp = new Microsoft.Office.Interop.Excel.Application();
object misValue = System.Reflection.Missing.Value;
string exeDir = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
Workbook xlWorkbook = xlApp.Workbooks.Open(System.IO.Path.Combine(exeDir, "excel\\manquants.xlsm"));
_Worksheet xlWorksheet = xlWorkbook.Sheets[1];
xlApp.Visible = true;
xlApp.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityByUI;
int i = 1;
string st = "";
foreach (Manquant manquant in _manquants)
{
bool flag = true;
bool flag1 = false;
bool flag2 = false;
bool flag3 = false;
while (flag)
{
try
{
i++;
flag3 = true;
if (st == "" || flag1)
{
st = manquant._nomPrep;
xlWorksheet.Cells[i++, 1].value2 = manquant._nomPrep;
flag1 = true;
}
else if (st != manquant._nomPrep || flag2)
{
st = manquant._nomPrep;
xlWorksheet.Cells[i++, 1].value2 = manquant._nomPrep;
flag2 = true;
}
xlWorksheet.Cells[i, 1].value2 = manquant._date + manquant._heure;
xlWorksheet.Cells[i, 2].value2 = manquant._lib + "\n" + manquant._ean;
xlWorksheet.Cells[i, 3].value2 = "=Transbar(" + manquant._ean + ")";
xlWorksheet.Cells[i, 4].value2 = manquant._loca;
xlWorksheet.Cells[i, 5].value2 = manquant._Prixvente;
xlWorksheet.Cells[i, 6].value2 = manquant._qtecmd;
xlWorksheet.Cells[i, 7].value2 = manquant._qteFact;
xlWorksheet.Cells[i, 8].value2 = manquant._Prep;
xlWorksheet.Cells[i, 9].value2 = manquant._Ncmd;
xlWorksheet.Cells[i, 10].value2 = manquant._nomClient;
flag = false;
}
catch (Exception)
{
if (flag1)
{
i--;
}
if (flag2)
{
i--;
}
if (flag3)
{
i--;
}
}
}
}
this._manquants = new List <Manquant>();
xlWorksheet.PageSetup.PrintArea = "A$1:I" + i;
xlWorkbook.PrintPreview();
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlWorksheet);
xlWorkbook.Close(false, misValue, misValue);
Marshal.ReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
public void ReadExcel(string path)
{ // path는 Excel파일의 전체 경로입니다.
// 예. D:\test\test.xslx
Excel.Application excelApp = null;
Excel.Workbook wb = null;
Excel.Worksheet ws = null;
string str1;
int num1;
string str3;
try
{
excelApp = new Excel.Application();
wb = excelApp.Workbooks.Open(Filename: path);
// path 대신 문자열도 가능합니다
// 예. Open(@"D:\test\test.xslx");
ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;
// 첫번째 Worksheet를 선택합니다.
Excel.Range rng = ws.UsedRange; // '여기'
// 현재 Worksheet에서 사용된 셀 전체를 선택합니다.
object[,] data = rng.Value;
// 열들에 들어있는 Data를 배열 (One-based array)로 받아옵니다. // -->, i = 0
for (int r = 1; r <= data.GetLength(0); r++)
{
for (int c = 1; c <= data.GetLength(1); c++)
{
if (data[r, c] == null)
{
continue;
}
else
{
if (c == 1)
{
str1 = (string)(rng.Cells[r, c] as Excel.Range).Value2; // 열과 행에 해당하는 데이터를 문자열로 반환
MessageBox.Show(str1);
}
else if (c == 2)
{
num1 = (int)(rng.Cells[r, c] as Excel.Range).Value2; // 열과 행에 해당하는 데이터를 문자열로 반환
string str2 = string.Format("{0}", num1);
MessageBox.Show(str2);
}
else
{
str3 = (string)(rng.Cells[r, c] as Excel.Range).Value2; // 열과 행에 해당하는 데이터를 문자열로 반환
MessageBox.Show(str3);
}
// Data 빼오기
// data[r, c] 는 excel의 (r, c) 셀 입니다.
// data.GetLength(0)은 엑셀에서 사용되는 행의 수를 가져오는 것이고,
// data.GetLength(1)은 엑셀에서 사용되는 열의 수를 가져오는 것입니다.
// GetLength와 [ r, c] 의 순서를 바꿔서 사용할 수 있습니다.
wb.Close(true);
excelApp.Quit();
}
}
// BuyDate buy = new BuyDate(string t, string l, string i, int lp, int hp, int pt);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
ReleaseExcelObject(ws);
ReleaseExcelObject(wb);
ReleaseExcelObject(excelApp);
}
}
public void InsertOp(int columnOp, int sheetOp) //конвертирует единицы измерения и перебрасывает левую часть таблицы до норм расхода времени (размеры заготовок, материал и веса)
{
int index = 8;
while (cls[sheetOp].getcell(index, 1) != null) //Проверяет первые колонки строк, начиная с 8-ой строки на первом листе, на заполненность
{
index++;
}
int ind = 0;
int star = 0;
marsh.Cells[8, 3] = 1; //Устанавливает количество в ячейке 8,3 в 1
int col = 1;
for (int i = begine + 1; i < sizeRows; i++) //Перебор по строкам и колонкам операций сверху вниз и слева направо. Начинаем с 8 до 21
{
if (clM.getcell(i, 11) != null) //Проверка на заполненность единиц измерения в 11-ой колонке
{
if (clM.getcell(i, 11).ToString() == "шт" && i != 8) //Норма расходов в штуках
{
ind++;
string s = clM.getcell(i, 10).ToString();
koef[ind] = koef[ind - 1] * Convert.ToInt32(s);
col = koef[ind];
marsh.Cells[i, 3] = 1;
star++;
}
if (clM.getcell(i, 11).ToString()[0] == '*') //Проверяет, являестя ли первый символ в единицах измерения звездочкой
{
if (star == 0 && begine >= 8)
{
ind += clM.getcell(i, 11).ToString().Length - 1;
}
star++;
ind--;
if (ind < 0)
{
MessageBox.Show("Неверно отмечены сборки. Проверте данные.", "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
objectExcel.Quit();
return;
}
col = koef[ind];
}
}
if (clM.getcell(i, columnOp) != null && !String.IsNullOrEmpty(clM.getcell(i, columnOp).ToString()))
{
string cll = clM.getcell(i, columnOp).ToString();
cll = cll.Replace('.', ',');
for (int m = 0; m < cll.Length; m++)
{
if (!Char.IsDigit(cll[m]) && cll[m] != ',' || cll.IndexOf(',') != cll.LastIndexOf(','))
{
MessageBox.Show("Неверный формат в ячейке " + i.ToString() + "," + columnOp.ToString(),
"Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
objectExcel.Quit();
return;
}
}
if (Convert.ToDouble(clM.getcell(i, columnOp)) > 0 && clM.getcell(i, 1) != null) //переносит на листы
{
sheet[sheetOp].Cells[index, 1] = marsh.Cells[i, 1];
sheet[sheetOp].Cells[index, 2] = marsh.Cells[i, 2];
for (int j = 3; j < 11; j++)
{
sheet[sheetOp].Cells[index, j] = marsh.Cells[i, j + 1];
}
sheet[sheetOp].Cells[index, 11] = col * Convert.ToDouble(clM.getcell(i, 3));
if (Convert.ToInt32('A') + columnOp - 1 > 90)
{
int c = Convert.ToInt32('A') + columnOp - 27;
char ch = Convert.ToChar(c);
string s = "=Маршрутка!A" + ch.ToString() + i.ToString() + "\n";
sheet[sheetOp].Cells[index, 12] = s;
}
else
{
int c = Convert.ToInt32('A') + columnOp - 1;
char ch = Convert.ToChar(c);
string s = "=Маршрутка!" + ch.ToString() + i.ToString() + "\n";
sheet[sheetOp].Cells[index, 12] = s;
}
//sheet[sheetOp].Cells[index, 12] = marsh.Cells[i, columnOp];
string str = "=K" + index + "*M$6\n";
sheet[sheetOp].Cells[index, 13] = str;
sheet[sheetOp].Cells[index, 17] = i;
index++;
}
}
}
if (ind > 0)
{
MessageBox.Show("Неверно отмечены сборки. Проверте данные.",
"Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
objectExcel.Quit();
return;
}
}
public UnitMarsh(string fileName)
{
flag = true;
fileMarsh = fileName;
objectExcel = new Application();
// bookMarch = OpenBook.Open(objectExcel, fileMarsh);
OpenExcelBook.OpenBook bk = new OpenExcelBook.OpenBook();
bookMarch = bk.OpenFile(objectExcel, fileMarsh);
marsh = (_Worksheet)bookMarch.Worksheets[1];
if (marsh.Name != "Маршрутка") //Если ИМЯ в данной строке не является Маршрутка
{
flag = false;
MessageBox.Show("Это не маршрутка", "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
else //Переходим сюда
{
sheet = new _Worksheet[size];
cls = new Class1[size];
begine = 7; //Cтрока с которой начинаем оброботку данных.
for (int i = 0; i < size; i++)
{
sheet[i] = (_Worksheet)bookMarch.Worksheets[i + 2];
cls[i] = new Class1(sheet[i]);
int rw = 8;
while (cls[i].getcell(rw, 17) != null) // Проверяет листы на наличие данных. Если хотя бы на одном из листов есть данные, то оброботка данных не происходит.
{
try
{
if (begine < Convert.ToInt32(cls[i].getcell(rw, 17)))
{
begine = Convert.ToInt32(cls[i].getcell(rw, 17)); //Устанавливает номер строки с которой начинать оброботку данных.????
}
rw++;
}
catch (Exception)
{
MessageBox.Show(
"Неверный формат: лист " + (i + 2).ToString() + ", строка " + rw.ToString(), "Ошибка",
MessageBoxButton.OK, MessageBoxImage.Error);
objectExcel.Quit();
return;
}
}
}
string str = "";
clM = new Class1(marsh);
sizeRows = 7;
while (str != "*")
{
sizeRows++;
if (sizeRows == 5000) // Проверка на наличие метки конца листа *
{
MessageBox.Show("Нет конца листа. Проверте данные.", "Ошибка", MessageBoxButton.OK,
MessageBoxImage.Error);
objectExcel.Quit();
return;
}
if (clM.getcell(sizeRows, 11) != null)
{
str = clM.getcell(sizeRows, 11).ToString();
}
}
for (int k = 0; k < koef.Capacity; k++)
{
koef.Add(Convert.ToInt32(clM.getcell(8, 10)));
}
}
}
public List <ObjectFull> ProcessFile(string filePath)
{
Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
var fileName = Path.GetFileName(filePath);
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
object[,] values = xlRange.Value2;
if (values == null)
{
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
return(null);
}
var tempList = new List <ObjectFull>();
for (int i = 1; i <= rowCount; i++)
{
try
{
string msgtype = ""; string transmissiontype = "";
string icao = "";
string tempdategenerate = "";
string temptimegenerate = "";
string tempdatelog = "";
string temptimelog = "";
string callsign = "";
string altitude = ""; string speed = ""; string track = ""; string latitude = "";
string longitude = "";
string verticalrate = ""; string squawk = "";
var te = xlRange.Cells[i, 1];
var xp = xlRange.Cells[i, 1].Value2;
msgtype = Convert.ToString(values[i, 1]);
transmissiontype = Convert.ToString(values[i, 2]);
if (transmissiontype.Trim() == "2" || transmissiontype.Trim() == "7" || transmissiontype.Trim() == "8")
{
continue;
}
icao = Convert.ToString(values[i, 5]);
tempdategenerate = Convert.ToString(values[i, 7]);
temptimegenerate = Convert.ToString(values[i, 8]);
tempdatelog = Convert.ToString(values[i, 9]);
temptimelog = Convert.ToString(values[i, 10]);
callsign = Convert.ToString(values[i, 11]);
altitude = Convert.ToString(values[i, 12]);
speed = Convert.ToString(values[i, 13]);
track = Convert.ToString(values[i, 14]);
latitude = Convert.ToString(values[i, 15]);
longitude = Convert.ToString(values[i, 16]);
verticalrate = Convert.ToString(values[i, 17]);
squawk = Convert.ToString(values[i, 18]);
double d1 = double.Parse(tempdategenerate);
DateTime dg = DateTime.FromOADate(d1);
double t1 = double.Parse(temptimegenerate);
DateTime tg = DateTime.FromOADate(t1);
double d2 = double.Parse(tempdatelog);
DateTime dl = DateTime.FromOADate(d2);
double t2 = double.Parse(temptimelog);
DateTime tl = DateTime.FromOADate(t2);
DateTime dateGenerate = new DateTime(dg.Year, dg.Month, dg.Day, tg.Hour, tg.Minute, tg.Second);
DateTime dateLog = new DateTime(dl.Year, dl.Month, dl.Day, tl.Hour, tl.Minute, tl.Second);
var newTargetObject = new ObjectFull();
newTargetObject.ICAO = icao;
newTargetObject.callsign = callsign;
newTargetObject.altitude = altitude;
newTargetObject.speed = speed;
newTargetObject.dategenerate = dateGenerate;
newTargetObject.datelog = dateLog;
newTargetObject.messageType = msgtype;
newTargetObject.transmissionType = transmissiontype;
newTargetObject.track = track;
newTargetObject.latitude = latitude;
newTargetObject.longitude = longitude;
newTargetObject.verticalrate = verticalrate;
newTargetObject.squawk = squawk;
tempList.Add(newTargetObject);
}
catch (Exception ex)
{
//MessageBox.Show(ex.InnerException.Message);
Console.WriteLine(ex.Message);
}
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
return(tempList);
}