import com.smartxls.*;
import com.smartxls.data.SXQuery;

import java.sql.*;

public class PivotExDSSample
{

    private static Connection getConnection()
            throws Exception
    {
        String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
        String url = "jdbc:odbc:Northwind";
        String username = "";
        String password = "";
        Class.forName(driver);
        return DriverManager.getConnection(url, username, password);
    }

    private static SXQuery getQuery()
    {
        try
        {
            Connection conn = getConnection();
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM Orders");
            ResultSetMetaData rsMetaData = rs.getMetaData();
            return new SXQuery(st, rs);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return null;
    }


    public static void main(String args[])
    {
        WorkBook workBook = new WorkBook();
        try
        {
            workBook.setSheet(0);
            
            BookPivotRange pivotRange = workBook.addPivotRange(getQuery(), 0, 7, 5);
            pivotRange.setDataOnRow(false);
            pivotRange.refreshRange();

            RangeArea rangeArea = pivotRange.getRangeArea();

            BookPivotArea rowArea = pivotRange.getArea(BookPivotRange.row);
            BookPivotArea columnArea = pivotRange.getArea(BookPivotRange.column);
            BookPivotArea dataArea = pivotRange.getArea(BookPivotRange.data);
            BookPivotArea pageArea = pivotRange.getArea(BookPivotRange.page);

            BookPivotField rowField = pivotRange.getField("OrderID");
            rowArea.addField(rowField);
            rowField = pivotRange.getField("CustomerID");
            rowArea.addField(rowField);

            BookPivotField columnField = pivotRange.getField("OrderDate");
            columnArea.addField(columnField);

            BookPivotField dataField = pivotRange.getField("Freight");
            dataArea.addField(dataField);

            //BookPivotField pageField = pivotRange.getField("ShipRegion");
            //pageArea.addField(pageField);

            //workBook.write(".\\PivotTableDS.xls");
            workBook.writeXLSX(".\\PivotTableDS.xlsx");
        }
        catch (Exception e)
        {
        }
    }
}