private string GetMinColumn(ImportTemplate template) { string value = template.ArticleColName; if (StringToUIntConvert(value) > StringToUIntConvert(template.CostBaseColName)) { value = template.CostBaseColName; } if (StringToUIntConvert(value) > StringToUIntConvert(template.CostNativeColName)) { value = template.CostNativeColName; } if (StringToUIntConvert(value) > StringToUIntConvert(template.DescriptionColName)) { value = template.DescriptionColName; } if (StringToUIntConvert(value) > StringToUIntConvert(template.ManufColName)) { value = template.ManufColName; } if (StringToUIntConvert(value) > StringToUIntConvert(template.ModelColName)) { value = template.ModelColName; } if (StringToUIntConvert(value) > StringToUIntConvert(template.OEMColName)) { value = template.OEMColName; } if (StringToUIntConvert(value) > StringToUIntConvert(template.QuantityColName)) { value = template.QuantityColName; } return(value); }
public IEnumerable <ImportDataRowModel> Import(ImportTemplate template, string fileName) { NetOffice.ExcelApi.Application _app; NetOffice.ExcelApi.Workbook _workbook; NetOffice.ExcelApi.Worksheet _worksheet; //Excel.Range _range; // start excel and turn off msg boxes try { _app = new NetOffice.ExcelApi.Application(); _app.DisplayAlerts = false; _app.Visible = false; } catch (Exception exc) { throw new Exception("Не удалось подключиться к Excell"); } // OpenFile try { _workbook = _app.Workbooks.Open(fileName); } catch (Exception exc) { _app.Quit(); _app.Dispose(); throw new FileNotFoundException(fileName); } _worksheet = (NetOffice.ExcelApi.Worksheet)_workbook.Worksheets.FirstOrDefault(); if (_worksheet == null) { _app.Quit(); _app.Dispose(); throw new Exception("Книга не найдена"); } //количество ошибок чтения int errorCount = 1; //текущее количество ошибок int error = 0; //количество строк для обработки int countRow = 500; int endRow; countRow = template.EndRowNumber - template.StartRowNumber; endRow = template.EndRowNumber; string startColumn = GetMinColumn(template); string endColumn = GetMaxColumn(template); object[,] values = (object[, ])_worksheet.get_Range(startColumn + template.StartRowNumber, endColumn + endRow).Value2; int shiftColumn = 0; if (StringToUIntConvert(startColumn) != 1) { //расчитываем сдвиг по горизонтали shiftColumn = StringToUIntConvert(startColumn) - 1; } int articleColNum = StringToUIntConvert(template.ArticleColName) - shiftColumn; int costBaseColNum = StringToUIntConvert(template.CostBaseColName) - shiftColumn; int costNativeColNum = StringToUIntConvert(template.CostNativeColName) - shiftColumn; int descriptionColNum = StringToUIntConvert(template.DescriptionColName) - shiftColumn; int manufColNum = StringToUIntConvert(template.ManufColName) - shiftColumn; int modelColNum = StringToUIntConvert(template.ModelColName) - shiftColumn; int oemColNum = StringToUIntConvert(template.OEMColName) - shiftColumn; int quantityColNum = StringToUIntConvert(template.QuantityColName) - shiftColumn; string brandStr = ""; string modelStr = ""; // считываем List <ImportDataRowModel> rowsFromExcel = new List <ImportDataRowModel>(); for (int i = 1; i <= countRow + 1; i++) { if (error > errorCount) { break; } try { int outLevel = Convert.ToInt32(_worksheet.Rows[i + template.StartRowNumber - 1].OutlineLevel); if (outLevel == 1) { brandStr = values[i, modelColNum] != null?Convert.ToString(values[i, modelColNum]) : ""; brandStr = brandStr.TrimEnd(' ').TrimStart(' '); } else if (outLevel == 2) { modelStr = values[i, modelColNum] != null?Convert.ToString(values[i, modelColNum]) : ""; modelStr = modelStr.TrimEnd(' ').TrimStart(' '); } else if (outLevel > 2) { string subModelStr = values[i, modelColNum] != null ? Convert.ToString(values[i, modelColNum]) : ""; subModelStr = subModelStr.TrimEnd(' ').TrimStart(' '); string articleStr = values[i, articleColNum] != null ? Convert.ToString(values[i, articleColNum]) : ""; articleStr = articleStr.TrimEnd(' ').TrimStart(' '); //если Article не найден то пропускаем строку if (string.IsNullOrWhiteSpace(articleStr)) { continue; } string priceBaseStr = values[i, costBaseColNum] != null ? Convert.ToString(values[i, costBaseColNum]) : ""; decimal priceBase = ParsingPrice(priceBaseStr); string priceNativeStr = values[i, costNativeColNum] != null ? Convert.ToString(values[i, costNativeColNum]) : ""; decimal priceNative = ParsingPrice(priceNativeStr); string descriptionStr = values[i, descriptionColNum] != null ? Convert.ToString(values[i, descriptionColNum]) : ""; descriptionStr = descriptionStr.TrimEnd(' ').TrimStart(' '); if (descriptionStr == "-2146826246") { descriptionStr = ""; } string manufStr = values[i, manufColNum] != null?Convert.ToString(values[i, manufColNum]) : ""; manufStr = manufStr.TrimEnd(' ').TrimStart(' '); if (manufStr == "-2146826246") { manufStr = ""; } string oemStr = values[i, oemColNum] != null?Convert.ToString(values[i, oemColNum]) : ""; List <string> oemList = oemParsingToList(oemStr, out oemStr); string qtyStr = values[i, oemColNum] != null?Convert.ToString(values[i, quantityColNum]) : ""; bool qty = ParsingQty(qtyStr); rowsFromExcel.Add(new ImportDataRowModel() { Brand = brandStr, Article = articleStr, PriceBase = priceBase, PriceNative = priceNative, Description = descriptionStr, Manufacturer = manufStr, Quantity = qty, Model = modelStr, SubModel = subModelStr, OEMs = oemStr, OEMList = oemList }); } } catch (Exception) { error += 1; //увеличиваем счетчик ошибок continue; } error = 0; //если чтение прошло удачно обнуляем } //if (error > errorCount) //{ // throw new Exception("Не верный формат данных"); //} _workbook.Close(); _app.Quit(); _app.Dispose(); GC.Collect(); return(rowsFromExcel); }