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);
}
}
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();
}
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);
}
}