private void btnStart_Click(object sender, EventArgs e) { try { if (this.OpenedExcelFile == null) { throw new Exception("Необходимо открыть файл Excel."); } ExcelWorkSpaceInfo excelInfo = new Office.ExcelWorkSpaceInfo(this.OpenedExcelFile, (int)cmbSheets.SelectedValue); excelInfo.ColumnPictureNames = cmbNames.Text; excelInfo.ColumnPictureHyperlinks = cmbLinks.Text; excelInfo.RowBeginUpload = Convert.ToInt32(txtBeginRow.Text); excelInfo.RowEndUpload = Convert.ToInt32(txtEndRow.Text); var param = new ProcessorParameters { ExcelInfo = excelInfo, Direction = rbSaveLocal.Checked ? UploadPicturesDirection.LOCAL : UploadPicturesDirection.FTP, UploadFolderName = string.IsNullOrEmpty(txtPictureFolderName.Text) ? Guid.NewGuid().ToString() : txtPictureFolderName.Text }; SetControlsEnabled(false); this.BWorker = new BackgroundWorker(); this.Processor = new Processor(); BWorker.WorkerReportsProgress = true; BWorker.WorkerSupportsCancellation = false; BWorker.ProgressChanged += BWorker_ProgressChanged; BWorker.RunWorkerCompleted += BWorker_RunWorkerCompleted; BWorker.DoWork += this.Processor.RunProcess; BWorker.RunWorkerAsync(param); } catch (Exception ex) { MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public List <PictureItem> GetPhotoItems(ExcelWorkSpaceInfo excelInfo, BackgroundWorker bw) { try { bw.ReportProgress(0, "Считываем записи из Excel файла"); Excel.Workbook xlWorkBook = OpenExcelFile(excelInfo.WorkBook.Path); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets[excelInfo.SelectedSheetIndex]; if (!CheckData(xlWorkSheet, excelInfo)) { xlWorkBook.Save(); xlWorkBook.Close(); Release(xlWorkSheet); Release(xlWorkBook); string error = "Обнаружены ошибки в таблице. Это могут быть пустые ячейки в столбце номеров плоскотей, либо пустые/некорректные гиперссылки" + Environment.NewLine + "Ошибки выделены в файле. Исправьте и повторите"; throw new Exception(error); } List <PictureItem> Items = new List <PictureItem>(); for (int i = excelInfo.RowBeginUpload; i <= excelInfo.RowEndUpload; i++) { Items.Add(new PictureItem(xlWorkSheet.Range[excelInfo.ColumnPictureNames + i].Value.ToString().Trim(), GetUrlFromCell(xlWorkSheet.Range[excelInfo.ColumnPictureHyperlinks + i]))); } xlWorkBook.Close(); Release(xlWorkSheet); Release(xlWorkBook); ExitExcelApplication(); return(Items); } catch (Exception ex) { try { if (xlApp != null) { ExitExcelApplication(); } } finally { ReleaseUnmanaged(); throw ex; } } }
public void UpdatePhotoItems(IEnumerable <PictureItem> items, ExcelWorkSpaceInfo excelInfo, BackgroundWorker bw) { string targetColumn = ExcelStatic.GetColumnName(excelInfo.SelectedSheet.LastCell.Column + 1); try { bw.ReportProgress(0, "Записываем результат в Excel файл"); Excel.Workbook xlWorkBook = OpenExcelFile(excelInfo.WorkBook.Path); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets[excelInfo.SelectedSheetIndex]; for (int i = excelInfo.RowBeginUpload; i <= excelInfo.RowEndUpload; i++) { var name = xlWorkSheet.Range[excelInfo.ColumnPictureNames + i].Value.ToString().Trim(); var item = items.FirstOrDefault(a => a.Name == name); if (item.Status) { xlWorkSheet.Hyperlinks.Add(Anchor: xlWorkSheet.Range[targetColumn + i], Address: item.Address, TextToDisplay: "Фото"); } else { xlWorkSheet.Range[targetColumn + i].Value = item.Address; } } xlWorkBook.Save(); xlWorkBook.Close(); Release(xlWorkSheet); Release(xlWorkBook); ExitExcelApplication(); } catch (Exception ex) { try { if (xlApp != null) { ExitExcelApplication(); } } finally { ReleaseUnmanaged(); throw ex; } } }
private bool CheckData(Excel.Worksheet sheet, ExcelWorkSpaceInfo excelInfo) { int errors = 0; Excel.Range range = null; for (int i = excelInfo.RowBeginUpload; i <= excelInfo.RowEndUpload; i++) { range = sheet.Range[excelInfo.ColumnPictureNames + i]; if (range.Value == null || range.Value.ToString().Trim() == string.Empty) { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); errors++; } range = sheet.Range[excelInfo.ColumnPictureHyperlinks + i]; if (GetUrlFromCell(range) == null) { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); errors++; } } Release(range); return((errors > 0) ? false : true); }