public static void CreateSpreadsheetWorkbook(string filepath, DataTable dt, List<SQLBuilder.Clauses.Column> SelectedColumns) { 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 Workbook(); WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); 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 = ""; uint row = 2; int index; Cell cell; foreach (DataRow dr in dt.Rows) { for (int idx = 0; idx < dt.Columns.Count; idx++) { if (idx >= 26) { cl = Convert.ToString(Convert.ToChar(65 + ((idx / 26) - 1))) + Convert.ToString(Convert.ToChar(65 + idx % 26)); } else { cl = Convert.ToString(Convert.ToChar(65 + idx)); } 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[idx].ColumnName, shareStringPart); cell = InsertCellInWorksheet(cl, row - 1, worksheetPart); cell.CellValue = new CellValue(index.ToString()); cell.DataType = new EnumValue<CellValues>(CellValues.SharedString); cell.StyleIndex = 7; } cell = InsertCellInWorksheet(cl, row, worksheetPart); cell.CellValue = new CellValue(Convert.ToString(dr[idx])); if (dr[idx] != null) { if (SelectedColumns.Count != 0) { string colFormat = SelectedColumns[idx].Format; if (colFormat != null) { cell.StyleIndex = (UInt32)SQLBuilder.Common.ColumnFormat.Instance.getExcelColumnFormat(colFormat); if (dr[idx].GetType() == typeof(string)) { cell.DataType = CellValues.String; } else if (dr[idx].GetType() == typeof(bool)) { cell.DataType = CellValues.Boolean; } else if (dr[idx].GetType() == typeof(DateTime)) { cell.DataType = CellValues.Date; } else if (dr[idx].GetType() == typeof(decimal) || dr[idx].GetType() == typeof(double)) { cell.DataType = CellValues.Number; } else if (dr[idx].GetType() == typeof(Int16) || dr[idx].GetType() == typeof(Int32) || dr[idx].GetType() == typeof(Int64)) { cell.DataType = CellValues.Number; } else { cell.DataType = CellValues.String; } } else { cell.StyleIndex = 8; cell.DataType = CellValues.String; } } } } row++; } worksheetPart.Worksheet.Save(); workbookpart.Workbook.Save(); // Close the document. spreadsheetDocument.Close(); }
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(); }