{"id":440,"date":"2025-10-25T11:45:34","date_gmt":"2025-10-25T09:45:34","guid":{"rendered":"https:\/\/www.silverice.it\/IpercubeTechWP1\/?page_id=440"},"modified":"2025-10-25T11:45:34","modified_gmt":"2025-10-25T09:45:34","slug":"xlwritedatatable2sheet","status":"publish","type":"page","link":"https:\/\/www.silverice.it\/IpercubeTechWP1\/ipercube-utilities\/excel-functions\/xlwritedatatable2sheet\/","title":{"rendered":"Data Import\/Export &#8211; Write Datatable to Sheet"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Writes the contents of a <em>DataTable<\/em> to the specified Excel worksheet starting from a given cell.<\/p>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-8f761849 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:75%\">\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-8f761849 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column page-snippet is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:100%\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-Geist-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-Geist-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">VB<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>    Public Sub xlWriteDatatable2Sheet(dt As Data.DataTable, \n                                      sheet As Worksheet, \n                                      Optional upperLeftCell As String = \"A1\")\n\n        If dt Is Nothing OrElse dt.Columns.Count = 0 Then Exit Sub\n\n        Dim startRow As Integer = sheet.Range(upperLeftCell).Row\n        Dim startCol As Integer = sheet.Range(upperLeftCell).Column\n        Dim currentRow As Integer = startRow\n\n        With sheet\n            ' Write headers\n            For c As Integer = 0 To dt.Columns.Count - 1\n                .Cells(currentRow, startCol + c).Value = dt.Columns(c).ColumnName\n            Next\n\n            ' Write data rows\n            For r As Integer = 0 To dt.Rows.Count - 1\n                currentRow += 1\n                For c As Integer = 0 To dt.Columns.Count - 1\n                    Try\n                        Dim value = dt.Rows(r)(c)\n                        .Cells(currentRow, startCol + c).Value = If(IsDBNull(value), String.Empty, value)\n                        '.Cells(currentRow, startCol + c).Value = If(IsDBNull(value), String.Empty, Convert.ChangeType(value, dc.DataType))\n                    Catch\n                        .Cells(currentRow, startCol + c).Value = String.Empty\n                    End Try\n                Next\n            Next\n        End With\n\n    End Sub<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">Public Sub <\/span><span style=\"color: #DCDCAA\">xlWriteDatatable2Sheet<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">dt<\/span><span style=\"color: #D4D4D4\"> As <\/span><span style=\"color: #4EC9B0\">Data<\/span><span style=\"color: #D4D4D4\">.DataTable, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                      sheet As <\/span><span style=\"color: #4EC9B0\">Worksheet<\/span><span style=\"color: #D4D4D4\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                      Optional upperLeftCell As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #CE9178\">&quot;A1&quot;<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\"> dt Is<\/span><span style=\"color: #569CD6\"> Nothing<\/span><span style=\"color: #D4D4D4\"> OrElse dt.Columns.<\/span><span style=\"color: #9CDCFE\">Count<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">Then<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">Exit Sub<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> startRow <\/span><span style=\"color: #D4D4D4\">As <\/span><span style=\"color: #4EC9B0\">Integer<\/span><span style=\"color: #D4D4D4\"> = sheet.<\/span><span style=\"color: #DCDCAA\">Range<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">upperLeftCell<\/span><span style=\"color: #D4D4D4\">).Row<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> startCol <\/span><span style=\"color: #D4D4D4\">As <\/span><span style=\"color: #4EC9B0\">Integer<\/span><span style=\"color: #D4D4D4\"> = sheet.<\/span><span style=\"color: #DCDCAA\">Range<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">upperLeftCell<\/span><span style=\"color: #D4D4D4\">).Column<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> currentRow <\/span><span style=\"color: #D4D4D4\">As <\/span><span style=\"color: #4EC9B0\">Integer<\/span><span style=\"color: #D4D4D4\"> =<\/span><span style=\"color: #9CDCFE\"> startRow<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #C586C0\">With<\/span><span style=\"color: #D4D4D4\"> sheet<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #6A9955\">&#39; Write headers<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">For<\/span><span style=\"color: #D4D4D4\"> c As <\/span><span style=\"color: #4EC9B0\">Integer<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">To<\/span><span style=\"color: #D4D4D4\"> dt.Columns.<\/span><span style=\"color: #9CDCFE\">Count<\/span><span style=\"color: #D4D4D4\"> - <\/span><span style=\"color: #B5CEA8\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                .<\/span><span style=\"color: #DCDCAA\">Cells<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">currentRow<\/span><span style=\"color: #D4D4D4\">,<\/span><span style=\"color: #9CDCFE\"> startCol<\/span><span style=\"color: #D4D4D4\"> +<\/span><span style=\"color: #9CDCFE\"> c<\/span><span style=\"color: #D4D4D4\">).<\/span><span style=\"color: #9CDCFE\">Value<\/span><span style=\"color: #D4D4D4\"> = dt.<\/span><span style=\"color: #DCDCAA\">Columns<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">c<\/span><span style=\"color: #D4D4D4\">).ColumnName<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">Next<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #6A9955\">&#39; Write data rows<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">For<\/span><span style=\"color: #D4D4D4\"> r As <\/span><span style=\"color: #4EC9B0\">Integer<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">To<\/span><span style=\"color: #D4D4D4\"> dt.Rows.<\/span><span style=\"color: #9CDCFE\">Count<\/span><span style=\"color: #D4D4D4\"> - <\/span><span style=\"color: #B5CEA8\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                <\/span><span style=\"color: #9CDCFE\">currentRow<\/span><span style=\"color: #D4D4D4\"> += <\/span><span style=\"color: #B5CEA8\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                <\/span><span style=\"color: #C586C0\">For<\/span><span style=\"color: #D4D4D4\"> c As <\/span><span style=\"color: #4EC9B0\">Integer<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">To<\/span><span style=\"color: #D4D4D4\"> dt.Columns.<\/span><span style=\"color: #9CDCFE\">Count<\/span><span style=\"color: #D4D4D4\"> - <\/span><span style=\"color: #B5CEA8\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                    Try<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                        <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> value <\/span><span style=\"color: #D4D4D4\">= dt.<\/span><span style=\"color: #DCDCAA\">Rows<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">r<\/span><span style=\"color: #D4D4D4\">)(<\/span><span style=\"color: #9CDCFE\">c<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                        .<\/span><span style=\"color: #DCDCAA\">Cells<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">currentRow<\/span><span style=\"color: #D4D4D4\">,<\/span><span style=\"color: #9CDCFE\"> startCol<\/span><span style=\"color: #D4D4D4\"> +<\/span><span style=\"color: #9CDCFE\"> c<\/span><span style=\"color: #D4D4D4\">).<\/span><span style=\"color: #9CDCFE\">Value<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #DCDCAA\">IsDBNull<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">value<\/span><span style=\"color: #D4D4D4\">), <\/span><span style=\"color: #DCDCAA\">String<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #569CD6\">Empty<\/span><span style=\"color: #D4D4D4\">,<\/span><span style=\"color: #9CDCFE\"> value<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                        <\/span><span style=\"color: #6A9955\">&#39;.Cells(currentRow, startCol + c).Value = If(IsDBNull(value), String.Empty, Convert.ChangeType(value, dc.DataType))<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                    Catch<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                        .<\/span><span style=\"color: #DCDCAA\">Cells<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">currentRow<\/span><span style=\"color: #D4D4D4\">,<\/span><span style=\"color: #9CDCFE\"> startCol<\/span><span style=\"color: #D4D4D4\"> +<\/span><span style=\"color: #9CDCFE\"> c<\/span><span style=\"color: #D4D4D4\">).<\/span><span style=\"color: #9CDCFE\">Value<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #DCDCAA\">String<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #569CD6\">Empty<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                    <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> Try<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                <\/span><span style=\"color: #C586C0\">Next<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">Next<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">With<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">End Sub<\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Writes the contents of a DataTable to the specified Excel worksheet starting from a given cell.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":349,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-440","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/440","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/comments?post=440"}],"version-history":[{"count":2,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/440\/revisions"}],"predecessor-version":[{"id":442,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/440\/revisions\/442"}],"up":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/349"}],"wp:attachment":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/media?parent=440"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}