title: Pivot Tables- Set The Table in a Tabular Form using Apache POI
body: <p>To whom it may concern,</p>
  <p>I am trying to develop a Pivot Table in Excel using Apache POI and Java. However, I want it in a &quot;Tabular Form&quot; with dropdown menus for each of the rows. To be more specific, I want the top row of the pivot table to be shown like this (apologies for part of the top row being cut off):
  <a href="https://i.stack.imgur.com/Szt4r.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Szt4r.png" alt="enter image description here" /></a></p>
  <p>Whereas in reality, the top row turns out to be this:
  <a href="https://i.stack.imgur.com/Yl26y.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Yl26y.png" alt="enter image description here" /></a></p>
  <p>The code which I have done to try and generate the pivot table is as follows:</p>
  <pre><code>    int count = 1;
          for (int j = 0; j &lt; s2.getPhysicalNumberOfRows()-1; j++)
          {
              try {
              Row r = s1.getRow(j+1);
              Row r2 = s3.createRow(count);
              //System.out.println(r.getCell(27).getStringCellValue());
              if (r.getCell(8).getStringCellValue().equals(r.getCell(11).getStringCellValue()) &amp;&amp; 
              (!r.getCell(8).getStringCellValue().equals(&quot;N/A&quot;)))
              {
                  for (int b = 0; b &lt; 30; b++)
                  {
                  r2.createCell(b).setCellValue(r.getCell(b).getStringCellValue());
                  System.out.println(j);
                  
                  }
                  count++;
              }
              }
              catch (NullPointerException e)
              {
                  e.printStackTrace();
              }
          }
          
          AreaReference a = new AreaReference(&quot;A1:AD344&quot;,SpreadsheetVersion.EXCEL2007);
          CellReference c = new CellReference(&quot;AF6&quot;);
          XSSFPivotTable iRxBest = s3.createPivotTable(a,c);
          iRxBest.getCTPivotTableDefinition().setRowHeaderCaption(&quot;Drug Name&quot;);
          iRxBest.addReportFilter(27);
          iRxBest.addReportFilter(10);
            iRxBest.getCTPivotTableDefinition().setUpdatedVersion((short)6); 
            for (int q = 0; q &lt; 30; q++)
            {
                
            if(q != 1 &amp;&amp; q != 7 &amp;&amp; q != 9 &amp;&amp; q != 10 &amp;&amp;  (q &lt; 12 || q &gt; 22) &amp;&amp; (q &lt; 25 || q &gt; 28))
                    {
                
                    
            TreeSet &lt;String&gt;uniqueItems = new java.util.TreeSet&lt;String&gt;();
             for (int r = a.getFirstCell().getRow()+1; r &lt; a.getLastCell().getRow()+1; r++) {
                  uniqueItems.add(s3.getRow(r).getCell(q).getStringCellValue());
                 }
                 System.out.println(&quot;SIZE IZ: &quot;  + uniqueItems.size());
                  iRxBest.addRowLabel(q);
                  
         CTPivotField pField1 =iRxBest.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(q);
         // pField1.setAutoShow(false);
          pField1.setDefaultSubtotal(false);
          pField1.setOutline(false);
  //        pField1.setSubtotalTop(false);
  //        pField1.setSubtotalCaption(&quot;&quot;);
      
  //     pField1.setOutline(false);
  //      pField1.setSubtotalTop(false);
  //        pField1.setSubtotalCaption(&quot;&quot;);
          
          for (int r = 0; r &lt; uniqueItems.size(); r++)
          {
               pField1.getItems().getItemArray(r).unsetT();
               pField1.getItems().getItemArray(r).setX((long)r);
          }
          for (int r = 343; r &gt; uniqueItems.size()-1; r--)
          {
              pField1.getItems().removeItem(r);
          }
          pField1.getItems().setCount(uniqueItems.size());
          for (String xyz: uniqueItems)
          {
              iRxBest.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(q).getSharedItems().addNewS().setV(xyz);
          }
  
           CTExtensionList extList = pField1.addNewExtLst();
             CTExtension ext = extList.addNewExt();
             String extXML = 
                &quot;&lt;x14:pivotField&quot;
              + &quot; xmlns:x14=\&quot;http://schemas.microsoft.com/office/spreadsheetml/2009/9/main\&quot;&quot;
              + &quot; fillDownLabels=\&quot;1\&quot;/&gt;&quot;;
             org.apache.xmlbeans.XmlObject xlmObject = org.apache.xmlbeans.XmlObject.Factory.parse(extXML);
             ext.set(xlmObject);
             ext.setUri(&quot;{2946ED86-A175-432a-8AC1-64E0C546D7DE}&quot;);  
            
            }
              //iRxBest.addRowLabel(13);
            }
              count = 1;
  </code></pre>
  <p>No effective solution has been found based on how to put the pivot table in this kind of form using Java. If you know of any way as to how to do this, please let me know.</p>