{"id":11809,"date":"2019-07-17T09:27:42","date_gmt":"2019-07-17T00:27:42","guid":{"rendered":"https:\/\/elleneast.com\/?p=11809"},"modified":"2019-07-17T09:27:42","modified_gmt":"2019-07-17T00:27:42","slug":"%e3%80%90vb-net%e3%80%91closedxml%ef%bc%9a%e3%83%87%e3%83%bc%e3%82%bf%e3%81%ae%e6%a4%9c%e7%b4%a2%e3%81%a8%e6%8a%bd%e5%87%ba","status":"publish","type":"post","link":"https:\/\/elleneast.com\/?p=11809","title":{"rendered":"\u3010VB.NET\u3011ClosedXML\uff1a\u30c7\u30fc\u30bf\u306e\u691c\u7d22\u3068\u62bd\u51fa"},"content":{"rendered":"[include-page id=\u201d11760\u2033]\n<p>\u4e0b\u56f3\u306e\u3088\u3046\u306a\u65e2\u5b58\u306e\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u304c\u3042\u308b\u3068\u3057\u307e\u3059\u3002<\/p>\n<p><a href=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_07b.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-11822 bigshadow\" src=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_07b.png\" alt=\"\" width=\"572\" height=\"348\" srcset=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_07b.png 572w, https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_07b-300x183.png 300w\" sizes=\"auto, (max-width: 572px) 100vw, 572px\"><\/a><\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_83 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">\u76ee\u6b21<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/elleneast.com\/?p=11809\/#%E5%89%8D%E6%8F%90%E6%9D%A1%E4%BB%B6\" >\u524d\u63d0\u6761\u4ef6<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/elleneast.com\/?p=11809\/#%E5%87%A6%E7%90%86%E5%86%85%E5%AE%B9\" >\u51e6\u7406\u5185\u5bb9<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/elleneast.com\/?p=11809\/#%E3%82%B3%E3%83%BC%E3%83%89\" >\u30b3\u30fc\u30c9<\/a><\/li><\/ul><\/nav><\/div>\n<h2 id=\"i-0\"><span class=\"ez-toc-section\" id=\"%E5%89%8D%E6%8F%90%E6%9D%A1%E4%BB%B6\"><\/span>\u524d\u63d0\u6761\u4ef6<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<ul>\n<li>\u5bfe\u8c61\u30c7\u30fc\u30bf\u306f\u300c\u4f1a\u793e\u30c7\u30fc\u30bf\u300d\u3068\u3044\u3046\u540d\u524d\u306e\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u306b\u3042\u308b\u3068\u3057\u307e\u3059\u3002<\/li>\n<li>\u5bfe\u8c61\u306e\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u306b\u306f\uff11\u3064\u4ee5\u4e0a\u306e\u7a7a\u884c\u3092\u631f\u3093\u3067\uff12\u3064\u306e\u30c6\u30fc\u30d6\u30eb\uff08\u30ab\u30c6\u30b4\u30ea\u30c6\u30fc\u30d6\u30eb\u3001\u4f1a\u793e\u30c6\u30fc\u30d6\u30eb\uff09\u304c\u3042\u308b\u3068\u3057\u307e\u3059\u3002<\/li>\n<li>\u3044\u305a\u308c\u306e\u30c6\u30fc\u30d6\u30eb\u3082\u958b\u59cb\u884c\u30fb\u958b\u59cb\u5217\u304c\u5909\u308f\u308b\u53ef\u80fd\u6027\u304c\u3042\u308b\u3068\u3057\u307e\u3059\uff08\u30d7\u30ed\u30b0\u30e9\u30e0\u3067\u30c6\u30fc\u30d6\u30eb\u306e\u5834\u6240\u3092\u63a2\u3059\uff09\u3002<\/li>\n<li>\u6700\u521d\u306e\u30c6\u30fc\u30d6\u30eb\u306e\u5217\u30bf\u30a4\u30c8\u30eb\u306f\u5909\u308f\u308b\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\u304c\u3001\u5217\u9806\u306f\u5909\u308f\u3089\u306a\u3044\u3068\u3057\u307e\u3059\uff08\u5217\u4f4d\u7f6e\u304b\u3089\u5bfe\u8c61\u30c7\u30fc\u30bf\u3092\u63a2\u3059\uff09\u3002<\/li>\n<li>\uff12\u3064\u76ee\u306e\u30c6\u30fc\u30d6\u30eb\u306e\u5217\u30bf\u30a4\u30c8\u30eb\u306f\u5909\u308f\u308a\u307e\u305b\u3093\u304c\u3001\u5217\u9806\u306f\u5909\u308f\u308b\u53ef\u80fd\u6027\u304c\u3042\u308b\u3068\u3057\u307e\u3059\uff08\u5217\u30bf\u30a4\u30c8\u30eb\u304b\u3089\u5bfe\u8c61\u30c7\u30fc\u30bf\u3092\u63a2\u3059\uff09\u3002<\/li>\n<\/ul>\n<h2 id=\"i-1\"><span class=\"ez-toc-section\" id=\"%E5%87%A6%E7%90%86%E5%86%85%E5%AE%B9\"><\/span>\u51e6\u7406\u5185\u5bb9<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>\u30ab\u30c6\u30b4\u30ea\u540d\u306e\u30ea\u30b9\u30c8\u3068\u4f1a\u793e\u540d\u306e\u30ea\u30b9\u30c8\u3092\u62bd\u51fa\u3057\u307e\u3059\u3002<\/p>\n<h2 id=\"i-2\"><span class=\"ez-toc-section\" id=\"%E3%82%B3%E3%83%BC%E3%83%89\"><\/span>\u30b3\u30fc\u30c9<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>\u547c\u3073\u51fa\u3057\u5074\uff08Main \u306a\u3069\uff09<\/p>\n<pre class=\"lang:vbnet decode:true\">Dim categories As New List(Of String)\nDim companies As New List(Of String)\nCall \u30ab\u30c6\u30b4\u30ea\u540d\u3068\u4f1a\u793e\u540d\u3092\u53d6\u5f97(\"D:\\test\\\u4f1a\u793e\u30c7\u30fc\u30bf.xlsx\", categories, companies)\n\n'\u53d6\u5f97\u3057\u305f\u30ea\u30b9\u30c8\u3092\u597d\u304d\u306a\u3088\u3046\u306b\u51e6\u7406\u3057\u307e\u3057\u3087\u3046\n\nConsole.WriteLine(vbCrLf &amp; \"===== \u30ab\u30c6\u30b4\u30ea =====\")\nFor Each category As String In categories\n    Console.WriteLine(category)\nNext\n\nConsole.WriteLine(vbCrLf &amp; \"===== \u4f1a\u793e =====\")\nFor Each company As String In companies\n    Console.WriteLine(company)\nNext<\/pre>\n<p>&nbsp;<\/p>\n<p>\u300c\u30ab\u30c6\u30b4\u30ea\u540d\u3068\u4f1a\u793e\u540d\u3092\u53d6\u5f97\u300d\u95a2\u6570<\/p>\n<pre class=\"lang:vbnet decode:true\">Private Sub \u30ab\u30c6\u30b4\u30ea\u540d\u3068\u4f1a\u793e\u540d\u3092\u53d6\u5f97(targetFileFullname As String, categories As List(Of String), companies As List(Of String))\n\n'\u5bfe\u8c61\u30d5\u30a1\u30a4\u30eb\u3092\u958b\u3044\u3066\u30ef\u30fc\u30af\u30d6\u30c3\u30af\u3092\u53d6\u5f97\nDim wb = new XLWorkbook(targetFileFullname)\n'\u30b7\u30fc\u30c8\u540d\u304b\u3089\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u3092\u53d6\u5f97\nDim ws = wb.Worksheet(\"\u4f1a\u793e\u30c7\u30fc\u30bf\")\n\nConst categoryIdColNum As Integer = 1 '\u30ab\u30c6\u30b4\u30eaID \u306e\u5217\u306e\u4f4d\u7f6e\nConst categoryNameColNum As Integer = 2 '\u30ab\u30c6\u30b4\u30ea\u540d\u306e\u5217\u306e\u4f4d\u7f6e\n\n'\u4f7f\u7528\u3055\u308c\u3066\u3044\u308b\u6700\u521d\u306e\uff11\u884c\u5168\u4f53\u3092\u53d6\u5f97\uff08\u3053\u308c\u306b\u306f\u672a\u4f7f\u7528\u306e\u5217\u7bc4\u56f2\u3082\u542b\u307e\u308c\u3066\u3044\u308b\uff09\u3002\nDim firstRowUsed = ws.FirstRowUsed\n\n'\u4f7f\u7528\u3055\u308c\u3066\u3044\u308b\u6700\u521d\u306e\uff11\u884c\u5168\u4f53\u306e\u7bc4\u56f2\u304b\u3089\u3001\u4f7f\u7528\u3055\u308c\u3066\u3044\u308b\u7bc4\u56f2\u306e\u307f\u3092\u6c42\u3081\u308b\uff08\u4e0d\u8981\u306a\u5217\u7bc4\u56f2\u3092\u53d6\u308a\u9664\u304f\uff09\nDim categoryRow = firstRowUsed.RowUsed '\u73fe\u5728\u3001\u30bf\u30a4\u30c8\u30eb\u884c\u3092\u6307\u3057\u3066\u3044\u308b\n\n'\u5bfe\u8c61\u7bc4\u56f2\u3092\u6b21\u306e\u884c\u306b\u79fb\u52d5\u3059\u308b\uff08\u3053\u3053\u304b\u3089\u30c7\u30fc\u30bf\u884c\uff09\ncategoryRow = categoryRow.RowBelow\n\n'\u30ab\u30c6\u30b4\u30ea ID \u306e\u5217\u306b ID \u304c\u8a18\u5165\u3055\u308c\u3066\u3044\u308b\u9650\u308a\u3001\u884c\u3092\u9806\u306b\u51e6\u7406\u3057\u3066\u30ab\u30c6\u30b4\u30ea\u540d\u3092\u53d6\u5f97\u3057\u3066\u3044\u304f\nWhile categoryRow.Cell(categoryIdColNum).IsEmpty = False\n    Dim categoryName As String = categoryRow.Cell(categoryNameColNum).GetString\n    categories.Add(categoryName)\n    categoryRow = categoryRow.RowBelow\nEnd While\n\n'\u4f1a\u793e\u30c6\u30fc\u30d6\u30eb\u3092\u53d6\u5f97\u3059\u308b\u65b9\u6cd5\u306f\u3044\u308d\u3044\u308d\u3042\u308a\u307e\u3059\u3002\n'\u3053\u3053\u3067\u306f\u76f4\u63a5\u7684\u306a\u3084\u308a\u65b9\u3092\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002\n'\u4ed6\u306e\u65b9\u6cd5\u3068\u3057\u3066\u306f\u3001\u305f\u3068\u3048\u3070\u3001row.IsEmpty \u304c\u771f\u3067\u3042\u308b\u9650\u308a\u30eb\u30fc\u30d7\u51e6\u7406\u3057\u3066\n'\u4f1a\u793e\u30c6\u30fc\u30d6\u30eb\u306e\u6700\u521d\u306e\u884c\u3092\u898b\u3064\u3051\u308b\u3053\u3068\u3082\u3067\u304d\u307e\u3059\u3002\n\n'\u4f1a\u793e\u30c6\u30fc\u30d6\u30eb\u306e\u958b\u59cb\u4f4d\u7f6e\u306b\u306a\u308a\u5f97\u308b\u6700\u521d\u306e\u30a2\u30c9\u30ec\u30b9\u3092\u53d6\u5f97\n'\uff08\u73fe\u5728 categoryRow \u306f\u30ab\u30c6\u30b4\u30ea\u30c6\u30fc\u30d6\u30eb\u306e\uff11\u3064\u4e0b\u306e\u884c\u3092\u6307\u3057\u3066\u3044\u308b\uff09\nDim firstPossibleAddress = ws.Row(categoryRow.RowNumber).FirstCell.Address\n'\u4f1a\u793e\u30c6\u30fc\u30d6\u30eb\u306e\u7d42\u4e86\u4f4d\u7f6e\u306b\u306a\u308a\u5f97\u308b\u6700\u5f8c\u306e\u30a2\u30c9\u30ec\u30b9\u3092\u53d6\u5f97\nDim lastPossibleAddress = ws.LastCellUsed.Address\n\n'\u4f1a\u793e\u30c6\u30fc\u30d6\u30eb\u304c\u5b58\u5728\u3057\u5f97\u308b\u7bc4\u56f2\u304b\u3089\u3001\u4f7f\u7528\u3055\u308c\u3066\u3044\u308b\u7bc4\u56f2\u3092\u53d6\u5f97\u3059\u308b\nDim companyRange = ws.Range(firstPossibleAddress, lastPossibleAddress).RangeUsed\n\n'\u53d6\u5f97\u3057\u305f\u7bc4\u56f2\u3092\u30c6\u30fc\u30d6\u30eb\u3068\u3057\u3066\u6271\u3046\uff08\u5217\u30bf\u30a4\u30c8\u30eb\u3092\u4f7f\u7528\u3059\u308b\u305f\u3081\uff09\nDim companyTable = companyRange.AsTable\n\n'\u4f1a\u793e\u540d\u306e\u30ea\u30b9\u30c8\u3092\u53d6\u5f97\n' \u2192 \u4f1a\u793e\u30c6\u30fc\u30d6\u30eb\u306e\u30c7\u30fc\u30bf\u7bc4\u56f2\u306e\u884c\u3092\u53d6\u5f97\n' \u2192 \u884c\u3092\u9806\u6b21\u51e6\u7406\u3057\u3066\u300c\u4f1a\u793e\u540d\u300d\u5217\u306e\u5024\u3092\u30ea\u30b9\u30c8\u306b\u3057\u3066\u8fd4\u3059\n' \u2192 companies \u306b\u683c\u7d0d\ncompanies.AddRange(\n    companyTable.DataRange.Rows _\n    .Select(Function(companyRow) companyRow.Field(\"\u4f1a\u793e\u540d\").GetString) _\n    .ToList\n)\n\nEnd Sub<\/pre>\n<p>&nbsp;<\/p>\n<p>\u5b9f\u884c\u7d50\u679c<\/p>\n<p><a href=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_06c.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-11821 bigshadow\" src=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_06c.png\" alt=\"\" width=\"182\" height=\"195\"><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/elleneast.com\/?cat=90\">\u300c\u3010VB.NET\u3011ClosedXML \u306e\u4f7f\u3044\u65b9\u300d\u306e\u4e00\u89a7\u3092\u8868\u793a<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>[include-page id=\u201d11760\u2033] \u4e0b\u56f3\u306e\u3088\u3046\u306a\u65e2\u5b58\u306e\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u304c\u3042\u308b\u3068\u3057\u307e\u3059\u3002 \u524d\u63d0\u6761\u4ef6 \u5bfe\u8c61\u30c7\u30fc\u30bf\u306f\u300c\u4f1a\u793e\u30c7\u30fc\u30bf\u300d\u3068\u3044\u3046\u540d\u524d\u306e\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u306b\u3042\u308b\u3068\u3057\u307e\u3059\u3002 \u5bfe\u8c61\u306e\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u306b\u306f\uff11\u3064\u4ee5\u4e0a\u306e\u7a7a\u884c\u3092\u631f\u3093 &#8230; <\/p>\n","protected":false},"author":1,"featured_media":2656,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[90],"tags":[],"class_list":{"0":"post-11809","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-closedxml"},"_links":{"self":[{"href":"https:\/\/elleneast.com\/index.php?rest_route=\/wp\/v2\/posts\/11809","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/elleneast.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/elleneast.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/elleneast.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/elleneast.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11809"}],"version-history":[{"count":3,"href":"https:\/\/elleneast.com\/index.php?rest_route=\/wp\/v2\/posts\/11809\/revisions"}],"predecessor-version":[{"id":12381,"href":"https:\/\/elleneast.com\/index.php?rest_route=\/wp\/v2\/posts\/11809\/revisions\/12381"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/elleneast.com\/index.php?rest_route=\/wp\/v2\/media\/2656"}],"wp:attachment":[{"href":"https:\/\/elleneast.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11809"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/elleneast.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11809"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/elleneast.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11809"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}