{"id":12112,"date":"2019-07-17T09:27:53","date_gmt":"2019-07-17T00:27:53","guid":{"rendered":"https:\/\/elleneast.com\/?p=12112"},"modified":"2019-07-17T09:27:53","modified_gmt":"2019-07-17T00:27:53","slug":"%e3%80%90vb-net%e3%80%91closedxml%ef%bc%9a%e3%82%bd%e3%83%bc%e3%83%88","status":"publish","type":"post","link":"https:\/\/elleneast.com\/?p=12112","title":{"rendered":"\u3010VB.NET\u3011ClosedXML\uff1a\u30bd\u30fc\u30c8"},"content":{"rendered":"[include-page id=\u201d11760\u2033]\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 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=12112\/#%E4%BA%8B%E5%89%8D%E6%BA%96%E5%82%99\" >\u4e8b\u524d\u6e96\u5099<\/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=12112\/#%E7%AF%84%E5%9B%B2%E3%81%AE%E5%8D%98%E7%B4%94%E3%82%BD%E3%83%BC%E3%83%88\" >\u7bc4\u56f2\u306e\u5358\u7d14\u30bd\u30fc\u30c8<\/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=12112\/#%EF%BC%91%E3%81%A4%E3%81%AE%E5%88%97%E3%81%AE%E3%82%BD%E3%83%BC%E3%83%88\" >\uff11\u3064\u306e\u5217\u306e\u30bd\u30fc\u30c8<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/elleneast.com\/?p=12112\/#%E8%A4%87%E9%9B%91%E3%81%AA%E3%82%BD%E3%83%BC%E3%83%88%EF%BC%88%EF%BC%91%EF%BC%89\" >\u8907\u96d1\u306a\u30bd\u30fc\u30c8\uff08\uff11\uff09<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/elleneast.com\/?p=12112\/#%E8%A4%87%E9%9B%91%E3%81%AA%E3%82%BD%E3%83%BC%E3%83%88%EF%BC%88%EF%BC%92%EF%BC%89\" >\u8907\u96d1\u306a\u30bd\u30fc\u30c8\uff08\uff12\uff09<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/elleneast.com\/?p=12112\/#%E5%B7%A6%E3%81%8B%E3%82%89%E5%8F%B3%E3%81%AB%E3%82%BD%E3%83%BC%E3%83%88\" >\u5de6\u304b\u3089\u53f3\u306b\u30bd\u30fc\u30c8<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/elleneast.com\/?p=12112\/#%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE%E3%82%BD%E3%83%BC%E3%83%88\" >\u30c6\u30fc\u30d6\u30eb\u306e\u30bd\u30fc\u30c8<\/a><\/li><\/ul><\/nav><\/div>\n<h2 id=\"i-0\"><span class=\"ez-toc-section\" id=\"%E4%BA%8B%E5%89%8D%E6%BA%96%E5%82%99\"><\/span>\u4e8b\u524d\u6e96\u5099<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>\u307e\u305a\u3001\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u633f\u5165\u7528\u306e\u95a2\u6570\u306a\u3069\u3092\u7528\u610f\u3057\u307e\u3059\u3002<\/p>\n<pre class=\"lang:vbnet decode:true \">'\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u633f\u5165\u7528\nPrivate Sub AddTestColumn(ws As IXLWorksheet)\n  ws.Cell(\"A1\").SetValue(\"B\").Style.Fill.SetBackgroundColor(XLColor.LightGreen)\n  ws.Cell(\"A2\").SetValue(\"A\").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise)\n  ws.Cell(\"A3\").SetValue(\"a\").Style.Fill.SetBackgroundColor(XLColor.BurlyWood)\n  ws.Cell(\"A4\").SetValue(\"\").Style.Fill.SetBackgroundColor(XLColor.DarkGray)\n  ws.Cell(\"A5\").SetValue(\"\").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon)\n  ws.Cell(\"A6\").SetValue(\"b\").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue)\n  ws.Cell(\"A7\").SetValue(\"B\").Style.Fill.SetBackgroundColor(XLColor.IndianRed)\n  ws.Cell(\"A8\").SetValue(\"c\").Style.Fill.SetBackgroundColor(XLColor.DeepPink)\nEnd Sub\n\n'\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u633f\u5165\u7528\nPrivate Sub AddTestTable(ws As IXLWorksheet)\n  ws.Cell(\"A1\").SetValue(\"B\").Style.Fill.SetBackgroundColor(XLColor.LightGreen)\n  ws.Cell(\"A2\").SetValue(\"A\").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise)\n  ws.Cell(\"A3\").SetValue(\"a\").Style.Fill.SetBackgroundColor(XLColor.BurlyWood)\n  ws.Cell(\"A4\").SetValue(\"A\").Style.Fill.SetBackgroundColor(XLColor.DarkGray)\n  ws.Cell(\"A5\").SetValue(\"\").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon)\n  ws.Cell(\"A6\").SetValue(\"A\").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue)\n  ws.Cell(\"A7\").SetValue(\"a\").Style.Fill.SetBackgroundColor(XLColor.IndianRed)\n  ws.Cell(\"A8\").SetValue(\"B\").Style.Fill.SetBackgroundColor(XLColor.DeepPink)\n\n  ws.Cell(\"B1\").SetValue(\"\").Style.Fill.SetBackgroundColor(XLColor.LightGreen)\n  ws.Cell(\"B2\").SetValue(\"a\").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise)\n  ws.Cell(\"B3\").SetValue(\"B\").Style.Fill.SetBackgroundColor(XLColor.BurlyWood)\n  ws.Cell(\"B4\").SetValue(\"A\").Style.Fill.SetBackgroundColor(XLColor.DarkGray)\n  ws.Cell(\"B5\").SetValue(\"a\").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon)\n  ws.Cell(\"B6\").SetValue(\"A\").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue)\n  ws.Cell(\"B7\").SetValue(\"a\").Style.Fill.SetBackgroundColor(XLColor.IndianRed)\n  ws.Cell(\"B8\").SetValue(\"a\").Style.Fill.SetBackgroundColor(XLColor.DeepPink)\n\n  ws.Cell(\"C1\").SetValue(\"A\").Style.Fill.SetBackgroundColor(XLColor.LightGreen)\n  ws.Cell(\"C2\").SetValue(\"\").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise)\n  ws.Cell(\"C3\").SetValue(\"A\").Style.Fill.SetBackgroundColor(XLColor.BurlyWood)\n  ws.Cell(\"C4\").SetValue(\"a\").Style.Fill.SetBackgroundColor(XLColor.DarkGray)\n  ws.Cell(\"C5\").SetValue(\"A\").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon)\n  ws.Cell(\"C6\").SetValue(\"b\").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue)\n  ws.Cell(\"C7\").SetValue(\"A\").Style.Fill.SetBackgroundColor(XLColor.IndianRed)\n  ws.Cell(\"C8\").SetValue(\"\").Style.Fill.SetBackgroundColor(XLColor.DeepPink)\nEnd Sub\n\n'\u77e2\u5370\u3092\u633f\u5165\nPrivate Sub InsertArrow(range As IXLRange, Optional arrowText As String = \"\u2192\")\n\nrange.Merge.SetValue(arrowText).Style.Alignment _\n    .SetVertical(XLAlignmentVerticalValues.Center).Alignment _\n    .SetHorizontal(XLAlignmentHorizontalValues.Center) _\n    .Font.SetFontSize(25).Font.SetBold(True) _\n    .Font.SetFontColor(XLColor.Red)\n\nEnd Sub<\/pre>\n<p>&nbsp;<\/p>\n<\/p>\n<div class=\"memo alert\">\n<div class=\"memo_ttl dfont\"> \u6ce8\u610f<\/div>\n<p>\u5143\u8a18\u4e8b\u306e\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u306f\u3001Blank\uff08\u7a7a\u767d\u3001\u7a7a\u6b04\uff09\u3092\u8868\u3059\u305f\u3081\u306b\u30b9\u30da\u30fc\u30b9\u6587\u5b57 \u201d \u201d \u3092\u4f7f\u7528\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<p>\u3068\u3053\u308d\u304c\u5143\u8a18\u4e8b\u306e\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u3092\u305d\u306e\u307e\u307e\u4f7f\u7528\u3059\u308b\u3068\u3001\u30bd\u30fc\u30c8\u3057\u3066\u3082\u3001\u5143\u8a18\u4e8b\u3068\u540c\u3058\u30bd\u30fc\u30c8\u7d50\u679c\u306b\u306f\u306a\u308a\u307e\u305b\u3093\u3067\u3057\u305f\u3002<\/p>\n<p>\u4f55\u5ea6\u304b\u8a66\u3057\u3066\u307f\u305f\u7d50\u679c\u3001ClosedXML \u306e Sort \u30e1\u30bd\u30c3\u30c9\u306e\u30aa\u30d7\u30b7\u30e7\u30f3\u3067\u3042\u308b \u201cIgnoreBlanks As Boolean\u201d \u306e \u201cBlanks\u201d \u306f\u30b9\u30da\u30fc\u30b9\u6587\u5b57\u306e\u3053\u3068\u3067\u306f\u306a\u304f\u3001\u7a7a\u6587\u5b57\u307e\u305f\u306f Nothing \u306e\u3053\u3068\u3060\u3068\u601d\u308f\u308c\u307e\u3059\u3002<\/p>\n<p>\u305d\u306e\u305f\u3081\u3001\u3053\u306e\u8a18\u4e8b\u3067\u306e\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u3067\u306f\u3001\u5143\u8a18\u4e8b\u3067\u30b9\u30da\u30fc\u30b9\u6587\u5b57 \u201d \u201d \u3060\u3063\u305f\u3068\u3053\u308d\u3092\u7a7a\u6587\u5b57 \u201c\u201d \u306b\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<p>by \u30a8\u30ec\u30f3\u30fb\u30a4\u30fc\u30b9\u30c8\uff08\u5f53\u30b5\u30a4\u30c8\u306e\u7ba1\u7406\u4eba\uff09<\/p>\n<\/p>\n<\/div>\n<h2 id=\"i-1\"><span class=\"ez-toc-section\" id=\"%E7%AF%84%E5%9B%B2%E3%81%AE%E5%8D%98%E7%B4%94%E3%82%BD%E3%83%BC%E3%83%88\"><\/span>\u7bc4\u56f2\u306e\u5358\u7d14\u30bd\u30fc\u30c8<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>\u7bc4\u56f2\u3092\u30b7\u30f3\u30d7\u30eb\u306b\u30bd\u30fc\u30c8\u3057\u307e\u3059\u3002<\/p>\n<p><a href=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_58.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-12118 bigshadow\" src=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_58.png\" alt=\"\" width=\"558\" height=\"259\" srcset=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_58.png 558w, https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_58-300x139.png 300w\" sizes=\"auto, (max-width: 558px) 100vw, 558px\"><\/a><\/p>\n<pre class=\"lang:vbnet decode:true\">Dim wb = New XLWorkbook\nDim ws = wb.Worksheets.Add(\"\u7bc4\u56f2\u3092\u30b7\u30f3\u30d7\u30eb\u306b\u30bd\u30fc\u30c8\")\n\n'\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u3092\u7528\u610f\nCall AddTestTable(ws)\n\n'\u7528\u610f\u3057\u305f\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u306e\u53f3\u5074\u306b\u30c7\u30fc\u30bf\u3092\u30b3\u30d4\u30fc\nDim rangeSimple = ws.RangeUsed\nDim copySimple = rangeSimple.CopyTo(ws.Column(ws.LastColumnUsed.ColumnNumber + 2))\n\n\n\n'\u30b3\u30d4\u30fc\u3057\u305f\u30c7\u30fc\u30bf\u3092\u30bd\u30fc\u30c8\ncopySimple.Sort\n\n\n\n'\u300c\u2192\u300d\u3092\u633f\u5165\nCall InsertArrow(ws.Range(\"D1:D8\"))\n\n'\u8aac\u660e\u6587\u3092\u633f\u5165\nws.Row(1).InsertRowsAbove(2)\nws.Cell(1, 1).SetValue(\".Sort = \u7bc4\u56f2\u3092\u4e0a\u304b\u3089\u4e0b\u3078\u6607\u9806\u30bd\u30fc\u30c8\u3001\u82f1\u5b57\u5927\u5c0f\u540c\u4e00\u8996\u3001\u7a7a\u767d\u3092\u7121\u8996\").Style.Font.SetBold\n'A1 \u304c\u9078\u629e\u3055\u308c\u3066\u308b\u3068\u8aac\u660e\u6587\u304c\u8aad\u307f\u306b\u304f\u3044\u306e\u3067\u5225\u306e\u3068\u3053\u308d\u3092\u9078\u629e\nws.LastCellUsed.CellRight.CellBelow.Select \n\nwb.SaveAs(\"D:\\test\\SortSimple.xlsx\")<\/pre>\n<h2 id=\"i-2\"><span class=\"ez-toc-section\" id=\"%EF%BC%91%E3%81%A4%E3%81%AE%E5%88%97%E3%81%AE%E3%82%BD%E3%83%BC%E3%83%88\"><\/span>\uff11\u3064\u306e\u5217\u306e\u30bd\u30fc\u30c8<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><a href=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_59.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-12117 bigshadow\" src=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_59.png\" alt=\"\" width=\"748\" height=\"258\" srcset=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_59.png 748w, https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_59-300x103.png 300w\" sizes=\"auto, (max-width: 748px) 100vw, 748px\"><\/a><\/p>\n<pre class=\"lang:vbnet decode:true\">Dim wb As New XLWorkbook\nDim ws = wb.Worksheets.Add(\"\uff11\u3064\u306e\u5217\u3092\u30bd\u30fc\u30c8\")\n\n'\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u3092\u7528\u610f\nCall AddTestColumn(ws)\n\n'\u7528\u610f\u3057\u305f\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u306e\u53f3\u5074\u306b\u30c7\u30fc\u30bf\u3092\u30b3\u30d4\u30fc\nDim rangeSimpleColumn = ws.RangeUsed\nDim copySimpleColumn = rangeSimpleColumn.CopyTo(ws.Column(ws.LastColumnUsed.ColumnNumber + 2))\n\n\n\n'\u30b3\u30d4\u30fc\u3057\u305f\u30c7\u30fc\u30bf\u3092\u30bd\u30fc\u30c8\ncopySimpleColumn.Sort(1, XLSortOrder.Descending, True)\n\n\n\n'\u300c\u2192\u300d\u3092\u633f\u5165\nCall InsertArrow(ws.Range(\"B1:B8\"))\n\n'\u8aac\u660e\u6587\u3092\u633f\u5165\nws.Row(1).InsertRowsAbove(2)\nws.Cell(1, 1).SetValue(\".Sort(1, XLSortOrder.Descending, True) = \u7bc4\u56f2\u3092\u4e0a\u304b\u3089\u4e0b\u3078\u964d\u9806\u30bd\u30fc\u30c8\u3001\u82f1\u5b57\u5927\u5c0f\u3092\u533a\u5225\u3001\u7a7a\u767d\u3092\u7121\u8996\").Style.Font.SetBold\n'A1 \u304c\u9078\u629e\u3055\u308c\u3066\u308b\u3068\u8aac\u660e\u6587\u304c\u8aad\u307f\u306b\u304f\u3044\u306e\u3067\u5225\u306e\u3068\u3053\u308d\u3092\u9078\u629e\nws.LastCellUsed.CellRight.CellBelow.Select\n\nwb.SaveAs(\"D:\\test\\SortSingleColumn.xlsx\")<\/pre>\n<h2 id=\"i-3\"><span class=\"ez-toc-section\" id=\"%E8%A4%87%E9%9B%91%E3%81%AA%E3%82%BD%E3%83%BC%E3%83%88%EF%BC%88%EF%BC%91%EF%BC%89\"><\/span>\u8907\u96d1\u306a\u30bd\u30fc\u30c8\uff08\uff11\uff09<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><a href=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_60.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-12126 bigshadow\" src=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_60.png\" alt=\"\" width=\"744\" height=\"265\" srcset=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_60.png 744w, https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_60-300x107.png 300w\" sizes=\"auto, (max-width: 744px) 100vw, 744px\"><\/a><\/p>\n<pre class=\"lang:vbnet decode:true\">Dim wb = New XLWorkbook\nDim ws = wb.Worksheets.Add(\"\u8907\u96d1\u306a\u30bd\u30fc\u30c8\uff08\uff11\uff09\")\n\n'\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u3092\u7528\u610f\nCall AddTestTable(ws)\n\n'\u7528\u610f\u3057\u305f\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u306e\u53f3\u5074\u306b\u30c7\u30fc\u30bf\u3092\u30b3\u30d4\u30fc\nDim rangeComplex1 = ws.RangeUsed\nDim copyComplex1 = rangeComplex1.CopyTo(ws.Column(ws.LastColumnUsed.ColumnNumber + 2))\n\n\n\n'\u30b3\u30d4\u30fc\u3057\u305f\u30c7\u30fc\u30bf\u3092\u30bd\u30fc\u30c8\ncopyComplex1.Sort(columnsToSortBy:=\"2, 1 DESC\", matchCase:=True)\n'\u5143\u8a18\u4e8b\u901a\u308a copyComplex1.Sort(\"2, 1 DESC\", True) \u3068\u3059\u308b\u3068\n'\u30aa\u30fc\u30d0\u30fc\u30ed\u30fc\u30c9\u306e\u89e3\u6c7a\u306b\u5931\u6557\u3057\u30a8\u30e9\u30fc\u3068\u306a\u308b\u3002\u540d\u524d\u4ed8\u304d\u5f15\u6570\u3092\u4f7f\u3048\u3070\u554f\u984c\u306a\u3044\u3002\n'by \u30a8\u30ec\u30f3\u30fb\u30a4\u30fc\u30b9\u30c8\uff08\u5f53\u30b5\u30a4\u30c8\u306e\u7ba1\u7406\u4eba\uff09\n\n\n\n'\u300c\u2192\u300d\u3092\u633f\u5165\nCall InsertArrow(ws.Range(\"D1:D8\"))\n\n'\u8aac\u660e\u6587\u3092\u633f\u5165\nws.Row(1).InsertRowsAbove(2)\nws.Cell(1, 1).SetValue(\".Sort = \u7bc4\u56f2\u3092\u4e0a\u304b\u3089\u4e0b\u3078\u5217\uff12\u3092\u6607\u9806\u30bd\u30fc\u30c8\u3001\u5217\uff11\u3092\u964d\u9806\u30bd\u30fc\u30c8\u3001\u82f1\u5b57\u5927\u5c0f\u3092\u533a\u5225\u3001\u7a7a\u767d\u3092\u7121\u8996\").Style.Font.SetBold\n'A1 \u304c\u9078\u629e\u3055\u308c\u3066\u308b\u3068\u8aac\u660e\u6587\u304c\u8aad\u307f\u306b\u304f\u3044\u306e\u3067\u5225\u306e\u3068\u3053\u308d\u3092\u9078\u629e\nws.LastCellUsed.CellRight.CellBelow.Select \n\nwb.SaveAs(\"D:\\test\\SortComplex1.xlsx\")<\/pre>\n<h2 id=\"i-4\"><span class=\"ez-toc-section\" id=\"%E8%A4%87%E9%9B%91%E3%81%AA%E3%82%BD%E3%83%BC%E3%83%88%EF%BC%88%EF%BC%92%EF%BC%89\"><\/span>\u8907\u96d1\u306a\u30bd\u30fc\u30c8\uff08\uff12\uff09<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><a href=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_61.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-12128 bigshadow\" src=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_61.png\" alt=\"\" width=\"832\" height=\"304\" srcset=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_61.png 832w, https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_61-300x110.png 300w, https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_61-768x281.png 768w\" sizes=\"auto, (max-width: 832px) 100vw, 832px\"><\/a><\/p>\n<pre class=\"lang:vbnet decode:true \">Dim wb = New XLWorkbook\nDim ws = wb.Worksheets.Add(\"\u8907\u96d1\u306a\u30bd\u30fc\u30c8\uff08\uff12\uff09\")\n\n'\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u3092\u7528\u610f\nCall AddTestTable(ws)\n\n'\u7528\u610f\u3057\u305f\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u306e\u53f3\u5074\u306b\u30c7\u30fc\u30bf\u3092\u30b3\u30d4\u30fc\nDim rangeComplex2 = ws.RangeUsed\nDim copyComplex2 = rangeComplex2.CopyTo(ws.Column(ws.LastColumnUsed.ColumnNumber + 2))\n\n\n\n'\u30b3\u30d4\u30fc\u3057\u305f\u30c7\u30fc\u30bf\u3092\u30bd\u30fc\u30c8\ncopyComplex2.SortColumns.Add(1, XLSortOrder.Ascending, False, True)\ncopyComplex2.SortColumns.Add(3, XLSortOrder.Descending)\ncopyComplex2.Sort\n'.Sort \u30e1\u30bd\u30c3\u30c9\u3068 .SortColumns \u30e1\u30bd\u30c3\u30c9\u3067\u306f matchCase, ignoreBlanks \u30aa\u30d7\u30b7\u30e7\u30f3\u306e\u9806\u756a\u304c\u7570\u306a\u308b\u306e\u3067\u6ce8\u610f\u3002\n'\u7d1b\u3089\u308f\u3057\u3044\u3068\u304d\u306f\u540d\u524d\u4ed8\u304d\u5f15\u6570\uff08matchCase:=True \u306a\u3069\uff09\u3092\u4f7f\u3046\u3068\u826f\u3044\u304b\u3082\u3002\n'by \u30a8\u30ec\u30f3\u30fb\u30a4\u30fc\u30b9\u30c8\uff08\u5f53\u30b5\u30a4\u30c8\u306e\u7ba1\u7406\u4eba\uff09\n\n\n\n'\u300c\u2192\u300d\u3092\u633f\u5165\nCall InsertArrow(ws.Range(\"D1:D8\"))\n\n'\u8aac\u660e\u6587\u3092\u633f\u5165\nws.Row(1).InsertRowsAbove(4)\nws.Cell(1, 1).SetValue(\".SortColumns.Add(1, XLSortOrder.Ascending, False, True) = \u5217\uff11\u3092\u6607\u9806\u30bd\u30fc\u30c8\u3001\u7a7a\u767d\u3092\u7121\u8996\u3057\u306a\u3044\u3001\u82f1\u5b57\u5927\u5c0f\u3092\u533a\u5225\").Style.Font.SetBold\nws.Cell(2, 1).SetValue(\".SortColumns.Add(3, XLSortOrder.Descending) = \u5217\uff13\u3092\u964d\u9806\u30bd\u30fc\u30c8\u3001\u7a7a\u767d\u3092\u7121\u8996\u3001\u82f1\u5b57\u5927\u5c0f\u3092\u7121\u8996\").Style.Font.SetBold\nws.Cell(3, 1).SetValue(\".Sort = SortColumns \u306b\u8a2d\u5b9a\u3055\u308c\u305f\u30d1\u30e9\u30e1\u30fc\u30bf\u3092\u4f7f\u7528\u3057\u3066\u30bd\u30fc\u30c8\").Style.Font.SetBold\n'A1 \u304c\u9078\u629e\u3055\u308c\u3066\u308b\u3068\u8aac\u660e\u6587\u304c\u8aad\u307f\u306b\u304f\u3044\u306e\u3067\u5225\u306e\u3068\u3053\u308d\u3092\u9078\u629e\nws.LastCellUsed.CellRight.CellBelow.Select \n\nwb.SaveAs(\"D:\\test\\SortComplex2.xlsx\")<\/pre>\n<h2 id=\"i-5\"><span class=\"ez-toc-section\" id=\"%E5%B7%A6%E3%81%8B%E3%82%89%E5%8F%B3%E3%81%AB%E3%82%BD%E3%83%BC%E3%83%88\"><\/span>\u5de6\u304b\u3089\u53f3\u306b\u30bd\u30fc\u30c8<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><a href=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_62.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-12127 bigshadow\" src=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_62.png\" alt=\"\" width=\"622\" height=\"260\" srcset=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_62.png 622w, https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_62-300x125.png 300w\" sizes=\"auto, (max-width: 622px) 100vw, 622px\"><\/a><\/p>\n<pre class=\"lang:vbnet decode:true\">Dim wb = New XLWorkbook\nDim ws = wb.Worksheets.Add(\"\u5de6\u304b\u3089\u53f3\u306b\u30bd\u30fc\u30c8\")\n\n'\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u3092\u7528\u610f\nCall AddTestTable(ws)\n\n'\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u3092\u56de\u8ee2\nws.RangeUsed().Transpose(XLTransposeOptions.MoveCells)\n\n'\u7528\u610f\u3057\u305f\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u306e\u4e0b\u306b\u30c7\u30fc\u30bf\u3092\u30b3\u30d4\u30fc\nDim rangeLeftToRight = ws.RangeUsed\nDim copyLeftToRight = rangeLeftToRight.CopyTo(ws.Row(ws.LastRowUsed.RowNumber + 2))\n\n\n\n'\u30b3\u30d4\u30fc\u3057\u305f\u30c7\u30fc\u30bf\u3092\u30bd\u30fc\u30c8\ncopyLeftToRight.SortLeftToRight\n\n\n\n'\u300c\u2193\u300d\u3092\u633f\u5165\nCall InsertArrow(ws.Range(\"D4:D4\"), \"\u2193\")\n\n'\u8aac\u660e\u6587\u3092\u633f\u5165\nws.Row(1).InsertRowsAbove(2)\nws.Cell(1, 1).SetValue(\".SortLeftToRight = \u7bc4\u56f2\u3092\u5de6\u304b\u3089\u53f3\u306b\u30bd\u30fc\u30c8\u3001\u6607\u9806\u3001\u7a7a\u767d\u3092\u7121\u8996\u3001\u82f1\u5b57\u5927\u5c0f\u3092\u7121\u8996\").Style.Font.SetBold\n'A1 \u304c\u9078\u629e\u3055\u308c\u3066\u308b\u3068\u8aac\u660e\u6587\u304c\u8aad\u307f\u306b\u304f\u3044\u306e\u3067\u5225\u306e\u3068\u3053\u308d\u3092\u9078\u629e\nws.LastCellUsed.CellRight.CellBelow.Select \n\nwb.SaveAs(\"D:\\test\\SortLeftToRight.xlsx\")<\/pre>\n<h2 id=\"i-6\"><span class=\"ez-toc-section\" id=\"%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE%E3%82%BD%E3%83%BC%E3%83%88\"><\/span>\u30c6\u30fc\u30d6\u30eb\u306e\u30bd\u30fc\u30c8<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><a href=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_63.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-12125 bigshadow\" src=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_63.png\" alt=\"\" width=\"940\" height=\"280\" srcset=\"https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_63.png 940w, https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_63-300x89.png 300w, https:\/\/elleneast.com\/wp\/wp-content\/uploads\/programming_topics\/ClosedXML\/ClosedXml_63-768x229.png 768w\" sizes=\"auto, (max-width: 940px) 100vw, 940px\"><\/a><\/p>\n<pre class=\"lang:vbnet decode:true  \">Dim wb = New XLWorkbook\nDim ws = wb.Worksheets.Add(\"\u30c6\u30fc\u30d6\u30eb\u306e\u30bd\u30fc\u30c8\")\n\n'\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u3092\u7528\u610f\nCall AddTestTable(ws)\n\n'\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u306e\u4e0a\u306b\u5217\u30bf\u30a4\u30c8\u30eb\u3092\u633f\u5165\nDim header = ws.Row(1).InsertRowsAbove(1).First\nFor co As Int32 = 1 To ws.LastColumnUsed.ColumnNumber\n    header.Cell(co).Value = \"\u5217\" &amp; co.ToString\nNext\n\n'\u7528\u610f\u3057\u305f\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u306e\u53f3\u5074\u306b\u30c7\u30fc\u30bf\u3092\u30b3\u30d4\u30fc\nDim rangeTable = ws.RangeUsed\nDim table = rangeTable.CopyTo(ws.Column(ws.LastColumnUsed.ColumnNumber + 2)) _\n    .CreateTable '\u305d\u3057\u3066\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\n\n\n\n'\u30c6\u30fc\u30d6\u30eb\u3092\u30bd\u30fc\u30c8\ntable.Sort(\"\u52172, \u52173 Desc, \u52171 ASC\")\n\n\n\n'\u300c\u2192\u300d\u3092\u633f\u5165\nCall InsertArrow(ws.Range(\"D2:D9\"))\n\n'\u8aac\u660e\u6587\u3092\u633f\u5165\nws.Row(1).InsertRowsAbove(2)\nws.Cell(1, 1).SetValue(\".Sort(\"\"\u52172, \u52173 Desc, \u52171 ASC\"\") = \u30c6\u30fc\u30d6\u30eb\u3092\u4e0a\u304b\u3089\u4e0b\u3078\u30bd\u30fc\u30c8\u3001\u52172 \u6607\u9806\u3001\u52173 \u964d\u9806\u3001\u52171 \u6607\u9806\u3001\u7a7a\u767d\u3092\u7121\u8996\u3001\u82f1\u5b57\u5927\u5c0f\u3092\u7121\u8996\").Style.Font.SetBold\n'A1 \u304c\u9078\u629e\u3055\u308c\u3066\u308b\u3068\u8aac\u660e\u6587\u304c\u8aad\u307f\u306b\u304f\u3044\u306e\u3067\u5225\u306e\u3068\u3053\u308d\u3092\u9078\u629e\nws.LastCellUsed.CellRight.CellBelow.Select \n\nwb.SaveAs(\"D:\\test\\SortTable.xlsx\")<\/pre>\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] \u4e8b\u524d\u6e96\u5099 \u307e\u305a\u3001\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u633f\u5165\u7528\u306e\u95a2\u6570\u306a\u3069\u3092\u7528\u610f\u3057\u307e\u3059\u3002 &#8216;\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u633f\u5165\u7528 Private Sub AddTestColumn(ws As IXLWorksh &#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-12112","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\/12112","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=12112"}],"version-history":[{"count":3,"href":"https:\/\/elleneast.com\/index.php?rest_route=\/wp\/v2\/posts\/12112\/revisions"}],"predecessor-version":[{"id":12140,"href":"https:\/\/elleneast.com\/index.php?rest_route=\/wp\/v2\/posts\/12112\/revisions\/12140"}],"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=12112"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/elleneast.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=12112"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/elleneast.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=12112"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}