public void ImportEXEL(string filename, Action <int, string> progress)
{
if (!File.Exists(filename))
{
throw new Exception("Файл книги не найден.");
}
if (Path.GetExtension(filename) != ".xls" && Path.GetExtension(filename) != ".xlsx")
{
throw new Exception("Данный формат не поддерживается.");
}
progress(10, "Запуск ядра Excel");
Excel.Application exApp = new Excel.Application();// Запуск в интрактивном режиме
//exApp.Visible = true;// Debug
try
{
try
{
exApp.Workbooks.Open(filename);//Открытие книги
}
catch
{
throw new Exception("Ошибка при попытке чтения книги.");
}
progress(20, "Чтение настроек");
DialogResult dialogresult = DialogResult.OK;
int rowExcel = 1, rowEndExcel = 1, courseCount = 1, Specialnost = 0, VidCurriculum = 0;
int ColumnName = 0, ColumnExam = 0, ColumnOKR = 0, ColumnFullHour = 0, ColumnLPS = 0, ColumnKP = 0, ColumnFullHour1 = 0, ColumnLPS1 = 0, ColumnFullHour2 = 0, ColumnLPS2 = 0, ColumnTotal = 0, ColumnDistance = 0;
this.numericUpDownBeginRow.BeginInvoke((MethodInvoker)(() => int.TryParse(numericUpDownBeginRow.Text, out rowExcel)));
this.numericUpDownEndRow.BeginInvoke((MethodInvoker)(() => int.TryParse(numericUpDownEndRow.Text, out rowEndExcel)));
this.numericUpDownCourse.BeginInvoke((MethodInvoker)(() => int.TryParse(numericUpDownCourse.Text, out courseCount)));
this.numericUpDownDistance.BeginInvoke((MethodInvoker)(() => int.TryParse(numericUpDownDistance.Text, out ColumnDistance)));
this.comboBoxSpecialnost.BeginInvoke((MethodInvoker)(() => Specialnost = (int)comboBoxSpecialnost.SelectedValue));
this.comboBoxVidCurriculum.BeginInvoke((MethodInvoker)(() => VidCurriculum = (int)comboBoxVidCurriculum.SelectedValue));
this.dataGridView1.BeginInvoke((MethodInvoker)(() =>
{
int.TryParse(dataGridView1["ColumnName", dataGridView1.RowCount - 1].Value.ToString(), out ColumnName);
//int.TryParse(dataGridView1["ColumnExam", dataGridView1.RowCount - 1].Value.ToString(), out ColumnExam);
int.TryParse(dataGridView1["ColumnOKR", dataGridView1.RowCount - 1].Value.ToString(), out ColumnOKR);
int.TryParse(dataGridView1["ColumnFullHour", dataGridView1.RowCount - 1].Value.ToString(), out ColumnFullHour);
int.TryParse(dataGridView1["ColumnLPS", dataGridView1.RowCount - 1].Value.ToString(), out ColumnLPS);
int.TryParse(dataGridView1["ColumnKP", dataGridView1.RowCount - 1].Value.ToString(), out ColumnKP);
int.TryParse(dataGridView1["ColumnFullHour1", dataGridView1.RowCount - 1].Value.ToString(), out ColumnFullHour1);
int.TryParse(dataGridView1["ColumnLPS1", dataGridView1.RowCount - 1].Value.ToString(), out ColumnLPS1);
int.TryParse(dataGridView1["ColumnFullHour2", dataGridView1.RowCount - 1].Value.ToString(), out ColumnFullHour2);
int.TryParse(dataGridView1["ColumnLPS2", dataGridView1.RowCount - 1].Value.ToString(), out ColumnLPS2);
int.TryParse(dataGridView1["ColumnTotal", dataGridView1.RowCount - 1].Value.ToString(), out ColumnTotal);
}));
//exApp.DisplayAlerts = false;
exApp.Workbooks.Open(filename);
Excel.Worksheet workSheet = (Excel.Worksheet)exApp.ActiveSheet;
//Excel.Worksheet workSheet = (Excel.Worksheet)exApp.Worksheets.get_Item("Группа");
Thread.Sleep(1500);
progress(25, "Чтение строк");
for (int i = rowExcel; i <= rowEndExcel; i++)
{
progress(30 + (60 / (rowEndExcel - rowExcel) * (i - rowExcel)), String.Format("Чтение дисциплин учебного плана {0}/{1}", (i - rowExcel), (rowEndExcel - rowExcel)));
int okr = 0, exam = 0;
Predmet predmet = new Predmet();
predmet.Specialnost = Specialnost;
predmet.CK = -1;
predmet.VidCurriculum = VidCurriculum;
predmet.NazvaniePolnoe = workSheet.Cells[i, ColumnName].Text.ToString();
predmet.Nazvanie = ClassMain.GenerationName(predmet.NazvaniePolnoe);
//int.TryParse((workSheet.Cells[i, ColumnExam] as Excel.Range).Value, out exam);
int.TryParse(workSheet.Cells[i, ColumnOKR].Text.ToString(), out okr);
if (!int.TryParse(workSheet.Cells[i, ColumnFullHour].Text.ToString(), out predmet.AllHour))
{
continue;
}
int.TryParse(workSheet.Cells[i, ColumnLPS].Text.ToString(), out predmet.LabHour);
int.TryParse(workSheet.Cells[i, ColumnKP].Text.ToString(), out predmet.CourseworkHour);
if (predmet.NazvaniePolnoe == "" || predmet.AllHour <= 0)
{
continue;
}
for (int j = courseCount - 1; j >= 0; j--)
{
PredmetCourse predmetcourse = new PredmetCourse();
if (workSheet.Cells[i, ColumnTotal + (ColumnDistance * j)].Text.ToString() == "" ||
!int.TryParse(workSheet.Cells[i, ColumnTotal + (ColumnDistance * j)].Text.ToString(), out predmetcourse.AllHour))
{
continue;
}
if (predmetcourse.AllHour <= 0)
{
continue;
}
predmetcourse.Course = j + 1;
predmetcourse.OKR = okr; okr = 0;
PredmetSemester predmetsemester = new PredmetSemester();
if (workSheet.Cells[i, ColumnFullHour2 + (ColumnDistance * j)].Text.ToString() != "" &&
int.TryParse(workSheet.Cells[i, ColumnFullHour2 + (ColumnDistance * j)].Text.ToString(), out predmetsemester.AllHour))
{
predmetsemester.Semester = 2;
if (workSheet.Cells[i, ColumnLPS2 + (ColumnDistance * j)].Text.ToString() != "")
{
int.TryParse(workSheet.Cells[i, ColumnLPS2 + (ColumnDistance * j)].Text.ToString(), out predmetsemester.LabHour);
}
predmetcourse.predmetsemester.Add(predmetsemester);
}
predmetsemester = new PredmetSemester();
if (workSheet.Cells[i, ColumnFullHour1 + (ColumnDistance * j)].Text.ToString() != "" &&
int.TryParse(workSheet.Cells[i, ColumnFullHour1 + (ColumnDistance * j)].Text.ToString(), out predmetsemester.AllHour))
{
predmetsemester.Semester = 1;
if (workSheet.Cells[i, ColumnLPS1 + (ColumnDistance * j)].Text.ToString() != "")
{
int.TryParse(workSheet.Cells[i, ColumnLPS1 + (ColumnDistance * j)].Text.ToString(), out predmetsemester.LabHour);
}
predmetcourse.predmetsemester.Add(predmetsemester);
}
predmet.predmetcourse.Add(predmetcourse);
}
if (dialogresult != DialogResult.Yes)
{
FormEdit.FormEditPredmet_Curse form = new FormEditPredmet_Curse(-2, predmet);
dialogresult = form.ShowDialog();
}
else
{
FormEdit.FormEditPredmet_Curse form = new FormEditPredmet_Curse(-3, predmet);
dialogresult = form.ShowDialog();
}
if (dialogresult == DialogResult.Cancel)
{
break;
}
Thread.Sleep(10);
}
}
catch
{
throw new Exception("Таблицы не удалось импортировать.");
}
finally
{
progress(95, "Завершение работы ядра Excel");
exApp.Quit();
}
}
