Integrating Pivot Table with .NET

FlexMonster Pivot Table Component can be integrated with .NET

We will guide you through simple C# integration sample.

To integrate Pivot Table into your page you should accomplish the following steps:

1. Copy content of Pivot folder (Pivot component and supported files) to root of your site or sandbox folder.
2. Insert Pivot component into your .aspx file – paste code below:
   

   <script type="text/javascript" src="swfobject.js"></script>
   <div id="flashContent">No Flash player or Javascript disabeled message</div>
   <script type="text/javascript">
     var so = new SWFObject("PivotTable.swf", "flash", "1000", "720", "9", "#FFFFFF");
       so.addParam("allowScriptAccess", "sameDomain");
       so.addParam("menu", "false");
       so.addParam("allowFullScreen", "true");
       so.addVariable("filename", "your_data_script.aspx");
       so.addVariable("styleSheetName", "original.css");
       so.addVariable("configuratorEnabled", "true");
       so.addVariable("chartsEnabled", "true");
       so.write("flashContent");
       flash.focus();
   </script>

3. Set path to your data file. To do this change value of filename FlashVar.

   so.addVariable("filename", "your_data_script.aspx");

4. Now we are ready to write data source script.

Wrinting data source script

First of all you we need to inform you that best data format for Pivot Table Component is CSV. So now we will write simple CSV generator. You can use external library or create your own. No matter.

Our script has two methods:

• First method works with database:
  
  • connect to database
  • retreive dataset
  • close connection
  • return data

  protected DataTable GetData() {
    if (DataTable != null) return DataTable;
    if (DBConnectionString == null || SQLQuery == null) throw new Exception("Please provide either DataRows, or both DBConnectionString and SQLQuery to get data");
  
    SqlConnection conn = new SqlConnection(DBConnectionString);
    SqlDataAdapter da = new SqlDataAdapter(SQLQuery, conn);
    DataSet ds = new DataSet();
    da.Fill(ds, "table0");
    conn.Close();
    return ds.Tables["table0"];
  }

• Second method convert data to CSV format:
  
  • get data from database via GetData() method
  • parse columns and write names to csv
  • parse rows and write values to csv
  • return csv

  public string GenerateCSVToString() {
    DataTable data = GetData();
    MemoryStream stream = new MemoryStream();
    CsvWriter writer = new CsvWriter(stream);
     ArrayList columns = new ArrayList();
    foreach (DataColumn col in data.Columns) {
      columns.Add(col.ColumnName);
    }
    object[] colsObject = new object[columns.Count];
    columns.CopyTo(colsObject);
  writer.WriteFields(colsObject);
  
    foreach (DataRow r in data.Rows) {
      int i = 0;
      foreach (DataColumn col in data.Columns) {
        colsObject[i] = r[col];
        i += 1;
      }
      writer.WriteFields(colsObject);
    }
  
    writer.Flush();
    stream.Position = 0;
    using (StreamReader reader = new StreamReader(stream)) {
    // Just read to the end.
    return reader.ReadToEnd();
    }
  }
  

You can download full source code of this sample.