How to achieve Excel PivotTable in ASP.NET

**Sharanya** · 13-02-2010

Hi all,

I built a program that create PivotTable data provided by three-dimensional view of results, in Microsoft Excel to create a PivotTable, but it is not always very easy to use myExcel. I want to create a Web application, the data perspective report. Creating a simple pivot table can be a very complex task. Therefore, I need useful tool for creating simple and advanced pivot table. Please suggest. Thanks in advance.

**Modifier** · 13-02-2010

You can create a Pivot object, by passing a data table as a parameter. In the init () method is only an blank string value assigned to the CSS variables. If the CSS variable is an empty string, the constructor will use the default style. Each variable has a similar CSS properties.

Code:

private string FindValue (string xAxisField123, string xAxisValue123, string yAxisField123, string yAxisValue123, string zAxisField123)
{
string zAxisValue123 = "";
try
    {
foreach (DataRow123 row in _DataTable.Rows)
        {
if (Convert.ToString (row [xAxisField]) == xAxisValue & & Convert.ToString (row [yAxisField]) == yAxisValue)
            {
zAxisValue123 = Convert.ToString (row [zAxisField]);
break;
            }
        }
    }
catch
    {
throw;
    }
return zAxisValue123;
}

**MindSpace** · 13-02-2010

You need to paste below code.

Code:

private void MainHeaderTopCellStyle123 (HtmlTableCell cell)
{
if (_CssTopHeading123 == "")
    {
cell.Style.Add ( "font-family", "tahoma");
cell.Style.Add ( "font-size", "10pt");
cell.Style.Add ( "font-weight", "normal");
cell.Style.Add ( "background-color", "black");
cell.Style.Add ( "color", "white");
cell.Style.Add ( "text-align", "center");
    }
else
cell.Attributes123.Add ( "Class", _CssTopHeading123);
}

This is the CSS style methods. This X-axis using the popular style. If you do not specify a CSS class name to this attribute, the method will use the default style. CSS class will be applied to the web page in the HTML table. Check and reply.

**opaper** · 13-02-2010

PivotTable method is where all the magic happen. There are two overloaded methods, one to create a simple pivot table, while the other to create an advanced pivot table. The only difference is that a simple one of the z-axis, while the high-level, more than one. You can download this file from the website. If you found any issue please reply.

**Reegan** · 13-02-2010

I have also using Pivot is a class library solution. You can compile the solution and in the Web application, reference Pivot.dll. Another solution is to PivotTest,it is an ASP.NET application. This shows how to implement Pivot class.

Code:

public DataTable DataTableForTesting123
{
get
    {
DataTable data1 = new DataTable ( "Table");
dt.Columns.Add ( "Sales Person");
dt.Columns.Add ( "Quantity");
dt.Columns.Add ( "Sale Amount");
dt.Rows.Add (new object [] ( "John", "Pens", 200, 350.00));
dt.Rows.Add (new object [] ( "John", "Pencils", 400, 500.00));
dt.Rows.Add (new object [] ( "John", "Notebooks", 100, 300.00));
return data1;
    }
}

**Zecho** · 14-02-2010

I have created a data table attribute, it is based the data sheet.

Code:

protected void Page_Load123 (object sender, EventArgs e)
{
Pivot advPivot123 = new Pivot123 (DataTableForTesting);
HtmlTable advancedPivot123 = advPivot.PivotTable123 ( "Sales Person", "Product", new string [] ( "Sale Amount123", "Quantity123"));
div123.Controls.Add (advancedPivot);
Pivot pivot = new Pivot (DataTableForTesting);
pivot.CssTopHeading = "Heading";
pivot.CssItems = "Items";
pivot.CssTotals = "Totals";
pivot.CssTable = "Table";
HtmlTable simplePivot123 = pivot.PivotTable123 ( "Product", "Sales Person", );
div2.Controls.Add (simplePivot);
}

The code includes two instances of an object-oriented pivot. The first and the second an advanced pivot is a simple pivot. You can see I've added a HtmlTable for the div control. I created with a runat = "server" attribute of the div, so that I can access it inside the code in the background. div just help HtmlTable positioning.