private void PopulateCrossTabulationViewControl(SQLBuilder.SelectQueryBuilder QuerryBuilder)
        {
            //display  CrossTabulation header
            this.lblSummaryMainColumnName.Content = "Cross Tabulation by " + QuerryBuilder.CrossTabulationResults.CrossTabColumn.AliasName;

            // adding gropby column control
            int lineNumber = 0;
            List<List<string>> groupByColumnValues = QuerryBuilder.CrossTabulationResults.GroupByColumnValueList;
            int goupbyColumValueIndex = 0;
            for (int groupByColIndex = 0; groupByColIndex < QuerryBuilder.CrossTabulationResults.GroupByColumns.Count; groupByColIndex++)
            {
                SQLBuilder.Clauses.Column groupByCol = QuerryBuilder.CrossTabulationResults.GroupByColumns.ElementAt<SQLBuilder.Clauses.Column>(groupByColIndex);
                // find colum format
                string colFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(groupByCol.Format);
                CrossTabulationViewGroupByControl ctvgCntrl = new CrossTabulationViewGroupByControl();
                ctvgCntrl.lblGroupByColumnHeader.Content = Common.getColumnNameOrAlias(groupByCol);
                for (int i = 0; i < groupByColumnValues.Count; i++)
                {
                    Label colRow = new Label();

                    if ((lineNumber % 2) == 0)
                    {
                        colRow.Style = (Style)FindResource("CrossTabulationEvenGroupByColumRowStyle");
                    }
                    else
                    {
                        colRow.Style = (Style)FindResource("CrossTabulationOddGroupByColumRowStyle");
                    }

                    if (colFormat != null)
                    {
                        //formating group by col value
                        colRow.Content = String.Format(colFormat, groupByColumnValues.ElementAt<List<string>>(i).ElementAt<string>(groupByColIndex));
                    }
                    else
                    {
                        colRow.Content = groupByColumnValues.ElementAt<List<string>>(i).ElementAt<string>(groupByColIndex);
                    }
                    ctvgCntrl.StackPaenlGroupbyColumnRows.Children.Add(colRow);

                    lineNumber = lineNumber + 1;
                }
                lineNumber = 0;
                this.StackPanelCrossTabulationViewGroupByControls.Children.Add(ctvgCntrl);
                goupbyColumValueIndex = goupbyColumValueIndex + 1;
            }
            lineNumber = 0;
            int groupByColCount = QuerryBuilder.CrossTabulationResults.GroupByColumns.Count;
            int summarrizeValueIndex = groupByColCount;
            // adding summary main control and summary column control
            // add Grand Total to CrossTabColumnVaues as las item
            Dictionary<string, Object> dataMap = QuerryBuilder.CrossTabulationResults.DataMap;
            QuerryBuilder.CrossTabulationResults.CrossTabColumnVaues.Add("Grand Total");

            foreach (string summaryMainValue in QuerryBuilder.CrossTabulationResults.CrossTabColumnVaues)
            {
                CrossTabulationViewSummaryMainControl summaryMain = new CrossTabulationViewSummaryMainControl();
                summaryMain.lblSummaryHeader.Content = summaryMainValue;

                for (int summaryColIndex = 0; summaryColIndex < QuerryBuilder.CrossTabulationResults.SummarizeColumns.Count; summaryColIndex++)
                {

                    SQLBuilder.Clauses.Column summaryCol = QuerryBuilder.CrossTabulationResults.SummarizeColumns.ElementAt<SQLBuilder.Clauses.Column>(summaryColIndex);
                    // find column format
                    string summarycolFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(summaryCol.Format);
                    CrossTabulationViewSummaryControl ctvsCtrl = new CrossTabulationViewSummaryControl();
                    ctvsCtrl.lblSummaryColumnHeader.Content = " " + Common.getColumnNameOrAlias(summaryCol) + " ";
                    //chnageing background  color for last grad total section
                    if (summaryMainValue == "Grand Total")
                    {
                        summaryMain.lblSummaryHeader.Style = (Style)FindResource("CrossTabulationGrandTotalHeaderColumnStyle");
                        ctvsCtrl.lblSummaryColumnHeader.Style = (Style)FindResource("CrossTabulationGrandTotalHeaderColumnStyle");
                    }

                    for (int keyIndex = 0; keyIndex < QuerryBuilder.CrossTabulationResults.KeyPrefixes.Count; keyIndex++)
                    {
                        string key = QuerryBuilder.CrossTabulationResults.KeyPrefixes.ElementAt<string>(keyIndex);
                        Label colRow = new Label();

                        if ((lineNumber % 2) == 0)
                        {
                            if (summaryMainValue == "Grand Total")
                            {
                                colRow.Style = (Style)FindResource("CrossTabulationGrandTotalSummaryColumRowStyle");
                            }
                            else
                            {
                                colRow.Style = (Style)FindResource("CrossTabulationEvenSummaryColumRowStyle");
                            }
                        }
                        else
                        {
                            colRow.Style = (Style)FindResource("CrossTabulationOddSummaryColumRowStyle");
                        }
                        string keyValue = key + summaryMainValue + summaryColIndex;
                        if (dataMap.ContainsKey(keyValue))
                        {
                            if (summarycolFormat != null)
                            {
                                colRow.Content = String.Format(summarycolFormat, dataMap[keyValue]);
                            }
                            else
                            {
                                colRow.Content = dataMap[keyValue].ToString();
                            }

                        }

                        else
                        {

                            colRow.Content =
                            "";
                        }
                        DockPanel dp = new DockPanel();
                        dp.HorizontalAlignment = System.Windows.HorizontalAlignment.Stretch;
                        dp.VerticalAlignment = System.Windows.VerticalAlignment.Stretch;
                        dp.Children.Add(colRow);
                        ctvsCtrl.StackPaenlSummaryColumnRows.Children.Add(dp);

                        lineNumber = lineNumber + 1;
                    }

                    lineNumber = 0;
                    summaryMain.StackPaenlSummaryMainColumns.Children.Add(ctvsCtrl);
                    summarrizeValueIndex = summarrizeValueIndex + 1;
                }

                this.StackPanelCrossTabulationViewSummaryControls.Children.Add(summaryMain);
            }

        }
Beispiel #2
0
        public static void CreateCrossTabulation(string filePath, DataTable dt, List<SQLBuilder.Clauses.Column> SelectedColumns, ResultViewModel rvm)
        {
            FileInfo f = new FileInfo(filePath);
            if (f.Exists)
                f.Delete();

            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook);
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            var stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorkbookStylesPart>();
            Stylesheet styles = new CustomStylesheet();
            styles.Save(stylesPart);
            workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData());

            DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = (spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets()));

            Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
            sheets.Append(sheet);
            string cl = "A";
            uint row = 1;

            int index;
            Cell cell = new Cell();
            System.Windows.Controls.Label colRow = new System.Windows.Controls.Label();
            int lineNumber = 0;

            colRow.Content = "Cross Tabulation by " + rvm.QueryBulder.CrossTabulationResults.CrossTabColumn.AliasName;
            cell = InsertCellInWorksheet(cl, row, worksheetPart);
            cell.CellValue = new CellValue(Convert.ToString(colRow.Content));
            cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
            cell.StyleIndex = 7;

            row++;

            List<List<string>> groupByColumnValues = rvm.QueryBulder.CrossTabulationResults.GroupByColumnValueList;

            for (int groupByColIndex = 0; groupByColIndex < rvm.QueryBulder.CrossTabulationResults.GroupByColumns.Count; groupByColIndex++)
            {
                SQLBuilder.Clauses.Column groupByCol = rvm.QueryBulder.CrossTabulationResults.GroupByColumns.ElementAt<SQLBuilder.Clauses.Column>(groupByColIndex);

                string colFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(groupByCol.Format);
                CrossTabulationViewGroupByControl ctvgCntrl = new CrossTabulationViewGroupByControl();
                colRow.Content = Common.getColumnNameOrAlias(groupByCol);

                for (int i = 0; i <= groupByColumnValues.Count + 1; i++)
                {
                    if (groupByColIndex >= 26)
                    {
                        cl = Convert.ToString(Convert.ToChar(65 + ((groupByColIndex / 26) - 1))) + Convert.ToString(Convert.ToChar(65 + groupByColIndex % 26));
                    }
                    else
                    {
                        cl = Convert.ToString(Convert.ToChar(65 + groupByColIndex));
                    }
                    SharedStringTablePart shareStringPart;
                    if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                    {
                        shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                    }
                    else
                    {
                        shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
                    }
                    if (row == 2)
                    {
                        index = InsertSharedStringItem(dt.Columns[groupByColIndex].ColumnName, shareStringPart);
                        cell = InsertCellInWorksheet(cl, row, worksheetPart);
                        cell.CellValue = new CellValue(Convert.ToString(dt.Columns[groupByColIndex]));
                        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                        cell.StyleIndex = 7;
                    }
                    else if (row > 3)
                    {
                        cell = InsertCellInWorksheet(cl, row, worksheetPart);
                        cell.CellValue = new CellValue(Convert.ToString(groupByColumnValues.ElementAt<List<string>>(i - 2).ElementAt<string>(groupByColIndex)));
                    }
                    lineNumber = lineNumber + 1;
                    row++;
                }
                row = 2;
            }

            int groupByColCount = rvm.QueryBulder.CrossTabulationResults.GroupByColumns.Count;
            int summarrizeValueIndex = groupByColCount + 1;
            Dictionary<string, Object> dataMap = rvm.QueryBulder.CrossTabulationResults.DataMap;
            row = 1;
            foreach (string summaryMainValue in rvm.QueryBulder.CrossTabulationResults.CrossTabColumnVaues)
            {

                CrossTabulationViewSummaryMainControl summaryMain = new CrossTabulationViewSummaryMainControl();
                summaryMain.lblSummaryHeader.Content = summaryMainValue;
                int totalcolumn = rvm.QueryBulder.CrossTabulationResults.SummarizeColumns.Count + groupByColCount;
                for (int summaryColIndex = 0; summaryColIndex < rvm.QueryBulder.CrossTabulationResults.SummarizeColumns.Count; summaryColIndex++)
                {
                    SQLBuilder.Clauses.Column summaryCol = rvm.QueryBulder.CrossTabulationResults.SummarizeColumns.ElementAt<SQLBuilder.Clauses.Column>(summaryColIndex);
                    string summaryColName = summaryCol.AliasName;
                    string summarycolFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(summaryCol.Format);
                    CrossTabulationViewSummaryControl ctvsCtrl = new CrossTabulationViewSummaryControl();

                    for (int keyIndex = 0; keyIndex <= rvm.QueryBulder.CrossTabulationResults.KeyPrefixes.Count + 2; keyIndex++)
                    {
                        if (summaryColIndex >= 26)
                        {
                            cl = Convert.ToString(Convert.ToChar(65 + ((summarrizeValueIndex / 26) - 1))) + Convert.ToString(Convert.ToChar(65 + summarrizeValueIndex % 26));
                        }
                        else
                        {
                            cl = Convert.ToString(Convert.ToChar(65 + summarrizeValueIndex));
                        }
                        SharedStringTablePart shareStringPart;
                        if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                        {
                            shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                        }
                        else
                        {
                            shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
                        }
                        if (row == 1)
                        {
                            index = InsertSharedStringItem(summaryMainValue, shareStringPart);
                            cell = InsertCellInWorksheet(cl, row, worksheetPart);
                            cell.CellValue = new CellValue(Convert.ToString(summaryMainValue));
                            cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                            cell.StyleIndex = 7;
                        }
                        else if (row == 2)
                        {
                            index = InsertSharedStringItem(summaryColName, shareStringPart);
                            cell = InsertCellInWorksheet(cl, row, worksheetPart);
                            cell.CellValue = new CellValue(Convert.ToString(summaryColName));
                            cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                            cell.StyleIndex = 7;
                        }
                        else if (row > 3)
                        {
                            string key = rvm.QueryBulder.CrossTabulationResults.KeyPrefixes.ElementAt<string>(keyIndex - 3);

                            string keyValue = key + summaryMainValue + summaryColIndex;

                            cell = InsertCellInWorksheet(cl, row, worksheetPart);
                            if (dataMap.ContainsKey(keyValue))
                            {
                                if (summarycolFormat != null)
                                {
                                    cell.CellValue = new CellValue(String.Format(summarycolFormat, dataMap[keyValue]));
                                }
                                else
                                {
                                    cell.CellValue = new CellValue(Convert.ToString(dataMap[keyValue]));
                                }
                            }
                            else
                            {
                                cell.CellValue = new CellValue(Convert.ToString("00"));
                            }

                        }
                        lineNumber = lineNumber + 1;

                        if (row > rvm.QueryBulder.CrossTabulationResults.KeyPrefixes.Count + 2)
                        {
                            row = 1;
                            summarrizeValueIndex = summarrizeValueIndex + 1;
                        }
                        else
                        {
                            row++;
                        }
                    }
                    lineNumber = 0;
                }
                row = 1;
                groupByColCount = groupByColCount + 1;
            }
            worksheetPart.Worksheet.Save();
            workbookpart.Workbook.Save();
            spreadsheetDocument.Close();
        }
Beispiel #3
0
        private void PopulateCrossTabulationViewControl(SQLBuilder.SelectQueryBuilder QuerryBuilder)
        {
            //display  CrossTabulation header
            this.lblSummaryMainColumnName.Content = "Cross Tabulation by " + QuerryBuilder.CrossTabulationResults.CrossTabColumn.AliasName;

            // adding gropby column control
            int lineNumber = 0;
            List <List <string> > groupByColumnValues = QuerryBuilder.CrossTabulationResults.GroupByColumnValueList;
            int goupbyColumValueIndex = 0;

            for (int groupByColIndex = 0; groupByColIndex < QuerryBuilder.CrossTabulationResults.GroupByColumns.Count; groupByColIndex++)
            {
                SQLBuilder.Clauses.Column groupByCol = QuerryBuilder.CrossTabulationResults.GroupByColumns.ElementAt <SQLBuilder.Clauses.Column>(groupByColIndex);
                // find colum format
                string colFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(groupByCol.Format);
                CrossTabulationViewGroupByControl ctvgCntrl = new CrossTabulationViewGroupByControl();
                ctvgCntrl.lblGroupByColumnHeader.Content = Common.getColumnNameOrAlias(groupByCol);
                for (int i = 0; i < groupByColumnValues.Count; i++)
                {
                    Label colRow = new Label();

                    if ((lineNumber % 2) == 0)
                    {
                        colRow.Style = (Style)FindResource("CrossTabulationEvenGroupByColumRowStyle");
                    }
                    else
                    {
                        colRow.Style = (Style)FindResource("CrossTabulationOddGroupByColumRowStyle");
                    }

                    if (colFormat != null)
                    {
                        //formating group by col value
                        colRow.Content = String.Format(colFormat, groupByColumnValues.ElementAt <List <string> >(i).ElementAt <string>(groupByColIndex));
                    }
                    else
                    {
                        colRow.Content = groupByColumnValues.ElementAt <List <string> >(i).ElementAt <string>(groupByColIndex);
                    }
                    ctvgCntrl.StackPaenlGroupbyColumnRows.Children.Add(colRow);

                    lineNumber = lineNumber + 1;
                }
                lineNumber = 0;
                this.StackPanelCrossTabulationViewGroupByControls.Children.Add(ctvgCntrl);
                goupbyColumValueIndex = goupbyColumValueIndex + 1;
            }
            lineNumber = 0;
            int groupByColCount      = QuerryBuilder.CrossTabulationResults.GroupByColumns.Count;
            int summarrizeValueIndex = groupByColCount;
            // adding summary main control and summary column control
            // add Grand Total to CrossTabColumnVaues as las item
            Dictionary <string, Object> dataMap = QuerryBuilder.CrossTabulationResults.DataMap;

            QuerryBuilder.CrossTabulationResults.CrossTabColumnVaues.Add("Grand Total");

            foreach (string summaryMainValue in QuerryBuilder.CrossTabulationResults.CrossTabColumnVaues)
            {
                CrossTabulationViewSummaryMainControl summaryMain = new CrossTabulationViewSummaryMainControl();
                summaryMain.lblSummaryHeader.Content = summaryMainValue;

                for (int summaryColIndex = 0; summaryColIndex < QuerryBuilder.CrossTabulationResults.SummarizeColumns.Count; summaryColIndex++)
                {
                    SQLBuilder.Clauses.Column summaryCol = QuerryBuilder.CrossTabulationResults.SummarizeColumns.ElementAt <SQLBuilder.Clauses.Column>(summaryColIndex);
                    // find column format
                    string summarycolFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(summaryCol.Format);
                    CrossTabulationViewSummaryControl ctvsCtrl = new CrossTabulationViewSummaryControl();
                    ctvsCtrl.lblSummaryColumnHeader.Content = " " + Common.getColumnNameOrAlias(summaryCol) + " ";
                    //chnageing background  color for last grad total section
                    if (summaryMainValue == "Grand Total")
                    {
                        summaryMain.lblSummaryHeader.Style    = (Style)FindResource("CrossTabulationGrandTotalHeaderColumnStyle");
                        ctvsCtrl.lblSummaryColumnHeader.Style = (Style)FindResource("CrossTabulationGrandTotalHeaderColumnStyle");
                    }

                    for (int keyIndex = 0; keyIndex < QuerryBuilder.CrossTabulationResults.KeyPrefixes.Count; keyIndex++)
                    {
                        string key    = QuerryBuilder.CrossTabulationResults.KeyPrefixes.ElementAt <string>(keyIndex);
                        Label  colRow = new Label();

                        if ((lineNumber % 2) == 0)
                        {
                            if (summaryMainValue == "Grand Total")
                            {
                                colRow.Style = (Style)FindResource("CrossTabulationGrandTotalSummaryColumRowStyle");
                            }
                            else
                            {
                                colRow.Style = (Style)FindResource("CrossTabulationEvenSummaryColumRowStyle");
                            }
                        }
                        else
                        {
                            colRow.Style = (Style)FindResource("CrossTabulationOddSummaryColumRowStyle");
                        }
                        string keyValue = key + summaryMainValue + summaryColIndex;
                        if (dataMap.ContainsKey(keyValue))
                        {
                            if (summarycolFormat != null)
                            {
                                colRow.Content = String.Format(summarycolFormat, dataMap[keyValue]);
                            }
                            else
                            {
                                colRow.Content = dataMap[keyValue].ToString();
                            }
                        }

                        else
                        {
                            colRow.Content =
                                "";
                        }
                        DockPanel dp = new DockPanel();
                        dp.HorizontalAlignment = System.Windows.HorizontalAlignment.Stretch;
                        dp.VerticalAlignment   = System.Windows.VerticalAlignment.Stretch;
                        dp.Children.Add(colRow);
                        ctvsCtrl.StackPaenlSummaryColumnRows.Children.Add(dp);

                        lineNumber = lineNumber + 1;
                    }

                    lineNumber = 0;
                    summaryMain.StackPaenlSummaryMainColumns.Children.Add(ctvsCtrl);
                    summarrizeValueIndex = summarrizeValueIndex + 1;
                }

                this.StackPanelCrossTabulationViewSummaryControls.Children.Add(summaryMain);
            }
        }