{"id":197,"date":"2013-04-22T11:34:19","date_gmt":"2013-04-22T16:34:19","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=197"},"modified":"2013-04-22T11:34:19","modified_gmt":"2013-04-22T16:34:19","slug":"vb-net-create-excel-file-with-pivot-table-using-vb-net","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2013\/04\/22\/vb-net-create-excel-file-with-pivot-table-using-vb-net\/","title":{"rendered":"VB.NET &#8211; Create Excel file with Pivot Table using VB.Net"},"content":{"rendered":"<div id=\"post-body-3510255022782291191\">In this example I create Excel file with 2 sheets<br \/>\n&#8211; one with data in table format<br \/>\n&#8211; another with pivot tableThis code can be copied and pasted, don&#8217;t forget to add Excel reference -Of course, Excel must be installed on the computer that will run this code.<\/p>\n<pre class=\"\">Dim fileTest As String = \"C:\\Temp\\ExcelTest\\testPivot.xlsx\"\n If File.Exists(fileTest) Then\n File.Delete(fileTest) ' oh, file is still open\n End If\nDim oExcel As Object\n oExcel = CreateObject(\"Excel.Application\")\n Dim oBook As Excel.Workbook\n Dim oSheet As Excel.Worksheet\noBook = oExcel.Workbooks.Add\n oSheet = oExcel.Worksheets(1)\noSheet.Name = \"Report\"\n oSheet.Range(\"A1\").Value = \"First Name\"\n oSheet.Range(\"B1\").Value = \"Year\"\n oSheet.Range(\"C1\").Value = \"Salary\"\noSheet.Range(\"A2\").Value = \"Frank\"\n oSheet.Range(\"B2\").Value = \"2012\"\n oSheet.Range(\"C2\").Value = \"30000\"\noSheet.Range(\"A3\").Value = \"Frank\"\n oSheet.Range(\"B3\").Value = \"2011\"\n oSheet.Range(\"C3\").Value = \"25000\"\noSheet.Range(\"A4\").Value = \"Ann\"\n oSheet.Range(\"B4\").Value = \"2011\"\n oSheet.Range(\"C4\").Value = \"55000\"\noSheet.Range(\"A5\").Value = \"Ann\"\n oSheet.Range(\"B5\").Value = \"2012\"\n oSheet.Range(\"C5\").Value = \"35000\"\noSheet.Range(\"A6\").Value = \"Ann\"\n oSheet.Range(\"B6\").Value = \"2010\"\n oSheet.Range(\"C6\").Value = \"35000\"\n' OK, at this point we have Excel file with 1 sheet with data\n ' Now let's create pivot table\n' first get range of cells from sheet 1 that will be used by pivot\n Dim xlRange As Excel.Range = CType(oSheet, Excel.Worksheet).Range(\"A1:C6\")\n' create second sheet\n If oExcel.Application.Sheets.Count() &lt; 2 Then\n oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)\n Else\n oSheet = oExcel.Worksheets(2)\n End If\n oSheet.Name = \"Pivot Table\"\n' specify first cell for pivot table on the second sheet\n Dim xlRange2 As Excel.Range = CType(oSheet, Excel.Worksheet).Range(\"B3\")\n' Create pivot cache and table\n Dim ptCache As Excel.PivotCache = oBook.PivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, xlRange)\n Dim ptTable As Excel.PivotTable = oSheet.PivotTables.Add(PivotCache:=ptCache, TableDestination:=xlRange2, TableName:=\"Summary\")\n' create Pivot Field, note that pivot field name is the same as column name in sheet 1\n Dim ptField As Excel.PivotField = ptTable.PivotFields(\"Salary\")\n With ptField\n .Orientation = Excel.XlPivotFieldOrientation.xlDataField\n .Function = Excel.XlConsolidationFunction.xlSum\n .Name = \" Salary\" ' by default name will be something like SumOfSalary, change it here to Salary, note space in front of it -\n ' this field name cannot be the same as therefore that space\n ' also it cannot be empty\n'' add another field\n 'ptField = ptTable.PivotFields(\"Year\")\n 'With ptField\n '\u00a0\u00a0\u00a0 .Orientation = Excel.XlPivotFieldOrientation.xlDataField\n '\u00a0\u00a0\u00a0 .Function = Excel.XlConsolidationFunction.xlMax\n '\u00a0\u00a0\u00a0 .Name = \" Year\" ' this is how you create another field, in my example I don't need it so let's comment it out\n 'End With\n' add column\n ptField = ptTable.PivotFields(\"First Name\")\n With ptField\n .Orientation = Excel.XlPivotFieldOrientation.xlColumnField\n .Name = \" \"\n End With\nEnd With\n ' add grouping - again I don't need this in my example, this is just to show how to do it\n 'oSheet.Range(\"C5\").Group(1, 20, 40)\noBook.SaveAs(fileTest)\n oBook.Close()\n oBook = Nothing\n oExcel.Quit()\n oExcel = Nothing<\/pre>\n<p>same example on Youtube &#8211; part 1<\/p>\n<p><iframe loading=\"lazy\" width=\"560\" height=\"315\" src=\"http:\/\/www.youtube.com\/embed\/RmvcyIxJNPM\" allowfullscreen=\"allowfullscreen\" frameborder=\"0\"><\/iframe><br \/>\nsame example on Youtube &#8211; part 2<br \/>\n<iframe loading=\"lazy\" width=\"560\" height=\"315\" src=\"http:\/\/www.youtube.com\/embed\/MszaJI8SQ6g\" allowfullscreen=\"allowfullscreen\" frameborder=\"0\"><\/iframe><\/p>\n<div><\/div>\n<\/div>\n<div>\n<div>Posted by\u00a0<a title=\"author profile\" href=\"http:\/\/www.blogger.com\/profile\/06906927517525220164\" rel=\"author\">HowToCSharpMsSqlExcelAccess\u00a0<\/a>at\u00a0<a title=\"permanent link\" href=\"http:\/\/howtodomssqlcsharpexcelaccess.blogspot.com\/2012\/08\/vbnet-create-excel-file-with-pivot.html\" rel=\"bookmark\"><abbr title=\"2012-08-30T11:03:00-07:00\">11:03<\/abbr><\/a><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>In this example I create Excel file with 2 sheets &#8211; one with data in table format &#8211; another with pivot tableThis code can be&hellip; <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[],"class_list":["post-197","post","type-post","status-publish","format-standard","hentry","category-vb-net"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/197","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/comments?post=197"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/197\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=197"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=197"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=197"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}