Search This Blog

Friday, February 7, 2020

Create Pivot table in Window Application Form using c#


using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Windows.Forms;

namespace TESTWindowsApps
{
    public partial class Winform : Form
    {
        static string connString = ConfigurationManager.AppSettings["connString"];
        DataTable results = new DataTable();
        DataTable TESTSummary = new DataTable();
        string code;string startdate;string toDate;
        public string ProcessCode
        {

            get { return code; }
            set { code = value; }
        }
        public string StartDate
        {

            get { return startdate; }
            set { startdate = value; }
        }
        public string ToDate
        {

            get { return toDate; }
            set { toDate = value; }
        }
        public Winform()
        {
            InitializeComponent();
        }

        private void Winform_Load(object sender, EventArgs e)
        {
            try
            {
                results = new DataTable();
                using (OleDbConnection conn = new OleDbConnection(connString))
                {
                    conn.Open();
                    DataTable TESTSummary = getTESTSummaryAllData(conn, "SELECT * FROM TESTSummaryQuery", ProcessCode, Convert.ToDateTime(StartDate).ToString("dd/MM/yyyy"), Convert.ToDateTime(ToDate).ToString("dd/MM/yyyy"));              
                    lblCell.Text = "IS";
                    lblProcess.Text = "Test Service Request";
                    //Advanced Pivot
                    Pivote advPivot = new Pivote(TESTSummary);
                    HtmlTable advancedPivot = advPivot.PivotTable("OperationCode", "TESTSummaryQuery.YearWeek", new string[] { "TB", "Percentage", "Output" });
                    StringWriter sw1 = new StringWriter();
                    HtmlTextWriter hw1 = new HtmlTextWriter(sw1);
                    advancedPivot.RenderControl(hw1);
                    StringReader sr1 = new StringReader(sw1.ToString());
                    string aLine, aParagraph = null;
                    while (true)
                    {
                        aLine = sr1.ReadLine();
                        if (aLine != null)
                        {
                            aParagraph = aParagraph + aLine + " ";
                        }
                        else
                        {
                            aParagraph = aParagraph + "\n";
                            break;
                        }
                    }
                    System.IO.File.WriteAllText(@"c:\TEST\Source\HTML\Pivot.HTML", aParagraph.ToString());
                    this.webBrowser1.Navigate("file:///c:/TEST/Source/HTML/Pivot.HTML");
                    results= TESTSummary.DefaultView.ToTable(true, "OperationCode", "OperationName");
                    dataGridView1.DataSource = results;
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                string msg = "Fetch Error:";
                msg += ex.Message;
                MessageBox.Show(msg);
            }
        }
        private DataTable getTESTSummaryAllData(OleDbConnection conn, string Qry, string process, string startdate, string enddate)
        {
            results = new DataTable();
            using (OleDbCommand cmd = new OleDbCommand(Qry, conn))
            {
                cmd.Parameters.Add("@Process", OleDbType.VarChar).Value = process;
                cmd.Parameters["@Process"].Value = process;

                cmd.Parameters.Add("@StartDate", OleDbType.VarChar).Value = startdate;
                cmd.Parameters["@StartDate"].Value = startdate;

                cmd.Parameters.Add("@EndDate", OleDbType.VarChar).Value = enddate;
                cmd.Parameters["@EndDate"].Value = enddate;
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
                {
                    adapter.Fill(results);
                }
            }        
          //  return results.DefaultView.ToTable(true,  "TESTSummaryQuery.OperationCode", "OperationName");
            return results;
        }

        private void btnGoBack_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}
/// <summary>
    /// Create simple and advanced pivot reports.
    /// </summary>
    public class Pivot
    {
        #region Variables

        private DataTable _DataTable;
        private string _CssTopHeading;
        private string _CssSubHeading;
        private string _CssLeftColumn;
        private string _CssItems;
        private string _CssTotals;
        private string _CssTable;

        #endregion Variables

        #region Constructors

        public Pivot(DataTable dataTable)
        {
            Init();
            _DataTable = dataTable;
        }

        #endregion Constructors

        #region Properties

        public DataTable ResultTable
        {
            get { return _DataTable; }
        }

        public string CssTopHeading
        {
            get { return _CssTopHeading; }
            set { _CssTopHeading = value; }
        }

        public string CssSubHeading
        {
            get { return _CssSubHeading; }
            set { _CssSubHeading = value; }
        }

        public string CssLeftColumn
        {
            get { return _CssLeftColumn; }
            set { _CssLeftColumn = value; }
        }

        public string CssItems
        {
            get { return _CssItems; }
            set { _CssItems = value; }
        }

        public string CssTotals
        {
            get { return _CssTotals; }
            set { _CssTotals = value; }
        }

        public string CssTable
        {
            get { return _CssTable; }
            set { _CssTable = value; }
        }

        #endregion Properties

        #region Private Methods

        private string[] FindValues(string xAxisField, string xAxisValue, string yAxisField, string yAxisValue, string[] zAxisFields)
        {
            int zAxis = zAxisFields.Length;
            if (zAxis < 1)
                zAxis++;
            string[] zAxisValues = new string[zAxis];
            //set default values
            for (int i = 0; i <= zAxisValues.GetUpperBound(0); i++)
            {
                zAxisValues[i] = "0";
            }

            try
            {
                foreach (DataRow row in _DataTable.Rows)
                {
                    if (Convert.ToString(row[xAxisField]) == xAxisValue && Convert.ToString(row[yAxisField]) == yAxisValue)
                    {
                        for (int z = 0; z < zAxis; z++)
                        {
                            zAxisValues[z] = Convert.ToString(row[zAxisFields[z]]);
                        }
                        break;
                    }
                }
            }
            catch
            {
                throw;
            }

            return zAxisValues;
        }

        private string FindValue(string xAxisField, string xAxisValue, string yAxisField, string yAxisValue, string zAxisField)
        {
            string zAxisValue = "";

            try
            {
                foreach (DataRow row in _DataTable.Rows)
                {
                    if (Convert.ToString(row[xAxisField]) == xAxisValue && Convert.ToString(row[yAxisField]) == yAxisValue)
                    {
                        zAxisValue = Convert.ToString(row[zAxisField]);
                        break;
                    }
                }
            }
            catch
            {
                throw;
            }

            return zAxisValue;
        }

        private void Init()
        {
            _CssTopHeading = "";
            _CssSubHeading = "";
            _CssLeftColumn = "";
            _CssItems = "";
            _CssTotals = "";
            _CssTable = "";
        }

        private void TableStyle(HtmlTable table)
        {
            if (_CssTable == "")
            {
                table.Style.Add("border-style", "solid");
                table.Style.Add("border-width", "1px");
                table.Style.Add("border-color", "Gray");
            }
            else
                table.Attributes.Add("Class", _CssTable);
        }

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

        private void MainHeaderLeftCellStyle(HtmlTableCell cell)
        {
            if (_CssLeftColumn == "")
            {
                cell.Style.Add("font-family", "tahoma");
                cell.Style.Add("font-size", "10pt");
                cell.Style.Add("font-weight", "normal");
                cell.Style.Add("background-color", "Gray");
                cell.Style.Add("color", "white");
            }
            else
                cell.Attributes.Add("Class", _CssLeftColumn);
        }

        private void SubHeaderCellStyle(HtmlTableCell cell)
        {
            if (_CssSubHeading == "")
            {
                cell.Style.Add("font-family", "tahoma");
                cell.Style.Add("font-size", "10pt");
                cell.Style.Add("font-weight", "normal");
                cell.Style.Add("background-color", "white");
                cell.Style.Add("color", "black");
            }
            else
                cell.Attributes.Add("Class", _CssSubHeading);
        }

        private void ItemCellStyle(HtmlTableCell cell)
        {
            if (_CssItems == "")
            {
                cell.Style.Add("font-family", "tahoma");
                cell.Style.Add("font-size", "8pt");
                cell.Style.Add("font-weight", "normal");
                cell.Style.Add("background-color", "#dde4f0");
                cell.Style.Add("width", "100px");
                cell.Style.Add("color", "Gray");
            }
            else
                cell.Attributes.Add("Class", _CssItems);
        }

        private void TotalCellStyle(HtmlTableCell cell)
        {
            if (_CssTotals == "")
            {
                cell.Style.Add("font-family", "tahoma");
                cell.Style.Add("font-size", "10pt");    
                cell.Style.Add("background-color", "Gray");
                cell.Style.Add("color", "white");
            }
            else
                cell.Attributes.Add("Class", _CssTotals);
        }

        #endregion Private Methods
  #region Public Methods

        /// <summary>
        /// Creates an advanced 3D Pivot table.
        /// </summary>
        /// <param name="xAxisField">The main heading at the top of the report.</param>
        /// <param name="yAxisField">The heading on the left of the report.</param>
        /// <param name="zAxisFields">The sub heading at the top of the report.</param>
        /// <returns>HtmlTable Control.</returns>
        public HtmlTable PivotTable(string xAxisField, string yAxisField, string[] zAxisFields)
        {
            HtmlTable table = new HtmlTable();
            //style table
            TableStyle(table);
            /*
                      * The x-axis is the main horizontal row.
                      * The z-axis is the sub horizontal row.
                      * The y-axis is the left vertical column.
                      */

            try
            {
                //get distinct xAxisFields
                ArrayList xAxis = new ArrayList();
                foreach (DataRow row in _DataTable.Rows)
                {
                    if (!xAxis.Contains(row[xAxisField]))
                        xAxis.Add(row[xAxisField]);
                }

                //get distinct yAxisFields
                ArrayList yAxis = new ArrayList();
                foreach (DataRow row in _DataTable.Rows)
                {
                    if (!yAxis.Contains(row[yAxisField]))
                        yAxis.Add(row[yAxisField]);
                }

                //create a 2D array for the y-axis/z-axis fields
                int zAxis = zAxisFields.Length;
                if (zAxis < 1)
                    zAxis = 1;
                string[,] matrix = new string[(xAxis.Count * zAxis), yAxis.Count];
                string[] zAxisValues = new string[zAxis];

                for (int y = 0; y < yAxis.Count; y++) //loop thru y-axis fields
                {
                    //rows
                    for (int x = 0; x < xAxis.Count; x++) //loop thru x-axis fields
                    {
                        //main columns
                        //get the z-axis values
                        zAxisValues = FindValues(xAxisField, Convert.ToString(xAxis[x])
                            , yAxisField, Convert.ToString(yAxis[y]), zAxisFields);
                        for (int z = 0; z < zAxis; z++) //loop thru z-axis fields
                        {
                            //sub columns
                            matrix[(((x + 1) * zAxis - zAxis) + z), y] = zAxisValues[z];
                        }
                    }
                }

                //calculate totals for the y-axis
                decimal[] yTotals = new decimal[(xAxis.Count * zAxis)];
                for (int col = 0; col < (xAxis.Count * zAxis); col++)
                {
                    yTotals[col] = 0;
                    for (int row = 0; row < yAxis.Count; row++)
                    {
                        yTotals[col] += Convert.ToDecimal(matrix[col, row]);
                    }
                }

                //calculate totals for the x-axis
                decimal[,] xTotals = new decimal[zAxis, (yAxis.Count + 1)];
                for (int y = 0; y < yAxis.Count; y++) //loop thru the y-axis
                {
                    int zCount = 0;
                    for (int z = 0; z < (zAxis * xAxis.Count); z++) //loop thru the z-axis
                    {
                        xTotals[zCount, y] += Convert.ToDecimal(matrix[z, y]);
                        if (zCount == (zAxis - 1))
                            zCount = 0;
                        else
                            zCount++;
                    }
                }
                for (int xx = 0; xx < zAxis; xx++) //Grand Total
                {
                    for (int xy = 0; xy < yAxis.Count; xy++)
                    {
                        xTotals[xx, yAxis.Count] += xTotals[xx, xy];
                    }
                }

                //Build HTML Table
                //Append main row (x-axis)
                HtmlTableRow mainRow = new HtmlTableRow();
                mainRow.Cells.Add(new HtmlTableCell());
                for (int x = 0; x <= xAxis.Count; x++) //loop thru x-axis + 1
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.ColSpan = zAxis;
                    if (x < xAxis.Count)
                        cell.InnerText = Convert.ToString(xAxis[x]);
                    else
                        cell.InnerText = "Grand Totals";
                    //style cell
                    MainHeaderTopCellStyle(cell);
                    mainRow.Cells.Add(cell);
                }
                table.Rows.Add(mainRow);
                //Append sub row (z-axis)
                HtmlTableRow subRow = new HtmlTableRow();
                subRow.Cells.Add(new HtmlTableCell());
                subRow.Cells[0].InnerText = yAxisField;
                //style cell
                SubHeaderCellStyle(subRow.Cells[0]);
                for (int x = 0; x <= xAxis.Count; x++) //loop thru x-axis + 1
                {
                    for (int z = 0; z < zAxis; z++)
                    {
                        HtmlTableCell cell = new HtmlTableCell();
                        cell.InnerText = zAxisFields[z];
                        //style cell
                        SubHeaderCellStyle(cell);
                        subRow.Cells.Add(cell);
                    }
                }
                table.Rows.Add(subRow);
                //Append table items from matrix
                for (int y = 0; y < yAxis.Count; y++) //loop thru y-axis
                {
                    HtmlTableRow itemRow = new HtmlTableRow();
                    for (int z = 0; z <= (zAxis * xAxis.Count); z++) //loop thru z-axis + 1
                    {
                        HtmlTableCell cell = new HtmlTableCell();
                        if (z == 0)
                        {
                            cell.InnerText = Convert.ToString(yAxis[y]);
                            //style cell
                            MainHeaderLeftCellStyle(cell);
                        }
                        else
                        {
                            cell.InnerText = Convert.ToString(matrix[(z - 1), y]);
                            //style cell
                            ItemCellStyle(cell);
                        }
                        itemRow.Cells.Add(cell);
                    }
                    //append x-axis grand totals
                    for (int z = 0; z < zAxis; z++)
                    {
                        HtmlTableCell cell = new HtmlTableCell();
                        cell.InnerText = Convert.ToString(xTotals[z, y]);
                        //style cell
                        TotalCellStyle(cell);
                        itemRow.Cells.Add(cell);
                    }
                    table.Rows.Add(itemRow);
                }
                //append y-axis totals
                HtmlTableRow totalRow = new HtmlTableRow();
                for (int x = 0; x <= (zAxis * xAxis.Count); x++)
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    if (x == 0)
                        cell.InnerText = "Totals";
                    else
                        cell.InnerText = Convert.ToString(yTotals[x - 1]);
                    //style cell
                    TotalCellStyle(cell);
                    totalRow.Cells.Add(cell);
                }
                //append x-axis/y-axis totals
                for (int z = 0; z < zAxis; z++)
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.InnerText = Convert.ToString(xTotals[z, xTotals.GetUpperBound(1)]);
                    //style cell
                    TotalCellStyle(cell);
                    totalRow.Cells.Add(cell);
                }
                table.Rows.Add(totalRow);
            }
            catch
            {
                throw;
            }

            return table;
        }
/// <summary>
        /// Creates a simple 3D Pivot Table.
        /// </summary>
        /// <param name="xAxisField">The heading at the top of the table.</param>
        /// <param name="yAxisField">The heading to the left of the table.</param>
        /// <param name="zAxisField">The item value field.</param>
        /// <returns></returns>
        public HtmlTable PivotTable(string xAxisField, string yAxisField, string zAxisField)
        {
            HtmlTable table = new HtmlTable();
            //style table
            TableStyle(table);
            /*
             * The x-axis is the main horizontal row.
             * The z-axis is the sub horizontal row.
             * The y-axis is the left vertical column.
             */

            try
            {
                //get distinct xAxisFields
                ArrayList xAxis = new ArrayList();
                foreach (DataRow row in _DataTable.Rows)
                {
                    if (!xAxis.Contains(row[xAxisField]))
                        xAxis.Add(row[xAxisField]);
                }

                //get distinct yAxisFields
                ArrayList yAxis = new ArrayList();
                foreach (DataRow row in _DataTable.Rows)
                {
                    if (!yAxis.Contains(row[yAxisField]))
                        yAxis.Add(row[yAxisField]);
                }

                //create a 2D array for the x-axis/y-axis fields
                string[,] matrix = new string[xAxis.Count, yAxis.Count];
                string zAxisValue = "";

                for (int y = 0; y < yAxis.Count; y++) //loop thru y-axis fields
                {
                    //rows
                    for (int x = 0; x < xAxis.Count; x++) //loop thru x-axis fields
                    {
                        //main columns
                        //get the z-axis values
                        zAxisValue = FindValue(xAxisField, Convert.ToString(xAxis[x])
                            , yAxisField, Convert.ToString(yAxis[y]), zAxisField);
                        matrix[x, y] = zAxisValue;
                    }
                }

                //calculate totals for the y-axis
                decimal[] yTotals = new decimal[xAxis.Count];
                for (int col = 0; col < xAxis.Count; col++)
                {
                    yTotals[col] = 0;
                    for (int row = 0; row < yAxis.Count; row++)
                    {
                        yTotals[col] += Convert.ToDecimal(matrix[col, row]);
                    }
                }

                //calculate totals for the x-axis
                decimal[] xTotals = new decimal[(yAxis.Count + 1)];
                for (int row = 0; row < yAxis.Count; row++)
                {
                    xTotals[row] = 0;
                    for (int col = 0; col < xAxis.Count; col++)
                    {
                        xTotals[row] += Convert.ToDecimal(matrix[col, row]);
                    }
                }
                xTotals[xTotals.GetUpperBound(0)] = 0; //Grand Total
                for (int i = 0; i < xTotals.GetUpperBound(0); i++)
                {
                    xTotals[xTotals.GetUpperBound(0)] += xTotals[i];
                }

                //Build HTML Table
                HtmlTableRow heading = new HtmlTableRow();
                for (int x = 0; x < (xAxis.Count + 1); x++)
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    if (x == 0)
                        cell.InnerText = yAxisField;
                    else
                        cell.InnerText = Convert.ToString(xAxis[(x - 1)]);
                    //style cell
                    MainHeaderTopCellStyle(cell);
                    heading.Cells.Add(cell);
                }
                //append grand totals heading
                HtmlTableCell grandTotal = new HtmlTableCell();
                grandTotal.InnerText = "Grand Totals";
                //style cell
                MainHeaderTopCellStyle(grandTotal);
                heading.Cells.Add(grandTotal);

                table.Rows.Add(heading);

                for (int y = 0; y < yAxis.Count; y++)
                {
                    HtmlTableRow row = new HtmlTableRow();
                    for (int x = 0; x < (xAxis.Count + 1); x++)
                    {
                        HtmlTableCell cell = new HtmlTableCell();
                        if (x == 0)
                        {
                            cell.InnerText = Convert.ToString(yAxis[y]);
                            //style cell
                            MainHeaderLeftCellStyle(cell);
                        }
                        else
                        {
                            cell.InnerText = Convert.ToString(matrix[(x - 1), y]);
                            //style cell
                            ItemCellStyle(cell);
                        }
                        row.Cells.Add(cell);
                    }
                    //append x-axis totals
                    HtmlTableCell totalCell = new HtmlTableCell();
                    totalCell.InnerText = Convert.ToString(xTotals[y]);
                    //style cell
                    TotalCellStyle(totalCell);
                    row.Cells.Add(totalCell);
                    table.Rows.Add(row);
                }
                //append y-axis totals
                HtmlTableRow totalRow = new HtmlTableRow();
                for (int x = 0; x <= (xAxis.Count + 1); x++)
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    if (x == 0)
                        cell.InnerText = "Totals";
                    else
                        if (x <= xAxis.Count)
                        cell.InnerText = Convert.ToString(yTotals[(x - 1)]);
                    else
                        cell.InnerText = Convert.ToString(xTotals[xTotals.GetUpperBound(0)]);
                    //style cell
                    TotalCellStyle(cell);
                    totalRow.Cells.Add(cell);
                }
                table.Rows.Add(totalRow);
            }
            catch
            {
                throw;
            }

            return table;
        }

        #endregion Public Methods
    }

No comments:

Post a Comment