public static int ColumnHeaderNumber(this OfficeOpenXml.ExcelWorksheet worksheet, String headerName, bool ignoreCase)
{
const int maxColumn = 9999;
for (int columnNumber = 1; columnNumber < maxColumn; columnNumber++)
{
if (ignoreCase)
{
if (string.Compare(headerName, worksheet.GetValue(1, columnNumber).ToString().Trim(),
StringComparison.OrdinalIgnoreCase) == 0)
{
return(columnNumber);
}
}
else
{
if (headerName == worksheet.GetValue(1, columnNumber).ToString().Trim())
{
return(columnNumber);
}
}
}
// There is no match
throw new ApplicationException("Column header not found \'" + headerName + "\'");
}
public override void CargarDatos(ExcelWorksheet hoja, int i)
{
Cuenta = hoja.GetValue<string>(i, ColumnaCuenta);
Nombre = hoja.GetValue<string>(i, ColumnaOportunidad);
Codigo = hoja.GetValue<int>(i, ColumnaCodigo);
Responsable = hoja.GetValue<string>(i, ColumnaResponsable);
Fase = hoja.GetValue<string>(i, ColumnaFase);
Probabilidad = hoja.GetValue<double>(i, ColumnaProbabilidad);
ImporteUSD = Math.Round(hoja.GetValue<double>(i, ColumnaImporteUSD));
Monto = Math.Round(hoja.GetValue<double>(i, ColumnaMonto));
Ponderado = Math.Round(hoja.GetValue<double>(i, ColumnaPonderado));
FechaDeIngreso = ConvertirExcelAFecha(hoja, i, ColumnaFechaDeIngreso);
}
public static TValue GetValue <TValue>(this ExcelWorksheet worksheet, Int32 row, Int32 column, TValue defaultValue)
where TValue : class
{
var result = worksheet.GetValue <TValue>(row, column);
return(result != null
? result
: defaultValue);
}
public static IEnumerable <string> ColumnHeaders(this OfficeOpenXml.ExcelWorksheet worksheet)
{
List <string> headers = new List <string>();
int maxColumn = worksheet.MaxColumnNumber();
for (int columnNumber = 1; columnNumber < maxColumn; columnNumber++)
{
headers.Add(worksheet.GetValue(1, columnNumber).ToString());
}
return(headers);
}
public ExcelMatch Les(ExcelWorksheet sheet)
{
var row = 2;
var match = new ExcelMatch
{
MatchId = Guid.Parse(sheet.GetValue(ExcelSheet.Match.MatchId, row)),
Navn = sheet.GetValue(ExcelSheet.Match.Navn, row),
StartTid = DateTime.Parse(sheet.GetValue(ExcelSheet.Match.Starttid, row)),
SluttTid = DateTime.Parse(sheet.GetValue(ExcelSheet.Match.Sluttid, row)),
DefaultPoengFordeling = sheet.GetValue(ExcelSheet.Match.DefaultPostPoengfordeling, row)
};
LesGeobox(sheet, row, match);
LesVåpenOppsett(sheet, row, match);
AddOrUpdate(match);
LeggInnVåpen();
return match;
}
private static void LesGeobox(ExcelWorksheet sheet, int row, ExcelMatch match)
{
var point = sheet.GetValue(ExcelSheet.Match.GeoBox_NW_latitude, row);
if (!string.IsNullOrEmpty(point))
match.GeoboxNWLatitude = double.Parse(point);
point = sheet.GetValue(ExcelSheet.Match.GeoBox_NW_longitude, row);
if (!string.IsNullOrEmpty(point))
match.GeoboxNWLongitude = double.Parse(point);
point = sheet.GetValue(ExcelSheet.Match.GeoBox_SE_latitude, row);
if (!string.IsNullOrEmpty(point))
match.GeoboxSELatitude = double.Parse(point);
point = sheet.GetValue(ExcelSheet.Match.GeoBox_SE_longitude, row);
if (!string.IsNullOrEmpty(point))
match.GeoboxSELongitude = double.Parse(point);
}
private void ProcessTableRow(ExcelWorksheet worksheet, ExcelTable table, int rowNum)
{
var output = new PSObject();
foreach (var col in table.Columns)
{
var val = worksheet.GetValue(rowNum, 1 + col.Position);
var prop = new PSNoteProperty(col.Name, val);
output.Members.Add(prop);
}
this.WriteObject(output);
}
private static IList<Questionary> FromExcelSheet(ExcelWorksheet worksheet)
{
var list = new List<Questionary>();
for (int i = 2; i <= worksheet.Dimension.End.Row; i++)
{
var questionary = new Questionary
{
LastName = worksheet.GetValue<string>(i, 3),
FirstName = worksheet.GetValue<string>(i, 4),
Patronymic = worksheet.GetValue<string>(i, 5),
BirthDate = worksheet.GetValue<DateTime>(i, 6),
MobilePhone = worksheet.GetValue<string>(i, 7),
Email = worksheet.GetValue<string>(i, 8),
PassportSeries = worksheet.GetValue<string>(i, 9),
PassportNumber = worksheet.GetValue<string>(i, 10),
IINPhysic = worksheet.GetValue<string>(i, 11),
PassportIssued = worksheet.GetValue<string>(i, 12),
AddressLocation = worksheet.GetValue<string>(i, 13)
};
list.Add(questionary);
}
return list;
}
private static void GetCategories(Dictionary<string, List<string>> parseErrors, ExcelWorksheet worksheet, int rowId,
ProductImportDataTransferObject product, string handle)
{
//Categories
try
{
var value = worksheet.GetValue<string>(rowId, 9);
if (!String.IsNullOrWhiteSpace(value))
{
var Cats = value.Split(';');
foreach (var item in Cats)
{
if (!String.IsNullOrWhiteSpace(item))
{
if (!product.Categories.Any(x => x == item))
product.Categories.Add(item);
else
{
parseErrors[handle].Add(
"Product Categories field value contains duplicate values.");
break;
}
}
}
}
}
catch (Exception)
{
parseErrors[handle].Add(
"Product Categories field value contains illegal characters / not in correct format.");
}
}
private static void GetSpecifications(Dictionary<string, List<string>> parseErrors, ExcelWorksheet worksheet, int rowId, string handle,
ProductImportDataTransferObject product)
{
//Specifications
var specificationsValue = worksheet.GetValue<string>(rowId, 10);
if (!String.IsNullOrWhiteSpace(specificationsValue))
{
try
{
if (!String.IsNullOrWhiteSpace(specificationsValue))
{
if (!specificationsValue.Contains(":"))
parseErrors[handle].Add(
"Product Specifications field value contains illegal characters / not in correct format. Names and Values (Item) must be split with :, and items must be split by ;");
var specs = specificationsValue.Split(';');
foreach (var item in specs)
{
if (!String.IsNullOrWhiteSpace(item))
{
string[] specificationValue = item.Split(':');
if (!String.IsNullOrWhiteSpace(specificationValue[0]) &&
!String.IsNullOrWhiteSpace(specificationValue[1]) &&
!product.Specifications.ContainsKey(
specificationValue[0]))
product.Specifications.Add(specificationValue[0],
specificationValue[1]);
}
}
}
}
catch (Exception)
{
parseErrors[handle].Add(
"Product Specifications field value contains illegal characters / not in correct format. Names and Values (Item) must be split with :, and items must be split by ;");
}
}
}
private static List<string> GetUrlHistory(ExcelWorksheet worksheet, int rowId, List<string> parseErrors)
{
var list = new List<String>();
try
{
var value = worksheet.GetValue<string>(rowId, 14);
if (!String.IsNullOrWhiteSpace(value))
{
var urls = value.Split(',');
foreach (var url in urls.Where(url => !String.IsNullOrWhiteSpace(url)))
{
list.Add(url);
}
}
}
catch (Exception)
{
parseErrors.Add("Url History field value contains illegal characters / not in correct format.");
}
return list;
}
private static void GetBasicData(Dictionary<string, List<string>> parseErrors, ExcelWorksheet worksheet, int rowId,
ProductImportDataTransferObject product, string handle)
{
if (worksheet.GetValue<string>(rowId, 2).HasValue())
product.Name = worksheet.GetValue<string>(rowId, 2);
else
parseErrors[handle].Add("Product Name is required.");
product.Description = worksheet.GetValue<string>(rowId, 3);
product.SEOTitle = worksheet.GetValue<string>(rowId, 4);
product.SEODescription = worksheet.GetValue<string>(rowId, 5);
product.SEOKeywords = worksheet.GetValue<string>(rowId, 6);
product.Abstract = worksheet.GetValue<string>(rowId, 7);
product.Brand = worksheet.GetValue<string>(rowId, 8);
if (worksheet.GetValue<string>(rowId, 32).HasValue())
{
if (!worksheet.GetValue<string>(rowId, 32).IsValidDateTime())
parseErrors[handle].Add("Publish Date is not a valid date.");
else
product.PublishDate = worksheet.GetValue<DateTime>(rowId, 32);
}
}
private static ProductVariantImportDataTransferObject GetProductVariant(Dictionary<string, List<string>> parseErrors, ExcelWorksheet worksheet,
int rowId, string handle)
{
var productVariant = new ProductVariantImportDataTransferObject
{
Name = worksheet.GetValue<string>(rowId, 11)
};
if (!GeneralHelper.IsValidInput<decimal>(worksheet.GetValue<string>(rowId, 12)))
parseErrors[handle].Add("Price value is not a valid decimal number.");
else if (worksheet.GetValue<string>(rowId, 12).HasValue())
productVariant.Price =
GeneralHelper.GetValue<decimal>(worksheet.GetValue<string>(rowId, 12));
else
parseErrors[handle].Add("Price is required.");
if (!GeneralHelper.IsValidInput<decimal>(worksheet.GetValue<string>(rowId, 13)))
parseErrors[handle].Add(
"Previous Price value is not a valid decimal number.");
else
productVariant.PreviousPrice =
GeneralHelper.GetValue<decimal>(worksheet.GetValue<string>(rowId, 13));
if (!GeneralHelper.IsValidInput<int>(worksheet.GetValue<string>(rowId, 14)))
parseErrors[handle].Add("Tax Rate Id value is not a valid number.");
else
productVariant.TaxRate =
GeneralHelper.GetValue<int>(worksheet.GetValue<string>(rowId, 14));
if (!GeneralHelper.IsValidInput<decimal>(worksheet.GetValue<string>(rowId, 15)))
parseErrors[handle].Add("Weight value is not a valid decimal number.");
else
productVariant.Weight =
GeneralHelper.GetValue<decimal>(worksheet.GetValue<string>(rowId, 15));
if (!GeneralHelper.IsValidInput<int>(worksheet.GetValue<string>(rowId, 16)))
parseErrors[handle].Add("Stock value is not a valid decimal number.");
else
productVariant.Stock = worksheet.HasValue(rowId, 16)
? GeneralHelper.GetValue<int>(
worksheet.GetValue<string>(rowId, 16))
: (int?) null;
if (!worksheet.GetValue<string>(rowId, 17).HasValue() ||
(worksheet.GetValue<string>(rowId, 17) != "Track" &&
worksheet.GetValue<string>(rowId, 17) != "DontTrack"))
parseErrors[handle].Add(
"Tracking Policy must have either 'Track' or 'DontTrack' value.");
else
{
productVariant.TrackingPolicy = worksheet.GetValue<string>(rowId, 17) == "Track"
? TrackingPolicy.Track
: TrackingPolicy.DontTrack;
}
if (worksheet.GetValue<string>(rowId, 18).HasValue())
productVariant.SKU = worksheet.GetValue<string>(rowId, 18);
else
parseErrors[handle].Add("SKU is required.");
productVariant.Barcode = worksheet.GetValue<string>(rowId, 19);
productVariant.ManufacturerPartNumber = worksheet.GetValue<string>(rowId, 20);
productVariant.ETag = worksheet.GetValue<string>(rowId, 33);
return productVariant;
}
private static void GetProductVariantOptions(ExcelWorksheet worksheet, int rowId,
ProductVariantImportDataTransferObject productVariant)
{
if (worksheet.GetValue<string>(rowId, 21).HasValue() &&
worksheet.GetValue<string>(rowId, 22).HasValue())
productVariant.Options.Add(worksheet.GetValue<string>(rowId, 21),
worksheet.GetValue<string>(rowId, 22));
if (worksheet.GetValue<string>(rowId, 23).HasValue() &&
worksheet.GetValue<string>(rowId, 24).HasValue())
productVariant.Options.Add(worksheet.GetValue<string>(rowId, 23),
worksheet.GetValue<string>(rowId, 24));
if (worksheet.GetValue<string>(rowId, 25).HasValue() &&
worksheet.GetValue<string>(rowId, 26).HasValue())
productVariant.Options.Add(worksheet.GetValue<string>(rowId, 25),
worksheet.GetValue<string>(rowId, 26));
}
private static void GetPriceBreaks(Dictionary<string, List<string>> parseErrors, ExcelWorksheet worksheet, int rowId, string handle,
ProductVariantImportDataTransferObject productVariant)
{
if (!String.IsNullOrWhiteSpace(worksheet.GetValue<string>(rowId, 30)))
{
try
{
var value = worksheet.GetValue<string>(rowId, 30);
if (!value.Contains(":"))
{
parseErrors[handle].Add(
"Product Variant Price Breaks field value contains illegal characters / not in correct format. Quantity and Price (Item) must be split with :, and items must be split by ;");
}
else
{
var priceBreaks = value.Split(';');
foreach (var item in priceBreaks)
{
if (!String.IsNullOrWhiteSpace(item))
{
var priceBreak = item.Split(':');
if (!String.IsNullOrWhiteSpace(priceBreak[0]) &&
!String.IsNullOrWhiteSpace(priceBreak[1]) &&
!productVariant.PriceBreaks.ContainsKey(
Int32.Parse(priceBreak[0])))
{
var quantity = Int32.Parse(priceBreak[0]);
var price = Decimal.Parse(priceBreak[1]);
productVariant.PriceBreaks.Add(quantity, price);
}
}
}
}
}
catch (ArgumentException)
{
parseErrors[handle].Add(
"Product Variant Price Breaks field contains duplicate price breaks.");
}
catch (Exception)
{
parseErrors[handle].Add(
"Product Variant Price Breaks field value contains illegal characters / not in correct format. Quantity and Price (Item) must be split with :, and items must be split by ;");
}
}
}
// プロセスを実行
private static void DoProcess(IProgress<int> progress, IProgress<string> logger, ExcelWorksheet i_sheet, ExcelWorksheet o_sheet)
{
var start_pos = 0;
if (checkbox1_state)
{
o_sheet.Cells[1, 1].Value = "URL";
o_sheet.Cells[1, 2].Value = "タイトル";
o_sheet.Cells[1, 3].Value = "デスクリプション";
o_sheet.Cells[1, 4].Value = "キーワード";
var header_cells = o_sheet.Cells[1, 1, 1, 4];
header_cells.Style.Font.Bold = true;
start_pos = 1;
}
int num_of_row = i_sheet.Dimension.End.Row;
foreach (var index in Enumerable.Range(1 + start_pos, num_of_row - start_pos))
{
if (!f1.isProgress)
{
break;
}
//
string url = i_sheet.GetValue(index, 1).ToString(); //これは保証できない
string title = "";
string description = "";
string keywords = "";
//
logger.Report(index.ToString() + "/" + num_of_row.ToString() + ": " + url + " を解析中...");
int percentage = index * 100 / num_of_row;
progress.Report(percentage);
//URLの確認
Uri uri = null;
HttpWebResponse res = null;
if (Uri.TryCreate(url, UriKind.Absolute, out uri))
{
//ページの存在の確認
var results = GetStatusAndContent(url, false);
if (300 <= results.StatusCode && results.StatusCode < 400)
{
title = "_リダイレクトされました_";
description = results.Url;
var error_cells = o_sheet.Cells[index, 1, index, 3];
error_cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
error_cells.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
else if (400 <= results.StatusCode && results.StatusCode < 500)
{
title = "_ページが見つかりません_";
description = "";
var error_cells = o_sheet.Cells[index, 1, index, 3];
error_cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
error_cells.Style.Fill.BackgroundColor.SetColor(Color.Red);
}
else if (500 <= results.StatusCode)
{
title = "_サーバエラーです_";
description = "";
var error_cells = o_sheet.Cells[index, 1, index, 3];
error_cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
error_cells.Style.Fill.BackgroundColor.SetColor(Color.Red);
}
else
{
//status 200
var xml = ParseHtmlFromText((string)results.Content);
if (xml == null) continue;
XNamespace ns = xml.Root.Name.Namespace;
foreach (var item in xml.Descendants(ns + "meta"))
{
XAttribute att = item.Attribute("name");
if (att != null)
{
if (att.Value == "description")
{
description = item.Attribute("content").Value;
}
else if (att.Value == "keywords")
{
keywords = item.Attribute("content").Value;
}
}
var elem = xml.Descendants(ns + "title");
var titleElem = elem.FirstOrDefault();
if (titleElem != null)
{
title = titleElem.Value;
}
}
}
}
else //不正なURI
{
title = "_不正なURIです_";
description = "";
var error_cells = o_sheet.Cells[index, 1, index, 3];
error_cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
error_cells.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
o_sheet.Cells[index, 1].Value = url;
o_sheet.Cells[index, 2].Value = title;
o_sheet.Cells[index, 3].Value = description;
o_sheet.Cells[index, 4].Value = keywords;
}
if (f1.processPercentage == 100)
{
logger.Report("終了しました！");
}
return;
}
private DocumentImportDTO GetDocumentImportDataTransferObject(ExcelWorksheet worksheet, int rowId,
string name, ref List<string> parseErrors)
{
var item = new DocumentImportDTO();
item.ParentUrl = worksheet.GetValue<string>(rowId, 2);
if (worksheet.GetValue<string>(rowId, 3).HasValue())
{
item.DocumentType = worksheet.GetValue<string>(rowId, 3);
item.UrlSegment = worksheet.GetValue<string>(rowId, 1).HasValue()
? worksheet.GetValue<string>(rowId, 1)
: _webpageUrlService.Suggest(null,
new SuggestParams { PageName = name, DocumentType = item.DocumentType });
}
else
parseErrors.Add("Document Type is required.");
if (worksheet.GetValue<string>(rowId, 4).HasValue())
item.Name = worksheet.GetValue<string>(rowId, 4);
else
parseErrors.Add("Document Name is required.");
item.BodyContent = worksheet.GetValue<string>(rowId, 5);
item.MetaTitle = worksheet.GetValue<string>(rowId, 6);
item.MetaDescription = worksheet.GetValue<string>(rowId, 7);
item.MetaKeywords = worksheet.GetValue<string>(rowId, 8);
item.Tags = GetTags(worksheet, rowId, parseErrors);
if (worksheet.GetValue<string>(rowId, 10).HasValue())
{
if (!worksheet.GetValue<string>(rowId, 10).IsValidInput<bool>())
parseErrors.Add("Reveal in Navigation is not a valid boolean value.");
else
item.RevealInNavigation = worksheet.GetValue<bool>(rowId, 10);
}
else
item.RevealInNavigation = false;
if (worksheet.GetValue<string>(rowId, 11).HasValue())
{
if (!worksheet.GetValue<string>(rowId, 11).IsValidInput<int>())
parseErrors.Add("Display Order is not a valid number.");
else
item.DisplayOrder = worksheet.GetValue<int>(rowId, 11);
}
else
item.DisplayOrder = 0;
if (worksheet.GetValue<string>(rowId, 12).HasValue())
{
if (!worksheet.GetValue<string>(rowId, 12).IsValidInput<bool>())
parseErrors.Add("Require SSL is not a valid boolean value.");
else
item.RequireSSL = worksheet.GetValue<bool>(rowId, 12);
}
else
item.RequireSSL = false;
if (worksheet.GetValue<string>(rowId, 13).HasValue())
{
if (!worksheet.GetValue<string>(rowId, 13).IsValidInputDateTime())
parseErrors.Add("Publish Date is not a valid date.");
else
item.PublishDate = worksheet.GetValue<DateTime>(rowId, 13);
}
item.UrlHistory = GetUrlHistory(worksheet, rowId, parseErrors);
return item;
}
private void LesVåpenOppsett(ExcelWorksheet sheet, int row, ExcelMatch match)
{
var våpen = sheet.GetValue(ExcelSheet.Match.Pr_lag_FELLE, row);
if (!string.IsNullOrEmpty(våpen))
match.PrLagFelle = int.Parse(våpen);
våpen = sheet.GetValue(ExcelSheet.Match.Pr_lag_BOMBE, row);
if (!string.IsNullOrEmpty(våpen))
match.PrLagBombe = int.Parse(våpen);
}
/// <summary>
/// 获取所有的行
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
public List<Dictionary<string, object>> GetRows(ExcelWorksheet sheet)
{
var rows = new List<Dictionary<string, object>>();
for (var rowIndex = 2; rowIndex <= sheet.Dimension.Rows; rowIndex++)
{
var row = new Dictionary<string, object>();
for (var columnIndex = 1; columnIndex <= sheet.Dimension.Columns; columnIndex++)
{
var cellName = sheet.GetValue(1, columnIndex);
if (cellName == null) continue;
var cellNameString = cellName.ToString();
object cellValue = sheet.GetValue<string>(rowIndex, columnIndex);
var cellType = this.GetExcelType(cellNameString);
if (this._converts != null && this._converts.ContainsKey(cellType.FullName))
{
//优先使用用户定义的转换器
cellValue = this._converts[cellType.FullName](sheet, rowIndex, columnIndex);
}
else
{
if (this.GlobalConverts.ContainsKey(cellType.FullName))
{
//如果没有匹配的 使用内置的转换器
cellValue = this.GlobalConverts[cellType.FullName](sheet, rowIndex, columnIndex);
}
}
if (row.ContainsKey(cellNameString))
{
row.Add(cellNameString + "1", cellValue);
continue;
}
row.Add(cellNameString, cellValue);
}
rows.Add(row);
}
return rows;
}
private static void GetImages(ExcelWorksheet worksheet, int rowId, ProductImportDataTransferObject product)
{
//Images
if (worksheet.GetValue<string>(rowId, 27).HasValue())
product.Images.Add(worksheet.GetValue<string>(rowId, 27));
if (worksheet.GetValue<string>(rowId, 28).HasValue())
product.Images.Add(worksheet.GetValue<string>(rowId, 28));
if (worksheet.GetValue<string>(rowId, 29).HasValue())
product.Images.Add(worksheet.GetValue<string>(rowId, 29));
}
private static void GetUrlHistory(Dictionary<string, List<string>> parseErrors, ExcelWorksheet worksheet, int rowId,
ProductImportDataTransferObject product, string handle)
{
//Url History
try
{
var value = worksheet.GetValue<string>(rowId, 31);
if (!String.IsNullOrWhiteSpace(value))
{
var urlHistory = value.Split(',');
foreach (var item in urlHistory)
{
if (!String.IsNullOrWhiteSpace(item))
{
product.UrlHistory.Add(item);
}
}
}
}
catch (Exception)
{
parseErrors[handle].Add(
"Product Url History field value contains illegal characters / not in correct format.");
}
}
private static List<string> GetTags(ExcelWorksheet worksheet, int rowId, List<string> parseErrors)
{
List<string> tagList = new List<string>();
try
{
var value = worksheet.GetValue<string>(rowId, 9);
if (!String.IsNullOrWhiteSpace(value))
{
var tags = value.Split(',');
foreach (var tag in tags.Where(tag => !String.IsNullOrWhiteSpace(tag)))
{
tagList.Add(tag);
}
}
}
catch (Exception)
{
parseErrors.Add(
"Url History field value contains illegal characters / not in correct format.");
}
return tagList;
}