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);
}