public Worker(string fileName)
{
flag = true;
fileWorker = fileName;
objectExcel = new Application();
//bookWorker = OpenBook.Open(objectExcel, fileWorker);
OpenExcelBook.OpenBook bk = new OpenExcelBook.OpenBook();
bookWorker = bk.OpenFile(objectExcel, fileWorker);
sizeWork = bookWorker.Sheets.Count;
sheet = new _Worksheet[sizeWork];
clWork = new Class1[sizeWork];
if (bookWorker.Worksheets.Count >= sizeWork)
{
for (int i = 0; i < sizeWork; i++)
{
sheet[i] = (_Worksheet)bookWorker.Worksheets[i + 1];
clWork[i] = new Class1(sheet[i]);
}
if (clWork[0].getcell(2, 17).ToString() != "Номер строки маршрутки")
{
flag = false;
MessageBox.Show("Это не лист заданий", "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
}
}
public RunWorks(string fileName)
{
flag = true;
fileWorker = fileName;
objectExcel = new Application();
//bookWorker = OpenBook.Open(objectExcel, fileWorker);
OpenExcelBook.OpenBook bk = new OpenExcelBook.OpenBook();
bookWorker = bk.OpenFile(objectExcel, fileWorker);
sizeWork = bookWorker.Sheets.Count;
sheet = new _Worksheet[sizeWork];
clRun = new Class1[sizeWork];
if (bookWorker.Worksheets.Count >= sizeWork)
{
for (int i = 0; i < sizeWork; i++)
{
sheet[i] = (_Worksheet)bookWorker.Worksheets[i + 1];
clRun[i] = new Class1(sheet[i]);
}
}
if (clRun[0].getcell(2, 17).ToString() != "Стоимость")
{
flag = false;
MessageBox.Show("Это не лист выполненных заданий", "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
int[] ind = new int[sizeWork];
for (int i = 0; i < sizeWork; i++)
{
ind[i] = 5;
while (clRun[i].getcell(ind[i], 1) != null)
{
ind[i]++;
}
}
}
public void InitializeExcel()
{
xlApp = new Excel.Application();
xlWorkbook = xlApp.Workbooks.Open(PathToFile);
xlWorksheet = xlWorkbook.Sheets[1];
xlRange = xlWorksheet.UsedRange;
}
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 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();
}
public ExcelSelectionTracker(int excelThreadId)
{
_application = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
_application.SheetSelectionChange += OnNewSelection;
_excelThreadId = excelThreadId;
_procCwp = CwpProc;
_hHookCwp = WindowsInterop.SetWindowsHookEx(HookType.WH_CALLWNDPROC, _procCwp, (IntPtr)0, excelThreadId);
if (_hHookCwp == 0)
{
throw new Exception("Failed to hook WH_CALLWNDPROC");
}
}
private void button1_Click(object sender, EventArgs e)
{
Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
xlApp.DisplayAlerts = false;
string filePath = @"c:\Test\Test2.xlsx";
Workbook xlWorkBook = xlApp.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Sheets worksheets = xlWorkBook.Worksheets;
string newFileName = "newsheet";
string newFile = RandomString(4, false);
newFileName = newFileName + "_" + newFile;
var xlNewSheet = (Worksheet)worksheets.Add(worksheets[1], Type.Missing, Type.Missing, Type.Missing);
xlNewSheet.Name = newFileName;
xlNewSheet.Cells[1, 1] = "New sheet content";
xlNewSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlNewSheet.Select();
xlWorkBook.Save();
xlWorkBook.Close();
releaseObject(xlNewSheet);
releaseObject(worksheets);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("New Worksheet Created! Called:" + newFileName);
}
private void button2_Click(object sender, EventArgs e)
{
Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
xlApp.DisplayAlerts = false;
Workbook newWorkbook = xlApp.Workbooks.Add();
newWorkbook.SaveAs("ExcelTest.xlsx");
//Input cell value by row and column coordinate
// worksheet.Cell(1, 1).Value = "A1";
// //Input cell value by cell name
// worksheet.Cell("B1").Value = "B1";
// //Input cell value by row id and column name
// worksheet.Cell(1, "C").Value = "C1";
// workbook.SaveAs("CreateNew.xlsx");
// //Set Excel property values
//// workbook.Properties.Title = "Sample";
// //workbook.Properties.Author = "iDiTect";
// //workbook.Properties.Subject = "iDiTect.Excel Sample";
// //workbook.Properties.Keywords = "iDiTect.Excel";
// //workbook.Properties.Category = "iDiTect.Excel";
// //workbook.Properties.Company = "iDiTect.com";
// workbook.Save();
}
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 button6_Click(object sender, EventArgs e)
{
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("Sıra No");
dt.Columns.Add("Ulusal Yeterlilik Kodu");
dt.Columns.Add("ISCO 08");
dt.Columns.Add("Millî Eğitim Bakanlığına Bağlı Mesleki ve Teknik Eğitim Kurumlarınca Verilen Diplomalar (Alan/Dal/Bölüm-FOET Kodu)");
_Application excel = new Microsoft.Office.Interop.Excel.Application();
Workbook wb = excel.Workbooks.Open(@"C:\Users\Lenovo\Desktop\Nota\MYK\Muafiyet_Tablosu_2020-Rev.04.xlsx");
Worksheet ws = wb.Worksheets[1];
for (int j = 2; j < 105; j++)
{
var SiraNo = ws.Cells[j, 1].Value;
var UYK = ws.Cells[j, 2].Value;
var ISCOkodu = ws.Cells[j, 3].Value;
var MEB = (string)ws.Cells[j, 4].Value;
string[] ayirici = new string[] { ")," };
var MeslekFoetListe = MEB.Split(ayirici, StringSplitOptions.None);
foreach (var item in MeslekFoetListe)
{
var MeslekFoet = item;
dt.Rows.Add(SiraNo, UYK, ISCOkodu, MeslekFoet);
}
}
dataGridView1.DataSource = dt;
}
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)));
}
}
}
private void btImport_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog openfile = new OpenFileDialog();
openfile.DefaultExt = ".xlsx";
openfile.Filter = "(.xlsx)|*.xlsx";
var browsefile = openfile.ShowDialog();
if (browsefile == true)
{
txtFilePath.Text = openfile.FileName;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(txtFilePath.Text.ToString(), 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);;
Microsoft.Office.Interop.Excel.Range excelRange = excelSheet.UsedRange;
int row = 9;
int cell = 3;
//1 страница
int CounterLIST = xlWorkbook.Sheets.Count;
if (CounterLIST <= 4)
{
//Sotrydniki
int rowS = 13;
int cellS = 3;
// Создаем экземпляр Regex
Regex rg = new Regex(@"[А-Я]{1}[.]{1}[А-Я]{1}[.]{1} [А-Я]{1}([а-я]{0,17})?([a-z]{0,17})?");
_Worksheet list_11 = (_Worksheet)xlWorkbook.Sheets[1]; //Получаем 1 лист
Range xlRange_11 = list_11.UsedRange; //Получаем используемый сектор ячеек в листе
string authors;
while (rowS <= 96)
{
while (cellS <= 9)
{
authors = xlRange_11.Cells[rowS, cellS].Text;
// Получаем все совпадения
MatchCollection matchedAuthors = rg.Matches(authors);
/// Выводим всех подходящих авторов
foreach (Match match in matchedAuthors)
{
try
{
String value = match.ToString();
String Surname = value.Substring(0, 2);
String Name = value.Substring(2, 2);
String FirstName = value.Substring(5);
queryOfTables("INSERT INTO Employees values('" + Surname + "','" + Name + "' ,'" + FirstName + "' , 1 )");
}
catch
{
}
}
cellS++;
} //Sotrydniki
cellS = 3;
rowS++;
_Worksheet list_1 = (_Worksheet)xlWorkbook.Sheets[1]; //Получаем первый лист
Range xlRange_1 = list_1.UsedRange; //Получаем используемый сектор ячеек в листе
while (cell <= 9)
{
if (cell == 3)
{
if (xlRange_1.Cells[row, cell].value == null)
{
}
else
{
numberSpeciality = xlRange_1.Cells[row, cell].Text;// Таблица специальность
string[] wordss = numberSpeciality.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
int Counter = wordss.Count();
if (Counter == 2)
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
string numberSpecialitysecondd = wordss[1];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialitysecondd + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
else
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
}
}
if (cell == 6)
{
if (xlRange_1.Cells[row, cell].value == null)
{
}
else
{
numberSpeciality = xlRange_1.Cells[row, cell].Text;// Таблица специальность
string[] wordss = numberSpeciality.Split(new char[] { ',' });
// new char[] - массив символов-разделителей. Как меня поправили в
// комментариях, в данном случае достаточно написать text.Split(':')
int Counter = wordss.Count();
if (Counter == 2)
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
string numberSpecialitysecondd = wordss[1];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialitysecondd + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
else
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
}
}
if (cell == 9)
{
if (xlRange_1.Cells[row, cell].value == null)
{
}
else
{
numberSpeciality = xlRange_1.Cells[row, cell].Text;// Таблица специальность
string[] wordss = numberSpeciality.Split(new char[] { ',' });
// new char[] - массив символов-разделителей. Как меня поправили в
// комментариях, в данном случае достаточно написать text.Split(':')
int Counter = wordss.Count();
if (Counter == 2)
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
string numberSpecialitysecondd = wordss[1];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialitysecondd + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
else
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
}
}
cell++;
}
cell = 3;
}
}
else
{
}
//2 страница
if (CounterLIST <= 4)
{
//Sotrydniki
int rowS = 13;
int cellS = 3;
// Создаем экземпляр Regex
Regex rg = new Regex(@"[А-Я]{1}[.]{1}[А-Я]{1}[.]{1} [А-Я]{1}([а-я]{0,17})?([a-z]{0,17})?");
_Worksheet list_11 = (_Worksheet)xlWorkbook.Sheets[2]; //Получаем 2 лист
Range xlRange_11 = list_11.UsedRange; //Получаем используемый сектор ячеек в листе
string authors;
while (rowS <= 96)
{
while (cellS <= 9)
{
authors = xlRange_11.Cells[rowS, cellS].Text;
// Получаем все совпадения
MatchCollection matchedAuthors = rg.Matches(authors);
/// Выводим всех подходящих авторов
foreach (Match match in matchedAuthors)
{
try
{
String value = match.ToString();
String Surname = value.Substring(0, 2);
String Name = value.Substring(2, 2);
String FirstName = value.Substring(5);
queryOfTables("INSERT INTO Employees values('" + Surname + "','" + Name + "' ,'" + FirstName + "' , 1 )");
}
catch
{
}
}
cellS++;
}
cellS = 3;
rowS++;
} //Sotrydniki
_Worksheet list_2 = (_Worksheet)xlWorkbook.Sheets[2];//Получаем 2 лист
Range xlRange_2 = list_2.UsedRange;//Получаем используемый сектор ячеек в листе
while (cell <= 9)
{
if (cell == 3)
{
if (xlRange_2.Cells[row, cell].value == null)
{
}
else
{
numberSpeciality = xlRange_2.Cells[row, cell].Text;// Таблица специальность
string[] wordss = numberSpeciality.Split(new char[] { ',' });
// new char[] - массив символов-разделителей. Как меня поправили в
// комментариях, в данном случае достаточно написать text.Split(':')
int Counter = wordss.Count();
if (Counter == 2)
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
string numberSpecialitysecondd = wordss[1];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialitysecondd + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
else
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
}
}
if (cell == 6)
{
if (xlRange_2.Cells[row, cell].value == null)
{
}
else
{
numberSpeciality = xlRange_2.Cells[row, cell].Text;// Таблица специальность
string[] wordss = numberSpeciality.Split(new char[] { ',' });
// new char[] - массив символов-разделителей. Как меня поправили в
// комментариях, в данном случае достаточно написать text.Split(':')
int Counter = wordss.Count();
if (Counter == 2)
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
string numberSpecialitysecondd = wordss[1];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialitysecondd + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
else
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
}
}
if (cell == 9)
{
if (xlRange_2.Cells[row, cell].value == null)
{
}
else
{
numberSpeciality = xlRange_2.Cells[row, cell].Text;// Таблица специальность
string[] wordss = numberSpeciality.Split(new char[] { ',' });
// new char[] - массив символов-разделителей. Как меня поправили в
// комментариях, в данном случае достаточно написать text.Split(':')
int Counter = wordss.Count();
if (Counter == 2)
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
string numberSpecialitysecondd = wordss[1];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialitysecondd + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
else
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
}
}
cell++;
}
cell = 3;
}
else
{
}
//3 страница
if (CounterLIST <= 4)
{
//Sotrydniki
int rowS = 13;
int cellS = 3;
// Создаем экземпляр Regex
Regex rg = new Regex(@"[А-Я]{1}[.]{1}[А-Я]{1}[.]{1} [А-Я]{1}([а-я]{0,17})?([a-z]{0,17})?");
_Worksheet list_11 = (_Worksheet)xlWorkbook.Sheets[3]; //Получаем 3 лист
Range xlRange_11 = list_11.UsedRange; //Получаем используемый сектор ячеек в листе
string authors;
while (rowS <= 96)
{
while (cellS <= 9)
{
authors = xlRange_11.Cells[rowS, cellS].Text;
// Получаем все совпадения
MatchCollection matchedAuthors = rg.Matches(authors);
/// Выводим всех подходящих авторов
foreach (Match match in matchedAuthors)
{
try
{
String value = match.ToString();
String Surname = value.Substring(0, 2);
String Name = value.Substring(2, 2);
String FirstName = value.Substring(5);
queryOfTables("INSERT INTO Employees values('" + Surname + "','" + Name + "' ,'" + FirstName + "' , 1 )");
}
catch
{
}
}
cellS++;
}
cellS = 3;
rowS++;
} //Sotrydniki
//читаем данные из Excel c 3 листа
_Worksheet list_3 = (_Worksheet)xlWorkbook.Sheets[3]; //Получаем 3 лист
Range xlRange_3 = list_3.UsedRange; //Получаем используемый сектор ячеек в листе
while (cell <= 9)
{
if (cell == 3)
{
if (xlRange_3.Cells[row, cell].value == null)
{
}
else
{
numberSpeciality = xlRange_3.Cells[row, cell].Text;// Таблица специальность
string[] wordss = numberSpeciality.Split(new char[] { ',' });
// new char[] - массив символов-разделителей. Как меня поправили в
// комментариях, в данном случае достаточно написать text.Split(':')
int Counter = wordss.Count();
if (Counter == 2)
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
string numberSpecialitysecondd = wordss[1];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialitysecondd + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
else
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
}
}
if (cell == 6)
{
if (xlRange_3.Cells[row, cell].value == null)
{
}
else
{
numberSpeciality = xlRange_3.Cells[row, cell].Text;// Таблица специальность
string[] wordss = numberSpeciality.Split(new char[] { ',' });
// new char[] - массив символов-разделителей. Как меня поправили в
// комментариях, в данном случае достаточно написать text.Split(':')
int Counter = wordss.Count();
if (Counter == 2)
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
string numberSpecialitysecondd = wordss[1];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialitysecondd + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
else
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
}
}
if (cell == 9)
{
if (xlRange_3.Cells[row, cell].value == null)
{
}
else
{
numberSpeciality = xlRange_3.Cells[row, cell].Text;// Таблица специальность
string[] wordss = numberSpeciality.Split(new char[] { ',' });
// new char[] - массив символов-разделителей. Как меня поправили в
// комментариях, в данном случае достаточно написать text.Split(':')
int Counter = wordss.Count();
if (Counter == 2)
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
string numberSpecialitysecondd = wordss[1];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialitysecondd + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
else
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
}
}
cell++;
}
cell = 3;
}
else
{
}
//4 страница
if (CounterLIST == 4)
{
//Sotrydniki
int rowS = 13;
int cellS = 3;
// Создаем экземпляр Regex
Regex rg = new Regex(@"[А-Я]{1}[.]{1}[А-Я]{1}[.]{1} [А-Я]{1}([а-я]{0,17})?([a-z]{0,17})?");
_Worksheet list_11 = (_Worksheet)xlWorkbook.Sheets[4]; //Получаем 4 лист
Range xlRange_11 = list_11.UsedRange; //Получаем используемый сектор ячеек в листе
string authors;
while (rowS <= 96)
{
while (cellS <= 9)
{
authors = xlRange_11.Cells[rowS, cellS].Text;
// Получаем все совпадения
MatchCollection matchedAuthors = rg.Matches(authors);
/// Выводим всех подходящих авторов
foreach (Match match in matchedAuthors)
{
try
{
String value = match.ToString();
String Surname = value.Substring(0, 2);
String Name = value.Substring(2, 2);
String FirstName = value.Substring(5);
queryOfTables("INSERT INTO Employees values('" + Surname + "','" + Name + "' ,'" + FirstName + "' , 1 )");
}
catch
{
}
}
cellS++;
}
cellS = 3;
rowS++;
} //Sotrydniki
//читаем данные из Excel c 4 листа
_Worksheet list_4 = (_Worksheet)xlWorkbook.Sheets[4]; //Получаем 4 лист
Range xlRange_4 = list_4.UsedRange; //Получаем используемый сектор ячеек в листе
while (cell <= 9)
{
if (cell == 3)
{
if (xlRange_4.Cells[row, cell].value == null)
{
}
else
{
numberSpeciality = xlRange_4.Cells[row, cell].Text;// Таблица специальность
string[] wordss = numberSpeciality.Split(new char[] { ',' });
// new char[] - массив символов-разделителей. Как меня поправили в
// комментариях, в данном случае достаточно написать text.Split(':')
int Counter = wordss.Count();
if (Counter == 2)
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
string numberSpecialitysecondd = wordss[1];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialitysecondd + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
else
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
}
}
if (cell == 6)
{
if (xlRange_4.Cells[row, cell].value == null)
{
}
else
{
numberSpeciality = xlRange_4.Cells[row, cell].Text;// Таблица специальность
string[] wordss = numberSpeciality.Split(new char[] { ',' });
// new char[] - массив символов-разделителей. Как меня поправили в
// комментариях, в данном случае достаточно написать text.Split(':')
int Counter = wordss.Count();
if (Counter == 2)
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
string numberSpecialitysecondd = wordss[1];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialitysecondd + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
else
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
}
}
if (cell == 9)
{
if (xlRange_4.Cells[row, cell].value == null)
{
}
else
{
numberSpeciality = xlRange_4.Cells[row, cell].Text;// Таблица специальность
string[] wordss = numberSpeciality.Split(new char[] { ',' });
// new char[] - массив символов-разделителей. Как меня поправили в
// комментариях, в данном случае достаточно написать text.Split(':')
int Counter = wordss.Count();
if (Counter == 2)
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
string numberSpecialitysecondd = wordss[1];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialitysecondd + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
else
{
try
{
string numberSpecialityfirstt = wordss[0];
queryOfTables("INSERT INTO GGroup values('" + numberSpecialityfirstt + "', '" + nameSpecialty + "' )");
}
catch
{
}
}
}
}
cell++;
}
}
else
{
}
}
dgEmployes(QRE);
dgGroup(QRG);
dgCabinetes(QRC);
dgTerritory(QRT);
}
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);
}
}
private int facultyfill(excel.Application application, excel.Workbook workBook, int line, string housingNumber)
{
string[] facultyList = new string[5];
int k = 0;
excel.Sheets sheet;
excel.Worksheet newWorkSheet;
string sheetName = "";
if (ITC.IsChecked == true)
{
facultyList[k] = "ИТС";
k += 1;
}
if (MTO.IsChecked == true)
{
facultyList[k] = "МТО";
k += 1;
}
if (FEM.IsChecked == true)
{
facultyList[k] = "ФЭМ";
k += 1;
}
if (COIG.IsChecked == true)
{
facultyList[k] = "ЦОИГ";
k += 1;
}
if (Aspr.IsChecked == true)
{
facultyList[k] = "Аспирантура";
k += 1;
}
try
{
sheetName = facultyList[0];
application.Sheets[1].Name = sheetName;
for (int i = 0; i < k; i++)
{
if (i < k - 1)
{
sheetName = facultyList[k - (i + 1)];
application.Sheets.Add().Name = sheetName;
}
}
sheet = application.ActiveWorkbook.Worksheets;
line = excelLivingClass.facultyHeader(application, line, housingNumber, k, (bool)insideUse.IsChecked, (bool)nonPayed.IsChecked, (DateTime)date.SelectedDate, (bool)evicted.IsChecked, globalConnection);
/*for(int i = 0; i < k; i++)
* {
* newWorkSheet = (excel.Worksheet)sheet.get_Item(i + 1);
* excelLivingClass.facultyFullReport(application, newWorkSheet, globalConnection, line);
* }*/
return(line);
}
catch
{
MessageBox.Show("Выберете хотя бы один факультет");
return(0);
}
}
protected void Page_Load(object sender, EventArgs e)
{
ExcelData aExcelData = new ExcelData();
ExcelAllData aExcelAllData = new ExcelAllData();
string aJsonStr = string.Empty;
if (Request.Files.AllKeys.Any())
{
HttpPostedFile aExcelFile = Request.Files["ExcelFile"];
string aFileName = aExcelFile.FileName;
string aPath = HttpContext.Current.Server.MapPath("~/Data/" + aFileName);
aExcelFile.SaveAs(aPath);
if (aExcelFile != null && aExcelFile.ContentLength != 0)
{
Application aExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook aWorkbook = aExcelApp.Workbooks.Open(aPath, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Sheets aSheet = aWorkbook.Worksheets;
//取得第一個sheet
Worksheet aWorkSheet = (Worksheet)aSheet.get_Item(1);
Range aRange = aWorkSheet.UsedRange;
//取出行列數
int aRow = aRange.Rows.Count;
int aColumns = aRange.Columns.Count;
try
{
for (int i = 2; i <= aRow; i++)
{
aExcelData.ProductID.Add(((Range)aWorkSheet.Cells[i, 1]).Value2.ToString());
aExcelData.ProductName.Add(((Range)aWorkSheet.Cells[i, 3]).Value2.ToString());
aExcelData.Price.Add(((Range)aWorkSheet.Cells[i, 7]).Value2.ToString());
}
aWorkbook.Close();
File.Delete(aPath);
}
catch
{
aWorkbook.Close();
File.Delete(aPath);
aExcelAllData.Result = "3";
aJsonStr = JsonConvert.SerializeObject(aExcelAllData);
Response.Write(aJsonStr);
}
aExcelAllData.Result = "0";
aExcelAllData.Price = aExcelData.Price.ToArray();
aExcelAllData.ProductName = aExcelData.ProductName.ToArray();
aExcelAllData.ProductID = aExcelData.ProductID.ToArray();
aJsonStr = JsonConvert.SerializeObject(aExcelAllData);
Response.Write(aJsonStr);
}
else
{
aExcelAllData.Result = "2";
aJsonStr = JsonConvert.SerializeObject(aExcelAllData);
Response.Write(aJsonStr);
}
}
else
{
aExcelAllData.Result = "2";
//沒有檔案或是檔案有問題
aJsonStr = JsonConvert.SerializeObject(aExcelAllData);
Response.Write(aJsonStr);
}
}
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);
}
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 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);
}
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);
}
}
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 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();
}
}
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);
}
