Integrating Pivot Table with .NET

FlexMonster Pivot Table Component can be integrated with .NET.

To see how to do this please look through this simple C# guide integration sample.

To be the Pivot Table Component integrated into your page please follow these steps:

1. You have to copy all Pivot component folder content with all supported files in there. Paste them into the root of your site or to a sandbox folder.
2. Next insert the Pivot component into .aspx file and past this code below:
   

   <div id="pivotContainer">The component will appear within this DIV.</div>
   <script type="text/javascript" src="flexmonster/flexmonster.js"></script>
   <script type="text/javascript">
       flexmonster.embedPivotComponent("flexmonster/", "pivotContainer","100%", "500");
   </script>

3. To select a valid path to your data file you need to add an attribute to a last embedPivotComponent function parameter:

   <div id="pivotContainer">The component will appear within this DIV.</div>
   <script type="text/javascript" src="flexmonster/flexmonster.js"></script>
   <script type="text/javascript">
       var params = {filename : "your_data_script.aspx"};
       flexmonster.embedPivotComponent("flexmonster/", "pivotContainer","100%", "500", params);
   </script>

How to Write a Data Source Script

With all previous steps finished completely we are ready to write own data source script. Please remember that the most suitable format for Pivot Table Component is CSV. Now we will write a simple CSV generator or you can use an external library to create it.

There are two methods what our script can use:

• First one is to work 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"];
  }

• The second one is to convert data to CSV format:
  
  • to get data from the database using 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();
    }
  }