C# ExcelWorksheet.GetValue方法代码示例

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

        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 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 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 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];
//.........这里部分代码省略.........

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