using System; using System.Data.OleDb; using SmartXLS; using SmartXLS.data; public class PivotExDSSample { private static SXQuery Query { get { try { string strAccessConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\SmartXLS\\000000\\2011\\Northwind.mdb"; OleDbConnection myAccessConn = new OleDbConnection(strAccessConn); myAccessConn.Open(); System.Data.OleDb.OleDbConnection conn = myAccessConn; System.Data.OleDb.OleDbCommand st = conn.CreateCommand(); st.CommandText = "SELECT * FROM Orders"; System.Data.OleDb.OleDbDataReader rs = st.ExecuteReader(); System.Data.DataTable rsMetaData = rs.GetSchemaTable(); return new SXQuery(st, rs); } catch (System.Exception e) { Console.WriteLine(e.StackTrace); } return null; } } [STAThread] public static void Main() { WorkBook workBook = new WorkBook(); try { workBook.Sheet = 0; BookPivotRangeModel model = workBook.getPivotModel(); model.setDataQuery(Query); workBook.setSelection(7,5,7,5); BookPivotRange pivotRange = model.getActivePivotRange(); pivotRange.setDataOnRow(false); model.refreshRange(pivotRange); 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) { Console.WriteLine(e); } } }